I NEED A MySQL MASTER... FAST!!!!

[XxMaCaBrExX]

Ok, I'm importing a SQL database into a MySQL database, and I'm only having one small problem. In my SQL database, there is an "ID" column that is auto-incrementing. I have dumped the SQL database to a textfile-script, and now I just need to edit the script to make it auto-increment the value of the "ID" column. Any experts that know what the hell I'm talking about?

[ 05 June 2001: Message edited by: XxMaCaBrExX ]

[r00t619]

Is this what you are talking about:

CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
name blah blah,
title blah,
)

If not please be more specific

[Strike]

Okay, in your database (a table, really), you have a column that is auto-incrementing named "ID". You want to edit the dumpfile so that it automatically increments the "ID" column for when you import it. That about right?

This makes no sense to me. The column is autoincrementing already. So, no I don't know what the hell you are talking about though I don't pretend to be an expert.

[XxMaCaBrExX]

Originally posted by r00t619:
<STRONG>Is this what you are talking about:

CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
name blah blah,
title blah,
)

If not please be more specific</STRONG>

Thanks, that did it. Sorry for the lack of information, but I'm still kind of a newbie to MySQL. :)

[r00t619]

Just glad I could help out and guess what your problem was :D

[asp]

Glad things worked out, only one thing stuck in my mind as a possible warning...

Don't know what database you dumped from, but if that ID column was referenced in any other table as an identifier back to your imported table, there's always the possibility of it being assigned a new number (if your original table had rows removed or if the original table was not dumped ORDER BY ID). Although both instances would have labels from 'careless' to 'really-dumb' it's something to keep in mind to preserve your database intregrity.

Just something to keep in mind if you notice problems and don't know what's wrong...

Sam

Clarification edit: I'm speaking as a DBA, since I'm a relative noob to linux/mysql, altho I have used both in my professional career I haven't had to do much troubleshooting.

Whenever you potentially change a field in a table that is used in another table in the same database you risk corrupting the integrity of your database. This is because the field in the secondary table no longer has a link to the table that was re-organized. I hope this clarifies what I was trying to say, but I don't have sufficient knowledge of the systems you used (one database wasn't mentioned by name) to offer anything else than the standard 'watch out for this' warning...

[ 10 June 2001: Message edited by: asp ]

[bolty]

Mysql allows for the creation of a column as just int. Then import the values into the table and after that change the column to auto increment.

MySQL continues the auto increment from the highest number in the column+1.

e.g.

create table tblTemp (id int);

insert into tblTemp (id) values (1),(2),(3),(4),(5);

alter table tblTemp modify column id int auto_increment, ADD primary key(id);

This is the way that I use.

:D I love being a DBA :D