Merge two mySQL databases.

[FreeStream]

Hi,

I have two mySQL databases.

They are essentially, exactly the same.

However one is from March this year and the other is from today.

I.e. the march one is a backup that was taken in March.

Now there are entries (Exactly speaking iPhone SMS messages) that were deleted by mistake.

Is there a way of merging the messages back into the newer database?

There is a date and time column of data so getting them back into the correct 'slot' shouldn't be too hard.

Ideas?

Thanks!

 

[visibleman]

I'd merge the tables into temporary table(s), check for primary key clashes and then put that the temp table(s) into production. Obviously take a full DB backup prior to the modifications.

 

[Linkex]

Assuming your using some kind of scripting language to do this rather than direct on the database, heres how I would do it:

Select all data from the old database
Loop through a result at a time, running a select on the new database
Anything that dosen't return a result is placed into an array
This creates an array of what is "missing" from your current DB

Once the script finishes, run the search on the new database again looking for the unique IDs, and adding a "clash/No clash" into the array depending on what it finds.
If it all comes back without clashes, or you don't need the IDs to be in the right order, update the script to add the items in the array to the database specifying the IDs and run the script again. If there are clashes and you need the ids to be in the right place , you will need to decide if you want to rebuild your DB to get the IDs in the right place.

Depending on the size of the database, this could take a very long time though. Oh, and what visibleman - do a backup before you do anything to the DB.

 

[visibleman]

Bit OTT when you could just use a simple SQL Query with a 'ON DUPLICATE'. To be honest, if you're using PhpMyAdmin it'll spit out errors when you do a merge.

 

[Linkex]

Doesn't 'ON DUPLICATE' bump the old ID up to the next unused ID? I agree that if the IDs are not important that this is by far the better method, but I'm guessing that they are if they are used to store messaging.

 

[FreeStream]

Can I actually upload my iPhone's SMS database to PHPMyAdmin and do that?

I don't think the ID's are important as when you delete an SMS all the messages get bumped down one and they retain their own ID.

I.e. If message 12111 (row 12111) is deleted then the messages jump from 12110 (row 12110) to 12112 (row 12111).

 

[visibleman]

Can I actually upload my iPhone's SMS database to PHPMyAdmin and do that?

I don't think the ID's are important as when you delete an SMS all the messages get bumped down one and they retain their own ID.

I.e. If message 12111 (row 12111) is deleted then the messages jump from 12110 (row 12110) to 12112 (row 12111).

If my memory serves me, the iPhone SMS database is SQLite so you'll need to do some string replacements (IIRC double quotes become grave accents for MySql) on the DB dump prior to importing it into MySQL. No doubt there are SQLite to MySQL converters if you do a google.

Sounds like the primary key, auto incremental ID in this case, is just used for referencing but you'll likely have primary key clashes when you merge the two tables/dbs.
As said, the simplest solution is to merge the data from both old and new tables (perhaps by date/time order), omitting the auto_inc ID field, into a new 'temp' table.

So for example both old/new tables has the following structure - id, date, phone_num, msg:

CREATE TABLE `TEMP_SMS` (`id` INT NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `phone_num` INT NOT NULL, `msg` TEXT NOT NULL, PRIMARY KEY (`id`))

INSERT INTO `TEMP_SMS` (`date`, `phone_num`, `msg`)
SELECT `date`, `phone_num`, `msg`
FROM `NEW_SMS_TABLE` ORDER BY `date`
SELECT `date`, `phone_num`, `msg`
FROM `OLD_SMS_TABLE` ORDER BY `date`

(this will be quite slow if you've got a lot of data, ideally you would use unions but from memory it gets a bit messy using unions and order by's).