MySQLHell

I’ve just had a nightmare of a development problem. In a nut-shell, I’ve had to import data, (specifically content articles), from one server to another.

This is normally not a problem, but in this instance the source database had each article’s content split into multiple ‘page’ rows. Also, the source database was developed and hosted by another company. They refused to install PHPMyAdmin, (for “security” reasons), and would not give me FTP access. All I had to work with was a terrible database admin system which they’d cobbled together.

My plan was to dump the data into a new database on our server, manipulate it there, then move the shiny clean data over to the live system. Things did not go to plan…

First problem: The database admin system timed out before a dump could be achieved. Also, the DBA would not allow me to dump the data in stages, so I had to create multiple tables :

INSERT INTO `article_export_2` (id, web_section, sub_heading, subject, headline, publishing_date, mag_issue_num, author_name, about_author, published, pub_date)
(SELECT id, web_section, sub_heading, subject, headline, publishing_date, mag_issue_num, author_name,
about_author, published, pub_date FROM `article`
WHERE `published` = 1
AND `web_section` = 4
LIMIT 1100,1100)
INSERT INTO `articlebody_export_1` (id, aid, page, bodytext)
(SELECT b.* FROM `articlebody` AS b
LEFT JOIN `article` AS a ON a.`id` = b.`aid`
WHERE a.`published` = 1
AND a.`web_section` = 4
LIMIT 0,695)
...
INSERT INTO `articlebody_export_5` (id, aid, page, bodytext)
(SELECT b.* FROM `articlebody` AS b
LEFT JOIN `article` AS a ON a.`id` = b.`aid`
WHERE a.`published` = 1
AND a.`web_section` = 4
LIMIT 2780,695)

This allowed me to dump and import the data in stages… in a round-about way. First, I had to dump the paged content data in a format where I could manipulate the queries to update rather than insert:

SELECT CONCAT( `bodytext` , '\' WHERE `article` ID = ', aid ) 
FROM `articlebody`
ORDER BY `aid` ASC, `page` ASC

Then, once I’d run about 13,829,875 search-replace commands on the dump, I was left with a load of queries like this:

UPDATE `article` SET `body_full` = CONCAT(`body_full`, ' ', '[body text here]') WHERE `id` = 30;

There was a hell of a lot of trial-and-error with this one. Very frustrating, considering each import/export query took a matter of minutes to perform. If the old developers would’ve been kind enough to just install PHPMyAdmin, (I think they were bitter), I wouldn’t now have a headache…

Share Button