Errors When Trying To Synch Databases

[cseymour]

Hello,

I am trying to synch the structure of a local and remote database. I have both connections opened. When I try to use the PowerTool Structure Synchronization Tool, I get two errors:

Error No. 1146 Table 'mysql.proc' does not exist

This error is displayed twice.

I can then see the differences in the db's but the buttons on the screen are greyed out.

Thanks.

Chris

I am using SQLYog ENT 5.22

[peterlaursen]

is there a LETTERCASE mismatch between two tables on the server(s)?

like 'tablename' and 'TABLENAME' ??

We know that this is an issue, but we have not yet quite decided on the future of how to handle such LETTERCASE issues with the SYNC tools.

[cseymour]

peterlaursen wrote on Jan 22 2007, 09:21 AM:

is there a LETTERCASE mismatch between two tables on the server(s)?

like 'tablename' and 'TABLENAME' ??

We know that this is an issue, but we have not yet quite decided on the future of how to handle such LETTERCASE issues with the SYNC tools.

No, the funny part is the database I am trying to synch with does not contain the table mysql.proc.

Thanks.

Chris

[peterlaursen]

“the database I am trying to synch with does not contain the table mysql.proc”

If that is the situation with MySQL 5.x, then you have probably upgraded the server from a previous version (and did incorrectly!) and you should absolutely run the “mysql_fix_privilege_tables.sql” script!  You always should when performing a major upgrade of the MySQL server.  Read the MySQL docs on how to upgrade MySQL. But basically you can do this (on Windows):

1) Find the “mysql_fix_privilege_tables.sql” file (in the /scripts -folder).

2) Copy to C:

3) Start mysql command line client

4) Execute “source c:mysql_fix_privilege_tables.sql”

(you cannot use SQLyog because the script generates a lot of errors and SQLyog will abort!)

5) It is consider 'good practice' to 'check/repair tables' after that.  You can do from SQLyog.

The most recent MySQL versions ship with a 'mysql_upgrade' program (in the /bin -folder) that does this automatically.

I cannot tell for sure if this is the porblem here (will need a complete test case).  But with a `mysql` database that has not what tables and columns it should have for a specific MySQL version, we cannot guarantee anything!

[cseymour]

peterlaursen wrote on Jan 23 2007, 05:50 AM:

“the database I am trying to synch with does not contain the table mysql.proc”

If that is the situation with MySQL 5.x, then you have probably upgraded the server from a previous version (and did incorrectly!) and you should absolutely run the “mysql_fix_privilege_tables.sql” script! You always should when performing a major upgrade of the MySQL server. Read the MySQL docs on how to upgrade MySQL. But basically you can do this (on Windows):

1) Find the “mysql_fix_privilege_tables.sql” file (in the /scripts -folder).

2) Copy to C:

3) Start mysql command line client

4) Execute “source c:mysql_fix_privilege_tables.sql”

(you cannot use SQLyog because the script generates a lot of errors and SQLyog will abort!)

5) It is consider 'good practice' to 'check/repair tables' after that. You can do from SQLyog.

The most recent MySQL versions ship with a 'mysql_upgrade' program (in the /bin -folder) that does this automatically.

I cannot tell for sure if this is the porblem here (will need a complete test case). But with a `mysql` database that has not what tables and columns it should have for a specific MySQL version, we cannot guarantee anything!

Thanks Peter,

The remote database is on a hosted provider, so I probably will not get access to the main MySQL database. But I have run , as you suggested.

I tried to do a synch and got the same error. Is there anyway to determine what database is being found as missing the mysql.proc table (local or remote)?

Thanks.

Chris

[cseymour]

Hi Peter,

I uninstalled version 5.22 Enterprise and re-installed version 5.0 enterprise.

I am able to Synch the databases as I had in the past.

I am using MySQL version 5.0.24a on Windows and the same version is on a Linux remote host.

Thanks.

Chris

[peterlaursen]

We need a test case.

A structure-only dump (no data required) of SOURCE and TARGET where it fails

Could you create such test case?

Zip the files and attach here (or create a ticket if you do not want to expose it in public).

[cseymour]

Hi Peter,

Here is the dump files I have created from the remote and local copies of the database I was trying to synch.

Thanks for looking into this for me.

Cheers.

Chris

[peterlaursen]

I can easily reproduce the error with the mysql.proc does not exist.  See attached.  I

just renamed to table.Do not ask us sfor help with that.  Ask your SysAdmin to fix that mysql installation instead.  It is sick and needs some medicine .. go to the MySQL doctor and get a recipe on some “mysql_fix_privilege_tables.sql” 😉

