My host is about to upgrade MySQL to 4.1 | Will it make trouble for my site ?

[Tanks]

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

Code Select Expand

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 ??

[Daniel15]

As far as I know, you should experience no trouble (I use MySQL 4.1 and it works just fine)

[Tanks]

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

[H]

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

[Tanks]

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