SELECT ... INTO variable results in NULL or idk you can find all the relevant code on pastebin.

The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend > points) is evaluated FALSE then SQLSTATE 45000 is signalled.

If I evaluate the query separately in PHPMyAdmin

SELECT `current_points` INTO @var FROM `smartfuel_users` WHERE `id` = user_id;
SELECT @var;

the expected value is returned. I have been searching the whole day to find similar issues. Everywhere the datatype incompatibility has been the solution. After I checked the datatypes, it seems, this one is not the case.

Thanks for any clues, in advance.
The complete code:

CREATE TABLE IF NOT EXISTS `smartfuel_users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `surname` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(50) CHARACTER SET ascii NOT NULL,
    `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
    `chip_card_id` varchar(20) CHARACTER SET ascii DEFAULT NULL,
    `total_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `current_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`,`chip_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `user_id` INT UNSIGNED, IN `points` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE to_spend MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = user_id AND `expired` = 0 ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO to_spend FROM `smartfuel_users` WHERE `id` = user_id;

    IF to_spend > points THEN
        UPDATE `smartfuel_users` SET `current_points` = `current_points` - points WHERE `id` = user_id;

        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF points > 0 THEN
                IF (loc_points - loc_spent) > points THEN
                    UPDATE `smartfuel_activities` SET `spent` = points WHERE `id` = id;
                    SET points = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = id;
                    SET points = points - loc_points;
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;

So, the ultimate answer to my own question is, as follows.

The stored procedures on MySQL version 5.6.13 don't like, when you use local variables and procedure parameters named exactly as the column names returned by queries used in the procedure's body - even though I used backticks in the queries. Documentation on the use of backticks in MySQL queries.

I have made some changes in the logic of the procedure, as it was not working as I expected. But after renaming the following variables the code did not throw any exceptions, at all.

user_id -> usr_id
points -> to_spend
to_spend -> cur_pts

The edited code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `usr_id` INT UNSIGNED, IN `to_spend` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE cur_pts MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE v_id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = usr_id AND `expired` = 0 AND `points` <> `spent` ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO cur_pts FROM `smartfuel_users` WHERE `id` = usr_id;

    IF cur_pts > to_spend THEN
        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO v_id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF to_spend > 0 THEN
                IF (loc_points - loc_spent) > to_spend THEN
                    UPDATE `smartfuel_activities` SET `spent` = loc_spent + to_spend WHERE `id` = v_id;
                    SET to_spend = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = v_id;
                    SET to_spend = to_spend - (loc_points - loc_spent);
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;


Postgres - Get the query cost assign to a variable To get the estimated cost of the query, i using EXPLAIN SELECT column FROM table;, to get the current cost of the query, i am using EXPLAIN ANALYSE SELECT column FROM table;, my question is how to get the cost of the query automatically, without having to run the explain for each query manually.

I need something like:

DECLARE cost integer;
DECLARE highercost integer;
DECLARE query text;

highercost := 0;
i := 0;
query = '';

WHILE i < array_length( queryarray ,1) LOOP

    cost := explain analyse queryarray[i];

    IF cost > highercost THEN

        highercost := cost;
        query := queryarray[i];

    END IF;

    i := i+1;

END LOOP;

The idea is to create a script to check the querys in a log and run in psql, or copy the log querys to a table in the database and run with plain SQL to verify the most expensive ones, at the moment is just what i seek, no need to worry about the real cost of the query ( "cost" X "times executed per minute"), cost of INSERT, UPDATE, and DELETE among other things.

I hope this is possible, if not, there is another way to search for expensive query without checking one by one?

