SortOrder Bug or Issue

Description
I have encountered problem with creating a bulk SortOrder.
What We Want to Calculate: We have a numeric identifier called SortOrder that is determined by combining:
District Assignment - Each of the 25 districts of Seoul has a corresponding numeric value, for example:
Dobong-gu (도봉구) = 1.0
Gangnam-gu (강남구) = 7.0
Jung-gu (중구) = 14.0
Yongsan-gu (용산구) = 25.0 (And other districts similarly mapped from 1.0 to 25.0).
First Letter of the Restaurant Name - The alphabet is assigned a decimal value as follows:
a = 0.01, b = 0.02, c = 0.03, ..., z = 0.26.
Example Calculation:
For a restaurant called "Ryunique" located in Gangnam-gu, the SortOrder would be 7.18 (7.0 for Gangnam-gu + 0.18 for the letter 'R').
While attempting a bulk update for a SortOrder, the calculation for the first letter of the restaurant name was applied correctly. It was only the district value portion of the SortOrder that remained incorrect, defaulting to 0 instead of assigning the appropriate numeric value for each district. For example, it would result in 0.13 instead of the correct value. However, when we updated an individual restaurant, both the district and the first letter were processed correctly, such as resulting in 25.16.
This partial success suggests that the calculation issue might be specific to how district names are being processed in the bulk operation, whereas the restaurant name portion works as intended.
Steps We Have Already Tried:
Creating Procedures with Basic Output: We attempted to create a simple test procedure that just outputs 'Hello World'. This was unsuccessful, returning a blank table when checking if the procedure exists (SHOW PROCEDURE STATUS).
Environment and Permissions Verification:
We ensured that the active database was properly selected (USE mapmarkers;).
Verified permissions with SHOW GRANTS FOR CURRENT_USER;, confirming that root has all privileges, including CREATE ROUTINE.
Testing SQL Mode:
We adjusted SQL mode to eliminate potential restrictive settings (SET GLOBAL sql_mode = '';). This also did not resolve the issue.
Storage Engine Verification:
We confirmed the table storage engine is InnoDB, which should fully support stored procedures.
Test in a Different Database:
We created a new database (test_db) and attempted to create a procedure there, but again no procedure was created.
Checked for Plugins:
We reviewed plugins (SHOW PLUGINS;) to identify any that could block stored procedures but did not find anything unusual.
Server Variables for Routines:
We examined routine-related server variables (SHOW VARIABLES LIKE '%routine%';), but nothing seemed out of place.
What We Need Help With:
We need assistance identifying why procedures are not being created successfully in MariaDB 10.4.32 when using DBeaver. There are no explicit error messages, and every attempt results in a blank table when verifying procedure creation.
Specifically, we are seeking guidance on whether this could be related to a configuration within DBeaver, MariaDB, or a compatibility issue we might have overlooked.
Any insight into possible debugging techniques or log files we should examine to understand why the procedures are not being created would be immensely helpful.
We would greatly appreciate your help in diagnosing this issue. Our goal is to create a procedure that efficiently calculates the SortOrder based on the district name and the first letter of the restaurant name as described above.
Thank you very much for your time and support.
Best regards,
Reuben
DBeaver Version
Community Edition Version <IP_ADDRESS>409221725
Operating System
Windows 11
Database and driver
No response
Steps to reproduce
No response
Additional context
No response

Just another user here.
Two thing you can turn on in DBeaver is Window -> Show View -> Error log and Query manager.
In the Query manager you can click on the filter button and enable any extra query / object type.
I hope it helps you to debug the problem.

Hi TB-HunBoka,
Thanks for your suggestion about using the Error Log and Query Manager in
DBeaver.
From reviewing the error log, I found a record of my previous
troubleshooting efforts with ChatGPT from several weeks ago. Initially,
there were issues with the database not being selected properly, but these
were resolved at the time. Despite that, the SortOrder issue persisted.
ChatGPT has just analyzed the error log again and reviewed the queries
executed via the Query Manager, but nothing stands out as incorrect with
the assignments or updates to SortOrder. Unfortunately, the values are
still not behaving as expected, and the problem remains unresolved. This
matches what ChatGPT found during our earlier attempts—nothing appears
wrong, yet the issue continues. Reviewing the error log has not provided
any new insights.
If you have any additional ideas or know someone with more specialized
expertise in the community or DBeaver team, I would be very grateful for
further assistance.
Thanks again for your support!
Best regards,
Reuben
On Sat, 2 Nov 2024 at 11:21, TB-HunBoka @.***> wrote:

Just another user here.
Two thing you can turn on in DBeaver is Window -> Show View -> Error log
and Query manager.
In the Query manager you can click on the filter button and enable any
extra query / object type.
I hope it helps you to debug the problem.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2452749059,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WHBZGYKBL35U6HYEJDZ6QLKNAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSG42DSMBVHE
.
You are receiving this because you authored the thread.Message ID:
@.***>

