Shardingsphere: tinyint(1) column type will result in a booleam value in the query data

Created on 17 Oct 2019  ·  22Comments  ·  Source: apache/shardingsphere

Project: Sharding-proxy
Version: v4.0.0-RC3

Question

when the table column type be set to tinyint(1), the query result set of this column will be set to '1'. but it's ok with tinyint(2) or tinyint(3).
This bug seems to be issued in v3.1.0 and i was told that it will be resolved in the later version.

mysql> desc test2;
+-------+------------------+------+------+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+------+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| ctype | tinyint(1) | NO | | 0 | |
+-------+------------------+------+------+---------+----------------+

mysql> insert into test2 values(1,1),(2,2),(3,0),(4,1),(5,2);
Query OK, 5 rows affected (0.13 sec)

In command line mode, no value will return.
mysql> select * from test2;
+------+-------+
| id | ctype |
+------+-------+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+------+-------+
5 rows in set (0.03 sec)

In navicate query result, will get a booleam value except value '0'
微信图片_20191017164947

when set to tinyint(2), the query result is ok.
mysql> alter table test2 modify ctype tinyint(2) not null default 0 comment '类型';
Query OK, 0 rows affected (0.06 sec)
mysql> select * from test2;
+------+-------+
| id | ctype |
+------+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 0 |
| 4 | 1 |
| 5 | 2 |
+------+-------+
5 rows in set (0.02 sec)
mysql>

proxy bug
Source
picture Myshiner

Most helpful comment

@Myshiner Can you add tinyInt1isBit=false parameter to jdbcUrl in configuration file of sharding-proxy and retry? I think this can solve your problem.

picture KomachiSion on 21 Oct 2019
👍2

All 22 comments

Hi @Myshiner , I will test it according to you describe info and reply you issue.

dongzl picture dongzl on 17 Oct 2019

