Today, I was helping to move a CMS Made Simple site from one webhost to another. It seemed easy enough. I was provided a database dump and copy of the files. It should have been just a matter of importing the database dump with phpmyadmin, scping the files to the new host, editing the config.php, clearing the cache, and refreshing the browser. Right, right? Of course not.
The export of the original tables from MySQL were pretty standard: drop the table if it exists, recreate it, lock it, populate it, unlock it:
CREATE TABLE `cms_additional_htmlblob_users` (
`additional_htmlblob_users_id` INT(11) NOT NULL DEFAULT '0',
`user_id` INT(11) DEFAULT NULL,
`htmlblob_id` INT(11) DEFAULT NULL,
PRIMARY KEY (`additional_htmlblob_users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `cms_additional_htmlblob_users`
--
LOCK TABLES `cms_additional_htmlblob_users` WRITE;
/*!40000 ALTER TABLE `cms_additional_htmlblob_users` DISABLE KEYS */;
INSERT INTO `cms_additional_htmlblob_users` (`additional_htmlblob_users_id`, `user_id`, `htmlblob_id`) VALUES (6,1,5),(15,1,1);
/*!40000 ALTER TABLE `cms_additional_htmlblob_users` ENABLE KEYS */;
UNLOCK TABLES;
At the new webhost, in phpmyadmin, when importing the SQL, I kept getting the following error:
MySQL said:
#1044 - Access denied FOR USER 'foo'@'%' TO DATABASE 'bar'
How can I not have the permission to lock tables? So I wanted to figure out if this lock really was the problem. Opening up the original SQL dump in Notepad++, I wanted to find the LOCK TABLES lines and remove them, and the corresponding UNLOCK TABLES. Now, there are way too many tables, so removing the LOCK TABLES by hand is a serious PITA.
Good thing Notepad++ supports regular expression in search. Use this to find all the LOCK TABLES:
and replace with nothing. You can find UNLOCK TABLES; directly and remove them.
Turns out after doing that, the SQL imported just fine. So that meant the stupid webhost didn’t allow LOCK TABLES access for the user. Why would you not let users lock their own tables? Plain retarded. There was nothing really wrong with the SQL, just a retarded host.
Thankfully the client got wise and moved to a better host where we had no such problems.
