Export to GitHub

cakephp-migrations - issue #4

Incorrect from Db schema generation, adding new strange fields during migrate up


Posted on Mar 25, 2008 by Quick Bear

What steps will reproduce the problem?

  1. Using MySQL 5.0.45-Debian_1ubuntu3.3, Ubuntu 7.10, php5, latest cakephp-1.2beta, latest Migrations, DBM2-2.4.1, DBM2_Driver_MySQL-1.4.1

2. Using the following yaml:

UP: create_table: appliances: no_dates name: [string, 64, notnull] description: [string, notnull] DOWN: drop_table: - appliances

What is the expected output? What do you see instead? Creates unexpected field "type varchar(255)"

Comment #1

Posted on Mar 25, 2008 by Quick Bear

I noticed this problem when was trying to move to migrations. I've created full_schema, then dropped all tables and tried to import schema with migration tool. Two tables of 8 failed because of SQL native errors.

[001] Initial Structure ...
    > creating table 'appliances'
    > creating table 'appliances_resource_types'
    > creating table 'consumption_data'

Error: _doQuery: [Error message: Could not execute statement] [Last executed query: CREATE TABLE consumption_data (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT DEFAULT NULL, resource_type_id INT DEFAULT NULL, value DOUBLE DEFAULT NULL, price DOUBLE DEFAULT NULL, default VARCHAR(255) DEFAULT 'modified', type VARCHAR(255) DEFAULT NULL, created DATETIME DEFAULT NULL, modified DATETIME DEFAULT NULL)] [Native code: 1064] [Native message: 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 'default VARCHAR(255) DEFAULT 'modified', type VARCHAR(255) DEFAULT NULL, created' at line 1]

    > creating table 'groups'
    > creating table 'registration_data'
    > creating table 'residence_data'
    > creating table 'residence_types'
    > creating table 'resource_types'
    > creating table 'user_prices'

Error: _doQuery: [Error message: Could not execute statement] [Last executed query: CREATE TABLE user_prices (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT DEFAULT NULL, resource_type_id INT DEFAULT NULL, price DOUBLE DEFAULT NULL, default VARCHAR(255) DEFAULT 'created', type VARCHAR(255) DEFAULT NULL, created DATETIME DEFAULT NULL, modified DATETIME DEFAULT NULL)] [Native code: 1064] [Native message: 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 'default VARCHAR(255) DEFAULT 'created', type VARCHAR(255) DEFAULT NULL, created ' at line 1]

    > creating table 'users'
    > creating table 'users_appliances'

As you can see: DEFAULT and TYPE fields appeared :-(

Cheers, Alex.

Comment #2

Posted on Mar 25, 2008 by Grumpy Ox

Try this:

UP: create_table: appliances: no_dates: true name: [string, 64, notnull] description: [string, notnull] DOWN: drop_table: - appliances

Comment #3

Posted on Mar 25, 2008 by Quick Bear

That works. But I was using code generated my migration. I suggest the schema should be generate more precisely.

Thanks.

Status: Fixed

Labels:
Type-Defect Priority-Medium