Mysql Stored Procedure error debugging records, mysql Stored Procedure

Mysql Stored Procedure error debugging records, mysql Stored Procedure
Zookeeper mysql Stored Procedure error debugging records

The company's Platform runs on the mysql database. Some stored procedures report errors, the debugging process is painful, and errors and solutions are recorded for reference.

Debugging environment: dbForge Studio for MySQL 6.1, trial version;

1. BIZ_GET_ORGAN_BY_CONDITION

Problem 1: a recursive call error is prompted. There are too many recursion times. The maximum value is 255 based on the service settings;

SET @ max_sp_recursion_depth = 10;

If garbled characters are common, the condition becomes invalid and an endless loop occurs;

 

Question 2: The variable content is garbled, the database content is Chinese, urt-8

Declarevc_agency_type varchar (64) character set utf8;

When defining variables, you must set the character set;

 

Question 3: The stored procedure parameters are passed in Chinese and garbled; the defined parameters are the same as above;

 

2. BIZ_QUERY_USER_BY_CONDITION

Problem 1: row_count () returns-1

The Return Value of the select statement is FOUND_ROWS (); row_count () is generally the number of rows affected by insertion, modification, and deletion;

 

Question 2: mysql division of Integers

Declare page_count int;

Set page_count = 15/10; the result is automatically rounded to 2;

Use the following statement: set page_count = FLOOR (result_count/page_size );

 

3. C3_BIZ_DATA_LIMIT

Problem 1: The cursor has a record, but the value is null: The variable in the cursor statement cannot have the same name as the field and cannot be a keyword;

Question 2: The cursor cannot get the value;

The field name and variable name must not be the same; if the field name must be the same, add an alias to the table for the difference;

 

Problem 3: The out parameter cannot be set.

Changed to the inout type. There are three types of stored procedures: in out inout;

Question 4: Change the right function to the subStr function;

 

4. BIZ_CHANGER_ORG_PARENT_ID

Problem 1: mysql does not have the if not exists syntax, so you need to take the total number and then determine the number of rows;

 

Question 2: update does not support subqueries;

Update sys_org set leaftype = 1 where id = (

Selectorg_parent_id from sys_org where id = Variable

);

To:

Update sys_org set leaftype = 1 where id = (

Select org_parent_id from

(Select * from sys_org WHERE id = vc_source_id)

);

How does mysql record the Stored Procedure error information?

Changed it for you.

DELIMITER $

Create procedure pplog. addUser ()
Begin
Declare count1 int default 0;
If count1 <1000000 then
Set count1 = count1 + 1;
Insert into pplog. user_log_inf (infohash, filesize, userIp, start_time, end_time, download, total_visit, protocal)
Values ("3662 HDSH", 232, '10, 23,351 ', "10:13:04", "11:34:40", 44 );
Else set count1 = count1 + 10;
End if;
END $

DELIMITER;

Why can't I perform one-step debugging when Mysql spam the stored procedure?

MySQL is a mature medium-and small-sized database with a wide range of users, and many of them are well-known large companies. Of course, every kind of things has its shortcomings, and mysql is no exception, but it is said to be rubbish, either technically or emotionally, what's more, your reason is that "phpmyadmin is difficult to use and the interface is ugly, and Chinese characters are garbled ", however, this also shows that you do not know mySQL (specifically, it should be "illiterate mySQL", because the above two problems are exactly what you are ignorant of mySQL, even if you are right-it looks ugly, can you change it? mySQL has many management tools). I want to learn from you further, you should like it.