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?
weekapaugAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

weekapaugAuthor Commented:
I think I have the problem narrowed down to the word "decor" causing the problem.

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.
gheistCommented:
Use mysqldump, then load the file using mysql command line.
Please post error (might be like 10 lines) if it occurs.
Steve BinkCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gheistCommented:
Squirrel dbcopy is good too.
Tomas Helgi JohannssonCommented:
Hi!

Use mysqldump and do it in two steps.
Dump first the structure
mysqldump --no-data -u username -p db_name > db_structure.sql
then the data
mysqldump --no-create-db --no-create-info -u username -p db_name > db_data.sql
Then import the structure
mysql -u username -p passw < db_structure.sql
then issue loading of the data
mysql -u username -p passw < db_data.sql

This will tell you if the error is structure-based or data-based.

Also if you are migrating the whole mysql database (mysql schemas and everything) then you will need to use the
proper migration paths.
mysqldump -u username -p > all_db_stuff.sql
move the dump to the other server with the new mysql binaries and import
mysql -u username -p passw < all_db_stuff.sql
and which is important run
mysq_upgrade
then restart the database.

http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Deployment_Guide/ch-Migrating_from_MySQL_5.0_to_MySQL_5.5.html

Also look for changes affecting upgrades to the desired version
https://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
https://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html



Regards,
      Tomas Helgi
weekapaugAuthor Commented:
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.
weekapaugAuthor Commented:
adding character set latin1 to the load data infile query fixed the issue on newer version of mysql
weekapaugAuthor Commented:
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
Steve BinkCommented:
Just FYI: you can use mysqldump in a script to automate these processes.  Nothing in your description prohibits using mysqldump, and it is, by far, the preferred way to do it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
weekapaugAuthor Commented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
E-Commerce

From novice to tech pro — start learning today.