mmatuson / schemasync Goto Github PK
View Code? Open in Web Editor NEWA MySQL Schema Versioning and Migration Utility
Home Page: http://mmatuson.github.io/SchemaSync/
License: Other
A MySQL Schema Versioning and Migration Utility
Home Page: http://mmatuson.github.io/SchemaSync/
License: Other
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?
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!
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.
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`
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?
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.
schemasync.org doesn't seem to belong to this project so I wouldn't know how to install it..
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` [..]
UTF-8 comments are written in ascii-encoded file and that makes them unusable.
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
;
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')
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.
Domain probably expired. The link for how to install this doesnt work.
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?
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)
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.
I am no longer maintaining this project but am open to finding it a new home.
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;
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.
It works well on MariaDB by the way ;)
I expected it all to be patches in one go not in two.
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.
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 ... ;
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?
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.
Reproduction steps:
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`;
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]
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.
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!
SchemaSync always tries to drop tables, even if they already have been dropped.
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';
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.
Is this a dead project?
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!
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
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/Downloads> cd SchemaSync-0.9.2
mpacheco@mpacheco:
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
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.
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)
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.
The regular expression for stripping comments can NOT be greedy. It results in incorrect create table statements.
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;
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.
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
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
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'
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.
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?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.