weekapaug
asked on
MySQL Migration Issue
Hi,
I was using this script to transfer a database to a new server.
It did work fine when moving between 2 older versions of MySQL, but when I tried to move to a newer version of MySQL it bugs out.
I am able to use the database migration function within the workbench to move it as well so I know there must be a way to make to make it compatible but when I do a dump on the server and try to import into new one with my own code it errors out.
Heres what I'm doing that works to move from same mysql versions, but is there something I should be doing to move to from server with with older mysql to newer mysql version?
SELECT *
From oldtable
INTO OUTFILE 'oldserver.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
I then am able to FTP from new server into old server and grab file and then use this code.
LOAD DATA INFILE 'oldserver.csv'
INTO TABLE newserver
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
When I try to run the whole thing it run an error saying its trying to insert invalid info into a field such as text into an INT field or exceeds the varchar limit, presumably because the import isn't lining up correctly.
Any ideas what I can do to correctly export older mysql info into a newer version the way the database migration tool in the workbench works?
I was using this script to transfer a database to a new server.
It did work fine when moving between 2 older versions of MySQL, but when I tried to move to a newer version of MySQL it bugs out.
I am able to use the database migration function within the workbench to move it as well so I know there must be a way to make to make it compatible but when I do a dump on the server and try to import into new one with my own code it errors out.
Heres what I'm doing that works to move from same mysql versions, but is there something I should be doing to move to from server with with older mysql to newer mysql version?
SELECT *
From oldtable
INTO OUTFILE 'oldserver.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
I then am able to FTP from new server into old server and grab file and then use this code.
LOAD DATA INFILE 'oldserver.csv'
INTO TABLE newserver
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
When I try to run the whole thing it run an error saying its trying to insert invalid info into a field such as text into an INT field or exceeds the varchar limit, presumably because the import isn't lining up correctly.
Any ideas what I can do to correctly export older mysql info into a newer version the way the database migration tool in the workbench works?
Use mysqldump, then load the file using mysql command line.
Please post error (might be like 10 lines) if it occurs.
Please post error (might be like 10 lines) if it occurs.
The suggestion from gheist is the actual answer - use mysqldump if at all possible.
If you don't have shell access on the servers, contact the host for assistance. They should be able to provide a mysqldump of your current database, or import one you give them.
If you have a really crappy host who refuses to assist, you can still get the OUTFILE/INFILE method to work, but it's a bit of manual labor. Post back when you have exhausted your possibilities with mysqldump.
If you don't have shell access on the servers, contact the host for assistance. They should be able to provide a mysqldump of your current database, or import one you give them.
If you have a really crappy host who refuses to assist, you can still get the OUTFILE/INFILE method to work, but it's a bit of manual labor. Post back when you have exhausted your possibilities with mysqldump.
Squirrel dbcopy is good too.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am trying to do this via a script, and not wanting to manually do this every time I want to synch them both.
So I am limiting myself to queries that accomplish the same function. I actually got it to work at one point by using the "ignore" in the function and it just skipped the records with non-valid characters.
Then I upgraded to newest version of MySQL and now it wont even work with ignore any longer. It just says, invalid utf8 character string. I have set the target database, table, and column to utf8mb4 from latin1 but it does nothing. The error still persists.
So I am limiting myself to queries that accomplish the same function. I actually got it to work at one point by using the "ignore" in the function and it just skipped the records with non-valid characters.
Then I upgraded to newest version of MySQL and now it wont even work with ignore any longer. It just says, invalid utf8 character string. I have set the target database, table, and column to utf8mb4 from latin1 but it does nothing. The error still persists.
ASKER
adding character set latin1 to the load data infile query fixed the issue on newer version of mysql
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for weekapaug's comment #a41158224
Assisted answer: 125 points for gheist's comment #a41141204
Assisted answer: 125 points for routinet's comment #a41143587
Assisted answer: 125 points for gheist's comment #a41144427
Assisted answer: 125 points for TomasHelgi's comment #a41152024
for the following reason:
needed it in mysql query format, not wanting to physically get on server to access mysql command line prompt
Accepted answer: 0 points for weekapaug's comment #a41158224
Assisted answer: 125 points for gheist's comment #a41141204
Assisted answer: 125 points for routinet's comment #a41143587
Assisted answer: 125 points for gheist's comment #a41144427
Assisted answer: 125 points for TomasHelgi's comment #a41152024
for the following reason:
needed it in mysql query format, not wanting to physically get on server to access mysql command line prompt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Steve,
I am writing this in coldfusion, which probably gives away i'm a novice.
I do not know how to incorporate mysldump into a script and I assumed incorrectly that it was a command prompt only function.
As of now, I got it working the "unpreferred" way, but would love to see an example of how I would do this more correctly.
I am on a dedicated server and have all the permissions to do what I need, but as of now I was doing load data outfile mysql query to a .csv, then using cfftp to grab it onto the local machine, then doing load data infile. All of which was incorporated in a <cfquery>.
Is there a mysql query to export a mysql dump on ONLY certain tables? And a subsequent command to import a mysql dump that would work as a mysql query within the <cfquery> rather than outfile into .csv and load data infile?
I am writing this in coldfusion, which probably gives away i'm a novice.
I do not know how to incorporate mysldump into a script and I assumed incorrectly that it was a command prompt only function.
As of now, I got it working the "unpreferred" way, but would love to see an example of how I would do this more correctly.
I am on a dedicated server and have all the permissions to do what I need, but as of now I was doing load data outfile mysql query to a .csv, then using cfftp to grab it onto the local machine, then doing load data infile. All of which was incorporated in a <cfquery>.
Is there a mysql query to export a mysql dump on ONLY certain tables? And a subsequent command to import a mysql dump that would work as a mysql query within the <cfquery> rather than outfile into .csv and load data infile?
ASKER
It has that accent mark over the e, and while I dont have the issue when inserting into older mysql database, its confusing the new MYSQL server. I know this has something to do with encoding but do not know what I need to do to write it properly.