This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

[propel-build-sql] not going into mysql

Discussion around this tutorial for symfony 1.0.x

[propel-build-sql] not going into mysql

by lafber » Wed Mar 29, 2006 12:13 am

Config : windows XP with XAMPP
Symfony 0.7.1048 (just upgraded from beta).
MySQL 5.0.18

I'm getting a lot of trouble and still unable to pass day tree. I've been going back to svn day 1 and I'm trying to do day 2.

I use the xml file given in day 2 to build the schema, model and sql.

Going in phpmyadmin after creating my database I import the schema.sql file and get the following error :


#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,
`user_id` INTEGER default NOT NULL,
`created_at` DATETIME,
PRIMARY' at line 3


Here is my schema.sql file

Code: Select all

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

#-----------------------------------------------------------------------------
#-- ask_answer
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ask_answer`;


CREATE TABLE `ask_answer`
(
   `id` INTEGER  NOT NULL AUTO_INCREMENT,
   `question_id` INTEGER,
   `user_id` INTEGER,
   `body` TEXT,
   `created_at` DATETIME,
   PRIMARY KEY (`id`),
   KEY `ask_answer_FI_1`(`question_id`),
   KEY `ask_answer_FI_2`(`user_id`),
   CONSTRAINT `ask_answer_FK_1`
      FOREIGN KEY (`question_id`)
      REFERENCES `ask_question` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT,
   CONSTRAINT `ask_answer_FK_2`
      FOREIGN KEY (`user_id`)
      REFERENCES `ask_user` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ask_interest
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ask_interest`;


CREATE TABLE `ask_interest`
(
   `question_id` INTEGER default  NOT NULL,
   `user_id` INTEGER default  NOT NULL,
   `created_at` DATETIME,
   PRIMARY KEY (`question_id`,`user_id`),
   KEY `ask_interest_FI_2`(`user_id`),
   CONSTRAINT `ask_interest_FK_1`
      FOREIGN KEY (`question_id`)
      REFERENCES `ask_question` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT,
   CONSTRAINT `ask_interest_FK_2`
      FOREIGN KEY (`user_id`)
      REFERENCES `ask_user` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ask_question
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ask_question`;


CREATE TABLE `ask_question`
(
   `id` INTEGER  NOT NULL AUTO_INCREMENT,
   `user_id` INTEGER,
   `title` TEXT,
   `body` TEXT,
   `created_at` DATETIME,
   `updated_at` DATETIME,
   PRIMARY KEY (`id`),
   KEY `ask_question_FI_1`(`user_id`),
   CONSTRAINT `ask_question_FK_1`
      FOREIGN KEY (`user_id`)
      REFERENCES `ask_user` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ask_relevancy
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ask_relevancy`;


CREATE TABLE `ask_relevancy`
(
   `answer_id` INTEGER default  NOT NULL,
   `user_id` INTEGER default  NOT NULL,
   `score` INTEGER,
   `created_at` DATETIME,
   PRIMARY KEY (`answer_id`,`user_id`),
   KEY `ask_relevancy_FI_2`(`user_id`),
   CONSTRAINT `ask_relevancy_FK_1`
      FOREIGN KEY (`answer_id`)
      REFERENCES `ask_answer` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT,
   CONSTRAINT `ask_relevancy_FK_2`
      FOREIGN KEY (`user_id`)
      REFERENCES `ask_user` (`id`)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)Type=InnoDB;

#-----------------------------------------------------------------------------
#-- ask_user
#-----------------------------------------------------------------------------

DROP TABLE IF EXISTS `ask_user`;


CREATE TABLE `ask_user`
(
   `id` INTEGER  NOT NULL AUTO_INCREMENT,
   `nickname` VARCHAR(50),
   `first_name` VARCHAR(100),
   `last_name` VARCHAR(100),
   `created_at` DATETIME,
   PRIMARY KEY (`id`)
)Type=InnoDB;

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;



The "`question_id` INTEGER default NOT NULL," seems problematic no ?

Why the PRIMARY KEY is missing despite it is define in the schema.xml :

<column name="question_id" type="INTEGER" required="true" primaryKey="true" default="">
<vendor type="mysql">
<parameter name="Field" value="question_id"/>
<parameter name="Type" value="int(11)"/>
<parameter name="Null" value="NO"/>
<parameter name="Key" value="PRI"/>
<parameter name="Default" value=""/>
<parameter name="Extra" value=""/>
</vendor>
</column>


In 0.6 I did import the database from this file with success.
lafber
Junior Member
 
Posts: 26
Joined: Fri Mar 03, 2006 12:00 am
Location: France -Asnières

Re: [propel-build-sql] not going into mysql

by Mascot » Wed Jul 16, 2008 7:19 am

Yeah, I seem to be having the same problem. It has to do with a default being set on the INTEGER type'd field. I'm not sure if it's Propel that just started catching the blank defaults or if it's mysql that (may have been updated without my knowledge) is causing the problem.
Mascot
Junior Member
 
Posts: 14
Joined: Tue Jul 08, 2008 7:07 am

Re: [propel-build-sql] not going into mysql

by immortal » Tue Aug 19, 2008 8:11 am

I just ran into this same issue and wanted to leave some help for the next person -- you need to have a default value defined when you create via SQL.

Code: Select all
`element_id` INTEGER default NOT NULL


needs to be something along the lines of:

Code: Select all
`element_id` INTEGER default 0 NOT NULL
.

Neat.
immortal
Junior Member
 
Posts: 3
Joined: Mon Aug 11, 2008 9:40 pm