Symfony 1.x propel-insert-sql [propel-sql-exec] SQLSTATE[42000]: Syntax error or access violation: 1064

Posted on by

You are using a old version of symfony 1.x on a new version of mysql and when you try to insert the model on your database you get this error :

[propel-sql-exec] 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 ‘Type=InnoDB’ at line 6

The problem come from this query

CREATE TABLE `pret_externe_type`
 (
 `id_pret_externe_type` BIGINT(20)  NOT NULL AUTO_INCREMENT,
 `libelle` VARCHAR(100)  NOT NULL,
 PRIMARY KEY (`id_pret_externe_type`)
 )Type=InnoDB

Mysql do not use “Type=” option anymore, you have to replace it by “Engine=”

CREATE TABLE `pret_externe_type`
 (
 `id_pret_externe_type` BIGINT(20)  NOT NULL AUTO_INCREMENT,
 `libelle` VARCHAR(100)  NOT NULL,
 PRIMARY KEY (`id_pret_externe_type`)
 )Engine=InnoDB

To fix it the code, on symfony 1.2 go on this file /lib/plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/builder/sql/mysql/MysqlDDLBuilder.php and change the line 156
from

 $script .= "Type=$mysqlTableType";

to 

 $script .= "Engine=$mysqlTableType";

On symfony 1.4, you have to change this file : lib/vendor/symfony/lib/plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/builder/sql/mysql/MysqlDDLBuilder.php
Always on ligne 156

After code change, rebuild your SQL model and insert it to your databases

php symfony propel-build-sql
php symfony propel-insert-sql

Leave a Reply