maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

tested with binary data 0x00 and 0x01
return ALWAYS bit=1
hehe load data have a documented bug with bit type :P

i will test BIT with length 64

2015-11-03 0:42 GMT-02:00 Justin Swanhart <greenlion@xxxxxxxxx>:

> Yes, \0 is ascii zero, but it must be converting the string \0 to the
> int.  It doesn't do any escape conversion, which is kind of idiotic. :D
>
> I don't know what the LOAD DATA delimiter parser will do with a null
> character in the input.  It might fail.
>
> --Justin
>
> On Mon, Nov 2, 2015 at 7:39 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
> wrote:
>
>> character ascii 0 ?
>> \0 \1 b'0' b'1' 0 1
>> don't work
>>
>> i will test binary data
>>
>> 2015-11-03 0:26 GMT-02:00 Justin Swanhart <greenlion@xxxxxxxxx>:
>>
>>> Try \0 and 1  if you have only one bit to set.
>>>
>>> On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
>>> wrote:
>>>
>>>> maybe
>>>> enum_field_types type() const { return MYSQL_TYPE_BIT; }
>>>> if(field->type()==MYSQL_TYPE_BIT){
>>>> // convert string to unsigned long long
>>>> }else{
>>>> // use normal field->store()
>>>> }
>>>>
>>>> must try, but i didn't found how to store() with the "b'0'"
>>>> representation or someting like it
>>>>
>>>> 2015-11-02 22:45 GMT-02:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>>>>
>>>>> maybe check if it's a BIT type at sql_load.cc and cast to bigint
>>>>> unsigned before field->store ? i think it's the easier patch, i don't know
>>>>> i tryed any kind of file
>>>>> the bit column only with with "SET bit_column=@some_variable+0" or
>>>>> anything to cast from "string" to "integers"
>>>>>
>>>>> 2015-11-02 22:40 GMT-02:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>>>>>
>>>>>> probably something here:
>>>>>> https://github.com/MariaDB/server/blob/10.1/sql/field.cc#L9127
>>>>>>
>>>>>> 2015-11-02 22:28 GMT-02:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>>>>>>
>>>>>>> hi justing, but i always get 1
>>>>>>> even when data file have "0" (character "0") at line
>>>>>>>
>>>>>>> i'm reading the load data file:
>>>>>>>
>>>>>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L853
>>>>>>> that's something with field->store (i think)
>>>>>>> field->store((char*) pos,length,read_info.read_charset);
>>>>>>>
>>>>>>> https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad9d7/sql/sql_load.cc#L1025
>>>>>>> field->store((char*) pos, length, read_info.read_charset);i didn't
>>>>>>> checked yet store of bit field type
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> 2015-11-02 22:15 GMT-02:00 Justin Swanhart <greenlion@xxxxxxxxx>:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> Instead of using b'...' notation, use bitwise math to get the
>>>>>>>> integer representation of the bitset, and place that in your loader file.
>>>>>>>>
>>>>>>>> For example:
>>>>>>>> select 1 | 2 | 8;  ---> 11
>>>>>>>>
>>>>>>>> Put 11 in your data file and you will get bits 1 2 and 8 set in
>>>>>>>> your bitset.
>>>>>>>>
>>>>>>>> --Justin
>>>>>>>>
>>>>>>>> On Mon, Nov 2, 2015 at 4:52 PM, Roberto Spadim <
>>>>>>>> roberto@xxxxxxxxxxxxx> wrote:
>>>>>>>>
>>>>>>>>> sorry found at mysql docs
>>>>>>>>>
>>>>>>>>> BIT <https://dev.mysql.com/doc/refman/5.7/en/bit-type.html> values
>>>>>>>>> cannot be loaded using binary notation (for example, b'011010').
>>>>>>>>> To work around this, specify the values as regular integers and use the
>>>>>>>>> SET clause to convert them so that MySQL performs a numeric type
>>>>>>>>> conversion and loads them into the BIT
>>>>>>>>> <https://dev.mysql.com/doc/refman/5.7/en/bit-type.html> column
>>>>>>>>> properly:
>>>>>>>>>
>>>>>>>>>  <https://dev.mysql.com/doc/refman/5.7/en/load-data.html>
>>>>>>>>> shell> *cat /tmp/bit_test.txt*
>>>>>>>>> 2
>>>>>>>>> 127
>>>>>>>>> shell> *mysql test*
>>>>>>>>> mysql> *LOAD DATA INFILE '/tmp/bit_test.txt'*
>>>>>>>>>     -> *INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);*
>>>>>>>>> Query OK, 2 rows affected (0.00 sec)
>>>>>>>>> Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> but i'm checking something very bad
>>>>>>>>> if i execute without "SET" a big file take 1second, with "SET" it
>>>>>>>>> takes 3 times more
>>>>>>>>> maybe we could include the bit cast at load data infile and solve
>>>>>>>>> this problem? maybe a mariadb improvement?
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> 2015-11-02 21:43 GMT-02:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>>>>>>>>>
>>>>>>>>>> hi guys, how could i send bit data to mysql with LOAD FILE?
>>>>>>>>>>
>>>>>>>>>> a table example:
>>>>>>>>>> create table t (i bit not null default 0);
>>>>>>>>>>
>>>>>>>>>> LOAD DATA LOCAL INFILE "file.csv" IGNORE INTO TABLE `t` FIELDS
>>>>>>>>>> TERMINATED BY ';' LINES TERMINATED BY '\n' (i)
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> the data file is simple:
>>>>>>>>>>
>>>>>>>>>> 0
>>>>>>>>>> 1
>>>>>>>>>> 0
>>>>>>>>>> 1
>>>>>>>>>> 0
>>>>>>>>>> 1
>>>>>>>>>>
>>>>>>>>>> but i got Warning 1024: Out of range value for column 'i' at row
>>>>>>>>>> 1 ... to last row
>>>>>>>>>>
>>>>>>>>>> any idea? i tried
>>>>>>>>>> b'0'
>>>>>>>>>> b'1'
>>>>>>>>>> '0'
>>>>>>>>>> '1'
>>>>>>>>>> 0
>>>>>>>>>> 1
>>>>>>>>>> but no one work, i don't know what happen but it's cast always to
>>>>>>>>>> 1  (maybe convert from string to binary?)
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Roberto Spadim
>>>>>>>>> SPAEmpresarial - Software ERP
>>>>>>>>> Eng. Automação e Controle
>>>>>>>>>
>>>>>>>>> _______________________________________________
>>>>>>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>>>>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>>>>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Roberto Spadim
>>>>>>> SPAEmpresarial - Software ERP
>>>>>>> Eng. Automação e Controle
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Roberto Spadim
>>>>>> SPAEmpresarial - Software ERP
>>>>>> Eng. Automação e Controle
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Roberto Spadim
>>>>> SPAEmpresarial - Software ERP
>>>>> Eng. Automação e Controle
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Roberto Spadim
>>>> SPAEmpresarial - Software ERP
>>>> Eng. Automação e Controle
>>>>
>>>
>>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>
>


-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle