Category Archives : MySQL

Incorrect integer value – How to change mysql sql_mode

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:

SELECT @@sql_mode;

To remove all settings (and allow string to get inserted into INT cols)

SET global sql_mode="";

Restart Zend Server CE on Windows

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″ .

How to import a delimited file with mysql

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”

How to download and import a large mysql database locally

Download the remote file
scp my_username_on_remote_server@remote_server_ip_address:fullPathToFileOnRemoteServer pathToPutFileOnLocalMachine

If the file was zipped, then we’ll need to unzip it locally
gunzip -f pathToFileOnLocalMachine

Then load the data into the database
mysql -u root -p -h localhost databaseName < pathToUnzippedFileOnLocalMachine

How to stop a mysql process

Get into an infinite loop or query that’s taking longer than your patience will allow? Using mysql’s “kill” command will be helpful in this case. First log into mysql and view the process list “select * from information_schema.processlist;” . View the list of processes and identify the process id number of the process you want to stop, then run “kill (process id);”. Be careful when stopping queries other than selects because you may run into some data corruption.