Errors after running schema:update - foreign key constraint

Product Support
1

When I run php bin/console doctrine:schema:update --force, I get the following error messages:

In AbstractMySQLDriver.php line 128:

  An exception occurred while executing 'ALTER TABLE oauth2_clients CHANGE id id INT UNSIGNED AUTO_INCREMENT NOT NULL, CHANGE name name VARCHAR(191) NOT NULL, CHANGE random_id random_id VARCHAR(191) NOT NULL, CHANGE secret secret VARCHAR(191) NOT NULL':

  SQLSTATE[HY000]: General error: 1833 Cannot change column 'id': used in a foreign key constraint 'FK_818C32519EB6921' of table 'mymauticdb.oauth2_accesstokens'

In Exception.php line 18:

  SQLSTATE[HY000]: General error: 1833 Cannot change column 'id': used in a foreign key constraint 'FK_818C32519EB6921' of table 'mymauticdb.oauth2_accesstokens'

In PDOConnection.php line 141:

  SQLSTATE[HY000]: General error: 1833 Cannot change column 'id': used in a foreign key constraint 'FK_818C32519EB6921' of table 'mymauticdb.oauth2_accesstokens'

doctrine:schema:update [--em EM] [--complete] [--dump-sql] [-f|--force] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

What does this mean? How could I fix it? What could I manually check, change, “reset” in the database (via PhpMyAdmin)?

I usually ignore these errors, without any noticeable issues, but I now have a serious import problem that may be related.

Several people posted the same/similar problem here (2021-22), without a solution. Several others reported the same issue here. Also discussion here and here.

@maumatic apparently has a solution here. The closest thing found on that link that looks like it might be a solution is this recipe - complicated and scary looking, will try this later…

Or was this fixed around/after version 3.3.1, @rcheesley? I see it was reopened as issue.

Edit:

I am trying to follow the instructions here, but am stuck at the SQL via command line stuff. I guess I have to enter my database credentials in those lines, like:

mysql -h"localhost" -u"mydbuser" -p"mydbpassword" -N -B -e 'SELECT concat("ALTER TABLE ", TABLE_NAME, " DROP FOREIGN KEY ", CONSTRAINT_NAME, ";") FROM information_schema.key_column_usage WHERE CONSTRAINT_SCHEMA = "mautic" AND referenced_table_name IS NOT NULL' > schema_update_constraints.sql

But I get error -bash: !p@E": event not found…

Is the syntax correct? Lots of " and '… Am I supposed to replace TABLE_NAME and referenced_table_name with something else?

Edit2:

If I replace double quotes with single quotes and add spaces, it does go through:

mysql -h 'localhost' -u 'mydbuser' -p 'mydbpassword' -N -B -e 'SELECT concat("ALTER TABLE ", TABLE_NAME, " DROP FOREIGN KEY ", CONSTRAINT_NAME, ";") FROM information_schema.key_column_usage WHERE CONSTRAINT_SCHEMA = "mautic" AND referenced_table_name IS NOT NULL' > schema_update_constraints.sql

It then asks for a password, but the next response is Access denied for user 'mydbuser'@'localhost' (using password: YES). Should I do this as root?

Edit3:

Is this issue solved in 4.4.2? Or will upgrading cause a bigger mess?

Your software
My Mautic version is: v4.4.1
My PHP version is: 7.4.30
My Database type and version is: 10.3.36-MariaDB

2

Why do you use schema update force command in prod. env.? Please use bin/console doctrine:migrations:migrate.

Also since it seems like a problem related to incomplete migrations from the past, you might benefit from reading this: MySQL error upgrading to 4.2 - #9 by cwmarketing

I would avoid any upgrades until you resolve the issues with migration.

3

Thanks for the response @mzagmajster

Before I do anything in Mautic I always run these in this order - learnt this in my earliest upgrade hells:

php bin/console doctrine:migration:migrate
php bin/console doctrine:schema:update --force
php bin/console cache:clear

I always get No migrations to execute. on the first command.

I always get a foreign key constraint error on the second and have mostly ignored these until now, because it didn’t seem to cause any problems.

I now want to fix this, because now I do have problems with failing imports related to foreign key constraints.

As pointed out in my previous post, I am not the only one who has run into this issue and am pretty sure it has nothing to do with me not running migrate enough.

I would avoid any upgrades until you resolve the issues with migration.

How can I resolve the issues?

4

I would try to manually execute migrations using the guide I posted above.

The thing with the update force command is that its doing comparison ORM vs schema in database, so before running it, I would also look at what is trying to execute. You can do so by:

