Tried to import a CSV file (350K) but got this error:

user warning: The used command is not allowed with this MySQL version query: LOAD DATA LOCAL INFILE '/usr/local/www/apache22/data/sites/default/files/tw_delimited/Property_DB.csv' INTO TABLE property_db FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' IGNORE 1 LINES in /usr/local/www/apache22/data/sites/all/modules/tw/tw_import/tw_import_delimited.module on line 132.

It loaded the file into the directory but that's all.

I'm using PHP 5.2.8 and MySQL 5.0.67

Comments

dejamuse’s picture

dejamuse commented

It created a table in MySQL which showed up in the dropdown list but it was empty. I then clicked "View Table Contents" and got all these errors:

# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 907.
# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 882.
# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 907.
# user warning: 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 'FROM property_db property_db ) count_alias' at line 2 query: SELECT COUNT(*) FROM (SELECT FROM property_db property_db ) count_alias in /usr/local/www/apache22/data/sites/all/modules/views/includes/view.inc on line 705.
# user warning: 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 'FROM property_db property_db LIMIT 0, 25' at line 2 query: SELECT FROM property_db property_db LIMIT 0, 25 in /usr/local/www/apache22/data/sites/all/modules/views/includes/view.inc on line 731.
dejamuse’s picture

dejamuse commented

I manually imported the CSV file into MySQL with PHPMyAdmin.

Once again lot's of errors when attempting to show the table contents:

# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 907.
# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 882.
# warning: Invalid argument supplied for foreach() in /usr/local/www/apache22/data/sites/all/modules/views/includes/query.inc on line 907.
# user warning: 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 'FROM property_db property_db ) count_alias' at line 2 query: SELECT COUNT(*) FROM (SELECT FROM property_db property_db ) count_alias in /usr/local/www/apache22/data/sites/all/modules/views/includes/view.inc on line 705.
# user warning: 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 'FROM property_db property_db LIMIT 0, 25' at line 2 query: SELECT FROM property_db property_db LIMIT 0, 25 in /usr/local/www/apache22/data/sites/all/modules/views/includes/view.inc on line 731.
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Title: Handle column names with spaces » Handle LOAD DATA errors
Priority: Critical » Normal
Status: Closed (fixed) » Active

Another motivation for #392334: More general import, apparently the LOAD DATA command isn't even universally supported on MySQL...

I'll look at better error handling (cleaning up if the LOAD DATA fails). In the meantime, have you tried doing an Analyze before viewing the data?

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Title: Importing CSV MySQL error » Handle LOAD DATA errors
Assigned: Unassigned » mikeryan
Category: support » bug
dejamuse’s picture

dejamuse commented

Yes, I did try 'analyze' and there were no errors, but not sure what to do next as I could not find any TW documentation.

Subsequently tried displaying the table but same errors resulted.

dejamuse’s picture

dejamuse commented

Not used to seeing 'help' buttons in Drupal, but did find the documentation there.

Still cannot import CSV files after updating the modules today. Same error as before but on line 132. After updating the modules I was able to view the old table's contents in its entirety. But I realized I'd loaded the wrong data CSV file so I deleted the whole table.

I then manually created a new table in PHPMyAdmin and imported the CSV there. I had previously just imported the CSV into the empty table created by the tw wizard. I didn't realize how much manual work had to be done to create a new table from scratch in PHPmyadmin - I have 30 fields, some integer, some varchar. After creating the new table and importing the CSV, everything looked fine in PHPmyadmin.

But for some reason the tw module doesn't like my new table and 16 of the fields didn't make it, though all the column headers did. The analysis says those fields contain no data. No rhyme or reason really why one text field was ok and another wasn't.

Got this error for each of the failed fields:

# user warning: 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 'ID) FROM property_db' at line 1 query: SELECT MIN(Unit ID) FROM property_db in /usr/local/www/apache22/data/sites/all/modules/tw/tw.module on line 141.
# user warning: 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 'ID) FROM property_db' at line 1 query: SELECT MAX(Unit ID) FROM property_db in /usr/local/www/apache22/data/sites/all/modules/tw/tw.module on line 143.

So I repeated what I did before. Even though TW reported that it did not create any tables when I tried to import the CSV, one still was created, albeit empty, only visible in PHPmyadmin. So I imported the correct CSV there and voila, TW now can show the entire table's contents.

I also noticed that once you remove a table from the list it disappears from the drop down box for available tables. It reappeared after I flushed every cache.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Title: Handle LOAD DATA errors » Handle column names with spaces

First off, look carefully at the message you got trying to import CSV through the Table Wizard:

The used command is not allowed with this MySQL version

The delimited import uses LOAD DATA LOCAL, which your version of MySQL does not support. Table Wizard needs to support row-by-row import, with LOAD DATA LOCAL only supported as an if-you're-on-MySQL-and-you-know-what-you're-doing-this-is-faster option - there is an existing issue for this (#392334).

Now, for the problem attaching to the table you created directly - I see in the sample error message SELECT MAX(Unit ID)... The problem is the space in the column name - the delimited import will replace all non-alphanumeric characters with underscores, but we still have to deal with this situation with tables created directly in MySQL.

I've changed the title to reflect the issue, and will deal with it by putting backticks around column names.

Thanks.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented

OK, I've committed a fix to the analysis - you can now add and analyze tables that have columns with non-alphanumeric characters in their names. However, the data in those columns does not show up in views - I can't see anything Table Wizard can do about that, it's probably a Views bug - I'll pursue this.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented

And... It just occurred to me that the backtick is MySQL-specific, Postgres uses double-quotes. This needs to be a bit smarter...

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Title: Handle LOAD DATA errors » Handle column names with spaces
Priority: Normal » Critical
Status: Active » Fixed

I've updated the fix to handle Postgres.

That's all that can be done on this side - I've submitted a partial fix to Views (#410452: Support non-standard field/table names) to address the viewing issue.

Status: Active » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.