Can you attach the test_dbf.dbf file so I can test what happens with it?

The record length is calculated adding the field lengths and the final end of line separator. This one is CRLF on Windows and LF on Linux and CONNECT adds by default 1 on Linux and 2 on Windows. Changing manually LRECL does not work because CONNECT adjust it to be sure it contains the whole line(s).
Therefore, when using a file that doesn't have the default line ending, the option to use is ENDING.
On Linux, when using a Windows file, specify ending=2 and on Windows specify ending=1 for a Linux file.
This is equally true for FIX, BIN and VCT tables.
I shall update the documentation about this point.

Hmmm, what I said is true only for FIX, BIN and VCT tables. The case of DBF is different.
I tried the attached DBF file and it was successfully read as well on Windows 7 32bits as on Linux ubuntu 15.10.
Therefore, something happened that I could not reproduce.

There is something strange with this error. I have made tests with the attach file on a 64bit machine both under Windows and Linux and could not reproduce this bug.

Now, looking in your error message:

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

What is weird is the number 857. Normally we are comparing the LRECL calculated by CONNECT with the one coming from the file header.
(This being a checking of correct definition when the create table was done with explicit column definition)

Obviously, the calculated LRECL was lost and replaced by something else. It could come from a shift in the class members due to some values having a different size on some machines.

Because the true record length is the one of the file header, this test could be simply suppressed and replace by setting LRECL to the value coming from the file header.
However, if class members are shifted when passed from one class to another, some other errors could happen.

If your are working with a source distribution, could you try this:

in the file filamdbf.cpp line 413, replace:

  if ((int)header.Reclen() != lrecl) {

    sprintf(g->Message, MSG(BAD_LRECL), lrecl, header.Reclen());

    return -1;

    } // endif Lrecl

by:

  lrecl = (int)header.Reclen();

(and perhaps also line 642 if needed)

So we can test whether this can fix the problem.

Note: The show create table should not have a LRECL option. If it was added manually, note that the correct value is 301, not 310.
Thanks.

Sorry, I'm not working with a source distribution.

Can you publish the exact error message you had on debian?
Thanks.

Hi, Oliver. Here it is (tested under Debian Jessie 64bit and wheezy 64bit as before).

Errore SQL (1296): Got error 174 'Table/File lrecl mismatch (46,24)' from CONNECT

BTW: I forgot to report that the original file is built under an old Clipper application. The sample file was anonymized using libreoffice calc. But it has the same behaviour before the treatment.

Hi, Olivier. I attach a second test case (untouched, no anonymization), it has the same behaviour

Error:
Errore SQL (1296): Got error 174 'Table/File lrecl mismatch (463,155)' from CONNECT

Table created with:
create table testdbf2 ENGINE=CONNECT table_type=DBF file_name='(omissis)/test_dbf_2.dbf';

Resulting create table code:
CREATE TABLE `testdbf2` (
`CODICE` CHAR(2) NOT NULL,
`NOME` CHAR(30) NOT NULL,
`NOME1` CHAR(30) NOT NULL,
`NOME2` CHAR(30) NOT NULL,
`PRESENTE` CHAR(1) NOT NULL,
`TURNISTA` CHAR(1) NOT NULL,
`ORA_INIZIO` CHAR(8) NOT NULL,
`CAMBIO_T` CHAR(5) NOT NULL,
`CAMBIO_T2` CHAR(5) NOT NULL,
`MATT_DALLE` CHAR(5) NOT NULL,
`MATT_ALLE` CHAR(5) NOT NULL,
`POME_DALLE` CHAR(5) NOT NULL,
`POME_ALLE` CHAR(5) NOT NULL,
`NOTT_DALLE` CHAR(5) NOT NULL,
`NOTT_ALLE` CHAR(5) NOT NULL,
`MINUTI_D` CHAR(4) NOT NULL,
`MINUTI_U` CHAR(5) NOT NULL,
`MANSIONE` CHAR(1) NOT NULL,
`LIBERO` CHAR(1) NOT NULL,
`IMPIAN_OP` CHAR(1) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=CONNECT;

Debian Wheezy,64bit mariadb 10.1.17 64bit

This error seems to occurs only on a limited number of platforms. I could not reproduce it and I checked on builbot where MariaDB is compiled on many platforms and all the tests implying DBF tables pass.

Therefore I cannot currently fix it but here is what I did:

When you create a DBF table with the option accept set to true, for instance adding:

option_list='Accept=1'

the mismatch between the calculated LRECL and the one extracted from the DBF file header is no more regarded as an error but just as a warning. In addition, because I don't know whether the LRECL is miscalculated or lost between the time it is computed and the time it is compared, I have added a trace showing the calculated value. This trace can be activated by:

set connect_xtrace=1

Then the LRECL is set to the value extracted from the DBF file header.

I don't know whether will actually fix your problem (there can be other side effects) so I'd like you to test it in the next MariaDB release and let me know what happens.

Thanks, Olivier.

I'll keep you informed, I see that the changes will be inserted in 10.1.19.

Greetings, Marco.

As a matter of facts, this solution was buggy and could cause a server crash because the block size has to be updated with the new LRECL.
This is fixed now but this will be available only in next versions of MariaDB.

Hi, Olivier.

Just tested the 10.1.22 win64, now it does work.

Thanks, Marco.