Tag Info

New answers tagged

-2

CREATE TEMPORARY TABLE IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT) AS ( SELECT CONCAT(MONTHNAME(m1),' ',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence FROM ( SELECT ('2014-01-01' - INTERVAL DAYOFMONTH('2014-01-01')-1 DAY) +INTERVAL m MONTH AS m1 FROM ( SELECT @rownum:=@rownum+1 AS m FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ...


1

If you want to raise error and exit from the procedure, you may want to try return. Example below. alter PROCEDURE sp1 @PersonID int as begin IF (@PersonId is null) --you can check 0, if that is the intention begin RAISERROR(N'Error already raised. See previous errors.', 16, 1); return; end; print '@PersonId is not null'; ...


4

Making use of Jeff Moden's Tally-Ho! CSV splitter from here: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values ...


1

I have a single MySQL query that do all the heavy lifting SELECT TimeDisplay FROM ( SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'', IF(dy=1,'1 day ',CONCAT(dy,' days '))), IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))), IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))), IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ...


1

You can have stored procedures execute as the definer instead of the invoker: CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END; See: Access Control for Stored Programs and Views


1

I would code as follows: SELECT (some values) FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND; DELETE FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL ...


0

Try this I believe the first part already evaluates to 00:00:00. So I just added the seconds in. WHERE m_dt BETWEEN (CURDATE() - INTERVAL 60 DAY) AND (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))


-1

How about: select @@hostname into your_variable;


0

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.


1

Drop the table if it exists before the next run. ... begin drop table if exists test; create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop; a := endIndex - startIndex; ... Or, as @dezso suggests, you could create the table only if it doesn't exist. ... begin create temp table IF NOT EXISTS test(up_link ...


1

You can't embed DDL in a stored procedure like this. Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.


4

inet_client_addr() is a system information function. It is located in the schema pg_catalog like other built-in functions (except for additional modules). pg_catalog is automatically part of the search_path. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables ...


2

Must be postgres user or other Superuser for mine. Only need create schema privilege for Erwin's answer. start tests: alter FUNCTION INET_CLIENT_ADDR() rename to _inet_client_addr; CREATE OR REPLACE FUNCTION inet_client_addr() returns inet language sql stable cost 1 as ' select ''192.168.0.1''::inet; '; select inet_client_addr(); end tests alter ...


0

Thanks for migrating my question. I solved the problem. It seems that the tool I used to migrate my DB from EC2 to RDS added a bunch of weird indexes which caused the performance of the DB to tank. Once I removed any and all elements of the DB with 'azure' in their name my performance returned to normal! The tool was extremely useful but if anyone ...


5

You should specify the length of the varchar column e.g. varchar(2000).


1

Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second. I will add that code: DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`( ) BEGIN DECLARE v_BatchCount,v_BatchLimit INTEGER; DECLARE v_user_id INTEGER DEFAULT 0 ; DECLARE v_officialjobtitle ...



Top 50 recent answers are included