I have two supposedly identical server environments, one for stage/test and one for production archiving.  I’ve gone through one project wherein I loaded data extracts from teradata into 4 tables, many millions of rows, no problem.  I used simple Load Data Infile scripts.

I have another project, a new table, that worked just fine in my stage environment.  I copied the schema and data files to the production environment.  When I tried to load via scripts, I got this error:

mysql> source LOAD_CENSUS_STG_MYSQL.sql
Database changed
ERROR 5 (HY000): Unknown error.

This kind of threw me for a loop as this exact thing had worked on the other server.  I fiddled around, then tried to do a simple 1-row insert into my newly created table and got the following error:

mysql> Insert into CENSUS_STG_MYSQL (TOTAL_ID, CHANNEL_ID, COUNTRY_ID, STORE, DEPT, TRAN_SEQ_NO, REGISTER, TRAN_NO, ITEM_SEQ_NO, ITEM, NON_MERCH_ITEM, NET_RETAIL, MTH_IDNT, YR_IDNT, BUSINESS_DATE) VALUES (‘333’, ‘222’, ‘11’, 44, 2, ‘12345’, ‘REGISTER’, ‘99’, ‘1’, ‘TEST ITEM’, ‘TEST NONMERCH’, 19.95, ‘200901’, ‘2009’, ‘20090101’);
ERROR 1031 (HY000): Table storage engine for ‘CENSUS_STG_MYSQL’ doesn’t have this option

Any clues?

Hi rprince,

Please provide the DDL for your table CENSUS_STG_MYSQL and the contents of your LOAD_CENSUS_STG_MYSQL.sql it would be helpful.

... Bob

Hi !

Are you using ICE? ICE does not support INSERT

Here is my table schema and the load script.  As mentioned, this works just fine on one system and on the other system, gets the error:
mysql> source LOAD_CENSUS_STG_MYSQL.sql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
ERROR 5 (HY000): Unknown error.

use rdw10dm;\

drop table CENSUS_STG_MYSQL;

CREATE TABLE CENSUS_STG_MYSQL
(  TOTAL_ID varchar(10) NOT NULL,
CHANNEL_ID int(11) NOT NULL,
COUNTRY_ID varchar(10)  NOT NULL,
STORE int(11) NOT NULL,
DEPT int(11) NOT NULL,
TRAN_SEQ_NO varchar(25) NOT NULL,
REGISTER varchar(10) DEFAULT NULL,
TRAN_NO varchar(10)  DEFAULT NULL,
ITEM_SEQ_NO varchar(4) NOT NULL,
ITEM varchar(25)  DEFAULT NULL,
NON_MERCH_ITEM varchar(25)  DEFAULT NULL,
NET_RETAIL decimal(18,4) DEFAULT NULL,
MTH_IDNT int(11) NOT NULL,
YR_IDNT int(11) NOT NULL,
BUSINESS_DATE varchar(25)  NOT NULL
)ENGINE=BRIGHTHOUSE DEFAULT CHARSET=ascii COLLATE=ascii_bin

Here is the script for the load file

use rdw10dm_a;

Load data infile ‘/data1/census_stg_mysql_jan2’ into table CENSUS_STG_MYSQL
Fields terminated by ‘^’
Enclosed by ‘“’
Lines terminated by ‘\r\n’;

Actually, just now got this solved - had a SAN switchover a few months ago and my unix admins had butchered my drives.  Thought it was fixed, but hadn’t done any data writes until this exercise.  Found that the permission on my data directory was flubbed.

thanks for the comments