0

I'm trying to update my D9 dev site database using my production database, but it keeps erroring out when it reaches to ALTER table section in the database.

This is the ERROR 1068 (42000) at line 37370: Multiple primary key defined.

Error right when it reaches:

ALTER TABLE ban_ip ADD PRIMARY KEY (iid), ADD KEY ip (ip)

I'm using:

  • PHP8.1.23
  • MYSQL 8.0.32
  • Drupal 9

Never had this issue until last week. Does anyone have any thoughts on this?

Improve this question
3
  • What is the import process you use? Add that to the question.
    – mona lisa
    Commented Sep 13, 2023 at 23:14
  • Also: What did you change last week? Add that to the question too.
    – mona lisa
    Commented Sep 13, 2023 at 23:42
  • Figured it out. It's because Azure MySQL Flex updated to Mysql8.0.30+ and had sql_generate_invisible_primary_key turned to "ON" when the default should be "OFF". I'll write the answer at the bottom in case anyone sees this. Big fan of yours cilefen. You're huge in the Drupal community!
    – MetalBearSolid
    Commented Sep 14, 2023 at 0:20

1 Answer 1

Reset to default
1

Azure MySQL Flex updated to MySQL8.0.30+ and had sql_generate_invisible_primpary_key turned to "ON" when the default should be "OFF". So essentially it's generating invisible primary keys causing the error Multiple Primary Key defined.

To fix it here are the steps:

  1. Login to azure
  2. Go to your MySQL Flex server
  3. Search for "Server Parameters" on the left sidebar
  4. Click "ALL" tab in the middle of the page
  5. Search for "sql_generate_invisible_primary_key"
  6. Turn it "OFF"
  7. Save and done!

Hope this helps anyone who has this issue. Good luck!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.