SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    4
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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 '\n'
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    4
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    4
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    "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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •