[MDEV-16508] spider: sql_mode not maintained between spider node and data nodes Created: 2018-06-18  Updated: 2020-08-25  Resolved: 2019-06-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Fix Version/s: 10.4.7

Type: Task Priority: Major
Reporter: Simon Mudd Assignee: Kentoku Shiba (Inactive)
Resolution: Fixed Votes: 0
Labels: spider

Issue Links:
Duplicate
is duplicated by MDEV-13383 Spider is not maintaining SQL_MODE ac... Closed
Relates
relates to MDEV-16246 insert timestamp into spider table fr... Closed

 Description   

This was noticed some time ago and I believe is still present. No explicit bug report was made at the time.

If the spider node and data nodes have a different sql_mode setup then this may cause issues.

Example seen was to have sql_mode = STRICT_TRANS_TABLES on data nodes and the spider node and some input data which was accepted on the spider node was rejected by the data node as not being valid. This broke the data load. Other mismatches could also trigger issues.

My expectation would for the spider node to validate the data nodes' sql_mode matches the spider node, or to push out the spider node's current sql_mode setting (or when it changes as we can have session local sql_nodes) to the data nodes on connection or as the value changes.

I think this still applies in spider on 10.3.

 Comments   
Comment by Simon Mudd [ 2018-07-04 ]

I would also expect this to affect:

  • timezone
  • character set / collation

Basically some of the other session attributes which if not managed consistently between client and server can cause issues.

Comment by Kentoku Shiba (Inactive) [ 2019-03-13 ]

The timezone issue is fixed at MDEV-16246.
Character set/collation is adjusted when Spider rewrite plugin is used. (MDEV-16967)
Please let me know if you have another problem about character set/collation.

About sql_mode, I added a pushdown feature with the following option.

  • spider_sync_sql_mode
    Local sql_mode synchronous existence to remote server.
    0 : not synchronize.
    1 : synchronize.
    The default value is 1

2f4e5d5

Comment by Kentoku Shiba (Inactive) [ 2019-03-13 ]

Please review this changes.

Comment by willhan [ 2019-06-28 ]

kentoku
It's ok

Comment by Kentoku Shiba (Inactive) [ 2019-06-28 ]

willhan
Thank you for checking this.
I just merged.

Generated at Thu Feb 08 08:29:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.