0

I'm using MariaDB 10.2.4. By default, sql_mode includes STRICT_TRANS_TABLES, which is good for the most part. But in specific cases I would like the data to just be truncated. Is there a way to override the sql_mode temporarily, or an option for the INSERT statement to explicitly permit truncation? I can't find anything in documentation or by googling.

You might be tempted to think this is a duplicate of Should I disable MySQL strict mode?, but that question was in 2011, so there might be new options since then. Plus, that question was about best practices regarding the mode in general, not about the ability to override in runtime.

OsakaWebbie
  • 645
  • 1
  • 7
  • 21
  • If you are talking about simple text fields/values here, you could perhaps try and do the “truncating” in your statement already? I’m thinking something like `INSERT INTO ... VALUES(SUBSTRING('long dynamic text value here', 1, 10))` ... Of course that would not be very flexible and would need to be modified if the table structure changes; but as a workaround for a single occurrence within the application, it might do. – CBroe Jul 20 '17 at 10:49
  • Yeah, I know, or I could do it in PHP when generating the statement, but either way, I was hoping not to have to remember to keep the schema and code in sync with a hard-coded number. It's certainly my Plan B, but are there any other options out there? – OsakaWebbie Jul 20 '17 at 14:45
  • Well I guess you could always set it from your code by firing a `SET sql_mode = 'mode';` statement before your INSERT, and setting it back right after, https://mariadb.com/kb/en/mariadb/sql-mode/#setting-sql_mode - _“The session value only affects the current client, and can be changed by the client when required”_ – CBroe Jul 20 '17 at 14:50
  • 1
    @CBroe Ah, I didn't realize that such a change would not affect other concurrent users - that's good. If you make that an answer, I'll accept it. – OsakaWebbie Jul 20 '17 at 17:38

1 Answers1

1

Is there a way to override the sql_mode temporarily

Yes, you can change this by firing a SET sql_mode = 'mode'; statement before your INSERT.

https://mariadb.com/kb/en/mariadb/sql-mode/#setting-sql_mode:

The session value only affects the current client, and can be changed by the client when required.

So if you set it back right after, you can be sure that it takes effect for this specific INSERT statement only.

CBroe
  • 91,630
  • 14
  • 92
  • 150