Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

error when deploying the sqitch database #158

Closed
doy opened this issue Apr 18, 2014 · 16 comments
Closed

error when deploying the sqitch database #158

doy opened this issue Apr 18, 2014 · 16 comments
Milestone

Comments

@doy
Copy link

doy commented Apr 18, 2014

$ sqitch deploy db:mysql://root@/community_test
Adding registry tables to db:mysql://root@/sqitch
ERROR 1418 (HY000) at line 140 in file: '/home/doy/perl5/perlbrew/perls/perl-5.18/lib/site_perl/5.18.2/App/Sqitch/Engine/mysql.sql': This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Deploying changes to db:mysql://root@/community_test
  + appuser .. ok

This is with MariaDB 5.5.36. It causes verify scripts to fail, because the sqitch.checkit function doesn't exist.

@theory
Copy link
Collaborator

theory commented Apr 18, 2014

Can sqitch.checkit() be updated to work on MariaDB? I have never used it. :-(

@doy
Copy link
Author

doy commented Apr 18, 2014

MariaDB is in theory entirely compatible with MySQL - it's possible this is just an issue with 5.5. The error is documented here in any case: https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_binlog_unsafe_routine

I think the function just needs to be marked as DETERMINISTIC.

@theory
Copy link
Collaborator

theory commented Apr 18, 2014

Let me know if that fixes it (and if it will work with MySQL), would you?

@doy
Copy link
Author

doy commented Apr 18, 2014

That does fix it for my local setup, but trying to run sqitch on a VM with MySQL installed (before even making this change) gives me the error

# sqitch deploy db:mysql://root@/flipr_test
Adding registry tables to db:mysql://root@/sqitch
ERROR 1064 (42000) at line 5 in file: '/usr/local/share/perl/5.14.2/App/Sqitch/Engine/mysql.sql': 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 '(6)  NOT NULL
                    COMMENT 'Date the project was added to the dat' at line 4
"mysql" unexpectedly returned exit value 1

No idea what might be the problem here. I'll look more into it next week.

@theory
Copy link
Collaborator

theory commented Apr 18, 2014

Sqitch requires MySQL v5.6.4. I don't see anything at line five that would be a problem otherwise. :-(

@theory
Copy link
Collaborator

theory commented Jun 2, 2014

@doy What version of MySQL yields the syntax error?

@theory theory added bug labels Jun 2, 2014
@theory theory added this to the v1.0.0 milestone Jun 2, 2014
@bored-engineer
Copy link

Seeing the issue on 5.5.35-0ubuntu0.12.10.2

@bored-engineer
Copy link

Here's my error log:

> sqitch deploy db:mysql://migrator:blah@127.0.0.1:3306/blah
Adding registry tables to db:mysql://migrator:@127.0.0.1:3306/sqitch
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 5 in file: '/usr/local/lib/perl5/App/Sqitch/Engine/mysql.sql': 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 '(6)  NOT NULL
                    COMMENT 'Date the project was added to the dat' at line 4
"mysql" unexpectedly returned exit value 1

@bored-engineer
Copy link

Interestingly, running the same command again results in:

> sqitch deploy db:mysql://migrator:blah@127.0.0.1:3306/blah
Sqitch requires MySQL 5.6.4 or higher; this is 5.5.35-0ubuntu0.12.10.2

@bored-engineer
Copy link

Upgrading mysql fixed the issue. It seems that the version check occurs after it should, which is the cause of the issue.

@theory
Copy link
Collaborator

theory commented Jun 3, 2014

Odd. Do you still have 5.26 handy, @innoying? If so, what do you see when you deploy to a database for the first time with this patch?

--- a/lib/App/Sqitch/Engine/mysql.pm
+++ b/lib/App/Sqitch/Engine/mysql.pm
@@ -92,6 +92,7 @@ has dbh => (
         my ($dbms, $vnum, $vstr) = $dbh->{mysql_serverinfo} =~ /mariadb/i
             ? ('MariaDB', 50300, '5.3')
             : ('MySQL',   50604, '5.6.4');
+        say "$vnum vs $dbh->{mysql_serverversion}";
         hurl mysql => __x(
             'Sqitch requires {rdbms} {want_version} or higher; this is {have_version}',
             rdbms        => $dbms,

@bored-engineer
Copy link

I do still have it handy. I'll give the patch a try in a few hours once I get off work. If that doesn't work, I've been testing this on a server with nothing else on it, so I can give you root access to try anything you'd like if that would help.

@theory
Copy link
Collaborator

theory commented Jun 3, 2014

Thanks, @innoying.

@bored-engineer
Copy link

Interesting. My lib/App/Sqitch/Engine/mysql.pm installed via brew doesn't match the patch above. I figured it was just out of date (which is is, 0.991) instead of latest. A update via brew fails saying there are no new versions available. Despite this, I added the new line in the appropriate location, and found that it is not executed at all when the error is thrown, however it is on subsequent runs.

For the sake of documentation and people googling the issue (how I found it), I was able to replicate the issue by doing the following:

I spawned up a fresh ubuntu 14.04 64-Bit server. Ran the following commands:

apt-get update
apt-get upgrade
apt-get install mysql-server
mysql -u root -p
mysql> CREATE DATABASE blah;

I set the root password to blah and then created a new sqitch project with:

brew install sqitch_mysql
sqitch --engine mysql init blah
sqitch add Blah -n 'Blah'
sqitch deploy db:mysql://root:blah@127.0.0.1/blah

(Where 127.0.0.1 is the ip of the ubuntu server)
Initial run of the above deploy command results in:

Adding registry tables to db:mysql://root:@127.0.0.1/sqitch
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 5 in file: '/usr/local/lib/perl5/App/Sqitch/Engine/mysql.sql': 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 '(6)  NOT NULL
                    COMMENT 'Date the project was added to the dat' at line 4
"mysql" unexpectedly returned exit value 1

With future runs resulting in:

Sqitch requires MySQL 5.6.4 or higher; this is 5.5.37-0ubuntu0.14.04.1

Dropping the sqitch database recreates the initial error.

@theory theory closed this as completed in 363dbef Jun 3, 2014
@theory
Copy link
Collaborator

theory commented Jun 3, 2014

Thanks for the digging, @innoying. I think 363dbef should fix the issue, properly telling you on first deploy that your version of MySQL isn't supported. Thanks!

theory added a commit that referenced this issue Jun 3, 2014
Because it is, and @doy tells me that this gets it to install properly on
MariaDB. Ref Issue #158.
@theory
Copy link
Collaborator

theory commented Jun 3, 2014

@doy I think that 18551f8 should fix your issue with MariaDB. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants