"Data too long for column 'country' at row1 when using table_name"

Question

I am using cmd to import a table_name.sql file into db2 from UPLOADS sub-folder in the secure_priv path C:\ProgramData using the code below:

mysqlimport -uroot -p ecm2 "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\table_name.sql"

This table was created in db1 using the following CREATE TABLE and INSERT statements:

CREATE TABLE *table_name* (country varchar(20), country_long VARCHAR(100), Name VARCHAR(100) DEFAULT NULL, gid_no VARCHAR(50) NOT NULL PRIMARY KEY,
    cap DOUBLE,
    lat DOUBLE,
    lng DOUBLE,
    p_f VARCHAR(50),
    o_f1 VARCHAR(50) DEFAULT NULL,
    o_f2 VARCHAR(50) DEFAULT NULL,
    o_f3 VARCHAR(50) DEFAULT NULL,
    c_year VARCHAR(75),
    owner VARCHAR(100),
    source VARCHAR(50),
    url VARCHAR(200),
    gl_source VARCHAR(50),
    wepp_id DOUBLE,
    year_data DOUBLE,
    gen13 VARCHAR(20) DEFAULT NULL,
    gen14 VARCHAR(20) DEFAULT NULL,
    gen15 VARCHAR(20) DEFAULT NULL,
    gen16 VARCHAR(20) DEFAULT NULL,
    gen17 VARCHAR(20) DEFAULT NULL,
    total_est VARCHAR(20) DEFAULT NULL);

INSERT INTO *table_name* VALUES ('AFG', 'Afghanistan', 'lorem ipsum text', 'GEODB100538', 33.0, 33.0, 66.00, 'type1', '', '', '', '', '', 'GEODB', 'http://comingshortly.com', 'XXODB', 1009793, 2017, '', '', '', '', '', ''
),('AFG', 'Afghanistan', 'lorem ipsum text 2', 'GEODB1040541', 66.0, 55.0, 69.5, 'type2', '', '', '', '', '', 'XXODB', 'http://comingshortly.com', 'GEODB', 1009795, 2017, '', '', '', '', '', ''
);

Table in db1 was exported as .sql file using the mysqldump command in CMD. Pls note I am now trying to import this .sql file into db2 that has a table with same name and schema. When I open the .sql file with Notepad++ I see it does specify the engine and charset specs at the end of CREATE TABLE statement as follows.

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Database and table_name specified in mysqlimport command (uptop) already exist but the import command fails to import the table with the error: Error: 1406, Data too long for column 'country' at row 1, when using table: table_name

'country' happens to be the first column in the table. Values in 'country' col are barely 3 or 4 characters with col length set to VARCHAR(20) while creating the table that was exported to table_name.sql file. The db and table where I am trying to import also had the same col dtype and length specs i.e. VARCHAR(20) Upon experiencing error 1406 I tried changing the col name in the table (not import file) to TEXT from VARCHAR(20). Now it gives a new error: Error: 1261, Row 1 doesn't contain data for all columns, when using table: table_name

FYI- I am using a std installation of MySQL server 8.0.29 on Win 10 machine.

HOW DO I FIX THIS ERROR?

Please [edit your question](https://stackoverflow.com/posts/72847296/edit) to show the `CREATE TABLE` statement and the first `INSERT` statement. — kmoser, Jul 04 '22 at 18:53
`LONG DOUBLE,`? How can you use this? `LONG` is a reserved keyword — Asgar, Jul 05 '22 at 13:51
@Asgar it's actually `lng DOUBLE`. Kindly ignore the typo as the server is running on another PC and I had to type the code manually. — Sachin D, Jul 05 '22 at 13:57

score 0 · Answer 1 · answered Jul 05 '22 at 13:56

0

Do refer to this Post.

If this doesn't help, try disabling innodb strict mode to false with SET GLOBAL innodb_strict_mode=0

answered Jul 05 '22 at 13:56

Asgar

1,920
2
8
17

I tried setting `GLOBAL.sql_mode=' ';` and 'TRADITIONAL' That threw a different error altogether. Error 1290. Is innodb_strict_mode=0 something v different? These modes are ususaly required if we try to insert a different data type or length. Doesn't seem to be the case here though. – Sachin D Jul 05 '22 at 14:10
I had faced similar issues earlier, but the thing is I tried them either on MariaDB or MySQL 5.6. Maybe this has to take something with version 8 of MySQL – Asgar Jul 05 '22 at 14:19
Using `SET GLOBAL innodb_strict_mode=0` I ran into Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: table_name. So, I tried changing `secure_file_priv=''` in the my.ini file and restarted the server. Now I get this: "'C:\ProgramData\MySQL\MySQL Server 8.0\Data\ecm2\table_name' not found (OS errno 2 - No such file or directory), when using table: table_name". Same error msg if I place the .sql file in another folder on local drive and provide the new path in the mysqlimport command. – Sachin D Jul 05 '22 at 14:32

"Data too long for column 'country' at row1 when using table_name"

1 Answers1