When the databases/servers are OK the STRUCTURE SYNC tool generates this SYNC script:

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom` (

`crid` int(11) NOT NULL auto_increment ,

`nid` int(11) NOT NULL DEFAULT '0' ,

`poll_freq` int(2) NOT NULL DEFAULT '3000' ,

`idle_freq` int(3) NOT NULL DEFAULT '60000' ,

`kicked_out_message` longtext NULL ,

`banned_message` longtext NULL ,

`module` varchar(255) NULL DEFAULT 'chatroom' ,

`auto_archive` int(1) NULL DEFAULT '0' ,

`old_msg_count` int(3) NULL DEFAULT '20' ,

`modified` int(11) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`crid`) ,

KEY `nid` (`nid`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_ban_list` (

`crid` int(11) NOT NULL auto_increment ,

`uid` int(11) NOT NULL DEFAULT '0' ,

`admin_uid` int(11) NOT NULL DEFAULT '0' ,

`modified` int(11) NOT NULL DEFAULT '0' ,

KEY `crid_uid` (`crid`,`uid`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_chat` (

`ccid` int(11) NOT NULL auto_increment ,

`crid` int(11) NOT NULL DEFAULT '0' ,

`uid` int(11) NOT NULL DEFAULT '0' ,

`chatname` varchar(255) NOT NULL ,

`modified` int(11) NOT NULL DEFAULT '0' ,

`when_archived` int(11) NULL ,

PRIMARY KEY (`ccid`) ,

KEY `crid` (`crid`) ,

KEY `modified` (`modified`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_chat_invites` (

`cciid` int(11) NOT NULL auto_increment ,

`sender_uid` int(11) NOT NULL DEFAULT '0' ,

`invitee_uid` int(11) NOT NULL DEFAULT '0' ,

`declined` int(1) NULL ,

`created_ccid` int(11) NULL ,

PRIMARY KEY (`cciid`) ,

KEY `sender_uid` (`sender_uid`) ,

KEY `invitee_uid` (`invitee_uid`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_msg` (

`cmid` int(11) NOT NULL auto_increment ,

`ccid` int(11) NOT NULL DEFAULT '0' ,

`uid` int(11) NOT NULL DEFAULT '0' ,

`msg_type` varchar(64) NOT NULL ,

`msg` longtext NOT NULL ,

`session_id` varchar(255) NOT NULL ,

`recipient` varchar(255) NOT NULL ,

`modified` int(11) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`cmid`) ,

KEY `ccid` (`ccid`) ,

KEY `session_id` (`session_id`) ,

KEY `recipient` (`recipient`) ,

KEY `modified` (`modified`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_msg_archive` (

`cmid` int(11) NOT NULL DEFAULT '0' ,

`ccid` int(11) NOT NULL DEFAULT '0' ,

`uid` int(11) NOT NULL DEFAULT '0' ,

`msg_type` varchar(64) NOT NULL ,

`msg` longtext NOT NULL ,

`session_id` varchar(255) NOT NULL ,

`recipient` varchar(255) NOT NULL ,

`modified` int(11) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`cmid`) ,

KEY `ccid` (`ccid`) ,

KEY `session_id` (`session_id`) ,

KEY `recipient` (`recipient`) ,

KEY `modified` (`modified`)

)Engine=MyISAM;

/* Create table in First database */

create table `mysql_bdhockey`.`chatroom_online_list` (

`coid` int(11) NOT NULL auto_increment ,

`ccid` int(11) NOT NULL DEFAULT '0' ,

`uid` int(11) NOT NULL DEFAULT '0' ,

`session_id` varchar(255) NOT NULL ,

`guest_id` int(11) NOT NULL DEFAULT '1' ,

`away` int(1) NULL DEFAULT '0' ,

`is_admin` int(1) NULL DEFAULT '0' ,

`modified` int(11) NOT NULL DEFAULT '0' ,

PRIMARY KEY (`coid`) ,

KEY `update_time` (`ccid`,`uid`,`session_id`)

)Engine=MyISAM;

.. what is what it should.  Right?? After execution of the script next sync does not report any difference.

Apart from the problem(s) with your server/host, what is then the porblem?  Please explain!

[cseymour]

Hi Peter,

Thanks for looking into that for me. I will touch base with my hosting company and see if they have run the script you have mentioned.

Thanks again for you help.

Best regards.

Chris Seymour

[cseymour]

Hi Peter,

Just a quick follow-up. Hosting company ran the script and all is working fine now.

Thanks again for your help.

Cheers.

Chris

[Author]

Posts