Tag Info

New answers tagged

3

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.


3

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 ...


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...


2

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches. If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select ...


0

In my case this command give me an error mysqldump: Got error: 23: "Out of resources when opening file ' I had the --single-transaction option to resolve this error. And if youy only want the routine, you can add --no-create-info to avoid the create table statement.



Top 50 recent answers are included