Based in this answer [https://pt.stackoverflow.com/questions/155113][1] :

CREATE or REPLACE function custo_consulta(_consulta text[])
returns table (consulta text, custo_execucao numeric, tempo_execucao numeric ) as '
declare custo text;
begin
    foreach consulta in array _consulta loop
        execute ''EXPLAIN ( FORMAT JSON, ANALYZE ) '' || consulta INTO custo;
        custo_execucao := split_part(split_part(custo, ''"Total Cost": '', 2), '','', 1);
        tempo_execucao := split_part(split_part(custo, ''"Actual Total Time": '', 2), '','', 1);
        return next;
    end loop;
end;
' language plpgsql;

SELECT *
FROM custo_consulta(array['SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''OPORTUNIDADE''', 'SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = ''REVISAO'''])
ORDER BY custo_execucao desc;

Result

 consulta                                                               custo_execucao     tempo_execucao
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'REVISAO'       38426,44           128,267
 SELECT COUNT(*) FROM syo_evento WHERE id_grupoevento = 'OPORTUNIDADE'  38252,65           123,996

Just another problem, i am receiving an error when using $$, but this will be solved.


Import all csv files into a table oracle Trying to import all the files into a table but I receive the following error.

#!/bin/bash
. ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1
sqlldr USERID=user/pass@mydb DATA=/data/SDP/Import/*.csv \
    CONTROL=/home/path/test.ctl LOG=/home/path/test.log

Error Message

 SQL*Loader-500: Unable to open file  (/path/path/Import/*.csv)
 SQL*Loader-553: file not found
 SQL*Loader-509: System error: No such file or directory
 SQL*Loader-2026: the load was aborted because SQL Loader cannot  continue.

Please help.

Use a shell loop, instead of trying to pass a wildcard:

#!/bin/bash
. ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/client_1

for FILE in /data/SDP/Import/*.csv; do
    sqlldr USERID=user/pass@mydb "DATA=$FILE" \
        CONTROL=/home/path/test.ctl LOG=/home/path/test.log
    done


Linking job steps from a single run together in sysjobhistory We have several SQL Servers running SQL Agent jobs, recording history in their own sysjobhistory tables. I am attempting to set up one centralized server with a job that collects the history from all the other servers, formats it a bit, and puts it into a table called AllJobHistory. As part of this process, I would like one column to indicate that multiple steps of a job were part of the same job run. They are already marked as being part of the same job through the job_id column, but I want to know that specific rows came from the 3:00 run of the job versus the 4:00 run. Being able to filter based on this column would make troubleshooting jobs that much easier for us, but I don’t see anything that links those steps together in any existing system table or DMV, is there?

My first attempt at rolling my own was to use the run_date, run_time, and run_duration columns. For each step, if I subtract the total run_duration up to this point from the run_time, it should bring me back to a time that is unique compared to all other runs of this job. This looked like it was working until I found a case where it wasn’t (likely because SQL Server is rounding run_time and run_duration at the precision of seconds). Here is my attempt at the query (with extra columns removed).

WITH JobDetails AS
(
    SELECT
        QUOTENAME(UPPER('ServerName')) AS [Server],
        j.job_id AS [JobID],
        j.name AS [JobName],
        s.step_id AS [Step],
        msdb.dbo.agent_datetime(run_date, run_time) AS [RunDate],
        (run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100) AS [RunDurationSeconds]
    FROM msdb.dbo.sysjobhistory h
    INNER JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps s ON s.job_id = h.job_id AND s.step_id = h.step_id
    WHERE h.step_id != 0
), GroupedDetails AS (
    SELECT
        jd.[Server],
        jd.[JobID],
        jd.JobName,
        jd.Step,
        jd.RunDate,
        jd.RunDurationSeconds,
        DATEADD(SECOND,
            -ISNULL(SUM(jd.RunDurationSeconds) OVER
                (PARTITION BY jd.JobName ORDER BY jd.JobName, jd.RunDate, jd.Step
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
            jd.RunDate) AS grp
    FROM JobDetails AS  jd
)
SELECT
    gd.[Server],
    gd.JobName,
    gd.Step,
    gd.RunDate,
    gd.RunDurationSeconds,
    CONVERT(VARCHAR(36), gd.JobID) + '_' + FORMAT(gd.grp, 'yyyyMMdd_HHmmss') AS JobRunString
FROM GroupedDetails AS gd;

Here is an example where it worked as I wanted it to, for a job that has three steps. Note that the JobRunString matches for the first run and again for the second run.

And here is an example where it did not work as I wanted it to. Note that Step1RunDate + Step1RunDurationSeconds != Step2RunDate, causing a mismatch of JobRunString.

So, is there any reliable way of linking the steps of a job run together in sysjobhistory?

Note that sysjobhistory has an ID column (instance_id). at least one entry should hit the table for each step of a job that was completed, followed by an entry with step_id = 0, recording the outcome of the job. Each step also records the time (run_date and run_time) the step was started, which will be equal to or greater than the time the job was started. So, the step_id = 0 row for a given run has a higher instance_id than the related steps, but a lower (or equal) run time.

So, try doing an initial pull of the data from the rows where step_id = 0 into a temp table (or equivalent). Then, all rows from sysjobhistory with the same job_id, a lower instance_id, and a higher or equal start time (from run_date and run_time) should belong to the job run you're looking for.

I used something much like this for a failed job report I used to run, at  a former employer.

Here's a stripped down, modified version of that code. It ran for me in a quick test on a SQL Server 2016 box, just now. However, I don't have any jobs that run frequently enough that multiple runs would have the same run times.

USE msdb;

DECLARE @start_date varchar(8) = '20171001';

IF(OBJECT_ID('tempdb..#failhist') IS NOT NULL) DROP TABLE #failhist;

CREATE TABLE #failhist
(
    [job_name] [sysname] NOT NULL,
    [run_datetime] [datetime] NULL,
    [run_ended] [datetime] NULL,
    [instance_id] [int] NOT NULL,
    [job_id] [uniqueidentifier] NOT NULL,
    [run_date] [int] NOT NULL,
    [run_time] [int] NOT NULL
);

INSERT INTO #failhist
select *
  FROM
(SELECT j.name as job_name
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4
        as run_datetime
       ,CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4 + (run_duration * 9 + run_duration % 10000 * 6 + run_duration % 100 * 10) / 216e4
        as run_ended
       ,h.instance_id
       ,h.job_id
       ,h.run_date
       ,h.run_time
   from msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
  WHERE h.step_id = 0
    and h.run_date >= @start_date
) x
;

SELECT
       t.job_name AS [Job/Step]
      ,' ' + CONVERT(varchar(19), t.run_datetime, 121) + ' to ' + CONVERT(varchar(19), t.run_ended, 121) AS Run_Msg
      ,0 as HdrDetail
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,-1 as step_id
      ,jh.instance_id as actual_instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 WHERE jh.step_id = 0
UNION ALL
SELECT
       '        '
      +CASE
         WHEN jh.step_id = 0
           THEN 'Job Summary'
         WHEN jh.run_status BETWEEN 0 AND 1  -- summary
           THEN RIGHT(CAST(100 + jh.step_id as varchar),2) + ' - ' + jh.step_name
         ELSE   '  ' + RIGHT(CAST(100 + jh.step_id as varchar),2) + ' (add''l info)'
       END
      ,'        ' + CAST(jh.message as varchar(max))
      ,1
      ,t.job_name
      ,t.run_datetime
      ,t.instance_id
      ,jh.step_id
      ,jh.instance_id
  FROM #failhist t
         INNER JOIN msdb..sysjobhistory jh ON (    t.job_id = jh.job_id
                                               AND t.instance_id >= jh.instance_id
                                               AND (   t.run_date < jh.run_date
                                                    OR (    t.run_date = jh.run_date
                                                        AND t.run_time <= jh.run_time
                                                       )
                                                   )
                                              )
 ORDER BY run_datetime, instance_id, step_id

If you actually have a job that runs more that once a second, you might have to use a windowing function, to ensure that you don't pick up job steps from an earlier run of the job with the same run_time value.

Caveat: If the limit on the number of rows each job can have in sysjobhistory is hit, you may get odd/incomplete results. Also, I occasionally saw jobs that failed without generating a job outcome (usually a temporary failure to authenticate the Windows user running the job).


Avoiding deadlocks in PostgreSQL with multiple clients I am developing software which launches multiple, concurrent clients to connect to a PostgreSQL (12) database. When each client starts, the first thing it does upon connecting to PostgreSQL is to run the schema creation script.

This script has been written idempotently -- at least in principle -- such that the multiple clients shouldn't trip over themselves. By in large, this works fine. However, PostgreSQL sometimes detects deadlocks and affected client(s) crash. Looking through the logging, I believe these occur under such a sequence:

Client A: Begin schema creation transaction
Client A: Finish schema creation transaction
Client B: Begin schema creation transaction
Client A: New transaction that uses schema (select from view)

Client A and B now in deadlock

The logs aren't 100% clear and I can't reproduce this deterministically, but that seems to be what's going on: Client A is trying to SELECT from a view defined by the schema, but it's deadlocking because Client B is trying to recreate that view (CREATE OR REPLACE VIEW) in the schema script.

Is it possible to ensure that the schema creation script runs exclusively? Or, is there some other solution (e.g., rather than CREATE OR REPLACE VIEW, I only CREATE VIEW once I've determined it doesn't already exist)?

From the comments to my question, the problem can be summarised, per @DanielVérité, as "concurrent DDL is bad; concurrent DDL and DML is really bad". I was not aware of that -- even with the care I took to make the DDL idempotent -- but it's a good thing to know. So, that leaves me with two broad solutions:

Don't do the DDL concurrently (e.g., have a single client instantiate the schema).

This is probably the "easiest" solution from an application development perspective -- and I see it as my backup plan -- but it has consequences on how the application is architectured. I only want to take this route if I have to.
Use advisory locks, on @a_horse_with_no_name's suggestion.

This requires a minor modification to my application's database library only, so no large architectural changes or changes to the schema. Its downside is that I have to acquire and release the lock on every transaction (to make sure it isn't already locked by the DDL section of the code), which necessarily serialises all transactions across clients.

I am experimenting with option 2 and it appears to be working. I need to give it a few more runs through to convince myself that the deadlocking has gone and what performance impact the serialisation has (anecdotally, so far, it's negligible). I'll report back with results when I have them...


Will my database index be more performant with a smaller varchar size? We're using Mysql 5.7

We have an index over a varchar(255) column in a table of ~ 500M rows.

select distinct varchar_column from table yields seven results, with the max length 21.

A DBA consultant recommended we drop the length of the varchar column to 21. Dropping the size of the varchar column in tests shrank the index size equivalently to simply defragmenting the table without changing the schema (e.g. alter table).

We saw marginally better results when we dropped the length to 17.

Are there any advantages we're missing besides index size?

If some of the values are 21 characters, dropping to 17 will lose data.  You probably don't want to do that.

Be conservative -- what will future data be like?  Maybe 22 characters?  Drop the length to, say, 30.

No space will be saved by changing the max length -- in the "data" or in the "index".  Well, not because of the change.  However, most ALTERs include some degree of rebuilding the table.  This is likely to squeeze out some, not all, of the freed or wasted space.  To see what I mean,

SHOW TABLE STATUS LIKE 't';   -- current size
OPTIMIZE TABLE t;
SHOW TABLE STATUS LIKE 't';   -- defragmented size
ALTER TABLE t MODIFY COLUMN c VARCHAR(30)...;`
SHOW TABLE STATUS LIKE 't';   -- probably no change to Data_length or Index_length

Performance?  In a complex SELECT, where a temporary table needs to be built, VARCHARs turn into CHARs temporarily.  For utf8mb4, that is 4*255 bytes versus 4*30.  This leads to bulkiness.  If the temp table is too big for RAM, it will spill to disk.  Disk is slower, even SSDs.


Is Information in sys.dm_db_index_usage_stats reliable I'm archiving data from an old system that has no documentation. Lucky me...

I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct answer or is there some parameters that I first need to check? SQL Server 2008 R2:

 SELECT t.Name AS Tabelname, p.rows AS NoOfRows, MAX(us.last_user_lookup) AS LastUsed, t.create_date AS CreatedDate
FROM sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN --A lot of the tables did not have any records in this table
    sys.dm_db_index_usage_stats as us ON t.OBJECT_ID = us.OBJECT_ID
GROUP BY t.Name, p.rows, create_date
ORDER BY MAX(us.last_user_lookup) DESC

No.

The sys.dm_db_index_usage_stat view reflects only, at best, data since the last database startup (last instance startup, or last time the DB was brought online). Furthermore the entries may clear under memory pressure. It will give accurate positives (if a table has non-zero stats then it is used)) but may give false negatives (0 usage in stats may not reflect actual usage). Also there are many systems that use certain tables only once per week, once per month or even once per year.


Restoring a database is not working I have a database called FDB. I have created this database on my PC with SQL Server Express Edition and added one table. I made a backup of this database. The SQL Server version on the server is 2005.

I copied this backup to the server and tried to restore it, but I get this message:

  The media family on device C:\Program File\Microsoft SQL Server\MSSQL.1\MSSQL\Bacup\FDB.bak is incorrectly formatted. SQL Server cannot process this media family. RESTORE Headeronly is terminating abnormally. Microsoft SQL Server error 3241

If you have created the backup of a SQL Server 2008 database and are trying to restore it on a SQL Server 2005 instance, you will receive this error.  You cannot do this.

Your best bet would be to script out the database (as long as all of the schema and objects are 2005 compatibile).  You can't downgrade a database.


Why monitor bytes sent and received on mysql I want to know why we should monitor bytes sent and received on mysql ?
I hope I have not asked a vague question.

Thanks.

First, let's identify each status variable:

Bytes_received: The number of bytes received from all clients.
Byte_sent: The number of bytes sent to all clients.

The only place I can think of where these number would make sense would be in the network. These status values represent the amount of data passing in and out of DB Connections. These bytes would most likely be visible from another perspective: the Operating System.

You could measure the amount on incoming/outgoing traffic in netstat against MySQL's view of it. If the amount of incoming data is low, or if the amount of outgoing data from MySQL is significantly higher than netstat says, check MySQL and/or the network. You may also want to look for any signs of dropped packets along any interfaces.

In light of this, when it comes to tuning, the only thing I can think of that you may want to tune is setting two things:

net_buffer_length : Connection and result buffer size
max_allowed_packet : Maximum packet size used internally by MySQL

What is a MySQL Packet ?

According to the page 99 of the Book

here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given this information, you may want to

raise net_buffer_length to its max value of 1048576
raise max_allowed_packet to 256M, 512M, or 1G (especially if you have BLOB data)


Lease Timeout VS HealthCheck Timeout - AlwaysON After all the reading this is what I understood.  Is it correct?

LeaseTimeout is a simple heartbeat between SQL Server resource DLL and the SQL Server instance.

HealthCheck Timeout is related to sp_server_diagnostics run.

Why do we have two timeouts?

Also how do I change the LeaseTimeout?  Is it through the cluster manager because  I couldnt find any T-SQL command like below for HealthCheck Timeout

ALTER AVAILABILITY GROUP AG1 SET (HEALTH_CHECK_TIMEOUT = 40000);

The HealthCheck is between WSFC and SQL Server. The timeout is how long WSFC will wait to get data back from the sp_server_diagnostics execution.
LeaseTimeout is the SQL Server resource and SQL Server Availability Group. How Lease Timeout works

There are two timeouts because they are for 2 different mechanisms for health of the WSFC and SQL Server. Both are changed via the WSFC property of the AG resource, picture is shown in the linked article above.


how to user table name as variable in mysql stored procedure I create a procedure to set auto-increment index value to 1. But there I want to use table name as a variable. I am try it but it not working correctly. I know there have a lot of question like that but those are not help me to solve my problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop:
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   ALTER TABLE var1 AUTO_INCREMENT = 1;
  END IF;
 END LOOP;
END

This is the my stored procedure. I want to user var1 as a variable in bellow line

ALTER TABLE var1 AUTO_INCREMENT = 1;

I already use it as a variable but it not working. because of it get like a table name.

please help me. I am new to the stored procedure.

ALTER TABLE var1 AUTO_INCREMENT = 1;

we can replace above line in below code fragment. Then it will solve this problem.

SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Complete code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop:
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
   PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END IF;
 END LOOP;
END


Two Sequences on one column You can use 1 or 2 sequence.

Let's say I have a table tblOrder and 2 columns in it, OrderID and Company. I only use two companies in my table, IBM and Airtel.

What I want is when  Company = IBM then OrderID insert as 1 and when company is Airtel then it should insert OrderID as 1001 and repeat steps with increment 1, meaning next OrderID for IBM should be 2 and for Airtel should be 1002.

I think I need to use two sequences, on one column with case expression.

Create two sequences, one for IBM and one for Airtel. Set the start points appropriately. Put all the logic in a stored procedure. Pick the right one for each INSERT.

This is not a great design. Surrogate keys like this are best if they are completely meaningless. By tying a range of values to a customer you may have problems in the future. What will you do when a range is exhausted? What if you acquire another customer? Will you tolerate gaps in the sequence? For a short-lived, single-purpose application you may be OK. For an enterprise application it is a risk.


Importing Oracle database from dump where source dump is spread across different tablespaces How to import an Oracle schema from a dump file into another schema in new Oracle DB, where the target schema has only USERS and TEMP table spaces where as Source schema is having data distributed in several table spaces.

I mean I need to map all the tablespaces from source schema to USERS tablespace in target schema. How can I achieve this?

I am using this but not working.

impdp system/oracle@comverse2 schemas=epimart directory=DATA_PUMP_DIR dumpfile=mart.dmp logfile=epimart.log

try remap_tablespace parameter:

remap_tablespace:source_tablespace_1:user,source_tablespace_2:user


Mongo DB - reading during update Is it possible in a three-member-replica set (1 primary, 2 secondaries) to update records on the primary, or even delete several records and insert the updated data, and still execute read-ops on the secondaries by setting the read-preference to secondary?

I do know that all write-ops will only go to the primary and will be replicated by the secondaries but I do not know if it is possible to delay the replication or even better to replicate one secondary and after it is finished to replicate the other one.

Thank in advance

Actually, it is pretty easy to configure a delayed member of a replica set via the shell:

cfg = rs.conf()
cfg.members[0].priority = 0
cfg.members[0].hidden = true
cfg.members[0].slaveDelay = 3600
rs.reconfig(cfg)

In this example taken from the docs, the first member of replica set will be reconfigured to have a delay of one hour. Note that this delayed member can never become primary (and should not). By setting the hidden attribute to false, you may end up reading from this member by setting the read preference to secondary, but there is no guarantee for it. So you have to connect directly to the delayed member (remember to set the slaveOk option), which obviously lacks failover capacity.

Waiting for the data to be acknowledged by one secondary and then send it to the next would be very inefficient, and therefor I doubt that it is possible. Think of how long it would take when you had a replication factor of 15, multiple data centers involved and a write concern of majority...


MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix? SELECT  e2.personId, COUNT(*)

FROM        entries e1
JOIN        entries e2   ON  e2.categoryId   =   e1.categoryId
                        AND  e2.personId    !=   e1.personId

WHERE       e1.personId  =  1

GROUP BY    e2.personId

gives:
id   select_type   table   type   key                   key_length   ref                     rows   extra
1    SIMPLE        e1      ref    personId_categoryId   4            const                   59     Using index; Using temporary; Using filesort
1    SIMPLE        e2      ref    categoryId_personId   4            project.e1.categoryId   8      Using where; Using index

If I remove the GROUP BY I get "Using index".
What's the problem here?  Is it something to do with joining a table onto itself?
(The indexes present on the table are the two shown in the explain output, containing the columns that the names suggest.)
DDL:
CREATE TABLE `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `personId` int(10) unsigned NOT NULL,
  `categoryId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `personId_categoryId` (`personId`,`categoryId`),
  KEY `categoryId_personId` (`categoryId`,`personId`),
  CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`personId`) REFERENCES `people` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `entries_ibfk_2` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1465605 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Try to convert your query to
SELECT e2.personId, COUNT(*) - SUM(e2.personId = e1.personId)
FROM entries e1
JOIN entries e2 USING (categoryId)
WHERE e1.personId  =  1
GROUP BY e2.personId

Update
It seems that MariaDB uses implicit STRAIGHT_JOIN... or plan builder cannot optimize the query correctly.
For query optimize it's enough to swap table copies. See fiddle for details.


Restoring MySQL Tables from .ibd, .frm and mysqllogbin files For some reason, when I try to open up my tables that are stored in .frm and .ibd files (whether on MySQL or phpmyadmin) it gives me a syntax error, or it says it does not exist.
I've read restore table from .frm and .ibd file? that had similar problem to this, but I don't know how to check if innodb_file_per_table is enabled, and I'm overall just really confused. I also converted a copy of my mysql-bin.000002 file to a txt file so I see that the data from my database is not completely lost.
The database was created last year. I have 6 of those mysql-bin.00000 files, but for some reason the .000002 is the largest. Right now, I have the .ibd and .frm files for all my databases, but I'm at a loss as to how I can restore it back into MySQL, or at least into something I can read.
I'm using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. Also, am I supposed to download a plugin in InnoDB?

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.

Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
Go into your command/terminal to open the MySQL utility, mysqlfrm, and use it to find the structure of your table that you need to restore. How I did that, was I cd'ed into the file location of mysqlfrm, then entered "mysqlfrm --server=user:pwd@localhost --port=3307 "path_to_.frm_file" > table_name.txt". The .txt file should be saved in the same folder as where your utilities are saved if you didn't specify where you wanted it to go.
In the text file, you'll see the CREATE TABLE statements, that include all the columns and info (basically, the original structure) of your table. Copy that CREATE statement with all that info.
In your MySQL Command, create a new database (CREATE DATABASE database_name). Make it the same name as your original database name.
Create a new table inside the new database--it doesn't have to be the same name as the folder. You can create the new table inside the command prompt, but I created my table in PhpMyAdmin, a free software tool that handles the administration of MySQL over the web. I simply clicked on the database on PhpMyAdmin, then the SQL table, and pasted the table structure from #3. (As a side note, I always received errors if I named my tables "table" in my command prompt, so try to avoid that name).
On your MySQL Command, go into your database, and enter "ALTER TABLE table_name DISCARD TABLESPACE", which will essentially remove this table's .ibd file.
Copy your original table (the table you want to restore)'s .ibd file into the newly-created table to replace the .ibd file that you just removed. Change your initial .ibd file to the newly created table's name. This will mimic the old .ibd file that you just deleted. You can find this folder in the MySQL data folder, under the newly-created database folder on your computer.
Go back to your MySQL Command, go into your database, and enter "ALTER TABLE table_Name IMPORT TABLESPACE." You'll receive a "warning" (1) error-type statement, but just ignore that.
And done! if you try to access your new table, it should contain all the data from your old table.

I hope this helped, and let me know if you have any questions or comments! Also, check out http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file for other details.


Delete a variable number of records by a chronological order Our developers have been using a cursor within a stored procedure to delete old password history.  The number of records to delete is being passed by variable.

DECLARE hist_cursor CURSOR LOCAL FOR
    SELECT history_nr   FROM usr_pwd_hist   WHERE usr_id = @usr_id
    ORDER BY    history_nr ASC

OPEN hist_cursor

WHILE @to_delete > 0
BEGIN
    FETCH NEXT FROM hist_cursor INTO @hist_val

    DELETE FROM usr_pwd_hist WHERE CURRENT OF hist_cursor

    SET @to_delete = @to_delete-1
END

CLOSE hist_cursor;
DEALLOCATE hist_cursor;

I would like to replace this with a set based approach.  I can't do a simple top statement because the number of records to delete is a variable.  I can't use a top with a variable without dynamic sql and by policy we don't allow dynamic sql in production.

I'm considering this approach below but it makes me nervous as I know that Microsoft is planning on changing the way ROWCOUNT affects return results.  By putting the delete targets in a subquery I should be ok with future SQL versions, but I'm still wondering if there is a better way to delete a variable number of records by a chronological order.

SET ROWCOUNT @to_delete;  /* limit records to be deleted */
DELETE FROM usr_pwd_hist WHERE history_nr IN
(
    SELECT history_nr
    FROM usr_pwd_hist
    WHERE usr_id = @usr_id
    ORDER BY history_nr ASC
);
SET ROWCOUNT 0;  /* return rowcount to default setting */

You can use TOP with a variable. You just need to put it in parentheses.

WITH T
     AS (SELECT TOP (@to_delete) *
         FROM   usr_pwd_hist
         WHERE  usr_id = @usr_id
         ORDER  BY history_nr ASC)
DELETE FROM T;


How to control position of startup predicate in the execution plan For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows that come from test_fn1 function. I need to control this behaviour.

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
  insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
  return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
  select s.*
    from dbo.test_fn1(@Par2,';') x
    inner join sys.objects s on s.name = x.item
    where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2
   varchar(100))
