CiviCRM Community Forums (archive)

In my civicrm database I have uploaded a table with contacts to import into civicrm.  However, when attempting to import the contacts I receive the DB Error: syntax error message.

The query I use in civicrm to import the data (SELECT * FROM test_civicrm.first_import;) runs successfully in mysql and phpmyadmin to select all of the records in that table. 

Below is additional information on the error and what I am running.

Civicrm - 4.2
MySQL - 5.1.41

Database Error Code: 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 'ADD COLUMN _status VARCHAR(32) DEFAULT 'NEW' NOT NUL' at line 2, 1064
Additional Details:
Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => ALTER TABLE
                       ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NULL,
                       ADD COLUMN _statusMsg TEXT,
                       ADD COLUMN _id INT PRIMARY KEY NOT NULL
                               AUTO_INCREMENT [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 'ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NUL' at line 2]
    [type] => DB_Error
    [user_info] => ALTER TABLE
                       ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NULL,
                       ADD COLUMN _statusMsg TEXT,
                       ADD COLUMN _id INT PRIMARY KEY NOT NULL
                               AUTO_INCREMENT [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 'ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NUL' at line 2]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE
                       ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NULL,
                       ADD COLUMN _statusMsg TEXT,
                       ADD COLUMN _id INT PRIMARY KEY NOT NULL
                               AUTO_INCREMENT [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 'ADD COLUMN _status VARCHAR(32)
                            DEFAULT 'NEW' NOT NUL' at line 2]"]
)

So I'm stuck since I don't have a dev environment on the computer I'm on.  But if anyone else wants to take a peek...

The code in question is in CRM/Import/Form/DataSource.php.  The postProcess function calls _prepareImportTable at line 316.  On line 315 it SHOULD be getting $importTableName, but I guess it's not.  Someone with debugging enabled can take it from there.

Based on lines 306-312, I suspect that line 315 wasn't changed when the "hack to prevent multiple tables" was added.  If trut, line 315 should be changed from:
      $importTableName = $this->get('importTableName');

to:
      $importTableName = $this->_params['import_table_name']

...but a) I don't have a way to test that, and b) my understanding of this code without watching it run is pretty shaky.  So I'm not going to write that patch.  cdoctor, if you wanted to try making that edit on a NON-PRODUCTION version of your db and report if it fixes the problem, that would be awesome!

Jon

I'm seeing the same thing. CiviCRM 4.2, PHP 5.3.2, MySQL 5.1.63. Contact import using SQL query, "select * from <table_name>;" (table is in the civicrm db). At first I was getting the same error as cdoctor where the ALTER TABLE was missing a table name. I likewise followed JonGold's suggestion to get the import table name from the _params ($importTableName = $this->_params['import_table_name']) since get('importTableName') was not returning anything, and _params did contain a table name. But that doesn't work either and produces a no such table error.

I can't post the log output because this forum thinks it is an external link, but the message is "DB Error: no such table".

This worked in 4.1. It looks like the table is not being created.

Is this working for anyone or has anyone made any progress on a fix? Thanks!