Tag Archives : MySQL


How To Turn off MySQL Strict Mode

By default, MySQL is enabled with strict_mode ON, which will most likely cause problems with older PHP applications written for MySQL 4.   For example, strict mode will return an error on insert statements if an empty string is provided where an integer is expected.

You may disable strict mode in one of two ways:

Open your “my.ini” file within the MySQL installation directory, and look for the text “sql-mode”.
Find:

# Set the SQL mode to strict
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

and change to

# Set the SQL mode to strict
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

or you can run the following in phpMyAdmin

SET @@global.sql_mode= ”;


How to copy a mysql database

While there is no “copy database” function for mysql, there is a way to automate the process better than saving a bunch of SQL to a file, then uploading the file. Mysqldump offers a way to copy a database to another existing database on the same server or a remote server. The official documentation is here http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html , although their syntax doesn’t include the required username and password login syntax (if your database server requires a login, I hope).

Use the following on linux –
mysqldump nameofdatabase1 -u root -p | mysql –host=localhost -u root -p -C nameofdatabase2;

You’ll be prompted for the password and you’ll need to enter it twice. If your destination database is remote, then replace localhost with the IP address.


MySQL import via command line on windows

Today I needed to import a 4.5GB file of SQL into a MySQL database. Good ole phpmyadmin won’t handle anything close to that large, I it had to be command line. It took me a bit to find the proper syntax, so hopefully this will rank well and provide a quick reference not only to myself but to everybody else.

Save your text file of SQL somewhere on your computer and open a command window (start > run and type “cmd” and hit enter). Type the following command

1. mysql -u root -p (then hit enter. “root” is the database username you’re using)
2. It’ll then prompt you to enter the password for that user. Enter it and hit enter.
3. type “USE databaseName” and hit enter. (“databaseName” must be changed to whatever your database name is).
4. the final step is to use the “source” command and provide the location of your text file on your hard drive like source C:\dump.sql


mysql table is read only mode error 1

I recently restored a mysql database from a backup and come to find it wouldn’t allow any modifications.  The problem was the database belonged to user “root”, which is what I used with SSH to restore the database.  The user needs to be mysql.

The fix

Open an SSH connection and navigate to /var/lib/mysql/ and run the following command to recursively set all database owners to mysql -

chown -R mysql:mysql *