returns table
as
return (
  select s.*
    from sys.objects s
    inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
    where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)

Below is the proper plan where startup predicate makes the query does not work on any data.

Here is the plan that show wrong behaviour of placing startup predicate. In both cases we start from the same function and only order in T-SQL is changed.

Tested on SQL Server 2016 SP2.

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?

No, not that I've ever seen. They don't get written about all that much. The one time I blogged about them, I did a lot of head scratching.

Now, I have an answer -- it's not guaranteed to always work. It's playing some tricks on the optimizer that you can learn more about by watching Query Tuning Mastery: Clash of the Row Goals, a talk by Adam Machanic.

With that out of the way, we can control to some degree where the optimizer decides to stick predicates by using TOP.

Function rewrite 1:

In this one, the WHERE clause appears inside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erik ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item
                          AND    @Par1 = 'CASE1' ) AS ca );
GO

Function rewrite 2:

In this one, the WHERE clause appears outside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erikx ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item ) AS ca
               WHERE  @Par1 = 'CASE1' );
GO

You can see the resulting plans here.

In plan 1, the startup expression predicate appears inside by the TOP:

In plan 2, it appears outside.

If you want the Startup Expression Predicate to act as a constant scan, try this:

CREATE OR ALTER FUNCTION dbo.test_erik_filter ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (
               SELECT ca.*
               FROM (
                     SELECT TOP (1) 1 AS n
                     WHERE @Par1 = 'CASE1'
                    ) AS x
               CROSS APPLY
                (
               SELECT     s.*
               FROM       sys.objects AS s
               INNER JOIN dbo.test_fn1(@Par2, ';') AS x
                   ON s.name = x.item
               ) AS ca

         );
