phpMyAdmin/Mysql

[osgood_]

Apparently there is an 'export'  bug in some versions of mySql when allocating a database column to be 'auto increment'

When  trying to import the table information locally OR back to the remote after exporting it using phpMyAdmin an error ocurrs and no table is created, just an error message:

MySQL said: Documentation

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Just wondering if anyone else has encounterd this error and how they were exporting the data in such circumstances.

I've been told by the host that I can use 'mysqldump':

mysqldump -u<database_username> -p<database_password> database_name > file_name.sql

How do you go about using that in a 'remote' situation. If I insert the above in the mysql tab in phpMyAdmin and hit go it just flags up an error message.

I can alter the .sql file after it has been exported BUT surely there must be a way of exporting the data correctly without having to doing that?

Thanks

Os

[Nancy OShea]

Yes, I have seen this.

If I EMPTY content from tables first, importing SQL works for me. 

Give it a try .

Nancy O.

[osgood_]

Nancy O. wrote:

Yes, I have seen this.

If I EMPTY content from tables first, importing SQL works for me.

Give it a try .

Nancy O.

I'm not following?

I'm exporting the table information but when I import the table information back the error occurs.  I don't have any tables to empty, I'm trying to create a new one based on the information saved in the .sql file.

I can open the .sql file and correct what needs to be corrected then import the information and it works fine but I was just wondering if there was any other way around as it seems a bit awkard to have to do keep doing that.

The real annoyance is why wasnt a bug like that spotted before that particular version of mysql was released. I mean thats just crazy. Its like rolling out a new car with only 3 wheels when there is meant to be 4 and spotting the 4th wheel is missing when driving along, totally insane. I can't even begin to get my head around it to be honest.

Os

[Guest]

Can you ssh into your server? You should be able to run mysqldump command from the server through the shell using ssh. If your host suggested that you use mysqldump then mysql client might already be installed on your server. If mysql client isn't installed on the server then when you try to run mysqldump command from the shell you'll get an error message like: mysqldump command not found If that happens then you should install the mysql client on the server first by running.

sudo apt-get install mysql-client-5.1

best,

Shocker

[osgood_]

the_shocker wrote:

Can you ssh into your server? You should be able to run mysqldump command from the server through the shell using ssh. If your host suggested that you use mysqldump then mysql client might already be installed on your server. If mysql client isn't installed on the server then when you try to run mysqldump command from the shell you'll get an error message like: mysqldump command not found If that happens then you should install the mysql client on the server first by running.

sudo apt-get install mysql-client-5.1

best,

Shocker

Hi Shocker,

Thanks for the information. I think the easiest way, for me, might just be to amend the exported .sql file, that seems to work. I'm just a bit miffed that a bug of that magnitude escaped unnoticed.

To be honest I'd never encountered it before although it seems to have been around since 2013. Maybe the host should have updated their mysql version by now but maybe that's too problematical for them <shrug>

I'll look into the 'dump' method as you describe for peace of mind, it's something that I should probably know anyhow.

Cheers

Os

[Rob Hecker2]

I didn't respond to this thread sooner because I was interested in seeing if someone else would have a better solution.

Without suggesting that it's the best way, I generally drop the previous database, create a new database of the same name, and import the SQL dump file to the new empty database. Of course this process can be automated to make it a little quicker.

The one messy aspect of the above method is that stored procedures are not carried over, but I have scripts stored in MySQL workbench that restore those.

My workflow is to export from the remote and import to the local when it is a whole database. That's because there are financial transactions and website content changes happening on the remote database.

In a situation where I create a new table locally, there are never issues importing it to the remote database since it is new to the remote.

MySQL workbench is a nice (free) tool. Very different from PHPmySQL. Provides more info about the data and more tools for working with it, including access to remote databases. I like being able to store and quickly run stored scripts. I could not even imagine working with stored procedures with PHPmyAdmin alone (well, it can be done).

[osgood_]

Rob Hecker2 wrote:

My workflow is to export from the remote and import to the local when it is a whole database. That's because there are financial transactions and website content changes happening on the remote database.

Good point. I think I may have usually done that in the past and encountered no isuues with importing locally but I can't really remember, I'll try exporting the remote database as a whole. Strange, I've been working with mysql for quite a few years and this exporting one table 'bug' has only just come to light, maybe I've just never exported one table on it's own before from the remote server.

Rob Hecker2 wrote:

In a situation where I create a new table locally, there are never issues importing it to the remote database since it is new to the remote.

That's what I find too. Local to remote is always good for me.

Rob Hecker2 wrote:

MySQL workbench is a nice (free) tool. Very different from PHPmySQL. Provides more info about the data and more tools for working with it, including access to remote databases. I like being able to store and quickly run stored scripts. I could not even imagine working with stored procedures with PHPmyAdmin alone (well, it can be done).

I'll look into workbench.

Thanks Rob.