Which executes first? Trigger or rule? Using PostgreSQL.

Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true.

My question is: will the rule prevent the trigger from occurring as well?

The trigger definition:

CREATE TRIGGER wypo AFTER INSERT ON "Wyp" FOR EACH ROW EXECUTE PROCEDURE funk_wyp();

The trigger function

CREATE OR REPLACE FUNCTION funk_wyp() RETURNS TRIGGER AS '
DECLARE
  dodatek real := (SELECT f.cena FROM filmy f WHERE f.ID = NEW.co);
BEGIN
    UPDATE "zyskiklient"
       SET "ile" = ile + dodatek
    WHERE kto = NEW.kto;
    UPDATE "istotnoscklienta"
       SET "poziom" = (SELECT poziom(NEW."kto"))
    WHERE "kto" = NEW.kto;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

The rule:

CREATE OR REPLACE RULE zasada_delete_konto
  AS ON DELETE TO "konto"
WHERE konto_zwrocone(OLD."ID") = FALSE
DO INSTEAD NOTHING;

You can consider a rule as transforming the command being executed, whereas a trigger is altering the data itself. (Note that this is a simplification! Spend some time reading the documentation for CREATE TRIGGER and CREATE RULE rather than trusting some random internet guy.)

So you can define a rule that is invoked when PostgreSQL sees a certain command, and transforms that command into something that would not invoke the trigger.

Take for instance a rule defined as such:

CREATE OR REPLACE RULE dont_insert AS
ON INSERT TO xyz.items
DO INSTEAD NOTHING;

And a trigger defined as such:

CREATE TRIGGER insert_with_func BEFORE INSERT
ON xyz.items FOR EACH ROW
EXECUTE PROCEDURE xyz.row_update_func();

Attempting to do an insert will not invoke row_update_func(), even if the trigger is defined as BEFORE INSERT, because the rule is modifying the command itself; after the command is re-evaluated, it's no longer doing anything, and the trigger no longer applies.

Obviously your rule does not need to be defined with INSTEAD OF, and in that case, your rule and your trigger could both apply.

With specific regard to your code, it's hard to say without knowing the definition of konto_zwrocone, but it doesn't look like it since your trigger and rule are defined for different operations on different tables.


Copy large table to archive table SQL Server 2012 I have a table with logs of my application that have 161.626.718 rows (and growing) and it's very slow to make a insert on it.
My idea is to keep only 6 months of log in the table and all older records I want to copy on a archive table that is not accessible by the application and if anyone need some info, just do a select direct on the table.
So, the idea is to create a daily job like this:

SELECT * INTO audlog_backup FROM audlog WHERE XDATE <= DATEADD(day,-185,GETDATE())

DELETE FROM audlog WHERE XDATE <= DATEADD(day,-185,GETDATE())

But because of the size of the table, this will take ages and will slow down or even lock the database for some time on the first run, after all most of the rows will be older then 6 months.

Whats the recommendation for doing it safely and faster as possible for the first time?

More information:

SQL Server 2012 Standard

Script of the create table:

CREATE TABLE [dbo].[audlog_backup](
    [PVKEY] [float] NULL,
    [DKEY] [varchar](14) NULL,
    [XDATE] [datetime] NULL,
    [XTIME] [varchar](8) NULL,
    [XFROM] [varchar](50) NULL,
    [XTO] [varchar](50) NULL,
    [DETAILS] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[audlog_backup] ADD [XUSER] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [PEOPLEKEY] [int] NULL
ALTER TABLE [dbo].[audlog_backup] ADD [ADDRESSKEY] [int] NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XFILEKEY] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XLOGNO] [int] IDENTITY(1,1) NOT NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XTABLE] [varchar](50) NULL
ALTER TABLE [dbo].[audlog_backup] ADD [XFIELD] [varchar](50) NULL
 CONSTRAINT [PK_AUDLOG_backup] PRIMARY KEY NONCLUSTERED