GO

If we run two different test cases, the plans will be slightly different:

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE2', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE1', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

Looking at the live query plans (here are the regular ones), there are a couple early important differences.

No rows come out of this, which means the start up expression isn't true.

Rows will flow out of this one, because it does.

Remember that data flows from right to left in a query plan. Putting the filter further towards the left side of the plan has no advantage for doing less work.

Aaron Bertrand discusses a similar issue with regular filters here:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

As an aside, if your real-life code doesn't just select from system tables/views, you should consider adding the SCHEMABINDING attribute to it, which could aid in filter placement.

Hope this helps!


Insufficient privilege We are using oracle 11g

I am replicating production db for dev environment.
I Have a user named CST2 and CST0 where CST0 contains all tables and view and public synonyms for the same. I have provided same set of privileges for CST2 in dev env as in production.

There is a view named Product(based on product_info and product_ainfo) in CST0 which can be accessed by CST2 in production(Did select). but doing the same in replicated dev env is giving the error insufficient privilege.

I checked following means from which the user can get privileges.

the roles assigned are same and privileges for that roles are also same.
usr_tab_privs are same
usr_sys_priv are same

I cant find out how the CST2 in prod is able to access view 'product' in CST0 as it got no privilege through roles or tab_privs.

Am I missing any other way by which CST2 in prod got privilege which I do grant in dev.?

An issue I found on a similar problem was that the ‘first’ grant on the object had to be done ‘with grant option’.

Eg for the issue above, this would therefore mean:

GRANT SELECT ON <underlying table owner>.<table_name> TO CST0 WITH GRANT OPTION;

Then running

GRANT SELECT ON <schema>.<view_name> TO CST0;

should work.

Note this this ‘foundation’ grant will need to be repeated for each of the underlying table owner>. which forms part of the view definition.


SQL Trigger that creates new table when a new record is added to current table It's been quite a few years since I have done DB work.  I am working through mysql workbench (using MS Access as a frontend).

I have a base table that I am multilinking (basically a name index type of thing). Through a form, the table is updated (a record added) and I want that new record to trigger an event that creates a table and that table is named based on what the user inputted in the form/record.

Any thoughts on how to do this?  Good tutorial/documentation that you would recommend reading?
Thanks!

First things first

this would you use, to create special table per id Name and table you have to add

DELIMITER $$

CREATE TRIGGER after_table_insert
AFTER INSERT
ON tabl1 FOR EACH ROW
BEGIN
    SET @sql = CONCAR("CREATE TABLE table_",NEW.name,"_",NEW.ID, " (id INT,name TEXT)");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

If you had a table for all

CREATE TABLE Table1_companion
( id BIGINT AUTO_INCREMENT PRIMARY KEy
, Refid  INTEGER
,col1 TEXT
,col2 TEXT
,FOREIGN KEY (Refid) REFERENCES tavle1(id));

So you have

SELECT t1.*,t2.* FROM table1 t1 INNER JOIN Table1_companion t2 ON t1.id = t2.Refid;

And when you have a n:m Relationship

You need two tables

CREATE TABLE Table1_companion
( tc_id BIGINT AUTO_INCREMENT PRIMARY KEy
,col1 TEXT
,col2 TEXT);

CREATE TABLE Table1_relation
(
, Ref_id  INTEGER
, Ref_tc_id   BIGINT
,FOREIGN KEY (Ref_id) REFERENCES table1(id)
,FOREIGN KEY (Ref_tc_id) REFERENCES Table1_companion(tc_id ));

Of course you need two joins for that.

You could take a look at normalization.

With that you have a relational database, that works with every rdms.

this is of course only a basic idea,  you must have your own datatypes and foreign keys need of both sides the same datatype, and always remember have every information only once in  your database


Adding id column of table to a hstore column postgres I am trying to figure out how to store the id column of a table into a hstore column.  That way the id is stored in two places.  Going forward the table will be moved to another database and the current id will need to be preserved.

I have tried:

update new_table set hstore_col = hstore_col || hstore("id",id::text) where id = 1

This does not work  Any other suggestions adding the id column of a database table to a hstore column it has.

Usually in SQL double quotes used to specify the identifiers like tables, columns, functions and other DB objects which name contains "non-conventional" characters or have to be case-sensitive. For example it is correct PostgreSQL statement:

create table "This is#my...%TABLE" ("And&Field" int);

(It is not recommended to use such names however because it may cause a lot of problems)

So double quoted literals meanings the DB objects and in the your statement "id" meanings the id field.

On the other hand to specify the string literal in the standard SQL you have to use single quotes. For example:

select 'foo' as "bar field";

Here 'foo' is a text constant and "bar field" is a column alias where double quotes is required because of the space character inside it.

According to the above you have to change your statement to

update new_table set hstore_col = hstore_col || hstore('id',id::text) where id = 1


How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows:

42, 1
43, 1
42, 2
43, 5
43, 6
42, 3

the output contains the client_id, and boundaries of the gaps, like this:

43, 2, 4

Note that there's no row with client_id=42 because there are no gaps in its data.

I've tried this for the core part of the query:

select
    client_id,
    order_no as start_order_no,
    lead(order_no) over (order by client_id, order_no) as end_order_no

but that apparently doesn't do what I want, and I suspect it's because of client_id in the OVER part.

You should use a CTE or a subquery to get the lead order_no first.

select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   (select client_id,
               order_no,
               lead(order_no)
                   over (partition by client_id order by client_id, order_no) as no
from     tbl) ct
where  no is not null
and    no - order_no > 1;

with ct as
(
select   client_id,
         order_no,
         lead(order_no)
             over (partition by client_id order by client_id, order_no) as no
from     tbl
)
select client_id,
       order_no + 1 as c1,
       no - 1 c2
from   ct
where  no is not null
and    no - order_no > 1;

client_id | c1 | c2
--------: | -: | -:
       43 |  2 |  4

db<>fiddle here


select all tables from dba_table except list I'm missing something in my query. I want to select all tables from dba_tables except a list of tables.

For example, show all except HR.DEPT, SCHEMA.TAB

dba_tables:

OWNER    TABLE_NAME
------   ----------
HR       DEPT
HR       TEST
SCHEMA   TAB
SCHEMA   NAMES
TEM      TBA

Result after query:

OWNER    TABLE_NAME
------   ----------
HR       TEST
SCHEMA   NAMES
TEM      TBA

Query I tried:

select owner, table_name from dba_tables
where (owner != 'HR' and table_name != 'DEPT' )  OR (owner != 'SCHEMA' and table_name != 'TAB' )

select owner, table_name from dba_tables
where (owner, table_name) not in (('HR', 'DEPT'), ('SCHEMA', 'TAB'));


There is an equivalent of login.sql on PostgresSQL? When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed.

It's usefull to set preferences...

There is an equivalent when I log to a PostgreSQL database with psql ?

Yes, there is. It's stored in ~/.psqlrc

More details in the manual: http://www.postgresql.org/docs/current/static/app-psql.html#AEN85369


mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7).

Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but there are no Locked Transactions charting in the InnoDB Active/Locked Transactions graph. I also checked SHOW PROCESSLIST and SHOW INNODB STATUS and there are no locks.

However, if I do a mysqladmin debug it does print out a long list (dozens) of Current locks to the error log, i.e.:

lock: 0x1e6a21b0:

lock: 0x1e7cd750:

lock: 0x1e5bdcb0:

lock: 0x1e839180:

lock: 0x1e821870:

lock: 0x1e737840:

lock: 0x1e6c5590:

lock: 0x1e5b8fa0:

[snip]

How can I determine what is causing these locks if they don't show up in a SHOW PROCESSLIST or a SHOW INNODB STATUS? FWIW, I also tried the InnoDB Lock Monitor but it too did not show any locks.

Please advise.

Thanks!

The locks appear to be spurious. Verified bug: http://bugs.mysql.com/bug.php?id=64140


SQLServer tempdb datafiles SQL newbie here, trying to understand how tempdb work when there is more than one datafile.
To keep this simple, let's assume the server has three tempdb datafiles, each 10GB, totaling 30GB and are all configured not to auto grow. If a user query were large enough to require 25GB of tempdb, would it:
A: fail
B: fill the first datafile then the second and into the third
C: randomly spread the usage across all three datafiles
D: something else entirely? If so, what is that something else?

Any insight into this is greatly appreciated.

It's a mix of C and D, depending on the free space in each of your tempdb files.

SQL Server uses a Proportional Fill algorithm to determine which data file should be used for any given write. Paul Randall discusses this in detail here.

Provided that your tempdb data files have roughly the same amount of free space, writes will be performed in a round robin fashion (one extent at a time).


Receive strange error during mysql auto-repair check optimize I am running the command:

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize DATABASE_NAME

And up until this past week, this command has always run just fine. Last week however, I started receiving the error:

Error : Table 'DATABASE_NAME.H1#~i' doesn't exist
status : Operation failed

This is not a table I created or recognize. I have no idea why the error would suddenly start occurring. Has anyone seen this or have any idea what might be causing this?

Here is the original command that you ran

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize DATABASE_NAME

Since you are running mysqlchk against the database, you need to specify the --databases option

/usr/bin/mysqlcheck -hlocalhost -uDATABASE_NAME -pPASSWORD --auto-repair --check --optimize --databases DATABASE_NAME

Give it a Try !!!


Unable to create DB trace using DEA I want to create a DB trace file using DEA(Database Experimentation Assistant). I am using below softwares:

-SQL Server Express 2012 on Win10 machine where my DB is present.

-DEA is installed on a separate win10 machine.

When I am trying to capture the trace using DEA, it is giving me below error:

  Unable to start the trace on the target SQL Server, please check if
  you have required permissions and SQL Server account has write access
  to the specified trace file path. | Sql Error Code (19062)

On both my machine, Windows user have admin rights
-'Alter Trace' permission is granted to that DB user, which I am passing to DEA.
-Using that user, I am able to access the DB using SQL Management Studio from the machine where DEA is installed.

DEA saves the trace files on the target server, not on your client machine (assuming you run DEA on your machine). So the path (folder) has to exist on the target server, and the SQL Server account needs write access to that.


How to use MySQL Server 5.6 for GeoIP replication? I have downloaded the new MySQL Community Server 5.6 and I dont quite understand how to use it properly for GeoIP based replication across 20 nodes. I want to have one central node, then three smaller ones, and then 8 sub-sub nodes so it's like a tree, and the writes are propagating in the cycle around the globe.

Have anyone has any link to point any theory on the above scenario, or what would be appropriate option without migrating to MySQL Cluster?

Would you be interested in a MySQL Replication setup that is shaped like a snowflake ?

You could setup a Star Topology

Have a Write Master
Setup up a Distribution Master (DM). The role of the Distribution Master is simple

Slave to Write Master
Store No Data (All tables use BLACKHOLE Storage Engine)
Have Binary Logging Enabled
Act as Master to Multiple Read Slaves

For your case, you could have

Write Master in WM1

DM1 as Slave to WM1

SLV1 as Slave to DM1
SLV2 as Slave to DM1

DM2 as Slave to WM1

SLV3 as Slave to DM2
SLV4 as Slave to DM2

DM3 as Slave to WM1

SLV5 as Slave to DM3
SLV6 as Slave to DM3

You could hang a DM as a Slave off of another DM

The possibilities are endless

I have written about setting this up before

https://stackoverflow.com/a/5781504/491757
https://serverfault.com/a/264444/69271
https://dba.stackexchange.com/a/9336/877


How to achive a many to one merge join in Azure Data Warehouse I have a merge join in azure data warehouse. My estimated execution plan currently shows it as a many to many join. I'd like to know if it is possible to achieve a many to one join. Currently I'm struggling to think of a way to do this as primary keys and unique constraints are unsupported. Is there anything available that would allow me to tell it that one of the tables will always contain unique values.

Are you getting this info from an EXPLAIN plan or the new SSMS plan functionality for Azure SQL Data Warehouse?  Please provide.

In the "box product" engine (SQL Server 2017 14.0.3008.27), I can reproduce converting a many-to-many MERGE join to a one-to-many using the DISTINCT keyword against a columnstore table.  I can't guarantee this would behave the same in Azure SQL Data Warehouse however and it won't apply to all queries:

SELECT *
FROM dbo.Numbers a
    INNER JOIN
        (
        SELECT TOP 10 Number
        FROM dbo.Numbers
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );
GO

SELECT *
FROM dbo.Numbers a
    INNER JOIN
        (
        SELECT DISTINCT TOP 10 Number
        FROM dbo.Numbers
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );

Full script available here.  My results:


sys.partitions row count is badly wrong - how to correct this? Querying sys.partitions can return an approximate row count for a table.

I've noticed that this is returning the same row count for all partitions, regardless of the actual content (even for empty partitions).

The table has a clustered columnstore index and statistics have been created on almost all of the columns. Statistics are updated daily after each data load. The table is partitioned by date.

sys.partitions query:

    SELECT   convert(date, convert(varchar,rv.[value])) as partitionDate, p.rows as syspartitions_RowCount
        FROM       sys.tables t
        join       sys.schemas  sc on sc.schema_id = t.schema_id
        JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
        JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                                    AND     i.[index_id]          = p.[index_id]
        JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
        LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
        LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
        LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                                    AND     rv.[boundary_id]+1      = p.[partition_number]
        WHERE   p.[index_id] <=1
                and t.[name] ='tbl'
                and sc.name = 'temp'
                and convert(date, convert(varchar,rv.[value])) > '2016-05-31'
                order by convert(date, convert(varchar,rv.[value])),
                t.[name]

table query:

                    select date, count_big(*) as real_count
                from temp.tbl
                where date > '2016-05-31'
                group by date
                order by date

Sample results from both queries:

Try using sys.dm_db_partition_stats instead of sys.partitions, as in:

SELECT ObjectName = QUOTENAME(sc.name) + '.' + QUOTENAME(t.name)
    , RangeValue = rv.value
    , sys_partitions_RowCount = p.rows
    , sys_dm_db_partition_stats_row_count = ddps.row_count
FROM sys.tables t
    INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
        AND p.index_id = i.index_id
    INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
    INNER JOIN sys.partition_schemes ps ON ps.data_space_id = ds.data_space_id
    INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
    INNER JOIN sys.partition_range_values rv ON rv.function_id = pf.function_id
        AND (rv.boundary_id + 1) = p.partition_number
    INNER JOIN sys.dm_db_partition_stats ddps ON t.object_id = ddps.object_id
        AND p.partition_id = ddps.partition_id
WHERE p.index_id <= 1
    and t.name ='tbl'
    and sc.name = 'temp'
ORDER BY sc.name
    , t.name
    , rv.value;

For Azure SQL Data Warehouse, you'll need to use sys.dm_pdw_nodes_db_partition_stats instead of sys.dm_db_partition_stats, even though they contain the same details.

Note, I removed the CONVERT(date,...) functionality so this code is compatible with all partition schemes, not just those with date range values.

In the on-prem version of SQL Server, sys.partitions gets its row counts from the internal table ALUCOUNT or sys.sysrowsets, if ALUCOUNT.rows is NULL.  The definition of sys.partitions is:

CREATE VIEW sys.partitions AS
    SELECT rs.rowsetid AS partition_id
        , rs.idmajor AS object_id
        , rs.idminor AS index_id
        , rs.numpart AS partition_number
        , rs.rowsetid AS hobt_id
        , isnull(ct.rows, rs.rcrows) AS rows
        , rs.fgidfs AS filestream_filegroup_id
        , cmprlevel AS data_compression
        , cl.name AS data_compression_desc
    FROM sys.sysrowsets rs OUTER APPLY OpenRowset(TABLE ALUCOUNT, rs.rowsetid, 0, 0) ct
    LEFT JOIN sys.syspalvalues cl ON cl.class = 'CMPL' AND cl.value = cmprlevel

