0
votes
1answer
16 views

I cant figure out the error probably with the date field in this mysql stored procedure

This is my empDetails table I am trying to create a stored procedure on. mysql> describe empDetails; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
30 views

How to retrieve stored procedures from raw data

I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't ...
0
votes
0answers
13 views

MYSQL Stored Procedure Problem. Need Help

MySQL Stored procedure: Delimiter $$ DROP PROCEDURE IF EXISTS spGetCompanyNames $$ Create procedure spGetCompanyNames (IN Employer nvarchar(75), Out Employer_Namee nVarchar(75)) begin Select ...
1
vote
1answer
10 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
-1
votes
0answers
12 views

Error stored procedure Mysql

I have a table and every month need to delete data for new data are included. I made a stored procedure to automate this process. This table has about 3 million lines in a monthly period. This can ...
0
votes
1answer
52 views

MySQL: update field with “x hours xx minutes”using int from another field

We have two fields that hold time data totalTime (int): number of minutes (eg 243) totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes') We have a lot of different formats ...
2
votes
1answer
16 views

MySQL Stored Procedure Requiring SUPER

I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user ...
0
votes
0answers
27 views

Function performance issue

This one is puzzling me since yesterday and maybe someone could shed some light on this. We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
1
vote
2answers
27 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
0answers
27 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
1
vote
1answer
43 views

MySQL Cursor Batching Process

I have a cursor in MySQL and what I want to do is rather than having an enormous cursor loop that can be up to 4000+ users in size, do it smaller batches so that the MySQL engine doesn't suffer. I ...
0
votes
2answers
120 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
0
votes
0answers
181 views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
0
votes
1answer
151 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
1answer
36 views

Error while creating procedure

This is my procedure: DELIMITER $$ CREATE PROCEDURE sp_test_final6() BEGIN INSERT INTO ...
-1
votes
1answer
62 views

My auto increment primary key is not automatically incrementing

I have the following database Whenever I try inserting values in the database I get the following error. Duplicate entry '0' for key 'PRIMARY' This means ID is not incrementing automatically. I ...
0
votes
1answer
118 views

“Access denied for user” error when creating a procedure

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK ...
0
votes
0answers
39 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
1answer
282 views

Rollback parent procedure on failure in MySQL

I have the following two procedures on my server: CREATE DEFINER=`someone`@`localhost` PROCEDURE `NewMagnet`( IN `nick` VARCHAR(32), IN `tth` CHAR(39), IN `name` TINYTEXT, IN ...
0
votes
0answers
307 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
0
votes
1answer
236 views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
0
votes
0answers
65 views

Dynamic Query creation by looping through Query-Result?

after my first question regarding pivoting a table (here) got great answers I'm now struggling with how to adapt this to variable table sizes. I need something like a "FOR EACH IN" which creates a ...
0
votes
3answers
98 views

Inner Join Table (Mapping table)

this is first time i am try mapping table in mysql i write this procedure but not work ? whats wrong ? DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`( ...
-1
votes
1answer
57 views

delete in stored procedure [closed]

i am write this stored procedure to delete one row DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT) BEGIN DELETE FROM students WHERE ...
1
vote
3answers
3k views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
1
vote
1answer
1k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
1
vote
0answers
151 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
1
vote
2answers
62 views

How to fill a tabe automatically with a procedure in MySQL

I have a very big table for users as below: CREATE TABLE `karbar` ( `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ...
1
vote
1answer
1k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
1answer
136 views

MySQL: Why am I getting this warning?

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe? 1 queries executed, 1 success, 0 errors, 1 warnings Query: call ...
1
vote
1answer
399 views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
2
votes
1answer
394 views

How to call stored procedure inside trigger?

I have made stored procedure that will update product quantity as per product id. That works fine for me. Following is code : DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE ...
5
votes
2answers
279 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
0
votes
1answer
697 views

Move data from one table to another

How do I retrieve data from one MySQL table and store the data into another database table on the same server. I have tried this code but keep getting errors: CREATE PROCEDURE `ProfusionTest` () ...
1
vote
1answer
2k views

Stored Procedure Performance using Temporary Tables

I have the following Stored Procedure I would like to improve the performance of DELIMITER $$ CREATE PROCEDURE NEXT_UNIT_OF_WORK ( IN batchId INT, IN size INT ) BEGIN start transaction; ...
1
vote
0answers
110 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
0
votes
0answers
201 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
0
votes
2answers
78 views

Stored Procedure - MySQL

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to ...
0
votes
1answer
129 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
1answer
57 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
265 views

MySQL: all stored procedures deleted?

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be ...
0
votes
0answers
165 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
0
votes
1answer
101 views

Procedure and Automation in Mysql

i have a table with these columns and the values are coming in these tables from a csv file at a regular interval of n minutes ( by cron the load infile command) : ID (PK) status first ...
0
votes
0answers
43 views

Does MySQL have built in support for procedural programming languages made in Python, PHP, Java, etc?

If I understand correctly, Postgresql has built in support so you can write procedures in different languages. But when I look at MySQL I only see a native language, it's own thing. Does MySQL have ...
-1
votes
1answer
31 views

prepare stmt is not working

set @sqlt = concat("select", @rowname_date," from syslog_data.syslogs_index_1 "); prepare stmt from @sqlt; execute stmt; deallocate prepare stmt; gives me a eeror 1064 in the prepare statement? Any ...
0
votes
2answers
2k views

How to create a stored procedure with several delete in(select) statements?

Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the ...
1
vote
1answer
657 views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
1
vote
0answers
1k views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
2
votes
0answers
91 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
0
votes
1answer
941 views

Stored procedure to handle empty sets

How can I make a stored procedure to print a specific message if an empty set wa returned by the query?