cannot sync workbench

Scriptcase 8 Discussion
#1

This is not directly related to SC but it is MySQL/Workbench question/issue.
Never had that error before. I just updated my table (Added 5 new columns) Trying to sync mySQL Workbench with remote server but when I do this I get errors

Executing SQL script in server
ERROR: Error 1054: Unknown column 'Facebook' in 's_staff'
SQL Code:
        ALTER TABLE `klon56_tecrep`.`s_staff`
        CHANGE COLUMN `Email` `Email` VARCHAR(80) NULL DEFAULT NULL AFTER `EmergencyPhone`,
        CHANGE COLUMN `PinCode` `PinCode` CHAR(4) NULL DEFAULT NULL AFTER `Facebook`,
        CHANGE COLUMN `UserActive` `UserActive` VARCHAR(3) NULL DEFAULT NULL AFTER `PinCode`,
        CHANGE COLUMN `Activation` `Activation` DATE NULL DEFAULT NULL AFTER `UserActive`,
        CHANGE COLUMN `AdminPriv` `AdminPriv` VARCHAR(3) NULL DEFAULT NULL AFTER `Activation`,
        CHANGE COLUMN `AdminFlag` `AdminFlag` CHAR(1) NULL DEFAULT NULL AFTER `AdminPriv`,
        CHANGE COLUMN `Language` `Language` VARCHAR(30) NULL DEFAULT NULL AFTER `AdminFlag`,
        CHANGE COLUMN `Signature` `Signature` BLOB NULL DEFAULT NULL AFTER `Language`,
        CHANGE COLUMN `RegistrationDate` `RegistrationDate` DATE NULL DEFAULT NULL AFTER `Signature`,
        CHANGE COLUMN `Active` `Active` VARCHAR(1) NULL DEFAULT NULL AFTER `RegistrationDate`,
        CHANGE COLUMN `Continent` `Continent` VARCHAR(45) NULL DEFAULT NULL AFTER `Active`,
        CHANGE COLUMN `CompanyID` `CompanyID` INT(11) NULL DEFAULT NULL ,
        CHANGE COLUMN `Stamper` `Stamper` TIMESTAMP NOT NULL ,
        ADD COLUMN `Skype` VARCHAR(30) NULL DEFAULT NULL AFTER `Email`,
        ADD COLUMN `ICQ` VARCHAR(30) NULL DEFAULT NULL AFTER `Skype`,
        ADD COLUMN `Facebook` VARCHAR(80) NULL DEFAULT NULL AFTER `ICQ`,
        DROP PRIMARY KEY,
        ADD PRIMARY KEY (`StaffID`, `Stamper`)

SQL script execution finished: statements: 3 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

I can add this same column through phpMyAdmin but not from the Workbench Sync. Found lots of recerences to this Error on line but none of them pertrains to MySQL workbench. Any ideas ?

ARTHUR

#2

I have found that altering tables to be a bit tricky when using MySQL Workbench’s Alter Table feature, i.e. via the graphical environment changing field names, adding fields, changing keys, etc. The SQL code that it builds sometimes just doesn’t work. Sometimes it can be fixed by changing the sequence of the changes, sometimes they have to be redone in smaller transaction packages, i.e. first changing the field names, then the sequence of them, then the index changes, etc. Perhaps it is as much a weakness in the general workbench application or even the core MySQL as in the alter table feature itself. Thus, It could be the same issue that you are encountering.

But it certainly look strange that you cannot just add a new field. I would try to break the changes into smaller packages that are committed individually, e.g. do all the column changes first and then add the new fields.

#3

thanks for sharing - Orion. I can do all changes through the MySQL tools or phpMyAdmin. It is just workbench that has some issues. I’m using Workbench for months and never had a problem so far with adding new columns. I will take your advice in the future and see what happens. Fortunately this time changes are not so big so I could do it manually, then re-sync Workbench and it works OK. I will see in the future if I have this same problem. Strange that the problems ocurred with fields which possibly could be used as reserver words (i.e ICW, Facebook), but there was no problem adding columns ‘Skype’. Do you know of any limitations in MySQL pertraining to those typical keywords which one might want to use as column names ?

ARTHUR

#4

You are welcome.

I love the WorkBench for when you have to do serious work on the databases: Many tabs, it remembers all files open from last time, great visual tools for schema and data changes, but it does have the quirks when it comes to changing existing schema. In comparison I find the phpMyAdmin somewhat clunky with a single SQL window, no history, etc. I only use it for admin work like exports and loads and even for that stuff I often just use the MySQL command line.

Yes, strange with the field names, perhaps reserved as you mention.

#5

I haven’t found the solution so far so had to modify structures with phpMyAdmin. Once the new columns are present Workbench sync is back to normal. After some research I found that this error is not related to Workbench only. It happens in many other scenarios when the data structure needs to be modified- so it seems it is rather MySQL problem, but no clear understanding under what conditions it happens.

#6

Strange that you can get it to work with phpMyAdmin if it is a core MySQL problem – perhaps they have found out to work around it.
Well, we can post here, if anything new comes to light about this.

#7

Yes, so the steps are to fix the issues with phpMyAdmin then run Sync with Workbench and it works.
well… until another issues,…haha!

#8

It looks like a bug in Workbench.
The

CHANGE COLUMN PinCode PinCode CHAR(4) NULL DEFAULT NULL AFTER Facebook

within the ALTER TABLE statement occurs before the column ‘Facebook’ is created. Just a situation where order is important.

#9

yes you are right! this is exactly that’s tripping it. I just wonder how this got messed up ? probably by moving the columns in order…