You have an error in your SQL syntax

colineby

Hi -

Help!

Since upgrade from 1.7.8 to 1.7.17 we've been having issues saving updates to Object records. The database didn't change, only the application. The Schema in 158. The database is MariaDB 10.4.42. What could be going wrong?

The full (anonymised) error is:

`Something went wrong
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')) AND (l.is_preferred = 1) ORDER BY loc.name' at line 4 in /opt/providence/app/lib/Db/mysqli.php line 316:

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
In Db_mysqli->execute() in DbStatement line 151

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At DbStatement->executeWithParamsAsArray() in Db line 261

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At Db->query() in LabelableBaseModelWithAttributes line 1812

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At LabelableBaseModelWithAttributes->getLabels() in LabelableBaseModelWithAttributes line 364

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At LabelableBaseModelWithAttributes->replaceLabel() in BundlableLabelableBaseModelWithAttributes line 4045

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At BundlableLabelableBaseModelWithAttributes->saveBundlesForScreen() in BaseEditorController line 302

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At BaseEditorController->Save() in RequestDispatcher line 273

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At RequestDispatcher->dispatch() in AppController line 113

Warning: join(): Invalid arguments passed in /opt/providence/themes/default/views/system/fatal_error_html.php on line 52
At AppController->dispatch() in index line 120
Request parameters:
_formName => ObjectEditorForm
form_timestamp => 1717693538
csrfToken => 27d01b3f59c2cbf16a5d26426d43c3bc861f88096ff382c0122c70e6e5bd5685
idno_accession_number => 20110725-0496
P752ObjectEditorForm_attribute_367_367_113331 =>
P752ObjectEditorForm_attribute_367_locale_id_113331 => 1
P495ObjectEditorForm_Prefname_498 => Lorem ipsum dolor sit amet
P495ObjectEditorForm_Preflocale_id_498 => 1
P491ObjectEditorForm_attribute_493_493_4891 => Lorem ipsum dolor sit amet
P491ObjectEditorForm_attribute_493_locale_id_4891 => 1
P492ObjectEditorForm_attribute_494_494_4892 => 13354-60005
P492ObjectEditorForm_attribute_494_locale_id_4892 => 1
P493ObjectEditorForm_attribute_495_495_4893 => B-1845
P493ObjectEditorForm_attribute_495_locale_id_4893 => 1
P490ObjectEditorForm_attribute_492_492_4894 => Lorem ipsum
P490ObjectEditorForm_attribute_492_locale_id_4894 => 1
P494ObjectEditorForm_autocompletenew_0 =>
P494ObjectEditorForm_idnew_0 =>
P494ObjectEditorFormBundleList => new_0
P561ObjectEditorForm_attribute_507_507_4895 =>
P561ObjectEditorForm_attribute_507_locale_id_4895 => 1
P562ObjectEditorForm_attribute_508_508_4896 =>
P562ObjectEditorForm_attribute_508_locale_id_4896 => 1
P563ObjectEditorForm_attribute_368_368_4897 => 543
P564ObjectEditorForm_attribute_283_283_113332 =>
P501ObjectEditorForm_RelationBundleSortControl =>
P501ObjectEditorForm_RelationBundleSortDirectionControl => ASC
P501ObjectEditorForm_type_id10612 => ltor_17
P501ObjectEditorForm_id10612 => 1232
P501ObjectEditorFormBundleList =>
P744ObjectEditorForm_attribute_359_359_113333 =>
P744ObjectEditorForm_attribute_359_locale_id_113333 => 1
P685ObjectEditorFormBundleList =>
P502ObjectEditorForm_is_primary_521 =>
P502ObjectEditorForm_rep_label_521 => Lorem ipsum dolor sit amet
P502ObjectEditorForm_idno_521 =>
P502ObjectEditorForm_access_521 => 1
P502ObjectEditorForm_status_521 => 0
P502ObjectEditorForm_upload_type521 => upload
P502ObjectEditorForm_center_x_521 =>
P502ObjectEditorForm_center_y_521 =>
P502ObjectEditorForm_ObjectRepresentationBundleList => 521
P503ObjectEditorForm_attribute_63_67_4898 => 30 October 2011
P503ObjectEditorForm_attribute_63_65_4898 => 525
P503ObjectEditorForm_attribute_63_502_4898 => 535
P503ObjectEditorForm_attribute_63_69_4898 =>
P503ObjectEditorForm_attribute_63_locale_id_4898 => 1
P504ObjectEditorForm_attribute_19_19_4899 => Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam...
P504ObjectEditorForm_attribute_19_locale_id_4899 => 1
P679ObjectEditorForm_RelationBundleSortControl =>
P679ObjectEditorForm_RelationBundleSortDirectionControl => ASC
P679ObjectEditorForm_type_id10 => 11
P679ObjectEditorForm_id10 =>
P679ObjectEditorFormBundleList => 10
P680ObjectEditorForm_RelationBundleSortControl =>
P680ObjectEditorForm_RelationBundleSortDirectionControl => ASC
P680ObjectEditorForm_type_id1246 => ltor_50
P680ObjectEditorForm_id1246 => 1
P680ObjectEditorFormBundleList =>
object_id => 512
collection_id =>
above_id =>
is_save_and_return => 0
rel_table =>
rel_type_id =>
rel_id =>
after_id =>`

