MySQL Bugs: #87788: Special characters in first field name when creating table using CSV file

Bug #87788	Special characters in first field name when creating table using CSV file
Submitted:	18 Sep 2017 7:56	Modified:	19 Sep 2017 13:29
Reporter:	Hassan Naqvi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.9	OS:	Microsoft Windows (Microsoft Windows 7 Enterprise Service Pack 1)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\hassan.naqvi\AppData\Roaming\MySQL\Workbench\log\wb.log]

I got this error while importing data from CSV files. But that's not the bug rather its the solution to 'The Bug' mentioned below.

UnicodeDecodeError: 'ascii' codec can't decode byte 0xef in position 1: ordinal not in range(128)
ERROR: Import data file: 'ascii' codec can't decode byte 0xef in position 1: ordinal not in range(128)

SOLUTION TO ABOVE ERROR: 
- Alter table.
- Delete name of first field in the table.
- Re-typed the same name again applied the change.
- Done, when I imported the file no error!

The Bug:
When table is created using csv file '0xef' is added to the beginning of first field name. Now, when that field name is used in any query it shows and error 'unknown column name'.

How to repeat:
Create a new table by importing data from a (UTF-8) csv file.

Hello Hassan,

Thank you for the bug report.
I could not repeat the issue at our end using with MySQL workbench 6.3.9 versions.
Could you please provide repeatable test case (exact steps, sample CSV file, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

sample csv

Attachment: test1.csv (application/vnd.ms-excel, text), 100 bytes.

sample csv

Attachment: test2.csv (application/vnd.ms-excel, text), 102 bytes.

sample csv

Attachment: test3.csv (application/vnd.ms-excel, text), 105 bytes.

Steps to recreate:
=================

First File:
==========

- Right click on schema name and select Table Data Import Wizard

- Enter CSV file path (test1.csv) or select using Browse button. Press Next

- On the next screen, Select Create New Table. I didn't change given table name.

- Next, Next, Next, Finished.

Second File:
=========== 

- Right-clicked on newly created table's name and selected Table Data Import Wizard from popup menu.

- Enter CSV file path (test2.csv) or select using Browse button. Press Next

- On the next screen, Select Use existing table (test1). I didn't change given table name.

- Next, Next, Next, oh no! There is an error!!! (...Finished.)

Temporary Solution:
==================

- Right click table name (test1), select Alter table from popup menu. 

- Select first column in the table (id), select the entire content of form field column name (just above collation form field). IMPORTANT! Either double click to select entire content or use Select All from popup menu or use Ctrl+a when cursor is inside the column name form field. Selecting only 'i' and 'd' won't work.

- Delete and rewrite same field name again -- 'id' and Apply

- Repeat 'Second File' steps and there will be no error this time.

Before trying to import second file for the first time, try this...

Copy and paste both select statements in Workbench.

SELECT `id` FROM `test1`; <<< Error Code: 1054. Unknown column 'id' in 'field list'

SELECT `id` FROM `test1`; <<< This works.

Hello Hassan,

Thank you for the feedback and testcase.
Verified this behavior on MySQL Workbench in 6.3.9 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_87788.JPG (image/jpeg, text), 81.19 KiB.