MySQL Forums - Stored Procedures

How to read config data in UDF (no replies)

David L Lambert — Sat, 05 Jul 2025 03:54:10 +0000

I'm designing a MySQL UDF (or possibly family of UDFs) to accomplish a certain task. The UDF depends on some "configuration information" (among other things, the hostname for a server). I'd rather not pass that in as an argument to every function call. I can think of a few ways to do it...

1. As a set of environment variables, read them with getenv()

2. As a plain-text configuration file in a well-known place (perhaps "/etc/mysql", or the mysql data directory), and read and parse it in xxx_init()

3. As a table-row in a new table in the `mysql` schema

Any advice on the benefits and drawbacks of the various options?

For option 2, is there a server variable I should consider for that "well-known place" instead of hard-coding it? If so, what's the proper way to read a server veriable from within a C++ UDF?

For option 3, what's the proper way to read one row from one table from within a C++ UDF?

Hexadecimal concatenation in recursive CTE is truncated to 64 characters. (no replies)

Felipe Lorenzo — Thu, 08 May 2025 16:53:47 +0000

I'm working on Nested Set Hierarchies. I'm using a recursive CTE to build the nested equivalent of adjacency list table. MySQL 8.4 is my platform.

CREATE TABLE dct_node_adjc
(
hdct_node_adjc BIGINT UNSIGNED NOT NULL DEFAULT (UUID_SHORT()),
hdct BIGINT UNSIGNED NOT NULL,
hdct_node_adjc_parent BIGINT UNSIGNED NULL,
CONSTRAINT PRIMARY KEY (hdct_node_adjc),
CONSTRAINT FOREIGN KEY (hdct_node_adjc_parent)
REFERENCES dct_node_adjc(hdct_node_adjc) ON UPDATE CASCADE
);

INSERT INTO dct_node_adjc (hdct_node_adjc, hdct, hdct_node_adjc_parent)
VALUES
(101283669313847302, 101283669313847296, NULL),
(101324752823517184, 101283669313847297, 101283669313847302),
(101283669313847303, 101283669313847298, 101283669313847302),
(101324752823517185, 101283669313847298, 101324752823517184),
(101283669313847304, 101283669313847299, 101283669313847303),
(101324752823517186, 101283669313847299, 101324752823517185),
(101283669313847305, 101283669313847301, 101283669313847304),
(101324752823517187, 101283669313847301, 101324752823517186),
(101283669313847311, 101283669313847306, NULL),
(101283669313847312, 101283669313847307, 101283669313847311),
(101283669313847313, 101283669313847308, 101283669313847312),
(101283669313847314, 101283669313847309, 101283669313847313),
(101283669313847315, 101283669313847310, 101283669313847314),
(101283669313847320, 101283669313847316, NULL),
(101283669313847321, 101283669313847317, 101283669313847320),
(101283669313847322, 101283669313847318, 101283669313847321),
(101283669313847323, 101283669313847319, 101283669313847322);

-- nested set table
DROP TABLE IF EXISTS dct_node_nest;

CREATE TABLE dct_node_nest
(
hdct_node_adjc BIGINT UNSIGNED NOT NULL,
hdct_node_adjc_parent BIGINT UNSIGNED NULL,
hlevel INT NOT NULL DEFAULT 0,
LeftBower INT NOT NULL DEFAULT 0,
RightBower INT NOT NULL DEFAULT 0,
NodeNumber INT NOT NULL DEFAULT 0,
NodeCount INT NOT NULL DEFAULT 0,
SortPath VARBINARY(8000),
CONSTRAINT UNIQUE KEY (hdct_node_adjc)
);

Using recursive cte, to build the SortPath VARBINARY column for the final table.

SET SESSION sql_mode = '';

WITH RECURSIVE cte AS (
SELECT a.hdct_node_adjc, a.hdct_node_adjc_parent
, 1 AS hlevel, 0 AS LeftBower, 0 AS RightBower, 0 AS NodeNumber , 0 AS NodeCount
, UNHEX(HEX(a.hdct_node_adjc)) AS SortPath
FROM dct_node_adjc AS a WHERE a.hdct_node_adjc_parent IS NULL
UNION ALL
SELECT r.hdct_node_adjc, r.hdct_node_adjc_parent
, c.hlevel + 1 AS hlevel, c.LeftBower, c.RightBower, c.NodeNumber, c.NodeCount
, CONCAT(c.SortPath, UNHEX(HEX(r.hdct_node_adjc))) AS SortPath
FROM dct_node_adjc AS r JOIN cte AS c ON c.hdct_node_adjc = r.hdct_node_adjc_parent
)
SELECT c.hdct_node_adjc, c.hlevel, c.SortPath FROM cte AS c;

SET SESSION sql_mode = 'TRADITIONAL';

-- output
+--------------------+--------+--------------------------------------------------------------------+
| hdct_node_adjc | hlevel | SortPath |
+--------------------+--------+--------------------------------------------------------------------+
| 101283669313847302 | 1 | 0x0167D4F5EB000006 |
| 101283669313847311 | 1 | 0x0167D4F5EB00000F |
| 101283669313847320 | 1 | 0x0167D4F5EB000018 |
| 101283669313847303 | 2 | 0x0167D4F5EB0000060167D4F5EB000007 |
| 101324752823517184 | 2 | 0x0167D4F5EB0000060167FA536B000000 |
| 101283669313847312 | 2 | 0x0167D4F5EB00000F0167D4F5EB000010 |
| 101283669313847321 | 2 | 0x0167D4F5EB0000180167D4F5EB000019 |
| 101283669313847304 | 3 | 0x0167D4F5EB0000060167D4F5EB0000070167D4F5EB000008 |
| 101324752823517185 | 3 | 0x0167D4F5EB0000060167FA536B0000000167FA536B000001 |
| 101283669313847313 | 3 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB000011 |
| 101283669313847322 | 3 | 0x0167D4F5EB0000180167D4F5EB0000190167D4F5EB00001A |
| 101283669313847305 | 4 | 0x0167D4F5EB0000060167D4F5EB0000070167D4F5EB0000080167D4F5EB000009 |
| 101324752823517186 | 4 | 0x0167D4F5EB0000060167FA536B0000000167FA536B0000010167FA536B000002 |
| 101283669313847314 | 4 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB0000110167D4F5EB000012 |
| 101283669313847323 | 4 | 0x0167D4F5EB0000180167D4F5EB0000190167D4F5EB00001A0167D4F5EB00001B |
| 101324752823517187 | 5 | 0x0167D4F5EB0000060167FA536B0000000167FA536B0000010167FA536B000002 |
| 101283669313847315 | 5 | 0x0167D4F5EB00000F0167D4F5EB0000100167D4F5EB0000110167D4F5EB000012 |
+--------------------+--------+--------------------------------------------------------------------+
17 rows in set (0.02 sec)

Zeros in the select_expr are place holder for later construct. Only the essential columns is selected for testing. The INTO dct_node_nest statement is also not included.

I've tried cast, convert to binary since VARBINARY is rejected by the parser.

Then use of UNHEX(HEX(hdct_node_adjc )) worked fine to get the 8 byte value, and append the subsequent node id - hdct_node_adjc.

