Ran into an odd issue today with a mysql innodb table. I created a unique index on a few fields and inserted records into it where one of the field values was NULL. I ran the same insert query again with the “on duplicate update” clause and it performed another insert rather than updating as it should have. Only the B2B storage engine for mysql will treat NULL as a unique index value. Changing the null to an empty string solved the problem, but was not ideal.
delete FROM table_name WHERE column_name REGEXP ‘[^0-9]’;
You might be an unlucky soul me where you need to maintain a bunch of old and poorly written code. You’ll also probably get a bunch of weird errors if you’re running recent versions of PHP and mysql locally such as “invalid integer value xxx” when trying to modify a record. This occurs because the application is probably trying to insert a string into an INT field in mysql. Anyway, since the number of bugs are too numerous to fix in this application, the easiest solution is to remove the strict settings from your mysql.
To view the current sql mode setting:
To remove all settings (and allow string to get inserted into INT cols)
SET global sql_mode="";
What you do, don’t go to the control panel -> services and stop the service. I did this and was unable to log back into mysql, even after a reboot of my machine. The way to do it is via the command line and enter “net stop MySQL_ZendServer51” and “net start MySQL_ZendServer51” .
Using Mac OS X on the command line, log into mysql and select your database. Then run the following for a tab delimited file located on your desktop:
load data LOCAL infile ‘/Users/YOURNAME/Desktop/standards.txt’ into table TABLE_NAME fields terminated by “\t” lines terminated by “\r”