mmatuson/schemasync

A MySQL Schema Versioning and Migration Utility

schemasync's Issues

Ascii bug

Error:
Traceback (most recent call last):
File "/usr/local/bin/schemasync", line 11, in
sys.exit(main())
File "/usr/local/lib/python2.7/dist-packages/schemasync/schemasync.py", line 356, in main
sys.exit(parse_cmd_line(app)())
File "/usr/local/lib/python2.7/dist-packages/schemasync/schemasync.py", line 140, in processor
sync_comments=options.sync_comments))
File "/usr/local/lib/python2.7/dist-packages/schemasync/schemasync.py", line 258, in app
p_buffer.write(patch + '\n')
File "/usr/local/lib/python2.7/dist-packages/schemasync/utils.py", line 124, in write
self._buffer.write(data)
UnicodeEncodeError: 'ascii' codec can't encode characters in position 172-173: ordinal not in range(128)

Fix:
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

Two Issues

Issues 1: I down loaded the master version。after i installed it，i found it's version is not v0.9.5 but v0.9.4。
Maybe the version should be changed in schemasync.py here:

copyright = """
Copyright 2009-2016 Mitch Matuson
Copyright 2016 Mustafa Ozgur
"""
version = "0.9.4"

change it to: version = "0.9.5"

Issues 2: Feel it's not efficient than sqlyog compare tools。It takes too much time than sqlyog compare tools。Is there some way to improve efficiency of schemasync?

script analyses wrong databases

I initially tried
'schemasync mysql://root:xxx@localhost:3306/magento mysql://root:xxx@localhost:3306/another_db

The result was a slew of lines like this:
/usr/local/lib/python2.6/dist-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Triggers for table alitoco_development.jos_users have no creation context
cursor.execute(sql, values)
/usr/local/lib/python2.6/dist-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Triggers for table empty_ptp.pp_activities have no creation context
cursor.execute(sql, values)
...
These relate to other databases on localhost rather than those specified. I worked around the problem by switching from root to users that can only access the particular db of interest but this looks like a significant bug to me. Output was generated successfully when I used users that only had access to the single relevant db.

character sets related problem

The tool suggests to alter the database's collation and charset despite there's no diff, the patch and the rollback is the same.
Version: 0.9.4, cloned on 13, Oct 2016.

Also there is a duplicated line at the end of the sql files:

SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

mysql> SELECT *  FROM information_schema.SCHEMATA  WHERE schema_name = "db1";
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db1 | utf8mb4                    | utf8mb4_general_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT *  FROM information_schema.SCHEMATA  WHERE schema_name = "db2";
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db2         | utf8mb4                    | utf8mb4_general_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

patch

--
-- Schema Sync 0.9.4 Patch Script
-- Created: Thu, Oct 13, 2016
-- Server Version: 5.6.33-log
-- Apply To: xxx/db2
--


USE `db2`;
SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `db2` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

revert:

--
-- Schema Sync 0.9.4 Revert Script
-- Created: Thu, Oct 13, 2016
-- Server Version: 5.6.33-log
-- Apply To:  xxx/db2
--

USE `db2`;
SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `db2` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

SchemaSync output and FOREING KEYS

can we sort output so, that CREATE TABLE statement was before CONSTRAINT FOREIGN KEY definition.(see output below)
Many thanks,

[sample output]
--
-- Schema Sync 0.9.4 Revert Script
-- Created: Wed, Sep 06, 2017
-- Server Version: 5.7.17-log
-- Apply To: localhost/mydb
--