The on-prem version of sys.dm_db_partition_stats gets its row counts differently, from the internal table, PARTITIONCOUNTS:

CREATE VIEW sys.dm_db_partition_stats AS
    SELECT c.partition_id
        , i.object_id
        , i.index_id
        , c.partition_number
        , c.in_row_data_page_count
        , c.in_row_used_page_count
        , c.in_row_reserved_page_count
        , c.lob_used_page_count
        , c.lob_reserved_page_count
        , c.row_overflow_used_page_count
        , c.row_overflow_reserved_page_count
        , c.used_page_count
        , c.reserved_page_count
        , c.row_count
FROM sys.indexes$ i
CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS, i.object_id, i.index_id, i.rowset) c

While both sys.partitions and sys.dm_db_partition_stats should both have correct row counts, I'd put more trust in the PARTITIONCOUNTS internal table.


convert update field second to time I have 2 fields ( about 1000 records) name FROMTIME and TOTIME. Both fields will have time in seconds (for example FROMTIME = 20520 and TOTIME = 39360).

I would like to convert and update these fields to time format (for example FROMTIME = 20520 becomes FROMTIME = 05:42:00.000 and TOTIME = 39360 becomes TOTIME = 10:56:00.000)

Assuming your are using SQL Server database, you can use CONVERT function with style 114 to get the time portion from your seconds value in integer. Please refer to following code implementation.

Declare
     @FromTime int
    ,@ToTime int;

Set @FromTime = 20520;
Set @ToTime = 39360;

Select Convert(varchar, Dateadd(s, @FromTime, 0), 114) as FromTime;
Select Convert(varchar, Dateadd(s, @ToTime, 0), 114) as ToTime;

To update all the records, if your seconds fields are int or any numeric type you will require two additional time fields in your database and execute the update statement like below.

-- 1) if the fields are int type use following
Create table T1
     (FromTimeInSeconds int
    ,ToTimeInSeconds int);

Insert into T1 values (20520,39360)

--Alter table to add time fields
Alter table T1 add FromTime time, ToTime time;

Update T1
    set FromTime =  Convert(varchar, Dateadd(s, FromTimeInSeconds, 0), 114),
        ToTime =  Convert(varchar, Dateadd(s, ToTimeInSeconds, 0), 114);

If your seconds fields are char or varchar type you can update the same fields with statement like below.

-- 2) if the fields are character type use following
Create table T2
     (FromTime varchar(20)
    ,ToTime varchar(20));

Insert into T2 values ('20520','39360');

Update T2
    set FromTime =  Convert(varchar, Dateadd(s, cast(FromTime as int), 0), 114),
        ToTime =  Convert(varchar, Dateadd(s, cast(ToTime as int), 0), 114);

Please find a sample implementation of both the cases in this Fiddle.


Is there a fast way to change column type without dropping the clustered index? I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate the index is extremely slow.

Is there a way to speed this process, or by design this is the only to change the data type?

[Update] Just want to elaborate a bit about my question, the column that I like to update is a date dimension key. It is currently a bigint (8 bytes), and I would like to convert it to int (4 bytes). This should reduce the size of the database, and it should theoretically improve the database performance in general.

If you have Enterprise Edition, are on 2008 or greater (always useful to specify version), and most or all of the values in this column are less than the upper bound of INT, you can get all the space back and probably quite a bit more by applying data compression.

My guess is you can improve I/O by at least as much, probably more depending on the nature of the rest of the data (and your workload), but without having to make the change to the column directly (and hence all of the constraints that apply to it, foreign key columns in other tables, etc).

Something that's certainly worth testing, especially since it's so much less complicated than changing the data type.

Of course, you should establish first whether I/O is even your bottleneck, and whether stripping 4 bytes from this particular column is likely to help you much at all. On its own, I'd have to guess: no.


Open source & cheap 'data at rest' encryption solutions So I am exploring some options about database encryption. The best options are commercial (TDE). I am looking for an open-source implementation. Recent releases of MySQL and MariaDB have data-at-rest capabilities:
MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/
MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
What is important in this implementation (for companies) is: are these compliant with PCI-DSS / HIPAA etc?
From MariaDB:

The MariaDB file_key_management plugin enables the configuration of keys in a file. The key file is read at system start and no additional access is needed during runtime. The security of the encryption depends on access restriction to the key file. The key file can itself be encrypted, providing additional layer of protection.

From my point of view this will mean providing the decryption of the key during start (and OS reboot)? So whenever we (re-)boot a system does this mean we need to manually provide this key? Having this key readable on the server itself will defeat the use of data-at-rest encryption in the first place.
In MySQL 5.7.11+

The InnoDB tablespace encryption feature in non-enterprise editions of MySQL use the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).
MySQL Enterprise Edition offers the keyring_okv plugin, which includes a KMIP client (KMIP v1.2) that works with Oracle Key Vault (OKV) to provide encryption key management. A secure and robust encryption key management solution such as OKV is critical for security and for compliance with various security standards. Among other benefits, using a key vault ensures that keys are stored securely, never lost, and only known to authorized key administrators. A key vault also maintains an encryption key history.

Now I am wondering, can this be made compliant with security standards? When using this data-at-rest, will root or mysql user have access to the keys since they could read encryption keys from memory?

Data-at-rest
There may be a terminology problem here. Data-at-rest encryption usually means

Storage-encryption
Not peer-to-peer nor any other form of data-at-use encryption.

On the forms of encryption suggested, I would advise staying away from those RDBMS-specific solutions as they're less tested than the other options which PostgreSQL suggests

Storage encryption can be performed at the file system level or the block level. Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD. Many other operating systems support this functionality, including Windows.
This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. However, to mount the file system, you need some way for the encryption key to be passed to the operating system, and sometimes the key is stored somewhere on the host that mounts the disk.

Essentially, different operating systems and file systems abstraction layers provide a better better-tested method of handling data-at-rest encryption.
Yes, that means you have a key. Yes, that means if the key is compromised the data can be read. But if your database is compromised and not the key, the data is secure. And, that's why it's data-at-rest.
So you normally store the key owned by root. Have root mount the secured location, and let the postgres user access that. Obviously PostgreSQL needs access to the data and has to know how to decrypt it.
Now, if other users are on the machine they can't access the data unless they're they postgres user. Moreover, they can't access the key. And if they do manage to compromise the data or even steal the physical encrypted back up they can't access it without the key.


Selecting rows with a foreign key OR a join table relationship I have tables like this

# Things
id
user_id

# ThingUser
id
thing_id
user_id

I want to select all Things where user_id=123 OR it is related to user 123 through ThingUser.

I think maybe I'm close with something like this:

select * from things
  left outer join thing_users on things.id=thing_users.thing_id and thing_users.user_id = 123
  where
    things.user_id = 123 OR
    thing_users.id IS NOT NULL

But on my very large dataset this hangs for several seconds and then I quit it.

I concluded that the only / most appropriate way to achieve this is simply with a UNION. These two queries unioned together:

where user_id=123
it is related to user 123 through ThingUser


Why can I use named pipes to connect to SQL Server even if it's been disabled? Why can I still use named pipes to connect to SQL Server even if it's been disabled? I'm on SQL Server 2016 doing the testing. I have recorded my operation in this youtube video: https://www.youtube.com/watch?v=4lvkbwsspZc Hope someone can point me the right direction.

Check the SQL Server error log for the named pipes provider messages.  With named pipes enabled, you should see messages like those below and be able to connect both locally and remotely over named pipes:

Server local connection provider is ready to accept connection on (named pipe)
Server named pipe provider is ready to accept connection on (named pipe)

With named pipes disabled, you should see only local connection provider message(s).  You'll still be able to connect locally via named pipes (over shared memory) but not remotely via named pipes.


Conditional selection in FROM clause I have two tables:

parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.

There are additional columns, but I did not list them here as they are not really relevant.

I want to select the periods as following:

If the parent matches some predicate, then pick parent.period.
If not, then pick child.period from each child.

I have the following query:

select   q.period
from     parent,
lateral  (
           select  parent.period
           where   <some complex predicate>
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(<the same complex predicate as above>)
         ) as q(period);

While this query works and returns the expected periods, I am wondering if this query could be rewritten in such way that <some complex predicate> does not have to be evaluated twice, to improve performance (albeit by a small factor) and general query structure to represent the if ... else ... from above. Ideally, I want to loop once through each parent/child row and evaluate the predicate just once.

I tried to introduce a case when <some complex predicate> then ... else ... end clause, but I fail to get it right. I am not sure if that is even possible in the FROM clause. Is this possible?

I also had select case when <predicate> then parent.period else unnest(array(select child.period from child where ...)) from parent in mind (though I did not try it, so it might be disallowed in a case when ... as well), but I am not sure if first creating and then unnesting an array is really optimal. Also, I am not really fond of set-returning functions, such as unnest, in the selection.

The following queries evaluate the expensive predicate just once for each parent row. To achieve this, the predicate is evaluated in a separate subquery. The first version uses a lateral join, the second an inner join. This meets the requirements from the question.

select   q2.period
from     parent,
lateral  (
           select <some complex predicate>
         ) as q1(predicate),
lateral  (
           select  parent.period
           where   q1.predicate
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(q1.predicate)
         ) as q2(period);

select     q2.period
from       parent,
inner join (
             select  parent.identifier, <some complex predicate>
             from    parent
           ) as q1(identifier, predicate)