Hello @ReubenJFraser ,
So, the main problem is the numeric calculation and not a DBeaver issue, right? Because I have no experience with creating procedures in MariaDB. If you need help in debugging that, I think an example code would be beneficial for the developers.
Without having a clear view on your data and the SortOrder code (if it's a procedure or a table code) and since you manually updated a record in the database, it started calculating correctly, I have a bad data feeling.
You have probably already done this, but often the problem can be traced back to a simple problem.
Could you please take an example record where the Ordering value is less than 1 and check the string record character by character compare? Check for potential white space or characters that looks similar but having different ascii code. This is a common problem, especially for the whitespace character as it has multiple ascii codes.
You can turn on white space visualization if it is the problem:

I have enabled white space. I am running the SortOrder queries again with
the help of ChatGPT, and will copy and paste what is relevant into this
email.
We'll start by recalculating SortOrder for each entry.
UPDATE restaurants
SET SortOrder = (
CASE
WHEN District = 'Dobong-gu (도봉구)' THEN 1.0
WHEN District = 'Dongdaemun-gu (동대문구)' THEN 2.0
WHEN District = 'Dongjak-gu (동작구)' THEN 3.0
WHEN District = 'Eunpyeong-gu (은평구)' THEN 4.0
WHEN District = 'Gangbuk-gu (강북구)' THEN 5.0
WHEN District = 'Gangdong-gu (강동구)' THEN 6.0
WHEN District = 'Gangnam-gu (강남구)' THEN 7.0
WHEN District = 'Gangseo-gu (강서구)' THEN 8.0
WHEN District = 'Geumcheon-gu (금천구)' THEN 9.0
WHEN District = 'Guro-gu (구로구)' THEN 10.0
WHEN District = 'Gwanak-gu (관악구)' THEN 11.0
WHEN District = 'Gwangjin-gu (광진구)' THEN 12.0
WHEN District = 'Jongno-gu (종로구)' THEN 13.0
WHEN District = 'Jung-gu (중구)' THEN 14.0
WHEN District = 'Jungnang-gu (중랑구)' THEN 15.0
WHEN District = 'Mapo-gu (마포구)' THEN 16.0
WHEN District = 'Nowon-gu (노원구)' THEN 17.0
WHEN District = 'Seocho-gu (서초구)' THEN 18.0
WHEN District = 'Seodaemun-gu (서대문구)' THEN 19.0
WHEN District = 'Seongbuk-gu (성북구)' THEN 20.0
WHEN District = 'Seongdong-gu (성동구)' THEN 21.0
WHEN District = 'Songpa-gu (송파구)' THEN 22.0
WHEN District = 'Yangcheon-gu (양천구)' THEN 23.0
WHEN District = 'Yeongdeungpo-gu (영등포구)' THEN 24.0
WHEN District = 'Yongsan-gu (용산구)' THEN 25.0
ELSE 0.0
END +
CASE
WHEN LEFT(RestaurantName_English, 1) = 'a' THEN 0.01
WHEN LEFT(RestaurantName_English, 1) = 'b' THEN 0.02
WHEN LEFT(RestaurantName_English, 1) = 'c' THEN 0.03
WHEN LEFT(RestaurantName_English, 1) = 'd' THEN 0.04
WHEN LEFT(RestaurantName_English, 1) = 'e' THEN 0.05
WHEN LEFT(RestaurantName_English, 1) = 'f' THEN 0.06
WHEN LEFT(RestaurantName_English, 1) = 'g' THEN 0.07
WHEN LEFT(RestaurantName_English, 1) = 'h' THEN 0.08
WHEN LEFT(RestaurantName_English, 1) = 'i' THEN 0.09
WHEN LEFT(RestaurantName_English, 1) = 'j' THEN 0.10
WHEN LEFT(RestaurantName_English, 1) = 'k' THEN 0.11
WHEN LEFT(RestaurantName_English, 1) = 'l' THEN 0.12
WHEN LEFT(RestaurantName_English, 1) = 'm' THEN 0.13
WHEN LEFT(RestaurantName_English, 1) = 'n' THEN 0.14
WHEN LEFT(RestaurantName_English, 1) = 'o' THEN 0.15
WHEN LEFT(RestaurantName_English, 1) = 'p' THEN 0.16
WHEN LEFT(RestaurantName_English, 1) = 'q' THEN 0.17
WHEN LEFT(RestaurantName_English, 1) = 'r' THEN 0.18
WHEN LEFT(RestaurantName_English, 1) = 's' THEN 0.19
WHEN LEFT(RestaurantName_English, 1) = 't' THEN 0.20
WHEN LEFT(RestaurantName_English, 1) = 'u' THEN 0.21
WHEN LEFT(RestaurantName_English, 1) = 'v' THEN 0.22
WHEN LEFT(RestaurantName_English, 1) = 'w' THEN 0.23
WHEN LEFT(RestaurantName_English, 1) = 'x' THEN 0.24
WHEN LEFT(RestaurantName_English, 1) = 'y' THEN 0.25
WHEN LEFT(RestaurantName_English, 1) = 'z' THEN 0.26
ELSE 0.00
END
);
Due to SQL Error [1054] [42S22]: (conn=18) Unknown column
'RestaurantName_English' in 'field list', this being because ChatGPT had
assumed it was that, when instead it is RestaurantName-English, ChatGPT
then directed:
Here's the revised version of your UPDATE query:
USE mapmarkers;
UPDATE restaurants
SET SortOrder = CONCAT(District, '.',
ASCII(UPPER(LEFT(RestaurantName-English, 1))) - 64);
Explanation of Changes:

Added the USE mapmarkers; statement to make sure the correct database
is selected.
Updated the column names to use backticks () to wrap
RestaurantName-English`
since it contains a hyphen, which SQL may misinterpret.

We then got this error message:
Incorrect decimal value: 'Gangnam-gu (강남구).11' for column
mapmarkers.restaurants.SortOrder at row 1
The error message was the same for each row from 1 to 64. ChatGPT replied:
You have assigned specific numeric values for each district and each letter
of the alphabet:

Districts: Each district is assigned a base numeric value (e.g.,
Gangnam-gu is 7.0, Jongno-gu is 13.0).
Alphabet: Each letter of the alphabet is assigned a smaller decimal
value (e.g., 'a' is 0.01, 'b' is 0.02, etc.)(Decimal_System_for_the_…).

You are combining these two values to calculate the "SortOrder" of
restaurants within each district by considering the district number and the
restaurant's first letter.
For example:

If a restaurant in Gangnam-gu has a name starting with 'a,' the
SortOrder might be something like 7.01.

To proceed, we need to make sure that:

Districts and alphabet values are correctly assigned during your
calculations.
Data Types in your SQL database for SortOrder can accommodate the
combined decimal structure (e.g., 7.01).

It remained as it was before:
We will now test and verify the update on an individual row to see if we
can replicate the success we observed before. Once we confirm that an
individual update works correctly, we can determine how to adjust our
approach for bulk updates.
I provided the below detail for one restaurant:
RestaurantID = 1
RestaurantName-English = KwonSookSoo
RestaurantName-Korean = 권숙수
District = Gangnam-gu (강남구)
Provide the query for that restaurant
UPDATE restaurants
SET SortOrder = CONCAT(
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 1
WHEN District = 'Jung-gu (중구)' THEN 2
WHEN District = 'Jongno-gu (종로구)' THEN 3
WHEN District = 'Seocho-gu (서초구)' THEN 4
WHEN District = 'Yongsan-gu (용산구)' THEN 5
WHEN District = 'Mapo-gu (마포구)' THEN 6
WHEN District = 'Seongdong-gu (성동구)' THEN 7
WHEN District = 'Yeongdeungpo-gu (영등포구)' THEN 8
WHEN District = 'Songpa-gu (송파구)' THEN 9
WHEN District = 'Seodaemun-gu (서대문구)' THEN 10
ELSE 0
END,
'.',
ASCII(SUBSTRING(RestaurantName-English, 1, 1)) - 64
)
WHERE RestaurantID = 1;
It didn't change, being still 0.11.
The value "0.11" corresponds to the first letter 'K' of the restaurant name
"KwonSookSoo" and is correct in the intended context.
The root issue lies with the district value, which should not be 0 if
it’s being assigned based on the district names like "Gangnam-gu"
Step-by-Step Debugging
To help with the debugging and ensure that the district is correctly
calculated, let’s do the following:

Manual Check for District Assignment:

Let’s first verify if the District field is being read correctly in
our query.

Use a simple SELECT statement to check what the District value is
for "RestaurantID = 1".

SELECT District FROM restaurants WHERE RestaurantID = 1;
I replied that the result of the query is that it simply shows the name of
the district, not the value assigned to that district, i.e. 7
for Gangnam-gu (강남구)
ChatGPT replied:
The fact that the query returns the district name without providing a
numeric value indicates that the District to Numeric Value mapping is not
happening as expected. Let's refine our approach to better identify why the
district value is not converting properly.
Step-by-Step Diagnosis and Fix:
We will debug and update the process to make sure the district mapping is
working as intended.
Step 1: Verify District Data
We need to check if the District value matches our conditions in the query.
It’s possible that special characters or extra spaces are preventing a
proper match.

Run the following query to get detailed information on the District
field, focusing on finding any inconsistencies:
I provided the result:
The length is 22. The HEX is 47616E676E616D2D67752028EAB095EB82A8EAB5AC29
Here's a breakdown:

47616E676E616D2D6775: This corresponds to the ASCII values for
"Gangnam-gu ".

47 = G
61 = a
6E = n
67 = g
6E = n
61 = a
6D = m
2D = - (hyphen)
67 = g
75 = u

20: Represents the space character after "gu".
28: Represents the ( (left parenthesis).
EAB095EB82A8EAB5AC: Represents the Korean characters for "강남구".
29: Represents the ) (right parenthesis).

Issues Identified

Special Characters and Whitespace:

The space character (20) and the parentheses (28 and 29) are
included in the string.
Korean characters have UTF-8 representation which is
significantly different from standard ASCII values.

How This Affects SortOrder
The inclusion of spaces, parentheses, and UTF-8 characters may have
been preventing the matching conditions in your UPDATE query from properly
identifying districts like "Gangnam-gu." The use of special characters
means that when you try to match District to the literal string in the
query, it may fail because the district name stored in the database is not
a simple ASCII string.
Proposed Solutions1. Clean Up the District Values in Queries
To ensure matching works correctly, let’s remove or normalize unnecessary
characters when processing the districts. Update your query to trim
whitespace and remove parentheses and Korean characters:

You can use regular expressions to simplify the District strings or
focus only on their English part.

UPDATE restaurants
SET SortOrder = CONCAT(
CASE
WHEN TRIM(REGEXP_REPLACE(District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) = 'Jung-gu'
THEN 6.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Jongno-gu' THEN 5.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seocho-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 9.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) = 'Mapo-gu'
THEN 10.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 11.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Yeongdeungpo-gu' THEN 12.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Songpa-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.*\)', '')) =
'Seodaemun-gu' THEN 14.0
ELSE 0
END,
'.',
RIGHT(
CAST(ASCII(SUBSTRING(RestaurantName-English, 1, 1)) - 64 AS CHAR),
2
)
)
WHERE RestaurantID = 1;
After doing that, the SortOrder for that restaurant remained unchanged as
being 0.11
ChatGPT provided another query that was targeted specifically at the
Gangnam district:
UPDATE restaurants
SET SortOrder = CONCAT(
CASE
WHEN TRIM(REGEXP_REPLACE(District, '\s*\(.*\)', '')) =
'Gangnam-gu' THEN 7.0
ELSE 0
END,
'.',
RIGHT(
CAST(ASCII(SUBSTRING(RestaurantName-English, 1, 1)) - 64 AS CHAR),
2
)
)
WHERE RestaurantID = 1;
But again there was no change, it remained 0.11.
This outcome mirrors what happened previously. It's extremely challenging
to keep trying different methods when there is no visible result or change,
leading me to feel it may be best to stop for now, as I did before.
However, being able to order restaurants by district alphabetically—and
apply similar sorting to hotels, tourist attractions, and other
categories—is crucial for the functionality of my project. If this issue
cannot be resolved, I may have to consider switching to another database
tool besides DBeaver.
Thank you again for your assistance.
Best regards,
Reuben
On Mon, 4 Nov 2024 at 23:23, TB-HunBoka @.***> wrote:

Hello @ReubenJFraser https://github.com/ReubenJFraser ,
So, the main problem is the numeric calculation and not a DBeaver issue,
right? Because I have no experience with creating procedures in MariaDB. If
you need help in debugging that, I think an example code would be
beneficial for the developers.
Without having a clear view on your data and the SortOrder code (if it's a
procedure or a table code) and since you manually updated a record in the
database, it started calculating correctly, I have a bad data feeling.
You have probably already done this, but often the problem can be traced
back to a simple problem.
Could you please take an example record where the Ordering value is less
than 1 and check the string record character by character compare? Check
for potential white space or characters that looks similar but having
different ascii code. This is a common problem, especially for the
whitespace character as it has multiple ascii codes.
You can turn on white space visualization if it is the problem:
image.png (view on web)
https://github.com/user-attachments/assets/813af8e8-c7af-4183-a23b-7003ea1a95a5
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2454578189,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WA3HEXHXQ55AIUYWO3Z65RKJAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJUGU3TQMJYHE
.
You are receiving this because you were mentioned.Message ID:
@.***>

As you are running the update command on the server, I think this issue is not DBeaver related, but ShadelessFox will tell.
If nothing, then I will install MariaDB on my personal pc will try to help you. However, I still think it's a data issue.
Can you try these?
This should return the restaurant:
SELECT District FROM restaurants WHERE RestaurantID = 1 and District = 'Gangnam-gu (강남구)';
This should return the District name and the score 7.
select District,
	CASE
		WHEN District = 'Gangnam-gu (강남구)' THEN 7
    	ELSE 0
    END score
    from restaurants
    WHERE RestaurantID = 1

If this resulted in 0, you could try to select district from restaurants where RestaurantID = 1, and directly copy the value from the query result, then replace the "Gangnam-gu (강남구)" in the CASE statement with the value you copied. If this changes the score, then there is an invisible or special character in the record.
If the previous query didn't result 7 then try this:
select District,
	CASE
		WHEN Upper(District) like '%GANGNAM-GU%' THEN 7
		WHEN Upper(District) like '%GANGNAM%' THEN 6
    	ELSE 0
    END score
    from restaurants
    WHERE RestaurantID = 1

Let's see if we catch the problem.

Hi TB-HunBoka, 🤗
Thank you so much for your willingness to help me further, especially
beyond what I would have ever expected from a community forum.
I'll try out the queries you provided and see if we can uncover any subtle
data issues that might have slipped past my previous troubleshooting
attempts. If there is any special character or data inconsistency that’s
blocking the correct district assignment, I'll make sure to update you. I
deeply appreciate your offer to even install MariaDB to investigate
further—that kind of dedication is rare, and I'm really grateful.
I'll get back to you soon with the results. Thanks again for your patience
and for going above and beyond to help.
Best regards,
Reuben
On Tue, 5 Nov 2024 at 07:55, TB-HunBoka @.***> wrote:

As you are running the update command on the server, I think this issue is
not DBeaver related, but ShadelessFox will tell.
If nothing, then I will install MariaDB on my personal pc will try to help
you. However, I still think it's a data issue.
Can you try these?
This should return the restaurant:
SELECT District FROM restaurants WHERE RestaurantID = 1 and District =
'Gangnam-gu (강남구)';
This should return the District name and the score 7.
select District,
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
If this resulted in 0, you could try to select district from restaurants
where RestaurantID = 1, and directly copy the value from the query result,
then replace the "Gangnam-gu (강남구)" in the CASE statement with the value
you copied. If this changes the score, then there is an invisible or
special character in the record.
If the previous query didn't result 7 then try this:
select District,
CASE
WHEN Upper(District) like '%GANGNAM-GU%' THEN 7
WHEN Upper(District) like '%GANGNAM%' THEN 6
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
Let's see if we catch the problem.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2455682614,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WDGHT4QPVJLUDGWJMDZ67NKRAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJVGY4DENRRGQ
.
You are receiving this because you were mentioned.Message ID:
@.***>

Below is my step-by-step plan which I don't expect you to read, but I am
explaining what I intend to do which you might find helpful, if even only
to skim read.Step 1: Validation of District Data Consistency

Goal: Ensure the District field contains consistent and properly
formatted data.

Actions:
1.
Run TB-HunBoka's Initial Queries:

Execute the following SQL query:
sql
Copy code
SELECT District FROM restaurants WHERE RestaurantID = 1 AND
District = 'Gangnam-gu (강남구)';

This query is to verify whether the exact value Gangnam-gu (강남구)
exists in the database for RestaurantID = 1. If no rows are
returned, there might be extra spaces or special characters.
Follow up with:

sql
Copy code
SELECT District, CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7
ELSE 0END AS scoreFROM restaurantsWHERE RestaurantID = 1;

This query should return score = 7 for RestaurantID = 1. If the
score is 0, this indicates an issue with the District value.

Check for Invisible/Special Characters:

If no rows or an incorrect score is returned, run:
sql
Copy code
SELECT HEX(District) FROM restaurants WHERE RestaurantID = 1;

Compare the returned HEX code against the expected value
(already documented in your prior testing). Look for any
differences that
could indicate whitespace or special characters.

Manual Replacement:

If inconsistencies are found:
- Copy the exact District value from the query result.
- Update your queries and CASE statements to directly use the
copied value.
- Execute a similar query again to check if the score changes.

Step 2: Refine the Sorting Query

Goal: Ensure the sorting values are assigned consistently for
SortOrder.
Actions:

Rewrite Sorting Query with Normalization:

Normalize the District values in your update query using
trimming and removing parentheses/Korean characters:

sql
Copy code
UPDATE restaurantsSET SortOrder = CONCAT(
CASE
WHEN TRIM(REGEXP_REPLACE(District, '\s*\(.\)',
'')) = 'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.*\)',
'')) = 'Jung-gu' THEN 6.0
-- Continue with other districts
ELSE 0
END,
'.',
ASCII(SUBSTRING(RestaurantName-English, 1, 1)) - 64
)WHERE RestaurantID = 1;

Run this query on a single record to verify whether the SortOrder
value updates correctly.

Step 3: White Space & Character Visualization

Goal: Identify potential whitespace issues, especially with district
names.

Actions:
1.
Enable White Space Visualization in DBeaver:

Utilize DBeaver's visualizations to display any extra spaces in
District values. This will help to quickly identify if there are
leading, trailing, or double spaces.

Query to Highlight Anomalies:

Run a query to list District values that contain leading or
trailing spaces:
sql
Copy code
SELECT RestaurantID, DistrictFROM restaurantsWHERE District
LIKE ' %' OR District LIKE '% ';

Correct any values that contain unexpected spaces.

Step 4: Use Like Operator for Flexible Matching

Goal: Ensure matching even with slight data inconsistencies.
Actions:

Execute TB-HunBoka's Suggested Like Queries:

Run the following query:

sql
Copy code
SELECT District, CASE
WHEN UPPER(District) LIKE '%GANGNAM-GU%' THEN 7
WHEN UPPER(District) LIKE '%GANGNAM%' THEN 6
ELSE 0END AS scoreFROM restaurantsWHERE RestaurantID = 1;

If this query returns the correct score, it indicates there may
have been case inconsistencies or other characters
preventing a direct
match.

Step 5: Iterative Testing with Sample Updates

Goal: Determine if the corrected values properly update for
different records.

Actions:
1.
Single Record Testing:

Manually update SortOrder for one record using the corrected
approach.

Verify the output is correct before proceeding.

Expand Testing to Bulk Records:

If successful, gradually expand to update a small batch of records
(e.g., WHERE RestaurantID BETWEEN 1 AND 10).

Monitor the SortOrder output to ensure it works consistently
across different records.

Step 6: Communication and Feedback

Goal: Keep TB-HunBoka updated and request further guidance if needed.
Actions:

Document Results:

After running each of the queries, document the results—whether
expected or unexpected.
Include screenshots of any discrepancies found in whitespace
visualization.

Reply with Findings:

Reply to TB-HunBoka with the outcome of each step, particularly
if the manual replacement resolves the issue or if the LIKE operator
provides a successful result.
Request further advice if you still encounter issues,
particularly related to character encoding or other data
inconsistencies.

Summary:
This plan combines TB-HunBoka's valuable insights on potential character
inconsistencies with your own structured testing of SortOrder calculations.
By focusing first on data validation (particularly invisible characters and
whitespace), refining your update queries, and testing iteratively, you'll
be able to identify exactly where the SortOrder calculation is failing.
This step-by-step, evidence-based approach will hopefully uncover the
subtle data inconsistencies preventing successful ordering, while also
ensuring you have a detailed log of each attempt for further debugging. Let
me know if this approach makes sense or if you’d like any part of it
adjusted.
On Tue, 5 Nov 2024 at 15:07, Reuben Fraser @.***>
wrote:

Hi TB-HunBoka, 🤗
Thank you so much for your willingness to help me further, especially
beyond what I would have ever expected from a community forum.
I'll try out the queries you provided and see if we can uncover any subtle
data issues that might have slipped past my previous troubleshooting
attempts. If there is any special character or data inconsistency that’s
blocking the correct district assignment, I'll make sure to update you. I
deeply appreciate your offer to even install MariaDB to investigate
further—that kind of dedication is rare, and I'm really grateful.
I'll get back to you soon with the results. Thanks again for your patience
and for going above and beyond to help.
Best regards,
Reuben
On Tue, 5 Nov 2024 at 07:55, TB-HunBoka @.***> wrote:

As you are running the update command on the server, I think this issue
is not DBeaver related, but ShadelessFox will tell.
If nothing, then I will install MariaDB on my personal pc will try to
help you. However, I still think it's a data issue.
Can you try these?
This should return the restaurant:
SELECT District FROM restaurants WHERE RestaurantID = 1 and District =
'Gangnam-gu (강남구)';
This should return the District name and the score 7.
select District,
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
If this resulted in 0, you could try to select district from restaurants
where RestaurantID = 1, and directly copy the value from the query result,
then replace the "Gangnam-gu (강남구)" in the CASE statement with the value
you copied. If this changes the score, then there is an invisible or
special character in the record.
If the previous query didn't result 7 then try this:
select District,
CASE
WHEN Upper(District) like '%GANGNAM-GU%' THEN 7
WHEN Upper(District) like '%GANGNAM%' THEN 6
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
Let's see if we catch the problem.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2455682614,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WDGHT4QPVJLUDGWJMDZ67NKRAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJVGY4DENRRGQ
.
You are receiving this because you were mentioned.Message ID:
@.***>

Dear TB-HunBoka,
Thanks to your helpful suggestions, I was able to successfully calculate
the correct SortOrder values for all 155 rows in the restaurants table.
This is a major milestone as it ensures consistent and accurate sorting of
all entries by both district and map marker name, something crucial for the
functionality of my project.
However, our next step—automating this calculation via a trigger—has proven
significantly more challenging. Despite multiple iterations of trigger
scripts, both simple and complex, each attempt to create a functional
trigger in MariaDB has resulted in the same syntax error near the END
statement. We have tried various approaches, including using different
delimiters, simplifying the trigger logic to isolate the problem, and even
attempting to create the trigger through DBeaver's built-in GUI.
Unfortunately, none of these methods have yet resulted in a successful
trigger creation.
I'd greatly appreciate any further insights or suggestions you might have
on automating the SortOrder calculation. Thank you again for your continued
assistance and patience.
Best regards, Reuben
P.S. Below is a complete description of my process
Step 1: Run TB-HunBoka's Initial Queries:

Execute the following SQL query:

sql
Copy code
SELECT District FROM restaurants WHERE RestaurantID = 1 AND
District = 'Gangnam-gu (강남구)';
[image: 2024-11-05-1.png]
Since the query returns the row successfully, this means that the District
value is being matched correctly and there are no invisible or special
character inconsistencies preventing the match.
*Step 2: *Update SortOrder for a Single Record
We will update the SortOrder value for a single restaurant, RestaurantID = 1 as
an example to confirm that it’s correctly assigning values.
UPDATE restaurants
SET SortOrder = CONCAT(
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7.0
WHEN District = 'Jung-gu (중구)' THEN 14.0
-- Continue with other districts if needed
ELSE 0.0
END,
'.',
ASCII(UPPER(SUBSTRING(RestaurantName-English, 1, 1))) - 64
)
WHERE RestaurantID = 1;
[image: 2024-11-05-2.png]
That the UPDATE query was successfully executed for one row means that
the command ran without any SQL syntax issues and updated the SortOrder
value for RestaurantID = 1.Step 3: Verify the Updated SortOrder Value
We need to confirm whether the SortOrder was calculated and updated
correctly for this specific record.
Run the following query to check:
SELECT RestaurantID, District, RestaurantName-English, SortOrder
FROM restaurants
WHERE RestaurantID = 1;

Goal: Ensure that the SortOrder value has been updated to something
like 7.11 (if the RestaurantName-English starts with K). The format
should combine the district value and the first letter value.

Step 4: Analyze the Result

Expected Value: You should see the new value of SortOrder reflecting
the concatenation of the district numeric code and the first letter value.

For example:
- Gangnam-gu gives a base value of 7.0.
- The letter K should give (ASCII('K') - 64), resulting in 11.
- So, the SortOrder value should look something like 7.11

[image: 2024-11-05-3.png]
1 Gangnam-gu (강남구) KwonSookSoo 7.00 Value 7.00
The SortOrder value was updated to 7.00 instead of the expected 7.11.
Gangnam-gu (강남구) = 7.0
k = 0.11
Reasons for the Incorrect Update
The value 7.00 means that:

The district value assignment (Gangnam-gu (강남구) = 7.0) worked
perfectly.
The calculation intended to add the value based on the first letter of
RestaurantName-English did not get applied.

There are a few possible reasons why this happened:

Data Type Issue: The column SortOrder might be defined as a numeric
type, which doesn't handle the concatenation or addition as intended.
Incorrect Calculation Logic: The operation (ASCII(...) - 64) * 0.01
may not be adding properly if the resulting value is interpreted
incorrectly.

Steps to Fix the Issue**Step 5: Modify SortOrder to Properly Handle
Decimals
The problem might be that the addition of the district value and
alphabetical value is not properly recognized due to a data type or
calculation issue. Let's make sure everything works smoothly by following
these steps:
Step 6: Update Using Numeric Calculation
Instead of concatenating, let’s ensure we add the district value and
alphabetical value together as numeric values:

The updated query will add a small fractional value representing the
alphabetical position.

Here’s a refined version: UPDATE restaurants
SET SortOrder = (
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7.0
WHEN District = 'Jung-gu (중구)' THEN 14.0
-- Add other district cases here as needed
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(RestaurantName-English, 1, 1))) - 64) * 0.01
)
WHERE RestaurantID = 1;
[image: 2024-11-05-4.png]
Let's now check the result to confirm if the SortOrder value was updated
correctly with the additional alphabetical fraction.
Step 7: Verify the Updated SortOrder Value
Please run the following query to check what the SortOrder value looks like
after the update:
[image: 2024-11-05-5.png]
This time it returned the correct SortOrder 7.11.
That's fantastic news! 🎉
It looks like the changes have worked exactly as intended, and the SortOrder
now reflects both the district (7.0 for Gangnam-gu) and the alphabetical
contribution (0.11 for the letter K). This means the approach is working
perfectly!
Step 8: Update All Records in the restaurants Table
Now that we've confirmed the correct update for RestaurantID = 1, you can
proceed to apply this update to all records in the table:
UPDATE restaurants
SET SortOrder = (
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7.0
WHEN District = 'Jung-gu (중구)' THEN 14.0
WHEN District = 'Jongno-gu (종로구)' THEN 13.0
WHEN District = 'Seocho-gu (서초구)' THEN 18.0
WHEN District = 'Yongsan-gu (용산구)' THEN 25.0
-- Continue adding all districts as needed
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(RestaurantName-English, 1, 1))) - 64) * 0.01
);
[image: 2024-11-05-6.png]
Excellent! 🎉 You've successfully updated all rows in the restaurants
table, which is a major achievement.
Now, let's proceed with the final verification and ensure everything looks
consistent.
Step 9: Verify All Updated Records
Run the following query to check the updated SortOrder values across all
records in the restaurants table:
SELECT RestaurantID, District, RestaurantName-English, SortOrder
FROM restaurants
ORDER BY SortOrder;
[image: 2024-11-05-7a.png]
[image: 2024-11-05-7b.png]
[image: 2024-11-05-7c.png]
After running the full UPDATE query for the SortOrder field, I've observed
the following pattern:
Districts Where SortOrder Is Calculated Correctly:
The SortOrder was successfully calculated for all restaurants in the
following districts:

Gangnam-gu (강남구)
Jongno-gu (종로구)
Jung-gu (중구)
Seocho-gu (서초구)
Yongsan-gu (용산구)

For these districts, all restaurants have the correct base numeric value,
plus the fractional part based on the alphabetical ordering of the
restaurant names. Everything worked as expected.
Districts Where SortOrder Is Not Calculated Correctly:
Unfortunately, the SortOrder did not calculate correctly for any of the
restaurants in the following districts:

Dongjak-gu (동작구)
Eunpyeong-gu (은평구)
Gangseo-gu (강서구)
Mapo-gu (마포구)
Seodaemun-gu (서대문구)
Seongbuk-gu (성북구)
Seongdong-gu (성동구)
Songpa-gu (송파구)
Yeongdeungpo-gu (영등포구)

For these districts, the SortOrder was left as 0.00 across all
restaurants, indicating that the district value wasn't correctly assigned
during the update. The alphabetical value, which should appear as the
fractional part, was also not added.
Key Observations:

No Overlap in Calculation: If a district's restaurants were
calculated correctly, then all restaurants in that district are correct.
Conversely, if one restaurant was not calculated correctly in a district,
none of them were.
Starting Value Issue: The SortOrder starts with 0 for all
restaurants in the districts where the calculation failed, suggesting that
the CASE statement assigning the district numeric value did not match
the district name as expected.

Step 10: Identify the Inconsistencies in District Values
The fact that some districts are updating while others are not likely means
there's an issue with how the district names are being stored or compared.
There are several possible reasons for this, including invisible
characters, extra spaces, or subtle differences in encoding.
Here are the steps to identify these issues:
Run HEX Query for All Problematic Districts:
SELECT DISTINCT District, HEX(District) AS DistrictHex
FROM restaurants
WHERE District IN (
'Dongjak-gu (동작구)',
'Eunpyeong-gu (은평구)',
'Gangseo-gu (강서구)',
'Mapo-gu (마포구)',
'Seodaemun-gu (서대문구)',
'Seongbuk-gu (성북구)',
'Seongdong-gu (성동구)',
'Songpa-gu (송파구)',
'Yeongdeungpo-gu (영등포구)'
);
[image: 2024-11-05-8.png]
Analysis of HEX Values

Comparison to Expected Values: Compare these HEX values against a
district that was successfully calculated (e.g., Gangnam-gu (강남구)).

Hexadecimal Representation Patterns:

The format of the HEX values seems consistent in terms of structure:
- English Name (e.g., Mapo, Yeongdeungpo) followed by -6775
(which corresponds to -gu).
- Korean Characters inside parentheses.
2.

Potential Issues to Address:

Extra Spaces or Formatting Differences:
- The 28 at the end of the English name (which is () indicates the
start of the Korean name. We need to make sure there aren't any extra
characters or inconsistent formatting around this boundary.

Special Characters and Unicode Differences:

The Korean characters themselves are represented in multiple
bytes, which is correct, but it could potentially lead to
inconsistency if
not properly handled.

Step-by-Step Approach to Resolve the Issue**Step 11: Update Query to Match
the HEX Values
The best approach here is to normalize the district names in a way that
removes all inconsistencies. The following changes should help address any
discrepancies related to extra characters or formatting.
*Modify Update Query to Use TRIM() and *REGEXP_REPLACE()
This step will remove any trailing spaces and normalize the district names
before comparison:
UPDATE restaurants
SET SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) = 'Jung-gu'
THEN 14.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) = 'Mapo-gu'
THEN 16.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(RestaurantName-English, 1, 1))) - 64) * 0.01
);
[image: 2024-11-05-9.png]
That the update affected all 155 rows suggests that all records were
successfully processed. Now, let’s verify whether this has led to the
intended result, especially for the districts that previously weren’t
updating properly.
Step 12: Verify Updated SortOrder Values
To confirm if the districts that were previously problematic have now been
correctly updated, please run the following query:
SELECT RestaurantID, District, RestaurantName-English, SortOrder
FROM restaurants
ORDER BY SortOrder;
[image: 2024-11-05-9b.png]
s has led to the intended result, especially for the districts that
previously weren’t updating properly.
Step 13: Automate Updates for New Entries:
Here’s a possible trigger example for automatic SortOrder assignment:
DELIMITER //
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Jung-gu' THEN 14.0
-- Add other districts as needed
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END//
DELIMITER ;

Updated Rows -1
Query DELIMITER ;
This suggests that the DELIMITER command wasn't successfully executed in
DBeaver. The DELIMITER keyword is often used in MySQL to define and run
multiple SQL commands together, especially for triggers or stored
procedures. However, DBeaver may not require (or recognize) the DELIMITER
keyword in the same way as the MySQL command line.
Explanation of the Issue

DBeaver generally doesn’t use the DELIMITER command to change how
statements are recognized. The DELIMITER command is used more frequently
in MySQL's command-line client when working with procedures, functions, or
triggers that include multiple statements.
In DBeaver, you can execute complex scripts involving triggers or
stored procedures without using the DELIMITER keyword, since DBeaver's
editor automatically parses different statements correctly.

Next Steps to Correctly Create the Trigger in DBeaver
Here’s how to adjust the process so that the trigger can be successfully
created:
Step 14: Remove DELIMITER Commands
Remove the DELIMITER lines and let DBeaver interpret the entire trigger
definition as a block. Here’s the adjusted trigger:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1
Solution: Correcting the Trigger Syntax for MariaDB/DBeaver
The END keyword needs to be handled properly, and we may need to add
semicolons after individual statements to ensure they’re correctly
terminated. Here are some modifications you can make to the trigger to make
sure it runs successfully in DBeaver with MariaDB:

Use a Semicolon (;) After Each Statement Inside BEGIN ... END:

When creating triggers, each statement within the BEGIN ... END block
needs to be terminated with a semicolon.

However, since DBeaver interprets semicolons as the end of a
complete query, you should change the delimiter just for the creation of
the trigger.

Modify the Trigger Without Explicit DELIMITER Commands: Since DELIMITER
isn’t directly supported in DBeaver, instead you can use the //
delimiter to redefine the terminator at the time of creation.
Here’s an updated version of the trigger definition that should work:
DELIMITER //
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END//

DELIMITER ;
Updated Rows -1
Query DELIMITER ;
It seems like the issue with using DELIMITER is persisting because DBeaver
doesn’t recognize DELIMITER commands the same way the MySQL command-line
client does.
Revised Trigger Script Without DELIMITER
Instead of using DELIMITER, you can simply create the trigger in a
straightforward way. The trick is to include semicolons properly and
execute the entire block at once.
Please use the following script:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;
SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1
Corrected Trigger Script

The corrected version includes proper semicolons after each individual
statement and removes any potential ambiguity for the END statement.
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1
The repeated issue with the END statement suggests that there is a
persistent syntax problem, which could be tied to missing punctuation or
specific requirements of your MariaDB environment. Here are several further
refined suggestions and adjustments that could help us successfully create
the trigger.
Possible Cause and Solution for END Error
The END keyword needs proper punctuation or changes to the statement’s
formatting to be recognized correctly. Let’s attempt some modifications
that should resolve this, focusing on the most common causes of such issues.

Ensure Proper Semicolon Usage and Placement

Each statement inside the BEGIN ... END block must be terminated by a
semicolon.

The END keyword itself should have a terminating delimiter, which
might sometimes be missing or misinterpreted by MariaDB.

Add the DELIMITER Command Within the Same Script Block

Since DBeaver may not recognize the DELIMITER command like MySQL,
we’ll use it inline in the script without expecting it to separate
statements.

New Suggested Trigger Code (Without DELIMITER)
Let’s try this more streamlined version, focusing on simplicity:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END //
Simplified Approach to the Trigger

Here is a simplified version of the trigger without any extra delimiters or
non-standard syntax. Let’s make sure that:

Every line inside BEGIN ... END ends with a semicolon (;).
The END statement itself has a semicolon.
There’s no additional delimiter (// or $$).

Simplified Trigger Code
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1
Step 15: Test Step by Step
As the above version still does not work, we will try to Test a Super
Simple Trigger
Let's start with an extremely simple trigger, without any of the complex
logic, just to confirm that a basic trigger works in your environment:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = 1.0;
END;
SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1
The persistence of the END syntax error in your MariaDB environment
suggests there might be a more fundamental issue with how the SQL is being
processed by DBeaver or how MariaDB is expecting the syntax. Let's take a
very careful look at how we can adjust this to succeed.
I'll address several potential root causes:

The Trigger's Semicolon Placement: We need to be precise about
where we place the semicolons within the BEGIN ... END block.
DBeaver's Handling of SQL Scripts: DBeaver might have its own
nuances for trigger creation that differ from the MySQL/MariaDB command
line.
Syntax Errors Tied to DELIMITER Usage: Since we can’t use the
typical DELIMITER command, we must ensure that each SQL segment is
properly terminated and not confusing the parser.

Solution: Key Changes and a Careful Reattempt**Simplified Version of the
Trigger
Let’s use the following simplified version of the trigger. This time, I
will make the structure extremely explicit to ensure everything is
terminated correctly:
DELIMITER $$
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = 1.0;
END$$
DELIMITER ;
Updated Rows -1
Query DELIMITER ;
It seems that the attempt to use DELIMITER commands is still not working in
DBeaver, and it continues to indicate that the issue is with how DBeaver or
MariaDB is parsing the END statement. Since the delimiter command is not
being recognized, we should proceed with an entirely different approach
that DBeaver can better handle.
Final Strategy to Create the Trigger in DBeaver
DBeaver tends to handle standard SQL statements and the definition of
routines differently from the command-line environment. Let's proceed with
the following approach to ensure we avoid these syntax issues:
1. Create a Very Simple Trigger Without Changing Delimiters
We'll strip the logic down to its simplest form, then gradually add
complexity once the basic form works.
Here’s the minimal version of the trigger:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = 1;
END;
On Tue, 5 Nov 2024 at 15:20, Reuben Fraser @.***>
wrote:

Below is my step-by-step plan which I don't expect you to read, but I am
explaining what I intend to do which you might find helpful, if even only
to skim read.Step 1: Validation of District Data Consistency

Goal: Ensure the District field contains consistent and properly
formatted data.

Actions:
1.
Run TB-HunBoka's Initial Queries:

Execute the following SQL query:
sql
Copy code
SELECT District FROM restaurants WHERE RestaurantID = 1 AND District = 'Gangnam-gu (강남구)';

This query is to verify whether the exact value Gangnam-gu
(강남구) exists in the database for RestaurantID = 1. If no rows
are returned, there might be extra spaces or special characters.
Follow up with:

sql
Copy code
SELECT District, CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7
ELSE 0END AS scoreFROM restaurantsWHERE RestaurantID = 1;

This query should return score = 7 for RestaurantID = 1. If
the score is 0, this indicates an issue with the District
value.

Check for Invisible/Special Characters:

If no rows or an incorrect score is returned, run:
sql
Copy code
SELECT HEX(District) FROM restaurants WHERE RestaurantID = 1;

Compare the returned HEX code against the expected value
(already documented in your prior testing). Look for any differences that
could indicate whitespace or special characters.

Manual Replacement:

If inconsistencies are found:
- Copy the exact District value from the query result.
- Update your queries and CASE statements to directly use the
copied value.
- Execute a similar query again to check if the score changes.

Step 2: Refine the Sorting Query

Goal: Ensure the sorting values are assigned consistently for
SortOrder.
Actions:

Rewrite Sorting Query with Normalization:

Normalize the District values in your update query using
trimming and removing parentheses/Korean characters:

sql
Copy code
UPDATE restaurantsSET SortOrder = CONCAT(
CASE
WHEN TRIM(REGEXP_REPLACE(District, '\s*\(.\)', '')) = 'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(District, '\s\(.*\)', '')) = 'Jung-gu' THEN 6.0
-- Continue with other districts
ELSE 0
END,
'.',
ASCII(SUBSTRING(RestaurantName-English, 1, 1)) - 64
)WHERE RestaurantID = 1;

Run this query on a single record to verify whether the
SortOrder value updates correctly.

Step 3: White Space & Character Visualization

Goal: Identify potential whitespace issues, especially with
district names.

Actions:
1.
Enable White Space Visualization in DBeaver:

Utilize DBeaver's visualizations to display any extra spaces in
District values. This will help to quickly identify if there are
leading, trailing, or double spaces.

Query to Highlight Anomalies:

Run a query to list District values that contain leading or
trailing spaces:
sql
Copy code
SELECT RestaurantID, DistrictFROM restaurantsWHERE District LIKE ' %' OR District LIKE '% ';

Correct any values that contain unexpected spaces.

Step 4: Use Like Operator for Flexible Matching

Goal: Ensure matching even with slight data inconsistencies.
Actions:

Execute TB-HunBoka's Suggested Like Queries:

Run the following query:

sql
Copy code
SELECT District, CASE
WHEN UPPER(District) LIKE '%GANGNAM-GU%' THEN 7
WHEN UPPER(District) LIKE '%GANGNAM%' THEN 6
ELSE 0END AS scoreFROM restaurantsWHERE RestaurantID = 1;

If this query returns the correct score, it indicates there
may have been case inconsistencies or other characters preventing a direct
match.

Step 5: Iterative Testing with Sample Updates

Goal: Determine if the corrected values properly update for
different records.

Actions:
1.
Single Record Testing:

Manually update SortOrder for one record using the corrected
approach.

Verify the output is correct before proceeding.

Expand Testing to Bulk Records:

If successful, gradually expand to update a small batch of
records (e.g., WHERE RestaurantID BETWEEN 1 AND 10).

Monitor the SortOrder output to ensure it works consistently
across different records.

Step 6: Communication and Feedback

Goal: Keep TB-HunBoka updated and request further guidance if
needed.
Actions:

Document Results:

After running each of the queries, document the
results—whether expected or unexpected.
Include screenshots of any discrepancies found in whitespace
visualization.

Reply with Findings:

Reply to TB-HunBoka with the outcome of each step,
particularly if the manual replacement resolves the issue or if the LIKE
operator provides a successful result.
Request further advice if you still encounter issues,
particularly related to character encoding or other data inconsistencies.

Summary:
This plan combines TB-HunBoka's valuable insights on potential character
inconsistencies with your own structured testing of SortOrder
calculations. By focusing first on data validation (particularly invisible
characters and whitespace), refining your update queries, and testing
iteratively, you'll be able to identify exactly where the SortOrder
calculation is failing.
This step-by-step, evidence-based approach will hopefully uncover the
subtle data inconsistencies preventing successful ordering, while also
ensuring you have a detailed log of each attempt for further debugging. Let
me know if this approach makes sense or if you’d like any part of it
adjusted.
On Tue, 5 Nov 2024 at 15:07, Reuben Fraser @.***>
wrote:

Hi TB-HunBoka, 🤗
Thank you so much for your willingness to help me further, especially
beyond what I would have ever expected from a community forum.
I'll try out the queries you provided and see if we can uncover any
subtle data issues that might have slipped past my previous troubleshooting
attempts. If there is any special character or data inconsistency that’s
blocking the correct district assignment, I'll make sure to update you. I
deeply appreciate your offer to even install MariaDB to investigate
further—that kind of dedication is rare, and I'm really grateful.
I'll get back to you soon with the results. Thanks again for your
patience and for going above and beyond to help.
Best regards,
Reuben
On Tue, 5 Nov 2024 at 07:55, TB-HunBoka @.***> wrote:

As you are running the update command on the server, I think this issue
is not DBeaver related, but ShadelessFox will tell.
If nothing, then I will install MariaDB on my personal pc will try to
help you. However, I still think it's a data issue.
Can you try these?
This should return the restaurant:
SELECT District FROM restaurants WHERE RestaurantID = 1 and District =
'Gangnam-gu (강남구)';
This should return the District name and the score 7.
select District,
CASE
WHEN District = 'Gangnam-gu (강남구)' THEN 7
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
If this resulted in 0, you could try to select district from restaurants
where RestaurantID = 1, and directly copy the value from the query result,
then replace the "Gangnam-gu (강남구)" in the CASE statement with the value
you copied. If this changes the score, then there is an invisible or
special character in the record.
If the previous query didn't result 7 then try this:
select District,
CASE
WHEN Upper(District) like '%GANGNAM-GU%' THEN 7
WHEN Upper(District) like '%GANGNAM%' THEN 6
ELSE 0
END score
from restaurants
WHERE RestaurantID = 1
Let's see if we catch the problem.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2455682614,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WDGHT4QPVJLUDGWJMDZ67NKRAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJVGY4DENRRGQ
.
You are receiving this because you were mentioned.Message ID:
@.***>

Sorry, didn't have the time to read your whole comment, but I saw the error message:
"SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1"
Having error at "line 1" means the very first row is wrong, having "END" in the first row means that this was in the very first line, so the query execution was wrong.
Be carefull with ChatGPT, because when it doesn't know the answer, it will lie with complete confidence :)
Partially it was right, DBeaver divides your query, and this is why it's not running successfully.
The semicolon is used as a Delimiter:

It will split your queries per semicolon. It should not split it for Trigger creation, but potentially can cause this issue.
Can you try selecting your query and execute SQL script (Alt+x)

I hope it's this simple. If not, then simple give this comment a sad smile and will read your whole comment.

🤗 Will try tomorrow. Going to sleep now. 🕥
On Tue, 5 Nov 2024 at 21:04, TB-HunBoka @.***> wrote:

Sorry, didn't have the time to read your whole comment, but I saw the
error message:
"SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1"
Having error at "line 1" means the very first row is wrong, having "END"
in the first row means that this was in the very first line, so the query
execution was wrong.
Be carefull with ChatGPT, because when it doesn't know the answer, it will
lie with complete confidence :)
Partially it was right, DBeaver divides your query, and this is why it's
not running successfully.
The semicolon is used as a Delimiter:
image.png (view on web)
https://github.com/user-attachments/assets/5a76207c-e8ca-4dcf-a08b-5b8dfa4c8ccb
It will split your queries per semicolon. It should not split it for
Trigger creation, but potentially can cause this issue.
Can you try selecting your query and execute SQL script (Alt+x)
image.png (view on web)
https://github.com/user-attachments/assets/bccee2fd-fbc7-4f7f-a86c-e19db25766c1
I hope it's this simple. If not, then simple give this comment a sad smile
and will read your whole comment.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2456746003,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WAYT3VSRQLIABY5723Z7CJ4PAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJWG42DMMBQGM
.
You are receiving this because you were mentioned.Message ID:
@.***>

The key point from this update is that while your advice about query
splitting in DBeaver motivated us to make a second attempt, we still
haven't resolved the issue. Your suggestion to use "Execute SQL Script"
(Alt + X) to run the entire trigger definition in one go did make sense, as
it should help DBeaver interpret the entire trigger as a single block
without splitting. However, despite following this process carefully, we
are still encountering a syntax error near the END statement.
This experience has led us to doubt we will be able to figure out how to
use a trigger for automating SortOrder. However, we do find some
consolation in having successfully calculated the SortOrder manually for
all rows in the restaurants table. This method, though not automated,
worked perfectly for our current needs, and we can continue to use it in
the future if we are unable to automate the process. In this sense, the
problem is effectively solved, much thanks to your earlier advice, but we
would still prefer to have a trigger in place for greater efficiency.
If you have any other suggestions for resolving these persistent syntax
errors or any thoughts on an alternative approach, I would greatly
appreciate it.
Thank you again for your help and patience.
Best regards,
Reuben
Below is what I attempted with ChatGPT following your advice, which begins
with ChatGPT's response to what you advised:
The advice from TB-HunBoka is definitely useful for attempting to resolve
the problem. Here’s why:

Query Splitting Issue: TB-HunBoka's observation that DBeaver might be
splitting the query due to semicolons is insightful. DBeaver's behavior can
indeed cause multi-line or complex SQL statements, like trigger creation,
to fail if it mistakenly identifies an incomplete block. Executing the
entire script as a single block using "Execute SQL script" (Alt + X) is a
smart way to circumvent this issue, ensuring that DBeaver doesn’t interrupt
the intended flow of the SQL statements.
2.
Delimiters: The problem with the END statement could have been tied to
how DBeaver interprets the termination of commands when working with a
trigger that involves multiple lines. By using the "Execute SQL script"
option, it should prevent the system from seeing END; as the termination
of an entire session, which was likely the root of the persistent syntax
errors.
Next Steps
Based on TB-HunBoka’s advice, here’s how we can proceed:

Select the Entire Trigger Script: Ensure that you highlight the
entire trigger definition in the SQL editor.
Execute Using Alt + X: Run the script using Alt + X (or
right-click and choose "Execute SQL Script").
Observe the Result: Check if the syntax error still appears or if
the trigger is created successfully.

This method essentially forces DBeaver to interpret the trigger creation as
one unified SQL script without automatically splitting it based on
semicolons, which is crucial for defining complex SQL statements like
triggers or procedures.
Step 1: Trigger Script for Automating SortOrder Calculation:
Ensure that you highlight everything from CREATE TRIGGER to END; when you
execute it.
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

We got the same error message as previously:
SQL Error [1064] [42000]: (conn=52) 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 'END' at line 1
Step 2: Use Backticks and Delimiters in the Script
Use the DELIMITER keyword to avoid issues with multiple statements being
interpreted as separate.
USE mapmarkers;
DELIMITER $$
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END$$

We got the same unintended result as previously:
DELIMITER ;
Updated Rows -1 Query DELIMITER ;
Step 3: Test a Super Simple Trigger
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = 1.0;
END;
This is the revised trigger:
USE mapmarkers; CREATE TRIGGER before_insert_restaurants BEFORE INSERT ON
restaurants FOR EACH ROW BEGIN SET NEW.SortOrder = 1; END;
For both, there was the same error:
Error occurred during SQL script execution Reason: SQL Error [1064]
[42000]: (conn=54) 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 '' at line 5
Syntax Error at Line 5: This implies an issue within the BEGIN...END
block, possibly related to delimiters or how the trigger is being parsed by
DBeaver.
The persistent syntax error at line 5 is quite telling and suggests
there’s something deeper at play, possibly related to how MariaDB or
DBeaver is interpreting the script. Since this issue remains, let’s
continue breaking it down and applying additional strategies. Here are a
few detailed suggestions:
Possible Root Causes and Adjustments:

DBeaver’s Handling of SQL Blocks:

DBeaver may be having trouble interpreting multiple-statement
blocks (like those using BEGIN...END), which is why it splits scripts
at semicolons and doesn’t recognize END properly.

MariaDB Version Specifics:

Some versions of MariaDB have slight differences in the syntax they
support. For instance, some nuances in how CASE statements or
BEGIN...END blocks are defined might vary.

New Strategies to Attempt:1. Execute Without BEGIN...END:

A single statement trigger might bypass the issue with BEGIN...END.
Try a simpler approach that avoids the BEGIN...END entirely. This
script only sets a value directly without the need for a block structure,
which might eliminate the syntax parsing issue:

Another unintended:
Queries 2
Updated Rows 0
This outcome indicates that DBeaver recognized the script as consisting of two
queries, and it seems neither of the two queries executed fully as
intended. Specifically:

"Queries: 2" means that DBeaver split your script into two distinct
parts.
"Updated Rows: 0" means that no rows were updated, and the trigger
wasn’t created, indicating that something went wrong.

This result aligns with the possibility that DBeaver is misinterpreting
how to handle the CREATE TRIGGER block, and it also suggests that it
didn't recognize the whole block as a single query.
Immediate Actions and Recommendations:1. Execute Each Part Separately:
To understand where the problem lies:

Step 1: Highlight only USE mapmarkers; and execute it first.
Step 2: After that successfully executes, highlight the entire
trigger block (CREATE TRIGGER to END;) and execute it separately using *Alt

X*.

This separation should help:

Confirm if USE mapmarkers; is causing the separation.
Let you explicitly execute the CREATE TRIGGER statement without any
context-switching, allowing DBeaver to interpret it as a whole block.

2. Remove the USE mapmarkers Statement:
If you have already selected mapmarkers as the active database manually
in DBeaver, you do not need to run USE mapmarkers;:

Remove USE mapmarkers; entirely, to avoid the script being split.
Make sure that mapmarkers is already set as the current active
database.

Here is the simplified script:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
SET NEW.SortOrder = 1;
Another error message:
Error occurred during SQL query execution
Reason:
SQL Error [1359] [HY000]: (conn=54) Trigger
'mapmarkers.before_insert_restaurants' already exists
The error message "Trigger 'mapmarkers.before_insert_restaurants' already
exists" indicates that a trigger with the name before_insert_restaurants
has already been successfully created in your database, which is great news
in a way—it means one of your attempts to create the trigger actually
worked!
Now, let's take a look at the best way forward:
Immediate Actions:

Confirm the Existing Trigger:

You can confirm if the trigger is indeed created by running a query to
list all triggers in your database. Use the following SQL query to check:

before_insert_restaurants INSERT restaurants SET NEW.SortOrder = 1 BEFORE
2024-11-06
The result you’re seeing here (before_insert_restaurants INSERT restaurants
SET NEW.SortOrder = 1 BEFORE 2024-11-06) appears to be a summary of
the existing
trigger and its key properties:

before_insert_restaurants: This is the name of the trigger that
has already been created.
INSERT restaurants: Indicates that the trigger is fired before an
INSERT operation on the restaurants table.
SET NEW.SortOrder = 1: This is the action that the trigger
performs, which sets the value of SortOrder to 1 for any new row
inserted.
BEFORE 2024-11-06: This likely refers to the trigger’s creation or
activation date or is simply a part of a metadata description by DBeaver.

What This Tells Us:

The trigger already exists and appears to be functioning with the
action you provided, which is to set NEW.SortOrder to 1 before an insert
occurs.
It seems like this trigger was successfully created on or before the
date 2024-11-06.

*Next Steps:*1. Drop the Existing Trigger:
Since we need a new version of the trigger with the correct logic, you will
need to drop the current one. This can be done with:
DROP TRIGGER IF EXISTS before_insert_restaurants;
Updated Rows 0
Query DROP TRIGGER IF EXISTS before_insert_restaurants
Here’s what "Updated Rows: 0" really means in the context of dropping a
trigger:

The database system reports 0 updated rows because there are no rows
to modify in a table. Instead, the operation is about removing the
trigger itself.
The message "Updated Rows: 0" here indicates that no existing
trigger was found, meaning there wasn't anything for it to delete.
This could mean that:

The trigger had already been deleted previously.
The trigger creation in the past never succeeded, and therefore
there was nothing to drop.

SHOW TRIGGERS FROM mapmarkers;
This confirmed there are no triggers.
New Trigger:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

Error occurred during SQL script execution
Reason:
SQL Error [1064] [42000]: (conn=54) 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 '' at line 25
Revised Trigger:
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = (
CASE
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s*\(.\)', '')) =
'Gangnam-gu' THEN 7.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jung-gu' THEN 14.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Jongno-gu' THEN 13.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seocho-gu' THEN 18.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Yongsan-gu' THEN 25.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Dongjak-gu' THEN 3.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Eunpyeong-gu' THEN 4.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Gangseo-gu' THEN 8.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Mapo-gu' THEN 16.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seodaemun-gu' THEN 19.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongbuk-gu' THEN 20.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Seongdong-gu' THEN 21.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.\)', '')) =
'Songpa-gu' THEN 22.0
WHEN TRIM(REGEXP_REPLACE(NEW.District, '\s\(.*\)', '')) =
'Yeongdeungpo-gu' THEN 24.0
ELSE 0.0
END
+
(ASCII(UPPER(SUBSTRING(NEW.RestaurantName-English, 1, 1))) - 64)

0.01
);
END;

Error occurred during SQL script execution
Reason:
SQL Error [1064] [42000]: (conn=54) 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 '' at line 25
The error message you're receiving—"SQL Error [1064] [42000]: (conn=54)
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 '' at line 25"—is
a syntax error related to something being incorrect or missing in the SQL
statement, specifically around line 25 of the code.
Let's carefully troubleshoot and fix this step by step.
Possible Causes and Solutions for the Error:

Missing or Incorrect Semicolons:

One common mistake in trigger definitions is missing semicolons (;)
after each SQL statement. Every statement in the BEGIN ... END block
must end with a semicolon.

Ensure that every line (including each SET, WHEN, CASE, and END)
is followed by a semicolon.

Invalid Delimiters or Parsing Issues in DBeaver:

In DBeaver, the default semicolon (;) is the statement delimiter.
However, the CREATE TRIGGER statement contains multiple lines with
semicolons inside the block, which could confuse the parser if it isn’t
handled properly.

Unintended Special Characters or Formatting Issues:

Hidden or special characters sometimes accidentally make their way
into the script when copying and pasting, which might lead to unexpected
parsing errors.

MariaDB's Syntax Specifics:

MariaDB might have certain requirements regarding how the END block
is terminated.

Next Steps to Fix the Trigger Script:1. Incremental Debugging Approach:
Let's simplify things to debug the root of the issue by attempting simpler
versions of the trigger. This will help determine whether the problem lies
in the overall structure, the CASE statement, or elsewhere.
Start by creating a very simple trigger first. This will let us verify if
the basic trigger structure is correctly executed.
Attempt again with a Basic Trigger:
USE mapmarkers;
CREATE TRIGGER before_insert_restaurants
BEFORE INSERT ON restaurants
FOR EACH ROW
BEGIN
SET NEW.SortOrder = 1.0;
END;
Error occurred during SQL script execution
Reason:
SQL Error [1064] [42000]: (conn=54) 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 '' at line 5
What We Have Learned So Far

Syntax Errors at Line 5 or END Statement: The errors suggest
something fundamental with the way the trigger is being interpreted by the
MariaDB environment you're using, specifically with the BEGIN ... END
block.
Attempted Solutions: We've tried different iterations, simplified
versions, ensured delimiters, and checked syntax, but MariaDB still raises
errors.
DBeaver as an Environment: It’s possible that DBeaver, as a SQL
client, has particular quirks about parsing SQL blocks for triggers. GUI
clients can occasionally add complexity in situations like these.

On Tue, 5 Nov 2024 at 22:06, Reuben Fraser @.***>
wrote:

🤗 Will try tomorrow. Going to sleep now. 🕥
On Tue, 5 Nov 2024 at 21:04, TB-HunBoka @.***> wrote:

Sorry, didn't have the time to read your whole comment, but I saw the
error message:
"SQL Error [1064] [42000]: (conn=31) 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 'END' at line 1"
Having error at "line 1" means the very first row is wrong, having "END"
in the first row means that this was in the very first line, so the query
execution was wrong.
Be carefull with ChatGPT, because when it doesn't know the answer, it
will lie with complete confidence :)
Partially it was right, DBeaver divides your query, and this is why it's
not running successfully.
The semicolon is used as a Delimiter:
image.png (view on web)
https://github.com/user-attachments/assets/5a76207c-e8ca-4dcf-a08b-5b8dfa4c8ccb
It will split your queries per semicolon. It should not split it for
Trigger creation, but potentially can cause this issue.
Can you try selecting your query and execute SQL script (Alt+x)
image.png (view on web)
https://github.com/user-attachments/assets/bccee2fd-fbc7-4f7f-a86c-e19db25766c1
I hope it's this simple. If not, then simple give this comment a sad
smile and will read your whole comment.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2456746003,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WAYT3VSRQLIABY5723Z7CJ4PAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJWG42DMMBQGM
.
You are receiving this because you were mentioned.Message ID:
@.***>

So sorry mate. My bad.
Select the query and execute with ctrl+enter

Not as script. Sorry again. I wrote my previous comment mid work, I was distracted.

@ShadelessFox potential bug outcome:
Executing the Create Trigger commands with semicolon in the begin end clause will fail. The Dbeaver will try to split the sql.
Expected behavior: The delimiter should not split trigger commands having semicolon in the begin end clause.
I tried this on SQLite and I do not experience this issue.

Success 🏆
I took your advice: "I tried this on SQLite and I do not experience this
issue."
I did it on PHPMyAdmin, and it worked as expected.
Thanks 🤗
On Wed, 6 Nov 2024 at 12:15, TB-HunBoka @.***> wrote:

@ShadelessFox https://github.com/ShadelessFox potential bug outcome:
Executing the Create Trigger commands with semicolon in the begin end
clause will fail. The Dbeaver will try to split the sql.
Expected behavior: The delimiter should not split trigger commands having
semicolon in the begin end clause.
I tried this on SQLite and I do not experience this issue.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2458509066,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WHRGHUHDZDBOJ27MZ3Z7FUUDAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJYGUYDSMBWGY
.
You are receiving this because you were mentioned.Message ID:
@.***>

I am glad you achieved your goal.
If you don't want to change DB, then FYI: it works on MariaDB too, I tried that. The only problem is the default SQL parsing, that I provided a workaround.

It seems like you have a general database question rather than something specific to DBeaver. Can you please explain, briefly, what your question/issue is about and whether you still need help resolving it?

Have you read the exchange above between me and TB-HunBoka? If so, you will
find the answer to your question. If not, a glance at the conclusion of the
exchange might be sufficient for you to understand what can be learned from
my experience.
But if you are unwilling to do either, I will now sum it up.
I found the answer to my problem was to do the same query in PHPAdmin as
had been failing in DBeaver. You can either respond to this observation by
doing whatever is required on DBeaver to make such a query work on your
platform in the same way as it does on PHPAdmin, or you can choose to
ignore the problem with your platform.
Whatever, the lesson that I have learned is that while DBeaver is clearly
better than PHPAdmin for most things, I need to continue to use both due to
issues such as this.
Reuben
On Thu, 7 Nov 2024 at 00:27, ShadelessFox @.***> wrote:

It seems like you have a general database question rather than something
specific to DBeaver. Can you please explain, briefly, what your
question/issue is about and whether you still need help resolving it?
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2459750687,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WF2XXKWDEWA5Y4LGF3Z7IKKXAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJZG42TANRYG4
.
You are receiving this because you were mentioned.Message ID:
@.***>

To be completely honest, I wanted to avoid reading all these comments. But I realized the answer was right there, right before my eyes. Pardon me.
So, the problematic query is the following (adapted to work with the table that I have):
create trigger before_insert_album
before insert on Album
for each row
begin
	set NEW.ArtistId = ArtistId;
end;

And, as @TB-HunBoka correctly pointed out, it's indeed caused by the ; inside the trigger's body.

No. You are wrong. It is not even about an artist and album, so I don't
even know what you are referring to.
On Thu, 7 Nov 2024 at 22:13, ShadelessFox @.***> wrote:

To be completely honest, I wanted to avoid reading all these comments. But
I realized the answer was right there, right before my eyes. Pardon me.
So, the problematic query is the following (adapted to work with the table
that I have):
create trigger before_insert_album
before insert on Album
for each rowbegin
set NEW.ArtistId = ArtistId;
end;
And, as @TB-HunBoka https://github.com/TB-HunBoka correctly pointed
out, it's indeed caused by the ; inside the trigger's body.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2461963917,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WFOWP4XAYMNXR4ITY3Z7NDNDAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRRHE3DGOJRG4
.
You are receiving this because you were mentioned.Message ID:
@.***>

Then please explain in a short form, with examples, what your issue is about.

I wrote:
I am going to use both PHPMyAdmin and DBeaver. I have my database on both
simultaneously. It is continuously synced, or perhaps rather, both are
tools of MariaDB, or something like that. It has been explained to me, but
I am a little confused.
Insofar as I understand, both PHPMyAdmin and DBeaver are tools of MariaDB,
though I could be mistaken, but I do have both synced. What I found is that
the query that DBeaver couldn't process was easily processed on PHPMyAdmin.
That is why I am going to continue to use PHPMyAdmin for things like that,
even though when it comes to everything else, I much prefer DBeaver. As
such, from the perspective of you as a representative of DBeaver, isn't it
in your own self-interest as a company to correct the bug or whatever so
that your tool can process the same query as PHPMyAdmin, as they are your
competition?
Reuben
On Thu, 7 Nov 2024 at 23:26, ShadelessFox @.***> wrote:

Then please explain in a short form, with examples, what your issue is
about.
—
Reply to this email directly, view it on GitHub
https://github.com/dbeaver/dbeaver/issues/36132#issuecomment-2462109436,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/BBZ46WF3LSPWTATHANJOUF3Z7NL7TAVCNFSM6AAAAABQ7QESVGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRSGEYDSNBTGY
.
You are receiving this because you were mentioned.Message ID:
@.***>

It looks like an issue with determining the bounds of the CREATE TRIGGER statement, as @ShadelessFox and @TB-HunBoka described.


[TEST] Removed setting mkfs-ext4-parameters causes test cases failed

What's the test to develop? Please describe
Since setting mkfs-ext4-parameters has been removed, the related setup should be removed or modified in longhorn-tests too, otherwise lots of test cases would fail when trying to reference this value. Like: https://ci.longhorn.io/job/public/job/master/job/sles/job/amd64/job/longhorn-tests-sles-amd64/475/testReport/junit/tests/test_node/test_node_disk_update/
Describe the items of the test development (DoD, definition of done) you'd like

Please use a task list for items on a separate line with a clickable checkbox https://docs.github.com/en/issues/tracking-your-work-with-issues/about-task-lists

[ ] item 1

Additional context
Add any other context or screenshots about the test request here.

When removing some existing/deprecated features, need to run testing to ensure that any tests are broken.
cc @longhorn/dev

I broke this in #4914. I'll assign this one to myself and try to get things working again.

Verified passed on master-head (longhorn-manager a7dd20c). We can find daily regression is back to normal and isn't overwhelmed by mkfs-ext4-parameters related errors.
amd64: https://ci.longhorn.io/job/public/job/master/job/sles/job/amd64/job/longhorn-tests-sles-amd64/484/
arm64: https://ci.longhorn.io/job/public/job/master/job/sles/job/arm64/job/longhorn-tests-sles-arm64/472/


arm: fix dataabort on qemu-armv7a:knsh

Summary
add memory map for DDR region
fix arm-v7a/knsh boot dataabort on arm_addrenv_utils.c first time memset after arm_pgvaddr.
Impact
will dataabort before fix.
[    0.008000] arm_dataabort: Data abort. PC: 00606d42 DFAR: 48000000 DFSR: 00000805
[    0.008000] dump_assert_info: Current Version: NuttX  12.7.0-RC0 e00fbc5557d-dirty Oct 21 2024 20:26:19 arm
[    0.008000] dump_assert_info: Assertion failed panic: at file: armv7-a/arm_dataabort.c:161 task: Idle_Task process: Kernel 0x600c05
[    0.008000] up_dump_register: R0: 48000000 R1: 00000000 R2: 48000400  R3: 48000000
[    0.008000] up_dump_register: R4: 48000000 R5: 0000000b R6: 00000000  FP: 40205e00
[    0.008000] up_dump_register: R8: 00000000 SB: 80000000 SL: 0000a108 R11: 0000015f
[    0.008000] up_dump_register: IP: 00000028 SP: 40202df0 LR: 0060903f  PC: 00606d42

Testing
CI test.
# nuttx dir ubuntu environment.
./tools/configure.sh -l qemu-armv7a:knsh
make -j
make export -j
cd ../apps
./tools/mkimport.sh -z -x ../nuttx/nuttx-export-*.tar.gz
make import -j
cd ../nuttx
qemu-system-arm -semihosting -M virt -m 1024 -nographic -kernel ./nuttx

[Experimental Bot, please feedback here]
The PR summary is missing some key information, and the testing section needs more detail to meet the NuttX requirements fully. Here's a breakdown:
What's Missing/Needs Improvement:

Summary:

Why is the change necessary? The summary mentions a data abort but doesn't explain why adding a DDR memory map fixes it. Provide a technical explanation; for instance, is the DDR region not being recognized by the MMU, causing an access violation?
What functional part is changed? Be specific. Mention the files modified (e.g., memory map configuration, arm_addrenv_utils.c).
How does the change work? Explain the mechanism:

How does adding the DDR memory map resolve the data abort?
How is the memset issue in arm_addrenv_utils.c connected to the missing memory map?

Impact:

Impact on hardware: State which architectures and boards are affected by this change (specifically those using the DDR region).
Impact on documentation:  If the DDR region is new, documentation will likely need updates to inform users about its configuration and usage.

Testing:

Build Host(s): Specify the OS, CPU architecture, and compiler version used for testing.
Target(s):  "CI test" isn't sufficient. Mention the specific QEMU configurations used (e.g., qemu-armv7a:knsh).
Testing logs: The provided logs only show the error before the fix. Include logs after the change demonstrating that the data abort no longer occurs.

How to Improve the PR:

Expand the Summary:  Clearly explain the technical reason for the data abort and how your changes address the root cause. Be specific about the files and code sections modified.
Complete the Impact Section: Provide details about affected hardware and indicate whether documentation updates are necessary.
Enhance the Testing Section: Include details about your build host environment, the exact QEMU targets tested, and logs showing successful operation after applying your changes.

By providing this additional information, you'll make it much easier for reviewers to understand your changes and assess if the PR meets the NuttX requirements.