There seems to be some issues we are experiencing with this new lower_case_table_names variable introduced in 4.0.17 and modified in 4.0.18. So much so that I can't upgrade to 4.0.18 at all.
Here is the startup log from a 4.0.18 mysqld: 040227 02:00:22 mysqld started 040227 2:00:22 Warning: Setting lower_case_table_names=2 because file system for /mysqldata/ is case insensitive InnoDB: Error: tablespace size stored in header is 2438400 pages, but InnoDB: the sum of data file sizes is 2560000 pages 040227 2:00:23 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 040227 2:00:23 InnoDB error: Cannot find table jive_ebay_us/jiveuser from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. ERROR: 1016 Can't open file: 'jiveuser.InnoDB'. (errno: 1) 040227 2:00:23 Slave: Error 'Can't open file: 'jiveuser.InnoDB'. (errno:1)' on query 'UPDATE jiveUser SET some user data WHERE some qualifer'. Default database: 'jive_ebay_us', Error_code: 1016 040227 2:00:23 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binary-log.008' position 781142831 So, let's walk through these... For starters mysqld decides on it's own it will set lower_case_table_names=2 on it's own because "file system for /mysqldata/ is case insensitive"... Yes, that's true, the file system is case insensitive, it's Mac OSX 10.3. However because we have had issues with this in the 4.0.17 introduction of this "feature" we have a statement in our my.cnf file which specifically says "set-variable = lower_case_table_names=0". Despite us manually telling MySQL that we want it to be case sensitive in all cases it ignores our setting and chooses it's own. Now that it has ignored our request to be case sensitive all of the time for all of the requests we are experiencing problems with InnoDB not recognizing we have tables that we have. Specifically in the error cited above we have a table called jiveUser, that is evident in the way the request is made to to the database to "set jiveUser"... Yet InnoDB is failing because it can't find jiveuser. So it seems that MySQL has behaved as promised with a lower_case_table_names=2 value and converted the request to lower case... InnoDB however can't find a lower case version of this table. Of course all our table names have upper and lower case characters and this particular entry happened to score the jackpot because it was the first query too come along after we booted 4.0.18. It happens on all our databases, on all our tables. I don't know what we are doing wrong here... Any value of lower_case_table_names results in 4.0.18 not running (OK, it runs, but our applications don't run with it), so we can not upgrade to 4.0.18. MySQL seems to be ignoring our my.cnf startup value for lower_case_table_names and InnoDB seems to be unable to find our tables if MySQL changes the case to lower case. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
