Doctrine 1

DataDict entry missing for datetime type for MySQL causes migrations to fail due to sql error

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Invalid
  • Affects Version/s: 1.2.0, 1.2.1
  • Fix Version/s: None
  • Component/s: Migrations
  • Labels:
    None
  • Environment:
    LAMP

Description

I discovered this whilst trying out migrations via symfony. I added a datetime field to my schema.yml and generated the migrations, but upon running the migration I got the following error:

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 '()' at line 1. Failing Query: "ALTER TABLE order_item ADD purchased_at datetime()"

The following code causes the failure in the actual migration:

$this->addColumn('order_item', 'purchased_at', 'datetime', '', array());

because it generates the following sql:

ALTER TABLE order_item ADD purchased_at datetime()

The diff from my patched version which fixes the issue is as follows:

Index: Doctrine/DataDict/Mysql.php
===================================================================
— Doctrine/DataDict/Mysql.php (revision 7415)
+++ Doctrine/DataDict/Mysql.php (working copy)
@@ -227,6 +227,7 @@
return 'DATE';
case 'time':
return 'TIME';
+ case 'datetime':
case 'timestamp':
return 'DATETIME';
case 'float':

It's against the following repository file:

http://doctrine.mirror.svn.symfony-project.com/branches/1.2/lib/Doctrine/DataDict/Mysql.php

I hope this is useful and gets committed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Rich Birch added a comment -

I've just discovered that the same issue exists for fields of type 'text':

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 ') NOT NULL, session_time DATETIME NOT NULL, INDEX session_id_index_idx (session_' at line 1. Failing Query: "CREATE TABLE session (id BIGINT AUTO_INCREMENT, session_id VARCHAR(64) NOT NULL, session_data text() NOT NULL, session_time DATETIME NOT NULL, INDEX session_id_index_idx (session_id), PRIMARY KEY(id)) ENGINE = INNODB"

from the following schema:

Session:
columns:
session_id:

{ type: string(64), notnull: true }

session_data:

{ type: text, notnull: true }

session_time:

{ type: timestamp, notnull: true }

indexes:
session_id_index:
fields: [ session_id ]
unique: true

I guess there may be other field entries missing too. Is there a comprehensive list of doctrine field types somewhere?

Show
Rich Birch added a comment - I've just discovered that the same issue exists for fields of type 'text': 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 ') NOT NULL, session_time DATETIME NOT NULL, INDEX session_id_index_idx (session_' at line 1. Failing Query: "CREATE TABLE session (id BIGINT AUTO_INCREMENT, session_id VARCHAR(64) NOT NULL, session_data text() NOT NULL, session_time DATETIME NOT NULL, INDEX session_id_index_idx (session_id), PRIMARY KEY(id)) ENGINE = INNODB" from the following schema: Session: columns: session_id: { type: string(64), notnull: true } session_data: { type: text, notnull: true } session_time: { type: timestamp, notnull: true } indexes: session_id_index: fields: [ session_id ] unique: true I guess there may be other field entries missing too. Is there a comprehensive list of doctrine field types somewhere?
Hide
Permalink
Rich Birch added a comment -

Ok, I might have been being dumb here. I've just checked the doctrine documentation for defining the schema (http://www.doctrine-project.org/documentation/manual/1_2/en/defining-models) and there's no mention of a datetime or text field (I've just realised that I should have used string instead of text anyway), but datetime still works as a column type so shouldn't it be documented?

I guess either datetime should be fully removed or fully supported

Show
Rich Birch added a comment - Ok, I might have been being dumb here. I've just checked the doctrine documentation for defining the schema ( http://www.doctrine-project.org/documentation/manual/1_2/en/defining-models ) and there's no mention of a datetime or text field (I've just realised that I should have used string instead of text anyway), but datetime still works as a column type so shouldn't it be documented? I guess either datetime should be fully removed or fully supported
Hide
Permalink
Jonathan H. Wage added a comment -

You should be using the Doctrine portable types. So you would use date or timestamp I believe and Doctrine will convert it to the appropriate type for your dbms.

Show
Jonathan H. Wage added a comment - You should be using the Doctrine portable types. So you would use date or timestamp I believe and Doctrine will convert it to the appropriate type for your dbms.
Jonathan H. Wage made changes -
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Resolution Invalid [ 6 ]
Hide
Permalink
ToleaN added a comment -

not correctly generated migration, in my case generated:

$this->addColumn('tree', 'published_at', 'datetime', '', array(
));

but if change the fourth parameters on null, all ok

Show
ToleaN added a comment - not correctly generated migration, in my case generated: $this->addColumn('tree', 'published_at', 'datetime', '', array( )); but if change the fourth parameters on null, all ok
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-582, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Rich Birch
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: