archergod
commented
4 months ago 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
broda02
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
identitiesto query it generate part query asINNER JOIN ids ON ids.id = a.id INNER JOIN tenants AS t ON t.id = a.tenants_idVersion
1.48.3
Affects Versions
No response
Edit: If I change
Inner JOIN ids ON ids.id=a.idtoINNER JOIN identities ids ON ids.id=a.idthen 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