php bin/console doctrine:schema:update ---dump-sql

Also if you update mautic cli I kmow that migrations get executed during:

php bin/console mautic:update:apply --finish

Maybe thats why you always get no migrations to execute.

How to solve ti?

I would inspect the differences in schema vs ORM closely. Maybe even setup clean mautic install to just so that I can really see what final schema should look like. Try following the steps I outlined on the link I shared.

Best of luck :slight_smile:

5

@mzagmajster, I don’t recognize any of the issues in that MySQL error upgrading to 4.2 - #9 by cwmarketing thread; syntax errors, missing tables, etc., it is all miles away from my problem.

I have posted links to several threads that are very similar to my problem, including several hints - utf8mb4 vs utf8 encoding seems to have something to do with it - and even one possible solution. You are pointing me into a completely different direction that does not look promising at all.

I am not an experienced database/SQL coder, so dumping a lot of SQL and ‘inspect the differences in schema vs ORM closely’ is not going to get me anywhere - what does that mean, what would I be looking for, is that related to the encoding issue?

And yes, of course you can always delete and start over, but that is a last resort disaster option.

Does anyone have any insight in this solution? Does it make sense?

Is this issue a recognized bug that @rcheesley and developers are working on? I can’t really tell from the back-and-forths in the various threads where this stands at the moment.

1 Like
6

It all falls under the same category more or less. The error you posted above indicates that migration is trying to change column type but it cant because this column is referenced by another table.

What might work is turning FK checks off and on, thats why I shared the link. By installing fresh mautic instance, I meant so that you have a reference how database schema should look like, I was not suggesting you replace production database.

What I am trying to say here is that you need to check if the statement that migration is trying to execute is actually required based on current status of the database schema - it could be that statement is showing in schema:update command, but was actually already executed.

If you read post carefully you can see that, what I shared is similar - its just another way to the finish line. When I was encountering problems with migrations, I used approach outlined in my post so I can speak for that. But the approach outlined in GH post from above should also work - if you are not comfortable with SQL, consider getting a professional help to properly resolve the issue - thats because you should really understand the implications of the commands.

And please always make backups.

2 Likes
7

The error you posted above indicates that migration is trying to change column type but it cant because this column is referenced by another table.

Why does that happen? Will it keep happening even if I fix it?

What might work is turning FK checks off

I’ve tried that. That’s how I managed to get through an earlier upgrade hell - perhaps making things worse? Others have reported trying to use turning off FK checks in this case and having no luck with it.

if you are not comfortable with SQL, consider getting a professional help to properly resolve the issue

I can’t afford that. If I could I would buy commercial software or have custom development for everything.

Applications like Mautic are supposed to take care of the database; I shouldn’t have to dig deep into the database and do risky manual ad hoc fixes.

It’s just another thing delaying my business where I spent weekend after weekend googling and learning different parts of the web development stack.

I’m not the only one running into this issue, but I haven’t seen any substantial response or solution from Acquia or the Mautic development team. Is the final word really ‘oops, Mautic messed things up again, go hire a developer to sort it out you noob’?

8

I think it has to do with column type changes that happened between Mautic 2 & 3. But its also usually related to incomplete upgrades from the past. As long as you take care of database from that point on and properly execute the migrations, it should not.

I hope that you turn FK checks back on when you were done.

Mautic has its flaws, but so has every other software, thats not really news. :slight_smile:

When you come to this types of issues, the exact solution usually differs form installation to installation. The best you can do is provide some pointers and tell people to take care of database.

Even if someone would have wrote the migration file that would resolve your troubles it most likely would not resolve similar issue that others have.

Warning for future readers

That being said. I would like to take this opportunity to again warn people against blindly executing in production environment:

php bin/console doctrine:schema:update --force

automatically generating migration statements can be of great help when you are actually composing migration file during development, but its terrible idea when you are running it against production database without any backup. Those statements are generated by Doctrine itself, and Doctrine tries to be smart, which does not always produce SQL statements you need in a given situation.

Always inspect what kind of statements automatic migration generator tries to execute, before actually executing the SQL statements. The extra step is well worth it.

Regards, M.

9

The advice to run doctrine:schema:update --force originally came from here; the recommended processes that Mautic provided simply did not work.

Trying to use Mautic for my business as an end user, I’ve always had to scour the internet for solutions and explanations to make things work. Three years later nothing has changed.

automatically generating migration statements can be of great help when you are actually composing migration file during development, but its terrible idea when you are running it against production database