(
    [XLOGNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

You can use the following Stored Procedure, scheduled via a SQL Server Agent job, to archive records on a continual basis. You don't need to worry about the initial move of "everything older than 6 months" as it will be taken care of naturally by this process since it will be able to archive records faster than you are inserting them. Of course, if you want to get a large chunk of those rows archived immediately, you can run this Stored Procedure manually (while the job is not running), and just pass in parameter values that are slightly higher for @BatchSize and @SecondsToRun.

Important Notes:

Scheduled this to run via SQL Server Agent, probably once per hour
Set defaults for @BatchSize and @SecondsToRun to match how fast data is coming into the table
Create a NonClustered Index on dbo.AudLog ([XDATE] ASC)
Assuming that XLOGNO is in the main AudLog table, remove the IDENTITY specification from the XLOGNO column here (in AudLog_Backup) as that value comes from the main AudLog table
Change the datatype of the DETAILS column to be VARCHAR(MAX): text has been deprecated since the release of SQL Server 2005 and is more difficult to work with.

Minor notes:

Running this Stored Procedure, at least via the SQL Server Agent job if not also manually at first to get a large chuck of rows moved over, should be less work than swapping the table out, and there is no period of time when the AudLog table is empty.
The Stored Procedure will simply exit if there is nothing to archive, so it doesn't hurt to run once per hour, or maybe every 2 - 3 hours (depending on how fast new rows come in).
The Stored Procedure is throttled to exit after a certain number of seconds so that if it is causing contention on the main AudLog table, it will only be for short periods of time.
The OUTPUT clause binds the DELETE from the main AudLog table and the INSERT into the AudLog_Backup table in a Transaction, so you don't need to worry about the process failing, or system shutting down / crashing, between those two operations.

CREATE PROCEDURE dbo.AudLog_Archive
(
  @BatchSize INT = 2000, -- don't go above 3000 to avoid lock escalation
  @SecondsToRun INT = 180, -- run for 3 minutes
  @DaysToKeep INT = 185 -- archive rows older than 6 months
)
AS
SET NOCOUNT ON;

DECLARE @EndTime DATETIME = DATEADD(SECOND, @SecondsToRun, GETDATE()),
        @ArchiveDate DATETIME = DATEADD(DAY, @DaysToKeep * -1, GETDATE()),
        @RowsArchived INT = @BatchSize; -- initialize to be able to enter the loop

WHILE (@EndTime > GETDATE() AND @RowsArchived = @BatchSize)
BEGIN

    IF (EXISTS(
                 SELECT 1
                 FROM   dbo.AudLog al
                 WHERE  al.[XDATE] < @ArchiveDate
              )
        )
    BEGIN
        ;WITH batch AS
        (
            -- Keep this as SELECT * as it will alert you, via job failure, if
            -- you add columns to AudLog but forget to add them to AudLog_Backup
            SELECT  TOP (@BatchSize) al.*
            FROM    dbo.AudLog al
            WHERE   al.[XDATE] < @ArchiveDate
            ORDER BY al.[XDATE] ASC
        )
        DELETE b
        OUTPUT DELETED.* -- keep as * for same reason as noted above
        INTO   dbo.AudLog_Backup ([PVKEY], [DKEY], ...) -- specify all columns
        FROM   batch b

        SET @RowsArchived = @@ROWCOUNT;

        WAITFOR DELAY '00:00:01.000'; -- one second delay for breathing room
    END;
    ELSE
    BEGIN
        BREAK;
    END;
END;


SQL Server service won't start due to certificate issues Problem

We are running a SQL Server 2014 SP3 CU1 on Win 2016. After a each reboot of the host the SQL Server service won't start automatically. It always results in the following error messages.

The service is configured to start automatically.

SQL Server log:

  Error: 17190, Severity: 16, State: 1.
  Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.

  Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

  Error: 17182, Severity: 16, State: 1.
  TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support.

  TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors.

  Error: 17826, Severity: 18, State: 3.

  Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

  Error: 17120, Severity: 16, State: 1.

  SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Workaround

I could solve the issue temporarily in the following way:

connect via RDP to the host and login with the service account name
open the SQL Server Configuration Manager
start the SQL Server service

Unfortunately I can't start the service when I login with my personal login to the host. It results in the same error mentioned above.

Ideas

We already tried to delete the profile of the SQL service account. It doesn't solved the issue.

We also thought about space problems, but the host has more than 20% (> 50GB) free space available for each used drive.

What could be the issue with the certificate? How and what I have to check?

TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

// MessageId: CRYPT_E_NOT_FOUND
//
// MessageText:
//
// Cannot find object or property.
//
#define CRYPT_E_NOT_FOUND                _HRESULT_TYPEDEF_(0x80092004L)

  What could be the issue with the certificate?

The certificate is corrupt or otherwise unusable in its current state, or lacks a private key component (either corrupt or account isn't able to access).

  How and what I have to check?

A quite easy and quick check is to open up the MMC Certificates Manager console (certmgr.msc) and right-click on the certificate, choose "all tasks", "Manage Private Key", which will most likely result in an error stating that the key doesn't exist or some other random error message.

If you don't receive an error, then make sure the SQL Server Service account has permissions to read the private key (also on the manage private keys screen).

If you get an error, then SQL Server can't read the private key which means it's going to fail to start up. Thus, either remove the SSL Certificate from use for SQL Server, restore it from a good backup, or generate and use a new one.


DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group I have the following table in PostgreSQL 9.4:

CREATE TABLE dpa(
  id serial NOT NULL,
  currency_id integer,
  amount numeric(14,3),
  date timestamp without time zone,
  plat_id integer,
  pl_id integer,
  p_id integer,
  CONSTRAINT dpa_pkey PRIMARY KEY (id),
)

and settings:

work_mem = 128MB
table_size = 16 MB

And index:

CREATE INDEX idx1
  ON dpa
  USING btree
  (plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST, amount)

The table consists of approximately 242K rows. I don't have NOT NULL constraints on the column, but they are actually NOT NULL.

Now, I'm measuring performance of the queries:

I

SELECT plat_id, p_id, pl_id, player_account player_account
FROM(
    SELECT plat_id, p_id, pl_id,
    COALESCE(amount, 0) player_account,
    ROW_NUMBER() OVER (PARTITION BY plat_id, p_id, pl_id, currency_id
                       ORDER BY date DESC NULLS LAST) rn
    FROM dpa
) sub WHERE rn = 1;

Analyzed plan:

Subquery Scan on sub  (cost=0.42..25484.16 rows=1214 width=44) (actual time=0.044..296.810 rows=215274 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 27556
  ->  WindowAgg  (cost=0.42..22448.79 rows=242830 width=28) (actual time=0.043..255.690 rows=242830 loops=1)
        ->  Index Only Scan using idx1 on dpa  (cost=0.42..16378.04 rows=242830 width=28) (actual time=0.037..91.576 rows=242830 loops=1)"
              Heap Fetches: 242830

II

SELECT DISTINCT ON(plat_id, p_id, pl_id, currency_id)
       plat_id, p_id, pl_id, currency_id, amount
FROM dpa
ORDER BY plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST

Analyzed plan:

Unique  (cost=0.42..18794.73 rows=82273 width=28) (actual time=0.017..128.277 rows=215274 loops=1)
  ->  Index Only Scan using idx1 on dpa  (cost=0.42..16366.43 rows=242830 width=28) (actual time=0.016..72.110 rows=242830 loops=1)
        Heap Fetches: 242830

As can be seen, the second query is faster than the first one. But when I execute this queries in PGAdmin I got the following average statistics:

The query with ROW_NUMBER()(the first): 4999 ms

The query with DISTINCT ON(the second): 5654 ms

I understand that bandwith/latency overhead on such large result set is significant. All queries produce 215274 rows.

QUESTION: Why does it take more time to receive all rows in the second case than in the first one, although the planner shows that the second plan is more optimal?

The timing you see is given by pgAdmin (but it could be any other client) - that means it displays the time it needed to get and render the output. As you know the time needed for the database to produce the data (by using EXPLAIN ANALYZE), the difference you see must come from the transfer and/or rendering. You show less columns in the first query, that might be a reason, for example.

If you want to have an idea about how much time is consumed during the transfer, you can time the execution of the query from an application.  If you only fetch the data but don't process it in any way (not to mention render it), you get a good approximation on the time needed for transferring the data.  Just take the time needed for fetching the data and subtract the (already known) execution time of the database.

This way you will have an idea about the rendering time of pgAdmin, too, by comparing the above numbers to the one it gives you.


How to clear the data buffer cache for an Azure SQL database I want to do some performance testing on an Azure SQL database.

For SQL Server, I've read that DBCC DROPCLEANBUFFERS can be used
to clear the data buffer cache, and DBCC FREEPROCCACHE can be used to clear the execution plan cache.

But neither are supported for Azure SQL databases. For the execution plan cache, ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
seems to be an alternative. But I can't find an alternative for the data buffer cache.

How do I clear the data buffer cache for an Azure SQL database?

Azure SQL Database does not have at this time something like DBCC DROPCLEANBUFFERS available for cleaning the data on buffer pool, but scaling up or down the tier of the database has the same effect of DBCC DROPCLEANBUFFERS  and reduces the memory allocation also for the first few minutes.

Hope this helps.


How to query last restore date in SQL Server? Is there a T-SQL query that shows the last restore datetime for a certain database?

This will list all "most recent" restores for each database on your server:

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1


Will matching SQL Server network packet size to mtu increase performance We have recently upgraded our production facility to sql server 2017, and moved to clusterless availability groups. There is a primary, a secondary on site, and a remote secondary. We were experiencing periodic interruptions to synchronizations with the remote secondary. The bandwidth is low at 6G, and sql traffic competes with all other traffic. The good thing is that the AG would "catch up" after 5-15 minutes. In looking into whether there was anything I could do to improve the situation, I discovered experimentally that the network MTU was 1400, and sql's network packet size was set to the default at 4092. As an experiment I set the packet size to 1400 to match the MTU. It has been days since we had an alert about the AG, so it "seems" to have help.

My question is whether this was the right thing to do? I have read many times not to change the network packet size unless MS advises you too, and to never set it less than the default of 4096. And yet...it seems to have helped. So I am looking for input from more experienced folks in a similar situation.

TLDR: set it lower if it helps you, monitor your packet sizes to see if you are sending more packets than you need.

The MTU controls how large each network segment can be on all the points between each server, think traceroute hops - the MTU controls how large the segments are between 2 hops roughly. The smaller the MTU on a particular segment the more likely that the packet will be sent over more than one segment. This is normally fine unless you have a segment that is particularly busy.

The packet size controls how much data can be sent per TDS packet in sql. Each TDS packet has a header so some additional overhead. The smaller the packet size, the more packets you need to send your data with this caveat:

If the packets you want to send are always smaller than the max packet size then you could set max packet size to like 16k and it would make no difference because you can fit within one packet.

If you only call stored procs called “a” with no parameters and the response is only ever a stored proc status code then you could set max packet size to something tiny like 50. If you had very large requests (massive multi hundred line select statements) and lots of rows in the response then you would want as big a packet size as possible to avoid the overhead of more packet headers.

When you have a high error connection then TCP gets in the way and sends retransmits which further clog up the network and when packets go missing then everything gets held up until the missing ones sent on, so a high error rate with lots of retransmits is normally really bad for performance. Having a smaller packet size, results in more packets which results in more likelihood of lost packets which in turn means more retransmits and delays waiting for lost bits.

So is a smaller packet size bad? Normally but it really depends on your network.

I would do two things, first grab Microsoft Message Analyzer ( https://www.microsoft.com/en-gb/download/details.aspx?id=44226) and trace on each server and just look for TCP retransmits, if you have lots of these then you have a high error rate which would suggest you need to send less packets so a higher packet size.

In message analyzer you can add a column to show TDS PacketSize so if you have max packet size set to 1000 and you see lots of packets with a size of 1000 then lots of packets with size of 1 then the ideal is probably 1001 or 1002.

The second thing is look at something like tds nitro which compresses TDS so you can end up with less packets for your data which will likely help in high latency high error connections (http://nitrosphere.com/nitroaccelerator/)

Ed


Mysql upgrade to 5.7: Data too long for column I just upgraded mysql from 5.6 to 5.7 .A query used to work but after upgrading it don't seem to work anymore..The below error when i run the query on the mysql 5.7

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Data too long for column 'last_touch_by' at row 1

Can anybody help..
Thanks

Presumably the problem is that your sql_mode variable now has STRICT in it, which means that inserts/updates with varchar values that are too long for the defined length of the columns now lead to errors, whereas previously the values were truncated without any errors.

To go back to the old behaviour, you can edit your /etc/my.cnf file, look for the sql-mode variable, and remove the STRICT keyword from it. Then either restart mysqld or just log in and set sql_mode dynamically:

SET GLOBAL sql_mode='the same value as in the .cnf file here';


Change table from InnoDB to ARCHIVE in MySQL I try change my table from InnoDB to ARCHIVE with SQL command:

ALTER TABLE data_obook ENGINE = ARCHIVE;

But it always show me only error:

#1005 - Can't create table 'coincity.#sql-1350_8a' (errno: -1)

My structure:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `data_obook` (
  `market_id` int(6) NOT NULL,
  `curr_id` int(4) NOT NULL,
  `book_id` bigint(20) NOT NULL,
  `type` varchar(5) NOT NULL,
  `price` float NOT NULL,
  `amount` double NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`market_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Can somebody help me with solution? Thank you.

Since the ARCHIVE Storage Engine does not support indexes of any kind, your main problem is the presence of the  PRIMARY KEY. Simply drop the PRIMARY KEY. Then, convert the Storage Engine.

There are two approaches to this

APPROACH #1

ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;

APPROACH #2

CREATE TABLE date_obook_new LIKE date_obook;
ALTER TABLE date_obook_new DROP PRIMARY KEY;
ALTER TABLE date_obook_new ENGINE=ARCHIVE;
INSERT INTO date_obook_new SELECT * FROM date_obook;
ALTER TABLE date_obook RENAME date_obook_old;
ALTER TABLE date_obook_new RENAME date_obook;
DROP TABLE date_obook_old;

GIVE IT A TRY !!!

SPECIAL NOTE

If anyone has a PRIMARY KEY with the AUTO_INCREMENT attribute, you must remove the AUTO_INCREMENT attribute first. Then, remove the PRIMARY KEY, Then, convert the Storage Engine. For example, if market_id was defined as int(6) NOT NULL AUTO_INCREMENT, do the following:

ALTER TABLE date_obook MODIFY market_id INT NOT NULL;
ALTER TABLE date_obook DROP PRIMARY KEY;
ALTER TABLE date_obook ENGINE=ARCHIVE;


How to copy a database in sharded cluster We have a sharded MongoDB cluster, and we want to the copy current collection.

Is there a special process to do "copy collection" from a sharded cluster?

If you're using 3.2 and above you can use mongodump (Docs found here) and mongorestore (Docs found here).  Note that the docs state it can be problematic to backup sharded databases.  You will have to consider how in sync you want the data and take proper action.  Mongo docs going deeper on that can be found here.

Mongo 3.2 supports backing up sharded clusters with a snapshot, if you want to use that option check out the docs found here.

Finally, restoring the databases using the various processes can be found on Mongo's docs on restoring sharded clusters found here.  Again this will differ based on what version you're running, so be careful to read the docs.  They all go through the process in depth and clearly.

I thought you could use copyDatabase, but the docs state that it does not work with sharded databases.


Performing Calculations WIth Join I am in need of performing a calculation with a where clause based off two date fields in the joined two tables.  I have set-up the query like such, however the returned results I get are

SalesPerson SalesAmt    DiffAmt
G           22          -66

And my expected result set is

SalesPerson SalesAmt    DiffAmt
 G           11         -33

Below is sample DDL and the query I attempted to get my desired ouput.  What should be set-up differently in order to obtain my desired output?

Declare @Main Table (sn varchar(100), amt float, sd date)
Declare @Sub Table (sn varchar(100), samt float, ssd date)

Insert Into @Main (sn, amt, sd) Values
('G', 23.00, '20170601'), ('G', 21.00, '20170602'), ('G', 33.00, '20170608')

Insert Into @Sub (sn, samt, ssd) VALUES
('G', 11.00, '20170502'), ('G', 12.00, '20170601'), ('G', -1.00, '20170601')

Select
SalesPerson = m.sn
,SalesAmt = SUM(s.samt)
,DiffAmt = Sum(s.samt-m.amt)
FROM @Main m
Join @Sub s
On m.sn = s.sn
Where m.sd >= '20170601' And m.sd <= '20170605'
And s.ssd >= '20170601' And s.ssd <= '20170605'
GROUP BY m.sn

You could get aggregates before to join both tables.

;WITH g1 AS
(
    SELECT sn, SUM(samt) AS samt
    FROM   @Sub
    WHERE  ssd >= '20170601' AND ssd <= '20170605'
    GROUP BY sn
)
, g2 AS
(
    SELECT sn, SUM(amt) as amt
    FROM   @Main
    WHERE  sd >= '20170601' AND sd <= '20170605'
    GROUP BY sn
)
SELECT g1.sn AS SalesPerson,
       g1.samt AS SalesAmt,
       (g1.samt - g2.amt) AS DiffAmp
FROM   g1
JOIN   g2
ON     g1.sn = g2.sn;
GO

SalesPerson | SalesAmt | DiffAmp
:---------- | -------: | ------:
G           |       11 |     -33

dbfiddle here


MariaDB, SEQUENCE engine, dynamic upper bound I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema:

create table main (
  id integer primary key,
  -- ... other columns --
);

I've learned about MariaDB STORAGE engine for generating sequences but on the surface that's good only for generating static sequences, where you know the range at the time of writing the query, e.g.

MariaDB [skynetdb]> select * from seq_1_to_3;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.001 sec)

But how do I generate sequence whose upper bound is the result of select max(id) from main? It seems that MariaDB doesn't accept meta-queries where I would construct a name of a table to query and then query it in a single query, e.g. something like this:

-- INVALID, FOR ILLUSTRATION ONLY
select * from concat('seq_1_to_', select max(id) from main)

Or is it possible? How? (Note I'm aware that I can do one additional roundtrip and construct the sequence table name on the client, but I want to avoid it because I don't have a feature rich client, only mysql command)

The following recursive common table expression (CTE) works

MariaDB [skynetdb]> with recursive nums(n) as (select 1 union all select n+1 from nums where n <= (select max(id) from main)) select * from nums;

but it's too slow. It seems it constructs whole nums table in memory first.

SELECT seq
    FROM seq_0_to_99999999
    WHERE seq BETWEEN 12345678 and 12345688;

works fine and fast.  It returned 11 rows.

A more complex query:

SELECT MIN(FROM_DAYS(seq)) AS first_day,
       MAX(FROM_DAYS(seq)) AS last_day,
       COUNT(*) AS number_of_days
    FROM seq_0_to_99999999
    WHERE seq BETWEEN TO_DAYS('2020-01-01')
                  AND TO_DAYS(CURDATE());

Returned:

+------------+------------+----------------+
| first_day  | last_day   | number_of_days |
+------------+------------+----------------+
| 2020-01-01 | 2020-04-02 |             93 |
+------------+------------+----------------+
1 row in set (0.00 sec)

It, too, was fast, and touched only 94 rows according to the 'Handler_read%' values.  Cf: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

For your case, you may need something like

WHERE seq BETWEEN ...
              AND ( SELECT MAX(...) FROM ... )

I find that the general use of seq is to generate a generous number of values, then let WHERE go for the range I need.  The Optimizer avoids really generating the zillion values I seem to be asking for.  (At least in examples like these.)


Setup VIP for PostgreSQL Replication I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time.  I have set up replication, now as we don't want to change the database connection string in the application during failover, I need to set up VIP for the same so that I provide only one IP in the connection string.

I have used this article for setting and testing VIP. I am able to setup VIP on a machine and able to connect PostgreSQL with this VIP. But when I tried to assign the same VIP to the slave server, i got an error that cannot assign requested address bind.

I need help to setup VIP and make it work with streaming replication so that applications always point to the master server i.e. point to new master after failover.

lets suppose a case , if VIP is assigned to master and that VM goes down, how will I unassign VIP from old master and assign to a new master.

The VIP should only be assigned to one host: in a replication system, it must be assigned to the current primary/master server. Only failover or switchover should move the VIP to the standby/slave server.


bridge table joined table have both the keys equal to both the keys SELECT DISTINCT i0.id,
            i0.name
FROM main_table AS i0
   RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id"
   RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" = r4."id"

I need to get records for which other_table has name equal to both "name_1" and "name_2" or name is equal to "name_3". I have tried WHERE (r4."name" = ANY (name_1, name_2)) OR (r4."name" = name_3). it is many to many relation

I know its wrong query...
Any help will be really appreciated, thanks

Update:

CREATE TABLE main_table
(
      id   serial PRIMARY KEY,
      name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE other_table
(
      id   serial PRIMARY KEY,
      name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE bridge_table
(
      other_table_id INT NOT NULL
              REFERENCES other_table,

      main_table_id  INT NOT NULL
              REFERENCES main_table,

      PRIMARY KEY (other_table_id, main_table_id)
);

INSERT INTO main_table(id, name) VALUES (1, 'Main 1');

INSERT INTO main_table(id, name) VALUES (2, 'Main 2');

INSERT INTO main_table(id, name) VALUES (3, 'Main 3');

INSERT INTO other_table(id, name) VALUES (1, 'Other 1');

INSERT INTO other_table(id, name) VALUES (2, 'Other 2');

INSERT INTO other_table(id, name) VALUES (3, 'Other 3');

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (1, 1);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (1, 2);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (2, 2);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (3, 2);

SELECT mt.id     as mt_id,
      mt.name   as mt_name,
      ot_1.name as ot_1_name,
      ot_2.name as ot_2_name,
      ot_3.name as ot_3_name
FROM main_table AS mt
            INNER JOIN bridge_table AS bt_1 ON bt_1.main_table_id = mt.id
            INNER JOIN other_table AS ot_1 ON bt_1.other_table_id = ot_1.id

            INNER JOIN bridge_table AS bt_2 ON bt_2.main_table_id = mt.id
            INNER JOIN other_table AS ot_2 ON bt_2.other_table_id = ot_2.id

            INNER JOIN bridge_table AS bt_3 ON bt_3.main_table_id = mt.id
            INNER JOIN other_table AS ot_3 ON bt_3.other_table_id = ot_3.id
WHERE (ot_1.name = 'Other 1' AND ot_2.name = 'Other 2')
  OR (ot_3.name = 'Other 3')

I have created above sql to help generate required structure...

If I understand the task correctly, then look for

SELECT mt.id, mt.name
FROM main_table AS mt
JOIN bridge_table AS bt ON bt.main_table_id = mt.id
JOIN other_table AS ot ON bt.other_table_id = ot.id
GROUP BY mt.id, mt.name
HAVING 2 = COUNT( DISTINCT CASE WHEN ot.name IN ('Other 1', 'Other 2') THEN ot.name END )
    OR 1 = COUNT( DISTINCT CASE WHEN ot.name IN ('Other 3')            THEN ot.name END )

To make it dynamic use

-- constant part
HAVING 1 = 0
-- dynamic parts
    OR (cnt_1 = COUNT( DISTINCT CASE WHEN ot.name IN ( list_1 ) THEN ot.name END ))
    OR (cnt_2 = COUNT( DISTINCT CASE WHEN ot.name IN ( list_2 ) THEN ot.name END ))
-- ...

where list_N - values list, and cnt_N - unique values count in the list.


Percona/MySql Table does not exist after innobackupex restore on Vagrant Ubuntu 16.04 This question is cross posted here from stack overflow at the suggestion of stack overflow commenters (https://stackoverflow.com/questions/47086329/percona-mysql-table-does-not-exist-after-innobackupex-restore-on-vagrant-ubuntu)

I have an odd problem that doesn't make any sense to me. I have a mysql (percona 5.7) galera cluster that consists of 3 nodes.

Full backups are taken regularly using:
/usr/bin/innobackupex --user=${MYSQLROOT} --password=${MYSQLPASS} --no-timestamp ${BACKUP_PATH}${BACKUP_DIRNAME} --parallel=4

This works great an I have tested restoring on production with the following sequence of steps, and the restore works perfectly. The production server nodes are AWS instances based on Ubuntu 16.04 amd64. I have also tested the restore script on other AWS instances with the same setup as prod and it works perfectly:

#!/bin/bash

service mysql stop

s3cmd sync s3://${S3BUCKET}/${NEWEST_BACKUP_DIR} full_backup.tar.gz

tar -xzf full_backup.tar.gz

rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only db_backup_full/

innobackupex --apply-log db_backup_full/
innobackupex --copy-back db_backup_full/

chown -R mysql:mysql /var/lib/mysql

service mysql bootstrap-pxc

Everything works as expected so far, now in order to do some local development work, I created a Vagrant config that builds a Ubuntu 16.04 amd64 node using the exact same Ansible role that was used to build the production server. Then when I run my restore script above on my local Vagrant instance it seems like the backup file is downloaded and restored correctly. The /var/lib/mysql/ directory on my local instance looks the same as the /var/lib/mysql/ directory on my production nodes. When I login into mysql I can see all my tables when I use mysql> show tables;. However, when i try to query a table I get the following error:

mysql> select * from Record limit 100;
ERROR 1146 (42S02): Table 'franchisedirect.Record' doesn't exist
mysql>

My research tells me that the issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

However, when I look in /var/lib/mysql/ those files are present I can't see how they would not be there as the exact same restore script works on the AWS instances..

Am I missing something here ? Is this a Vagrant issue, a Percona/MySql issue, a innobackupex issue, Ubuntu 10.04 issue ? Im completely stumped, Thanks!

I figured out the answer if anyone else is reading this question. I think it's a bug with either vagrant or virtual box, basically, rebooting the virtual machine fixed the issue. Its like the restore data didn't get fully saved to disk and wasn't available until after the reboot.. I would be interested if anyone had a similar issue with virtual box or vagrant...


Get result of joining multiple tables as one row I have these 2 tables:

table1:

id | name
---------
1  | john
2  | jack

table2:

id | profile_id | institution
-----------------------------
1  | 1          | SFU
2  | 1          | UBC
3  | 2          | BU
4  | 2          | USC
5  | 2          | SFU

If I want to get all the information about a user using his userid, I can simply join them using this:

select a.id, a.name, b.institution from table1 a, table2 b
where a.id = USER_ID and a.id = b.profile_id

which for USER_ID = 1 returns:

id | name | institution
-----------------------
1  | john | SFU
1  | john | UBC

What I need is actually 1 unique row instead of multiple rows. Is it in any way possible to get something like this? (I haven't seen something to do it but am not sure)

id | name | institution
-----------------------
1  | john | [SFU, UBC]

You can use the GROUP_CONCAT function

SELECT a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
FROM table1 a INNER JOIN table2 b
ON a.id = b.profile_id
WHERE a.id = USER_ID
GROUP BY a.id, a.name;

or with your original query

select a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
from table1 a, table2 b where a.id = USER_ID and a.id = b.profile_id
group by a.id, a.name;

Give it a Try !!!


Why does SQL Server require the datatype length to be the same when using UNPIVOT? When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same.  I understand why the datatype must be the same but why does UNPIVOT require the length to be the same?
Let's say that I have the following sample data that I need to unpivot:
CREATE TABLE People
(
    PersonId int,
    Firstname varchar(50),
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

If I attempt to UNPIVOT the Firstname and Lastname columns similar to:
select PersonId, ColumnName, Value
from People
unpivot
(
  Value
  FOR ColumnName in (FirstName, LastName)
) unpiv;

SQL Server generates the error:

Msg 8167, Level 16, State 1, Line 6
The type of column "Lastname" conflicts with the type of other columns specified in the UNPIVOT list.

In order to resolve the error, we must use a subquery to first cast the Lastname column to have the same length as Firstname:
select PersonId, ColumnName, Value
from
(
  select personid,
    firstname,
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR
  ColumnName in (FirstName, LastName)
) unpiv;

See SQL Fiddle with Demo
Prior to UNPIVOT being introduced in SQL Server 2005, I would use a SELECT with UNION ALL to unpivot the firstname/lastname columns and the query would run without the need to convert the columns to the same length:
select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;

See SQL Fiddle with Demo.
We are also able to successfully unpivot the data using CROSS APPLY without having the same length on the datatype:
select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);

See SQL Fiddle with Demo.
I have read through MSDN but I didn't find anything explaining the reasoning for forcing the length on the datatype to be the same.
What is the logic behind requiring the same length when using UNPIVOT?

What is the logic behind requiring the same length when using UNPIVOT?

This question may only be truly answerable by the people who worked on the implementation of UNPIVOT. You might be able to obtain this by contacting them for support. The following is my understanding of the reasoning, which may not be 100% accurate:

T-SQL contains any number of instances of weird semantics and other counter-intuitive behaviours. Some of these will eventually go away as part of deprecation cycles, but others may never be 'improved' or 'fixed'. Quite aside from anything else, applications exist that depend on these behaviours, so backward compatibility has to be preserved.

The rules for implicit conversions, and expression type derivation account for a significant proportion of the weirdness mentioned above. I do not envy the testers who have to ensure that the weird (and often undocumented) behaviours are preserved (under all combinations of SET session values and so on) for new versions.

That said, there is no good reason not to make improvements, and avoid past mistakes, when introducing new language features (with obviously no backward compatibility baggage). New features like recursive common table expressions (as mentioned by Andriy M in a comment) and UNPIVOT were free to have relatively sane semantics and clearly-defined rules.

There will be a range of views as to whether including the length in the type is taking explicit typing too far, but personally I welcome it. In my view, the types varchar(25) and varchar(50) are not the same, any more than decimal(8) and decimal(10) are. Special casing string type conversion complicates things unnecessarily and adds no real value, in my opinion.

One could argue that only implicit conversions that might lose data should be required to be explicitly stated, but there are edge-cases there too. Ultimately, a conversion is going to be needed, so we might as well make it explicit.

If the implicit conversion from varchar(25) to varchar(50) were allowed, it would just be another (most likely hidden) implicit conversion, with all the usual weird edge cases and SET setting sensitivities. Why not make the implementation the simplest and most explicit possible? (Nothing is perfect, however, and it is a shame that hiding varchar(25) and varchar(50) inside a sql_variant is allowed.)

Rewriting the UNPIVOT with APPLY and UNION ALL avoids the (better) type behaviour because the rules for UNION are subject to backward compatibility, and are documented in Books Online as allowing different types so long as they are comparable using implicit conversion (for which the arcane rules of data type precedence are used, and so on).

The workaround involves being explicit about the data types and adding explicit conversions where necessary. This looks like progress to me :)

One way to write the explicitly-typed workaround:

SELECT
    U.PersonId,
    U.ColumnName,
    U.Value
FROM dbo.People AS P
CROSS APPLY
(
    VALUES (CONVERT(varchar(50), Lastname))
) AS CA (Lastname)
UNPIVOT
(
    Value FOR
    ColumnName IN (P.Firstname, CA.Lastname)
) AS U;

Recursive CTE example:

-- Fails
WITH R AS
(
    SELECT Dummy = 'A row'
    UNION ALL
    SELECT 'Another row'
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

-- Succeeds
WITH R AS
(
    SELECT Dummy = CONVERT(varchar(11), 'A row')
    UNION ALL
    SELECT CONVERT(varchar(11), 'Another row')
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

Finally, note that the rewrite using CROSS APPLY in the question is not quite the same as the UNPIVOT, because it does not reject NULL attributes.


compound array index mongodb I just realised you can't have a compound index with more than one array with mongodb.
I'm doing a tags,categories and actors search and I have simply stored them as arrays in the document example :

{
url = whatever.com
tags = [tag1,tag2]
actors = [actor1]
categories = [categorie1,categorie2]
}

How should I store them to make the search as fast as possible ? A search of this form:

tag1 AND tag 2 ... AND tagN AND categorie1 AND categorie2 ... AND categorieN AND actor1 AND actor2 ... AND actorn

Where the documents returned would be the videos that all have AT LEAST(so all) those criterias.

You're correct that MongoDB can index only one array per document. This is called Multikey index. The limitation is due to the fact that MongoDB will create one index entry per array element.

So for example if you have an array with 10 elements, MongoDB will create 10 index entries for that single document. If you index more than one array, you can imagine that the number of index entries will go up exponentially, making the overall index impractically large.

One solution to your question is to refactor your document to use only one array, with the key tag, actors, and categories acting as a sub-document key. For example:

> db.test.find()
{
  "_id": ObjectId("597820ef2dbe1d773beb11d4"),
  "url": "whatever.com",
  "values": [
    {"tag": "tag1"},
    {"tag": "tag2"},
    {"actor": "actor1"},
    {"actor": "actor2"},
    {"category": "category1"},
    {"category": "category2"}
  ]
}

You can index this document easily since it only contains one array.

To search for the condition you proposed: tag1 AND tag 2 ... AND tagN AND categorie1 AND categorie2 ... AND categorieN AND actor1 AND actor2 ... AND actorn, you can use the $all operator. For example:

> db.test.find({
    values: {$all: [
        {tag: 'tag1'},
        {tag: 'tag2'},
        {actor: 'actor1'},
        {actor: 'actor2'},
        {category: 'category1'},
        {category: 'category2'}
    ]}
})

Which will return the document shown above.

Please see Query an Array for more methods in querying an array in MongoDB.

Also please see Explain results for methods to observe and optimize your query performance.


Unable to shrink transaction log file in SQL Server I moved to a new company recently wherein they have 2 databases in a SQL server of size 20-25 GB. I'm unable to shrink the log file of the databases.
Transaction log backup of the databases are set to run every 30 mins from 6 AM-7PM.
They are of size 10 and 2 Gb respectively.
I tried to shrink it around 8-9 PM last night but I was not able to do.

The log_reuse_wait column stated it's waiting on a log_backup.
I tried taking couple of log backups which didn't help.
I ran DBCC OPENTRAN but there are no open transactions.

Also, this morning even after the Tlog backups started at 6:00 AM till now(10:30) it still shows LOG_BACKUP in log_reuse_wait column.
And no OPEN TRAN as well.

When I researched on this I found this article from Paul Randal:
http://www.sqlskills.com/blogs/paul/why-is-log_reuse_wait_desc-saying-log_backup-after-doing-a-log-backup/

It states that if the DB is small and all the content of tlog file is in same VLF it couldn't truncate.
I have some 300-400 VLFs in the Databases.
So that doesn't make sense here as well.

One thing I noticed that every morning at 6 AM the tlog is a little bigger than the other ones.
So I'm thinking to check with the lead DBA to run tlog round the clock.
Other than that, I would like to see why is this happening and Am i missing anything.

I tried to shrink with the below command:

Use DB1
go
DBCC SHRINKFILE(2,10)
GO

Note: It's SQL 2012 instance running 0n 2012 compatibility

The reason your process isn't working is that it's waiting to release the inactive portion of the log using a transaction log backup. Switching to Simple Recovery Model bypasses this issue.

If you really need to shrink down the transaction log, so that you can regrow it (to reduce the number of VLFs, for instance), this is the process:

1) Switch to Simple Recovery Model

2) Run a CHECKPOINT and DBCC DROPCLEANBUFFERS (just in case)

3) Shrink the log file

4) Switch back to Full Recovery Model

5) Grow the log in reasonable increments (I use 4000MB increments)

6) Set autogrowth to a value your I/O subsystem can handle

7) Run a full or differential backup to restart the log backup chain.


Modifying table structure within a transaction? In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction.

We have a tool (written in Java) that should modify a schema by:

adding some columns
removing some columns
updating a description table with the new schema layout

The tool as written will display the current schema layout to the user and allow him to modify it. (Basically adding or removing custom "attribute" columns to some tables) Once he's satisfied, he can apply his changes.

Please note: The basic schema layout, and the fact that you need to ALTER TABLE to change some things, is predefined by a standard and cannot be changed by us, as other tools wouldn't work anymore.

The problem now is that we cannot run these changes in a single transaction since, AFAIK, it's not possible to do multiple ALTER TABLE statements within a transaction.

What options do we have to "roll back" to the initial state if something goes wrong while applying the changes?

Note: Someone here proposed RESTORE POINT + FLASHBACK -- is it a good idea to call this from this (Java) tool? (We do not fully control the database instance at some sites where the tool should be used.)

Note: Oracle 10g2 and above

As DDL implicitly commits, the only way to "rollback" your changes is to construct the reverse operation and apply it to revert the change, as a_horse_with_no_name states.

Constructing such a rollback won't always be straightforward however. If data could be written to the table between type modifications (varchar2(10) -> varchar2(50), number -> varchar2) and rolling this back then you'll also have to check the new data will be valid when reverting to the original type (or perform some conversion). Be aware that dropping columns on large tables could take some time and generate large amounts of redo.

You'll also have to be wary of invalidating any stored procedures on your database and other application dependencies as a result of these changes.

The flashback option won't help you in this instance. Once you've made DDL changes to a table, you can't restore it to it's previous state using flashback. Trying to do so will give you the error:

ORA-01466: unable to read data - table definition has changed

Flashing-back your full database would be overkill and also not possible from the Java app - you need to shut down and then mount the database to complete this operation.

Which all raises the question of what your tool is for. If you just need a GUI for people to edit tables, then something like Oracle SQL Data Modeler can do this and generate DDL scripts for you. These can then be validated, tested, an appropriate rollback constructed and applied to the database. Modifying the structure of a (production) database should be done with care and tested to ensure that all changes are valid!


optimization of expensive subquery The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:

SELECT
    reports.*
FROM
    reports
    INNER JOIN
        units
        ON units.id = reports.unit_id
WHERE
    reports.unit_id IN (1111, 1112, 1113)
    AND
    (
        reports.id =
        (
            SELECT reports.id
            FROM reports
            WHERE reports.unit_id = units.id
            AND
            reports.time_secs != 0
            AND
            reports.latitude != 0.0
            AND
            reports.longitude != 0.0
            ORDER BY time desc
            LIMIT 1
        )
    )

This query took several minutes to run, and I was wondering if there is an optimization I can do to it.

This can be considerably simpler and faster with DISTINCT ON:

SELECT DISTINCT ON (r.unit_id)
       r.*
FROM   reports r
JOIN   units   u ON u.id = r.unit_id
WHERE  r.unit_id IN (1111, 1112, 1113)
AND    r.time_secs <> 0
AND    r.latitude  <> 0.0
AND    r.longitude <> 0.0
ORDER  BY r.unit_id, r.time DESC;

More explanation in these related answers:
How do I efficiently get "the most recent corresponding row"?
Select first row in each GROUP BY group? (on SO)

Minor detail: the standard SQL operator for "is not equal" is <>. Use that rather than != (which is accepted as well in Postgres).

The only possible purpose of joining to the table units in this query is to verify that one or more related rows exist. If reports.unit_id is bound to unit.id by foreign key (as the naming suggests) referential integrity is guaranteed and you can drop the table unit from the query completely. Just add: unit_id IS NOT NULL.

SELECT DISTINCT ON (unit_id) *
FROM   reports
WHERE  unit_id IN (1111, 1112, 1113)
AND    time_secs <> 0
AND    latitude  <> 0.0
AND    longitude <> 0.0
AND    unit_id IS NOT NULL
ORDER  BY unit_id, time DESC;


Stored Procedure is not returning complete result I am using the query as mentioned in the answer accepted in the question located at Stored Procedure doesn't show result in SQL Server

Now when I run this SP with the values provided in the parameter, the SP shows results only of specific parameter values when returning the result. Can there be any chance of values getting terminated? The parameter values are multiple and comma seperated.

Below is the SP;

    ALTER PROCEDURE [dbo].[sp_tst_JulieExportCSENGOnly]
    @ProjectHierarchy NVARCHAR(max),
    @ProjectHomeGroup NVARCHAR(max),
    @ProjName NVARCHAR(max),
    @ProjOwn NVARCHAR(max),
    @TimeByDay DATETIME,
    @TimeByDay2 DATETIME
AS
   SET NOCOUNT ON

   BEGIN

   DECLARE @ProjectHierarchyTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjectHomeGroupTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjNameTbl TABLE ( Value NVARCHAR(MAX))
   DECLARE @ProjOwnTbl TABLE ( Value NVARCHAR(MAX))

    INSERT  INTO @ProjectHierarchyTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHierarchy) ;

   INSERT  INTO @ProjectHomeGroupTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjectHomeGroup) ;

    INSERT  INTO @ProjNameTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjName) ;

    INSERT  INTO @ProjOwnTbl
        ( Value )
        SELECT  LTRIM(RTRIM(s))
        FROM    dbo.Split(',', @ProjOwn) ;

   SELECT
  [IR.TimesheetProjectHoursByDay].[Project Departments]
  ,[IR.TimesheetProjectHoursByDay].ProjectName
  ,[IR.TimesheetProjectHoursByDay].ProjectOwnerName
  ,[IR.TimesheetProjectHoursByDay].[Project Hierarchy]
  ,[IR.TimesheetProjectHoursByDay].[Project Home Group]
  ,[IR.TimesheetProjectHoursByDay].[CSENG Project Type]
  ,[IR.TimesheetProjectHoursByDay].TaskName
  ,[IR.TimesheetProjectHoursByDay].[Capital Project]
  ,[IR.TimesheetProjectHoursByDay].[Capital Task]
  ,[IR.TimesheetProjectHoursByDay].TimeByDay
  ,[IR.TimesheetProjectHoursByDay].ResourceName
  ,[IR.TimesheetProjectHoursByDay].ActualWork
  ,[IR.TimesheetProjectHoursByDay].[Resource Home Group]
  ,[IR.TimesheetProjectHoursByDay].[Resource Cost Center]
  ,[ResourceStandardRate]
FROM
  [IR.TimesheetProjectHoursByDay]
WHERE
  [IR.TimesheetProjectHoursByDay].[Project Departments] LIKE N'CSENG'
  AND ActualWork > 0
  AND [IR.TimesheetProjectHoursByDay].[Project Hierarchy] IN (SELECT VALUE FROM @ProjectHierarchyTbl)
  AND [IR.TimesheetProjectHoursByDay].[Project Home Group] IN (SELECT VALUE FROM @ProjectHomeGroupTbl)
  AND ProjectName IN (SELECT VALUE FROM @ProjNameTbl)
  AND ProjectOwnerName IN (SELECT VALUE FROM @ProjOwnTbl)
  AND [IR.TimesheetProjectHoursByDay].TimeByDay >= @TimeByDay
  AND [IR.TimesheetProjectHoursByDay].TimeByDay <= @TimeByDay2

   END

Thanks!

The split function you use truncate the valuelist to varchar(512). There are several split functions to choose from in this answer and here is another.

create function dbo.Split(@sep char(1), @s varchar(max))
returns @T table(s varchar(max))
as
begin
  while len(@s) > 0
  begin
    insert into @T values(left(@s, charindex(@sep, @s+@sep)-1))
    set @s = stuff(@s, 1, charindex(@sep, @s+@sep), '')
  end
  return
end


SQL Server Permission Denied Error When I'm DBOwner? So I created an account, gave him dbcreator and securityadmin privileges and then through that account tried to run CREATE TABLE on a database that I had created and now owned, but was getting a permission denied error.  Elevating the account to Sysadmin fixed the problem, but is there a way to do this without endowing this account with the Almighty sysadmin privilege?

This is on SQL Server 2008 R2.  This database and table was created via SharePoint 2010 (but applies more to SQL than SharePoint I think).

From what I see you created a server login with server roles (DBCreator and SecurityAdmin are server roles). But this doesn't mean it already has privileges on an existing database.

What you need to do is to create a user in that database and map it to the login:

use your_db
create user x for login y
exec sp_addrolemember 'db_owner', 'x'

This will assign your new user the database role db_owner to the database. Now you should be able to create tables with the login. You can try also other roles, if you don't need to be the db owner.


Combine two event tables into a single timeline Given two tables:

CREATE TABLE foo (ts timestamp, foo text);
CREATE TABLE bar (ts timestamp, bar text);

I wish to write a query that returns values for ts, foo, and bar that represents a unified view of the most recent values. In other words, if foo contained:

ts | foo
--------
1  | A
7  | B

and bar contained:

ts | bar
--------
3  | C
5  | D
9  | E

I want a query that returns:

ts | foo | bar
--------------
1  | A   | null
3  | A   | C
5  | A   | D
7  | B   | D
9  | B   | E

If both tables have an event at the same time, the order does not matter.

I have been able to create the structure needed using union all and dummy values:

SELECT ts, foo, null as bar FROM foo
UNION ALL SELECT ts, null as foo, bar FROM bar

which will give me a linear timeline of new values, but I'm not quite able to work out how to populate the null values based on the previous rows. I've tried the lag window function, but AFAICT it will only look at the previous row, not recursively backward. I've looked at recursive CTEs, but I'm not quite sure how to set up the start and termination conditions.

Use a FULL [OUTER] JOIN, combined with two rounds of window functions:
SELECT ts
     , min(foo) OVER (PARTITION BY foo_grp) AS foo
     , min(bar) OVER (PARTITION BY bar_grp) AS bar
FROM (
   SELECT ts, f.foo, b.bar
        , count(f.foo) OVER (ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (ts)
   ) sub;

Since count() does not count NULL values it conveniently only increases with every non-null value, thereby forming groups that will share the same value. In the outer SELECT, min() (or max()) likewise ignores NULL values, thereby picking the one non-null value per group. Voilá.
Related FULL JOIN case:

Add up conditional counts on multiple columns of the same table

It's one of those cases where a procedural solution might just be faster, since it can get the job done in a single scan. Like this plpgsql function:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column
DECLARE
   last_foo text;
   last_bar text;
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
      ORDER  BY 1
   LOOP
      IF foo IS NULL THEN foo := last_foo;
      ELSE                last_foo := foo;
      END IF;

      IF bar IS NULL THEN bar := last_bar;
      ELSE                last_bar := bar;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

Call:
SELECT * FROM f_merge_foobar();

db<>fiddle here, demonstrating both.
 Old sqlfiddle.
Related answer explaining the #variable_conflict use_column:

Naming conflict between function parameter and result of JOIN with USING clause


MariaDB remote access ERROR 1045 (28000): Access denied MariaDB 10.3.27.
Debian 10 Buster.
I've followed the instructions to grant remote access to a user to my remote MariaDB server:

Edited /etc/mysql/my.cnf:
 [mysqld]
 #skip-networking
 #bind-address = <some ip-address>
 skip-networking=0
 skip-bind-address

Created a user:
 CREATE USER 'theuser'@'%' IDENTIFIED BY 'mypass';
 GRANT ALL PRIVILEGES ON *.* TO 'theuser'@'%' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
 SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
 +---------+------+
 | User    | Host |
 +---------+------+
 | theuser | %    |
 +---------+------+

Restarted MariaDB:
 $ sudo systemctl restart mariadb
 $ systemctl status mariadb
 ● mariadb.service - MariaDB 10.3.27 database server
    Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Active: active (running) since Mon 2021-05-17 01:00:59 -03; 20min ago

Opened the 3306 port:
 $ sudo ufw allow 3306/tcp
 $ sudo ufw status | grep 3306
 3306                       ALLOW       Anywhere
 3306/tcp                   ALLOW       Anywhere

Checked open port locally:
 $ nmap -p3306 remote_ip
 PORT     STATE SERVICE
 3306/tcp open  mysql

But when I try to access from my local machine to the remote server:
$ mysql -utheuser -pmypass -hremote_ip
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'theuser'@'my_ip' (using password: YES)

I'm missing something or there are some errors I can't see?

The problem was simple, the password in the command line has a special character, the dollar sign $ which is not escaped by the shell unless in single quotes. So the proper command is:
$ mysql -utheuser -p'mypass' -hremote_ip

Nonetheless, security issues are arises when exposing the password in the shell:

6.1.2.1 End-User Guidelines for Password Security
MySQL users should use the following guidelines to keep passwords
secure.
When you run a client program to connect to the MySQL server, it is
inadvisable to specify your password in a way that exposes it to
discovery by other users. The methods you can use to specify your
password when you run client programs are listed here, along with an
assessment of the risks of each method. In short, the safest methods
are to have the client program prompt for the password or to specify
the password in a properly protected option file.


Database design to track student attendance I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students.

Creating a database for attendance to track their attendance will have almost 4000*30days*12months => ~ 1,400,000 rows (neglecting vacations/ sundays).

The attendance table will have:

id (INT)
student_id (INT)
course_id (INT)
data (DATETIME)
present/absent (TINYINT, as I'll store 1/0)
comments

I'm using PHP/MySql. With a table becoming so large, is there any other way?

To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.

Most systems I've seen store either the that the student is present (Positive attendance) or the student is absent (Negative attendance, although rarely referred to by that name). The decision on which to use came down to the structure and reporting requirements of the school (and whoever they report to).

If you're school is concerned when the student is absent for reasons like having to track Truant students, you should use a standard (negative) attendance structure. Most K-12 schools in the US  do it this way.

If you're school only awards credit for courses after a verified number of hours have been completed (e.g. Johnny doesn't pass the course until he's completed his coursework and attended 12 sessions of the class), then a positive attendance structure could be used.

Assuming your data looked like this: (Ignoring the course_id in your example for simplicity)

Attendance:
id | student_id | date       |
------------------------------
1  | 2          | 2013-10-24 |

Students:
student_id | student_name   |
-----------------------------
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |

To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id AND a.date = '2013-10-24'

Which would return:

Result :
student_id | student_name   | attendance_status |
-------------------------------------------------
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |


Delete all highscores from a certain country I have two tables:

tbl_players
tbl_highscores

I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by highscore. I can gather the information with JOIN like this:

SELECT scorePoints, playerAlias, playerEmail, playerCountry
    FROM tbl_highscores JOIN tbl_players ON tbl_highscores.playerId = tbl_players.playerId
    WHERE playerCountry = 'lt'

But how do I delete all highscores that are saved by a user from a certain language?

DELETE B.* FROM
(
    SELECT playerId FROM tbl_players
    WHERE playerCountry = 'lt'
) A INNER JOIN tbl_highscores B
USING (playerId);

Make sure you have this index

ALTER TABLE tbl_players ADD INDEX CountryIDIndex (playerCountry,playerId);


Frequently Querying the DMV - sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_exec_requests Could frequently (e.g. 5 seconds) querying the below DMVs from a custom tool to get the connection info impact performance on SQL server? I understand this would give a snapshot at that point.
SQL audit logs probably the best way to get this info but It would help if I get any guidance based on your experience with the DMV.

select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName,
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,sys.dm_exec_sessions.status,r.status, text, *
from sys.dm_exec_sessions inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)

It depends on the amount of connections and your hardware + your ability to change the query.

The first thing you should do is look at the query and decide which columns you need and which columns you don't need.

If we run the query with

set statistics io, time on;

On an idle server with < 100 connections

select count(*) from sys.dm_exec_connections

Result

20

The result is clear:

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 18 ms.

The logicalreads are also very small (thanks statisticsparser):

This should mean that the query is harmless on this server,even if we run it every 5 seconds.

On a prod server with over 10k connections

select count(*) from sys.dm_exec_connections

Result

13713

Which means that every 5 seconds, around 300 - 400 ms cpu time will be used:

 SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 485 ms.

And that amounts to the query running 1/10th of every 5 seconds.

However, if you can change the query, either by omitting some columns, or using a temp table, the overhead could be lowered by a lot.

E.G. Splitting up the query in two parts and removing some columns.

set statistics io, time on;
select sys.dm_exec_sessions.session_id, host_name as [Connecting machine name], client_net_address AS [Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name,
sys.dm_exec_sessions.status AS [Session status],command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.connection_id
INTO #temp
from sys.dm_exec_sessions
inner join sys.dm_exec_connections on sys.dm_exec_sessions.session_id = sys.dm_exec_connections.session_id
inner join sys.dm_exec_requests r on sys.dm_exec_connections.connection_id = r.connection_id

select #temp.session_id, #temp.[Connecting machine name], #temp.[Connecting machine IP],
program_name,client_interface_name,login_name,nt_domain,nt_user_name, name as DatabaseName,
#temp.[Session status],#temp.command,r.Status AS [Request Status],
last_request_start_time,last_request_end_time,r.status, text,  *
FROM #temp
inner join sys.dm_exec_requests r on #temp.connection_id = r.connection_id
inner join sys.databases on r.database_id = sys.databases.database_id
cross apply sys.dm_exec_sql_text(r.sql_handle)

DROP TABLE #temp

Resulting in 10X lower amount of CPU usage.

Extra testing

On a server with 600 connections

select count(*) from sys.dm_exec_connections

600

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 40 ms.

About twice as much cpu time as with 20 connections.

Testing every 5 seconds on the idle server

If we add two jobs that run every 10 seconds.

(with 5 seconds between them because we cannot go lower than each 10 seconds scheduling.)

And watch the result, The jobs started executing at ~9 AM on a server with 2 cores @3,5GHZ:

The cpu usage difference is not visible, before and after the 9AM mark.

Server activity remains closely the same

DB IO.

Wait stats went up with a negligible 20ms on ASYNC_NETWORK_IO.


Give particular privileges for a table to existing role My database have some tables such as:

Table A
Table B
Table C

I'm using user monitoring with read-only permission.

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name= 'Table A'

I want to set monitoring user with insert, update, delete, truncate, alter table, trigger permission for only table Table A. The another table don't affect. How to implement this?

Use GRANT.

GRANT INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON table A TO monitoring;

The role needs privileges for the DB and the schema, too, but since it already has the SELECT privilege, that should be the case. And if you want to keep SELECT and REFERENCES in the mix, you can just:

GRANT ALL ON table A TO monitoring;

But only superuser or the owner of a table can ALTER TABLE. The manual:

  You must own the table to use ALTER TABLE.

You would have to make the role monitoring the owner. (Do you really want that??)

ALTER TABLE A OWNER TO monitoring;

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


PostgreSQL how to return data from anonymous code block? I have a table mytable (id, "someLongURI", status, "userId") and a working query:

UPDATE          mytable
SET             status = 'IN_WORK'
WHERE           "someLongURI" IN (
    SELECT      "someLongURI"
    FROM        mytable
    WHERE       status = 'UNUSED'
    AND         pg_try_advisory_xact_lock(id)
    ORDER BY    id ASC
    LIMIT       1
    FOR UPDATE
)
RETURNING       id, "someLongURI";

But now I need to check the "userId" parameter and based on this either select existing row or update (and receive updated row). Something like that (in MySQL this would work):

IF NOT EXISTS (
    SELECT          1
    FROM            mytable tbl
    WHERE           tbl."userId" = 123
)
THEN
    UPDATE          mytable tbl
    SET             tbl.status = 'IN_WORK',
                    tbl."userId" = 123
    WHERE           tbl."someLongURI" IN (
        SELECT      "someLongURI"
        FROM        tbl.mytable
        WHERE       tbl.status = 'UNUSED'
            AND     pg_try_advisory_xact_lock(id)
        ORDER BY    id ASC
        LIMIT       1
        FOR UPDATE
    )
    RETURNING   tbl.id, tbl."someLongURI";
ELSE
    SELECT          tbl.id, tbl."someLongURI"
    FROM            mytable tbl
    WHERE           tbl."userId" = 123;
END IF;

I've wrapped it with DO $$ BEGIN {...} END $$; but it didn't work for     me. Last thing I came to:

DO
    RETURNS TABLE (id INT4, "someLongURI" TEXT) AS
$$
DECLARE
    "_userId" INT4;
BEGIN
    "_userId" = 123;

    IF NOT EXISTS (
        SELECT          1
        FROM            mytable tbl
        WHERE           tbl."userId" = "_userId"
    )
    THEN
        UPDATE          mytable tbl
        SET             tbl.status = 'IN_WORK'
                        tbl."userId" = "_userId"
        WHERE           tbl."someLongURI" IN (
            SELECT      "someLongURI"
            FROM        tbl.mytable
            WHERE       tbl.status = 'UNUSED'
                AND     pg_try_advisory_xact_lock(id)
            ORDER BY    id ASC
            LIMIT       1
            FOR UPDATE
        )
        RETURNING       tbl.id, tbl."someLongURI";
    ELSE
        SELECT          tbl.id, tbl."someLongURI"
        FROM            mytable tbl
        WHERE           tbl."userId" = "_userId";
    END IF;
END $$;

Can't understand where I need to add "RETURNS" or some other statements inside this anonymous code block to receive the results of query

With Postgres you could do that in a single data modifying CTE without the need for an IF statement:

WITH data AS (
    SELECT          tbl.id, tbl."someLongURI"
    FROM            mytable tbl
    WHERE           tbl."userId" = 123;
), changed AS (
    UPDATE          mytable tbl
    SET             tbl.status = 'IN_WORK',
                    tbl."userId" = 123
    WHERE           tbl."someLongURI" IN (
        SELECT      "someLongURI"
        FROM        tbl.mytable
        WHERE       tbl.status = 'UNUSED'
            AND     pg_try_advisory_xact_lock(id)
        ORDER BY    id ASC
        LIMIT       1
        FOR UPDATE
    )
    AND NOT EXISTS (SELECT * FROM data)
    RETURNING   tbl.id, tbl."someLongURI"
)
SELECT *
FROM changed
UNION ALL
SELECT *
FROM data
WHERE NOT EXISTS (SELECT * FROM changed);


Can I move SQL Server backup files on a running database? Our backup drive is filling up with TRN and BAK files. I have administrative privileges on the server, but I don't have a login to the database.

Can I move the oldest files off to somewhere else without causing service interruption? Which ones?

In general, yes, you can move the backup files.  Backup files are not in use by SQL Server once the associated backup is complete.  .BAK files are typically full or differential backups, and .TRN are typically transaction log backups, and they can all be moved.  It is certainly possible to use different file extensions for the backups, but it's not common.

Please be aware, though, that there may be other processes that use the backup files, such as log shipping or automated restores to non-production environments.  There is, of course, no way we can tell you what may be happening in your environment, so we can't tell you what might break if you move files.  If you are just moving the oldest files, then it's less likely you'll be interrupting something.


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

MySQL table: jti_member_interact

CREATE TABLE `jti_member_interact` (
  `INT_MEMBER_ID` int(11) NOT NULL,
  `INT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `INT_SOURCE` varchar(1) NOT NULL,
  `INT_DATE` datetime NOT NULL,
  `INT_TYPE` varchar(255) NOT NULL,
  `COPY_TO_STG` varchar(12) DEFAULT 'NO',
  `NEW_STG_SEQ` int(11) DEFAULT NULL,
  `COPY_TO_STG_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`INT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=43670 DEFAULT CHARSET=utf8$$

Oracle table: MEMBER_INTERACT_MYSQL_STG

  CREATE TABLE "JTI_HTP"."MEMBER_INTERACT_MYSQL_STG"
   (    "INT_MEMBER_ID" NUMBER(10,0) NOT NULL ENABLE,
    "INT_ID" NUMBER(10,0),
    "INT_SOURCE" NVARCHAR2(1) NOT NULL ENABLE,
    "INT_DATE" DATE,
    "INT_TYPE" NVARCHAR2(30) NOT NULL ENABLE,
    "INSERTING_DATE" DATE,
    "MYSQL_ID" NUMBER(12,0)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Basically, I needed to copy jti_member_interact into member_interact_mysql_stg with adding new columns as follow:

MEMBER_INTERACT_MYSQL_STG.STG_INT_ID = NEW_SEQ
MEMBER_INTERACT_MYSQL_STG.MYSQL_INT_ID = MEMBER_INTERACT.INT_ID
MEMBER_INTERACT.COPY_TO_STG = 'YES', once the copy operation is completed.
MEMBER_INTERACT.NEW_STG_SEQ = MEMBER_INTERACT_MYSQL_STG.STG_INT_ID

I have created the following procedure:

CREATE OR REPLACE PROCEDURE COPY_MYSQL_WEB_INT_TO_STG(
P_BATCH_NO IN NUMBER)
IS
BEGIN
    INSERT INTO MEMBER_INTERACT_MYSQL_STG
    SELECT "INT_MEMBER_ID",
      STG_SEQ.NEXTVAL,
      "INT_SOURCE",
      "INT_DATE",
      "INT_TYPE",
      CURRENT_DATE,
      "INT_ID"
    FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" des
    WHERE "NEW_STG_SEQ" IS NULL;
    COMMIT;
    FOR REC IN (SELECT  STG_INT_ID, INSERTING_DATE, MYSQL_INT_ID FROM MEMBER_INTERACT_MYSQL_STG)
    LOOP
        UPDATE "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE" SRC
        SET "COPY_TO_STG"     = 'YES',
            "NEW_STG_SEQ"     = REC.STG_INT_ID,
            "COPY_TO_STG_DATE" = REC.INSERTING_DATE
        WHERE SRC.INT_ID = REC.MYSQL_INT_ID;
    END LOOP;
--    COMMIT;
END;

The procedure compiles successfully. However, when I run the procedure, I get the following error:

[MySQL][ODBC 5.1 Driver][mysqld-5.5.31-30.3-log]Unknown column 'jtipartn_mydb.jti_member_interact.INT_ID' in 'where clause' {42S22,NativeErr = 1054}
ORA-02063: preceding 2 lines from JTIPARTN_WEBSITE

Apparently, the error I get has to do with MySQL table jti_member_interact specially, the column INT_ID. I double checked the name, it is correct! I tried to change it to something else but I got the same error.

I search this error and I found it quite common in some MySQL versions (version 5). I could not find a way to fix my problem (I do not use any trigger here or USING statement). In addition, my knowledge in MySQL is very limited. More interesting is that I ran the same procedure on another MySQL table that has identical structure and column names and it worked perfectly!

When I query the column in normal query such as:

SELECT INT_ID
FROM "jtipartn_mydb"."jti_member_interact"@"JTIPARTN_WEBSITE";

I get NO errors on both DB's (oracle & MySQL)! Only UPDATE statement cause errors.
In addition, I tried instead of SRC.INT_ID in where clause to use "INT_ID". I got the same error.
Using without double quote has the same the problem.
I have created another identical MySQL table and tried to update it and I got the same error.
The only difference between the table that I can update successfully and the the table I cannot, is that they belong to different MySQL databases (in different locations). But I do not know how to solve this problem.

by experience sometimes creating oracle tables by using ODBC produce errors.

so please try to do the following:

create the table manually on oracle db by using sql plus.
try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name)
try to run your application to check if any errors appears.

also in where clause you have to check it.
try:

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;

does it works?


Can Postgres index regular expressions stored in a column? I've got regular expressions stored as varchars in a column, which I need to match against incoming input. For example, the table might contain:

| field |     value     |
|-------|---------------|
| email | .*@domain.com |

And a query would be:

SELECT *
FROM table
WHERE field = 'email'
  AND 'someone@domain.com' ~* value

I'll be the first to admit this is pretty silly, though it did well enough for about 2 years. The table's now up to a mindshattering 10k rows, and queries slowed to on the order of 3 seconds. I've already moved us to a much more reasonable strategy, so this question is purely academic.

If I had kept this setup, is there any way to make the lookup efficient? I was hoping for some sibling of varchar_pattern_ops, but this query is the reverse of what that solves.

With the idea down, here's the full table, query, and explain.

+------------+-----------------------------+------------------------------------------------------------+
| Column     | Type                        | Modifiers                                                  |
|------------+-----------------------------+------------------------------------------------------------|
| id         | integer                     | not null default nextval('table_id_seq'::regclass)         |
| field      | character varying(255)      | not null                                                   |
| value      | character varying(1000)     | not null                                                   |
| comment    | text                        |                                                            |
+------------+-----------------------------+------------------------------------------------------------+
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)
    "index_table_on_field_and_value" UNIQUE, btree (field, value)

EXPLAIN ANALYZE
SELECT *
FROM table
WHERE (
    (field = 'contact_email' AND 'person@place.com' ~* value)
 OR (field = 'phone'         AND value = '1234567890')
 OR (field = 'unique_id'     AND value = 'abcdef')
);

Seq Scan on table (cost=0.00..613.08 rows=58 width=1) (actual time=744.371..744.371 rows=0 loops=1)
Filter: ((((field)::text = 'contact_email'::text) AND ('person@place.com'::text ~* (value)::text))
      OR (((field)::text = 'phone'::text) AND ((value)::text = '01234567890'::text))
      OR (((field)::text = 'unique_id'::text) AND ((value)::text = 'abcdef'::text)))
Rows Removed by Filter: 11643
Total runtime: 744.395 ms

Short version: no. There's no practical way (in PostgreSQL, at least) to index a pattern column so it can be matched against plaintext inputs in a way that will speed up "does this plaintext match any of these patterns" queries.

PostgreSQL would need a special custom index type that "understood" pattern matches. I'm not sure how practical it'd be to implement one, nor how much could be gained from such an index. Since there isn't one, the question is somewhat academic.


What is the MySQL monitor? Upon a successful connection to the MySQL server, the client is greeted with "Welcome to the MySQL monitor". A web search returned many results on monitoring for MySQL databases. Is "monitor" here another name for "server"?

It is sometimes called the "commandline mysql tool".  It is a client that comes with MySQL and MariaDB.  It is a simple but very useful way to type SQL and get it executed.  Think of it as the "shell" version of phpmyadmin or Workbench.

It does not do any monitoring, though it could be used as a way to execute SQL in a monitoring script.


Does a database update force index rebuild regardless of updated field? With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated.

For example, if I have a table with 20 fields, the first 10 of which are referenced in either individual or covering indexes, is the speed of updates to the other 10 non-indexed fields affected?

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko)

When you update an indexed column, changes must migrate through this architecture as follows:

From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1
From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file)

If you are updating a non-indexed column, the only bottleneck would just be the flushing changes in dirty page over to the .ibd file over the Write I/O threads. Index change buffering would not be involved for non-indexed columns.

If you have MySQL 5.5/5.6, you may want to increase innodb_write_io_threads and innodb_log_buffer_size for increased write performance.


Creating the database on different file groups What is the pros and cons of creating the database for a operational system on different file groups? I know it is useful if you want to keep the static part of a database online when the other part (which is on a different File Group) can be restored. However this scenario is not useful for us (according to the nature of the system).

Based on your experience, is there any other benefit that we can get if create the database on the different File Groups? If so, what would be the best practice in assigning file groups?

Thanks a lot.

One of the main benefits of utilizing multiple files and filegroups is that you have great control over file growth. Also, prominently you can control and optimize I/O performance, as putting database files on separate physical disks can lead to faster I/O.

If you have an I/O expensive query across two tables, putting then on different disks can lead to better performance as there won't be a single physical disk servicing the query.

Take a look at this resource for more information on the benefits: http://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx


Inserting 60 000 rows taking too long I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over.
Shouldn't it be faster? I'm using InnoDB

INSERT INTO ip2nation (ip, country) VALUES
 (687865856, 'za'),
 (689963008, 'eg'),
 (691011584, 'za'),
 (691617792, 'zw'),
 (691621888, 'lr'),
 (691625984, 'ke'),
 (691630080, 'za'),
 (691631104, 'gh'),
 (691632128, 'ng'),
 (691633152, 'zw'),
 (691634176, 'za'),
 (691650560, 'gh'),
...

In general, this will take a time, anyway this time can be reduced by some ways, for example you can lock the table and unlock it when to finish..

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

Connecting: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting row: (1 × size of row)
Inserting indexes: (1 × number of indexes)
Closing: (1)

Please read full article form the following Link by MySql dev Team:
https://dev.mysql.com/doc/refman/5.0/en/insert-speed.html


Moving model database I have moved the model database by using following method

For each file to be moved, run the following statement.

ALTER DATABASE model
MODIFY FILE ( NAME = modeldev , FILENAME = 'G:\model\model.mdf' )
ALTER DATABASE model
MODIFY FILE ( NAME = modellog , FILENAME = 'G:\model\modellog.ldf' )

Stop the instance of SQL Server to perform maintenance.
Move the file or files to the new location.
Restart the instance of SQL Server or the server.

While restarting instance of server i am getting following error as shown below.

"The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details."

How to solve this error?

Follow below steps :

    a. start SQL Server in master-only recovery mode

     for Default instance : NET START MSSQLSERVER /f /T3608

     for Nameed Instance  : NET START MSSQL$instancename /f /T3608

    b. Check the file location

          SELECT name, physical_name AS CurrentLocation, state_desc
            FROM sys.master_files
            WHERE database_id = DB_ID(N'model');

    4. sp_detach_db 'model'
        go

    3. Move the Model.mdf and Modellog.ldf files from OLD_LOCATION to NEW_LOCATION
    4. Reattach the model database as follows:
    use master
    go
    sp_attach_db 'model','G:\model\model.mdf','G:\model\modellog.ldf'
    go
   5. Stop SQL Server : NET STOP MSSQLSERVER or NET STOP MSSQL$instancename
   6. Restart SQL Server Normally : NET START MSSQLSERVER or NET START MSSQL$instancename
   7. Verify the new location : sp_helpdb model


Keeping 2 tables in sync I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, the two other tables on slave should also get updated using triggers or any other way.

In other words, I need to replicate only one table from this slave. Since there is no budget for one more server, I will use the same slave schema. Do I need to write after update, insert and delete triggers on table_a? What is the best approach?

If you have this scenario

table_a has an auto_increment field
table_b and table_c have table structure identical to table_a
You are running MySQL 5.1+

you can create a Stored Procedure and a MySQL event instead of a Trigger

For the sake of example, let's say table_a looks like this:

CREATE TABLE table_a
(
    id not null auto_increment,
    ....
    primary key (id),
);

Create a Stored Procedure on the Slave to Copy Data to table_b and table_c

DELIMITER $$
DROP PROCEDURE IF EXISTS `Load_New_Data` $$
CREATE PROCEDURE `Load_New_Data` ()
BEGIN
    DECLARE min_id,min_idb,mid_idc INT;

    SELECT MAX(id) INTO min_idb FROM table_b;
    SELECT MAX(id) INTO min_idc FROM table_c;
    SET min_id = IF(min_idb < min_idc,min_idb,min_idc);

    DROP TABLE IF EXISTS table_a_delta;
    CREATE TABLE table_a_delta SELECT * FROM table_a WHERE 1=2;
    INSERT INTO table_a_delta SELECT * FROM table_a WHERE id >= min_id;
    INSERT IGNORE INTO table_b SELECT * FROM table_a_delta;
    INSERT IGNORE INTO table_c SELECT * FROM table_a_delta;

    DROP TABLE table_a_delta;
END $$
DELIMITER ;

Then, schedule a MySQL Event on the Slave for the Stored Procedure to go off every 5 minutes starting 5 minutes after you create the event:

CREATE EVENT ev
    ON SCHEDULE
      EVERY 5 MINUTE
      STARTS (NOW() + INTERVAL 5 MINUTE)
    DO
      CALL Load_New_Data();

Don't forget to Add This to the Slave's /etc/my.cnf

[mysqld]
event_scheduler=1


MySQL performance tuning I would like to optimize my MySQL DB. I noticed some important points as follows:

Enabling Query cache.
Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I need not worry about this facility's drawback. Before executing a query, I'll check if memory tables exist or not.)
Increasing key_buffer_size.

My question is, can I use both Query cache and memory storage? As well as what is the role of key_buffer_size?

Why am I asking? Because everything depends on system memory. How these 3 options will work together? Can I do these things together?

Query cache can be combined with memory storage but it'll only make sense if don't have many updates (as your cached results will be no longer actual after your change your data).

Basically query cache stores results of your SELECT queries (if run again, data is retrieved faster), key buffer size stores indices (helps to use them more efficiently, i.e. to retrieve data quicker) and you said you know what memory storage is.

All three options you mentioned can be used together, but the real efficiency depends on your usage pattern. But hey won't do any harm when combined (by that I mean assigning bigger values to cache and buffer while using memory storage) as long as you have enough memory.

P.S. As you probably reckon yourself, your setup screams you're using a wrong tool. I can imagine situation where it is still necessary, e.g. using legacy / unchangeable code that depends on MySQL but otherwise consider redesigning and using other data storages besides MySQL, permanent or temporary like memcache.


How to exclude tempdb from sp_MSforeachdb? Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list.

exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup.user_name AS [User],
ceiling(bkup.backup_size /1048576) as ''Size Meg'' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig'',
bkup.backup_start_date AS [Backup Started],
bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)],
CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bms.description AS [Description]
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
,bkup.database_backup_lsn
,bkup.differential_base_lsn
,bkup.first_lsn
,bkup.last_lsn
,bkup.fork_point_lsn
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
--WHERE backup_finish_date = (SELECT MAX(bkup.backup_finish_date) FROM  msdb.dbo.backupset bkup WHERE sysdb.name = bkup.database_name) --Last backup
WHERE backup_finish_date > DATEADD(DAY, -1, (getdate()))  -- Last 60 days
AND bkup.type=''L''
AND sysdb.name = ''?''
ORDER BY backup_start_date DESC, backup_finish_date';

I tried writing 'IF ''?'' NOT IN (''tempdb'') SELECT.......' but all results were identical for only one database.
Then I tried writing AND sysdb.name <> ''tempdb'''' but it gave an error:

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'DES'.

A possibility would be to check for DB_Name = 'Tempdb', and if it's tempdb just end the script.

This is a quick test I did, and it seems to works :

EXEC sp_msforeachdb 'USE [?] IF DB_Name() = ''Tempdb''BEGIN RETURN END SELECT db_Name()'

That will run the code on each db, but if it's tempdb it wont execute a thing, cause it'll return.

It's also known that using sp_msforeachdb is not always the best thing. Aaron has blogged about using a self made replacement procedure you can use, it might be worth your time to go check it out! making-a-more-reliable-and-flexible-spmsforeachdb


Can I use result of one query inside other query in Cassandra I need to delete all rows with given clustering column value. I don't have partition key so I don't have complete primary key. To delete record from Cassandra table we need to provide specific partition key. Therefore first I need to select records to delete using this clustering column value and then deleting that record with the help of associated partition key values.

In CQL, you can't directly use the result of one query inside another, like you would in SQL.
You need to execute separate queries, and your application needs to handle the logic. If the result of your first query is very large, try to use paging.


Moving data from one DB to another where the table/column structure is identical Question about the import / export process.

I know is SQL Developer I can export all the columns from a table and import into the same table on another server.

My question is - what happens with the duplicate inserts? Will it just skip or what will happen?

I have these tables I need the data to be identical - one of the tables has data the other doesn't and I need to make them match without it messing up what is already in there.

My theory is that once I export all of the rows in the form of inserts and run it on the other DB-Table it will insert all the ones it's missing and error and skip the inserts where there already exsist the row, but want to make sure this is correct.

Thanks in advance!

If you have duplicate inserts, the first question I would have would be why would you have two rows in the same table with the exact same information?

That being said, if there are no unique keys or other such constraints on the table, the new DB server should dutifully import the duplicated data.

I would heartily recommend testing it in a non production environment first, though.


Limit columns to certain width in MySQL command The mysql command gives nice tabular outputs that take into consideration half and full width spacing.

For example:

> select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０';
+------------------------------------+
| text                               |
+------------------------------------+
| The quick brown fox jumps over the |
| ０１２３４５６７８９０             |
+------------------------------------+
2 rows in set

Given this, how do I truncate a column to a certain width?

e.g. How do I get this?

+----------------+
| text           |
+----------------+
| The quick brow |
| ０１２３４５６ |
+----------------+

Edit: In terms of what I've tried.

If I attempt this by changing the query to use something like LEFT():

> select left(text, 7) text from (select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set

What I get isn't exactly what I wanted. I don't want 7 left characters of each string, I want both strings to show as many characters as optimally fit into a given width.  I want a result similar to what's under "e.g. How do I get this?", if say this width was 14 (half-width characters).

MySQL does cope with different widths, as the first example establishes. (Look at it with a monospaced editor and the pipes line up.) Any solution like SQL Plus' column foo format a10, or using ENCODE() and BIT_LENGTH(), or user functions could work. The lack of answers probably means there isn't an easy solution?

Looks like a character set issue. Here is why:
I ran
select left(text, 7) text from
(select 'The quick brown fox jumps over the' text union select '０１２３４５６７８９０') x;

in MySQL 5.6.22 for Windows. Here is that output
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Same query in MySQL 5.6.21 for CentOS 6.3
+-----------------------+
| text                  |
+-----------------------+
| The qui               |
| ０１２３４５６        |
+-----------------------+
2 rows in set (0.00 sec)

You should use the CAST operator
select left(CONVERT(text USING latin1), 7) text from
(select 'The quick brown fox jumps over the' text union select '01234567890') x;

When I ran this in MySQL 5.6.22 for Windows, I got this
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

When I ran this in MySQL 5.6.21 for CentOS 6.3, I got the same thing
+---------+
| text    |
+---------+
| The qui |
| ??????? |
+---------+

Just hunt down the correct character set.
GIVE IT A TRY !!!


Why is it necessary to type "host" before the imp command when I'm using SQL*Plus to import a dmp file into Oracle? In SQL*Plus, I'm connected to an Oracle 11g database. When I type

imp help=y

It gives me

SP2-0042: unknown command "imp help=y" - rest of line ignored.

But when I type

host imp help=y

it gives mehelp with the import utility.

All the guides I've seen online for importing say to just type imp, without the host part. Why do I need to say host before using the utility?

imp is an operating system command, not an SQL*Plus command, so it should be run from the OS shell command line. If you insist on running it from SQL*Plus you need to use the SQL*Plus host command to send imp to the host OS for execution.