SoFunction
Updated on 2025-04-06

The problem-solving process of not executing MySQL stored procedures

1. Problem background

Recently, a test environment was deployed. My colleague found that a stored procedure left behind was not running before, so I needed my help to troubleshoot it. Let me talk about my investigation process below. List each possible reasons and considerations.

2. Troubleshooting

2.1. Issues in database version upgrade

We used version 8. MySQL before, and recently the operation and maintenance upgraded the database to version 8.4.3. At first, I suspected that it was caused by the database upgrade. After checking the relevant version instructions, I did not find any problem that would cause the stored procedure to not be executed.

2.2. Issues of insufficient user permissions

Check that the user defined in the stored procedure isroot@%, and checked the permissions and found no problem.

Here are the queries involved:

User permission query

Ensure that the user executing the stored procedure has sufficient permissions. You can use the following command to view permissions:

SHOW GRANTS FOR 'your_username'@'your_host';

And make sure you have EXECUTE and ALTER ROUTINE permissions. If not, you can grant permissions via the following command:

GRANT EXECUTE, ALTER ROUTINE ON your_database_name.* TO 'your_username'@'your_host';

Query command analysis

SHOW GRANTS FOR `root`@`%`;

This SQL command is used in the MySQL database to display the user name as root and can be from any host (by%Represents) Permissions to the user who connects to the MySQL server.

  • root user: root is the default superuser of MySQL, with full access to the database, including creating, modifying, deleting databases and data tables, and managing user permissions.
  • % Meaning:%In MySQL, it means any host, that is, the root user can connect to the MySQL server from any host.

If it is being executedSHOW GRANTS FOR 'root'@'%';Later I found that the results were missingEXECUTEPermissions, indicatingrootThe user is currently not granted permission to execute stored procedures or functions.EXECUTEPermissions are permissions used in MySQL to execute stored procedures and stored functions.

Can be usedGRANTThe statement isrootUser AddEXECUTEPermissions:

GRANT EXECUTE ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

This command does the following:

  • GRANT EXECUTE ON *.* TO 'root'@'%';:forrootUser grants on all databases and all tablesEXECUTEPermissions.*.*Represents all databases and all tables, and you can also specify specific databases or tables as needed.
  • FLUSH PRIVILEGES;: Refresh MySQL's permission cache so that the new permission settings take effect immediately.

2.3. Problems with stored procedures themselves

First of all, this stored procedure can run normally in production environment and other environments. Because it was copied, I didn't expect this error from the beginning. But after checking this, I still use itCALL()I manually scheduled the stored procedure, but it actually reported an error, and the error was as follows:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Newton.imsi_transaction_cdr_raw_cn.carrier_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Now I finally feel like I'm looking at it, which is obviously a problem caused by the SQL pattern. I checked the stored procedure statement, there is reallyGROUP BY, and does not meetONLY_FULL_GROUP_BYRequirements for the model. The reason why the stored procedure was not executed was found, and I thought that removing the SQL pattern next should solve the problem.

I quickly checked the current global SQL mode and the current session SQL mode, and was stunned. There was no SQL mode in the SQL mode.ONLY_FULL_GROUP_BYAt this time, I remembered that after the environment was deployed, I asked the operation and maintenance to implement itONLY_FULL_GROUP_BYThe mode is removed, so why do you still report such an error now? It shouldn't be, I can't figure it out.

Here is a statement that MySQL querys SQL mode and removes ONLY_FULL_GROUP_BY:

Query the global SQL mode, which affects all new sessions (connections), but does not affect existing sessions

SELECT @@GLOBAL.sql_mode;

Query the current session SQL mode, the current session SQL mode only affects the current connection‌

SELECT @@SESSION.sql_mode;

Or, more concisely:

SELECT @@sql_mode;

Temporarily remove ONLY_FULL_GROUP_BY (affects current session only)

useSET SESSIONStatement to change the SQL mode of the current session:

SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '');

Permanently remove ONLY_FULL_GROUP_BY (affects all new sessions)

To permanently change the global SQL schema, you need to edit the MySQL configuration file (e.g.or) and restart the MySQL service. However, direct modification of the global SQL pattern may affect other users and applications, so it is generally recommended that such changes be made only if necessary.

Operation steps:

  • Open the MySQL configuration file.
  • turn up[mysqld]part.
  • Modify or addsql_modeOK, make sure not includedONLY_FULL_GROUP_BY。
  • Save the file and restart the MySQL service.

For example, settings in a configuration file might look like this:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,...(Other modes，But not includingONLY_FULL_GROUP_BY)"

3. Problem resolution

The current situation is MySQL version 8.4.3. I have obviously removed the ONLY_FULL_GROUP_BY mode before, so why does it still report an error of 1055 when calling storage?

The error prompt is very obvious, it must be caused by the ONLY_FULL_GROUP_BY mode. Now we need to find out why this error is reported even though there is no such mode.

While searching for information online, I was puzzled and accidentally saw an explanation:

‌sql_mode inside stored procedure‌: The stored procedure may capture the current sql_mode setting when created and use it every time it is executed. If the stored procedure was created when ONLY_FULL_GROUP_BY mode is enabled, then the stored procedure may still use the old sql_mode inside it even if you disable the mode later. To resolve this issue, you need to recreate the stored procedure, making sure that the ONLY_FULL_GROUP_BY mode is disabled at creation time.

Well, the problem is clear. It was like this at that time. After the operation and maintenance side created the database, I initialized the library table and stored procedures. After the service deployment, I found that there was aGROUP BYThe operation and maintenance mode was found to remove the ONLY_FULL_GROUP_BY mode, which means that when the stored procedure is created, the SQL mode of the database contains the ONLY_FULL_GROUP_BY mode. Therefore, the sql_mode‌ inside the stored procedure must also contain the ONLY_FULL_GROUP_BY mode, so an error of 1055 will be reported.

The solution is very simple. Delete the current stored procedure and recreate it. I tried it, and after recreating it, I called it manually, and it was executed normally. At this point, the problem is solved!

The above is the detailed content of the problem troubleshooting process that the MySQL stored procedures are not executed. For more information about the MySQL stored procedures, please pay attention to my other related articles!