Cannot add or update a child row: a foreign key constraint fails on Files table
Permalink
I setup a new version of Concrete5 v8.3.2 using XAMPP on a windows development system. Early on I discovered the issue with lowercase table names and ran the database migration addon to fix the problem. After coding all my custom blocks and migrating my template design, I used the migration tools to migrate my site from version 5.6 on my production server to v 5.8.3 on the development server. The site worked great on the development server.
Today I tried to migrate the site and ran into problems with file manager. To make life easier, I setup a completely new account on webhostinghub with one of my unused domains and installed concrete5 v 8.3.2 from scratch . I then copied over all the necessary files from the development server per the directions in the developer docs under moving a site, installed the blocks and various packages with no problems. During this process I was able to add and delete file from file manger with no problems.
As the last step I exported the database from the development system using the export command in MariaDB (I don't think I have access to the command line). On the hosting site I cleared the cache, set foreign_key_checks=0, dropped all the tables and then imported the table from my development server. After everything was done I set foreign_key_checks=1. Everything seemed to be working great except file manager.
If I tried to drag and drop a file into filemanger it first turns green but at the end of the upload the file includes a red bar below the image. When I choose to ed the attributes I get bounced out of file manager. If I justr try to close the window after dragging and dropping the file, the image does not appear in file manager.
If I try to add a file using the incoming directory I get the following error:
An exception occurred while executing 'INSERT INTO Files (fDateAdded, fPassword, fOverrideSetPermissions, ocID, folderTreeNodeID, uID, fslID) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2018-02-06 15:57:20", null, 0, 0, "7", 1, 1]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`bootjo6_conc801`.`Files`, CONSTRAINT `FK_C7F46F5DFD71026C` FOREIGN KEY (`uID`) REFERENCES `users` (`uID`) ON DELETE SET NULL)
Details
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php(60): Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(128): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(177): Doctrine\DBAL\DBALException->driverExceptionDuringQuery
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(107): Doctrine\DBAL\Driver\PDOException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(168): Doctrine\DBAL\Driver\PDOStatement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(281): Doctrine\DBAL\Statement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1014): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->executeInserts
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(378): Doctrine\ORM\UnitOfWork->executeInserts
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(356): Doctrine\ORM\UnitOfWork->commit
/home/bootjo6/public_html/concrete/src/Entity/File/File.php(192): Doctrine\ORM\EntityManager->flush
/home/bootjo6/public_html/concrete/src/Entity/File/File.php(326): Concrete\Core\Entity\File\File->save
/home/bootjo6/public_html/concrete/src/File/File.php(119): Concrete\Core\Entity\File\File->setUser
/home/bootjo6/public_html/concrete/src/File/Importer.php(255): Concrete\Core\File\File->add
/home/bootjo6/public_html/concrete/tools/files/importers/incoming.php(51): Concrete\Core\File\Importer->importIncomingFile
/home/bootjo6/public_html/concrete/src/View/View.php(233): null->include
/home/bootjo6/public_html/concrete/src/View/View.php(211): Concrete\Core\View\View->renderInnerContents
/home/bootjo6/public_html/concrete/src/View/AbstractView.php(151): Concrete\Core\View\View->renderViewContents
/home/bootjo6/public_html/concrete/src/Routing/ControllerRouteCallback.php(41): Concrete\Core\View\AbstractView->render
/home/bootjo6/public_html/concrete/src/Routing/Router.php(149): Concrete\Core\Routing\ControllerRouteCallback->execute
/home/bootjo6/public_html/concrete/src/Http/DefaultDispatcher.php(114): Concrete\Core\Routing\Router->execute
/home/bootjo6/public_html/concrete/src/Http/DefaultDispatcher.php(54): Concrete\Core\Http\DefaultDispatcher->handleDispatch
/home/bootjo6/public_html/concrete/src/Http/Middleware/DispatcherDelegate.php(39): Concrete\Core\Http\DefaultDispatcher->dispatch
/home/bootjo6/public_html/concrete/src/Http/Middleware/ThumbnailMiddleware.php(71): Concrete\Core\Http\Middleware\DispatcherDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\ThumbnailMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/FrameOptionsMiddleware.php(39): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\FrameOptionsMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/CookieMiddleware.php(37): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\CookieMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/ApplicationMiddleware.php(29): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\ApplicationMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareStack.php(86): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/DefaultServer.php(85): Concrete\Core\Http\Middleware\MiddlewareStack->process
/home/bootjo6/public_html/concrete/src/Foundation/Runtime/Run/DefaultRunner.php(115): Concrete\Core\Http\DefaultServer->handleRequest
/home/bootjo6/public_html/concrete/src/Foundation/Runtime/DefaultRuntime.php(102): Concrete\Core\Foundation\Runtime\Run\DefaultRunner->run
/home/bootjo6/public_html/concrete/dispatcher.php(36): Concrete\Core\Foundation\Runtime\DefaultRuntime->run
/home/bootjo6/public_html/index.php(3): null->require
I have read posts I found in the forums from others that seemed to have similar problems. The fixes they have found -- such as looking at the case of the tables, does not appear to be my problem. As best I can tell it seem to be choking on the userid.
Right now there is only one entry in my user table and that is for the admin account.
I am hardly an expert at SQL or MariaDB. I sincerely appreciate it if someone could point me in the right direction. I hav been pulling my hair out trying to solve this last problem. After several weeks of work migrating the site it seems like I am almost there...
Today I tried to migrate the site and ran into problems with file manager. To make life easier, I setup a completely new account on webhostinghub with one of my unused domains and installed concrete5 v 8.3.2 from scratch . I then copied over all the necessary files from the development server per the directions in the developer docs under moving a site, installed the blocks and various packages with no problems. During this process I was able to add and delete file from file manger with no problems.
As the last step I exported the database from the development system using the export command in MariaDB (I don't think I have access to the command line). On the hosting site I cleared the cache, set foreign_key_checks=0, dropped all the tables and then imported the table from my development server. After everything was done I set foreign_key_checks=1. Everything seemed to be working great except file manager.
If I tried to drag and drop a file into filemanger it first turns green but at the end of the upload the file includes a red bar below the image. When I choose to ed the attributes I get bounced out of file manager. If I justr try to close the window after dragging and dropping the file, the image does not appear in file manager.
If I try to add a file using the incoming directory I get the following error:
An exception occurred while executing 'INSERT INTO Files (fDateAdded, fPassword, fOverrideSetPermissions, ocID, folderTreeNodeID, uID, fslID) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2018-02-06 15:57:20", null, 0, 0, "7", 1, 1]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`bootjo6_conc801`.`Files`, CONSTRAINT `FK_C7F46F5DFD71026C` FOREIGN KEY (`uID`) REFERENCES `users` (`uID`) ON DELETE SET NULL)
Details
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php(60): Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(128): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(177): Doctrine\DBAL\DBALException->driverExceptionDuringQuery
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(107): Doctrine\DBAL\Driver\PDOException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOException->null
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(168): Doctrine\DBAL\Driver\PDOStatement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php(281): Doctrine\DBAL\Statement->execute
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(1014): Doctrine\ORM\Persisters\Entity\BasicEntityPersister->executeInserts
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php(378): Doctrine\ORM\UnitOfWork->executeInserts
/home/bootjo6/public_html/concrete/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php(356): Doctrine\ORM\UnitOfWork->commit
/home/bootjo6/public_html/concrete/src/Entity/File/File.php(192): Doctrine\ORM\EntityManager->flush
/home/bootjo6/public_html/concrete/src/Entity/File/File.php(326): Concrete\Core\Entity\File\File->save
/home/bootjo6/public_html/concrete/src/File/File.php(119): Concrete\Core\Entity\File\File->setUser
/home/bootjo6/public_html/concrete/src/File/Importer.php(255): Concrete\Core\File\File->add
/home/bootjo6/public_html/concrete/tools/files/importers/incoming.php(51): Concrete\Core\File\Importer->importIncomingFile
/home/bootjo6/public_html/concrete/src/View/View.php(233): null->include
/home/bootjo6/public_html/concrete/src/View/View.php(211): Concrete\Core\View\View->renderInnerContents
/home/bootjo6/public_html/concrete/src/View/AbstractView.php(151): Concrete\Core\View\View->renderViewContents
/home/bootjo6/public_html/concrete/src/Routing/ControllerRouteCallback.php(41): Concrete\Core\View\AbstractView->render
/home/bootjo6/public_html/concrete/src/Routing/Router.php(149): Concrete\Core\Routing\ControllerRouteCallback->execute
/home/bootjo6/public_html/concrete/src/Http/DefaultDispatcher.php(114): Concrete\Core\Routing\Router->execute
/home/bootjo6/public_html/concrete/src/Http/DefaultDispatcher.php(54): Concrete\Core\Http\DefaultDispatcher->handleDispatch
/home/bootjo6/public_html/concrete/src/Http/Middleware/DispatcherDelegate.php(39): Concrete\Core\Http\DefaultDispatcher->dispatch
/home/bootjo6/public_html/concrete/src/Http/Middleware/ThumbnailMiddleware.php(71): Concrete\Core\Http\Middleware\DispatcherDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\ThumbnailMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/FrameOptionsMiddleware.php(39): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\FrameOptionsMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/CookieMiddleware.php(37): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\CookieMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/ApplicationMiddleware.php(29): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareDelegate.php(38): Concrete\Core\Http\Middleware\ApplicationMiddleware->process
/home/bootjo6/public_html/concrete/src/Http/Middleware/MiddlewareStack.php(86): Concrete\Core\Http\Middleware\MiddlewareDelegate->next
/home/bootjo6/public_html/concrete/src/Http/DefaultServer.php(85): Concrete\Core\Http\Middleware\MiddlewareStack->process
/home/bootjo6/public_html/concrete/src/Foundation/Runtime/Run/DefaultRunner.php(115): Concrete\Core\Http\DefaultServer->handleRequest
/home/bootjo6/public_html/concrete/src/Foundation/Runtime/DefaultRuntime.php(102): Concrete\Core\Foundation\Runtime\Run\DefaultRunner->run
/home/bootjo6/public_html/concrete/dispatcher.php(36): Concrete\Core\Foundation\Runtime\DefaultRuntime->run
/home/bootjo6/public_html/index.php(3): null->require
I have read posts I found in the forums from others that seemed to have similar problems. The fixes they have found -- such as looking at the case of the tables, does not appear to be my problem. As best I can tell it seem to be choking on the userid.
Right now there is only one entry in my user table and that is for the admin account.
I am hardly an expert at SQL or MariaDB. I sincerely appreciate it if someone could point me in the right direction. I hav been pulling my hair out trying to solve this last problem. After several weeks of work migrating the site it seems like I am almost there...
A wild guess here. The Table name case tool may not update the table names held in foreign key definitions. Do a MySQL dump, then in a text editor browse the foreign key definitions and see if the table names are correct.
Hi John,
Here are a few lines from the SQL file used to import information into the 8.3.2 system:
ALTER TABLE `atFile`
ADD CONSTRAINT `FK_73D17D61A2A82A5D` FOREIGN KEY (`avID`) REFERENCES `attributevalues` (`avID`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_73D17D61E3111F45` FOREIGN KEY (`fID`) REFERENCES `files` (`fID`) ON DELETE CASCADE;
ALTER TABLE `Files`
ADD CONSTRAINT `FK_C7F46F5DB81D3903` FOREIGN KEY (`fslID`) REFERENCES `filestoragelocations` (`fslID`),
ADD CONSTRAINT `FK_C7F46F5DFD71026C` FOREIGN KEY (`uID`) REFERENCES `users` (`uID`) ON DELETE SET NULL;
The names looked like they are correct from a case standpoint -- but maybe I am wrong.
At this point, I think I might just start over and do the migration directly to the new hosting account I setup on webhostinghub . This way I will be going unix to unix system.
Wasting too much time at this point trying to figure out the SQL problems.
Thanks for you help.
Here are a few lines from the SQL file used to import information into the 8.3.2 system:
ALTER TABLE `atFile`
ADD CONSTRAINT `FK_73D17D61A2A82A5D` FOREIGN KEY (`avID`) REFERENCES `attributevalues` (`avID`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_73D17D61E3111F45` FOREIGN KEY (`fID`) REFERENCES `files` (`fID`) ON DELETE CASCADE;
ALTER TABLE `Files`
ADD CONSTRAINT `FK_C7F46F5DB81D3903` FOREIGN KEY (`fslID`) REFERENCES `filestoragelocations` (`fslID`),
ADD CONSTRAINT `FK_C7F46F5DFD71026C` FOREIGN KEY (`uID`) REFERENCES `users` (`uID`) ON DELETE SET NULL;
The names looked like they are correct from a case standpoint -- but maybe I am wrong.
At this point, I think I might just start over and do the migration directly to the new hosting account I setup on webhostinghub . This way I will be going unix to unix system.
Wasting too much time at this point trying to figure out the SQL problems.
Thanks for you help.
Actually -- the statements are not correct since the table names are not in the right case. Strange since I set the option on MariaDB on my local system to act like a unix system from a case standpoint. In any case, I think at this point redoing the migration is the best option.
The references parts are all lowercase.
attributevalues should be AttributeValues etc.
