|
Example structure of "badfile" table using table discovery
create table data (name varchar(10) not null,birthyear year not null, weight float not null) charset=utf8mb4
|
| Table |
Create Table |
load_data_errors_data |
CREATE TABLE `load_data_errors_data` (<br> `lineno` bigint(20) unsigned NOT NULL,<br> `linetext` longtext DEFAULT NULL,<br> `error_column` enum('name','birthyear','weight') DEFAULT NULL,<br> `error_warning` enum('error','warning') NOT NULL,<br> `error_code` int(10) unsigned NOT NULL,<br> `error_text` varchar(2000) DEFAULT NULL,<br> `name` varchar(10) DEFAULT NULL,<br> `name_text` longtext DEFAULT NULL,<br> `birthyear` year(4) DEFAULT NULL,<br> `birthyear_text` longtext DEFAULT NULL,<br> `weight` float DEFAULT NULL,<br> `weight_text` longtext DEFAULT NULL,<br> PRIMARY KEY (`lineno`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
|
attempted insert
|
insert into data values ('Ash', '2004',4), ('Jovi', '2010', '30kg')
|
|
Data truncated for column 'weight' at row 2
|
| lineno |
linetext |
error_column |
error_warning |
error_code |
error_text |
name |
name_text |
birthyear |
birthyear_text |
weight |
weight_text |
| 2 |
Jovi,2010,30kg |
weight |
error |
1265 |
Data truncated for column 'weight' at row 2 |
Jovi |
Jovi |
2010 |
2010 |
<em>null</em> |
30kg |
*db<>fiddle here
Unresolved questions:
Multiple error tables possible or error file, or filename as composite PK with line number.
|
|
regarding unresolved questions, separate tables per load attempt would be fine, but I would be at least as happy with having the timestamp of the load, and perhaps an AUTO_INC of the load attempt.
Filename would be nice to have for certain.
|