[Bug]: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION db.PLUGIN_CMF_COLLECT_OBJECT_SEGMENT_ASSIGNMENTS does not exist
swina opened this issue · 15 comments
Expected behavior
After a fresh installation of Pimcore and CMF and enabled the packagea and created a Customer Data Object, update the configuration files should be everything ok (no data just Class definitions)
Environment (Docker):
- Pimcore 10.5.15
- Symfony: 5.4.18
Actual behavior
Error in Pimcore Admin
Timestamp: Fri Jan 20 2023 12:08:23 GMT+0300 (GMT+03:00)
Status: 500 | Internal Server Error
URL: /admin/customermanagementframework/segment-assignment/inheritable-segments?id=2&type=object&_dc=1674205703397
Method: GET
Message: An exception occurred while executing 'SELECT PLUGIN_CMF_COLLECT_OBJECT_SEGMENT_ASSIGNMENTS(?)' with params ["1"]:
SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION db.PLUGIN_CMF_COLLECT_OBJECT_SEGMENT_ASSIGNMENTS does not exist
Steps to reproduce
Pimcore Admin with no Exceptions or errors
Hi @swina , I tried reproducing this issue but failed in multiple attempts. After the installation, everything is working as expected and I get no errors in the admin dashboard as you mentioned. I did not get any error on the mentioned url as well. Are there any specific configurations that might be missing?
From the error, I only can think of the installer not being run properly or may have been interrupted somehow. Could you check if the bundle is installed properly by running bin/console pimcore:bundle:list. If the bundle is not installed, try running bin/console pimcore:bundle:install PimcoreCustomerManagementFrameworkBundle. This should add the necessary stored procedure that seems to be missing in your case.
Hope this helps.
Hi @aashan10 (cc @swina) - I can confirm that I experience the same issue with
- cmf v3.3.6
- pimcore v10.5.15
- php 8.0-fpm (official docker image)
- mariadb:10.5 (official docker image)
I have debugged into CMF's installer.php and added debug output around lines 238:
__DIR__ . '/Resources/sql/filterDefinition/' => [
'datamodel.sql'
],
__DIR__ . '/Resources/sql/segmentAssignment/' => [
'datamodel.sql',
'storedFunctionDocument.sql',
'storedFunctionAsset.sql',
'storedFunctionObject.sql',
],
__DIR__ . '/Resources/sql/activityMetadata/' => [
'datamodel.sql'
]
];
$db = Db::get();
foreach ($sqlFiles as $folder => $files) {
foreach ($files as $file) {
echo "CMF Install: SQL Folder: $folder File: $file\n";
echo "---------------------------\n\n\n\n";
$statement = file_get_contents($folder.$file);
echo $statement;
echo "\n\n\n---------------------------\n";
$res = $db->executeQuery($statement);
/*echo $res;
echo "\n\n\n---------------------------\n";*/
var_dump($res->fetchAllAssociative());
echo "\n\n\n---------------------------\n\n\n\n\n";
}
}
I can see that it successfully reads /Resources/sql/segmentAssignment/storedFunction*.sql and there is no (perceivable) error, but the function doesn't in fact get created.
Weirdly, this does not affect the TABLE definitions from the other SQL files.
Also just checked: If I connect to the DB with the same user pimcore uses and run the content of /Resources/sql/segmentAssignment/storedFunction*.sql, it creates the routines. So it's not a DB permission issue (would have expected to see an error if it was anyhow).
@aashan10 Are you using PDO of mysqli?
Working around like this in my local CI/dev setup now
echo "Installing CMF stored procedures (CMF installer doesn't install them properly"
for sqlFile in `find /var/www/html/vendor/pimcore/customer-management-framework-bundle/src/Resources/sql/ -name "stored*.sql"`; do
echo "SQL File: $sqlFile"
if [ -z "$MYSQL_HOST" ]; then MYSQL_HOST="db"; fi
if [ -z "$MYSQL_PORT" ]; then MYSQL_PORT="3306"; fi
if [ -z "$MYSQL_DB" ]; then MYSQL_DB="pimcore"; fi
if [ -z "$MYSQL_USER" ]; then MYSQL_USER="pimcore"; fi
if [ -z "$MYSQL_PASSWORD" ]; then MYSQL_USER="DEFAULT_LOCAL_DB_PASSWORD_HERE"; fi
if [ ! -z "$PIMCORE_INSTALL_MYSQL_USERNAME" ]; then MYSQL_USER="$PIMCORE_INSTALL_MYSQL_USERNAME"; fi
if [ ! -z "$PIMCORE_INSTALL_MYSQL_PASSWORD" ]; then MYSQL_PASSWORD="$PIMCORE_INSTALL_MYSQL_PASSWORD"; fi
cat $sqlFile | mysql -u "$MYSQL_USER" --password="$MYSQL_PASSWORD" -h "$MYSQL_HOST" -P $MYSQL_PORT $MYSQL_DB
done
Upon further investigation, I received this message of deprecated query being used. I think this also needs to be fixed. But this is still not the cause for the issue.
Hi, the problem maybe if you are going to use a db on AWS (like RDS with MariaDb) normally you are not the superadmin and you haven't granted access to execute them.
@swina I could reproduce this issue in the official pimcore docker image (which has pimcore user having all permissions already available). So this might not be the problem. I changed some things in the code and created a pr which is working for me for the default mariadb user in the official docker image. #413
Interestingly, this problem only persists if you already have a skeleton project and you try installing customer data framework bundle via composer.
Interestingly, this problem only persists if you already have a skeleton project and you try installing customer data framework bundle via composer.
Not able to reproduce it locally so closing #413 as won't fix. Please feel free to provide a fix with reproducible case. thanks!
@HageMaster3108 I tried with fresh setup and installed CMF. After that there was no exception in admin when creating customer data object (as per mentioned steps in the issue). My concern about #413 is that it removes delimiter introduced in the fix #112 so this could resurface the original issue. Do you have a better solution to fix it?