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

Migration script assumes quote table names are enabled for MySQL #3036

Closed
j0k3r opened this Issue Apr 10, 2017 · 10 comments

Comments

Projects
None yet
6 participants
@j0k3r
Copy link
Member

j0k3r commented Apr 10, 2017

From @shikasta-net on April 10, 2017 10:18

I run Wallabag in docker and was trying to upgrade 2.0.8 to 2.1.6 but had the following problem:

-> ALTER TABLE "wallabag_entry" ADD uuid LONGTEXT DEFAULT NULL
Migration 20160410190541 failed during Execution. Error An exception occurred while executing 'ALTER TABLE "wallabag_entry" ADD uuid LONGTEXT DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 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 '"wallabag_entry" ADD uuid LONGTEXT DEFAULT NULL' at line 1

This was similar to the issue described in issue 2364 but with MySQL. Eventually I realised the issue was with double quotes around the table names. I resolved the upgrade by logging into MySQL and running SET GLOBAL SQL_MODE=ANSI_QUOTES before performing the production migration. Reverting the setting after migration doesn't seem to cause a problem (yet).

I have a common database server for several services so I don't want to globally change behaviour. Could the migration script do a session level SET SQL_MODE=ANSI_QUOTES before running its commands to ensure the migration works as designed?

Copied from original issue: wallabag/docker#62

@j0k3r

This comment has been minimized.

Copy link
Member

j0k3r commented Apr 10, 2017

I wasn't aware of that sql mode .. 🤦‍♂️

@j0k3r

This comment has been minimized.

Copy link
Member

j0k3r commented Apr 10, 2017

From @xsteadfastx on April 10, 2017 12:7

Is that a docker problem?

@j0k3r

This comment has been minimized.

Copy link
Member

j0k3r commented Apr 10, 2017

I don't think so.

@aaa2000

This comment has been minimized.

Copy link
Contributor

aaa2000 commented May 14, 2017

Maybe, could be resolved by use dbal option and PDO::MYSQL_ATTR_INIT_COMMAND

# Doctrine Configuration
doctrine:
    dbal:
        driver: "%database_driver%"
        host: "%database_host%"
        port: "%database_port%"
        dbname: "%database_name%"
        user: "%database_user%"
        password: "%database_password%"
        charset: "%database_charset%"
        path: "%database_path%"
        unix_socket: "%database_socket%"
        server_version: 5.6
        options:
            # PDO::MYSQL_ATTR_INIT_COMMAND
            1002: "SET SQL_MODE=ANSI_QUOTES"
@nicosomb

This comment has been minimized.

Copy link
Member

nicosomb commented May 15, 2017

Is this option available for SQLite / MySQL and PostgreSQL?
@shikasta-net could you try with this option please?

@shikasta-net

This comment has been minimized.

Copy link

shikasta-net commented May 18, 2017

Is that app/config/config.yml that I need to edit, @nicosomb? And add

options:
  # PDO::MYSQL_ATTR_INIT_COMMAND
  1002: "SET SQL_MODE=ANSI_QUOTES"
@nicosomb

This comment has been minimized.

Copy link
Member

nicosomb commented May 19, 2017

Yes, and don't forget to clear your cache: bin/console cache:clear -e=prod

@docljn

This comment has been minimized.

Copy link

docljn commented Aug 17, 2018

Hello @shikasta-net : did that suggestion solve your issue, please?

@shikasta-net

This comment has been minimized.

Copy link

shikasta-net commented Aug 20, 2018

Sorry for the delay. Yes I can confirm that adding

options:
    # PDO::MYSQL_ATTR_INIT_COMMAND
    1002: "SET SQL_MODE=ANSI_QUOTES"

to the end of the doctrine dba1 configuration allows the migration to complete without any other intervention.

@j0k3r j0k3r referenced this issue Aug 20, 2018

Merged

Add common errors #72

@j0k3r

This comment has been minimized.

Copy link
Member

j0k3r commented Aug 20, 2018

Thanks @shikasta-net I've added a new chapter into the documentation with that solution 👍

@j0k3r j0k3r closed this Aug 20, 2018

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