Sure, thxSent from my Huawei Mobile-------- Original Message --------Subject: Re: [apache/incubator-shardingsphere] tinyint(1) column type will result in a booleam value in the query data (#3311)From: 董宗磊 To: apache/incubator-shardingsphere CC: Myshiner ,Mention Hi @Myshiner , I will test it according to you describe info and reply you issue.

—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or unsubscribe.
[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/apache/incubator-shardingsphere/issues/3311?email_source=notifications\u0026email_token=ALQCBEM7WAQ5PRFITBCLLNLQPA22FA5CNFSM4JBWJY32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBPSBBA#issuecomment-543105156",
"url": "https://github.com/apache/incubator-shardingsphere/issues/3311?email_source=notifications\u0026email_token=ALQCBEM7WAQ5PRFITBCLLNLQPA22FA5CNFSM4JBWJY32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBPSBBA#issuecomment-543105156",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]

Myshiner picture Myshiner on 17 Oct 2019

Hi @Myshiner , this is my debug pic, tinyint(1) covert to boolean value is MySQL's JDBC implemention, I think this is not a problem.
com.mysql.jdbc.ResultSetImpl

image

dongzl picture dongzl on 18 Oct 2019

Yes, i know, but actually it really resulted in what i said above. And when you set tinyint(2)/(3)/(4) will be ok.

Myshiner picture Myshiner on 18 Oct 2019

Hi @Myshiner , I'm sorry, I don't understand your said very clearly.
Your said is: when column is tinyint(1), the value is 0 or 1, this is a bug?

Do you think what is the right result?

dongzl picture dongzl on 18 Oct 2019

Yes, i know, but actually it really resulted in what i said above. And when you set tinyint(2)/(3)/(4) will be ok.

when you use mysql tinyint（1）,the result is boolean value,you shoule tinyint(2) tinyint(3) or tiny(4) i think it not a bug ，either.

copyshop picture copyshop on 20 Oct 2019

when you use mysql tinyint（1）,the result is boolean value,you shoule tinyint(2) tinyint(3) or tiny(4) i think it not a bug ，either.

Hi @Myshiner , I understand what you said, I debug the Sharding-Proxy's code.
in org.apache.shardingsphere.shardingproxy.transport.mysql.packet.command.query.text.MySQLTextResultSetRowPacket class file:

@Override
public void write(final MySQLPacketPayload payload) {
    for (Object each : data) {
        if (null == each) {
            payload.writeInt1(NULL);
        } else {
            if (each instanceof byte[]) {
                payload.writeBytesLenenc((byte[]) each);
            } else if (each instanceof Timestamp) {
                payload.writeStringLenenc(each.toString().split("\\.")[0]);
            } else if (each instanceof BigDecimal) {
                payload.writeStringLenenc(((BigDecimal) each).toPlainString());
            } else if (each instanceof Boolean) {
                payload.writeBytesLenenc((Boolean) each ? new byte[]{1} : new byte[]{0});
            } else {
                payload.writeStringLenenc(each.toString());
            }
        }
    }
}

if the value is Boolean type, I understand Sharding-Proxy will transfer a byte to client.
the process is:

  • MySQL JDBC Driver convert tinyint(1) to Boolean type and return to Sharding-Proxy;
  • Sharding-Proxy convert a Boolean value to Netty's ByteBuf one byte and return to client.

I think this is not a problem. We can discuss this issue with ShardingSphere team, How to handle it.

dongzl picture dongzl on 20 Oct 2019

Tracking the history issues about boolean value and tinyint in sharding-proxy:

  1. https://github.com/apache/incubator-shardingsphere/issues/2528
  2. https://github.com/apache/incubator-shardingsphere/issues/2604
  3. https://github.com/apache/incubator-shardingsphere/issues/3027

The history may help us to know why sharding-proxy behaves like this.

tuohai666 picture tuohai666 on 21 Oct 2019

@Myshiner
Do you mean Navicat client return the expected result? But MySQL cli return an unexpected result?
If so what's the version of MySQL client?

tuohai666 picture tuohai666 on 21 Oct 2019

@Myshiner Can you add tinyInt1isBit=false parameter to jdbcUrl in configuration file of sharding-proxy and retry? I think this can solve your problem.

KomachiSion picture KomachiSion on 21 Oct 2019
👍2

Just tested using your config option and it didn't work.Sent from my Huawei Mobile-------- Original Message --------Subject: Re: [apache/incubator-shardingsphere] tinyint(1) column type will result in a booleam value in the query data (#3311)From: 杨翊 SionYang To: apache/incubator-shardingsphere CC: Myshiner ,Mention @Myshiner Can you add tinyInt1isBit=false parameter to jdbcUrl in configuration file of sharding-proxy and retry? I think this can solve your problem.

—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or unsubscribe.
[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/apache/incubator-shardingsphere/issues/3311?email_source=notifications\u0026email_token=ALQCBEJBH6VQX5ICWFMYAFLQPVDCFA5CNFSM4JBWJY32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBZF6YA#issuecomment-544366432",
"url": "https://github.com/apache/incubator-shardingsphere/issues/3311?email_source=notifications\u0026email_token=ALQCBEJBH6VQX5ICWFMYAFLQPVDCFA5CNFSM4JBWJY32YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEBZF6YA#issuecomment-544366432",
"name": "View Issue"
},
"description": "View this Issue on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]

Myshiner picture Myshiner on 21 Oct 2019

@Myshiner I have tested too, it works. Can you double check your configuration?
My configuration likes:

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&tinyInt1isBit=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&tinyInt1isBit=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50

tuohai666 picture tuohai666 on 21 Oct 2019
👍1

@Myshiner, Can you provide more detail information? like sharding-proxy configuration?

KomachiSion picture KomachiSion on 21 Oct 2019

My configurations:

okaytest: !!org.apache.shardingsphere.orchestration.yaml.config.YamlDataSourceConfiguration
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
properties:
url: jdbc:mysql://10.60.0.183:3406/okaytest?serverTimezone=UTC&useSSL=false&tinyInt1isBit=false
username: writer
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
readOnly: false
slave_okaytest_0: !!org.apache.shardingsphere.orchestration.yaml.config.YamlDataSourceConfiguration
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
properties:
url: jdbc:mysql://10.60.0.183:4406/okaytest?serverTimezone=UTC&useSSL=false&tinyInt1isBit=false
username: reader
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 65
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
readOnly: false

when i query it threw error.
mysql> select * from test1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
when i change the jdbc url to "url: jdbc:mysql://10.60.0.183:3406/okaytest?serverTimezone=UTC&useSSL=false&zeroDateTimeBehavior=convertToNull", and the query is ok but the zero datatime is set to null.
mysql> select * from test1;
+------+-------+---------------------+
| id | ctype | create_time |
+------+-------+---------------------+
| 1 | 1 | 2019-10-21 10:52:24 |
| 2 | 2 | 2019-10-21 10:52:24 |
| 3 | 0 | 2019-10-21 10:52:24 |
| 4 | 1 | 2019-10-21 10:52:24 |
| 5 | 2 | 2019-10-21 10:52:24 |
| 6 | 1 | 2019-10-21 10:55:36 |
| 7 | 0 | 2019-10-21 11:14:09 |
| 8 | 0 | NULL |
+------+-------+---------------------+
8 rows in set (0.02 sec)

Myshiner picture Myshiner on 21 Oct 2019

Here is the error.

[INFO ] 17:16:42.906 [pool-51-thread-1] ShardingSphere-SQL - SQL: select * from test1 ::: DataSources: slave_okaytest_0
[ERROR] 17:16:42.922 [pool-51-thread-1] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLException: Value '800000-00-00 00:00:00' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:937)
at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5918)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5588)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getTimestamp(HikariProxyResultSet.java)
at org.apache.shardingsphere.core.execute.sql.execute.result.QueryResultUtil.getValueByColumnType(QueryResultUtil.java:75)
at org.apache.shardingsphere.core.execute.sql.execute.result.QueryResultUtil.getValue(QueryResultUtil.java:45)
at org.apache.shardingsphere.core.execute.sql.execute.result.StreamQueryResult.getValue(StreamQueryResult.java:71)
at org.apache.shardingsphere.core.merge.dql.common.StreamMergedResult.getValue(StreamMergedResult.java:49)
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.getQueryData(JDBCDatabaseCommunicationEngine.java:199)
at org.apache.shardingsphere.shardingproxy.backend.text.query.QueryBackendHandler.getQueryData(QueryBackendHandler.java:64)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.getQueryData(MySQLComQueryPacketExecutor.java:115)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.getQueryData(MySQLComQueryPacketExecutor.java:55)
at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:83)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:94)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:66)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Myshiner picture Myshiner on 21 Oct 2019

