I’ve found a critical problem: when you build a select query with a lot of fields (most of them supposing to be large data type), Lucee responds with the specific error: “The value [2147483648] is outside the range that can be represented as an int.”…
I tried changing a lot of bigint fields to int (most of them were “unsigned” because I’m using MariaDB), but the problem itself persists. I was changing a lot of values to 20% under limit of 2,147,483,647 expecting it solves the problem, but it still persists.
My last testing was removing a lot of fields or truncating the super select query to a lot of queries. Well, the truncation itself almost solved my situation. I could go on to my development.
I agree we need to do not overload our processes, but I used Adobe ColdFusion since MX7 and I’ve never had a problem like that! Sometimes I used Lucee, but I’ve never experienced a situation that I needed to avoid overload programming like that. I had some “Java heap space” problems that I needed to increase RAM memory to my VPS and it was solved. Now I’m using a VPS with large RAM memory and I need to face a problem like that…
Would you like to share your query and also your stack?
OS: ???
Java Version: ???
Tomcat Version: ???
Lucee Version: ???
1 Like
That’s the following query:
local.t_getSession=queryExecute(
sql="
select
tb_persons.pk_id as persons_pk_id
, case
when tb_persons.reg_blocked is null
then 0
else 1
end as persons_info_blocked
, case
when tb_persons.reg_removed is null
then 0
else 1
end as persons_info_removed
, tb_persons.info_fullName as persons_info_fullName
, tb_persons.info_nickName as persons_info_nickName
, tb_genders.pk_id as genders_pk_id
, tb_genders.info_icon as genders_info_icon
, tb_genders.info_caption as genders_info_caption
, tb_genders.info_label as genders_info_label
, case
when tb_genders.fit_female=1
and tb_genders.fit_male=0
then 'female'
when tb_genders.fit_female=0
and tb_genders.fit_male=1
then 'male'
else 'unisex'
end as genders_info_treatment
, vw_citizenship.pk_id as citizenship_pk_id
, vw_citizenship.info_name as citizenship_info_name
, vw_citizenship.info_label as citizenship_info_label
, vw_citizenship.info_code as citizenship_info_code
, vw_citizenship.ethnical_female as citizenship_ethnical_female
, vw_citizenship.ethnical_male as citizenship_ethnical_male
, vw_citizenship.ethnical_unisex as citizenship_ethnical_unisex
, tb_persons.birth_date as persons_birth_date
, vw_birth.pk_id as birth_pk_id
, vw_birth.info_name as birth_info_name
, vw_birth.info_label as birth_info_label
, vw_birth.info_code as birth_info_code
, vw_birth.ethnical_female as birth_ethnical_female
, vw_birth.ethnical_male as birth_ethnical_male
, vw_birth.ethnical_unisex as birth_ethnical_unisex
, tb_roles.pk_id as roles_pk_id
, tb_roles.info_caption as roles_info_caption
, tb_roles.label_female as roles_label_female
, tb_roles.label_male as roles_label_male
, tb_roles.label_unisex as roles_label_unisex
, case
when tb_persons.status_warned is null
then 0
else 1
end as persons_status_warned
, tb_persons.authentication_email as persons_authentication_email
, vw_countryPhone.pk_id as countryPhone_pk_id
, vw_countryPhone.info_name as countryPhone_info_name
, vw_countryPhone.info_label as countryPhone_info_label
, vw_countryPhone.info_code as countryPhone_info_code
, vw_countryPhone.info_phone as countryPhone_info_dial
, tb_persons.phone_areaCode as persons_phone_areaCode
, tb_persons.phone_number as persons_phone_number
, tb_persons.phone_extension as persons_phone_extension
, tb_persons.phone_mobile as persons_phone_mobile
, tb_persons.phone_office as persons_phone_office
, tb_persons.phone_dialing as persons_phone_dialing
, tb_persons.phone_messaging as persons_phone_messaging
, tb_persons.phone_whatsApp as persons_phone_whatsApp
, tb_persons.phone_telegram as persons_phone_telegram
, case
when tb_persons.phone_activated is null
then 0
else 1
end as persons_phone_activated
, tb_accesses.pk_id as accesses_pk_id
, case
when tb_accesses.reg_injured is null
then 0
else 1
end as accesses_info_injured
, case
when tb_accesses.reg_removed is null
then 0
else 1
end as accesses_info_removed
, tb_status_condition_positions.pk_id as status_condition_positions_pk_id
, tb_status_condition_positions.title_female as status_condition_positions_title_female
, tb_status_condition_positions.title_male as status_condition_positions_title_male
, tb_status_condition_positions.title_unisex as status_condition_positions_title_unisex
, tb_status_condition_durations.pk_id as status_condition_durations_pk_id
, tb_status_condition_durations.info_title as status_condition_durations_info_title
, tb_status_conditions.label_female as status_conditions_label_female
, tb_status_conditions.label_male as status_conditions_label_male
, tb_status_conditions.label_unisex as status_conditions_label_unisex
, tb_status_reasons.pk_id as status_reasons_pk_id
, tb_status_reasons.info_label as status_reasons_info_label
, tb_statuses.info_label as statuses_info_label
, tb_statuses.title_female as statuses_title_female
, tb_statuses.title_male as statuses_title_male
, tb_statuses.title_unisex as statuses_title_unisex
, tb_templates.pk_id as templates_pk_id
, concat(
'/_src/_cps/_tps/_'
, tb_templates.info_caption
, '.cfm'
) as templates_info_path
, tb_templates.info_label as templates_info_label
, tb_statuses.manifest_permanent as templates_info_permanent
from tb_persons
inner join tb_genders
on tb_persons.info_gender=tb_genders.pk_id
inner join tb_countries as vw_citizenship
on tb_persons.info_citizen=vw_citizenship.pk_id
inner join tb_countries as vw_birth
on tb_persons.birth_country=vw_birth.pk_id
inner join tb_roles
on tb_persons.authentication_role=tb_roles.pk_id
inner join tb_countries as vw_countryPhone
on tb_persons.phone_countryCode=vw_countryPhone.pk_id
inner join tb_accesses
on tb_persons.pk_id=tb_accesses.pk_persons
inner join tb_sessions
on tb_accesses.pk_sessions=tb_sessions.pk_id
inner join tb_statuses
on tb_accesses.login_position=tb_statuses.pk_positions
and tb_accesses.login_duration=tb_statuses.pk_durations
and tb_accesses.login_reason=tb_statuses.pk_reasons
inner join tb_status_conditions
on tb_statuses.pk_positions=tb_status_conditions.pk_positions
and tb_statuses.pk_durations=tb_status_conditions.pk_durations
inner join tb_status_condition_positions
on tb_status_conditions.pk_positions=tb_status_condition_positions.pk_id
inner join tb_status_condition_durations
on tb_status_conditions.pk_durations=tb_status_condition_durations.pk_id
inner join tb_status_reasons
on tb_statuses.pk_reasons=tb_status_reasons.pk_id
inner join tb_templates
on tb_statuses.manifest_template=tb_templates.pk_id
left outer join tb_keepMeConnected
on tb_accesses.pk_persons=tb_keepMeConnected.pk_persons
and tb_accesses.login_keepMeConnected=tb_keepMeConnected.pk_id
where tb_sessions.info_cookie=:v_sessions_info_cookie
;"
, params={
v_sessions_info_cookie:{
cfSQLtype:'cf_sql_char'
, value:cookie.cfid
}
}
, options={dataSource:'db_anything_mysql'}
);
OS: Windows Server 2022
Lucee Version: 6.0.1.83
IIS: 10.0.19041.1
Is that from Lucee or MariaDB?
Is there any indication whether it’s a specific column of the query?