Converting MyISAM to InnoDB

Hello again!

I am trying to convert my tables to InnoDB, and i am getting an error...

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

Now, I converted a table in my sandbox earlier this morning to do some
testing, and it worked fine... mind you, i did truncate the table first, but
i am not sure if that is relavent or not.

The table structure has a TONNE of fields, but to give you an idea... here
is what is starts out like:

CREATE TABLE `radacct_201002` (
`Year_Month` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`Radacct_Id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`Acct_Authentic` VARCHAR(32) DEFAULT '',
Etc etc........
PRIMARY KEY (`Year_Month`,`Radacct_Id`),
UNIQUE KEY `radacct_id` (`Radacct_Id`),
KEY (there are keys here.... not of any relevance that i can see)
) ENGINE=INNODB DEFAULT CHARSET=latin1

That is the table in the sandbox, and as i said, all i did was truncate it,
and change to innodb (there was prolly about 5-10 rows when i did it) and
there wasn't any issues. When i do it to the live database (i copied a
table of live data, so i can convert it and see what kind of times/loads i
get) i get the error...

I am in the midst of removing the combined unique primary key, to see if
that is the culperate or not, but if anyone has any ideas, i am eager to
listen :)

Steve.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Converting MyISAM to InnoDB

If you have a column defined as auto_increment, there must be a key on it. =
This is true both in myisam and innodb.

If you need further help, please show us the full structure of the real tab=
le you're operating on (not the one from your sandbox), the statement you r=
un, and the error message.

Regards,
Gavin Towey

-----Original Message-----
From: Steve Staples [mailto:sstaples@mnsi.net]
Sent: Monday, February 08, 2010 9:39 AM
To: mysql@lists.mysql.com
Subject: Converting MyISAM to InnoDB

Hello again!

I am trying to convert my tables to InnoDB, and i am getting an error...

Error: 1075
Incorrect table definition; there can be only one auto column and it must b=
e
defined as a key

Now, I converted a table in my sandbox earlier this morning to do some
testing, and it worked fine... mind you, i did truncate the table first, bu=
t
i am not sure if that is relavent or not.

The table structure has a TONNE of fields, but to give you an idea... here
is what is starts out like:

CREATE TABLE `radacct_201002` (
`Year_Month` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`Radacct_Id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`Acct_Authentic` VARCHAR(32) DEFAULT '',
Etc etc........
PRIMARY KEY (`Year_Month`,`Radacct_Id`),
UNIQUE KEY `radacct_id` (`Radacct_Id`),
KEY (there are keys here.... not of any relevance that i can see)
) ENGINE=3DINNODB DEFAULT CHARSET=3Dlatin1

That is the table in the sandbox, and as i said, all i did was truncate it,
and change to innodb (there was prolly about 5-10 rows when i did it) and
there wasn't any issues. When i do it to the live database (i copied a
table of live data, so i can convert it and see what kind of times/loads i
get) i get the error...

I am in the midst of removing the combined unique primary key, to see if
that is the culperate or not, but if anyone has any ideas, i am eager to
listen :)

Steve.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg