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

CONNECT DBF table not created propery - throws LRECL error on SELECT

Details

    Description

      When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

      I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

      The following executes fine:

      create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';

      However the following:

      select * from dbf_demo;

      throws this error:

      SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT

      Upon further inspection the DBF file is actually being read and the table is being created but you cannot use SELECT to view even the empty columns in the table, it consistently throws the LRECL error.

      If you run the following:

      show create table dbf_demo

      You can then see the structure of dbf_demo(FIELD NAMES CHANGED):

      CREATE TABLE `dbf_demo` (
      		 
        `FIRST_NAME` char(49) NOT NULL,
      		 
        `LAST_NAME` char(10) NOT NULL,
      		 
        `EVENT_DATE` char(10) NOT NULL,
      		 
        `EVENT_TYPE` char(2) NOT NULL,
      		 
        `GRID` char(13) NOT NULL,
      		 
        `LONG` double(11,2) NOT NULL,
      		 
        `LAT` double(11,2) NOT NULL,
      		 
        `LOCATION` char(181) NOT NULL,
      		 
        `APPROVED` char(13) NOT NULL
      		 
      ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='DBF' `FILE_NAME`='C:/MariaDB 10.1/data/demo/test_dbf.dbf' `LRECL`=310

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          boneill81 Barry O' Neill created issue -
          boneill81 Barry O' Neill made changes -
          Field Original Value New Value
          Description When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code='sql'}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code='sql'}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code='sql'}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}           		When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code:sql}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code:sql}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code:sql}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}
          boneill81 Barry O' Neill made changes -
          Description When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code:sql}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code:sql}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code:sql}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}           		When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code:sql}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code:sql}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code:sql}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}

          Upon further inspection the DBF file is actually being read and the table is being created but you cannot use SELECT to view even the empty columns in the table, it consistently throws the LRECL error.

          *If you run the following*:
          {code:sql}
          show create table dbf_demo
          {code}

          *You can then see the structure of dbf_demo(FIELD NAMES CHANGED)*:
          {code:sql}
          CREATE TABLE `dbf_demo` (
            `FIRST_NAME` char(49) NOT NULL,
            `LAST_NAME` char(10) NOT NULL,
            `EVENT_DATE` char(10) NOT NULL,
            `EVENT_TYPE` char(2) NOT NULL,
            `GRID` char(13) NOT NULL,
            `LONG` double(11,2) NOT NULL,
            `LAT` double(11,2) NOT NULL,
            `LOCATION_NAME` char(181) NOT NULL,
            `APPROVED` char(13) NOT NULL
          ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='DBF' `FILE_NAME`='C:/MariaDB 10.1/data/demo/test_dbf.dbf' `LRECL`=310
          {code}
          boneill81 Barry O' Neill made changes -
          Description When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code:sql}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code:sql}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code:sql}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}

          Upon further inspection the DBF file is actually being read and the table is being created but you cannot use SELECT to view even the empty columns in the table, it consistently throws the LRECL error.

          *If you run the following*:
          {code:sql}
          show create table dbf_demo
          {code}

          *You can then see the structure of dbf_demo(FIELD NAMES CHANGED)*:
          {code:sql}
          CREATE TABLE `dbf_demo` (
            `FIRST_NAME` char(49) NOT NULL,
            `LAST_NAME` char(10) NOT NULL,
            `EVENT_DATE` char(10) NOT NULL,
            `EVENT_TYPE` char(2) NOT NULL,
            `GRID` char(13) NOT NULL,
            `LONG` double(11,2) NOT NULL,
            `LAT` double(11,2) NOT NULL,
            `LOCATION_NAME` char(181) NOT NULL,
            `APPROVED` char(13) NOT NULL
          ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='DBF' `FILE_NAME`='C:/MariaDB 10.1/data/demo/test_dbf.dbf' `LRECL`=310
          {code}           		When you attempt to create a CONNECT DBF table, the create command executes without error. However when an attempt is made to query the data, an LRECL error is thrown and no data is returned.

          I have also attempted to manually set the LRECL via the options in the create statement but the same result is achieved. I have verified the DBF file and it opens fine it is a DBF version III file.

          *The following executes fine*:
          {code:sql}
          create table dbf_demo engine=CONNECT table_type=DBF file_name='C:/MariaDB 10.1/data/demo/test_dbf.dbf';
          {code}

          *However the following*:
          {code:sql}
          select * from dbf_demo;
          {code}

          *throws this error*:
          {code:sql}
          SQL Error (1296): Got error 174 'Table/File lrecl mismatch (857,301)' from CONNECT
          {code}

          Upon further inspection the DBF file is actually being read and the table is being created but you cannot use SELECT to view even the empty columns in the table, it consistently throws the LRECL error.

          *If you run the following*:
          {code:sql}
          show create table dbf_demo
          {code}

          *You can then see the structure of dbf_demo(FIELD NAMES CHANGED)*:
          {code:sql}
          CREATE TABLE `dbf_demo` (
            `FIRST_NAME` char(49) NOT NULL,
            `LAST_NAME` char(10) NOT NULL,
            `EVENT_DATE` char(10) NOT NULL,
            `EVENT_TYPE` char(2) NOT NULL,
            `GRID` char(13) NOT NULL,
            `LONG` double(11,2) NOT NULL,
            `LAT` double(11,2) NOT NULL,
            `LOCATION` char(181) NOT NULL,
            `APPROVED` char(13) NOT NULL
          ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='DBF' `FILE_NAME`='C:/MariaDB 10.1/data/demo/test_dbf.dbf' `LRECL`=310
          {code}
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Assignee Olivier Bertrand [ bertrandop ]
          squeeze69 Marco Banfi made changes -
          Attachment test_dbf_1.zip [ 42519 ]
          bertrandop Olivier Bertrand made changes -
          issue.field.resolutiondate 2016-09-13 08:45:24.0 2016-09-13 08:45:24.325
          bertrandop Olivier Bertrand made changes -
          Fix Version/s N/A [ 14700 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 6 ]
          bertrandop Olivier Bertrand made changes -
          Resolution Not a Bug [ 6 ]
          Status Closed [ 6 ] Stalled [ 10000 ]
          bertrandop Olivier Bertrand made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          squeeze69 Marco Banfi made changes -
          Attachment test_dbf_2.zip [ 42601 ]
          bertrandop Olivier Bertrand made changes -
          Fix Version/s 10.0.28 [ 22107 ]
          Fix Version/s 10.1.19 [ 22111 ]
          Fix Version/s N/A [ 14700 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.0.28 [ 22107 ]
          dbart Daniel Bartholomew made changes -
          Fix Version/s 10.1.20 [ 22112 ]
          Fix Version/s 10.1.19 [ 22111 ]
          dbart Daniel Bartholomew made changes -
          Fix Version/s 10.1.21 [ 22113 ]
          Fix Version/s 10.1.20 [ 22112 ]
          dbart Daniel Bartholomew made changes -
          Fix Version/s 10.1.22 [ 22502 ]
          Fix Version/s 10.1.21 [ 22113 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.1.22 [ 22502 ]
          bertrandop Olivier Bertrand made changes -
          issue.field.resolutiondate 2017-03-16 11:14:58.0 2017-03-16 11:14:58.372
          bertrandop Olivier Bertrand made changes -
          Fix Version/s 10.0.31 [ 22501 ]
          Fix Version/s 10.1.22 [ 22502 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 76609 ] MariaDB v4 [ 150734 ]

          People

            bertrandop Olivier Bertrand
            boneill81 Barry O' Neill
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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