Author Topic: Syntax error in step 4 of 1.9 => 2.0.2 (Read 3968 times)

twowheeler · April 19, 2008, 06:10:33 am

Looking for suggestions on how to complete the upgrade from 1.9 to 2.0.2. I do not have any 3rd party activity types. I have however imported activity data from outside sources into civicrm, as "Bought tickets" activities. The "Bought tickets" activity type is registered as a valid activity type. That seems to be what it is choking on now.

Quote

mysql> SELECT DISTINCT(activity_type) FROM civicrm_activity_history;
+--------------------+
| activity_type |
+--------------------+
| Event Registration |
| Meeting |
| Email Sent |
| Bought tickets |
+--------------------+
4 rows in set (0.06 sec)

mysql>

Quote

CiviCRM 2.0 Upgrade: Step Four (Activity Upgrade)
Operating in off-line mode.

unrecoverable error
Sorry. A non-recoverable error has occurred.

Return to home page.

Error Details:

Array
(
[callback] => Array
(

=> CRM_Core_Error
[1] => handle
)

Code: [Select]
=> -2 [message] => DB Error: syntax error [mode] => 16 [debug_info] => INSERT INTO civicrm_activity (source_contact_id, source_record_id, activity_type_id, subject, activity_date_time, due_date_time, duration, location, phone_id, phone_number, details, status_id, priority_id, parent_id, is_test) VALUES (5068,1,10,'Alice in Wonderland Jr Fall '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr Fall '07', 2, NULL, NULL, 0) [nativecode=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 '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr' at line 2] [type] => DB_Error [user_info] => INSERT INTO civicrm_activity (source_contact_id, source_record_id, activity_type_id, subject, activity_date_time, due_date_time, duration, location, phone_id, phone_number, details, status_id, priority_id, parent_id, is_test) VALUES (5068,1,10,'Alice in Wonderland Jr Fall '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr Fall '07', 2, NULL, NULL, 0) [nativecode=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 '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr' at line 2] [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info=" INSERT INTO civicrm_activity (source_contact_id, source_record_id, activity_type_id, subject, activity_date_time, due_date_time, duration, location, phone_id, phone_number, details, status_id, priority_id, parent_id, is_test) VALUES (5068,1,10,'Alice in Wonderland Jr Fall '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr Fall '07', 2, NULL, NULL, 0) [nativecode=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 '07','2007-11-02 19:30:00', NULL, NULL, NULL, NULL, NULL, 'Alice in Wonderland Jr' at line 2]"] ) [/quote]

Matt2000 · April 20, 2008, 02:25:46 am

The problem is the quotation mark in your field: Alice in Wonderland Jr Fall '07

If you can get rid of that punctuation in all rows, you should be fine.

twowheeler · April 20, 2008, 04:17:56 pm

Thank you for the pointer, that was very helpful. A little sed did the trick.

But now, I have a different error at step 6. Any suggestions?

Quote

CiviCRM 2.0 Upgrade: Step Six (Upgrade Miscellaneous Data)
Operating in off-line mode.

unrecoverable error
Sorry. A non-recoverable error has occurred.

Database Error Code: Error on rename of './civicrm2/civicrm_mailing' to './civicrm2/#sql2-6c21-29' (errno: 152), 1025

Return to home page.

Error Details:

