SMF Version: SMF 1.1 RC3
Soon my host plan to upgrade MySQL from 4.0 to 4.1
They say that some code might not be compatible if its written for 4.0
here are a list of what wont be compatible
The following is a list of incompatible changes in MySQL 4.1 compared to
MySQL 4.0. Additional details can be found on MySQL's website at
http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html
- String comparison works according to SQL standard: Instead of stripping
end spaces before comparison, we now extend the shorter string with
spaces. The problem with this is that now 'a' > 'a\t', which it was not
before.
- MyISAM tables now use an improved checksum algorithm in MySQL 4.1. If
you have MyISAM tables with live checksum enabled (you used CHECKSUM=1
in CREATE TABLE or ALTER TABLE), these tables appear to be corrupted
following an upgrade. Use REPAIR TABLE to recalculate the checksum for
each such table.
- MySQL interprets length specifications in character column definitions
in characters. (Earlier versions interpret them in bytes.) For example,
CHAR(N) means N characters, not N bytes. For single-byte character sets,
this change makes no difference. However, if you upgrade to MySQL 4.1
and configure the server to use a multi-byte character set, the apparent
length of character columns changes. Suppose that a 4.0 table contains a
CHAR(8) column used to store ujis characters. Eight bytes can store from
two to four ujis characters. If you upgrade to 4.1 and configure the
server to use ujis as its default character set, the server interprets
character column lengths based on the maximum size of a ujis character,
which is three bytes. The number of three-byte characters that fit in
eight bytes is two. Consequently, if you use SHOW CREATE TABLE to view
the table definition, MySQL displays CHAR(2). You can retrieve existing
data from the table, but you can only store new values containing up to
two characters. To correct this issue, use ALTER TABLE to change the
column definition.
- As of MySQL 4.1.2, handling of the FLOAT and DOUBLE floating-point data
types is more strict to follow standard SQL. For example, a data type of
FLOAT(3,1) stores a maximum value of 99.9. Before 4.1.2, the server
allowed larger numbers to be stored. That is, it stored a value such as
100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the maximum
allowable value of 99.9. If you have tables that were created before
MySQL 4.1.2 and that contain floating-point data not strictly legal for
the data type, you should alter the data types of those columns.
- In connection with the support for per-connection time zones in MySQL
4.1.3, the timezone system variable was renamed to system_time_zone.
- If you were using columns with the CHAR BINARY or VARCHAR BINARY data
types in MySQL 4.0, these were treated as binary strings. To have them
treated as binary strings in MySQL 4.1, you should convert them to the
BINARY and VARBINARY data types, respectively.
- The interface to aggregate user-defined functions changed as of MySQL
4.1.1. You must declare a xxx_clear() function for each aggregate
function XXX(). xxx_clear() is used instead of xxx_reset().
- In MySQL 4.1, TIMESTAMP display format changes to be the same as
DATETIME, that is, as a string in 'YYYY-MM-DD HH:MM:SS' format rather
than as a number in YYYYMMDDHHMMSS format.
- Display widths for TIMESTAMP columns are no longer supported in MySQL
4.1. For example, if you declare a column as TIMESTAMP(10), the (10) is
ignored.
- Binary values such as 0xFFDF are assumed to be strings instead of
numbers. This fixes some problems with character sets where it is
convenient to input a string as a binary value. With this change, you
should use CAST() if you want to compare binary values numerically as
integers (...) If you do not use CAST(), a lexical string comparison is
made instead
- Before MySQL 4.1.13, conversion of DATETIME values to numeric form by
adding zero produced a result in YYYYMMDDHHMMSS format. The result of
DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format.
- In MySQL 4.1.12, the behavior of LOAD DATA INFILE and SELECT ... INTO
OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY
values both are empty. Formerly, a column was read or written the
display width of the column. For example, INT(4) was read or written
using a field with a width of 4. Now columns are read and written using
a field width wide enough to hold all values in the field. However, data
files written before this change was made might not be reloaded
correctly with LOAD DATA INFILE for MySQL 4.1.12 and up.
- Before MySQL 4.1.1, the statement parser was less strict and its
string-to-date conversion would ignore everything up to the first digit.
As a result, invalid statements such as the following were accepted:
INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');
As of MySQL 4.1.1, the parser is stricter and treats the string as an
invalid date, so the preceding statement results in a warning.
- In MySQL 4.1.2, the Type column in the output from SHOW TABLE STATUS was
renamed to Engine. This affects applications that identify output
columns by name rather than by position.
- The syntax for multiple-table DELETE statements that use table aliases
changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true
table name to refer to any table from which rows should be deleted (...)
In MySQL 4.1, you must use the alias
- Some keywords are reserved in MySQL 4.1 that were not reserved in MySQL
4.0
- For functions that produce a DATE, DATETIME, or TIME value, the result
returned to the client is fixed up to have a temporal type. For example,
in MySQL 4.1, you obtain the following:
mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01 00:00:00'
In MySQL 4.0, the result of the stement is different:
mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01'
- DEFAULT values no longer can be specified for AUTO_INCREMENT columns.
(In 4.0, a DEFAULT value is silently ignored; in 4.1, an error occurs.)
- LIMIT no longer accepts negative arguments. Use some large number
(maximum 18446744073709551615) instead of -1.
- SERIALIZE is no longer a valid mode value for the sql_mode variable. You
should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead.
Will this put my SMF 1.1 RC3 site in trouble ?? Will my forum still work after the upgrade ??
Anyone have any experience on this matter ??
As far as I know, you should experience no trouble (I use MySQL 4.1 and it works just fine) ;)
Quote from: Daniel15 on January 09, 2007, 06:11:59 AM
As far as I know, you should experience no trouble (I use MySQL 4.1 and it works just fine) ;)
Thanks alot.. that really calms me down.. still i will be taking a complete backup of my site the day before the upgrade.. you never know :p
SMF is fully compatible with MySQL 4.1 ;)
It might be a good idea to upgrade to SMF 1.1.1 which contains many bugfixes ;)
Im glad to hear this. It gives me hope that my site will survive the upgrade. :)
About me upgrading to smf final - out of the question.. i have like 25 mods and tp installed on a heavy modified theme. My forum is pretty stable now and i think upgrading will give me to big a headace. :(
I dont see any big benefits in upgrading - but i see many problems with all my mods. And thats the honest thruth :)