Tagged Questions
0
votes
0answers
24 views
Procedure after commit
I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
0
votes
1answer
40 views
Stored procedure with and without preparing statement
I have 2 stored procedures:
CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
...
0
votes
0answers
21 views
developing procedure in sql for global use
what type of complex procedure can i develop in SQL which will make others work easy as well ,
like in Java , i had developed a program for FTP so that anyone can import this program and can use it ...
1
vote
2answers
59 views
Same Parameter in MySQL Stored Procedure
How do I cache dynamic query from store procedure?
Right now I have created my store procedure like this :
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @sqlQuery = ...
2
votes
2answers
78 views
How to create a mysql stored procedure through linux terminal
In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the command delimiter for only the mysql client(client side ...
1
vote
1answer
34 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
0
votes
1answer
36 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
1
vote
1answer
50 views
MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host
We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication.
"'Column count of mysql.proc is ...
0
votes
2answers
39 views
Why does this procedure raise a privilege error?
I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.
QUESTIONS
Could somebody help ...
0
votes
0answers
37 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
0
votes
0answers
57 views
Stored Procedures and Functions in MySQL
I just need to know about Stored Procedures and Functions in MySQL.
While I designing a database for an eCommerce website, I was in a situation to write stored procedure for my database, this is new ...
1
vote
1answer
478 views
How to return resultset from MySQL Stored Procedure using prepared statement?
DELIMITER $$
CREATE PROCEDURE List_IL()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Temp_List_val VARCHAR(255);
DECLARE Project_List_val VARCHAR(255);
DECLARE FoundCount INT;
...
2
votes
2answers
100 views
EMS SQL manager permission problem
I have a strange problem with EMS SQL Manager.
I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:
GRANT CREATE ROUTINE, ALTER ROUTINE ON ...
0
votes
1answer
23 views
Two dichotomy views of a table, and means to express this as a stored procudure?
There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.
How to express ...
0
votes
0answers
48 views
Returning multiple resultset from the same table
i am working with mysql procedure. final result set i have got in to one table.
now i need to return for each set i need to return the value.
example:
like this is contents in my the table.
i need ...
1
vote
2answers
51 views
MySQL: Adding Routines to a Slave
While setting up the MySQL replication, I took the mysqldump (without--opt --routinesoptions) unaware of the fact that in mysqldump, --routines it is NOT enabled by default.
Questions
Is there any ...
0
votes
1answer
145 views
how to dump a single stored procedure from a database
I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
1
vote
2answers
192 views
Using MySQL EXPLAIN to analyze the performance of a function
You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
0
votes
1answer
106 views
How do I check if a stored procedure is defined in MySQL
When I do :
select routine_definition
from information_schema.routines
where routine_name = 'sp_name';
it returns a row, but the routine_definition column is NULL. Does this mean that this Stored ...
1
vote
2answers
250 views
“can't return a result set in the given context” error when calling stored procedure - differences between mysql 5.0 and 5.5?
I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table)
When I'am calling
CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent');
...
1
vote
1answer
141 views
MySQL Dump all rountines except one
We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
0
votes
2answers
207 views
How can I change collation for all tables and columns in MySQL database?
I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, ...
2
votes
1answer
2k views
MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query
I've created a procedure to loop through large InnoDB tables and delete a chunk of rows, then pause and repeat until max field value allowed is reached.
Binary logging is enabled, but I'm leaving it ...
0
votes
1answer
100 views
MySQL user defined rollback procedure
I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more ...
11
votes
2answers
1k views
MySQL: Tree-Hierarchical query
SUB-TREE WITHIN A TREE in MySQL
In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
0
votes
2answers
376 views
Use of CASE statement in a MySQL query
I have 3 tables:
TASK (id_task (PRIMARY KEY), name)
PROJECT (id_project (PRIMARY KEY), name)
PROJECT_HAS_TASK (id_project, task_id)
PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK.
...
0
votes
2answers
93 views
Understanding the use of Sql command parameters
I am using MySql database and I have a stored procedure to insert data, like this-
CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`(
IN id varchar(10),
IN pwd varchar(10),
IN fname ...
0
votes
0answers
208 views
MySQL: Get info from SHOW ERRORS
How can I get the info (code and message fields) from SHOW ERRORS? Or even, how can I insert this information into a table to keep tracking the errors?
Something like
SELECT CONCAT(Code,' - ', ...
0
votes
1answer
290 views
MySQL - Create a function from another function
I want to create a function same like my old functions.
When I create a table, I can use `AS to other table as follows:
CREATE TABLE dummy2 AS SELECT * FROM dummy1;
Can I do the same thing with ...
1
vote
1answer
696 views
MySQL: how to optimize the stored procedure?
MySQL 5.5.28
Running pt-query-digest on a slow query log, I get something like this:
# 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz
# Current date: Wed Oct 24 23:44:05 2012
# ...
1
vote
1answer
195 views
`show procedure status` doesn't list all of the procedures?
mysql-server-5.5.28-1.el5.remi
I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
2answers
378 views
Event Scheduler: No data - zero rows fetched, selected, or processed?
MySQL 5.5.28
The error log keeps getting this error:
121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r
ows fetched, selected, or ...
1
vote
1answer
93 views
Access Drizzle databases through MySQL
This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot ...
1
vote
1answer
542 views
mysql.proc keeps crashing, cannot do a mysqldump?
Due to some problems with InnoDB, I'm going to dump all databases to a new server:
mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server
The dump process stopped with ...
0
votes
1answer
148 views
Stored procedure arguments get immediately truncated
I have a stored procedure with a single SELECT statement inside:
DROP PROCEDURE `pos_neg_test`//
CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP)
BEGIN
SELECT ...
4
votes
1answer
64 views
Is it possible to encapsulate a table in MySQL?
I am new to Databases. I want the following:
UPDATEs in a database to happen only through a stored procedure
The user does not have GRANT UPDATE
The user has GRANT EXECUTE ON PROCEDURE
I tried it ...
-4
votes
1answer
61 views
What is MySQL Procedure [closed]
I came to know about MySQL procedure. Can someone tell me how to use it and why it is used? Any external link or examples will be highly appreciable.
2
votes
2answers
2k views
Show procedure code in MySQL
I've tried to show the SQL code of a stored procedure called add_pixel:
mysql> SHOW PROCEDURE CODE add_pixel;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
1
vote
1answer
275 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
2
votes
1answer
2k views
MySQL user permission on stored procedure
I've created a simple stored procedure:
mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected, 1 ...
0
votes
0answers
98 views
slow response from MySql server
We just moved our database from an old server to a new one with more processing power and 4G RAM. However, for some unknown reason, the execution of our store procedures became even slower. An ...
4
votes
2answers
4k views
Can I execute a prepared statement inside a MySQL SELECT?
I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
2
votes
1answer
219 views
How to use the 'select benchmark' command on a procedure
I am trying to check which of my procedures is slowest and thus needs optimization.
I currently have around 160 procedures and about 30 functions.
I am using the select benchmark() to check the ...
0
votes
0answers
302 views
Grouping a comma separated value on common data [closed]
I have a table with col1 id int, col2 as varchar(comma seperated value) and column 3 for assigning group to them.
Table looks like
col1 col2 group
..............................
...
1
vote
1answer
190 views
Why are these queries slow in some environments and not others? Can we fix their performance?
The following stored procedures in a MySQL 5.x environment:
DELIMITER //
CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
BEGIN
SELECT TABLE_NAME as ...
1
vote
1answer
208 views
use of signal for insert statement in mysql stored procedure
I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure:
DROP PROCEDURE IF EXISTS `spItem`$$
CREATE PROCEDURE `spItem`(
IN ...
1
vote
0answers
558 views
stored procedure returns ERROR 1305 (42000): FUNCTION does not exist
I have a stored procedure and function as below
CREATE PROCEDURE ProcName()
begin
....
DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on
A.aid=B.id;
....
...
3
votes
1answer
1k views
Creating a table from variable names
Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement.
CREATE ...
3
votes
1answer
899 views
Is there a way to tell your hostname within a stored procedure?
Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?
You can run system hostname from a client but you can't make system calls from SP. I don't see it ...
