Innodb Constraints are not synced to the client database

[Andre Bruijn]

Hello,

I walked into a problem when synchronizing an Innodb that uses Constraints:

I noticed that my Innodb contraints are not synced from the server to the client;

This is my situation:

In my file lib.model.schema.sql I do have 2 tables:

1. aanheffingen

2. gebruikers

In short (I removed irrelevant columns), my CREATE statements look like this:

CREATE TABLE `gebruikers`

(

`id` INTEGER  NOT NULL AUTO_INCREMENT,

`id_aanheffing` INTEGER,

PRIMARY KEY (`id`),

INDEX `gebruikers_FI_2` (`id_aanheffing`),

CONSTRAINT `gebruikers_FK_2`

FOREIGN KEY (`id_aanheffing`)

REFERENCES `aanheffingen` (`id`)

ON DELETE RESTRICT

)Type=InnoDB;

CREATE TABLE `aanheffingen`

(

`id` INTEGER  NOT NULL AUTO_INCREMENT,

`naam` VARCHAR(255),

PRIMARY KEY (`id`)

)Engine=InnoDB;

After creating my schema in the MySQL database on my databaseserver, I make a dump via Export that looks like this:

CREATE TABLE IF NOT EXISTS `gebruikers` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `id_aanheffing` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `gebruikers_FI_2` (`id_aanheffing`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 ;

-- Triggers etc

and then

ALTER TABLE `gebruikers`

  ADD CONSTRAINT `gebruikers_FK_2` FOREIGN KEY (`id_aanheffing`) REFERENCES `aanheffingen` (`id`);

  

As far as now no problems.

After making a syncschema with a client and a server database, I perform a synchronization.

At the client side the database is filled with the structure and with the data.

However, when I delete a row in the table `aanheffingen`, I am able to do that.

That should not happen because of the Constraint ON DELETE RESRICT on `id_aanheffing`

So next step I did was checking if I could find the Alter Table statement in the dump of the table `gebruikers`

or even in the whole database dump. I mean this one:

ALTER TABLE `gebruikers`

  ADD CONSTRAINT `gebruikers_FK_2` FOREIGN KEY (`id_aanheffing`) REFERENCES `aanheffingen` (`id`);

But this was not there.

So my problem is that the Constraints are not synchronized to the client database.

And after that, I did remove a row in the table `aanheffingen` at the client side, and I then perform

a sync to the server, and walk (of course what I expect) into an error, namely this one:

Sync failed with the following error(s).

Please correct the error(s) and/or try again.

PVS-2029: Internal error when checking in client transaction (caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row:

a foreign key constraint fails (`serverdbname`.`gebruikers`, CONSTRAINT `gebruikers_FK_2` FOREIGN KEY (`id_aanheffing`) REFERENCES `aanheffingen` (`id`)))

Is this problem known to you, and/or do you (already) have a solution for this?

Regards,

[perv...@gmail.com]

See Section 5.12 of the Pervasync User's Guide:

Using Sync SQL to Create Indexes and Constraints on Local DBs

Sync SQL

Pervasync does not automatically sync indexes (except for Primary key indexes) and constraints (except for NOT NULL) to local databases. The reason is to make the system less complex and to make synchronization between different types of databases possible.

In addition to indexes and constraints, there are other database objects, such as views, functions, triggers and PL/SQL packages (for Oracle) that users may or may not want to sync to local DBs. If they do, they may not want the exact same objects as central Db to be created on local DBs.

To assist users in managing these generic DB objects, Pervasync implemented a feature called “Sync SQL” that enables users to publish SQL statements on the web admin console. The SQL statements will be downloaded and executed on local DB during synchronization. In the SQL statement, users could create, alter and delete any DB objects that are appropriate for the local databases.

Sync SQL belongs to a sync schema. To get started, locate a sync schema on the web admin console “Sync Schemas” page, click on “Sqls” under “Sync Objects” and then you will get to a page where you can add, remove and update sync SQLs.

The Sync SQLs are executed with the client side sync schema as the default schema.

NOTE: How to include multiple SQL statements in one Sync SQL so that you don’t have to create too many Sync SQLs? For Oracle databases each Sync SQL has to be a single execution unit to be executed by the database. However you could enclose multiple SQL statements by “BEGIN” and “END;” to make it a PLSQL block so that it can be executed as a single unit. For MySQL databases, you could put multiple statements, separated by semi-colon, in one Sync SQL as long as you have the “allowMultiQueries=true” in your JDBC URL, for example:

jdbc:mysql://localhost:3306/?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true

 To update the JDBC URL, go to the “Setup” tab of the sync client UI, enable “Advance Mode”, edit and submit.

NOTE:  For Oracle databases, a simple SQL statement cannot have a trailing “;” while a PLSQL block has to have a trailing “;”.

 An Example of Foreign Key Creation via Sync SQL

Foreign key constraints on client DB can be tricky as they may interfere with the synchronization process. For example, in some situations like subscription parameter values being updated, Pervasync would need to re-create and re-populate affected tables on device. If a table has a foreign key constraint, the operation may fail.

 

A solution to this problem is to disable the foreign key constraints during sync: drop the foreign key constraints before sync and re-create the constraints after sync. In <Pervasync Client Home>/config/ pervasync_client_mysql.conf, you can set the "drop constraint" sql as value of parameter pervasync.client.before.refresh.sql and set the "create constraint" sql as value of pervasync.client.after.refresh.sql. To make the “create/drop” sql easier to manage, you can put them in stored procedures and use Sync SQL to create the stored procedures. This way you can make changes to the constraints any time you want from the admin console.

 

Following is an example of the stored procedures that you can modify and then publish as a Sync SQL of your publication schema.

 

 

DROP PROCEDURE IF EXISTS `CreateFK`;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateFK`(

    IN parm_table_name VARCHAR(100),

    IN parm_key_name VARCHAR(100),

    IN parm_cascade_on_delete BOOL,

    IN parm_child_field_name VARCHAR(100),

    IN parm_parent_table_name VARCHAR(100),

    IN parm_parent_field_name VARCHAR(100)

)

BEGIN

    set @ParmTable = parm_table_name ;

    set @ParmKey = parm_key_name ;    

    set @ParmChildFieldName = parm_child_field_name;

    set @ParmParentTableName = parm_parent_table_name;

    set @ParmParentFieldName = parm_parent_field_name;

   

    IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN

        set @StatementToExecute = concat('ALTER TABLE ',@ParmTable,' DROP FOREIGN KEY ',@ParmKey);

        prepare DynamicStatement from @StatementToExecute ;

        execute DynamicStatement ;

        deallocate prepare DynamicStatement ;

    END IF;

 

    IF (parm_cascade_on_delete = false) THEN

        set @AddFKStatement1 = concat('ALTER TABLE `',@ParmTable,'` ADD CONSTRAINT `',@ParmKey, '` FOREIGN KEY (`', @ParmChildFieldName, '`) REFERENCES `', @ParmParentTableName, '` (`', parm_parent_field_name, '`) ON UPDATE NO ACTION');

        prepare DynamicStatement1 from @AddFKStatement1;

        execute DynamicStatement1 ;

        deallocate prepare DynamicStatement1 ;

    ELSE

        set @AddFKStatement2 = concat('ALTER TABLE `',@ParmTable,'` ADD CONSTRAINT `',@ParmKey, '` FOREIGN KEY (`', @ParmChildFieldName, '`) REFERENCES `', @ParmParentTableName, '` (`', parm_parent_field_name, '`) ON DELETE CASCADE ON UPDATE NO ACTION');

        prepare DynamicStatement2 from @AddFKStatement2;

        execute DynamicStatement2 ;

        deallocate prepare DynamicStatement2 ;

    END IF;

END

 

 

DROP PROCEDURE IF EXISTS `DropFK`;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `DropFK`(

    IN parm_table_name VARCHAR(100),

    IN parm_key_name VARCHAR(100),

    IN parm_cascade_on_delete BOOL,

    IN parm_child_field_name VARCHAR(100),

    IN parm_parent_table_name VARCHAR(100),

    IN parm_parent_field_name VARCHAR(100)

)

BEGIN

    set @ParmTable = parm_table_name ;

    set @ParmKey = parm_key_name ;    

    set @ParmChildFieldName = parm_child_field_name;

    set @ParmParentTableName = parm_parent_table_name;

    set @ParmParentFieldName = parm_parent_field_name;

   

    IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN

        set @StatementToExecute = concat('ALTER TABLE ',@ParmTable,' DROP FOREIGN KEY ',@ParmKey);

        prepare DynamicStatement from @StatementToExecute ;

        execute DynamicStatement ;

        deallocate prepare DynamicStatement ;

    END IF;

END

 

 

DROP PROCEDURE IF EXISTS `CreateForeignKeys`;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateForeignKeys`( )

BEGIN

 

CALL YourDBName.CreateFK('announcementattachments', 'fk_AnnouncementAttachments_Announcements1', true, 'AnnouncementID', 'announcements', 'AnnouncementID');

 

CALL YourDBName.CreateFK('announcementattachments', 'fk_AnnouncementAttachments_Companies', false, 'CompanyID', 'companies', 'CompanyID');

 

.... (add the other call to CreateFK here for other foreign keys)

 

END

 

 

DROP PROCEDURE IF EXISTS `DropForeignKeys`;

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `DropForeignKeys`( )

BEGIN

   

CALL YourDBName.DropFK('announcementattachments', 'fk_AnnouncementAttachments_Announcements1', true, 'AnnouncementID', 'announcements', 'AnnouncementID');

 

CALL YourDBName.DropFK('announcementattachments', 'fk_AnnouncementAttachments_Companies', false, 'CompanyID', 'companies', 'CompanyID');

 

.... (add the other call to DropFK here for other foreign keys)

 

END

 

 

After that, call the stored procedures DropForeignKeys and CreateForeignKeys in the client config file:

 

pervasync.client.before.refresh.sql = call YourDBName.DropForeignKeys()

pervasync.client.after.refresh.sql = call YourDBName.CreateForeignKeys()

 

[Andre Bruijn]

Aha, I should have read better the pervasync_users_guide.pdf

I tried the fast solution in the pervasync client config: 
pervasync.client.after.refresh.sql=create constraint

but I get this error:

pervasync.SyncException: PVC-1001: .... You have an error in your SQL syntax ..... near 'constraint' at line 1

if I change the other sentence:

pervasync.client.before.refresh.sql=drop constraint

then I get this error

PVC-1019: Client download phase internal error etc  ..... near 'constraint' at line 1 

I tried several syntaxes like with quotes or double quotes or the backtick operator `, but the error remains.

Do you have a suggestion?

Op maandag 23 juli 2012 14:53:43 UTC+2 schreef Andre Bruijn het volgende:

[Andre Bruijn]

I noticed that I took this to literally:

It should be worked out like this:

pervasync.client.before.refresh.sql=call YourDBName.DropForeignKeys()

pervasync.client.after.refresh.sql=call YourDBName.CreateForeignKeys()

Where YourDBName is your client db name.

Op dinsdag 24 juli 2012 13:43:01 UTC+2 schreef Andre Bruijn het volgende: