pounard
commented
7 months ago OK I fixed the CREATE FUNCTION statement, MySQL doesn't support IF NOT EXISTS and the return type cannot be BIGINT but must be INTEGER instead. Easy one.
Now, we have another much harder problem:
update `table_test`
inner join `table_test` as `_target_table`
on (`table_test`.`_db_tools_id` = `_target_table`.`_db_tools_id`)
left outer join (
select `value`,
ROW_NUMBER() OVER (ORDER BY rand()) as `rownum`
from `_db_tools_sample_662910cfef262`
limit 3
) as `_db_tools_sample_662910cfef262_value`
on (`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910cfef262_value`.`rownum`
and `_target_table`.`value` is not null)
left outer join (
select `iban`, `bic`,
ROW_NUMBER() OVER (ORDER BY rand()) as `rownum`
from `_db_tools_sample_662910d0090ed`
limit 3
) as `_db_tools_sample_662910d0090ed_foo` on (
`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910d0090ed_foo`.`rownum`)
set
`table_test`.`value` = `_db_tools_sample_662910cfef262_value`.`value`,
`table_test`.`my_iban` = `_db_tools_sample_662910d0090ed_foo`.`iban`,
`table_test`.`my_bic` = `_db_tools_sample_662910d0090ed_foo`.`bic
;This cannot work because MySQL 5.7 has not implemented the WINDOW functions yet.
We could probably emulate it using this trick: https://stackoverflow.com/questions/1895110/row-number-in-mysql which would give then:
update `table_test`
inner join `table_test` as `_target_table`
on (`table_test`.`_db_tools_id` = `_target_table`.`_db_tools_id`)
left outer join (
select `value`,
@rownum := @rownum + 1 AS `rownum`
from `_db_tools_sample_662910cfef262`
limit 3
) as `_db_tools_sample_662910cfef262_value`
on (`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910cfef262_value`.`rownum`
and `_target_table`.`value` is not null)
left outer join (
select `iban`, `bic`,
@rownum := @rownum + 1 AS `rownum`
from `_db_tools_sample_662910d0090ed`
limit 3
) as `_db_tools_sample_662910d0090ed_foo` on (
`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910d0090ed_foo`.`rownum`)
set
`table_test`.`value` = `_db_tools_sample_662910cfef262_value`.`value`,
`table_test`.`my_iban` = `_db_tools_sample_662910d0090ed_foo`.`iban`,
`table_test`.`my_bic` = `_db_tools_sample_662910d0090ed_foo`.`bic
;Which would require to hack for MySQL 5.7 only.
Before we had one problem, now we have two, because the WINDOW function used here is generated by makinacorpus/query-builder and there is no easy solution for patching it, because ROW_NUMBER() OVER (ORDER BY rand()) cannot simply be emulated (it's a complex expression).
I guess we can target the exact place where we instanciate this expression in db-tools-bundle in order to replace it with a raw expression for MySQL 5.7, I have to test that and pray it works.
For now the DbToolsBundle does not work properly with MySQL5.7.
But as it is still common to encounter this version in the wild, it could be nice to fix issues we have and officially support this version.
For example for now, when I launch tests with this version I get this kind of errors: