D.2.2 Changes in release 4.1.0 (03 Apr 2003: Alpha)

Functionality added or changed:

• On Windows, we are now using shared memory to communicate between server and client when they are running on the same machine and you are connecting to localhost.
• REPAIR of MyISAM tables now uses less temporary disk space when sorting char columns.
• DATE/DATETIME checking is now a bit stricter to support the ability to automatically distinguish between date, datetime, and time with microseconds. For example, dates of type YYYYMMDD HHMMDD are no longer supported; one must either have separators between each DATE/TIME part or not at all.
• Server side help for all MySQL functions. One can now type help week in the mysql client and get help for the week() function.
• Added new client function: mysql_get_server_version().
• Fixed bug in libmysqlclient that fetched field defaults
• Fixed bug in `mysql.cc' client when skipping comments
• Added record_in_range() method to MERGE tables to be able to choose the right index when there are many to choose from.
• Replication now works with RAND() and user variables @var.
• Allow one to change mode for ANSI_QUOTES on the fly.
• EXPLAIN SELECT now can be killed. See section 4.5.6 KILL Syntax.
• REPAIR TABLE now can be killed. See section 4.5.6 KILL Syntax.
• Allow one to specify empty key lists for USE|IGNORE|FORCE INDEX.
• DROP TEMPORARY TABLE now only drops temporary tables and doesn't end transactions.
• Added a support for UNION in derived tables.
• TIMESTAMP is now returned as a string of type 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported. This change was necessary for SQL standards compliance. In a future version, a further change will be made (backward compatible with this change), allowing the timestamp length to indicate the desired number of digits of fractions of a second.
• New faster client/server protocol which supports prepared statements, bound parameters, and bound result columns, binary transfer of data, warnings.
• Added database and real table name (in case of alias) to the MYSQL_FIELD structure.
• Multi-line queries: You can now issue several queries at once and then read the results in one go.
• In CREATE TABLE foo (a INT not null primary key) the PRIMARY word is now optional.
• In CREATE TABLE the attribute SERIAL is now an alias for BIGINT NOT NULL AUTO_INCREMENT UNIQUE.
• SELECT ... FROM DUAL is an alias for SELECT .... (To be compatible with some other databases).
• If one creates a too long CHAR/VARCHAR it's now automatically changed to TEXT or BLOB; One will get a warning in this case.
• One can specify the different BLOB/TEXT types with the syntax BLOB(length) and TEXT(length). MySQL will automatically change it to one of the internal BLOB/TEXT types.
• CHAR BYTE is an alias for CHAR BINARY.
• VARCHARACTER is an alias for VARCHAR.
• New operators integer MOD integer and integer DIV integer.
• SERIAL DEFAULT VALUE added as an alias for AUTO_INCREMENT.
• TRUE and FALSE added as alias for 1 and 0, respectively.
• Aliases are now forced in derived tables, as per SQL-99.
• Fixed SELECT .. LIMIT 0 to return proper row count for SQL_CALC_FOUND_ROWS.
• One can specify many temporary directories to be used in a round-robin fashion with: --tmpdir=dirname1:dirname2:dirname3.
• Subqueries: SELECT * from t1 where t1.a=(SELECT t2.b FROM t2).
• Derived tables:

  SELECT a.col1, b.col2
         FROM (SELECT MAX(col1) AS col1 FROM root_table) a,
         other_table b
         WHERE a.col1=b.col1;
  

• Character sets to be defined per column, table and database.
• Unicode (UTF8) support.
• BTREE index on HEAP tables.
• Faster embedded server (new internal communication protocol).
• One can add a comment per column in CREATE TABLE.
• SHOW FULL COLUMNS FROM table_name shows column comments.
• ALTER DATABASE.
• Support for GIS (Geometrical data). See section 11 Spatial Extensions in MySQL.
• SHOW [COUNT(*)] WARNINGS shows warnings from the last command.
• One can specify a column type for a colum in CREATE TABLE ... SELECT by defining the column in the CREATE part.

  CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;
  

• expr SOUNDS LIKE expr same as SOUNDEX(expr)=SOUNDEX(expr).
• VARIANCE(expr) returns the variance of expr
• One can create a table from the existing table using CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table). The table can be either normal or temporary.
• New options --reconnect and disable-reconnect for the mysql client, to reconnect automatically or not if the connection is lost.
• START SLAVE (STOP SLAVE) no longer returns an error if the slave is already started (stopped); it returns a warning instead.
• SLAVE START and SLAVE STOP are no longer accepted by the query parser; use START SLAVE and STOP SLAVE instead.

User Comments

It would be nice if more attention were spent on the
embedded server; there are many people who I'm
sure aren't quite satiated with level of progress at
the moment...a shared library (DLL) would be
helpful, for instance. Also, it'd be useful if an
embedded server could be accessed via TCP/IP or
sockets (this would be a compile-time option, so as
not to bloat the normal version).

I would like to encourage the work on the primary linux server first, not the embedded one (with reference to the previous comment). As a user of mysql since it's first version to get sub-select into a production version would be fantastic. We're currently a very large user of replication, one master - 18 slaves, getting the reliability of 4.1 will be great. Please keep up the good work.
Thanks

Mark
www.cd-wow.com

I think that the change in TIMESTAMP format was unfortunate. The first I knew about it was when my hosting company upgraded the server last week (they didn't tell me of course) - I got a call about my sites yesterday. Lots of scripts either threw up errors or didn't display properly. Having just tracked this down, I have a lot of work to do this week trying to fix stuff.

More advanced REPLICATION is also important to me, and my company.

I am cheered by the very needed inclusion of subqueries and derived tables. Very badly missed by myself, particularly. I am not demanding flawless,enterprise resilience - I find MySQL handles my web apps very well, and the featureset being enhanced is a great benefit.

Bravo gentlemen! Keep up the great work.

You did a great job. In reference to previous messages I agree. The most important things are advanced replication, sub queries and derived tables.

Just wanted to mention that adding sub selects will be greatly appreciated. They're very usefull for multi step data processing tasks.
I hope 4.1 reaches some level of stability soon :)

.netghost

Great addition of the awaited subqueries!!
But oh nooo, now i finally worked out an update that makes use of them, and now suddenly i spot the TIMESTAMP 'bug'. I'm completely in trouble now, as i am running MySQL on a Windows host, and there is no ODBC driver out yet that can 'read' the new TIMESTAMPS... Please upload the compatible ODBC driver to the database!

Enhorabuena. Estabamos esperando las SUBQUERIES para poner MySQL en producción. Muchas gracias al equipo de desarrollo.

English: thanks very much, mysql development team

Please enable setting AUTOCOMMIT on database startup!

Add your own comment.