Database transfer issues

Worker Joined: Mar 30, 2006 Posts: 111

I'm moving to a new server with a huge database to transfer. I spent a few hours cutting and pasting the CREATE and INSERT data through phpmyadmin, and all seemed to be going well until I received a couple of errors;

When I got to nuke_nsnst_tracked_ips I received -
#1170 - BLOB/TEXT column 'user_agent' used in key specification without a key length

I also received an error for nuke_stdemail -
#1170 - BLOB/TEXT column 'fumessage' used in key specification without a key length

I'm not savvy on this stuff and I'm hoping someone can give me the "for dummies" solution. Reading the documentation was more confusing than helpful...*blush*

Thanks

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

You are not using compatible versions of MySQL. What you need to do is use the phpMyAdmin export option SQL compatibility mode to allow the import. So, if your old database is MySQL 4.x, you will want to select MYSQL40. That should allow the old schema to be imported.

Posted: Tue Apr 22, 2008 2:14 pm

Thanks Raven.

I tried that option before and apparently the file was too large to import, hence the cut and paste method through SQL queries. Is there something else I can try to bring just the unacceptable portions up to speed? I'll edit manually if you, or someone, is willing to show me precisely what to do...so I don't blow-up anything.

Posted: Tue Apr 22, 2008 3:05 pm

I'm not referring to the size of the import. I'm just saying that the engines aren't compatible so you need to export the tables in a different format. You may still need to do the cut and paste but you need to be using compatible engines so you need to export and import with the same engine regardless of how you import. BTW, have you tried using BigDump?

Posted: Tue Apr 22, 2008 11:50 pm

I'll try the export feature first.

BigDump sounds like the ticket...I couldn't find in your downloads, I'm guessing I'll have to google it?

Thanks again.

Worker Joined: Aug 26, 2007 Posts: 236

MySQLDumper is also an alternative for huge backups and restores:
http://www.mysqldumper.de/en/

Posted: Wed Apr 23, 2008 9:34 am

Thanks slackervaara.

Okay, just checked my Export function and I must have an older version of cpanel because the only options I have are radio buttons asking for which type of file e.g. windows, SQL, PDF... Nothing about "version". Am I stuck, or just missing something?

Posted: Wed Apr 23, 2008 9:48 am

Found the missing piece...though it doesn't help me. Under Options on my newer server the Export offers Compatibility Mode, while my older one does not. Is my best option, now, to use one of the Dump scripts, and if so, does it automatically adjust for versions?

Posted: Wed Apr 23, 2008 9:53 am

Do you have phpMyAdmin?
On its export page to the right is Alternative and there you can choose SQL compatibility.
If you don't have phpMyAdmin installed you can do that yourself in your web space. I have done that and it works. There could, however, be time out issues stopping a big backup.

Posted: Wed Apr 23, 2008 10:39 am

I'm sorry, I wrong to keep referring to cpanel...I should have said phpMyAdmin. The version on the older server does not have the compatibility option, while the new one does.

Posted: Wed Apr 23, 2008 10:54 am

You can install the latest phpMyAdmin on your older server in your web space and then you will get this option for the old server too.

Posted: Wed Apr 23, 2008 11:13 am

after looking at both versions of phpmyadmin, both are version 2.11.4, but each have a different "Server version", older; 4.0.27-standard, new; 4.1.22-standard. When I look at the myadmin site it only offers 2.11.5 (and older), but I don't see anything referencing server versions.

Posted: Wed Apr 23, 2008 11:20 am

phpMyAdmin can differ although the versions are identical by different config files, but those are controlled by the web hotel. If you install it yourself you determine how it should be.

Posted: Thu Apr 24, 2008 10:09 am

Thanks, I've upgraded and it appears to have worked well - no errors! Now I have another question; Is there a way to test the site without redirecting my DNS pointer to the new server?

Nevermind...duh...it's just been so long since I've done it (http://IP/~user/)

Posted: Thu Apr 24, 2008 11:27 am

I have replicated the database name, user, and password. used the same config file as on the old server (dbhost = localhost), and when I access the site via IP I get the MySQL error temporary page. What should I be focusing on at this point to identify the problem?

Posted: Thu Apr 24, 2008 11:33 am

one other point that might be important, the response after import was "import has been successfully finished, 140502 queries executed" would you be satisfied that everything is in order without concern for integrity?

Posted: Thu Apr 24, 2008 11:49 am

Disregard the last two posts. It was a syntax error in the config file - *blush*

Posted: Thu Apr 24, 2008 1:28 pm

For future reference - one other option to the database compatibility issue - if both of your sites will allow access via ODBC, you could set up links within MSAccess and move data via queries directly from one database to the other.

Just a thought.

-sting

Posted: Thu Apr 24, 2008 2:35 pm

Sting, it is looking as though I might not have to go any further with this, however, I am always trying to learn as much as possible and would like to know if you are familiar with a site that would outline, in detail, the process you are referring to.

Posted: Thu Apr 24, 2008 3:08 pm

I am not aware of any one particular site that details how this is done - for me it started with a cheesy video of 'how to connect your AS/400 to Microsoft Office via ODBC" circa 1997.

From there the concepts are the same - the ODBC client for MySql allows you to connect to the database back end by linking tables - a F1 in Access or any database / front end utility can give you an idea on how to set up the ODBC link.

The really nice thing about doing it this way is when you are moving data from one system to another - for example during an ERP implementation we moved a ton of data from tables in a legacy AS/400 application to tables in an Informix database on a Unix machine.

Using ODBC I was able to view both systems in the same MS Access interface. From there it was just a matter of creating queries to retrieve the data from the 400, clean it up so that it matched the new table structure, and append to tables on the Unix box.

Saves a ton of time and effort from exporting, manipulating, importing, etc.

The drawback is that more and more websites are not allowing direct ODBC access to their back ends due to security concerns.

/end ramble

I would suggest reading up on MySql's ODBC client from their website to get more familiar with the process - especially if you are going to work with databases a lot.

-sting
is STILL using bloody MS Access to do this type of stuff: Latest arrangement: Re-writing an accounting reporting system from a Pervasive backend. . .

Can we say fun boys and girls? (please forgive the sarcasm)

Posted: Thu Apr 24, 2008 7:09 pm

Thanks Sting, I'll have a look at that since I do plan to dig much deeper into the db world. AND, thanks to all of you! the site is up and running flawlessly.

Posted: Thu Apr 24, 2008 11:12 pm

With all due respect to Sting Wink

I can see no reason whatsoever to got through all those hoops to transfer for MySQL to MySQL. I guess to sum it up, using Rasmus' quotation on PHP eval as a template ...

If ODBC and MSACCESS is the answer then you're almost certainly asking the wrong question:)!!

Posted: Fri Apr 25, 2008 8:47 am

Quote:

If ODBC and MSACCESS is the answer then you're almost certainly asking the wrong question:)!!

I couldn't agree more - for more than one reason. . .

I wouldn't do that for MySql to MySql. I usually only do it when the databases are different and there is no quick utility.

As for the all due respect part, that's debatable.

Wink

-sting

Posted: Fri Apr 25, 2008 10:40 am

Quote:

As for the all due respect part, that's debatable

That was for the benefit of the others killing me

BTW, I got your PM and will respond. Been kind of a busy week. The Mrs. had surgery on Wednesday and I was gone all day and am still playing catch-up Smile