Hi Emmanuel, The key was that sql is a reserved keyword. Adding "sqlColumnName="sql_stmt" solve my problem.
Thanks, On Tue, Jan 20, 2009 at 3:36 AM, <[email protected] > wrote: > Send Sequoia mailing list submissions to > [email protected] > > To subscribe or unsubscribe via the World Wide Web, visit > https://forge.continuent.org/mailman/listinfo/sequoia > or, via email, send a message with subject or body 'help' to > [email protected] > > You can reach the person managing the list at > [email protected] > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of Sequoia digest..." > > > Today's Topics: > > 1. [ Sequoia - MySQL ] : SQLException on initialization caused > by RecoveryLog config (Bakary Dialaya DJIBA) > 2. Re: [ Sequoia - MySQL ] : SQLException on initialization > caused by RecoveryLog config (Emmanuel Cecchet) > 3. duplicate key value violates unique constraint (Tyler Shen) > 4. Re: duplicate key value violates unique constraint > (Emmanuel Cecchet) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 19 Jan 2009 18:11:07 +0100 > From: "Bakary Dialaya DJIBA" <[email protected]> > Subject: [Sequoia] [ Sequoia - MySQL ] : SQLException on > initialization caused by RecoveryLog config > To: [email protected] > Message-ID: > <[email protected]> > Content-Type: text/plain; charset="iso-8859-1" > > Hi All, > > I am new in sequoia.... > > I am trying to setup Sequoia to Mirror two MySQL Servers(v5.1.30). > My Controller fails to create the RecoveryLog Tables because "VARCHAR(size) > NOT NULL" not supported... > > *RecoveryLog elements*: > > <RecoveryLogTable tableName="RECOVERY" > logIdColumnType="BIGINT NOT NULL" > vloginColumnType="VARCHAR(50) NOT NULL" > sqlColumnType="VARCHAR(20) NOT NULL" > sqlParamColumnType="VARCHAR(20) NOT NULL" > autoConnTranColumnType="CHAR(1) NOT NULL" > extraStatementDefinition=",PRIMARY KEY (log_id)"/> > > <CheckpointTable tableName="CHECK_POINT" > checkpointNameColumnType="VARCHAR(127) NOT NULL"/> > > <BackendTable tableName="BACKEND" > databaseNameColumnType="VARCHAR(50) NOT NULL" > backendNameColumnType="VARCHAR(50) NOT NULL" > checkpointNameColumnType="VARCHAR(127) NOT NULL"/> > > <DumpTable tableName="SEQUOIA_DUMP" > dumpNameColumnType="TEXT NOT NULL" > dumpDateColumnType="DATETIME" > dumpPathColumnType="TEXT NOT NULL" > dumpFormatColumnType="TEXT NOT NULL" > checkpointNameColumnType="TEXT NOT NULL" > backendNameColumnType="TEXT NOT NULL" > tablesColumnType="TEXT NOT NULL"/> > > *BUG*: > java.lang.RuntimeException: Unable to initialize the database: > java.sql.SQLExcep > tion: Unable to create table RECOVERY (You have an error in your SQL > syntax; > che > ck the manual that corresponds to your MySQL server version for the right > syntax > to use near 'sql VARCHAR(20) NOT NULL,sql_param VARCHAR(20) NOT > NULL,auto_conn_tran CHAR(1) NOT NULL,trans > action_id BIGI' at line 1) > > What should I set the type of the fields in the RecoveryLog Tables > to? > > Help please. > > Regards > > -- > Bakary, > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://forge.continuent.org/pipermail/sequoia/attachments/20090119/6b220266/attachment-0001.html > > ------------------------------ > > Message: 2 > Date: Mon, 19 Jan 2009 18:49:27 -0500 > From: Emmanuel Cecchet <[email protected]> > Subject: Re: [Sequoia] [ Sequoia - MySQL ] : SQLException on > initialization caused by RecoveryLog config > To: Sequoia general mailing list <[email protected]> > Message-ID: <[email protected]> > Content-Type: text/plain; charset="iso-8859-1" > > Hi Bakary, > > The problem is that sql is a reserved keyword in MySQL 5.1 (see > http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html) > This is why the error message is "syntax to use near 'sql VARCHAR(20) > NOT NULL...". You can override the SQL column name using the > sqlColumnName attribute in the RecoveryLogTable element. This should work: > > <RecoveryLogTable tableName="RECOVERY" > logIdColumnType="BIGINT NOT NULL" > vloginColumnType="VARCHAR(50) NOT NULL" > * sqlColumnName="sql_stmt"* > sqlColumnType="VARCHAR(20) NOT NULL" > sqlParamColumnType="VARCHAR(20) NOT NULL" > autoConnTranColumnType="CHAR(1) NOT NULL" > extraStatementDefinition=",PRIMARY KEY (log_id)"/> > > Hope this helps, > Emmanuel > > > > > I am new in sequoia.... > > > > I am trying to setup Sequoia to Mirror two MySQL Servers(v5.1.30). > > My Controller fails to create the RecoveryLog Tables because > > "VARCHAR(size) NOT NULL" not supported... > > > > *RecoveryLog elements*: > > > > <RecoveryLogTable tableName="RECOVERY" > > logIdColumnType="BIGINT NOT NULL" > > vloginColumnType="VARCHAR(50) NOT NULL" > > sqlColumnType="VARCHAR(20) NOT NULL" > > sqlParamColumnType="VARCHAR(20) NOT NULL" > > autoConnTranColumnType="CHAR(1) NOT NULL" > > extraStatementDefinition=",PRIMARY KEY (log_id)"/> > > > > <CheckpointTable tableName="CHECK_POINT" > > checkpointNameColumnType="VARCHAR(127) NOT NULL"/> > > > > <BackendTable tableName="BACKEND" > > databaseNameColumnType="VARCHAR(50) NOT NULL" > > backendNameColumnType="VARCHAR(50) NOT NULL" > > checkpointNameColumnType="VARCHAR(127) NOT NULL"/> > > > > <DumpTable tableName="SEQUOIA_DUMP" > > dumpNameColumnType="TEXT NOT NULL" > > dumpDateColumnType="DATETIME" > > dumpPathColumnType="TEXT NOT NULL" > > dumpFormatColumnType="TEXT NOT NULL" > > checkpointNameColumnType="TEXT NOT NULL" > > backendNameColumnType="TEXT NOT NULL" > > tablesColumnType="TEXT NOT NULL"/> > > > > *BUG*: > > java.lang.RuntimeException: Unable to initialize the database: > > java.sql.SQLExcep > > tion: Unable to create table RECOVERY (You have an error in your SQL > > syntax; che > > ck the manual that corresponds to your MySQL server version for the > > right syntax > > to use near 'sql VARCHAR(20) NOT NULL,sql_param VARCHAR(20) NOT > > NULL,auto_conn_tran CHAR(1) NOT NULL,trans > > action_id BIGI' at line 1) > > > > What should I set the type of the fields in the RecoveryLog Tables > > to? > > > > Help please. > > > > Regards > > > > -- > > Bakary, > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Sequoia mailing list > > [email protected] > > https://forge.continuent.org/mailman/listinfo/sequoia > > > -- > Emmanuel Cecchet > FTO @ Frog Thinker > Open Source Development & Consulting > -- > Web: http://www.frogthinker.org > email: [email protected] > Skype: emmanuel_cecchet > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://forge.continuent.org/pipermail/sequoia/attachments/20090119/b1038cf5/attachment-0001.html > > ------------------------------ > > Message: 3 > Date: Tue, 20 Jan 2009 12:36:25 +1100 > From: "Tyler Shen" <[email protected]> > Subject: [Sequoia] duplicate key value violates unique constraint > To: <[email protected]> > Message-ID: <5e1605ccfa3b4b06a1afc528acaf6...@wuliaszpc> > Content-Type: text/plain; charset="us-ascii" > > Hi > > > > I got the following errors on a single Sequoia controller with two backend > nodes: > > > > 009-01-20 09:59:48,302 INFO controller.loadbalancer.RAIDb1 write request > 48348 failed: > > Backend ABDR - BackendWorkerThread for backend 'cluster2' with RAIDb > level:1 > failed (ERROR: insert or update on table "contactdetails" violates f > > oreign key constraint "fk5cbbce22494239c6" > > Detail: Key (emailaddress)=(569596) is not present in table > "emailaddress".) > > Backend ABDR - BackendWorkerThread for backend 'cluster1' with RAIDb > level:1 > failed (ERROR: insert or update on table "contactdetails" violates f > > oreign key constraint "fk5cbbce22494239c6" > > Detail: Key (emailaddress)=(569596) is not present in table > "emailaddress".) > > > > 2009-01-20 09:59:48,303 INFO > virtualdatabase.VirtualDatabaseWorkerThread.ABDR Error during command > execution (write request 48348 failed: > > Backend ABDR - BackendWorkerThread for backend 'cluster2' with RAIDb > level:1 > failed (ERROR: insert or update on table "contactdetails" violates f > > oreign key constraint "fk5cbbce22494239c6" > > Detail: Key (emailaddress)=(569596) is not present in table > "emailaddress".) > > Backend ABDR - BackendWorkerThread for backend 'cluster1' with RAIDb > level:1 > failed (ERROR: insert or update on table "contactdetails" violates f > > oreign key constraint "fk5cbbce22494239c6" > > Detail: Key (emailaddress)=(569596) is not present in table > "emailaddress".) > > > > It appeared to me that it is a result of previous query not executed on one > node before the next query gets executed. This error happens randomly. > > we managed to repeat this problem only when the server is running under > certain load. Also, FYI, WaitForCompletion policy is already set to "all". > > Any help is appreciated. > > > > Tyler > > > > > > > > -------------- next part -------------- > An HTML attachment was scrubbed... > URL: > http://forge.continuent.org/pipermail/sequoia/attachments/20090120/42492352/attachment-0001.html > > ------------------------------ > > Message: 4 > Date: Mon, 19 Jan 2009 21:35:40 -0500 > From: Emmanuel Cecchet <[email protected]> > Subject: Re: [Sequoia] duplicate key value violates unique constraint > To: Sequoia general mailing list <[email protected]> > Message-ID: <[email protected]> > Content-Type: text/plain; charset=windows-1252; format=flowed > > Hi Tyler, > > From the messages you sent, it looks like the request fails > consistently on both backends. So it looks like the behavior is > consistent and you might have a race condition in your application that > can lead to the foreign key violation. > If the problem only occurs under a certain load, the delay introduced by > Sequoia might exacerbate some synchronization issues in the application. > You might be able to reproduce the issue with a standalone database if > you artificially load the node where the database is running. > > Hope this helps, > Emmanuel > > > > I got the following errors on a single Sequoia controller with two > > backend nodes: > > > > 009-01-20 09:59:48,302 INFO controller.loadbalancer.RAIDb1 write > > request 48348 failed: > > > > Backend ABDR - BackendWorkerThread for backend 'cluster2' with RAIDb > > level:1 failed (ERROR: insert or update on table "contactdetails" > > violates f > > > > oreign key constraint "fk5cbbce22494239c6" > > > > Detail: Key (emailaddress)=(569596) is not present in table > > "emailaddress".) > > > > Backend ABDR - BackendWorkerThread for backend 'cluster1' with RAIDb > > level:1 failed (ERROR: insert or update on table "contactdetails" > > violates f > > > > oreign key constraint "fk5cbbce22494239c6" > > > > Detail: Key (emailaddress)=(569596) is not present in table > > "emailaddress".) > > > > 2009-01-20 09:59:48,303 INFO > > virtualdatabase.VirtualDatabaseWorkerThread.ABDR Error during command > > execution (write request 48348 failed: > > > > Backend ABDR - BackendWorkerThread for backend 'cluster2' with RAIDb > > level:1 failed (ERROR: insert or update on table "contactdetails" > > violates f > > > > oreign key constraint "fk5cbbce22494239c6" > > > > Detail: Key (emailaddress)=(569596) is not present in table > > "emailaddress".) > > > > Backend ABDR - BackendWorkerThread for backend 'cluster1' with RAIDb > > level:1 failed (ERROR: insert or update on table "contactdetails" > > violates f > > > > oreign key constraint "fk5cbbce22494239c6" > > > > Detail: Key (emailaddress)=(569596) is not present in table > > "emailaddress".) > > > > It appeared to me that it is a result of previous query not executed > > on one node before the next query gets executed. This error happens > > randomly. > > > > we managed to repeat this problem only when the server is running > > under certain load. Also, FYI, WaitForCompletion policy is already set > > to "all". > > > > Any help is appreciated. > > > > Tyler > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > Sequoia mailing list > > [email protected] > > https://forge.continuent.org/mailman/listinfo/sequoia > > > -- > Emmanuel Cecchet > FTO @ Frog Thinker > Open Source Development & Consulting > -- > Web: http://www.frogthinker.org > email: [email protected] > Skype: emmanuel_cecchet > > > > ------------------------------ > > _______________________________________________ > Sequoia mailing list > [email protected] > https://forge.continuent.org/mailman/listinfo/sequoia > > End of Sequoia Digest, Vol 41, Issue 16 > *************************************** > -- Bakary,
_______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
