Re: [I] issue when testing with mysql server 9.6.0 [pekko-persistence-jdbc]

["pjfanning (via GitHub)" <gi...@apache.org>]

pjfanning commented on issue #431:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/431#issuecomment-5704018993

   Root cause found; nothing to fix in this repo.
   
   **Why it failed:** at the time of the failing run, `core/src/test/resources/mysql-application.conf` pointed at `jdbc:mysql://host:3306/mysql`, so `event_journal` and `event_tag` were created inside MySQL's own `mysql` system schema. MySQL classifies any table in the `mysql` schema whose name starts with `event` (also `help*`, `time*`, `proc`) as a system table (`is_system_table_name` in `sql/table.cc`).
   
   MySQL 9.6.0 moved foreign-key cascade handling from InnoDB into the SQL layer (WL#11249). The parent `DELETE` now prelocks the `ON DELETE CASCADE` child for write, and `lock_tables_check` (`sql/lock.cc`) refuses to write-lock a "system" table in the same lock set as other tables -> `ERROR 1428: You can't combine write-locking of system tables with other tables or lock types`. On 9.5.0 the cascade happened inside InnoDB, so the server-level lock check never saw the child table.
   
   **Why it "went away":** #386 changed the test URL to `/pekko_persistence_jdbc` as a side effect. MySQL never fixed anything - the same 15-line repro (parent + `ON DELETE CASCADE` child + one `DELETE`) still fails on 9.7.2 and 26.7.0 when run in the `mysql` schema, and passes in a user schema:
   
   | Server | tables in `mysql` schema | tables in `pekko_persistence_jdbc` |
   |---|---|---|
   | 9.5.0 | ok | ok |
   | 9.6.0 | ERROR 1428 | ok |
   | 9.6.0 `--innodb-native-foreign-keys=ON` | ok | ok |
   | 9.7.2 | ERROR 1428 | ok |
   | 26.7.0 | ERROR 1428 | ok |
   
   Also verified by checking out `a9bc09a4` (the failing commit) and running `MySQLJournalSpec` against 9.6.0: reproduced the exact 3 failures from the run linked above; current `main` passes against 9.6.0.
   
   Follow-up: the MariaDB test configs still used the `mysql` schema; moving them to `pekko_persistence_jdbc` too so we don't hit the same footgun there.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org