rails
rails copied to clipboard
rails
Ensure binary column defaults are not hex in MySQL 8.0.3+
MySQL 8.0.3+ returns column defaults as hexadecimal. We use these values for the column defaults before the record is loaded from the database. To be compatible with MySQL before and after 8.0.3 we should try to cast these values to strings the way it behaved before mysql 8.0.3
FIXES: https://github.com/rails/rails/issues/45832
I'm wondering if we need to have some options to show varbinary default value as hex because MySQL 8.0 now prefers to show them as hex by default and also, even if --disable-binary-as-hex is set for MySQL client, describe and SHOW FULL FIELDS FROM commands show them as hex.
@yahonda It seems most useful to me to have the default values match the format that you get when you select a record from the database. That is mostly because of how we use the field data to populate values on newly initialized models. I think mysql prefers it because you could have a binary column default value that is difficult or impossible to display as a string. I don't know how useful getting a hex string is generally in a rails app. Maybe this is something we can discuss adding separately?
Although, I also do not have actual use cases to display varbinary data type as hex, Recent version of MySQL prefers hex to display varbinary data by default. Then I think we need to think carefully if we can override this behavior in Rails without options. MySQL 8.0.11 is the first GA version of MySQL 8.0 has been released Apr 2018. It has passed about 4 years so there may be some users who accepts this behavior. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html
By the way, I have opened https://bugs.mysql.com/bug.php?id=108182 to see if --skip-binary-as-hex option should work for show full fields, desc and information_schema.column.column_default output or not.
Thanks for opening that @yahonda! I agree that if --skip-binary-as-hex works on show full fields, we likely don't need this workaround in Rails.
Updated the tests. With this change all 3 adapters treat binary columns defaults the same way.
After some consideration of how Rails migration specifies the default value, now I do not think we need to have any options to fall back to show binary/varbinary default values in hex in Rails.
I think we will need to decide how to handle trailing spaces for binary columns in MySQL 5.7 (MySQL 8.0.2 and lower) and MariaDB.
I prefer to removing the trailing spaces like column_info[:Default].delete("\u0000").
https://buildkite.com/rails/rails/builds/88922#0182b839-b68a-47de-be0e-fe8cfc45248f/1186-1195
Failure:
DefaultBinaryTest#test_default_binary_string [/rails/activerecord/test/cases/defaults_test.rb:107]:
--- expected
+++ actual
@@ -1 +1 @@
-"binary_default"
+"binary_default\u0000\u0000\u0000\u0000\u0000\u0000"
- How default values are saved in MariaDB
mysql -uroot test
MariaDB [test]> select version();
+-----------------+
| version() |
+-----------------+
| 10.5.16-MariaDB |
+-----------------+
1 row in set (0.000 sec)
MariaDB [test]> CREATE TABLE `default_binaries` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `varbinary_col` varbinary(64) DEFAULT x'76617262696e6172795f64656661756c74' NOT NULL, `varbinary_col_hex_looking` varbinary(64) DEFAULT x'30784445414442454546' NOT NULL);
MariaDB [test]> ALTER TABLE default_binaries ADD COLUMN binary_col binary(20) DEFAULT 'binary_default';
MariaDB [test]> select TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT,lengthb(COLUMN_DEFAULT) from information_schema.columns where TABLE_SCHEMA = 'test';
+------------------+---------------------------+------------------------------+-------------------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_DEFAULT | lengthb(COLUMN_DEFAULT) |
+------------------+---------------------------+------------------------------+-------------------------+
| default_binaries | id | NULL | NULL |
| default_binaries | varbinary_col | 'varbinary_default' | 19 |
| default_binaries | varbinary_col_hex_looking | '0xDEADBEEF' | 12 |
| default_binaries | binary_col | 'binary_default\0\0\0\0\0\0' | 28 |
+------------------+---------------------------+------------------------------+-------------------------+
4 rows in set (0.001 sec)
Thanks @yahonda I agree removing them is probably right. I spend a bit of time thinking about the,
t.binary :varbinary_col_trailing_trailing_null, null: false, limit: 64, default: "hi\u0000"
case, but I don't think that is a meaningful use case. I am open to opinions on this though.
Oh, well that is another wrinkle I didn't expect,
in my test with MySQL 8.0.30 SELECTing a binary column or varbinary returns the full value (including null bytes), however SHOW FULL FIELDS truncates at the first null byte,
for example,
mysql> show full fields FROM default_binaries;
+---------------------------+---------------+-----------+------+-----+--------------------------------------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------------------------+---------------+-----------+------+-----+--------------------------------------+----------------+---------------------------------+---------+
| id | bigint | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| varbinary_col | varbinary(64) | NULL | NO | | 0x76617262696E6172795F64656661756C74 | | select,insert,update,references | |
| varbinary_col_hex_looking | varbinary(64) | NULL | NO | | 0x30784445414442454546 | | select,insert,update,references | |
| varbinary_col_with_null | varbinary(64) | NULL | NO | | 0x68695F | | select,insert,update,references | |
| binary_col | binary(20) | NULL | YES | | 0x62696E6172795F64656661756C74 | | select,insert,update,references | |
+---------------------------+---------------+-----------+------+-----+--------------------------------------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)
mysql> INSERT INTO default_binaries VALUES ();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM default_binaries;
+----+--------------------------------------+------------------------------------------------------+--------------------------------------------------+--------------------------------------------+
| id | varbinary_col | varbinary_col_hex_looking | varbinary_col_with_null | binary_col |
+----+--------------------------------------+------------------------------------------------------+--------------------------------------------------+--------------------------------------------+
| 1 | 0x76617262696E6172795F64656661756C74 | 0x30784445414442454546 | 0x68695F007468657265 | 0x62696E6172795F64656661756C74000000000000 |
+----+--------------------------------------+------------------------------------------------------+--------------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
Here the SELECT for binary_col has trailing null bytes, while the default does not, similarly in varbinary_col_with_null the default is truncated after just 3 bytes.
Interestingly, mysqldump seems to have the values I expect.
mysqldump -u root -P 3307 -h 127.0.0.1 -d activerecord_unittest default_binaries 18:58:29
-- MySQL dump 10.13 Distrib 8.0.30, for macos12.4 (x86_64)
--
-- Host: 127.0.0.1 Database: activerecord_unittest
-- ------------------------------------------------------
-- Server version 8.0.30
<snip encoding info >
--
-- Table structure for table `default_binaries`
--
DROP TABLE IF EXISTS `default_binaries`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `default_binaries` (
`id` bigint NOT NULL AUTO_INCREMENT,
`varbinary_col` varbinary(64) NOT NULL DEFAULT 'varbinary_default',
`varbinary_col_hex_looking` varbinary(64) NOT NULL DEFAULT '0xDEADBEEF',
`varbinary_col_with_null` varbinary(64) NOT NULL DEFAULT 'hi_\0there',
`binary_col` binary(20) DEFAULT 'binary_default\0\0\0\0\0\0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
So there must be someway to get the values we want from the server.
Ah, looks like mysqldump uses show create table if it is available,
show create table `default_binaries`;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| default_binaries | CREATE TABLE `default_binaries` (
`id` bigint NOT NULL AUTO_INCREMENT,
`varbinary_col` varbinary(64) NOT NULL DEFAULT 'varbinary_default',
`varbinary_col_hex_looking` varbinary(64) NOT NULL DEFAULT '0xDEADBEEF',
`varbinary_col_with_null` varbinary(64) NOT NULL DEFAULT 'hi_\0there',
`binary_col` binary(20) DEFAULT 'binary_default\0\0\0\0\0\0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
@HParker MySQL Client has a --binary-mode option that changes the behavior w.r.t. null bytes. Not sure that changes things but you might want to give it a try.
I have also update https://bugs.mysql.com/bug.php?id=108182 to show that it is not the client, but the server that does the hex encoding of the defaults field.
