[mkinnan]

I agree that I want the info tab back on the lower half for the same reasons mentioned by SimonGalaxy.

I specifically liked the ability to 'click' a table name and have the table columns/data shown up in the bottom half. It made it so much easier to write a query and click the table I need to get the column information I need.

I do not want a drag-and-drop feature as mentioned in the code.google.com issue tracker.

[mkinnan]

The tables created by Drupal use CHARSET=utf8. When I created the table manually using the SQL query you posted, it apparently used the CHARSET=latin1. I didn't select a specific charset.

Code:

CREATE TABLE newtable AS SELECT * from oldtable WHERE uid=0 or uid=1

The exporting/backing up I have done is the 'Backup Database as SQL Dump' [Ctrl + Alt + E]

[mkinnan]

peterlaursen wrote on Jun 8 2009, 11:04 AM:

I understand with both sync to an empty and an non-empty table?

The problem only seems to occur on an empty table.

peterlaursen wrote on Jun 8 2009, 11:04 AM:

1) the server versions (both source and target – probably unimportant though).

CentOS 5.3 (x86_64), Apache (2.2.8 (EL)), PHP (5.2.6), MySQL (5.0.58)

peterlaursen wrote on Jun 8 2009, 11:04 AM:

2) is the database used by the application while syncing? Data sync does not take a 'snapshot' of each table, but 'chews its way' through it from one end (and actually I also think backup/export does). Also this we should have handled but that is just an idea right at the moment. The 'duplicate' indicates that an INSERT-attempt is performed that should have been an UPDATE attempt. Can you afford to try stop the application from connecting the database while syncing?

The database is not being used. All tests were performed on a fresh install of the database (by Drupal installation) so no users are accessing the website and no part of Drupal is accessing the database.

This is the table I created manually:

Code:

CREATE TABLE `newtable1` (
`uid` int(10) unsigned NOT NULL default '0',
`name` varchar(60) character set utf8 NOT NULL default '',
`pass` varchar(32) character set utf8 NOT NULL default '',
`mail` varchar(64) character set utf8 default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) character set utf8 NOT NULL default '',
`signature` varchar(255) character set utf8 NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) character set utf8 default NULL,
`language` varchar(12) character set utf8 NOT NULL default '',
`picture` varchar(255) character set utf8 NOT NULL default '',
`init` varchar(64) character set utf8 default '',
`data` longtext character set utf8,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This is the table Drupal creates:

Code:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

[mkinnan]

mkinnan wrote on Jun 8 2009, 10:36 AM:

Everything worked smoothly. I even was able to Sync (schema sync followed by data sync) 'newtable' to another table without a problem. However, if I try schema sync followed by data sync of the 'oldtable' to another table I receive an error.

I just realized that since this works even though 'uid = 0' has an empty 'name' value, then SQLyog doesn't have a problem with this value being NULL even though the column is defined as NOT NULL. Could the problem lie with the auto incrementation for the 'uid' column?

[mkinnan]

peterlaursen wrote on Jun 8 2009, 09:48 AM:

1) I first wrote this

Please tell the program version! And also if you are using HTTP tunnel.

I stated in my post I was using version 8.1. Is there another version number you need? I connect to the databases directly using MySQL (no SSH or HTTP).

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Do you use SQLyog for both export and import?

I use SQLyog for both export and import.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Code:

CREATE newtable AS SELECT * from oldtable WHERE uid=0 or uid=1

.. and next create exactly the same indexes on 'newtable' as on 'oldtable' .. if not the 'duplicate error' comes again.

I created another table and manually added the PRIMARY and UNIQUE indexes without trouble.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

Next export/dump 'newtable' .. drop 'newtable'. Can you now import it?

Everything worked smoothly. I even was able to Sync (schema sync followed by data sync) 'newtable' to another table without a problem. However, if I try schema sync followed by data sync of the 'oldtable' to another table I receive an error.

I did retest an entire database export/import and cannot reproduce the error (it has only happened a couple times over a month). But I always get the error with every sync job.

peterlaursen wrote on Jun 8 2009, 09:48 AM:

.. so it could be an issue with that key and not the Primary Key. What does the content of 'name' look like for column 0 and column 1? And is 'name' column declared NULL or NOT NULL?

Name is declared as NOT NULL. The name for 0 is empty and the name for 1 is 'admin' (the attached picture in my previous post shows most of the values of the tables before and after sync). I attached the full 'table create' schema below.

You are thinking that since 'uid = 0' has an empty 'name' value, SQLyog gives an error because the 'name' column is set as NOT NULL and it expects there to be a value?

Code:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `access` (`access`),
KEY `created` (`created`),
KEY `mail` (`mail`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

[Author]

Posts