How to handle 1136 error in MySQL

Hi,

I am trying to load up my new database using MySQL. I am getting “error 1136: count does not match value count”. and none of the tables are getting data. Also five of the link tables have not even been formatted. Any ideas?

I have defined the columns in the tables to be precisely the same as those in the source file.
Here is the script of my commands:

DROP TABLE IF EXISTS excel_table;
CREATE temporary TABLE excel_table (
journalId INT not null,
Preview VARCHAR(255),
month VARCHAR(255),
year VARCHAR(255),
performerId INT not null,
Name VARCHAR(255),
Surname VARCHAR(255),
artsId INT not null,
Art VARCHAR(255),
groupId INT not null,
Groupname VARCHAR(255),
contactId INT not null,
LandLine VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
website VARCHAR(255),
Address1 VARCHAR(255),
Address2 VARCHAR(255),
locationId int not null,
Locale VARCHAR(255),
County VARCHAR(255)
) DEFAULT CHARSET utf8;

LOAD DATA LOCAL INFILE ‘C:/wamp/www/brayarts/allDetails.csv’
INTO TABLE excel_table
CHARACTER SET utf8
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ’

IGNORE 1 LINES;

DROP TABLE IF EXISTS performers;
CREATE TABLE performers (
performerId int NOT NULL AUTO_INCREMENT ,
Name VARCHAR(255),
Surname VARCHAR(255),
primary key (performerId)
) DEFAULT CHARSET utf8;

INSERT INTO performers
SELECT distinctrow NULL, performerId, Name, Surname
FROM excel_table
ORDER BY Surname;

DROP TABLE IF EXISTS journals;
CREATE TABLE journals (
journalId INT not null AUTO_INCREMENT ,
Preview VARCHAR(255),
month VARCHAR(255),
year VARCHAR(255),
primary key (journalId)
) DEFAULT CHARSET utf8;

INSERT INTO journals
SELECT distinctrow NULL, journalId, Preview, Month, Year
FROM excel_table
ORDER BY journalId;

DROP TABLE IF EXISTS arts;
CREATE TABLE arts (
artsId INT not null AUTO_INCREMENT ,
Art VARCHAR(255),
primary key (artsId)
) DEFAULT CHARSET utf8;

INSERT INTO arts
SELECT distinctrow NULL, artsId, Art
FROM excel_table
ORDER BY art;

DROP TABLE IF EXISTS groups;
CREATE TABLE groups (
groupId INT not null AUTO_INCREMENT ,
Groupname VARCHAR(255)
primary key (groupId)
) DEFAULT CHARSET utf8;

INSERT INTO groups
SELECT distinctrow NULL, groupId, groupName
FROM excel_table
ORDER BY groupID;

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
contactId INT not null AUTO_INCREMENT ,
LandLine VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
webSite VARCHAR(255),
Address1 VARCHAR(255),
Address2 VARCHAR(255),
primary key (contactId)
) DEFAULT CHARSET utf8;

INSERT INTO contacts
SELECT distinctrow NULL, contactId, LandLine, mobile, email, webSite, Address1, Address2
FROM excel_table
ORDER BY contactId;

DROP TABLE IF EXISTS Locations;
CREATE TABLE Locations (
LocationId INT not null AUTO_INCREMENT ,
Locale VARCHAR(255),
County VARCHAR(255),
primary key (locationId)
) DEFAULT CHARSET utf8;

INSERT INTO locations
SELECT distinctrow NULL, locationId, locale, county
FROM excel_table
ORDER BY locationId;

DROP TABLE IF EXISTS performers__journals;
CREATE TABLE performers__journals (
Id INT NOT NULL auto_increment,
performerId int NOT NULL ,
journalId int not null,
primary key (performerId, journalId)
) DEFAULT CHARSET utf8;

INSERT INTO performers__journals
SELECT distinctrow NULL, performerId, journalId
FROM excel_table
ORDER BY journalId;

DROP TABLE IF EXISTS journals__Arts;
CREATE TABLE journals__Arts (
Id INT NOT NULL auto_increment,
journalId INT not null,
artsId INT not null,
primary key (journalId, artsId)
) DEFAULT CHARSET utf8;

INSERT INTO journals__Arts
SELECT distinctrow NULL, journalId, artsId
FROM excel_table
ORDER BY journalId;