Why does Mautic even provide that command if it is so dangerous/destructive to use? I have no idea what migration statements are; I thought I could rely on the Mautic application to take care of the complicated stuff.

Now I’ll be googling “migration statements”, “database schemas”, indexes and “foreign keys in MySQL” and learning the intricacies of database programming for the next couple of weekends. Yay!

Edit:

Also since it seems like a problem related to incomplete migrations from the past, you might benefit from reading this: MySQL error upgrading to 4.2 - #9 by cwmarketing

I see I had already found that thread two months ago and couldn’t find anything useful in that thread - apologies for posting what turned out to be an irrelevant side issue there.

That thread has lots of different problems with people trying lots of different solutions; some work, others don’t. Recipes for bigger messes.

I’ll start from scratch, starting here - I wouldn’t call that a “solution”, hints for troubleshooting at best… - and then googling and doubling-checking everything. Will post my findings in a next comment on this thread.

Why don’t migrations just work in Mautic?

10

Starting from scratch trying to solve migrations mess, following this advice here.

A first step is to run php bin/console doctrine:migrations:migrate. I had earlier mentioned that I never have anything to migrate here, but I do get a list of stuff from 2015-2019. I always assumed these were just old archives, but scrolling up I see this message:

WARNING! You have 134 previously executed migrations in the database that are not registered migrations.

Googling that leads me here where @rcheesley explains ‘We had to make some decisions on how to handle old migrations and this is I believe a result of that’ - sounds solid!

Also this: ‘… we are going to be writing up some documentation on this for developers as it caused a bit of a problem for MariaDB users.’ What is the status of that? Where can I find that documentation? I am a MariaDB user. Sucks to be me?

She advices to run a database schema check when that shows up - which perhaps prompted someone else to advice to run doctrine:schema:update --force

If I try to update schema via the browser using /s/update/schema as adviced, I get Database schema is already up-to-date.

Should I ignore the error I get when I run doctrine:schema:update --force? I am still stuck with this csv import problem that I assumed was related.

If that doesn’t solve the issue, rcheesley advices to look for outstanding database migrations with php bin/console doctrine:migration:status

This gives me the following useful looking info:

`

== Configuration

>> Name:                                               Mautic Migrations
>> Database Driver:                                    pdo_mysql
>> Database Host:                                      localhost
>> Database Name:                                      mymauticdb
>> Configuration Source:                               manually configured
>> Version Table Name:                                 migrations
>> Version Column Name:                                version
>> Migrations Namespace:                               Mautic\Migrations
>> Migrations Directory:                               /home/myusername/public_html/mautic/app/migrations
>> Previous Version:                                   2021-06-23 07:13:26 (20210623071326)
>> Current Version:                                    2022-01-11 20:29:17 (20220111202917)
>> Next Version:                                       Already at latest version
>> Latest Version:                                     2022-01-11 20:29:17 (20220111202917)
>> Executed Migrations:                                181
>> Executed Unavailable Migrations:                    134
>> Available Migrations:                               47
>> New Migrations:                                     0

`

Are those 47 ‘Available Migrations’ supposed to run? When I hit ‘yes’ on doctrine:migrations:migrate - ignoring the WARNING at the top… - I get No migrations to execute. As usual, this is what I always get.

Is this an irrelevant tangent/dead-end/distraction? Moving back to the CSV import problem thread that lead me here…

11

In short: based on status above your database schema is up to date.

More details:

You probably already executed 47 migrations when you upgraded mautic core.

The difference in numbers happens because in your migrations directory on the file system:

app/migrations

there are only 47 files, but in your migrations table inside database there are 181 migrations that referenced of which 134 migrations cannot be linked to the files on the filesystem (because the files do not exist anymore).

12

The database schema is not up to date - ‘not in sync with the current mapping file’.

13

I do not think this is the reason for your troubles. When I execute schema:validate I get the same error, but my mautic works normally.

14

My Mautic works normally as well, except for failing CSV imports - that’s why I moved back to that thread. Should I ignore all Mautic WARNINGs, error messages and fails then?

15

No you should not ignore warnings and fails, I am saying that schema:validate will probably show the same output for every other user out there. So import contacts stopper has to be somewhere else.

Did you solve the problems with FKs?

16

No, I’m currently back here - although that led me back to schema issues, so maybe I should continue here?

17

I would set debug flag to true in app/config/local.php and try to get more info on the problem. I would not stress so much about the mapping problem just yet.

Like I said, when I do schema:validate on my mautic instance the same output appears and my import works OK.