Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13046

Feature Request: Need BADFILE option like in oracle - LOAD DATA INFILE

Details

    Description

      Need BADFILE option like oracle in LOAD DATA INFILE
      There is already one open bug for warnings: MDEV-6166

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6166 Store all of LOAD DATA INFILE's warnings

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open
          links to

          Web Link SQL*Loader: Specifying the Bad File

          Activity

            Ascending order - Click to sort in descending order
            danblack Daniel Black added a comment -

            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.

            danblack Daniel Black added a comment - 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.
            Eric_Herman Eric Herman added a comment -

            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.

            Eric_Herman Eric Herman added a comment - 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.
            svoj Sergey Vojtovich added a comment -

            Resetting contribution label: pull request https://github.com/MariaDB/server/pull/2572 was closed as it was incomplete and there was no progress for over 2 years.

            svoj Sergey Vojtovich added a comment - Resetting contribution label: pull request https://github.com/MariaDB/server/pull/2572 was closed as it was incomplete and there was no progress for over 2 years.

            People

              Unassigned Unassigned
              niljoshi Nilnandan Joshi
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.