Error when moving from local host to live server

Question

I am getting the following error after deploying a website on server.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of /httpdocs/includes/lock.inc).

Basically, I created a Drupal website on local server. After finishing it, I generated the database script and ran it on the live server using phpMyAdmin. Then, I uploaded the files on server and changed the settings.php file for the database credentials.

Once I tried to access the website, I got that error.

Updating phpMyAdmin would fix the issue, but I am running a dedicated server and I don't want to update phpMyAdmin. I am looking for a work-around.

Answer 1

When creating database exports, the tables are usually locked to ensure that there are no attempts to access or change data within the tables while they are being exported and imported. Those locks will usually be released at the end of the import process. That obviously did not happen for your database.

Most likely your database import in phpmyadmin timed out and did not complete the import process. Check the tables available in phpmyadmin and see if there are the same number of tables as on your local server. If the data appears to be properly intact on the production server, then you will need to manually unlock the tables.

Answer 2

The best way I've found to export a local Drupal database and reimport it into a live database is to use Drush on the local server by entering the following code in the root of the installation you want to export.

drush sql-dump --gzip --result-file=pathto/yourdb.sql

I suggest making that path in the code above relative to the root of your Drupal install. Upload all the files to your server, then in the root of the Drupal install on the server use MYSQL to import the database.

gzip -d yourdb.sql.gz
mysql -h "your host" -u "username" -p "db name" < pathto/yourdb.sql

make sure to remove the quotes in the code above, and also you can remove the "-h your host" section if your mysql is hosted on localhost.

After that just make sure your settings.php file has the right configuration and you should be set. I've found using MYSQL to import databases is generally more reliable than phpMyAdmin, especially when DB files become large. Let me know if that works for you.