I'm inserting a CSV files into MySQL 8.0 using Python 3.7:
bulk_insert_sql = "LOAD DATA LOCAL INFILE filename.csv INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES"
# Execute the query built above
self._cursor.execute(bulk_insert_sql)
I could not then select items by the last column of the table directly in the MySQL cli or MySQL Workbench such as:
SELECT * FROM table_name WHERE column_name = "123";
I printed the inserted table from the MySQL cli and did not notice anything, but in MySQL WorkBench there is a square character representing an unprintable character after the column value. I could select it using LIKE '%123%' though.
I used python cli to confirm the length of the value of the last column of a line in the csv file was 17 chars including the '\n' newline character and 16 after stripping the newline character. The newline should be stripped during the query since the SQL command specifies it as the line delimiter.
I then looked closer at the column value in MySQL cli. The length was specified as 17 characters (but as mentioned above, could only see the expected 16 characters when priting a row). I then used the HEX() function in MySQL to print the hex value. The hex value printed and I converted it using a website HEX to ASCII converter (https://www.rapidtables.com/convert/number/hex-to-ascii.html).
The converted ASCII printed as I expected the column would print if it was normal.
I have removed and re-installed MySQL 8.0 in Centos 7.6.
1) Any ideas about what is going on? 2) What can I do to further understand why that character is there and how to prevent it from being there.
This is a fresh install of Centos 7 and I was inserting bulk data about 15GB worth, and thought maybe I broke MySQL due to the amount of data. However reinstall did not fix the issue. Can anyone see anything I am doing that would cause this?
Here is an example table structure however it's happening in multiple tables:
CREATE TABLE IF NOT EXISTS `uspto`.`APPLICATION` (
`ApplicationID` VARCHAR(20) NOT NULL,
`PublicationID` VARCHAR(20) DEFAULT NULL,
`FileDate` DATE DEFAULT NULL,
`Kind` VARCHAR(2) DEFAULT NULL,
`USSeriesCode` VARCHAR(2) DEFAULT NULL,
`AppType` VARCHAR(45) DEFAULT NULL,
`PublishDate` DATE DEFAULT NULL,
`Title` VARCHAR(2000) DEFAULT NULL,
`Abstract` TEXT DEFAULT NULL,
`ClaimsNum` INT DEFAULT NULL,
`DrawingsNum` INT DEFAULT NULL,
`FiguresNum` INT DEFAULT NULL,
`FileName` VARCHAR(45) NOT NULL,
PRIMARY KEY (`ApplicationID`, `FileName`))
ENGINE = InnoDB;
Pastebin:
https://pastebin.com/VwpzJnc0