monica

If you didn't after the upgrade, you will want to clear caches. Run caUtils clear-caches or directly delete everything in the app/tmp directory. If your using a caching system that isn't file (like redis or memcache), you might also need to empty that too.

colineby

The system runs in containers. There is no persistent storage in the container image. The media directory is mounted as a network drive. 'app/tmp' will build up after the container starts of course, but it's not persisted between restarts or upgrades. The upgrades start from a fresh container image. 'app/tmp' should not be a problem, but I'll recheck the image to make sure it's empty in the image.

We are using Memcached, but that was also restarted during the upgrade. Since it's a container with no persistent storage it should be empty as well.

monica

Only other thing I can suggest is reindexing. Version 1.7.10/11 had changes to the indexer.

I don't know the 1.7.* versions very well.

colineby

Any idea if the indexer changes were backward compatible?

I've rolled back 1.7.17 to 1.7.8 to recover our service's functionality. If I roll it forward again, and re-index, will I have to do a database restore, or second re-index if it doesn't work and I have to roll back again?

And just to confirm, rolling back to the previous image does appear to have fixed the service.

monica

I don't know for certain, but I'd imagine you'd need to reindex again.
The database schema isn't different between 1.7.8 and 1.7.17, so it would just be the indexed data itself in the ca_sql_search tables.

To confirm it's the 1.7.10 or 11 release that triggers this, you can go up in versions one at a time testing.

colineby

Tried running about half a dozen maintenance jobs, including the re-index. I'm afraid that made no difference. Does anyone else have any ideas?

seth

Is it possible for me to see the actual system? I know where the error is occurring, but I'm still not sure why. I suspect it's something specific to your system.

colineby

I'm happy to do an online session using my test environment. Or I can try and share my container implementation. Anything you'd like that would help. Can you DM me and tell me what works best?

colineby

We've solved the problem.

In case anyone else comes across something similar.... our specific issue was we used an unsupported locale value in setup.php. This resulted in an empty set of locale IDs in a SQL join. We introduced this problem in the process of rebuilding the whole container from a new baseline. setup.php is one of the files you'd want to parameterise and build from a template in order to drive your container configuration from environment variables (typical practice).

To discover this issue, Seth guided us to dump out the SQL statement when an exception occurred. You do this by adding this line to app/lib/Db/mysqli.php at line 315 (in v1.7.17):

print "<pre>$vs_sql</pre>";

That allowed us to see the problem SQL which was:

SELECT l.*, loc.country locale_country, loc.language locale_language,
loc.dialect locale_dialect, loc.name locale_name
FROM ca_object_representation_labels l
INNER JOIN ca_locales AS loc ON loc.locale_id = l.locale_id
WHERE (l.representation_id = 6202) AND (l.locale_id IN ()) AND (l.is_preferred = 1)
ORDER BY loc.name.

Note the '(l.locale_id IN ())' clause.

seth

In the dev/php8 code you can just put this line in setup.php to see what queries are being run:

define("__CA_LOG_DATABASE_QUERIES__", true);

It'll write all SQL to a log file named "queries" in the app/log directory. Don't leave this option on all the time. The log will get very large quickly, and it does slow things down.

Generally, you shouldn't have to go into the code to debug this sort of thing, but the 1.7 code is older.