USE `mydb`;
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE `CALL` ( 
`UUID` varchar(45) NOT NULL, 
`CREATED` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3), 
`UPDATED` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), 
`GETIT_CALLER_ID` bigint(20) DEFAULT NULL, 
`SECOND_PARTY` varchar(190) DEFAULT NULL, 
`ANONYMOUS` bit(1) DEFAULT NULL, 
`myPHONES` bigint(20) NOT NULL, 
`DIRECTION` varchar(45) DEFAULT NULL, 
`MEDIA_SERVER_ID` varchar(190) DEFAULT NULL, 
`CALL_UUID` varchar(190) DEFAULT NULL, 
`CLOSED` bit(1) DEFAULT NULL, 
`CALL_ID` varchar(190) DEFAULT NULL, 
PRIMARY KEY (`UUID`), 
KEY `CALL_myPHONES_CREATED_GETIT_CALLER_SECOND_PARTY_CLOSED_CALL_ID` (`myPHONES`,`CREATED`,`GETIT_CALLER_ID`,`SECOND_PARTY`,`CLOSED`,`CALL_ID`), 
CONSTRAINT `FK_myPHONES` FOREIGN KEY (`myPHONES`) REFERENCES `myPHONES` (`myPHONES`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ...others tables definitions --
CREATE TABLE `myPHONES` (
`myPHONES` bigint(20) NOT NULL,
`CREATED` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3), 
`UPDATED` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
 PRIMARY KEY (`myPHONES`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
[end of sample output]

Wont work on Fedora 27

Looks like there is incompatibility with below package. All the other versions I've tried to install wont work for Fedora 27. Any ideas?

python2-mysql x86_64 1.3.12-1.fc27 fedora 112 k

Traceback (most recent call last):
File "/usr/bin/schemasync", line 11, in
load_entry_point('SchemaSync==0.9.2', 'console_scripts', 'schemasync')()
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 264, in main
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 123, in processor
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 189, in app
TypeError: init() takes exactly 3 arguments (2 given)

Bit-Field processed incorrectly

Reproduction steps:

1. Add a bit field named 'SEX' to the source table and set the default value to 0;
2. Run schemasync to create the patch script;
3. Import the patch script into mysql;

Following script fails:

ALTER TABLE `table1` ADD COLUMN `SEX` bit(1) NULL DEFAULT 'b'0'' AFTER `LEVEL`;

with error:

ERROR 1064 (42000) at line 9: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right 
syntax to use near '0'' AFTER `LEVEL`' at line 1

This error is caused by the extra single quotation surrounding b'0'. Correct script should be:

ALTER TABLE `table1` ADD COLUMN `SEX` bit(1) NULL DEFAULT b'0' AFTER `LEVEL`;

CREATE VIEW duplicate

When the source schema has 1 view and the target schema has none, the CREATE VIEW line is added twice, and it looks like this:

USE `db_dest`;
SET FOREIGN_KEY_CHECKS = 0;
// ... create new tables
SET FOREIGN_KEY_CHECKS = 1;
CREATE VIEW `view1` AS ... ;
SET FOREIGN_KEY_CHECKS = 1;
CREATE VIEW `view1` AS ... ;

UTF-8 Comments

UTF-8 comments are written in ascii-encoded file and that makes them unusable.

Cannot run via `pipx`

After the 'simple' fixes in #68 schemasync seems to run properly under python3 again, but when trying to install it as a global tool we're advised to no longer use sudo pip install but instead to use pipx (with some other hoop jumping to get a truly global install).

However something isn't quite right when it's installed this way, but I can't quite tell what.

Using schemasync from a pipx install (using a local copy of the repo with the fixes applied in #68 as the source) gives:

Traceback (most recent call last):
  File "/home/vagrant/.local/bin/schemasync", line 5, in <module>
    from schemasync.schemasync import main
  File "/home/vagrant/.local/pipx/venvs/schemasync/lib/python3.11/site-packages/schemasync/schemasync.py", line 9, in <module>
    import syncdb
ModuleNotFoundError: No module named 'syncdb'

The schemasync executable at /home/vagrant/.local/bin/schemasync contains this:

#!/home/vagrant/.local/pipx/venvs/schemasync/bin/python
# -*- coding: utf-8 -*-
import re
import sys
from schemasync.schemasync import main
if __name__ == '__main__':
    sys.argv[0] = re.sub(r'(-script\.pyw|\.exe)?$', '', sys.argv[0])
    sys.exit(main())

Even weirder, if I just execute the main schemasync.py file directly using that env's python, it works fine:

/home/vagrant/.local/pipx/venvs/schemasync/bin/python /home/vagrant/.local/pipx/venvs/schemasync/lib/python3.11/site-packages/schemasync/schemasync.py 

Python isn't really my deal, I'm just trying to make this tool work for a client's project. It'd be great if someone a little more knowledgeable in Python could look into this. I don't have unlimited time to mess around with it, but if there are suggestions of what to try, I can do so, within reason.

Slow comparision

It takes a lot of time if there are hundreds of table in a DB. It works very fast if the table names from one DB do not match with the other.

Error: Missing Required Dependency SchemaObject

I've tried to install SchemaSync in Ubuntu without success, I don't know what I'm doing wrong. These are the steps that I followed:

mpacheco@mpacheco:/Downloads> tar -zxvf SchemaSync-0.9.2.tar.gz
mpacheco@mpacheco:/Downloads> cd SchemaSync-0.9.2
mpacheco@mpacheco:~/Downloads/SchemaSync-0.9.2> sudo python setup.py install
...

Downloading http://pypi.python.org/packages/2.6/s/setuptools/setuptools-0.6c9-py2.6.egg
/usr/lib/python2.6/distutils/dist.py:266: UserWarning: Unknown distribution option: 'entry_points'
warnings.warn(msg)
/usr/lib/python2.6/distutils/dist.py:266: UserWarning: Unknown distribution option: 'install_requires'
warnings.warn(msg)

unning install
running build
running build_py
creating build
creating build/lib.linux-x86_64-2.6
creating build/lib.linux-x86_64-2.6/schemasync
copying schemasync/init.py -> build/lib.linux-x86_64-2.6/schemasync
copying schemasync/utils.py -> build/lib.linux-x86_64-2.6/schemasync
copying schemasync/syncdb.py -> build/lib.linux-x86_64-2.6/schemasync
copying schemasync/schemasync.py -> build/lib.linux-x86_64-2.6/schemasync
running install_lib
creating /usr/local/lib/python2.6/dist-packages/schemasync
copying build/lib.linux-x86_64-2.6/schemasync/init.py -> /usr/local/lib/python2.6/dist-packages/schemasync
copying build/lib.linux-x86_64-2.6/schemasync/utils.py -> /usr/local/lib/python2.6/dist-packages/schemasync
copying build/lib.linux-x86_64-2.6/schemasync/syncdb.py -> /usr/local/lib/python2.6/dist-packages/schemasync
copying build/lib.linux-x86_64-2.6/schemasync/schemasync.py -> /usr/local/lib/python2.6/dist-packages/schemasync
byte-compiling /usr/local/lib/python2.6/dist-packages/schemasync/init.py to init.pyc
byte-compiling /usr/local/lib/python2.6/dist-packages/schemasync/utils.py to utils.pyc
byte-compiling /usr/local/lib/python2.6/dist-packages/schemasync/syncdb.py to syncdb.pyc
byte-compiling /usr/local/lib/python2.6/dist-packages/schemasync/schemasync.py to schemasync.pyc
running install_egg_info
Writing /usr/local/lib/python2.6/dist-packages/SchemaSync-0.9.2.egg-info

Then I was unable to execute schemasync from the terminal (didn't find the command). So I did this:

mpacheco@mpacheco:~/Downloads/SchemaSync-0.9.2> python /usr/local/lib/python2.6/dist-packages/schemasync/schemasync.py
Error: Missing Required Dependency MySQLdb.

mpacheco@mpacheco:~/Downloads/SchemaSync-0.9.2> sudo apt-get install python-mysqldb
...
success

mpacheco@mpacheco:~/Downloads/SchemaSync-0.9.2> python /usr/local/lib/python2.6/dist-packages/schemasync/schemasync.py
Error: Missing Required Dependency SchemaObject

www.schemasync.org not available

Domain probably expired. The link for how to install this doesnt work.

Drop table if exists support

SchemaSync always tries to drop tables, even if they already have been dropped.

Drop index syntax error

I have found a bug while i drop a index
The patch .sql show the SQL is ALTER TABLE table_name DROP INDEX index_name ON table_name;
I think the correct SQL is ALTER TABLE table_name DROP INDEX index_name
Can anybody repair this issues?
Thanks a lot!

Schema Sync requires MySQL version 5.0+ (source is v10.1.0-MariaDB)

It works well on MariaDB by the way ;)

Python 3.8 Incompability

OS/Python version:

grayson@graysonpeddie-admin:~$ python3 --version
Python 3.8.6
grayson@graysonpeddie-admin:~$ cat /etc/debian_version 
bullseye/sid

Method of installation: pip3 install SchemaSync

Output of installation:

grayson@graysonpeddie-admin:~$ sudo pip install SchemaSync
Collecting SchemaSync
  Downloading SchemaSync-0.9.6-py2.py3-none-any.whl (10 kB)
Collecting SchemaObject>=0.5.7
  Downloading SchemaObject-0.5.9-py2.py3-none-any.whl (19 kB)
Collecting PyMySQL>=0.6.2
  Downloading PyMySQL-0.10.1-py2.py3-none-any.whl (47 kB)
     |████████████████████████████████| 47 kB 545 kB/s 
Installing collected packages: PyMySQL, SchemaObject, SchemaSync
Successfully installed PyMySQL-0.10.1 SchemaObject-0.5.9 SchemaSync-0.9.6

Errors I came across:

grayson@graysonpeddie-admin:~$ schemasync --help
Traceback (most recent call last):
  File "/usr/local/bin/schemasync", line 5, in <module>
    from schemasync.schemasync import main
  File "/usr/local/lib/python3.8/dist-packages/schemasync/schemasync.py", line 30
    print "Error: Missing Required Dependency PyMySQL."
          ^
SyntaxError: Missing parentheses in call to 'print'. Did you mean print("Error: Missing Required Dependency PyMySQL.")?

I put parenthesis for print as described in SyntaxError and now I get this:

grayson@graysonpeddie-admin:~$ schemasync --help
Traceback (most recent call last):
  File "/usr/local/bin/schemasync", line 5, in <module>
    from schemasync.schemasync import main
  File "/usr/local/lib/python3.8/dist-packages/schemasync/schemasync.py", line 228
    except schemaobject.connection.DatabaseError, e:
                                                ^
SyntaxError: invalid syntax

It seems the developers of Python 3.8 made some changes that caused SchemaSync not to work.

Schemasync TypeError

I got the following errors when trying to run schemasync on two databases:

Traceback (most recent call last):
File "/usr/local/bin/schemasync", line 9, in
load_entry_point('SchemaSync==0.9.2', 'console_scripts', 'schemasync')()
File "build/bdist.macosx-10.9-intel/egg/schemasync/schemasync.py", line 264, in main
File "build/bdist.macosx-10.9-intel/egg/schemasync/schemasync.py", line 123, in processor
File "build/bdist.macosx-10.9-intel/egg/schemasync/schemasync.py", line 210, in app
File "build/bdist.macosx-10.9-intel/egg/schemaobject/schema.py", line 48, in selected
File "build/bdist.macosx-10.9-intel/egg/schemaobject/schema.py", line 67, in databases
File "build/bdist.macosx-10.9-intel/egg/schemaobject/database.py", line 29, in DatabaseSchemaBuilder
File "build/bdist.macosx-10.9-intel/egg/schemaobject/connection.py", line 63, in execute
File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4-py2.7-macosx-10.9-intel.egg/MySQLdb/cursors.py", line 187, in execute
query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

After I installed the schema sync, I had to run the following command to get rid of an error complaining "Mysqldb" not found.

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Any clue what the problem is?

Thanks!

can't find file error (errorno: 13)

although it appears to create the patch and revert sql, it always produces these errors upon running:

/usr/bin/schemasync --tag="20150319" --log-directory="/var/www/html/wtlogistics/app/updates/" --output-dir="/var/www/html/wtlogistics/app/updates/" mysql://ebflitman:Jammin95@localhost:3306/wlogistics_demo mysql://ebflitman:Jammin95@localhost:3306/wlogistics
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_access_levels.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_accounts.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_accounts_alt_addresses.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_asset_event_reasons.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_asset_events.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_asset_log.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_asset_types.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_assets.frm' (errno: 13)
/usr/lib/python2.6/site-packages/SchemaObject-0.5.3-py2.6.egg/schemaobject/connection.py:63: Warning: Can't find file: './performance_schema/wl_carriers.frm' (errno: 13)

Documentation ambiguity about the output file names

From schemasync.org:
schemasync [options]

# source/target format: mysql://user:pass@host:port/database
# output format: <database>[_<tag>].YYYYMMDD.(patch|revert)[_<version>].sql

is the source database name or the target?

Thanks!

Drop column if exists

SchemaSync always tries to drop columns, even if they already have been dropped.

Support in MySQL is not straightforward:

http://stackoverflow.com/questions/39738594/how-to-drop-a-column-if-exists-in-mysql

Multiple change alter statements created

I''m not sure if this is a schemasync thing, or a mysql thing, but the alter table statements that schemasync produces refuse to run on either of my MYSQl instances. The problem seems to be issuing multiple changes (comma separated) within the same alter table statement. I'd not seen this syntax before, and can't figure out if I should be able to run things such as the stuff below. In this case it kicks out refusing to find the URL column (which is definitely there).

ALTER TABLE events ADD COLUMN frozen_by_user_key bigint(20) unsigned NULL AFTER frozen_time, ADD COLUMN frozen_reason varchar(1000) NULL AFTER frozen_by_user_key, DROP COLUMN frozen, MODIFY COLUMN owner_user_key varchar(45) NULL AFTER event_key, MODIFY COLUMN title text NULL AFTER owner_user_key, MODIFY COLUMN url varchar(255) NOT NULL AFTER title, MODIFY COLUMN guest_password varchar(45) NULL AFTER frozen_reason, ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Compact;

No Sync with Foreign Keys in Schema

Error

Traceback (most recent call last):
File "/usr/bin/schemasync", line 7, in ?
sys.exit(
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 264, in main
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 123, in processor
File "build/bdist.linux-x86_64/egg/schemasync/schemasync.py", line 229, in app
File "build/bdist.linux-x86_64/egg/schemasync/syncdb.py", line 45, in sync_schema
File "build/bdist.linux-x86_64/egg/schemasync/syncdb.py", line 95, in sync_table
File "build/bdist.linux-x86_64/egg/schemaobject/table.py", line 154, in foreign_keys
File "build/bdist.linux-x86_64/egg/schemaobject/foreignkey.py", line 64, in ForeignKeySchemaBuilder
TypeError: an integer is required

I am trying to run schemasync against a server running
MySQL5.1.34 and a Server running MySQL5.1.48 with innodb_plugin.

I have done some testing, it will not work if a table in database has a foreign key.

Error while dropping and adding the same index

Dropping and adding the same index should happen in two separated queries.

Example:

ALTER TABLE `x` [..], DROP INDEX `y` ON `x`;

ALTER TABLE `x` ADD INDEX `y` (`k`) USING BTREE;

Plus, there should not be the ON clause #49.

No longer maintained

I am no longer maintaining this project but am open to finding it a new home.

project dead?

schemasync.org doesn't seem to belong to this project so I wouldn't know how to install it..

There should be an option to ignore charset differences

Charset differences should be able to be ignored when generating schema differences. This leads to a lot of false positive changes as developers run in latin-1 and production runs in utf-8.

Is this a dead project?

Is this a dead project?

Upload SchemaSync to PyPi

I would like to integrate SchemaSync into a tool that i'm writing.
It would help greatly if SchemaSync was available on PyPi, then i could use it in buildout and in my setup.py requirements.
Are you planning on hosting it on PyPi?

Column Comment Regex is Greedy

The regular expression for stripping comments can NOT be greedy. It results in incorrect create table statements.

Drop index incorrect syntax

There should not be an ON statement after DROP INDEX inside an ALTER TABLE query (http://dev.mysql.com/doc/refman/5.7/en/alter-table.html). This behavior is legit if the DROP INDEX is used as a standalone query (http://dev.mysql.com/doc/refman/5.7/en/drop-index.html).

ALTER TABLE `foo` ADD COLUMN `x` bigint(20) unsigned NOT NULL AFTER `id`, DROP INDEX `y` ON `foo` [..]

Should be:

ALTER TABLE `foo` ADD COLUMN `x` bigint(20) unsigned NOT NULL AFTER `id`, DROP INDEX `y` [..]

Colliding key names

Hi, I get a MySQL error (colliding index names) when trying to redefine a foreign key:

Following generated SQL fails:

ALTER TABLE `gallery_image`
DROP FOREIGN KEY `gallery_image_FK_3`
ADD CONSTRAINT `gallery_image_FK_3` FOREIGN KEY `gallery_image_FK_3` (`COLUMN_NAME`) 
REFERENCES  [more stuff here];

... with error message ...

ERROR 1005 (HY000) at line 26: Can't create table 'DATABASENAME.#sql-45c_e7' (errno: 121)

That error goes away if I split the statement in two ...

ALTER TABLE `gallery_image`
DROP FOREIGN KEY `gallery_image_FK_3`;

ALTER TABLE `gallery_image`
ADD CONSTRAINT `gallery_image_FK_3` FOREIGN KEY `gallery_image_FK_3` (`COLUMN_NAME`) 
REFERENCES  [more stuff here];

... so It seems that if both alterations are in a single statement - even though it first drops the key and then recreates it - the name still collides.

Column sync misses columns changes once sequence is in order

StopIteration is being raised if the columns are in order, which is causing the column sync to quit before it verifies all columns are equal.

Enhancement: Prevent dropping tables in target

It would be fine to have a switch to prevent dropping tables in the target database.
Sometimes, we open auxiliary tables in some databases to keep some information which is relevant to that particular database.

I came across an error

my py version is 2.6 and i installed SchemaSync,but ,when when i run it throw out an error like this below:

Traceback (most recent call last):
File "/usr/bin/schemasync", line 9, in
load_entry_point('SchemaSync==0.9.5', 'console_scripts', 'schemasync')()
File "build/bdist.linux-x86_64/egg/pkg_resources/init.py", line 550, in load_entry_point
File "build/bdist.linux-x86_64/egg/pkg_resources/init.py", line 2710, in load_entry_point
File "build/bdist.linux-x86_64/egg/pkg_resources/init.py", line 2370, in load
File "build/bdist.linux-x86_64/egg/pkg_resources/init.py", line 2376, in resolve
File "/usr/lib/python2.6/site-packages/schemasync/schemasync.py", line 34, in
import schemaobject
File "/usr/lib/python2.6/site-packages/schemaobject/init.py", line 11, in
from schemaobject.schema import SchemaObject
File "/usr/lib/python2.6/site-packages/schemaobject/schema.py", line 1, in
from schemaobject.connection import DatabaseConnection
File "/usr/lib/python2.6/site-packages/schemaobject/connection.py", line 1, in
import pymysql
File "/usr/lib/python2.6/site-packages/pymysql/init.py", line 96, in
from . import connections as _orig_conn
File "/usr/lib/python2.6/site-packages/pymysql/connections.py", line 79
FIELD_TYPE.BIT,
^
SyntaxError: invalid syntax

This is the connections.py:
TEXT_TYPES = {
FIELD_TYPE.BIT,
FIELD_TYPE.BLOB,
FIELD_TYPE.LONG_BLOB,
FIELD_TYPE.MEDIUM_BLOB,
FIELD_TYPE.STRING,
FIELD_TYPE.TINY_BLOB,
FIELD_TYPE.VAR_STRING,
FIELD_TYPE.VARCHAR,
FIELD_TYPE.GEOMETRY,
}

this is not a dict but a list , Is Schemasync dependent on pymysql?

optimize schema sync query

I tried to optimize the queries using the following parameters. But it does not seem to help.

tmp_table_size=200M
max_heap_table_size=200M

How do I make the schema sync query faster?
It typically looks something like this in the processlist:

SELECT K.CONSTRAINT_NAME, K.TABLE_SCHEMA, K.TABLE_NAME, K.COLUMN_NAME,
K.REFERENCED_TABLE_SCHEMA, K.REFERENCED_TABLE_NAME, K.REFERENCED_COLUMN_NAME,
K.POSITION_IN_UNIQUE_CONSTRAINT
FROM information_schema.KEY_COLUMN_USAGE K, information_schema.TABLE_CONSTRAINTS T
WHERE K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
AND T.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND K.CONSTRAINT_SCHEMA='murti'
AND K.TABLE_NAME='response_pages'

sync_dropped_procedures error

def sync_dropped_procedures(src, dest):
for p in dest:
if p not in src:
# yield dest[p].drop(), src[p].create()
yield dest[p].drop(), dest[p].create() # fix bug, key of p not in src, src[p].create() will be error

Script doesn't observe sub-part indexes

When creating the patch script, we have a number of unique indexes based on the first 255 characters in a table, but the script doesn't include those values

ALTER TABLE dbservers MODIFY COLUMN serverid int(10) unsigned NOT NULL FIRST, MODIFY COLUMN zonenumber int(10) unsigned NOT NULL AFTER serverid, ADD UNIQUE INDEX u_dnsname (dnsname(255)) USING BTREE, ADD INDEX f_zonenumber (zonenumber) USING BTREE, DROP INDEX dnsname, DROP INDEX zonenumber;

• i've manually added the index length to dnsname above (which is tinytext) else the following error is thrown by mysql
  ERROR 1170 (42000): BLOB/TEXT column 'dnsname' used in key specification without a key length

schemasync can't find MySQLDb

I know next-to-nothing about Python (and that's probably the real problem here), but I have tried to install MySQLDb 1.2.3 according to the instructions in its README file. I then installed schemasync and got no error messages. But when I try to run schemasync, I see

Error: Missing Required Dependency MySQLdb.

Any clues on how to correct this?

CREATE VIEW on second try only

1. Source DB has View and target doesn't.
2. 1st patch finds all TABLE differences including foreign key constraints and everything but no CREATE VIEW
3. 2nd patch finds MISSING VIEW and creates a CREATE VIEW command.

I expected it all to be patches in one go not in two.

enums go uppercase

If I have enum column type like tihs:
enum("aLice","BoB")

Then schemasync will transform it to ENUM("ALICE", "BOB"), which is a bug.

One of the conqeuences of this is that if I apply that diff, then run schemasync again, it will still show the difference for this column.

I think the problem is in schemaobject/column.py:36 where you decide to make column type uppercase for some reason.

Thanks.

Comma Seperated ALTER TABLE comamnds causes Error Code 1054

The .patch script generated produces comma separated statements like this:

ALTER TABLE a ADD COLUMN b tinyint(1) NOT NULL DEFAULT '0' AFTER c
, ADD COLUMN d tinyint(1) NULL DEFAULT '1' AFTER f;

But this is a known problem and produces Error Code: 1054
http://bugs.mysql.com/bug.php?id=60650

A suggested fix is to use separate ALTER TABLE statements:
ALTER TABLE a ADD COLUMN b tinyint(1) NOT NULL DEFAULT '0' AFTER c
ALTER TABLE a ADD COLUMN d tinyint(1) NULL DEFAULT '1' AFTER f;

Can this be implemented?

select database tables

is there any "easy" way we can tell schemasync wich tables to work with?

i have a very big database with a lot of tables but not all of them are modified and schemasync takes a lot of time to check everything.

source and target url doesn't honor port number

When trying to run schemasync via ssh tunnels (3306 is not publicly available on our production machines) I found out the the port number on the url is not handled correctly.

mysql://root:XXX@localhost:2001/service_dev2

Does still go to 3306.

ERROR while running Schemasync

Hi @mmatuson

I have an error when Im running this command,

schemasync --tag="client1" mysql://root:[email protected]:3306/client mysql://root:[email protected]:3306/production_client

getting this error,

MySQL Error 1130: Host '192.168.21.233' is not allowed to connect to this MySQL server

I have this user and host in the mysql.user and granted all the privileges, and also the target. Can you please advise?

By the way, when I run this command mysql -u -h 192.168.21.233 -p Im able to connect to mysql so how come the 192.168.21.233 is not allowed to the SQL server?

Many thanks!

Kind Regards,
jsdizon

Wrong diff command generated by schema sync

The following patch generated by schemasync fails to apply because of export column because first it tries to add some column AFTER export, then later calls MODIFY COLUMN export.

One of the solutions is to issue each column modification in separate ALTER statement.

ALTER TABLE `tbl_verticals` ADD COLUMN `alert_warning_email` VARCHAR(250) NOT NULL COMMENT 'Comma+space separated list of email to send warning alerts to.
Example  "[email protected], [email protected]".' AFTER `export`, ADD COLUMN `alert_error_email` VARCHAR(250) NOT NULL COMMENT 'Comma+space separated list of email to send error alerts to.
Example  "[email protected], [email protected]".' AFTER `alert_warning_email`, ADD COLUMN `alert_critical_email` VARCHAR(250) NOT NULL COMMENT 'Comma+space separated list of emails to send critical alerts to.
Example  "[email protected], [email protected]".' AFTER `alert_error_email`, DROP COLUMN `_type`, DROP COLUMN `export_method`, DROP COLUMN `export_uri`, DROP COLUMN `export_format`, DROP COLUMN `export_compress`, DROP COLUMN `export_regression_guard`, DROP COLUMN `export_digest`, MODIFY COLUMN `learn_scan_pages` INT(10) UNSIGNED NOT NULL DEFAULT '100' COMMENT 'jjj.' AFTER `vertical_name`, MODIFY COLUMN `full_scan_pages` INT(10) UNSIGNED NOT NULL DEFAULT '200' COMMENT 'kkk.' AFTER `learn_scan_pages`, MODIFY COLUMN `fast_scan_pages` INT(10) UNSIGNED NOT NULL DEFAULT '100' COMMENT 'kkk. ' AFTER `full_scan_pages`, MODIFY COLUMN `detailed_scan_pages` INT(10) UNSIGNED NOT NULL DEFAULT '100' COMMENT 'iii.' AFTER `fast_scan_pages`, MODIFY COLUMN `garbage_scan_pages` INT(10) UNSIGNED NOT NULL DEFAULT '100' COMMENT ooo.' AFTER `detailed_scan_pages`, MODIFY COLUMN `number_of_trials` INT(10) UNSIGNED NOT NULL DEFAULT '2' COMMENT 'oooo ' AFTER `garbage_scan_pages`, MODIFY COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Miiii ' AFTER `number_of_trials`, MODIFY COLUMN `max_learn_scan_jobs` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT kkk' AFTER `is_active`, MODIFY COLUMN `max_full_scan_jobs` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'kkk' AFTER `max_learn_scan_jobs`, MODIFY COLUMN `max_fast_scan_jobs` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'ooo `max_full_scan_jobs`, MODIFY COLUMN `max_detailed_scan_jobs` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Mppp' AFTER `max_fast_scan_jobs`, MODIFY COLUMN `max_garbage_scan_jobs` INT(10) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'ooo' AFTER `max_detailed_scan_jobs`, MODIFY COLUMN `export` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'kkk.' AFTER `parallel_detailed_jobs_per_site`, COMMENT='The supported verticals';

Literals default values

SchemaSync improperly generate SQL code for default values that are strings, for example in enums.

DEFAULT section AFTER `id`

Should be:

DEFAULT 'section' AFTER `id`