[Maria-discuss] LOAD FILE and BIT datatype
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?)
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@spadim.com.br>:
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
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@spadim.com.br> 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@spadim.com.br>:
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset); https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
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@lists.launchpad.net 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
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@spadim.com.br>:
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
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@lists.launchpad.net 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
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@spadim.com.br>:
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@spadim.com.br>:
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
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@lists.launchpad.net 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
Try \0 and 1 if you have only one bit to set. On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@spadim.com.br> 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@spadim.com.br>:
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@spadim.com.br>:
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br> 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@spadim.com.br>:
> 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@lists.launchpad.net 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
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@gmail.com>:
Try \0 and 1 if you have only one bit to set.
On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@spadim.com.br> 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@spadim.com.br>:
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@spadim.com.br>:
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
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@spadim.com.br > 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@spadim.com.br>: > >> 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@lists.launchpad.net > 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
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@spadim.com.br> 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@gmail.com>:
Try \0 and 1 if you have only one bit to set.
On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@spadim.com.br> 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@spadim.com.br>:
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@spadim.com.br>:
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@spadim.com.br>:
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/e19a6f3dca7509eb8e042796a9311dba937ad... that's something with field->store (i think) field->store((char*) pos,length,read_info.read_charset);
https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... 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@gmail.com>:
> 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@spadim.com.br> 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@spadim.com.br>: >> >>> 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@lists.launchpad.net >> 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
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@gmail.com>:
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@spadim.com.br> 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@gmail.com>:
Try \0 and 1 if you have only one bit to set.
On Mon, Nov 2, 2015 at 5:56 PM, Roberto Spadim <roberto@spadim.com.br> 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@spadim.com.br>:
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@spadim.com.br>:
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@spadim.com.br>:
> 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/e19a6f3dca7509eb8e042796a9311dba937ad... > that's something with field->store (i think) > field->store((char*) pos,length,read_info.read_charset); > > https://github.com/MariaDB/server/blob/e19a6f3dca7509eb8e042796a9311dba937ad... > 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@gmail.com>: > >> 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@spadim.com.br> 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@spadim.com.br>: >>> >>>> 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@lists.launchpad.net >>> 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
well some conversion is done.... SQL: drop table if exists a; create table if not exists a(i bit(64)) ENGINE=MYISAM; LOAD DATA LOCAL INFILE "c:\\spadim\\t.txt" IGNORE INTO TABLE `a` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (i); select * from a; RESULT: i 00001101 00001101 00001101 00001101 00001101 00001101 00001101 00001101 file t.txt attached [image: Imagem inline 1]
i openned a MDEV: https://mariadb.atlassian.net/browse/MDEV-9073 and will upload this last example 2015-11-03 0:50 GMT-02:00 Roberto Spadim <roberto@spadim.com.br>:
well some conversion is done....
SQL: drop table if exists a; create table if not exists a(i bit(64)) ENGINE=MYISAM; LOAD DATA LOCAL INFILE "c:\\spadim\\t.txt" IGNORE INTO TABLE `a` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (i); select * from a;
RESULT: i 00001101 00001101 00001101 00001101 00001101 00001101 00001101 00001101
file t.txt attached [image: Imagem inline 1]
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
participants (2)
-
Justin Swanhart -
Roberto Spadim