fusionauth-issues
fusionauth-issues copied to clipboard
[Bug]: SQLSyntaxError: MySQL server version for the right syntax to use near 'ids AS`
What happened?
Fast Installation using Debian/Ubuntu Setup step with MySQL 5.7 on Ubuntu 20.04LTS edition when viewing Application or Tenents admin page generate the error
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ids AS (
It is because the INNER JOIN Query doesn't put table name identities
to query it generate part query as
INNER JOIN ids ON ids.id = a.id INNER JOIN tenants AS t ON t.id = a.tenants_id
Version
1.48.3
Affects Versions
No response
Edit: If I change Inner JOIN ids ON ids.id=a.id
to INNER JOIN identities ids ON ids.id=a.id
then query works on MYSQL (on server directly) But I am not sure where I can change that in code or how I change that. Seems some sort of typo
Query as shown in Log
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ids AS (
SELECT id
FROM tenants t
ORDER BY t.n' at line 1
### The error may exist in io/fusionauth/api/domain/TenantMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: WITH ids AS ( SELECT id FROM tenants t ORDER BY t.name ASC LIMIT ? OFFSET ? ) SELECT t.id AS t_id, t.access_token_signing_keys_id AS t_access_token_signing_keys_id, t.data AS t_data, t.client_credentials_access_token_populate_lambdas_id AS t_client_credentials_access_token_populate_lambdas_id, t.confirm_child_email_templates_id AS t_confirm_child_email_templates_id, t.email_update_email_templates_id AS t_email_update_email_templates_id, t.email_verified_email_templates_id AS t_email_verified_email_templates_id, t.failed_authentication_user_actions_id AS t_failed_authentication_user_actions_id, t.family_request_email_templates_id AS t_family_request_email_templates_id, t.forgot_password_email_templates_id AS t_forgot_password_email_templates_id, t.id_token_signing_keys_id AS t_id_token_signing_keys_id, t.insert_instant AS t_insert_instant, t.login_id_in_use_on_create_email_templates_id AS t_login_id_in_use_on_create_email_templates_id, t.login_id_in_use_on_update_email_templates_id AS t_login_id_in_use_on_update_email_templates_id, t.last_update_instant AS t_last_update_instant, t.login_new_device_email_templates_id AS t_login_new_device_email_templates_id, t.login_suspicious_email_templates_id AS t_login_suspicious_email_templates_id, t.multi_factor_email_message_templates_id AS t_multi_factor_email_message_templates_id, t.multi_factor_sms_message_templates_id AS t_multi_factor_sms_message_templates_id, t.multi_factor_sms_messengers_id AS t_multi_factor_sms_messengers_id, t.name AS t_name, t.parent_registration_email_templates_id AS t_parent_registration_email_templates_id, t.passwordless_email_templates_id AS t_passwordless_email_templates_id, t.password_reset_success_email_templates_id AS t_password_reset_success_email_templates_id, t.password_update_email_templates_id AS t_password_update_email_templates_id, t.scim_enterprise_user_request_converter_lambdas_id AS t_scim_enterprise_user_request_converter_lambdas_id, t.scim_enterprise_user_response_converter_lambdas_id AS t_scim_enterprise_user_response_converter_lambdas_id, t.scim_group_request_converter_lambdas_id AS t_scim_group_request_converter_lambdas_id, t.scim_group_response_converter_lambdas_id AS t_scim_group_response_converter_lambdas_id, t.scim_user_request_converter_lambdas_id AS t_scim_user_request_converter_lambdas_id, t.scim_user_response_converter_lambdas_id AS t_scim_user_response_converter_lambdas_id, t.set_password_email_templates_id AS t_set_password_email_templates_id, t.scim_client_entity_types_id AS t_scim_client_entity_types_id, t.scim_server_entity_types_id AS t_scim_server_entity_types_id, t.themes_id AS t_themes_id, t.two_factor_method_add_email_templates_id AS t_two_factor_method_add_email_templates_id, t.two_factor_method_remove_email_templates_id AS t_two_factor_method_remove_email_templates_id, t.ui_ip_access_control_lists_id AS t_ui_ip_access_control_lists_id, t.verification_email_templates_id AS t_verification_email_templates_id, ct.connectors_id AS ct_connectors_id, ct.data AS ct_data, ct.sequence AS ct_sequence, t.admin_user_forms_id AS t_admin_user_forms_id FROM tenants AS t LEFT OUTER JOIN connectors_tenants AS ct ON t.id = ct.tenants_id INNER JOIN ids ON ids.id = t.id ORDER BY t.name ASC
Also happening to me... Any workaround?
Also happening to me... Any workaround?
What version of MySQL you are using. I was using MySQL 5.7 and the error was there. when I manage to upgrade to 8.0 the error goes away. I still think it should not happen based on mySQL version. But it is what it is.
Also 5.7 (23).... Pretty bad if I have to upgrade a db version to fix !
Also 5.7 (23).... Pretty bad if I have to upgrade a db version to fix !
Says the documentation too :)