cakephp / cakephp/phinx

identifying sql error

Open
#1,081 1 comment 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement
Dominant language
PHP
Stars
4.5k
Forks
884
PR merge metrics
No merged PRs in 30d

Description

i'm trying to add mysql column of type SET.

        $this->table('project', ['id' => 'prj_id'])
            ->addColumn('prj_status', 'set', ['default'=>'active'])
            ->create();

yet this gives SQL error, and i'm unable to see anywhere what the assembed sql statement was, even with -vvv commandline option and mysql server general log.

 == 20170427170945 InitDatabase: migrating
 -- createTable('project')
  [PDOException]                                                                                                              
  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corres  
  ponds to your MySQL server version for the right syntax to use near 'NOT NULL DEFAULT 'active', PRIMARY KEY (`prj_id`)) EN  
  GINE = MyISAM CHARACTER SET' at line 1                                                                                      
                                                                                                                              

Exception trace:
 () at vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:338
 PDO->exec() at vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:338
 Phinx\Db\Adapter\PdoAdapter->execute() at vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdap
ter.php:306
...

Suggestions:

  • a) print the assembled sql when exception is thrown
  • b) print the assembed sql when high verbosity is enabled

ps: i added echo $sql to vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:305, just before $this->execute($sql); and the broken sql was:

CREATE TABLE `project` (
 `prj_id` INT(11) NOT NULL AUTO_INCREMENT,
 `prj_status` SET NOT NULL DEFAULT 'active', PRIMARY KEY (`prj_id`)
) ENGINE = MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

ps2: the correct field definition would be:

->addColumn('prj_status', 'set', ['default' => 'active', 'values' => ['active', 'archived']])

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SQL assembly in src/Phinx/Db/Adapter/MysqlAdapter.php around line 305, then follow execution through PdoAdapter.php:338. Reproduce the migration using the SET column example and verify that the assembled SQL is available when the database reports an error or verbosity is enabled.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, php
Domain
database
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.