DROP TABLE IF EXISTS journals__groups;
CREATE TABLE journals__groups (
Id INT NOT NULL auto_increment,
journalId INT not null,
groupId INT not null,
primary key (journalId, groupId)
) DEFAULT CHARSET utf8;

INSERT INTO journals__groups
SELECT distinctrow NULL, journalId, groupId
FROM excel_table
ORDER BY journalId;

DROP TABLE IF EXISTS performers__Arts;
CREATE TABLE journals__Arts (
Id INT NOT NULL auto_increment,
performerId INT not null,
artsId INT not null,
primary key (performerId, artsId)
) DEFAULT CHARSET utf8;

INSERT INTO performers__Arts
SELECT distinctrow NULL, performerId, artsId
FROM excel_table
ORDER BY performerId;

DROP TABLE IF EXISTS performers__groups;
CREATE TABLE journals__groups (
Id INT NOT NULL auto_increment,
performerId INT not null,
groupId INT not null,
primary key (performerId, groupId)
) DEFAULT CHARSET utf8;

INSERT INTO performers__groups
SELECT distinctrow NULL, performerId, groupId
FROM excel_table
ORDER BY performerId;

DROP TABLE IF EXISTS performers__locations;
CREATE TABLE journals__locations (
Id INT NOT NULL auto_increment,
performerId INT not null,
locationId INT not null,
primary key (performerId, locationId)
) DEFAULT CHARSET utf8;

INSERT INTO performers__locations
SELECT distinctrow NULL, performerId, locationId
FROM excel_table
ORDER BY performerId;

DROP TABLE IF EXISTS arts__groups;
CREATE TABLE arts__groups (
Id INT NOT NULL auto_increment,
artsId INT not null,
groupId INT not null,
primary key (artsId, groupId)
) DEFAULT CHARSET utf8;

INSERT INTO arts__groups
SELECT distinctrow NULL, artsId, groupId
FROM excel_table
ORDER BY artsId;

DROP TABLE IF EXISTS arts__locations;
CREATE TABLE arts__locations (
Id INT NOT NULL auto_increment,
artsId INT not null,
locationId INT not null,
primary key (artsId, locationId)
) DEFAULT CHARSET utf8;

INSERT INTO arts__locations
SELECT distinctrow NULL, artsId, locationId
FROM excel_table
ORDER BY artsId;

DROP TABLE IF EXISTS performers__contacts;
CREATE TABLE performers__contacts(
Id INT NOT NULL auto_increment,
performerId INT not null,
contactId INT not null,
primary key performerId, contactId)
) DEFAULT CHARSET utf8;

INSERT INTO performers__contacts
SELECT distinctrow NULL, performerId, contactId
FROM excel_table
ORDER BY performerId;

DROP TABLE IF EXISTS contacts__locations;
CREATE TABLE contacts__locations (
Id INT NOT NULL auto_increment,
performerId INT not null,
contactId INT not null,
primary key performerId, contactId)
) DEFAULT CHARSET utf8;

INSERT INTO contacts__locations
SELECT distinctrow NULL, contactId, locationId
FROM excel_table
ORDER BY contactId;
1136 column count does not match value

the table definition for contacts__locations contains a syntax error and it would not have been created

therefore the 1136 error message would not have been issued for that last INSERT

any other questions?

Thanks for taking the trouble to reply. I am using a screen reader and so I miss some fo these editing mistakes. I found a missing left parenthesis in the contacts__locations definition dn in the preceding one. I fixed these and ran the script again but I still get about 6 1136 and a 1042 and other things. Can you guide me to how to understand these errors and hwo to display the full message when it happens?

Comanna

Rudy.

I don’t know if my last message went through as I don’t see it here anywhere!

As you can see, I am now to SQL ut find it interesting and worthwhile. I will take a look at your book but I am already following Kevin Yank’s tutorial and I have the internet for quick checks on things.

Is the 1135 error a warning of various anomalies in the script or is it specific to one issue? Am I right in thinking that when an error is found it throughs the whole script into chaos?

for the moment I would welcome your comments on any flaws you see in my script and I will o an fix them. Then I will learn what to watch out for in the future!
All the best,

Comanna.

“column count does not match value count” means exactly what it says

the data you are attempting to load into the table does not have the same number of columns as the table

my advice is not to run this as one long script for all your tables, but to process one table at a time – create it, then load it, and fix any errors before proceeding to the next table