ERROR 2013 (HY000): Lost connection to MySQL server during query

It looks like you restart sharding-proxy, but do not reconnection.
For my test, I restart my sharding-proxy and mysql client, and query work well.

KomachiSion picture KomachiSion on 21 Oct 2019

@KomachiSion no, i didn't restart sharding-proxy, i made the changes from zk center and then did query.

Myshiner picture Myshiner on 21 Oct 2019

@Myshiner , Can you use tinyInt1isBit=false without create_time to test?
Such as select id, ctype from test1? the timestamp is other issue.
The issue should focus on tinyint problem.

KomachiSion picture KomachiSion on 21 Oct 2019

@KomachiSion Sorry, it does work, my mistake, i got confused.
Thx, i will close the ticket.

Myshiner picture Myshiner on 21 Oct 2019

By the way, does the parameter work well in sharding-proxy 3.1.0 ? coz we have some other servers run with v3.1.0.

Myshiner picture Myshiner on 21 Oct 2019

By the way, does the parameter work well in sharding-proxy 3.1.0 ? coz we have some other servers run with v3.1.0.

I think it can work well, but I think do test will be better.

KomachiSion picture KomachiSion on 21 Oct 2019

@Myshiner I guess you are in foreign company, aren't you?
Is it compliant to leave your company's information in https://github.com/apache/incubator-shardingsphere/issues/234? If so, ShardingSphere will get the first known foreign user.

tuohai666 picture tuohai666 on 21 Oct 2019
Was this page helpful?
0 / 5 - 0 ratings

Related issues

sandynz picture sandynz  ·  4Comments
hzlzz picture hzlzz  ·  4Comments
xiaojianyu315 picture xiaojianyu315  ·  3Comments
terrymanu picture terrymanu  ·  6Comments
cocodroid picture cocodroid  ·  5Comments