Problem: only up to 64 characters (4 id's) is displayed. The 5th id is dropped in rows with hlevel of 5. Why?

MySQL temporary table hung in system lock (no replies)

Biju Philips — Wed, 29 Jan 2025 19:52:28 +0000

We have a data warehouse load process in MySQL 8.0.39 which gets hung in system lock while creating and loading temporary table. If we stop it and rerun it finishes in under a minute, otherwise it will be hung for hours till you kill the thread. We checked all the configurations and everything seems to be ok. Anyone any idea what could be causing this, what is it waiting for? There is no other process running on the server, the server is not out of any resource.

Create Temporary Table temp_svc AS
SELECT eps.EpisodeID,
eps.BusinessUnitID,
eps.PlanGroupID,
eph.EffectiveBeginDate,
eph.EffectiveEndDate
From stg_EpisodePropertiesHistory eph
INNER JOIN stg_EpisodeServices eps
ON eps.EpisodePropertySetID = eph.EpisodePropertySetID
AND eps.BusinessUnitID = eph.BusinessUnitID
And eps.ServiceTypeID = eph.ServiceTypeID
AND eph.IsInvalid = 0
Where eps.GroupResponsibilityLevel = 'Primary';
Tried checking all the configs, indexes, stopped all other jobs, no change.

Table: stg_EpisodePropertiesHistory
PRIMARY KEY (`BI_ID`),
KEY `IX_stg_EpisodePropertiesHistory_BusinessUnitID` (`BusinessUnitID`),
KEY `IX_stg_EpisodePropertiesHistory_EpisodeID` (`EpisodeID`),
KEY `IX_stg_EpisodePropertiesHistory_EPSID_BID` (`EpisodePropertySetID`,`BusinessUnitID`,`ServiceTypeID`,`IsInValid`,`EffectiveBeginDate`,`EffectiveEndDate`)

Table: stg_EpisodeServices
PRIMARY KEY (`BI_ID`),
KEY `IX_stg_EpisodeServices_BusinessUnitID` (`BusinessUnitID`),
KEY `IX_stg_EpisodeServices_EpisodeID` (`EpisodeID`),
KEY `IX_stg_EpisodeServices_EpisodePropertySetID` (`EpisodePropertySetID`),
KEY `IX_stg_EpisodeServices_ServiceTypeID` (`ServiceTypeID`)

When does Stored Procedure execution plan get determined? (1 reply)

Robert Rothkopf — Thu, 21 Nov 2024 20:07:32 +0000

In SQL Server, Stored Procedure (SP) execution plans are saved at compile time and used whenever the SP is invoked, even if SP parameters in subsequent calls would make a different execution plan more efficient. Using WITH RECOMPILE forces new plans to be considered with each call.

Per MySQL docs, there is no equivalent "with recompile".

So my question is: "does MySQL reevaluate the execution plans within an SP each time it is called, or are they saved when the SP is first loaded (or first called) and always used regardless of new and changing parameters?"

I've searched the docs, Web and forum and can't find info on this.

JSON_TABLE processing (no replies)

DK Singh — Fri, 04 Oct 2024 03:54:51 +0000

SET @json_data = '{"PhCity":["Houston","Brooklyn","New york","Memphis","Hytxsville"]}';

SET @phcity_list = (
SELECT GROUP_CONCAT(CONCAT("'", value, "'"))
FROM JSON_TABLE(@json_data, '$.PhCity[*]' COLUMNS (value TEXT PATH '$')) AS jt
);
select @phcity_list;

this is my json and want to get @phcity_list output in 'Houston','Brooklyn','Memphis' so that I can use it in my query like 'select * from MyTable where City in(@phcity_list ) limit 100'. But currently select @phcity_list; returns me result like '\'HOUSTON\',\'FAR ROCKAWAY\',\'WAUSAU\',\'KINGSTREE\',\'ORANGE CITY\'' so, due to '\' no result(s) able to get. Looking a genuine solution for the same. Can't use table variable because it gives error that table variable can't be reopen. There are several uses of this json in a single stored procedure

'IF' does not work on mysql workbench (1 reply)

Adam S — Mon, 19 Aug 2024 13:57:41 +0000

I am new on mysql and trying to become a data analyst. I am trying to use the 'IF' function but it keeps giving me an error. I couldn't find a way to fix it. Please help me, thank you!

1064 error when create a stored procedure (no replies)

goldli zhang — Sun, 11 Aug 2024 03:36:35 +0000

I have defined 2 sp. one, named 'prc_emp_has_24h', success created . and another one, 'prc_filter_block_emp' created failed. the error is

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'out has24h, out xStartTime, out xEndTime);

sp 'prc_emp_has_24h' code likes
```
CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_ai2soft_emp_has_24h`(IN empNo VARCHAR(24), OUT has24h int(4), OUT startTime DATETIME(6), OUT endTime DATETIME(6))
BEGIN
DECLARE ret INT;

...

IF ret > 0 THEN
set has24h = 1;

...
ELSE
set has24h = 0;
SET startTime = null;
SET endTime = null;
END if;

END
```

and sp 'prc_filter_block_emp' code likes
```

CREATE PROCEDURE prc_filter_block_emp(destDay DATETIME(6))
BEGIN
DECLARE has24h int(4);
DECLARE xStartTime DATETIME(6);
DECLARE xEndTime DATETIME(6);
DECLARE done INT DEFAULT FALSE;

DECLARE cursor_emp CURSOR FOR
SELECT r.pin, r.`name`, r.inorout, r.ttime -- , r.event_point_name
FROM acc_records r
INNER JOIN (
SELECT id, ROW_NUMBER() OVER(PARTITION BY pin ORDER BY ttime DESC) xindex
FROM acc_records
WHERE TO_DAYS(ttime) = TO_DAYS(destDay)
and event_no=0
) src
on r.id = src.id
where src.xindex=1 and r.inorout = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cursor_emp;
read_emp_loop: LOOP
FETCH cursor_emp INTO empNo, empName, isInOut, ttime;

IF done THEN
LEAVE read_emp_loop;
ELSE
call prc_ai2soft_emp_has_24h(empNo, out has24h, out xStartTime, out xEndTime);

IF has24h THEN -- 如果人员有24门禁权限
IF xStartTime = null AND xEndTime = NULL THEN
CONTINUE;

if xStartTime <= ttime and ttime <= xEndTime THEN
CONTINUE;
END IF;
END IF;
END LOOP read_emp_loop;
CLOSE cursor_emp;
END
```

what is the problem?

Need procedure help to insert entries to new table from two tables (no replies)

PK S — Fri, 05 Jul 2024 18:12:50 +0000

HI team,
I am new to procedures i have a requirement and not sure how to start to achieve it.
I have 3 tables
Table_A, Table_B, Table_C

need to insert entries to Table_A by joining Tble_B and Table_C on bellow conditions
1. all records from table_C which are not in table_B
2. all records form table_B
3. If same records already present in table_A then dont insert same records from Table_B or Table_C
I need to write a stored procedure in oracle for this requirement. can some one help on this plz

Thanks
sk

MySQL 8.0.28 - Call Stored Procedure stuck after dropping and re-creating (no replies)

Amol Gupta — Tue, 20 Feb 2024 14:55:20 +0000

Hi
I am facing an issue after upgrading MySQL 5.7 to MySQL 8.0.28 whenever I am trying to edit stored procedure on new version MySQL 8.0.28.

As soon as I drop and re-create stored procedure , the concurrent connections on same stored procedure are stuck and in performance schema in event_Waits_history shows following events

wait/synch/mutex/innodb/lock_sys_page_mutex

I am using dbeaver tool to persist changes in stored procedure.

MySQL-JavaScript: Stored programs (functions and procedures) in JavaScript inside MySQL (no replies)

Edwin Desouza — Fri, 15 Dec 2023 22:54:16 +0000

MySQL-JavaScript: Stored programs (functions and procedures) in JavaScript inside MySQL
- https://blogs.oracle.com/mysql/post/introducing-javascript-support-in-mysql

Find all procs that inserted/updated data at least once against AWS RDS (no replies)

M Z — Thu, 15 Jun 2023 20:09:17 +0000

Hi! Perhaps it is not the right forum and I greatly appreciate in advance if you point me in the right direction or relocate my question.

Background:
We made a problem a while ago with making an AWS MySQL RDS Replica writable so we can run some jobs to update some tables on Replica with calculated results without sending the calculation results to Master(or Source) to be replicated back to Replica. This is how we temporarily reduced the workload from Master.

Question:
I need to find out all stored procs that are doing inserts/updates to the Replica. The issue is we do not have a dedicated set of procs just for Replicas - we create all procs on the Master and then the procs are replicated to the Replicas.
Is there a system view I can query for all DML operations that are not SELECT on Replicas?
Is there a view to see which procs execute on the Replicas as oppose to being dormant?

Many thanks in advance,

Create Procedure Generating Errors (no replies)

Subramanian Mayuranathan — Sun, 30 Apr 2023 07:09:13 +0000

Hi All,

I have just started with MySQL. Trying to create a SP like so. But unable as MySQL throwing many errors

CREATE PROCEDURE IF NOT EXISTS db_dentalclinic.sp_AddTreatmentRecord IN (appointmentid int, natureoftreatment varchar, treatmentrendered VARCHAR, toothno tinyint, treatmentcomplete text, treatmentdate date, OUT Saved int)

DELIMITER //

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
select 0 into Saved;
ROLLBACK;
RESIGNAL;
END;
BEGIN

START TRANSACTION;
insert into tblTreatments (appointmentid, natureoftreatment, toothnumber, treatmentcomplete, treatmentdate)
VALUES
(appointmentid, 'Root Canal Treatment', toothnumber, 1, CURRENT_DATE());

insert into tblTreatmentHistory (treatmentid, treatmentrendered, posttreatmentadvice, treatmentdate, treatmentcomplete, followupdate, materialsused)
VALUES
(LAST_INSERT_ID(), treatmentrendered, posttreatmentadvice, CURRENT_DATE(), 1, CURRENT_DATE(), materialused);

select LAST_INSERT_ID into Saved;
COMMIT;

END//
DELIMITER ;

SP runs in Workbench but when called through command line does not behave as expected (no replies)

John G — Tue, 07 Feb 2023 19:38:10 +0000

I have a SP that works fine when called in Workbench. However, when I call it through command line only certain portions of the SP are executed. The INSERT lines are ignored. I have never seen this behavior before. Any thoughts? Again, works fine when called in workbench. I do not believe it is a permissions issue. I am not getting any syntax errors either. The referenced account does other inserts inside this db. The inserted columns are just varchar(12) and usually about 1000 rows. Real simple.

CREATE DEFINER=`importer`@`%` PROCEDURE `client_stage_step1`()
BEGIN

/* In some client files such as bogejt, givens, sniderm, a blank label is created due to a starting comment line before the first actual data line. This removes this line. */
DELETE FROM client_stage
WHERE Label = '';

Truncate client_stage_hold;

/* This is the portion not working */

INSERT INTO client_stage_hold (portfoliocodefile,label,labelvalue)
select distinct a.portfoliocodefile, 'vehicle' as label, 'Wrap' as labelvalue from client_stage a
where a.portfoliocode IN
(select portfoliocode from groupings where groupcode='totaum.grp') AND a.portfoliocode IN (select portfoliocode from groupings where groupcode='wrapacct.grp');

INSERT INTO client_stage_hold (portfoliocodefile,label,labelvalue)
select distinct a.portfoliocodefile, 'vehicle' as label, 'SMA' as labelvalue from client_stage a
where a.portfoliocode IN
(select portfoliocode from groupings where groupcode='totaum.grp') AND NOT a.portfoliocode IN (select portfoliocode from groupings where groupcode='wrapacct.grp');

/* END: This is the portion not working */

INSERT INTO client_stage (portfoliocodefile,label,labelvalue)
SELECT * from client_stage_hold;

Insert into client_stage_hold values('test','test','test');

Truncate client_stage_hold;

UPDATE client_stage
SET portfoliocode = TRIM(trailing '.cli' FROM portfoliocodefile)
WHERE portfoliocodefile = portfoliocodefile;
END

Stored Procedure with date and month input (no replies)

Ramakant Magade — Sun, 05 Feb 2023 07:17:05 +0000

Hello everyone, I have a table given below and now i want to
1.create a stored procedure that takes month and year as input and returns the orderNumber,status as output
2. Write a stored procedure to insert a record into the cancellations table for all cancelled orders.
Since I'm new learner so if anyone can help me with this then thank you for your help.

CREATE TABLE `orders` (
`orderNumber` int(11) NOT NULL,
`orderDate` date NOT NULL,
`requiredDate` date NOT NULL,
`shippedDate` date DEFAULT NULL,
`status` varchar(15) NOT NULL,
`comments` text DEFAULT NULL,
`customerNumber` int(11) NOT NULL,
PRIMARY KEY (`orderNumber`),
KEY `customerNumber` (`customerNumber`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ;

INSERT INTO `orders` VALUES (10100,'2003-01-06','2003-01-13','2003-01-10','Shipped',NULL,363),(10101,'2003-01-09','2003-01-18','2003-01-11','Shipped','Check on availability.',128),(10102,'2003-01-10','2003-01-18','2003-01-14','Shipped',NULL,181),(10103,'2003-01-29','2003-02-07','2003-02-02','Shipped',NULL,121),(10104,'2003-01-31','2003-02-09','2003-02-01','Shipped',NULL,141),(10105,'2003-02-11','2003-02-21','2003-02-12','Shipped',NULL,145),(10106,'2003-02-17','2003-02-24','2003-02-21','Shipped',NULL,278),(10107,'2003-02-24','2003-03-03','2003-02-26','Shipped','Difficult to negotiate with customer. We need more marketing materials',131),(10108,'2003-03-03','2003-03-12','2003-03-08','Shipped',NULL,385),(10109,'2003-03-10','2003-03-19','2003-03-11','Shipped','Customer requested that FedEx Ground is used for this shipping',486),(10110,'2003-03-18','2003-03-24','2003-03-20','Shipped',NULL,187),(10111,'2003-03-25','2003-03-31','2003-03-30','Shipped',NULL,129),(10112,'2003-03-24','2003-04-03','2003-03-29','Shipped','Customer requested that ad materials (such as posters, pamphlets) be included in the shippment',144),(10113,'2003-03-26','2003-04-02','2003-03-27','Shipped',NULL,124),(10114,'2003-04-01','2003-04-07','2003-04-02','Shipped',NULL,172),(10115,'2003-04-04','2003-04-12','2003-04-07','Shipped',NULL,424),(10116,'2003-04-11','2003-04-19','2003-04-13','Shipped',NULL,381),(10117,'2003-04-16','2003-04-24','2003-04-17','Shipped',NULL,148),(10118,'2003-04-21','2003-04-29','2003-04-26','Shipped','Customer has worked with some of our vendors in the past and is aware of their MSRP',216),(10119,'2003-04-28','2003-05-05','2003-05-02','Shipped',NULL,382),(10120,'2003-04-29','2003-05-08','2003-05-01','Shipped',NULL,114),(10121,'2003-05-07','2003-05-13','2003-05-13','Shipped',NULL,353),(10122,'2003-05-08','2003-05-16','2003-05-13','Shipped',NULL,350),(10123,'2003-05-20','2003-05-29','2003-05-22','Shipped',NULL,103),(10124,'2003-05-21','2003-05-29','2003-05-25','Shipped','Customer very concerned about the exact color of the models. There is high risk that he may dispute the order because there is a slight color mismatch',112),(10125,'2003-05-21','2003-05-27','2003-05-24','Shipped',NULL,114),(10126,'2003-05-28','2003-06-07','2003-06-02','Shipped',NULL,458),(10127,'2003-06-03','2003-06-09','2003-06-06','Shipped','Customer requested special shippment. The instructions were passed along to the warehouse',151),(10128,'2003-06-06','2003-06-12','2003-06-11','Shipped',NULL,141),(10129,'2003-06-12','2003-06-18','2003-06-14','Shipped',NULL,324),(10130,'2003-06-16','2003-06-24','2003-06-21','Shipped',NULL,198),(10131,'2003-06-16','2003-06-25','2003-06-21','Shipped',NULL,447),(10132,'2003-06-25','2003-07-01','2003-06-28','Shipped',NULL,323),(10133,'2003-06-27','2003-07-04','2003-07-03','Shipped',NULL,141),(10134,'2003-07-01','2003-07-10','2003-07-05','Shipped',NULL,250),(10135,'2003-07-02','2003-07-12','2003-07-03','Shipped',NULL,124),(10136,'2003-07-04','2003-07-14','2003-07-06','Shipped','Customer is interested in buying more Ferrari models',242),(10137,'2003-07-10','2003-07-20','2003-07-14','Shipped',NULL,353),(10138,'2003-07-07','2003-07-16','2003-07-13','Shipped',NULL,496),(10139,'2003-07-16','2003-07-23','2003-07-21','Shipped',NULL,282),(10140,'2003-07-24','2003-08-02','2003-07-30','Shipped',NULL,161),(10141,'2003-08-01','2003-08-09','2003-08-04','Shipped',NULL,334),(10142,'2003-08-08','2003-08-16','2003-08-13','Shipped',NULL,124),(10143,'2003-08-10','2003-08-18','2003-08-12','Shipped','Can we deliver the new Ford Mustang models by end-of-quarter?',320),(10144,'2003-08-13','2003-08-21','2003-08-14','Shipped',NULL,381),(10145,'2003-08-25','2003-09-02','2003-08-31','Shipped',NULL,205),(10146,'2003-09-03','2003-09-13','2003-09-06','Shipped',NULL,447),(10147,'2003-09-05','2003-09-12','2003-09-09','Shipped',NULL,379),(10148,'2003-09-11','2003-09-21','2003-09-15','Shipped','They want to reevaluate their terms agreement with Finance.',276),(10149,'2003-09-12','2003-09-18','2003-09-17','Shipped',NULL,487),(10150,'2003-09-19','2003-09-27','2003-09-21','Shipped','They want to reevaluate their terms agreement with Finance.',148),(10151,'2003-09-21','2003-09-30','2003-09-24','Shipped',NULL,311),(10152,'2003-09-25','2003-10-03','2003-10-01','Shipped',NULL,333),(10153,'2003-09-28','2003-10-05','2003-10-03','Shipped',NULL,141),(10154,'2003-10-02','2003-10-12','2003-10-08','Shipped',NULL,219),(10155,'2003-10-06','2003-10-13','2003-10-07','Shipped',NULL,186),(10156,'2003-10-08','2003-10-17','2003-10-11','Shipped',NULL,141),(10157,'2003-10-09','2003-10-15','2003-10-14','Shipped',NULL,473),(10158,'2003-10-10','2003-10-18','2003-10-15','Shipped',NULL,121),(10159,'2003-10-10','2003-10-19','2003-10-16','Shipped',NULL,321),(10160,'2003-10-11','2003-10-17','2003-10-17','Shipped',NULL,347),(10161,'2003-10-17','2003-10-25','2003-10-20','Shipped',NULL,227),(10162,'2003-10-18','2003-10-26','2003-10-19','Shipped',NULL,321),(10163,'2003-10-20','2003-10-27','2003-10-24','Shipped',NULL,424),(10164,'2003-10-21','2003-10-30','2003-10-23','Resolved','This order was disputed, but resolved on 11/1/2003; Customer doesn\'t like the colors and precision of the models.',452),(10165,'2003-10-22','2003-10-31','2003-12-26','Shipped','This order was on hold because customers\'s credit limit had been exceeded. Order will ship when payment is received',148),(10166,'2003-10-21','2003-10-30','2003-10-27','Shipped',NULL,462),(10167,'2003-10-23','2003-10-30',NULL,'Cancelled','Customer called to cancel. The warehouse was notified in time and the order didn\'t ship. They have a new VP of Sales and are shifting their sales model. Our VP of Sales should contact them.',448),(10168,'2003-10-28','2003-11-03','2003-11-01','Shipped',NULL,161),(10169,'2003-11-04','2003-11-14','2003-11-09','Shipped',NULL,276),(10170,'2003-11-04','2003-11-12','2003-11-07','Shipped',NULL,452),(10171,'2003-11-05','2003-11-13','2003-11-07','Shipped',NULL,233),(10172,'2003-11-05','2003-11-14','2003-11-11','Shipped',NULL,175),(10173,'2003-11-05','2003-11-15','2003-11-09','Shipped','Cautious optimism. We have happy customers here, if we can keep them well stocked. I need all the information I can get on the planned shippments of Porches',278),(10174,'2003-11-06','2003-11-15','2003-11-10','Shipped',NULL,333),(10175,'2003-11-06','2003-11-14','2003-11-09','Shipped',NULL,324),(10176,'2003-11-06','2003-11-15','2003-11-12','Shipped',NULL,386),(10177,'2003-11-07','2003-11-17','2003-11-12','Shipped',NULL,344),(10178,'2003-11-08','2003-11-16','2003-11-10','Shipped','Custom shipping instructions sent to warehouse',242),(10179,'2003-11-11','2003-11-17','2003-11-13','Cancelled','Customer cancelled due to urgent budgeting issues. Must be cautious when dealing with them in the future. Since order shipped already we must discuss who would cover the shipping charges.',496),(10180,'2003-11-11','2003-11-19','2003-11-14','Shipped',NULL,171),(10181,'2003-11-12','2003-11-19','2003-11-15','Shipped',NULL,167),(10182,'2003-11-12','2003-11-21','2003-11-18','Shipped',NULL,124),(10183,'2003-11-13','2003-11-22','2003-11-15','Shipped','We need to keep in close contact with their Marketing VP. He is the decision maker for all their purchases.',339),(10184,'2003-11-14','2003-11-22','2003-11-20','Shipped',NULL,484),(10185,'2003-11-14','2003-11-21','2003-11-20','Shipped',NULL,320),(10186,'2003-11-14','2003-11-20','2003-11-18','Shipped','They want to reevaluate their terms agreement with the VP of Sales',489),(10187,'2003-11-15','2003-11-24','2003-11-16','Shipped',NULL,211),(10188,'2003-11-18','2003-11-26','2003-11-24','Shipped',NULL,167),(10189,'2003-11-18','2003-11-25','2003-11-24','Shipped','They want to reevaluate their terms agreement with Finance.',205),(10190,'2003-11-19','2003-11-29','2003-11-20','Shipped',NULL,141),(10191,'2003-11-20','2003-11-30','2003-11-24','Shipped','We must be cautions with this customer. Their VP of Sales resigned. Company may be heading down.',259),(10192,'2003-11-20','2003-11-29','2003-11-25','Shipped',NULL,363),(10193,'2003-11-21','2003-11-28','2003-11-27','Shipped',NULL,471),(10194,'2003-11-25','2003-12-02','2003-11-26','Shipped',NULL,146),(10195,'2003-11-25','2003-12-01','2003-11-28','Shipped',NULL,319),(10196,'2003-11-26','2003-12-03','2003-12-01','Shipped',NULL,455),(10197,'2003-11-26','2003-12-02','2003-12-01','Shipped','Customer inquired about remote controlled models and gold models.',216),(10198,'2003-11-27','2003-12-06','2003-12-03','Shipped',NULL,385),(10199,'2003-12-01','2003-12-10','2003-12-06','Shipped',NULL,475),(10200,'2003-12-01','2003-12-09','2003-12-06','Shipped',NULL,211),(10201,'2003-12-01','2003-12-11','2003-12-02','Shipped',NULL,129),(10202,'2003-12-02','2003-12-09','2003-12-06','Shipped',NULL,357),(10203,'2003-12-02','2003-12-11','2003-12-07','Shipped',NULL,141),(10204,'2003-12-02','2003-12-10','2003-12-04','Shipped',NULL,151),(10205,'2003-12-03','2003-12-09','2003-12-07','Shipped',' I need all the information I can get on our competitors.',141),(10206,'2003-12-05','2003-12-13','2003-12-08','Shipped','Can we renegotiate this one?',202),(10207,'2003-12-09','2003-12-17','2003-12-11','Shipped','Check on availability.',495),(10208,'2004-01-02','2004-01-11','2004-01-04','Shipped',NULL,146),(10209,'2004-01-09','2004-01-15','2004-01-12','Shipped',NULL,347),(10210,'2004-01-12','2004-01-22','2004-01-20','Shipped',NULL,177),(10211,'2004-01-15','2004-01-25','2004-01-18','Shipped',NULL,406),(10212,'2004-01-16','2004-01-24','2004-01-18','Shipped',NULL,141),(10213,'2004-01-22','2004-01-28','2004-01-27','Shipped','Difficult to negotiate with customer. We need more marketing materials',489),(10214,'2004-01-26','2004-02-04','2004-01-29','Shipped',NULL,458),(10215,'2004-01-29','2004-02-08','2004-02-01','Shipped','Customer requested that FedEx Ground is used for this shipping',475),(10216,'2004-02-02','2004-02-10','2004-02-04','Shipped',NULL,256),(10217,'2004-02-04','2004-02-14','2004-02-06','Shipped',NULL,166),(10218,'2004-02-09','2004-02-16','2004-02-11','Shipped','Customer requested that ad materials (such as posters, pamphlets) be included in the shippment',473),(10219,'2004-02-10','2004-02-17','2004-02-12','Shipped',NULL,487),(10220,'2004-02-12','2004-02-19','2004-02-16','Shipped',NULL,189),(10221,'2004-02-18','2004-02-26','2004-02-19','Shipped',NULL,314),(10222,'2004-02-19','2004-02-27','2004-02-20','Shipped',NULL,239),(10223,'2004-02-20','2004-02-29','2004-02-24','Shipped',NULL,114),(10224,'2004-02-21','2004-03-02','2004-02-26','Shipped','Customer has worked with some of our vendors in the past and is aware of their MSRP',171),(10225,'2004-02-22','2004-03-01','2004-02-24','Shipped',NULL,298),(10226,'2004-02-26','2004-03-06','2004-03-02','Shipped',NULL,239),(10227,'2004-03-02','2004-03-12','2004-03-08','Shipped',NULL,146),(10228,'2004-03-10','2004-03-18','2004-03-13','Shipped',NULL,173),(10229,'2004-03-11','2004-03-20','2004-03-12','Shipped',NULL,124),(10230,'2004-03-15','2004-03-24','2004-03-20','Shipped','Customer very concerned about the exact color of the models. There is high risk that he may dispute the order because there is a slight color mismatch',128),(10231,'2004-03-19','2004-03-26','2004-03-25','Shipped',NULL,344),(10232,'2004-03-20','2004-03-30','2004-03-25','Shipped',NULL,240),(10233,'2004-03-29','2004-04-04','2004-04-02','Shipped','Customer requested special shippment. The instructions were passed along to the warehouse',328),(10234,'2004-03-30','2004-04-05','2004-04-02','Shipped',NULL,412),(10235,'2004-04-02','2004-04-12','2004-04-06','Shipped',NULL,260),(10236,'2004-04-03','2004-04-11','2004-04-08','Shipped',NULL,486),(10237,'2004-04-05','2004-04-12','2004-04-10','Shipped',NULL,181),(10238,'2004-04-09','2004-04-16','2004-04-10','Shipped',NULL,145),(10239,'2004-04-12','2004-04-21','2004-04-17','Shipped',NULL,311),(10240,'2004-04-13','2004-04-20','2004-04-20','Shipped',NULL,177),(10241,'2004-04-13','2004-04-20','2004-04-19','Shipped',NULL,209),(10242,'2004-04-20','2004-04-28','2004-04-25','Shipped','Customer is interested in buying more Ferrari models',456),(10243,'2004-04-26','2004-05-03','2004-04-28','Shipped',NULL,495),(10244,'2004-04-29','2004-05-09','2004-05-04','Shipped',NULL,141),(10245,'2004-05-04','2004-05-12','2004-05-09','Shipped',NULL,455),(10246,'2004-05-05','2004-05-13','2004-05-06','Shipped',NULL,141),(10247,'2004-05-05','2004-05-11','2004-05-08','Shipped',NULL,334),(10248,'2004-05-07','2004-05-14',NULL,'Cancelled','Order was mistakenly placed. The warehouse noticed the lack of documentation.',131),(10249,'2004-05-08','2004-05-17','2004-05-11','Shipped','Can we deliver the new Ford Mustang models by end-of-quarter?',173),(10250,'2004-05-11','2004-05-19','2004-05-15','Shipped',NULL,450),(10251,'2004-05-18','2004-05-24','2004-05-24','Shipped',NULL,328),(10252,'2004-05-26','2004-06-04','2004-05-29','Shipped',NULL,406),(10253,'2004-06-01','2004-06-09','2004-06-02','Cancelled','Customer disputed the order and we agreed to cancel it. We must be more cautions with this customer going forward, since they are very hard to please. We must cover the shipping fees.',201),(10254,'2004-06-03','2004-06-13','2004-06-04','Shipped','Customer requested that DHL is used for this shipping',323),(10255,'2004-06-04','2004-06-12','2004-06-09','Shipped',NULL,209),(10256,'2004-06-08','2004-06-16','2004-06-10','Shipped',NULL,145),(10257,'2004-06-14','2004-06-24','2004-06-15','Shipped',NULL,450),(10258,'2004-06-15','2004-06-25','2004-06-23','Shipped',NULL,398),(10259,'2004-06-15','2004-06-22','2004-06-17','Shipped',NULL,166),(10260,'2004-06-16','2004-06-22',NULL,'Cancelled','Customer heard complaints from their customers and called to cancel this order. Will notify the Sales Manager.',357),(10261,'2004-06-17','2004-06-25','2004-06-22','Shipped',NULL,233),(10262,'2004-06-24','2004-07-01',NULL,'Cancelled','This customer found a better offer from one of our competitors. Will call back to renegotiate.',141),(10263,'2004-06-28','2004-07-04','2004-07-02','Shipped',NULL,175),(10264,'2004-06-30','2004-07-06','2004-07-01','Shipped','Customer will send a truck to our local warehouse on 7/1/2004',362),(10265,'2004-07-02','2004-07-09','2004-07-07','Shipped',NULL,471),(10266,'2004-07-06','2004-07-14','2004-07-10','Shipped',NULL,386),(10267,'2004-07-07','2004-07-17','2004-07-09','Shipped',NULL,151),(10268,'2004-07-12','2004-07-18','2004-07-14','Shipped',NULL,412),(10269,'2004-07-16','2004-07-22','2004-07-18','Shipped',NULL,382),(10270,'2004-07-19','2004-07-27','2004-07-24','Shipped','Can we renegotiate this one?',282),(10271,'2004-07-20','2004-07-29','2004-07-23','Shipped',NULL,124),(10272,'2004-07-20','2004-07-26','2004-07-22','Shipped',NULL,157),(10273,'2004-07-21','2004-07-28','2004-07-22','Shipped',NULL,314),(10274,'2004-07-21','2004-07-29','2004-07-22','Shipped',NULL,379),(10275,'2004-07-23','2004-08-02','2004-07-29','Shipped',NULL,119),(10276,'2004-08-02','2004-08-11','2004-08-08','Shipped',NULL,204),(10277,'2004-08-04','2004-08-12','2004-08-05','Shipped',NULL,148),(10278,'2004-08-06','2004-08-16','2004-08-09','Shipped',NULL,112),(10279,'2004-08-09','2004-08-19','2004-08-15','Shipped','Cautious optimism. We have happy customers here, if we can keep them well stocked. I need all the information I can get on the planned shippments of Porches',141),(10280,'2004-08-17','2004-08-27','2004-08-19','Shipped',NULL,249),(10281,'2004-08-19','2004-08-28','2004-08-23','Shipped',NULL,157),(10282,'2004-08-20','2004-08-26','2004-08-22','Shipped',NULL,124),(10283,'2004-08-20','2004-08-30','2004-08-23','Shipped',NULL,260),(10284,'2004-08-21','2004-08-29','2004-08-26','Shipped','Custom shipping instructions sent to warehouse',299),(10285,'2004-08-27','2004-09-04','2004-08-31','Shipped',NULL,286),(10286,'2004-08-28','2004-09-06','2004-09-01','Shipped',NULL,172),(10287,'2004-08-30','2004-09-06','2004-09-01','Shipped',NULL,298),(10288,'2004-09-01','2004-09-11','2004-09-05','Shipped',NULL,166),(10289,'2004-09-03','2004-09-13','2004-09-04','Shipped','We need to keep in close contact with their Marketing VP. He is the decision maker for all their purchases.',167),(10290,'2004-09-07','2004-09-15','2004-09-13','Shipped',NULL,198),(10291,'2004-09-08','2004-09-17','2004-09-14','Shipped',NULL,448),(10292,'2004-09-08','2004-09-18','2004-09-11','Shipped','They want to reevaluate their terms agreement with Finance.',131),(10293,'2004-09-09','2004-09-18','2004-09-14','Shipped',NULL,249),(10294,'2004-09-10','2004-09-17','2004-09-14','Shipped',NULL,204),(10295,'2004-09-10','2004-09-17','2004-09-14','Shipped','They want to reevaluate their terms agreement with Finance.',362),(10296,'2004-09-15','2004-09-22','2004-09-16','Shipped',NULL,415),(10297,'2004-09-16','2004-09-22','2004-09-21','Shipped','We must be cautions with this customer. Their VP of Sales resigned. Company may be heading down.',189),(10298,'2004-09-27','2004-10-05','2004-10-01','Shipped',NULL,103),(10299,'2004-09-30','2004-10-10','2004-10-01','Shipped',NULL,186),(10300,'2003-10-04','2003-10-13','2003-10-09','Shipped',NULL,128),(10301,'2003-10-05','2003-10-15','2003-10-08','Shipped',NULL,299),(10302,'2003-10-06','2003-10-16','2003-10-07','Shipped',NULL,201),(10303,'2004-10-06','2004-10-14','2004-10-09','Shipped','Customer inquired about remote controlled models and gold models.',484),(10304,'2004-10-11','2004-10-20','2004-10-17','Shipped',NULL,256),(10305,'2004-10-13','2004-10-22','2004-10-15','Shipped','Check on availability.',286),(10306,'2004-10-14','2004-10-21','2004-10-17','Shipped',NULL,187),(10307,'2004-10-14','2004-10-23','2004-10-20','Shipped',NULL,339),(10308,'2004-10-15','2004-10-24','2004-10-20','Shipped','Customer requested that FedEx Ground is used for this shipping',319),(10309,'2004-10-15','2004-10-24','2004-10-18','Shipped',NULL,121),(10310,'2004-10-16','2004-10-24','2004-10-18','Shipped',NULL,259),(10311,'2004-10-16','2004-10-23','2004-10-20','Shipped','Difficult to negotiate with customer. We need more marketing materials',141),(10312,'2004-10-21','2004-10-27','2004-10-23','Shipped',NULL,124),(10313,'2004-10-22','2004-10-28','2004-10-25','Shipped','Customer requested that FedEx Ground is used for this shipping',202),(10314,'2004-10-22','2004-11-01','2004-10-23','Shipped',NULL,227),(10315,'2004-10-29','2004-11-08','2004-10-30','Shipped',NULL,119),(10316,'2004-11-01','2004-11-09','2004-11-07','Shipped','Customer requested that ad materials (such as posters, pamphlets) be included in the shippment',240),(10317,'2004-11-02','2004-11-12','2004-11-08','Shipped',NULL,161),(10318,'2004-11-02','2004-11-09','2004-11-07','Shipped',NULL,157),(10319,'2004-11-03','2004-11-11','2004-11-06','Shipped','Customer requested that DHL is used for this shipping',456),(10320,'2004-11-03','2004-11-13','2004-11-07','Shipped',NULL,144),(10321,'2004-11-04','2004-11-12','2004-11-07','Shipped',NULL,462),(10322,'2004-11-04','2004-11-12','2004-11-10','Shipped','Customer has worked with some of our vendors in the past and is aware of their MSRP',363),(10323,'2004-11-05','2004-11-12','2004-11-09','Shipped',NULL,128),(10324,'2004-11-05','2004-11-11','2004-11-08','Shipped',NULL,181),(10325,'2004-11-05','2004-11-13','2004-11-08','Shipped',NULL,121),(10326,'2004-11-09','2004-11-16','2004-11-10','Shipped',NULL,144),(10327,'2004-11-10','2004-11-19','2004-11-13','Resolved','Order was disputed and resolved on 12/1/04. The Sales Manager was involved. Customer claims the scales of the models don\'t match what was discussed.',145),(10328,'2004-11-12','2004-11-21','2004-11-18','Shipped','Customer very concerned about the exact color of the models. There is high risk that he may dispute the order because there is a slight color mismatch',278),(10329,'2004-11-15','2004-11-24','2004-11-16','Shipped',NULL,131),(10330,'2004-11-16','2004-11-25','2004-11-21','Shipped',NULL,385),(10331,'2004-11-17','2004-11-23','2004-11-23','Shipped','Customer requested special shippment. The instructions were passed along to the warehouse',486),(10332,'2004-11-17','2004-11-25','2004-11-18','Shipped',NULL,187),(10333,'2004-11-18','2004-11-27','2004-11-20','Shipped',NULL,129),(10334,'2004-11-19','2004-11-28',NULL,'On Hold','The outstaniding balance for this customer exceeds their credit limit. Order will be shipped when a payment is received.',144),(10335,'2004-11-19','2004-11-29','2004-11-23','Shipped',NULL,124),(10336,'2004-11-20','2004-11-26','2004-11-24','Shipped','Customer requested that DHL is used for this shipping',172),(10337,'2004-11-21','2004-11-30','2004-11-26','Shipped',NULL,424),(10338,'2004-11-22','2004-12-02','2004-11-27','Shipped',NULL,381),(10339,'2004-11-23','2004-11-30','2004-11-30','Shipped',NULL,398),(10340,'2004-11-24','2004-12-01','2004-11-25','Shipped','Customer is interested in buying more Ferrari models',216),(10341,'2004-11-24','2004-12-01','2004-11-29','Shipped',NULL,382),(10342,'2004-11-24','2004-12-01','2004-11-29','Shipped',NULL,114),(10343,'2004-11-24','2004-12-01','2004-11-26','Shipped',NULL,353),(10344,'2004-11-25','2004-12-02','2004-11-29','Shipped',NULL,350),(10345,'2004-11-25','2004-12-01','2004-11-26','Shipped',NULL,103),(10346,'2004-11-29','2004-12-05','2004-11-30','Shipped',NULL,112),(10347,'2004-11-29','2004-12-07','2004-11-30','Shipped','Can we deliver the new Ford Mustang models by end-of-quarter?',114),(10348,'2004-11-01','2004-11-08','2004-11-05','Shipped',NULL,458),(10349,'2004-12-01','2004-12-07','2004-12-03','Shipped',NULL,151),(10350,'2004-12-02','2004-12-08','2004-12-05','Shipped',NULL,141),(10351,'2004-12-03','2004-12-11','2004-12-07','Shipped',NULL,324),(10352,'2004-12-03','2004-12-12','2004-12-09','Shipped',NULL,198),(10353,'2004-12-04','2004-12-11','2004-12-05','Shipped',NULL,447),(10354,'2004-12-04','2004-12-10','2004-12-05','Shipped',NULL,323),(10355,'2004-12-07','2004-12-14','2004-12-13','Shipped',NULL,141),(10356,'2004-12-09','2004-12-15','2004-12-12','Shipped',NULL,250),(10357,'2004-12-10','2004-12-16','2004-12-14','Shipped',NULL,124),(10358,'2004-12-10','2004-12-16','2004-12-16','Shipped','Customer requested that DHL is used for this shipping',141),(10359,'2004-12-15','2004-12-23','2004-12-18','Shipped',NULL,353),(10360,'2004-12-16','2004-12-22','2004-12-18','Shipped',NULL,496),(10361,'2004-12-17','2004-12-24','2004-12-20','Shipped',NULL,282),(10362,'2005-01-05','2005-01-16','2005-01-10','Shipped',NULL,161),(10363,'2005-01-06','2005-01-12','2005-01-10','Shipped',NULL,334),(10364,'2005-01-06','2005-01-17','2005-01-09','Shipped',NULL,350),(10365,'2005-01-07','2005-01-18','2005-01-11','Shipped',NULL,320),(10366,'2005-01-10','2005-01-19','2005-01-12','Shipped',NULL,381),(10367,'2005-01-12','2005-01-21','2005-01-16','Resolved','This order was disputed and resolved on 2/1/2005. Customer claimed that container with shipment was damaged. FedEx\'s investigation proved this wrong.',205),(10368,'2005-01-19','2005-01-27','2005-01-24','Shipped','Can we renegotiate this one?',124),(10369,'2005-01-20','2005-01-28','2005-01-24','Shipped',NULL,379),(10370,'2005-01-20','2005-02-01','2005-01-25','Shipped',NULL,276),(10371,'2005-01-23','2005-02-03','2005-01-25','Shipped',NULL,124),(10372,'2005-01-26','2005-02-05','2005-01-28','Shipped',NULL,398),(10373,'2005-01-31','2005-02-08','2005-02-06','Shipped',NULL,311),(10374,'2005-02-02','2005-02-09','2005-02-03','Shipped',NULL,333),(10375,'2005-02-03','2005-02-10','2005-02-06','Shipped',NULL,119),(10376,'2005-02-08','2005-02-18','2005-02-13','Shipped',NULL,219),(10377,'2005-02-09','2005-02-21','2005-02-12','Shipped','Cautious optimism. We have happy customers here, if we can keep them well stocked. I need all the information I can get on the planned shippments of Porches',186),(10378,'2005-02-10','2005-02-18','2005-02-11','Shipped',NULL,141),(10379,'2005-02-10','2005-02-18','2005-02-11','Shipped',NULL,141),(10380,'2005-02-16','2005-02-24','2005-02-18','Shipped',NULL,141),(10381,'2005-02-17','2005-02-25','2005-02-18','Shipped',NULL,321),(10382,'2005-02-17','2005-02-23','2005-02-18','Shipped','Custom shipping instructions sent to warehouse',124),(10383,'2005-02-22','2005-03-02','2005-02-25','Shipped',NULL,141),(10384,'2005-02-23','2005-03-06','2005-02-27','Shipped',NULL,321),(10385,'2005-02-28','2005-03-09','2005-03-01','Shipped',NULL,124),(10386,'2005-03-01','2005-03-09','2005-03-06','Resolved','Disputed then Resolved on 3/15/2005. Customer doesn\'t like the craftsmaship of the models.',141),(10387,'2005-03-02','2005-03-09','2005-03-06','Shipped','We need to keep in close contact with their Marketing VP. He is the decision maker for all their purchases.',148),(10388,'2005-03-03','2005-03-11','2005-03-09','Shipped',NULL,462),(10389,'2005-03-03','2005-03-09','2005-03-08','Shipped',NULL,448),(10390,'2005-03-04','2005-03-11','2005-03-07','Shipped','They want to reevaluate their terms agreement with Finance.',124),(10391,'2005-03-09','2005-03-20','2005-03-15','Shipped',NULL,276),(10392,'2005-03-10','2005-03-18','2005-03-12','Shipped',NULL,452),(10393,'2005-03-11','2005-03-22','2005-03-14','Shipped','They want to reevaluate their terms agreement with Finance.',323),(10394,'2005-03-15','2005-03-25','2005-03-19','Shipped',NULL,141),(10395,'2005-03-17','2005-03-24','2005-03-23','Shipped','We must be cautions with this customer. Their VP of Sales resigned. Company may be heading down.',250),(10396,'2005-03-23','2005-04-02','2005-03-28','Shipped',NULL,124),(10397,'2005-03-28','2005-04-09','2005-04-01','Shipped',NULL,242),(10398,'2005-03-30','2005-04-09','2005-03-31','Shipped',NULL,353),(10399,'2005-04-01','2005-04-12','2005-04-03','Shipped',NULL,496),(10400,'2005-04-01','2005-04-11','2005-04-04','Shipped','Customer requested that DHL is used for this shipping',450),(10401,'2005-04-03','2005-04-14',NULL,'On Hold','Customer credit limit exceeded. Will ship when a payment is received.',328),(10402,'2005-04-07','2005-04-14','2005-04-12','Shipped',NULL,406),(10403,'2005-04-08','2005-04-18','2005-04-11','Shipped',NULL,201),(10404,'2005-04-08','2005-04-14','2005-04-11','Shipped',NULL,323),(10405,'2005-04-14','2005-04-24','2005-04-20','Shipped',NULL,209),(10406,'2005-04-15','2005-04-25','2005-04-21','Disputed','Customer claims container with shipment was damaged during shipping and some items were missing. I am talking to FedEx about this.',145),(10407,'2005-04-22','2005-05-04',NULL,'On Hold','Customer credit limit exceeded. Will ship when a payment is received.',450),(10408,'2005-04-22','2005-04-29','2005-04-27','Shipped',NULL,398),(10409,'2005-04-23','2005-05-05','2005-04-24','Shipped',NULL,166),(10410,'2005-04-29','2005-05-10','2005-04-30','Shipped',NULL,357),(10411,'2005-05-01','2005-05-08','2005-05-06','Shipped',NULL,233),(10412,'2005-05-03','2005-05-13','2005-05-05','Shipped',NULL,141),(10413,'2005-05-05','2005-05-14','2005-05-09','Shipped','Customer requested that DHL is used for this shipping',175),(10414,'2005-05-06','2005-05-13',NULL,'On Hold','Customer credit limit exceeded. Will ship when a payment is received.',362),(10415,'2005-05-09','2005-05-20','2005-05-12','Disputed','Customer claims the scales of the models don\'t match what was discussed. I keep all the paperwork though to prove otherwise',471),(10416,'2005-05-10','2005-05-16','2005-05-14','Shipped',NULL,386),(10417,'2005-05-13','2005-05-19','2005-05-19','Disputed','Customer doesn\'t like the colors and precision of the models.',141),(10418,'2005-05-16','2005-05-24','2005-05-20','Shipped',NULL,412),(10419,'2005-05-17','2005-05-28','2005-05-19','Shipped',NULL,382),(10420,'2005-05-29','2005-06-07',NULL,'In Process',NULL,282),(10421,'2005-05-29','2005-06-06',NULL,'In Process','Custom shipping instructions were sent to warehouse',124),(10422,'2005-05-30','2005-06-11',NULL,'In Process',NULL,157),(10423,'2005-05-30','2005-06-05',NULL,'In Process',NULL,314),(10424,'2005-05-31','2005-06-08',NULL,'In Process',NULL,141),(10425,'2005-05-31','2005-06-07',NULL,'In Process',NULL,119);

Continue Handler problem (no replies)

John Noble — Mon, 16 Jan 2023 19:13:20 +0000

Hi folks,

I have getting a problem with my continuehandler. It is setting the vFInished value to 1 too early.

Here is my code...

DECLARE CONTINUE HANDLER FOR NOT FOUND SET vfinished = 1;

SET vfinished = 0;

read_loop: LOOP
FETCH shopDetailCursor INTO vStockID;

IF vfinished = 1 THEN
LEAVE read_loop;
end if;

-- Get Stock Item Cost
-- Debug code shows vfinished = 0 at this point
SELECT cost INTO vCost FROM MS1_stock WHERE ID = vStockID;
-- Debug code shows vfinished = 1 at this point. How come ??

-- Do Other Stuff

END Loop read_loop;

Any ideas what might cause this ?

John

Row not being added. But pkey is getting incremented (1 reply)

John Noble — Tue, 20 Dec 2022 10:26:14 +0000

Hi folks,

My stored procedure (see below) is set to just add a new row. When I call it with test data from WorkBench it works fine. But when I call it from my .NET program nothing is getting updated. However, it does increment the pKey field in the table.

Any idea what might be causing this? Some sort of lock perhaps ?

John

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_remittancesInsert`(
IN p_vendorID varchar(7),
IN p_vendorName varchar(100),
IN p_address1 varchar(50),
IN p_address2 varchar(50),
IN p_town varchar(50),
IN p_postcode varchar(15),
IN p_documentNumber varchar(30),
IN p_ref1 varchar(30),
IN p_docType varchar(3),
IN p_docDate date,
IN p_payable decimal(14,2),
IN p_balance decimal(14,2),
IN p_remit decimal(14,2),
IN p_invoiceCurrency varchar(3),
IN p_paymentDate date
)
BEGIN

INSERT INTO remittances (vendorID, vendorName, address1, address2, town, postcode, documentNumber, ref1, docType, docDate, payable, balance, remit, invoiceCurrency, paymentDate)
VALUES (p_vendorID, p_vendorName, p_address1, p_address2, p_town, p_postcode, p_documentNumber, p_ref1, p_docType, p_docDate, p_payable, p_balance, p_remit, p_invoiceCurrency, p_paymentDate);

END

Create TEMPORARY Table using dynamic SQL (1 reply)

John Noble — Tue, 20 Dec 2022 10:40:51 +0000

Hi folks,

I am trying to create a Temporary table using variables

If I use:
CREATE TEMPORARY TABLE tempTable SELECT * FROM myTable;
it will work fine,

Except, I don't know the name of the table in advance. That is passed into the sproc.

So I need to use something more dynamic like

SET @vTableName= CONCAT("tableName", variableName);
SET @vSQL = CONCAT("SELECT * FROM ", @vTableName);
CREATE TEMPORARY TABLE tempTable @vSQL;

Except this wont compile.

Is there a way around this?

Thanks,

JOhn

1287 Setting user variables within expressions is deprecated and will be removed in a future release (no replies)

M Z — Wed, 19 Oct 2022 17:57:13 +0000

Hi! We use this technique to update ranking columns
SET category_rank = (SELECT @i := @i + 1)

Now we've been getting lots of these warnings:
1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression,

How can I change the technique to avoid the warning?

CREATE TABLE `categories` (
`client_id` int NOT NULL,
`category_id` int NOT NULL DEFAULT '0',
`category_rank` int DEFAULT NULL,
`index_low` int DEFAULT NULL,
`index_high` int DEFAULT NULL,
`category_color` varchar(45) DEFAULT NULL,
`category_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO categories(client_id, category_id, category_rank, index_low, index_high, category_color, category_name) VALUES
(100, 36, NULL, 0, 44, '#006830', 'A'),
(100, 37, NULL, 45, 110, '#FBB022', 'B'),
(100, 38, NULL, 111, 222, '#F15800', 'C'),
(100, 39, NULL, 223, 400, '#FE8011', 'D'),
(104, 1, NULL, 0, 50, '#47AB5B', 'E'),
(104, 2, NULL, 51, 100, '#47A1CB', 'F'),
(104, 3, NULL, 101, 200, '#475FCB', 'G'),
(104, 4, NULL, 201, 400, '#7147CB', 'H'),
(144, 21, NULL, 300, 6000, '#47AB5B', 'I'),
(158, 15, NULL, 59, 99, '#330000', 'J'),
(158, 23, NULL, 0, 11, NULL, 'K'),
(162, 16, NULL, 0, 50, '#47AB5B', 'L'),
(162, 17, NULL, 51, 100, '#47A1CB', 'M'),
(162, 18, NULL, 101, 200, '#475FCB', 'N'),
(162, 19, NULL, 201, 400, '#7147CB', 'O'),
(192, 40, NULL, 0, 44, '#006830', 'P'),
(192, 41, NULL, 45, 110, '#FBB022', 'Q'),
(192, 42, NULL, 111, 222, '#F15800', 'R');

Many thanks in advance

Understanding SIGINT, KILL, and DECLARE HANDLER (no replies)

Sam Sun — Wed, 12 Oct 2022 07:31:09 +0000

Hello,

I'm reading the documentation for MySQL 5.7 and I would like to know how to write a Stored Procedure that gracefully terminates when requested by the user.

It seems like CTRL-C or SIGINT only kills the statements on the client end, and the statements continue to execute server side.

How does the KILL command affect a stored procedure that is mid-transaction?
Does it trigger the DECLARE HANDLER? If so, with what SQLSTATE?

Thank you!

ORDER BY clause in stored procedure (no replies)

Ken Guiche — Sun, 09 Oct 2022 20:38:58 +0000

Hello, I am new to MySQL and mightily struggling with a simple store procedure.

The below compiles and runs fine with the expected results in Workbench:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN SORTORDER char(5))
BEGIN
SELECT * FROM mydictionary
order by case SORTORDER
when 'HWORD' then mydictionary.HeadWord
when 'RANK' then mydictionary.Rank
end;
END

However the same thing with additional ORDER BY conditions won't even compile:
.....
order by case SORTORDER
when 'HWORD' then mydictionary.HeadWord, mydictionary.Rank
when 'RANK' then mydictionary.Rank, mydictionary.HeadWord
.....

Workbench highlights the comma after 'mydictionary.HeadWord' as the error. What is the correct syntax, or you just cannot have more than one ORDER BY condition? I have a feeling that there is a simple solution, but after hours of reading manuals and looking for examples and I still cannot tell what I am missing. I greatly appreciate if anybody could put me out of the misery.

Stored Procedure inserting duplicate Rows (1 reply)

Derek McKinnon — Thu, 14 Jul 2022 17:41:39 +0000

I have just inherited a system from the previous developer who has left the company. We have a bug where very occasionally a stored procedure (shown below) will create two entries into a table instead of one.

His verbal report to me when leaving is that he suspects it has to do with Atomisation, which I do not know much about.

It does seem to come in waves. So this week, we had it happen 3 times within 30 inserts (all running within one cursor - see below). Prior to that it hadn't happened for several months - which is probably 5000 inserts.

I suspect I need to use a transaction. However I have very limited experience with Cursors, due to obsessively avoiding them in the past, and so am not sure how best to use a transaction with a cursor.

Here is the stored procedure. If you can see anything else that is wrong, please let me know.

DELIMITER $$

CREATE PROCEDURE `ProcessCSSPayment`(
IN ipClearingDocumentDate DATE,
IN ipChildID VARCHAR(7)
)
BEGIN
DECLARE vPaymentItemID INT;
DECLARE vFamilyIDNo VARCHAR(7);
DECLARE vChildId VARCHAR(7);
DECLARE vChildIdInteger INT;
DECLARE vDetail VARCHAR(120) DEFAULT "Direct Deposit CCS Payment";
DECLARE vShortDescription VARCHAR(1) DEFAULT "G";
DECLARE vClearingDocumentDate DATETIME;
DECLARE vClearingDocumentNumber VARCHAR(12);
DECLARE vPaymentFiscalYear VARCHAR(4);
DECLARE vAmount DECIMAL(12,2);
DECLARE vWEID INT;
DECLARE vWeekEnding DATE;
DECLARE vdr02ID INT;
DECLARE vDone INT DEFAULT 0;

DECLARE curPaymentItem CURSOR FOR
SELECT PH.ClearingDocumentNumber,
PH.ClearingDocumentDate,
PH.PaymentFiscalYear,
PI.id AS PaymentItemID,
PI.Amount,
CH.FamilyIDNo,
CH.childid,
CH.id AS childIdInteger,
WE.WEID,
WE.WeekEnd AS WeekEnding
FROM CCSPaymentItem PI
INNER JOIN CCSPayment PH
ON PI.PaymentID = PH.id
INNER JOIN CCSEnrolment ER
ON PI.EnrolmentID = ER.EnrolmentID
INNER JOIN children CH
ON ER.ChildID = CH.ID
LEFT JOIN weekendings WE
ON PI.SessionReportStartDate BETWEEN WE.WeekStart AND WE.WeekEnd
WHERE ISNULL(PI.dr02Id)
AND (ISNULL(ipClearingDocumentDate) OR PH.ClearingDocumentDate = ipClearingDocumentDate)
AND (ipChildID = "" OR CH.childId = ipChildID)
AND PI.EnrolmentID <> '';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = 1;

OPEN curPaymentItem;

PaymentItem_LOOP: LOOP
-- Fetch one record from CURSOR and set to some variable
-- (If not found then done will be set to 1 by continue handler)
FETCH curPaymentItem INTO vClearingDocumentNumber,
vClearingDocumentDate,
vPaymentFiscalYear,
vPaymentItemID,
vAmount,
vFamilyIDNo,
vChildId,
vChildIdInteger,
vWEID,
vWeekEnding;

IF vDone THEN
-- If done set to 1 then exit the loop else continue
LEAVE PaymentItem_LOOP;
END IF;

IF EXISTS ( SELECT id
FROM dr02
WHERE ChildIdInteger = vChildIdInteger
AND childId = vChildId
AND PayId = vClearingDocumentNumber
AND DATE = vClearingDocumentDate
AND WEID = vWEID
AND WeekEnd = vWeekEnding
AND AMOUNT = vAmount * -1
AND USER = "Admin"
) THEN

ITERATE PaymentItem_LOOP;
END IF;

CALL Drsub_DrJnl(vFamilyIDNo,
vDetail,
vShortDescription,
vClearingDocumentDate,
0,
vAmount * -1,
0,
0,
"Admin", -- UserName
vdr02ID);

UPDATE dr02
SET ref = ID,
ChildIdInteger = vChildIdInteger,
childId = vChildId,
PayId = vClearingDocumentNumber,
WEID = vWEID,
WeekEnd = vWeekEnding
WHERE ID = vdr02ID;

CALL Drsub_UpdateChildGovtAmounts(vWEID, vChildId);

UPDATE CCSPaymentItem
SET dr02Id = vdr02ID
WHERE ID = vPaymentItemID;

END LOOP PaymentItem_LOOP;

-- Closing the cursor
CLOSE curPaymentItem;

-- Calculate the CCSHourly
SET vWEID = (SELECT WE.WEID
FROM weekendings WE
WHERE WE.WeekStart = (SELECT MAX(SR.SessionReportStartDate)
FROM CCSSessionReport SR
WHERE NOT ISNULL(SR.FeeReductionAmount)));

UPDATE children CH
INNER JOIN dr02 D21
ON CH.childid = D21.childid
AND D21.WEID = vWEID
AND D21.T1 = "L"
INNER JOIN dr02 D22
ON CH.childid = D22.childid
AND D22.WEID = D21.WEID - 1
AND D22.T1 = D21.T1
SET CCSHourly = (IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) + IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0)) /
CASE WHEN ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) = 0
THEN 1
ELSE ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) END;

END$$

Stored Procedure to create table (6 replies)

Mike Demaris — Sat, 16 Jul 2022 02:34:07 +0000

Hello all,

I am attempting to create an SP that will take three params and create a table using the params as the schema.tableName.

The purpose is to create many tables, all the same fields, but the names must change based on the date. Before asking why, these tables are auto-generated and filled with raw machine data. We are moving the tables to test them in another database, but doing so manually takes a lot of time.

Here is the code I have so far, I have added the @ symbol in the PREPARE stmt because Mysql gave me an error if I didn't use it.

CREATE PROCEDURE `param_test01`(IN p_schema varchar(30), IN p_year char(4), IN p_month char(2))
BEGIN
DECLARE t_name varchar(50);
DECLARE k_name varchar(50);
DECLARE t_create text;

set k_name = concat('sqlt_data_1_', p_year, '_', p_month, 't_stampndx');

if length(p_month) < 2 then
set p_month = concat('0',p_month);
end if;

set t_name = concat(p_schema, '.', 'sqlt_data_1_', p_year, '_', p_month);
set t_create = concat('create table ', t_name, ' (
`tagid` int(11) NOT NULL DEFAULT ''0'',
`intvalue` bigint(20) DEFAULT NULL,
`floatvalue` double DEFAULT NULL,
`stringvalue` varchar(255) DEFAULT NULL,
`datevalue` datetime DEFAULT NULL,
`dataintegrity` int(11) DEFAULT NULL,
`t_stamp` bigint(20) NOT NULL DEFAULT ''0'',
PRIMARY KEY (`tagid`,`t_stamp`),
KEY k_name (`t_stamp`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;');

prepare stmt from @t_create;
execute stmt;
deallocate prepare stmt;
END

Any ideas?

Thank you

Passing TableName to a Stored Procedure (1 reply)

Clive Morris — Fri, 10 Jun 2022 16:09:18 +0000

I am trying to pass a table name to a stored procedure simple example code follows:

DELIMITER //
CREATE PROCEDURE myProcedure(IN tabName varchar(50))
BEGIN

SELECT tabName; -- Correctly outputs the expected value assigned to tabName

SELECT ClientID FROM tabName limit 100; -- Throws error

END //
DELIMITER ;

The first select statement displays the value of the variable as expected but the second select statement throws the error: Table 'mydatabase.tabName' doesn't exist

PHP mysql master detail part procedure (1 reply)

Jegatheesan S — Mon, 14 Mar 2022 17:18:13 +0000

I am developing a online shopping web site. While the enquiry is raised i want to insert the master data (name, address, email etc...) in the master table and multiple detail data (itemid, Qty, Rate etc..) in the detail table. I plan to use stored procedure because master id is stored in the detail table. When use in online if multiple people raise order at a same time then no problem in number dublication.

Error Code: 1172. Result consisted of more than one row INSIDE A FUNCTION (3 replies)

Nestor Pedraza — Mon, 14 Mar 2022 17:12:46 +0000

Hello everybody thanks you in advance. I’m getting this error message when I run a user defined function .
The function just read from a table the EURO exchange value for a certain date. There is a unique result for each date, but keep given me the message .
Here is the code

CREATE DEFINER=`myNestor`@`%` FUNCTION `EURO`(FEC DATE ) RETURNS decimal(11,2)
BEGIN
SET @TODAY=FEC;
SELECT EURO FROM DATA.IYM WHERE DOER=@HOY INTO @RESULT ;
RETURN @RESULT ;
END

Function problem (2 replies)

name name — Sat, 05 Mar 2022 15:49:20 +0000

Call function (n1)
How does one parameter pass in multiple strings
Achieve the effect of in ()

Procedure Random Partial Failure (1 reply)

David Sarvai — Fri, 04 Mar 2022 18:21:22 +0000

I have an event which kicks off a stored procedure at midnight to drop the oldest partition (180 Days old), and create a new partition (2 days into the future). This maintenance task randomly started failing last week (2/19) for 2 of the 3 tables it updates.

NOTE: I am not the author of this procedure.

MySQL Error Message:
2022-03-04T00:00:00.915128-05:00 64212 [ERROR] [MY-010045] [Server] Event Scheduler: [myuser@%][app.job_update_partitions] Error in list of partitions to DROP

The interesting thing is that the event/procedure drops the 3 partitions correctly, but only only creates the new partition on the first table. The second/third table do not get a new partition.

call_log - drops oldest partition
call_log - creates new partition

cc_agent_log - drops oldest partition
cc_agent_log - fails to create newest

cc_queue_log - drops oldest partition
cc_queue_log - fails to create newest

EVENT:
Delimiter $$
Create Event `Job_Update_Partitions`
On Schedule
Every 1 Day
Starts Curdate() + Interval '0:00' Hour_Minute
Do
Begin
call Update_Partitions('call_log');
call Update_Partitions('cc_agent_log');
call Update_Partitions('cc_queue_log');
End
$$
Delimiter ;

Procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `UPDATE_PARTITIONS`$$
CREATE PROCEDURE UPDATE_PARTITIONS(VAR_TABLE VARCHAR(25) )
BEGIN
DECLARE DAYSTOKEEP INT DEFAULT 180;
DECLARE CREATEINDAYS INT DEFAULT 2;

DECLARE MYDATE DATE;
DECLARE MYDATEPLUS1 DATE;
DECLARE MYDATEMINUS DATE;
DECLARE Y CHAR(2);
DECLARE M CHAR(2);
DECLARE D CHAR(2);
DECLARE DATE_STRING VARCHAR(100);

DECLARE PARTITION_NAME VARCHAR(100);
DECLARE NO_MORE_ROWS BOOLEAN;
DECLARE ROW_COUNT INT DEFAULT 0;
DECLARE NUM_ROWS INT DEFAULT 0;

SET MYDATE=DATE_ADD(CURDATE(), INTERVAL CREATEINDAYS DAY);
SET Y=DATE_FORMAT(MYDATE, '%y');
SET M=DATE_FORMAT(MYDATE, '%m');
SET D=DATE_FORMAT(MYDATE, '%d');
SET DATE_STRING=CONCAT('p', Y, M, D);

SET MYDATEPLUS1=DATE_ADD(CURDATE(), INTERVAL (CREATEINDAYS + 1) DAY);

SET @sql := CONCAT('ALTER TABLE ', VAR_TABLE, ' ADD PARTITION ( PARTITION ',
DATE_STRING, ' VALUES LESS THAN (',
TO_DAYS(MYDATEPLUS1),
'));
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET MYDATEMINUS=DATE_SUB(CURDATE(), INTERVAL DAYSTOKEEP DAY);
SET Y=DATE_FORMAT(MYDATEMINUS, '%y');
SET M=DATE_FORMAT(MYDATEMINUS, '%m');
SET D=DATE_FORMAT(MYDATEMINUS, '%d');
SET DATE_STRING=CONCAT('p', Y, M, D);

SET @sql := CONCAT('ALTER TABLE ', VAR_TABLE, ' DROP PARTITION ', DATE_STRING);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

I upgraded the mysql server from 8.0.21 to 8.0.26, which still has the problem.

My open_files_limit is way higher than the number of tables on the server.

SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+

Looking for suggestions/recommendations to troubleshoot/debug.

Thanks,
Dave

xml into store procedure (1 reply)

Elkin Fernando Ortiz — Thu, 03 Mar 2022 22:22:26 +0000

I need to process an xml parameter inside a store procedure for affect tables inside mysql. can someone help me with this case and provide examples? thanks a lot.
elkin, Medellin, colombia.

REGEXP_SUBSTR function missing (5 replies)

ian diddams — Thu, 17 Feb 2022 15:49:40 +0000

scenario: mysql community server 5.7
Centos 7

REGEXP_SUBSTR function missing

Our devs have reported that when trying to use REGEXP_SUBSTR function missing on a UAT db instance we have the function is missing.

The live equivalent (same OS & mysql version) does have this fucntion.

REGEXP_SUBSTR function missing is an "in built" mysql provision from what i can gather, but what doesnt appear clear is how it has "disappeared" recently from this UAT mysql instance, and how to get it back.

the UAT server was last patched Jan 18th 2022, the live server Dec 15th 2021.

Exact version are

UAT: 5.7.36
LIVE: 5.7.36

so it doesnt appear to be a mysql patch issue.

Anyway, ignoring for now how it happened, how do I get it back?

cheers!

Ian

stored procedure adds empty fields when creating new user (1 reply)

badr douah — Wed, 16 Feb 2022 16:47:28 +0000

Hi,
i have a stored procedure that im testing inside mysqlworkbench, im getting new user created successfly after executing the SP but with empty values for username email HashPass and SaltPass,
any ideas what will be causing this
Ps: the stored procedure works perfectly if i don't use concat

here is
CREATE DEFINER=`admin`@`%` PROCEDURE `CreateUser`(IN dbName VARCHAR(50),IN UserName varchar(250),IN Email varchar(250),IN HashPass varchar(250),IN SaltPass varchar(250))
BEGIN

SET @_dbName = dbName;

SET @query = CONCAT('INSERT INTO ',@_dbName, '.Users (UserName,Email,HashPass,SaltPass,CreationDate)
VALUES(UserName, Email, HashPass, SaltPass, NOW());');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

test
call ProjectsDB.CreateUser('kiwi', 'test', 'test@gmail.com', 'qsaaksjhaslja', 'jbkasakskajka');