on         parent.identifier = q1.identifier,
lateral    (
             select  parent.period
             where   q1.predicate
             union all
             select  child.period
             from    child
             where   child.parent_identifier = parent.identifier and
                     not(q1.predicate)
           ) as q2(period);


Changing ANSI_NULLS setting on a table Can we use the following query to update ANSI_NULLS option.

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = status | 0x20000000 where name = 'tblClient'

I am not sure what it is doing. I got this after searching for a solution. Our db is SQL server 2008.

But while i am running this i got an error like this "Ad hoc updates to system catalogs are not allowed."

Please give your suggestions

Updating system tables directly is unsupported and unsafe. The structure of them has changed since 2000 so the code you have won't work against them anyway.

It is sometimes possible to use ALTER TABLE ... SWITCH to do this as a metadata only change however.

A full worked example below.

/*Create table with option off*/
SET ANSI_NULLS OFF;

CREATE TABLE dbo.YourTable (X INT)

/*Add some data*/
INSERT INTO dbo.YourTable VALUES (1),(2),(3)

/*Confirm the bit is set to 0*/
SELECT uses_ansi_nulls, *
FROM   sys.tables
WHERE  object_id = object_id('dbo.YourTable')

GO

BEGIN TRY
    BEGIN TRANSACTION;
    /*Create new table with identical structure but option on*/
    SET ANSI_NULLS ON;
    CREATE TABLE dbo.YourTableNew (X INT)

    /*Metadata only switch*/
    ALTER TABLE dbo.YourTable  SWITCH TO dbo.YourTableNew;

    DROP TABLE dbo.YourTable;

    EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT';

    /*Confirm the bit is set to 1*/
    SELECT uses_ansi_nulls, *
    FROM   sys.tables
    WHERE  object_id = object_id('dbo.YourTable')

    /*Data still there!*/
    SELECT *
    FROM dbo.YourTable

    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    IF XACT_STATE() <> 0
      ROLLBACK TRANSACTION;

    PRINT ERROR_MESSAGE();
END CATCH;

One caveat however is that this does not always work. Adding a check constraint with definition CHECK (X <> NULL) causes it to fail with

  ALTER TABLE SWITCH statement failed. Table 'YourTableNew' has a column
  level check constraint 'CK_YourTableNew_X__37A5467C' on column 'X'
  that is not loadable for semantic validation.

This makes sense as the semantics of the constraint do change with the setting.

If you encounter this you could consider temporarily removing the problematic constraints etc and adding them back after the switch or just biting the bullet and recreating the table in a more conventional way.


SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how? I have an SQL Server 2008 R2 server. I open "Activity Monitor", and inside the "Resource Waits" category, I see that the "Logging" category is taking up more than 1400 ms/sec.

That doesn't seem to be normal, what does that mean and how do I diagnose the cause?

UPDATE:

I ran this query, suggested by Shark:

select * from sys.dm_os_wait_stats order by wait_time_ms desc

and got these results:

wait_type                         waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
--------------------------------- ------------------- -------------------- --------------------- ----------------
LCK_M_IX                          3662                 102060386            31410                233
LAZYWRITER_SLEEP                  741527               63315760             305163               28805
WRITELOG                          3531635              42147142             4142                 309360
REQUEST_FOR_DEADLOCK_SEARCH       6327                 31676553             5038                 31676553

I would check disk health / performance for volumes holding your transaction logs. You are seeing a delay due to the durability requirement, logging is backed up.

Check for I/O delays on tlog files in activity monitor as well.


Oracle restore to a differnet drive I have the following script for rman to backup the main database and archive logs:

connect target sysdba/password@localhost/db
run
{
allocate channel d1 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
backup AS COMPRESSED BACKUPSET tag Full_database_open format 'E:\backup\data\db_%Y_%M_%D_%t_%s_p%p.dbf' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup AS COMPRESSED BACKUPSET archivelog all format 'E:\backup\data\al_%Y_%M_%D_%t_%s_p%p.dbf' delete all input;
delete noprompt obsolete redundancy = 1;
backup AS COMPRESSED BACKUPSET current controlfile tag =cf1 format 'E:\backup\data\cf_%Y_%M_%D_%t_%s_p%p.dbf';
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
}

The data files are in: E:\APP\ADMINISTRATOR\ORADATA\DB\

I am trying to setup a test server, but installed oracle in the C:\ drive.
How, so the data file is: C:\app\administrator\oradata\DB

I tried the following:

-- sqlplus
shutdown abort;
startup nomount;

-- rman
restore controlfile from "C:\backup\data\CF_2019_08_27_1017362393_20_P1.DBF";

-- sqlplus
alter database mount;

-- rman
restore database;

unfortunately the backup tries to write data to the E:\ drive which is not present in the test server.

How can I make oracle restore the files to: C:\app\administrator\oradata\DB ?

run
{
  set newname for database to 'C:\app\administrator\oradata\DB\%U';
  restore database;
}
switch database to copy;

This will generate filenames in the specified directory. If you want nice looking names following your naming convention, specify them manually, example:

run
{
  set newname for datafile 1 to 'C:\app\administrator\oradata\DB\system01.dbf';
  set newname for datafile 2 to 'C:\app\administrator\oradata\DB\sysaux01.dbf';
  ..
  restore database;
}
switch database to copy;

SET


What is SQL Server 2000 spid -2? I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size.

There's a particular table that has been a source of some production issues and any attempt to try to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 2000 SP4 but then a blocking spid of -2).

Even something like SELECT count(1) FROM <MyProblemTable> causes this spid(-2) to appear.  I went into the table properties and there's about 1.5 million rows which isn't much.  I have no problems running the same count statement on much larger sized tables.  I did notice the fragmentation is quite high on this table (around 45%) but any attempt to either rebuild or reorganize wreaks havoc on the system.  I tried running the reorganize during the weekend for like 8 hours and the job was still running and people were complaining of time-out issues.

Then the other day, I tried using the import/export wizard to copy the table to my local instance and it ran fine for the first 250K rows and I was also monitoring sysprocesses and saw the spid for the ETL process.  It was fine for those first 250K rows...then the wizard row count wouldn't increment anymore.  Then I check the spid on 2K box and it was blocked by spid(-2) again.

What the hell is wrong with the table?  Is there an integrity issue?  The only way to run SELECT statements against it without this happening is to use NOLOCK.

From the documentation on sp_who:

  In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. For more information, see KILL.

You can find the actual owner of the work using:

USE master;
GO
SELECT req_transactionUOW -- UOW GUID
  FROM syslockinfo
  WHERE req_spid = -2;

You can then use:

KILL 'GUID_from_above';


Sorting the Table and getting the position I am able to sort a table using the following code:SELECT * FROM Persons
ORDER BY LastName

But I wanted to get the ranking/position of a person using that order.

For example when I sort Person table by age, I want to get the rank/position of a person named Antinio Trias.

How will I do it?

Analytic functions have not been implemented in MySQL yet. There are some ways to overcome this limitation.

Joining the table to itself using not equality but > or >= and then using GROUP BY and COUNT(*) (what @deszo is essentially doing - a self LEFT JOIN). Downside is that it may not be fast enough, especially if you are self-joining a complex query/view.
Using MySQL variables (I should add a huge Warning though: using MySQL variables this way may be broken in future MySQL releases. This is not a guaranteed behaviour):

SELECT LastName
     , FirstName
     , RowNumber
     , Rank
     , DenseRank
     , RowNumber_OverPartitionBy
FROM
  ( SELECT p.*,
           @rown := @rown + 1 AS RowNumber
         , @rnk :=  CASE WHEN LastName = @prev_lastname
                      THEN @rnk
                      ELSE @rown
                    END AS Rank
         , @drnk := CASE WHEN LastName = @prev_lastname
                      THEN @drnk
                      ELSE @drnk + 1
                    END AS DenseRank
         , @rowp := CASE WHEN LastName = @prev_lastname
                      THEN @rowp + 1
                      ELSE 1
                    END AS RowNumber_OverPartitionBy
         , @prev_lastname := LastName
    FROM
        Person p
      CROSS JOIN
        ( SELECT @rown := 0, @rnk := 0
               , @drnk := 0, @rowp := 0
        ) AS dummy
    ORDER BY LastName
           , FirstName
  ) AS p
ORDER BY LastName
       , FirstName ;

You can test in SQL-Fiddle: test-1

In other DBMS, that have window (analytic) functions, you could have the same in a much more compact query:

SELECT LastName
     , FirstName
     , Row_Number() OVER(ORDER BY LastName, FirstName)
         AS RowNumber
     , Rank() OVER(ORDER BY LastName)
         AS Rank
     , Dense_Rank() OVER(ORDER BY LastName)
         AS DenseRank
     , Row_Number() OVER( PARTITION BY LastName
                          ORDER BY FirstName)
         AS RowNumber_OverPartitionBy
FROM
    Person p
ORDER BY
    LastName
  , FirstName ;

Test in SQL-Fiddle (SQL-Server, Postgres, Oracle): test-2


Why does mongodb require three config servers The official mongodb documentation states to use three config servers. They say its for redudancy, because 1 server would be a point of failure.... but why not 2? in a production sharded cluster, if I have 1 shard, I shouldn't need more than 2, right? Is one config server only meant to handle 1 shard? so, if the minimum is 2 shards, then at least I should have 3?

