Can't upload files in C5.8 - MySQL problem?
Permalink
I have just migrated a C5.8.2.1 site from an old Windows server to a Linux box. Everything seemed to be working fine until I tried uploading images in the CMS.
Folder permissions seem okay.
Checking C5's error log, I found this:
[code]Exception Occurred: /home/mywebsite/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60 An exception occurred while executing 'INSERT INTO Files (fDateAdded, fPassword, fOverrideSetPermissions, ocID, folderTreeNodeID, uID, fslID) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2018-01-19 21:32:04", null, 0, 0, "99", null, 1]:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`my_database`.`Files`, CONSTRAINT `FK_C7F46F5DB81D3903` FOREIGN KEY (`fslID`) REFERENCES `filestoragelocations` (`fslID`)) (0)[code]
Anyone know what the error means or how to fix it?
Folder permissions seem okay.
Checking C5's error log, I found this:
[code]Exception Occurred: /home/mywebsite/public_html/concrete/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:60 An exception occurred while executing 'INSERT INTO Files (fDateAdded, fPassword, fOverrideSetPermissions, ocID, folderTreeNodeID, uID, fslID) VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["2018-01-19 21:32:04", null, 0, 0, "99", null, 1]:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`my_database`.`Files`, CONSTRAINT `FK_C7F46F5DB81D3903` FOREIGN KEY (`fslID`) REFERENCES `filestoragelocations` (`fslID`)) (0)[code]
Anyone know what the error means or how to fix it?
Could be a database capitalisation issue, because Windows and linux handle capitalisation differently. This often causes major problems with table names, but this doesn't seem to be the situation here. I'd be wondering about the foreign keys (FK).
Yes, I'm all too familiar with the table name capitalisation issue - already had to fix that today :-(
I'm not sure how or why that would cause an issue with the FKs though. The error above seems to REFERENCE the table name in lowercase, so maybe that is the issue?
If so, that's worrying - how many other FK's could be wrongly referenced in the database?
I'm not sure how or why that would cause an issue with the FKs though. The error above seems to REFERENCE the table name in lowercase, so maybe that is the issue?
If so, that's worrying - how many other FK's could be wrongly referenced in the database?
This looks like your database table names are all lowercase.
This part of the error code
Should be
So you will need to convert your table names accordingly
You may find a free addon to do this for you here on the concrete site..
This part of the error code
filestoragelocations
Should be
FileStorageLocations
So you will need to convert your table names accordingly
You may find a free addon to do this for you here on the concrete site..
Take a look here
http://www.concrete5.org/marketplace/addons/database-migration...
http://www.concrete5.org/marketplace/addons/database-migration...
I have already converted the table names to CamelCase.
I've just double checked - this table is correctly named 'FileStorageLocations'. The Files table is also capitalised.
Any other ideas?
I've just double checked - this table is correctly named 'FileStorageLocations'. The Files table is also capitalised.
Any other ideas?
The foreign key reference is to a table with an all lower-case name which, of course, doesn't exist. Those FK references are probably what you need to fix.
Have you setup an alternative file storage location in System & Settings?
Does this location path agree with the one stored in the FileStorageLocation table?
Are you trying to upload a file from a file storage location that is not setup in System & Settings and therefore does not exist in the FileStorageLocation table?
Are you using back slashes (windows style) instead of forward slashes in the folder path?
Does this location path agree with the one stored in the FileStorageLocation table?
Are you trying to upload a file from a file storage location that is not setup in System & Settings and therefore does not exist in the FileStorageLocation table?
Are you using back slashes (windows style) instead of forward slashes in the folder path?
Thank you Gondwana and weyboat, your speedy replies both helped me solve the problem.
In a nutshell, it was the lowercase FK references causing the problem. I had already written a script to change the table names to camelCase. In my local Windows dev setup, this script ran in MySQL workbench without renaming the FKs, despite me having the correct setting in MySQL (lower_case_table_names = 2). Probably, if I’d set this temporarily to 0 it would have sorted the problem, but I didn’t think of that until just now :-(
I tried the Database Migration add-on and had the same problem. It looks like a good tool but it did nothing my own script didn’t already do.
In the end, I set up a fresh copy of the original database on the Linux production server. Running the script in the Linux environment fixed the FK references as well as the table names.
Thanks again - really appreciate your help.
In a nutshell, it was the lowercase FK references causing the problem. I had already written a script to change the table names to camelCase. In my local Windows dev setup, this script ran in MySQL workbench without renaming the FKs, despite me having the correct setting in MySQL (lower_case_table_names = 2). Probably, if I’d set this temporarily to 0 it would have sorted the problem, but I didn’t think of that until just now :-(
I tried the Database Migration add-on and had the same problem. It looks like a good tool but it did nothing my own script didn’t already do.
In the end, I set up a fresh copy of the original database on the Linux production server. Running the script in the Linux environment fixed the FK references as well as the table names.
Thanks again - really appreciate your help.