Array
(
[callback] => Array
(

=> CRM_Core_Error
[1] => handle
)

Code: [Select]
=> -1 [message] => DB Error: unknown error [mode] => 16 [debug_info] => ALTER TABLE `civicrm_mailing` MODIFY `reply_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the auto-responder component.', MODIFY `unsubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the unsubscribe component.', MODIFY `resubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT '', MODIFY `optout_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the opt-out component.', DROP FOREIGN KEY `FK_civicrm_mailing_footer_id`, DROP FOREIGN KEY `FK_civicrm_mailing_header_id`, DROP FOREIGN KEY `FK_civicrm_mailing_msg_template_id`, DROP FOREIGN KEY `FK_civicrm_mailing_optout_id`, DROP FOREIGN KEY `FK_civicrm_mailing_reply_id`, DROP FOREIGN KEY `FK_civicrm_mailing_unsubscribe_id` [nativecode=1025 ** Error on rename of './civicrm2/civicrm_mailing' to './civicrm2/#sql2-6c21-29' (errno: 152)] [type] => DB_Error [user_info] => ALTER TABLE `civicrm_mailing` MODIFY `reply_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the auto-responder component.', MODIFY `unsubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the unsubscribe component.', MODIFY `resubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT '', MODIFY `optout_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the opt-out component.', DROP FOREIGN KEY `FK_civicrm_mailing_footer_id`, DROP FOREIGN KEY `FK_civicrm_mailing_header_id`, DROP FOREIGN KEY `FK_civicrm_mailing_msg_template_id`, DROP FOREIGN KEY `FK_civicrm_mailing_optout_id`, DROP FOREIGN KEY `FK_civicrm_mailing_reply_id`, DROP FOREIGN KEY `FK_civicrm_mailing_unsubscribe_id` [nativecode=1025 ** Error on rename of './civicrm2/civicrm_mailing' to './civicrm2/#sql2-6c21-29' (errno: 152)] [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_mailing` MODIFY `reply_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the auto-responder component.', MODIFY `unsubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the unsubscribe component.', MODIFY `resubscribe_id` int(10) unsigned NULL DEFAULT NULL COMMENT '', MODIFY `optout_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'FK to the opt-out component.', DROP FOREIGN KEY `FK_civicrm_mailing_footer_id`, DROP FOREIGN KEY `FK_civicrm_mailing_header_id`, DROP FOREIGN KEY `FK_civicrm_mailing_msg_template_id`, DROP FOREIGN KEY `FK_civicrm_mailing_optout_id`, DROP FOREIGN KEY `FK_civicrm_mailing_reply_id`, DROP FOREIGN KEY `FK_civicrm_mailing_unsubscribe_id` [nativecode=1025 ** Error on rename of './civicrm2/civicrm_mailing' to './civicrm2/#sql2-6c21-29' (errno: 152)]"] ) [/quote]

Matt2000 · April 20, 2008, 04:31:19 pm

First try running it again, but watch 'top' for activity from mysqld, and wait until it's stopped before proceeding with step six.

If it still errors, check your id columns for any invalid values, like 0 or NULL.

A query like this might help find problem rows:

SELECT * FROM civicrm_mailing m LEFT JOIN civicrm_mailing_event_reply r ON m.reply_id = r.id WHERE r.id IS NULL;

If this returns any rows, fix or delete them.

...repeat for unsubscribe, opt-out, opt-out and resubscribe (looks like the first 2 both key to _event_unsubscribe table, and I'd guess resub keys to _event_subscribe)

twowheeler · April 20, 2008, 05:26:57 pm

First, thanks for helping.

I reran the whole thing and waited for mysqld to stop before going to the next step, but it ends with the same error.

I also browsed civicrm_mailing and civicrm_mailing_event_* looking for 0 or NULL ids, but found none.

The sql statement you wrote finds zero rows.

Any suggestions?

Matt2000 · April 20, 2008, 05:36:29 pm

Did you try the LEFT JOIN query on the other keys?

E.g.,

SELECT * FROM civicrm_mailing m LEFT JOIN civicrm_mailing_event_unsubscribe r ON m.unsubscribe_id = r.id WHERE r.id IS NULL;

SELECT * FROM civicrm_mailing m LEFT JOIN civicrm_mailing_event_unsubscribe r ON m.optout_id = r.id WHERE r.id IS NULL;

Actually, those should cover it. The description of resubscribe_ID doesn't say it's a FK. But I'm just guessing on all this. I'm far from being a expert DB admin.

Other than that, I'm not sure. Maybe lobo or deepak will jump in with something more.

twowheeler · April 20, 2008, 06:12:33 pm

Yes, I did that, with the same result.

There is a discussion of this error here:
http://dev.mysql.com/doc/refman/4.1/en/alter-table-problems.html

Quote

Error on rename the table occurs also when you try to drop a primary key from the InnoDB table that is referenced by other tables (i.e. there exists a foreign key constraint that references the primary key you want to drop.) It's a pity that mysql doesn't report this error in more user friendly way.

I am wondering if this is it. Still, if that was the problem I would think that others would have encountered it.

Deepak Srivastava · April 22, 2008, 02:27:57 am

Have you tried rebuilding your db to ensure schema integrity - http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0#UpgradeDrupalSitesto2.0-4.Rebuildyour1.9DatabasetoEnsureSchemaIntegrity ?

Since dropping a non existing constraint also results in this kind of error.

twowheeler · April 23, 2008, 05:12:12 am

Ok, I got it. I went back to the production server and got a fresh mysqldump file to work with. This time it worked fine. I don't know exactly what the issue was, but I may have entered the commands incorrectly. Thanks to all.

davej · April 29, 2008, 04:16:07 am

I've filed a JIRA issue for the original syntax error:
2.0 activity upgrade Step 4 fails with SQL syntax error due to unescaped quotes

CiviCRM Community Forums (archive)

News:

Author Topic: Syntax error in step 4 of 1.9 => 2.0.2 (Read 3968 times)

twowheeler

Syntax error in step 4 of 1.9 => 2.0.2

Matt2000

Re: Syntax error in step 4 of 1.9 => 2.0.2

twowheeler

Re: Syntax error in step 4 of 1.9 => 2.0.2

Matt2000

Re: Syntax error in step 4 of 1.9 => 2.0.2

twowheeler

Re: Syntax error in step 4 of 1.9 => 2.0.2

Matt2000

Re: Syntax error in step 4 of 1.9 => 2.0.2

twowheeler

Re: Syntax error in step 4 of 1.9 => 2.0.2

Deepak Srivastava

Re: Syntax error in step 4 of 1.9 => 2.0.2

twowheeler

Re: Syntax error in step 4 of 1.9 => 2.0.2

davej

Re: Syntax error in step 4 of 1.9 => 2.0.2