Schemifier and indexes in a migrated mysql db

[Andrew Bucknell]

I have an old liftweb app that used lift 2.6 and mysql 5.7. I've recently dusted it off and am trying to make it work properly. To date this has gone pretty smoothly. So far I have

1. Updated to lift 3.3.0

2. Updated to scala 2.11.12

3. Updated to JDK 14

So far so good.

I am currently trying to make it work on mysql 8 and have run into a problem where the mapper schemifier is not finding indexes. I dumped the 5.7 database to an export file and loaded into mysql 8. Now when I start my app I get

19:55:36.521 [main] INFO  net.liftweb.mapper.Schemifier - CREATE INDEX offer_location ON offer ( location )

java.sql.SQLSyntaxErrorException: Duplicate key name 'offer_location'

and in mysql the index does exist

# Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment, Visible, Expression

'offer', '1', 'offer_location', '1', 'location', 'A', '14', NULL, NULL, 'YES', 'BTREE', '', '', 'YES', NULL

I'm looking in Schemifier.scala and as far as I can tell it should only be trying to create the key if the key name doesnt exist. Any thoughts on why Schemifier isnt finding the index?

[Howard Palmer]

I encountered this same error on Lift 2.6, and it seems related to some change in behavior of MySQL 8 vs. 5.7.

Specifically, in Schemifier.scala, the getIndexInfo call in ensureIndexes is returning more values in the ResultSet with connector 8 than with 5.7. As the function, quad,

builds up its list, that list ends up containing duplicates.

I solved it by changing the top-level quad(rs) call in the calculation of "val q" to quad(rs).distinct to remove the duplicates.

I have not investigated the ResultSet in detail to see how the duplicates arise. I suspect they are not literally duplicates in the ResultSet, but differ by the TYPE column,

i.e.rs.getShort(7).

[Andrew Bucknell]

Thanks for the reply Howard. Good to know I'm not alone :) I think the key will be not relying on Schemifier til I get everything manually cleaned up.