I'm trying to import a CSV file with user information, which I will then use to create user accounts with content profile fields, using the Migrate module (the documentation is unclear on how to do so, but I'm trying to figure this out step by step).

In any case, when using the Table Wizard to import the CSV, I get the following error:

user warning: BLOB/TEXT column 'ID' can't have a default value query: CREATE TABLE userimporting ( `ID` LONGTEXT DEFAULT '', `username` LONGTEXT DEFAULT '', `email` LONGTEXT DEFAULT '', `Extension` LONGTEXT DEFAULT '', `Full_Name` LONGTEXT DEFAULT '', `First` LONGTEXT DEFAULT '', `Last` LONGTEXT DEFAULT '', `Job_Title` LONGTEXT DEFAULT '', `Dept` LONGTEXT DEFAULT '', `Division` LONGTEXT DEFAULT '', `Orig_Start` LONGTEXT DEFAULT '' ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in C:\xampp\htdocs\inSite\includes\database.inc on line 515.

Then every further record import fails because the table creation did not work, like so:

C:\xampp\htdocs\inSite\includes\database.inc on line 515.
user warning: Table 'insite.userimporting' doesn't exist query: INSERT INTO userimporting (ID, username, email, Extension, Full_Name, First, Last, Job_Title, Dept, Division, Orig_Start) VALUES('1', 'aschwartz', 'aschwartz@eminnetonka.com', '8422', 'Aaron Schwartz', 'Aaron', 'Schwartz', 'Natural Resources Specialist', 'Public Works', 'Natural Resources', '2005-11-21 00:00:00') in C:\xampp\htdocs\inSite\sites\all\modules\contributed\tw\tw_import\tw_import_delimited.module on line 243.
user warning: Table 'insite.userimporting' doesn't exist query: INSERT INTO userimporting (ID, username, email, Extension, Full_Name, First, Last, Job_Title, Dept, Division, Orig_Start) VALUES('2', 'awiemiller', 'awiemiller@eminnetonka.com', '', 'Aaron Wiemiller', 'Aaron', 'Wiemiller', 'Police Officer', 'Police', '', '2004-07-19 00:00:00') in C:\xampp\htdocs\inSite\sites\all\modules\contributed\tw\tw_import\tw_import_delimited.module on line 243.

Further on, I get the following errors:

Field event_timezones.offset: no Schema type for mysql type time.
Field event_timezones.offset_dst: no Schema type for mysql type time.
Could not analyze userimporting (connection default)

Here's some sample data from the CSV if that helps to narrow down the problems:

ID,username,email,Extension,Full Name,First,Last,Job Title,Dept,Division,Orig Start
1,aschwartz,aschwartz@eminnetonka.com,8422,Aaron Schwartz,Aaron,Schwartz,Natural Resources Specialist,Public Works,Natural Resources,2005-11-21 00:00:00
12,adavy,adavy@eminnetonka.com,8357,Ann Davy,Ann,Davy,Recreation Program Division Manager,Recreation,,2005-02-07 00:00:00
13,apetersen,apetersen@eminnetonka.com,8402,Ann Peterson,Ann,Peterson,Part-time Secretary/Receptionist,Public Works,Street Maintenance,2005-05-04 00:00:00

I appreciate any assistance you might be able to provide.

Comments

mikeryan’s picture

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

What's your MySQL version? Drupal core? This sounds like issues seen in the Schema module (#458828: difference on: default - appropriate action?, #256826: Comparison mismatch when default is NULL) that I haven't been able to reproduce in my own environment (Drupal 6.11, MySQL 5.0.41).

Thanks.

rainbowarray’s picture

rainbowarray
she/her
Primary language English
Location Twin Cities, Minnesota
commented

I found another way to handle my problem by using the user import module, followed by the node import module for profile information.

It was Drupal 6.11, and I'm using XAMPP, so I'll check which version of MySQL on Monday if that would be useful for further troubleshooting to help others.

thanks!

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Active » Closed (fixed)

I haven't reproduced this

cjdavis’s picture

cjdavis commented
Status: Closed (fixed) » Needs review

I can confirm this error, and have found a solution. I have two systems on which I am working with table wizard / migrate:

1. LAMP - Apache with MySQL 5.0.51a-3ubuntu5.4 - CSV import works (at least I'm not seeing this error)
2. WIMP - IIS with MySQL 5.1.32-community-log - consistently getting this error -

user warning: BLOB/TEXT column 'user_id' can't have a default value query: CREATE TABLE drupal_customer_data_first_25_customers ( `user_id` LONGTEXT DEFAULT '', `title` LONGTEXT DEFAULT '', `first_nm` LONGTEXT DEFAULT '', `last_nm` LONGTEXT DEFAULT '', `email` LONGTEXT DEFAULT '', `password` LONGTEXT DEFAULT '', `type` LONGTEXT DEFAULT '', `fields` LONGTEXT DEFAULT '', `applications` LONGTEXT DEFAULT '', `registration_date` LONGTEXT DEFAULT '' ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in C:\XXXXl\includes\database.inc on line 517.

A bit of the google points to this being a MySQL on Windows default installation configuration issue:
http://bugs.mysql.com/bug.php?id=25520
and affects any MySQL table using a text field with a default of '':
#310294: Installation Warning
#143881: MySQL: BLOB/TEXT column 'xxx' can't have a default value

Removing the default at line 121 of tw/tw_import/tw_import_delimited.module appears to have solved this for me:

      // Default to big text fields, which can hold anything
      $schema['fields'][$colname] = array(
          'type' => 'text',
          'size' => 'big',
          'not null' => FALSE,
          //'default' => '',
      );
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Needs review » Fixed

Committed, thanks!

Status: Fixed » Closed (fixed)

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

ClearXS’s picture

ClearXS commented
Status: Closed (fixed) » Needs review

Field event_timezones.offset: no Schema type for mysql type time.
Field event_timezones.offset_dst: no Schema type for mysql type time.

=> I have these errors always on admin/reports/status and on /admin/build/schema together with:
# Field storage_object.md5: no Schema type for mysql type binary.
# Field storage_object.whirlpool: no Schema type for mysql type binary.

I always have the latest modules upgraded, for tw is that the 1.3 version of Jul-21-2010; the latest and same date as of the dev version.

I suppose these errors are not part of the problem mentioned here and should I file at Event and Storage modules?

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Pittsfield, MA, USA
commented
Status: Needs review » Closed (fixed)

The "needs review" status is for when you've submitted a patch that needs review. In general, closed issues should not be reopened anyway - open a new issue.

Those are messages produced by the Schema module and can be suppressed on the Schema Settings page. They are telling you that those columns have mysql type time and that the Drupal schema does not support that column, which is true. As long as the modules using those tables manages to function properly, it's nothing to be concerned about.

fajrialshah’s picture

fajrialshah commented

i wanna ask about csv delimeter