Description:
This problem occur many times and sometimes I lost 6 hours to correct it. In some cases the MySQL Database was replace for SQL Server or MSDE to stop the problems and we will prepare the program for Oracle Database.
The MySQL is very unstable and it isn't trustworthy. The problem that I have is the following:
It happens when I alter a table structure or create a table using ADO with SQL command CREATE TABLE... , ALTER TABLE...
The problem occurs when I alter and create I lot of tables at the same time. But this error don't occur always.
Error on rename table #002xxxx*
The problem occurs on MySQL console too.
* The message is a the # signal and a number value I guess that it use an temporary table to do some actions.
Supose until third command are executed and tables was updated successfully and the problem occur in forth command. If I execute the commands again and it try to add a column that exists will show a duplicate column name message.
How to repeat:
Try to "ALTER TABLE" and "CREATE TABLE" many times.
For example:
-- BEGIN
DROP TABLE IF EXISTS NRSERIEPRODUTO;
-- END
-- BEGIN
CREATE TABLE IF NOT EXISTS NRSERIEPRODUTO (
CDEMPRESA INT NOT NULL,
CDPRODUTO VARCHAR(20) NOT NULL,
NRSERIEPROD VARCHAR(20) NOT NULL,
NRLOTEPROD VARCHAR(20),
DTVALIDADEPROD DATETIME,
INBAIXADOPROD VARCHAR(1)
) TYPE=INNODB;
-- END
-- BEGIN
ALTER TABLE NRSERIEPRODUTO ADD
CONSTRAINT NRSERIEPRODUTO_PK PRIMARY KEY
(
CDEMPRESA,
CDPRODUTO,
NRSERIEPROD
);
-- END
-- BEGIN
ALTER TABLE NRSERIEPRODUTO ADD COLUMN INCONCILIADOPROD VARCHAR(1);
-- END
-- BEGIN
DROP TABLE IF EXISTS NRSERIEPRODUTOLOG;
-- END
-- BEGIN
CREATE TABLE IF NOT EXISTS NRSERIEPRODUTOLOG (
CDEMPRESA INT NOT NULL,
CDPRODUTO VARCHAR(20) NOT NULL,
NRSERIEPROD VARCHAR(20) NOT NULL,
CDSEQUENCIAL_LOG INT NOT NULL,
CDINDICE_LOG INT NOT NULL,
CDIDENTIFICADOR_LOG INT NOT NULL,
DATAHORA_LOG DATETIME NOT NULL,
CDPROGRAMA_LOG INT NOT NULL,
NMPROGRAMA_LOG VARCHAR(20) NOT NULL,
TIPO_LOG VARCHAR(1) NOT NULL,
NMCOMPLEMENTO_LOG VARCHAR(50),
INESTORNADO_LOG VARCHAR(1) NOT NULL,
INCONCILIADO_LOG VARCHAR(1) NOT NULL,
CDUSUARIO INT
) TYPE=INNODB;
-- END
-- BEGIN
ALTER TABLE NRSERIEPRODUTOLOG ADD
CONSTRAINT NRSERIEPRODUTO1_PK PRIMARY KEY
(
CDEMPRESA,
CDPRODUTO,
NRSERIEPROD,
CDSEQUENCIAL_LOG
);
-- END
-- BEGIN
ALTER TABLE NRSERIEPRODUTOLOG ADD
INDEX NRSERIEPRODUTO1_IDX
(
CDEMPRESA,
CDPRODUTO,
CDPROGRAMA_LOG,
INESTORNADO_LOG,
CDINDICE_LOG,
CDIDENTIFICADOR_LOG
);
-- END
-- BEGIN
ALTER TABLE NRSERIEPRODUTOLOG ADD
INDEX NRSERIEPRODUTO2_IDX
(
CDEMPRESA,
CDIDENTIFICADOR_LOG
);
-- END
CREATE TABLE IF NOT EXISTS TEST (
CDID INT NOT NULL,
CDID2 VARCHAR(20) NOT NULL,
NR VARCHAR(20) NOT NULL,
NMCOMP VARCHAR(50)
) TYPE=INNODB;
-- BEGIN
ALTER TABLE TEST ADD COLUMN CDIDENTIFICADOR_NRS INT;
-- END
-- BEGIN
CREATE TABLE IF NOT EXISTS CODIGOTABELA (
CDEMPRESA INT NOT NULL,
NMTABELA VARCHAR(30) NOT NULL,
CDTABELA INT NOT NULL
) TYPE=INNODB;
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD
CONSTRAINT CODIGOTABELA_PK PRIMARY KEY
(
CDEMPRESA,
NMTABELA
);
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD COLUMN DTABERTURA_CH DATETIME;
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD COLUMN CDMULTA_BCO INT;
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD COLUMN VRMULTA_BCO DOUBLE(13,2);
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD COLUMN INCALCPRECOVENDAICMS_CONFIG VARCHAR(1);
-- END
-- BEGIN
ALTER TABLE CODIGOTABELA ADD COLUMN INPRECOSEMICMS_TPPRC VARCHAR(1);
-- END
Suggested fix:
Verify if an column exists before add an column
In SQL Server, for example is:
-- BEGIN
if not exists (select * from [dbo].[syscolumns] where name = 'INCONTROLEAUTONRSERIE_PRD' and id = (select id from [dbo].[sysobjects] where name = 'NRSERIEPRODUTO'))
ALTER TABLE [dbo].[NRSERIEPRODUTO] ADD [INCONCILIADOPROD] [varchar] (1) NULL
-- END
The command is executed only if the column don't exists. This command function with ADO command.
Description: This problem occur many times and sometimes I lost 6 hours to correct it. In some cases the MySQL Database was replace for SQL Server or MSDE to stop the problems and we will prepare the program for Oracle Database. The MySQL is very unstable and it isn't trustworthy. The problem that I have is the following: It happens when I alter a table structure or create a table using ADO with SQL command CREATE TABLE... , ALTER TABLE... The problem occurs when I alter and create I lot of tables at the same time. But this error don't occur always. Error on rename table #002xxxx* The problem occurs on MySQL console too. * The message is a the # signal and a number value I guess that it use an temporary table to do some actions. Supose until third command are executed and tables was updated successfully and the problem occur in forth command. If I execute the commands again and it try to add a column that exists will show a duplicate column name message. How to repeat: Try to "ALTER TABLE" and "CREATE TABLE" many times. For example: -- BEGIN DROP TABLE IF EXISTS NRSERIEPRODUTO; -- END -- BEGIN CREATE TABLE IF NOT EXISTS NRSERIEPRODUTO ( CDEMPRESA INT NOT NULL, CDPRODUTO VARCHAR(20) NOT NULL, NRSERIEPROD VARCHAR(20) NOT NULL, NRLOTEPROD VARCHAR(20), DTVALIDADEPROD DATETIME, INBAIXADOPROD VARCHAR(1) ) TYPE=INNODB; -- END -- BEGIN ALTER TABLE NRSERIEPRODUTO ADD CONSTRAINT NRSERIEPRODUTO_PK PRIMARY KEY ( CDEMPRESA, CDPRODUTO, NRSERIEPROD ); -- END -- BEGIN ALTER TABLE NRSERIEPRODUTO ADD COLUMN INCONCILIADOPROD VARCHAR(1); -- END -- BEGIN DROP TABLE IF EXISTS NRSERIEPRODUTOLOG; -- END -- BEGIN CREATE TABLE IF NOT EXISTS NRSERIEPRODUTOLOG ( CDEMPRESA INT NOT NULL, CDPRODUTO VARCHAR(20) NOT NULL, NRSERIEPROD VARCHAR(20) NOT NULL, CDSEQUENCIAL_LOG INT NOT NULL, CDINDICE_LOG INT NOT NULL, CDIDENTIFICADOR_LOG INT NOT NULL, DATAHORA_LOG DATETIME NOT NULL, CDPROGRAMA_LOG INT NOT NULL, NMPROGRAMA_LOG VARCHAR(20) NOT NULL, TIPO_LOG VARCHAR(1) NOT NULL, NMCOMPLEMENTO_LOG VARCHAR(50), INESTORNADO_LOG VARCHAR(1) NOT NULL, INCONCILIADO_LOG VARCHAR(1) NOT NULL, CDUSUARIO INT ) TYPE=INNODB; -- END -- BEGIN ALTER TABLE NRSERIEPRODUTOLOG ADD CONSTRAINT NRSERIEPRODUTO1_PK PRIMARY KEY ( CDEMPRESA, CDPRODUTO, NRSERIEPROD, CDSEQUENCIAL_LOG ); -- END -- BEGIN ALTER TABLE NRSERIEPRODUTOLOG ADD INDEX NRSERIEPRODUTO1_IDX ( CDEMPRESA, CDPRODUTO, CDPROGRAMA_LOG, INESTORNADO_LOG, CDINDICE_LOG, CDIDENTIFICADOR_LOG ); -- END -- BEGIN ALTER TABLE NRSERIEPRODUTOLOG ADD INDEX NRSERIEPRODUTO2_IDX ( CDEMPRESA, CDIDENTIFICADOR_LOG ); -- END CREATE TABLE IF NOT EXISTS TEST ( CDID INT NOT NULL, CDID2 VARCHAR(20) NOT NULL, NR VARCHAR(20) NOT NULL, NMCOMP VARCHAR(50) ) TYPE=INNODB; -- BEGIN ALTER TABLE TEST ADD COLUMN CDIDENTIFICADOR_NRS INT; -- END -- BEGIN CREATE TABLE IF NOT EXISTS CODIGOTABELA ( CDEMPRESA INT NOT NULL, NMTABELA VARCHAR(30) NOT NULL, CDTABELA INT NOT NULL ) TYPE=INNODB; -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD CONSTRAINT CODIGOTABELA_PK PRIMARY KEY ( CDEMPRESA, NMTABELA ); -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD COLUMN DTABERTURA_CH DATETIME; -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD COLUMN CDMULTA_BCO INT; -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD COLUMN VRMULTA_BCO DOUBLE(13,2); -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD COLUMN INCALCPRECOVENDAICMS_CONFIG VARCHAR(1); -- END -- BEGIN ALTER TABLE CODIGOTABELA ADD COLUMN INPRECOSEMICMS_TPPRC VARCHAR(1); -- END Suggested fix: Verify if an column exists before add an column In SQL Server, for example is: -- BEGIN if not exists (select * from [dbo].[syscolumns] where name = 'INCONTROLEAUTONRSERIE_PRD' and id = (select id from [dbo].[sysobjects] where name = 'NRSERIEPRODUTO')) ALTER TABLE [dbo].[NRSERIEPRODUTO] ADD [INCONCILIADOPROD] [varchar] (1) NULL -- END The command is executed only if the column don't exists. This command function with ADO command.