forums › forums › SQLyog › SQLyog: Bugs / Feature Requests › Problem Syncing Opencms Databases
- This topic is empty.
-
AuthorPosts
-
-
October 5, 2006 at 10:35 am #9945
Tirpune
MemberHello there,
We are using SJA to syncronize 2 MySQL servers storing OpenCMS databases.
Syncronization of CMS_ONLINE_CONTENTS (holds all the website contents) doesn't succeed (most of the times).
The problem can be confirmed from missing content on the other web server. But after re-running the syncronization content looks fine on it.
Here's the table:
CREATE TABLE `cms_online_contents` (
`CONTENT_ID` varchar(36) character set utf8 collate utf8_bin NOT NULL default '',
`RESOURCE_ID` varchar(36) character set utf8 collate utf8_bin NOT NULL default '',
`FILE_CONTENT` longblob NOT NULL,
PRIMARY KEY (`CONTENT_ID`),
UNIQUE KEY `IDX1` (`RESOURCE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
From log after first run:
Table SrcRows TgtRows Inserted Updated Deleted
========================= ======= ======= ======== ======= =======
`CMS_ONLINE_CONTENTS` 6528 6525 4 0 7
From log after second run:
Table SrcRows TgtRows Inserted Updated Deleted
========================= ======= ======= ======== ======= =======
`CMS_ONLINE_CONTENTS` 6528 6522 6 0 0
Size of the db is more than 500MB.
Does this ring any bell for anyone? Thanks in advance.
-
October 5, 2006 at 10:40 am #22707
peterlaursen
ParticipantQuote:CHARSET=utf8;is this the case on both servers?
how is the charset dropdown/
tag for BOTH connections? Always inform about Program versions!!
MySQL versions?
Platform on which the servers run?
SJA version?
SJA for Linux or Windows?
-
October 5, 2006 at 10:58 am #22708Memberpeterlaursen wrote on Oct 5 2006, 01:40 PM:is this the case on both servers?
how is the charset dropdown/
tag for BOTH connections? Always inform about Program versions!!
MySQL versions?
Platform on which the servers run?
SJA version?
SJA for Linux or Windows?
The charset is identical.
>>MySQL versions?
mysql-4.1.12-3.RHEL4.1
>>Platform on which the servers run?
Red Hat Enterprise Linux ES release 4 (Nahant Update 2)
>>SJA version?
5.18
>>SJA for Linux or Windows?
Linux
-
October 5, 2006 at 11:14 am #22709Participant
do you use the
tag in your XML ? Quote:missing content on the other web server.can you identify those missing rows, copy them to a seperate table (CREATE TABLE AS SELECT … WHERE ….) and export it?
I think we will need to see what they look like in a HEX-editor!
Are you sure that all apps that you use don't have issues with the utf8 charset?
-
October 5, 2006 at 11:15 am #22710Memberpeterlaursen wrote on Oct 5 2006, 02:09 PM:do you use the
tag in your XML ? No.
localhost root xxx 3306 opencms xxx root 3306 opencms -
October 5, 2006 at 11:20 am #22711ParticipantQuote:missing content on the other web server.
can you identify those missing rows, copy them to a seperate table (CREATE TABLE AS SELECT … WHERE ….) and export it?
I think we will need to see what they look like in a HEX-editor!
Are you sure that all apps that you use don't have issues with the utf8 charset?
Quote:Size of the db is more than 500MB.what is that equivalent in rows?
try
Code:…
localhost
root mintrold 3306 utf8
test
localhost
root mintrold 3306 ujis
tst
….. -
October 5, 2006 at 11:45 am #22712Memberpeterlaursen wrote on Oct 5 2006, 02:20 PM:what is that equivalent in rows?
mysql>show table status;
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+| CMS_ONLINE_CONTENTS | MyISAM | 10 | Dynamic | 6524 | 111700 | 1060443632 | 281474976710655 | 936960 | 331708824 | NULL | 2006-05-24 16:07:23 | 2006-10-05 14:18:59 | | utf8_general_ci | NULL |
-
October 5, 2006 at 1:14 pm #22713Participant
OK .. now I think I understand
the problem is that the concatted strings that SJA need to generate to build checksums with become to long compared to some buffer settings on the server (or the memory available) for those rows where FILE_CONTENT are largest. It is a LONGBLOB column and can be as big as 4 GB.
Use the
option in the jobfile. Read about it here: http://webyog.com/en/whitepapers/Using_SQL…L_Databases.pdf
as the column IDX1 is unique, you use that single columns with the
option and concatted strings will be very short! And sync will be MUCH faster too. -
October 5, 2006 at 1:45 pm #22714Memberpeterlaursen wrote on Oct 5 2006, 04:14 PM:OK .. now I think I understand
the problem is that the concatted strings that SJA need to generate to build checksums with become to long compared to some buffer settings on the server (or the memory available) for those rows where FILE_CONTENT are largest. It is a LONGBLOB column and can be as big as 4 GB.
Use the
option in the jobfile. Read about it here: http://webyog.com/en/whitepapers/Using_SQL…L_Databases.pdf
as the column IDX1 is unique, you use that single columns with the
option and concatted strings will be very short! And sync will be MUCH faster too. Great! I'll try that tomorrow morning and will let you know how it went. Thanks!
-
October 6, 2006 at 8:23 am #22715Memberpeterlaursen wrote on Oct 5 2006, 04:14 PM:OK .. now I think I understand
the problem is that the concatted strings that SJA need to generate to build checksums with become to long compared to some buffer settings on the server (or the memory available) for those rows where FILE_CONTENT are largest. It is a LONGBLOB column and can be as big as 4 GB.
Can you name any settings that could affect this?
peterlaursen wrote on Oct 5 2006, 04:14 PM:Use theoption in the jobfile. Read about it here: http://webyog.com/en/whitepapers/Using_SQL…L_Databases.pdf
as the column IDX1 is unique, you use that single columns with the
option and concatted strings will be very short! And sync will be MUCH faster too. I tried and got an error:
Error No. 1054
Unknown column 'IDX1' in 'field list'
IDX1 is not a column, is it? Did you maybe mean the CONTENT_ID or RESOURCE_ID?
On the other hand if I understand you correctly this would only work with inserts and deletes but not with updates to existing content?
-
October 6, 2006 at 8:30 am #22716Participant
I meant RESOURCE_ID of course.
as ther is a Unique Index on RESORUCE_ID that column must be unique
-
October 6, 2006 at 11:32 am #22717Memberpeterlaursen wrote on Oct 6 2006, 11:30 AM:I meant RESOURCE_ID of course.
as ther is a Unique Index on RESORUCE_ID that column must be unique
Column RESOURCE_ID didn't do the trick. When through fine but without the results you (and I) where hoping.
What do think of my comment about the updated content (existing rows)?
-
October 6, 2006 at 11:39 am #22718Participant
it will/should work with UPDATES too.
a md5()-checksum is is generated on the column(s) on both hosts. If they diifer the source-row will owerwrite the target row.
Could we see the jobfile? (fake details as you like).
You can create a ticket if you do not wnat to expose it in public.
-
October 6, 2006 at 12:18 pm #22719Memberpeterlaursen wrote on Oct 6 2006, 02:39 PM:it will/should work with UPDATES too.
a md5()-checksum is is generated on the column(s) on both hosts. If they diifer the source-row will owerwrite the target row.
Could we see the jobfile? (fake details as you like).
You can create a ticket if you do not wnat to expose it in public.
localhost root xxx 3306 utf8 opencms xxx root 3306 utf8 opencms `CMS_BACKUP_CONTENTS` `CMS_BACKUP_PROJECTRESOURCES` `CMS_BACKUP_PROJECTS` `CMS_BACKUP_PROPERTIES` `CMS_BACKUP_PROPERTYDEF` `CMS_BACKUP_RESOURCES` `CMS_BACKUP_STRUCTURE` `CMS_GROUPS` `CMS_GROUPUSERS` `CMS_OFFLINE_ACCESSCONTROL` `CMS_OFFLINE_CONTENTS` `CMS_OFFLINE_PROPERTIES` `CMS_OFFLINE_PROPERTYDEF` `CMS_OFFLINE_RESOURCES` `CMS_OFFLINE_STRUCTURE` `CMS_ONLINE_ACCESSCONTROL` `CMS_ONLINE_CONTENTS` `RESOURCE_ID` `CMS_ONLINE_PROPERTIES` `CMS_ONLINE_PROPERTYDEF` `CMS_ONLINE_RESOURCES` `CMS_ONLINE_STRUCTURE` `CMS_PROJECTRESOURCES` `CMS_PROJECTS` `CMS_PUBLISH_HISTORY` `CMS_STATICEXPORT_LINKS` `CMS_SYSTEMID` `CMS_TASK` `CMS_TASKLOG` `CMS_TASKPAR` `CMS_TASKTYPE` `CMS_USERS` -
October 6, 2006 at 12:32 pm #22720Memberpeterlaursen wrote on Oct 6 2006, 02:39 PM:it will/should work with UPDATES too.
a md5()-checksum is is generated on the column(s) on both hosts. If they diifer the source-row will owerwrite the target row.
Could we see the jobfile? (fake details as you like).
You can create a ticket if you do not wnat to expose it in public.
The UPDATE concerns FILE_CONTENT column so shouldn't that be included also.
And that gets us right where started… 🙂
-
-
AuthorPosts
- You must be logged in to reply to this topic.
