It’s supposed to work together in clever/mysterious ways. I’m pretty sure you can’t just remove lines from this. OK for troubleshooting, but I guess I’ll have to put them back when I have a real solution to the database problem.
What does lead_event_log do? Is there any way to update/restore/change that table, so the upgrade script doesn’t choke on it again?
Apparently you can use this to clean up “something” involving lead_event_log:
php bin/console mautic:maintenance:cleanup --days-old=365 --dry-run
Without ‘–dry-run’ it executes the actual cleaning and testing again with it looks like old entries have been remove, so lead_event_log has been edited in a way that pleases Mautic?
Contacts still produce the same errors. doctrine:migration:migrate still produces the same error below. Cache clearing still does nothing.
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your S
QL syntax; check the manual that corresponds to your MariaDB server version for the
right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1
doctrine:schema:update produces this error:
An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE c
lient_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191)
DEFAULT NULL':
SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a fo
reign key constraint 'FK_818C32519EB6921'
Any other commands I could try? Trying to find a way to fix whatever in the database is conflicting with the upgrade script.
What does this mean?
Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/myuser/public_html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: HY000): SQLSTATE[HY000]:
Apparently you can circumvent this by (temporarily) disabling foreign key checks in MySQL/MariaDB, with something like this:
ALTER TABLE table_name DISABLE KEYS;
Or
SET FOREIGN_KEY_CHECKS=0;
Or
SET foreign_key_checks = 0;
-- run some queries
SET foreign_key_checks = 1;
Applied to one of the offending pieces of SQL:
SET foreign_key_checks = 0;
ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL,
CHANGE user_id user_id INT UNSIGNED DEFAULT NULL,
CHANGE token token VARCHAR(191) NOT NULL,
CHANGE scope scope VARCHAR(191) DEFAULT NULL;
SET foreign_key_checks = 1
This went through, but now when I try doctrine:schema:update --force I get another error:
LATEST FOREIGN KEY ERROR
------------------------
2022-05-28 11:08:55 0x7fa4040f2700 Error in foreign key constraint of table `mydb_mautic`.`mtc_oauth2_accesstokens`:
FOREIGN KEY (client_id) REFERENCES mtc_oauth2_clients (id) ON DELETE CASCADE:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
Alter table `mydb_mautic`.`mtc_oauth2_accesstokens` with foreign key constraint failed. Field type or character set for column 'client_id' does not mach referenced column 'id' near ' FOREIGN KEY (client_id) REFERENCES mtc_oauth2_clients (id) ON DELETE CASCADE'.
Trying to clear the cache I now get this error:
Unable to write in the "/home/myuser/public_html/mrktng/var/cache/prod" directory
.
After messing with ownership/permission and endless cache clearing and --force whatever, the upgrade finally went through and individual Contact pages work again - but only if I remove all those lines from .htaccess. I started another thread for that issue.