Config servers are a special case, you can use one (for testing), or three and that's it.  They are not a replica set, at least not yet, that might change in the future, see SERVER-1448.
Go and (re)read the first two paragraphs here - note the piece about them not being a replica set is explicitly mentioned.  I will pick out some other relevant points:

Config servers use a two-phase commit to ensure immediate consistency
and reliability
to ensure redundancy and safety in production, you should always use
three

The first part gives you a hint about why 3 are needed, the second part is telling, particularly in terms of the safety aspect (redundancy is obvious - you have 3 copies of the data).
With how the 2 phase commit is implemented, all three config servers must receive (and acknowledge) a change for it to be considered successful.  That's the safety piece - all three must be in a quorum and have the same data (there are regular checks), and that is why it is three config servers required exactly (for now).  When you run with just one for testing, this can't happen, and your cluster meta data is essentially at risk, hence using just one is for testing purposes only.


how to build table relationships in a firebird database? I am a beginner in Delphi and I have a Firebird database with 2 tables namely masterlist and daily collection. I used Zeos 7.0.3 to access my Firebird database.

My masterlist contains the following columns:

╔══════╦══════╦═════════╦════════╗
║ name ║ date ║ balance ║ status ║
╚══════╩══════╩═════════╩════════╝

My daily collection contains the following columns:

╔══════╦══════╦═════════╦═════════╗
║ date ║ name ║ payment ║ balance ║
╚══════╩══════╩═════════╩═════════╝

I would like to build a relation in which the balance from masterlist will be copied to the balance column of the daily collection, and when I update the column in the daily collection it will also update the content of the masterlist.

Hope this will be considered a good question I have tried very hard to make a useful question.

While I'm pretty sure you should redesign your database at first (and probably read something on SQL basics), you can get what you want with triggers :)

This one inserts row into daily collection with appropriate balance:

CREATE trigger masterlist_ai for masterlist
active after insert position 0
AS
begin
  insert into "daily collection" (  "date",     balance)
                        values (new."date", new.balance);
end

And this one updates balance in masterlist after updates of daily collection (assuming date is the primary key):

CREATE trigger "daily_collection_au" for "daily collection"
active after update position 0
AS
begin
  update masterlist
  set balance = new.balance
  where "date" = new."date";
end


Shrink or no Shrink dilemma I have databases that I am tasked to move two databases DBName1 and DBName2  to SSD and I only have limited space on the SSD to fit these databases.I do not want to shrink log or data files for the obvious reason and Brent or Paul might go crazy. I noticed the database is not growing much and it is using fraction of what it is originally allocated. The initial size for log files are the current size 41GB and 147GB respectively. When I checked the DBCC SQLPERF(logspace) , I found 41017.3 MB log size and 0.4339632 % log space use % and status 0 147474MB log size and  0.08165617 log space use %  and status 0.

Database Name   Log Size (MB)   Log Space Used (%)  Status
 DBNAme1            41017.3      0.4339632           0

 DBName2          147474        0.08165617           0

Database files(Sp_Spaceused)

database_name   database_size   unallocated space
DBName1         126294.31 MB    67443.73 MB

reserved    data    index_size  unused
18261272 KB 8347376 KB  9677480 KB  236416 KB

database_name   database_size   unallocated space
DBName2        271075.31 MB      115074.44 MB

reserved    data    index_size  unused
8731200 KB  5634520 KB  12976 KB    3083704 KB

What do you think I should do. I need to be able to utilize the SSD. Is it worth shrinking or do you think of other way of reclaiming the free space? I know shrinking is the last resort.

At the risk of making an unpopular statement, I think that shrinking might be a good option for you here. Let me explain...

Shrinking a database data file will almost certainly cause fragmentation. This is critical in a production database, where you likely cannot afford the time to defrag the database after the shrink.

However, it sounds like you have some time up your sleeves. Perhaps you could restore the databases onto a non-production server where you will have ample time and resources to shrink and defrag the databases. Then you could migrate these databases to your SSD.

Of course this takes time. You might need to keep your log backups on production so you can roll forward changes, or possibly setup something like replication to keep your data in sync.

Will be interested in what other people think, and what you ultimately choose to do.


MySQL Performance: conditionnal left outer join vs where only I have three tables:

FORUM ( id PRIMARY KEY, public BOOLEAN )
MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id)
USER( id PRIMARY KEY, username VARCHAR(25) )

So, basically, one message can belong at most to one user, or can be anonymous. On the other hands, one user can have multiples messages. Messages always belong to a forum, which can be "public" or not.

I have to create this query: return true if the specified forum is public, OR if the specified forum contains at least one message from a specified user.

SQL:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    INNER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id`
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` = 5)
    LIMIT 1
) AS `boolean`

Such query works great. However my concerns is about performance. In my head, if the forum is not public, InnoDB has to do complete join between MESSAGE and USER, then filter the results with u.id = 5.

So If I wrote the query differently:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    LEFT OUTER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id` AND `u`.`id` = 5
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` IS NOT NULL)
    LIMIT 1
) AS `boolean`

The point of the second query is a new condition into the ON clause, to prevent InnoDB to do joins of the whole tables.

My question is: what's the fastest query ? Thanks!

If both tables are big enough so this is really a concern for you then fastest way is using two queries, one for each side of OR. That way you can be sure each query only accesses necessary rows, or even uses only index.

If you are worried about "sending" two queries, then you can "glue" them together using UNION - if you get at least one row, then one of those conditions was true. If result is empty, both were false.

Just a note: you can analyze your queries and see plan and estimated rows to check by using EXPLAIN.


How to Limit DTU Percentage Consumption for a Given Session in Azure SQL Database? We have an Azure SQL Database tier S4 (200 DTU), and there is one big table inside
    When I run a "alter index rebuild..." statement, it takes about 18 mins to complete

This particular nonclustered index is 5 Gb in size, and during those 18 minutes of rebuilding, it takes all 100% of DTU available to this database
Of course, this is impacting other applications that are trying to work with this database

Question:

How can we limit available resources for a given session that runs a rebuild ?
Say, we want to make it use only 50%-60% of Database's DTUs, but not 100%

Is this possible on Azure SQL Database ?

Regards,

Have you considered using the Resumable Online Index Rebuild feature so you can schedule X executions of one minute of those indexes?

ALTER INDEX [ix_CustomerIDs]
ON [ContosoSales].[ConstosoTransactionData]
REBUILD
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1 MINUTES);
GO

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] PAUSE

ALTER INDEX ix_CustomerIDs ON [ContosoSales].[ConstosoTransactionData] RESUME

Have you considered to scale up the tier to Premium to speed up things when performing index maintenance during any small maintenance window? You will pay Premium for just the hour where you scaled up and users may perceive less performance impact.


Materialized view not purging (over dblink via view) I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c):
create materialized view primary_system.workorder_mv
build immediate
refresh force
on demand
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder_vw@gistomax --view in external system that is served up by dblink (gistomax)
    --the view looks like this: select * from workorder where isgis=1

create materialized view log on external_system.workorder with rowid;

The docs say materialized views are supposed to purge automatically:

Oracle automatically tracks which rows in a materialized view log have
been used during the refreshes of materialized views, and purges these
rows from the log so that the log does not grow endlessly. Because
multiple simple materialized views can use the same materialized view
log, rows already used to refresh one materialized view might still be
needed to refresh another materialized view. Oracle does not delete
rows from the log until all materialized views have used them.

I don't have any other materialized views that reference the MV logs.
Here are the MV log properties (Toad).
Here is what all_registered_mviews looks like.
The MV has been syncing successfully. If I create or update records in the WORKORDER table in the external system, the records get synced to the primary system on the 30-second schedule.

Question:
So far, the MV log data doesn't seem to be purging automatically:

Why aren't' the MV logs automatically purging once the records are synced?

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query.
create materialized view primary_system.workorder_mv
build immediate
refresh force
on demand
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax
    where isgis=1


Why does InnoDB allow a 772 byte unique constraint? I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci

Then this error occured (which I'm already familiar with):

Specified key was too long; max key length is 767 bytes

I then tried to set the length of the VARCHAR column as high as possible without getting an error.

What suprised me was that MySQL allowed me to change the length to 191 but that would mean that the index is 8+191*4=772 bytes long but the error told that only 767 bytes are allowed.

This is the table:

CREATE TABLE `file_content`
(
  `file_content_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `local_filename` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  ...
  PRIMARY KEY (`file_content_id`),
  UNIQUE KEY `fc_dir_name` (`directory_id`,`local_filename`),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm using MySQL 5.5.27

Revised math:

8 bytes for BIGINT -- Do you really need more than 4 billion values?  Consider INT UNSIGNED at only 4 bytes.
2 byte length of VARCHAR
764 bytes for 191 characters (potentially 4 bytes per utf8mb4 character)

2+764 < 767, so the VARCHAR column passes that test
8+2+764 < 3KB, so the entire index passes another test.  (As would a pair of 191-char VARCHAR)

Edit

The reference manual has some complicated limits on indexes.  Generally you will be safe with a column taking no more than 767 bytes, and the entire index taking no more than 3072 bytes.  Keep in mind that one character in utf8mb4 counts (for these limits) as 4 bytes.  I have a compilation of limits.