texts: SQL delete where not in (...) query not behaving as expected

texts:
We have the following query which returns the most recent fares from our database :
SELECT f1.id
  FROM ( SELECT * FROM fares ) AS f1
INNER JOIN ( SELECT origin_id
                  , destination_id
                  , market_id
                  , cabin
                  , tripType
                  , max(created_at) AS maxDate
               FROM fares
              GROUP BY origin_id
                     , destination_id
                     , market_id
                     , cabin
                     , tripType
            ) AS f2  ON f2.origin_id      = f1.origin_id
                    AND f2.destination_id = f1.destination_id
                    AND f2.market_id      = f1.market_id
                    AND f2.cabin          = f1.cabin
                    AND f2.tripType       = f1.tripType
 WHERE f1.created_at = f2.maxDate
 GROUP BY f1.origin_id
        , f1.destination_id
        , f1.market_id
        , f1.cabin
        , f1.tripType

In our test dataset (SQL Fiddle) this returns two rows. When we change the query to work as a delete query it deletes everything but three rows, while it should delete everything but the two rows:
DELETE FROM fares
 WHERE id NOT IN
     ( SELECT f1.id
         FROM ( SELECT * FROM fares ) AS f1
        INNER JOIN ( SELECT origin_id
                          , destination_id
                          , market_id
                          , cabin
                          , tripType
                          , max(created_at) AS maxDate
                       FROM fares
                      GROUP BY origin_id
                             , destination_id
                             , market_id
                             , cabin
                             , tripType
                   ) AS f2  ON f2.origin_id = f1.origin_id
                           AND f2.destination_id = f1.destination_id
                           AND f2.market_id = f1.market_id
                           AND f2.cabin = f1.cabin
                           AND f2.tripType = f1.tripType
        WHERE f1.created_at = f2.maxDate
        GROUP BY f1.origin_id
               , f1.destination_id
               , f1.market_id
               , f1.cabin
               , f1.tripType
    )

We noticed that the three rows that remain are also the product of the select query when the GROUP BY part is ommited.
Now the question is why does the SELECT query in the DELETE statement behave different than the SELECT statement by itself?
Reproduction:

Take the schema from this SQLFiddle: http://sqlfiddle.com/#!9/e943ee/2
Run the query supplied in the SQLFiddle, only two rows are returned
Add the delete query to the schema & rebuild the schema
Execute "SELECT * FROM fares" and three rows are returned (where there should have been only two)


texts: Transaction - Timestamp Ordering. When is aborted transaction restarted

texts:
(I don't know if this is the correct place to ask, since this is more like a theoretical question).

I have a question about restarting aborted transactions. I don't know when / at which point should restarting of the transaction(s) occur.

Example - using Basic Timestamp Ordering

w11(x), r12(x), r11(z), r13(x), w13(z), r12(z), w13(x), w14(x)

Initial values are:

read_ts(x) = write_ts(x) = read_ts(z) = write_ts(z) = 10

As we can see transaction 12 is aborted because it is trying to read value z, written already by T13 (older, T12 can't read it).

So in this case T12 is aborted and its timestamp is raised (to 15?). My question is when does the aborted transaction(s) restart?
(1) After all the non-aborted transactions are done, or (2) soon as possible (= restarts after next operation w13(x))?

In scenario 1, w14(x) would be done, and after that aborted transaction 12 would start (as T15).

In scenario 2 (soon as possible), transaction 12 (as T15) is restarted immediately after next operation w13(x). After that w14(x) is aborted (14 < 15) and restarted as T16

What is the correct order in this case? What does the restart actually mean? When do we restart an aborted transaction(s)?
I would like to see a practical example.

edit: changed the last operation to w14(x) instead of r14(x)


texts: How to view full blocking postgres queries?

texts:
I'm using a statement from this page to view blocking queries in Postgres

SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.granted;

However the queries are truncated so I typically can't see what's in the WHERE clause which makes debugging these blocking issues very hard. Is there a setting where I can tell postgres to log the full queries to pg_stat_activity


texts:
I have a SQLite table with these fields among others

Fld_QuoteNumberRoot     string
Fld_Revision            integer
Fld_QuoteNumber         string

I need to fill Fld_QuoteNumber on every UPDATE and on every INSERT like this:

'Q00897' + 4 => 'Q00897/4'

Using this trigger

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision)
END;

I always the error:

  near "END": syntax error

Questions
What is wrong with my syntax?

Can I create a trigger which fires on UPDATE and on INSERT or do I need to define two triggers?


texts: Is there any way to trim data automatically

texts:
Lets I have a table named myTable in mysql with Three field

1. id int autoincrement
2. product_id varchar(20) not null
3. product_name varchar(200) not null

Now I want to keep data in product_id and product_name without white-space.


texts:
One of our tables is over 200gb and the old records are hardly ever queried, and this makes me think partitioning is a good solution, however I am having trouble figuring out which column to use as a Partition Key for a table.
We have 2 tables as follows (names and structures are altered for confidentiality):

transactions table - with a transaction ID,
checks table - With the schema (id, transcation_id, created_at, metadata, last_updated_at) there can be multiple different checks with for a given transaction_id

As part of data aggregation we write a summarized version of the checks into a single column in the transactions table. The way we do this aggregation is by running an UPDATE on transactions table with joins on the checks table filtering by last_updated_at to only check for checks that have been updated recently.
Users however query using created_at in the checks table, and also last_updated_at could potentially change for a given row (although not likely).
Which one should I use as partition key? created_at or last_updated_at?


texts:
I basically have the same question posed here - Optimal way to ignore duplicate inserts? - except in my case it's SQL Server.

I have a table that is a list of person IDs who are deceased.  There are many places in the source database that a person can be indicated as deceased.  I would like to do INSERTS from each of these locations into this main table, but a person may be indicated as deceased in multiple places.

Ideally (I think) I just want to ignore duplicate key errors - in the most efficient way possible.  (Thus I don't want to have to check the main table for the IDs as part of my INSERT statement.)

(related to solving my question need suggestions to improve view performance)


texts: SQL Server 2008 Linked Servers Consistently Dropping Connections

texts:
I have set up a Linked Server between 2 SQL Server 2008 R2 databases (both 10.50.1600).

The linked server worked normally for several weeks until I received reports that users were not able to hit one of the Linked Servers. I got this report on Friday.

When investigating via SQL Server Management Studio, when expanding the Catalogs for the linked server, I noticed that all the databases that the account being used for the linked server normally has access to were gone. Instead there was a "default" database listed.

I dropped and recreated the linked server using the same account used previously, and it didnt work the first several times I tried. I got "Linked server error - OLE DB provider "SQLNCLI" for linked server "" returned message "Login timeout expired" exceptions until finally it decided to "take".

It worked for little while, but this morning I observed issues with the linked server again and expanded the server catalogs and again got the "default" database with no other databases.

This time, I refreshed the view, and now all databases are displayed, and the jobs we run that depend on this linked server are executing.

SQL Server logs on either server do not show any exceptions surrounding Logon events.

In light of the fact that there are no logged events, where are some other places I should look to start troubleshooting this, knowing that no hardware configuration changes have been made that could influence this behavior?


texts: Query returning unused stored procedures

texts:
When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries.

When I run the following query, the results show the query was created in 2003, and last updated over a year ago.

select d.name, p.name, e.last_execution_time, p.create_date, p.modify_date
from sys.DM_exec_procedure_stats as e
  right join sys.databases as d on d.database_id = e.database_id
  right join sys.procedures as p on p.object_id = e.object_id
WHERE is_ms_shipped = 0
order by e.last_execution_time asc, p.modify_date desc, p.create_date desc

What am I missing? Why am I seeing create procedure calls nonstop while the procedures are seemingly very old and unmodified. As a reference, the DB has 700+ stored procedures, while being only 6,5GB in size, and the query above shows that 675 of them have not been used since the last reboot (unless I'm misinterpreting what last_executed = NULL means).


texts: sql producing duplicate column id on join?

texts:
Is there any way to only show one id column which is used to join on other tables?
This query produces two id columns used to join each other as primary and foreign key. I only want it to repeat the id column once.
SELECT *
FROM products
JOIN productimg ON products.id = productimg.id
WHERE products.id IN (1, 2, 3)

Query results:
id    name     cid  stk prc    id       url
 1  product1    1   10  3000    1   /static/images/dresses/td1.jpg
 2  product2    2   10  2500    2   /static/images/dresses/td2.jpg
 3  product3    2   4   4000    3   /static/images/dresses/td3.jpg


texts: Error: 468 trying to connect to SQL server with SQL Server management studio

texts:
I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.

The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this error:

  No se puede resolver el conflicto de intercalación entre
  "SQL_Latin1_General_CP1_CS_AS" y "Modern_Spanish_CI_AS" de la
  operación equal to. No se pudo usar la vista o función
  'dbo.sysdac_instances' debido a errores de enlace. (Microsoft SQL
  Server, Error: 468)

It seems to be a problem related with collation.
How I could specify the proper collation for the connection?


texts: How can I get Performance Statistics to work in MySQL Workbench?

texts:
I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema.

The EXPLAIN plan is similar enough to not warrant such a massive increase in execution time, and identical in terms of the number of rows returned at each step.

This query is going against a query only database, so it's not a result of not having analyzed table statistics either.

I installed MySQL Workbench which seems to be a really nice tool, especially considering the fact that it's free. However, this section in the explorer interface for my connection that is completely grayed out:

PERFORMANCE
  Dashboard
  Performance Reports
  Performance Schema Setup

I did add the following line to my /etc/my.cnf file and restart my server:

[mysqld]
performance_schema

And I followed the instructions in the MySQL Performance Schema Quick Start Guide and verified that this feature is indeed enabled.

I kicked off a job last night in order to have some statistics collected, but this morning only find:

Performance_schema_files_instances_lost    89804

while every other performance_schema-related variable is zero.

The error log is blank, so I have so idea how to find out how to make this work.

I am running MySQL 5.5.21, and MySQL Workbench 6.1 on Mac OSX Mavericks.


texts: Why are my Postgresql sessions writing directly on disk?

texts:
I have a write-heavy application on a PostgreSQL 9.4 on CentOS 6 that I'm trying to tune.

Using iotop, I see that my session processers are doing all the heavy writing on disk when (to my understanding) only the checkpointer and writer processes should be doing it.

I thought that the sessions might be writing temp files due to lack of workmem but when I query the pg_stat_database, the columns "temp_files" and "temp_bytes" are zero. I'm trying to log this kind of usage via system parameter "log_temp_files" but nothing appears on the log files.

Can somebody help me? Are there any kind of data that the sessions write directly on disk besides temp files?

EDIT: Added an iotop example

19156 be/4 postgres    0.00 B/s  382.33 K/s  0.00 %  0.00 % postgres: checkpointer process
19167 be/4 postgres    0.00 B/s  146.96 K/s  0.00 %  3.03 % postgres: example example 10.18.14.1(59536) idle
19190 be/4 postgres    0.00 B/s  146.16 K/s  0.00 %  2.89 % postgres: example example 10.18.14.1(59551) idle
19164 be/4 postgres    0.00 B/s  144.17 K/s  0.00 %  2.20 % postgres: example example 10.18.14.1(59533) idle
19172 be/4 postgres    0.00 B/s  128.64 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59541) idle
19161 be/4 postgres    0.00 B/s  122.27 K/s  0.00 %  1.88 % postgres: example example 10.18.14.1(59525) idle
19174 be/4 postgres    0.00 B/s  119.08 K/s  0.00 %  2.08 % postgres: example example 10.18.14.1(59543) idle
19170 be/4 postgres    0.00 B/s  116.29 K/s  0.00 %  2.62 % postgres: example example 10.18.14.1(59539) idle
19169 be/4 postgres    0.00 B/s  115.50 K/s  0.00 %  1.99 % postgres: example example 10.18.14.1(59538) idle
 1304 be/4 root        0.00 B/s  115.50 K/s  0.00 %  0.92 % [flush-253:0]
19173 be/4 postgres    0.00 B/s  113.90 K/s  0.00 %  1.97 % postgres: example example 10.18.14.1(59542) SELECT
19175 be/4 postgres    0.00 B/s  113.50 K/s  0.00 %  1.98 % postgres: example example 10.18.14.1(59544) idle
19476 be/4 postgres    0.00 B/s  111.91 K/s  0.00 %  1.91 % postgres: example example 10.18.14.1(59618) idle
19434 be/4 postgres    0.00 B/s  110.32 K/s  0.00 %  1.77 % postgres: example example 10.18.14.1(59606) DISCARD ALL
19168 be/4 postgres    0.00 B/s  101.16 K/s  0.00 %  1.82 % postgres: example example 10.18.14.1(59537) SELECT
19166 be/4 postgres    0.00 B/s   99.57 K/s  0.00 %  2.82 % postgres: example example 10.18.14.1(59535) idle
19171 be/4 postgres    0.00 B/s   98.77 K/s  0.00 %  1.78 % postgres: example example 10.18.14.1(59540) idle
19179 be/4 postgres    0.00 B/s   98.37 K/s  0.00 %  2.22 % postgres: example example 10.18.14.1(59547) idle
19163 be/4 postgres    0.00 B/s   96.38 K/s  0.00 %  1.72 % postgres: example example 10.18.14.1(59532) idle
19189 be/4 postgres    0.00 B/s   94.39 K/s  0.00 %  2.26 % postgres: example example 10.18.14.1(59550) idle

Thanks in advance.


texts:
I have a group of scripts set to run in a certain order, but sometimes I find the group running long and overlapping into the next group run.   Is there a way to prevent that?

group.sh:
script1.sh &
wait
script2.sh &
wait
script3.sh

group.sh is still running (perhaps on script3.sh) when group.sh kicks off for the next run.


texts:
I support a database that has many tables that hold very similar data.  Over time, much of this data isn't needed, and the table names are dynamic as an application is creating these tables, and the current date is used within the table name.

I am attempting to write a batch file that uses isql to remove rows that are no longer required to store in all of the tables.  They all start off with the same set of characters, followed by _creation date.

Is there any way to write a script to find those names, or use a wildcard such as:

DELETE FROM Table_* WHERE Type = 1

From my testing, the above SQL command does not work. Is there a way around this without having to specify the exact table name?


texts:
We're trying to add an index to one of our tables in production database.

CREATE TABLE `abcd_s3_metadata` (
  `rowId` bigint(20) NOT NULL AUTO_INCREMENT,
  `sId` varchar(50) NOT NULL,
  `ruid` varchar(200) NOT NULL,
  `s3Pointer` varchar(250) NOT NULL,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `latest` tinyint(1) NOT NULL DEFAULT '1',
  `version` int(11) NOT NULL,
  PRIMARY KEY (`rowId`),
  UNIQUE KEY `ruid_UNIQUE` (`ruid`)
)

The table has ~ 1B rows.
We created a copy of the live table and I tried to add an index on updateTime field.
With the following statement:

ALTER TABLE `innodb`.`ruids_s3_metadata`
ADD INDEX `updateTime_idx` (`updateTime` ASC);

The ALTER statement ran for 3 hours resulted in extra read 1500 IOPS consumed all free InnoDB memory. And eventually caused the DB to choke and become unusable.

What can we do better? Are there other options?

Thanks

Alex


texts: DLATK: Resolving (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

texts:
I am currently using the Differential Language Analysis ToolKit (DLATK) that relies partly on SQL.
I tried accessing a SQL file dla_tutorial in the DLATK file using this code in my terminal (MacOS):
dlatkInterface.py -d dla_tutorial -t msgs_zz -c user_id --add_ngrams -n 1 2 3 --combine_feat_tables 1to3gram

However, I get this error:
*MYSQL Connect ERROR on db:dla_tutorial
(1045, "Access denied for user 'root'@'localhost' (using password: NO)")

I have seen other answers that recommend changing my password for SQL. However, I had some troubles understanding and executing them, and I'm not sure if this is a DLATK-specific problem. If anyone could offer any leads, that would be super helpful!
Thanks!


texts: Is pg12 cache used with two SELECT on same table but different fields

texts:
Figure a table with fields a, b and c
Figure two requests on this table :
SELECT a, b from table

SELECT b, c from table

Is postgres able to reuse cache for the second request?


texts: How to improve the liveness of a system when having a long-running transaction blocking multiple reading transactions

texts:
Is there any recommended approach for improving the application liveness when having, over the same database object, multiple reading transactions, and one long-running transaction which is updating the data?
For example, a transaction Ti is updating several records of [CUSTOMER] table (which can last hours long), and multiple Tj transactions are trying to read the number of orders of each client. I'm assuming that if Ti gets an exclusive lock, all Tj transactions will be suspended resulting in a bad performance (low liveness).
My application is at design time, and I haven't chosen a concrete DBMS yet.
My goal is that Tj are served with the newest version of [CUSTOMER] before Ti began and that Ti doesn't block Tj.


texts: MySQL 5.6 - Creating an event inside a stored procedure

texts:
I am trying to create a procedure which create an event in MySQL.

For example, something similar to this:

delimiter //
CREATE PROCEDURE schedule_my_db_job()
  BEGIN

    CREATE EVENT MY_DB_JOB
    ON SCHEDULE EVERY 1 MINUTE DO
    BEGIN
        SELECT * FROM information_schema.events; -- for example
    END;

  END//
delimiter ;

But it gives me following error when I try to create this procedure.

ERROR 1576 (HY000): Recursion of EVENT DDL statements is forbidden when body is present

As I understand MySQL allow stored procedures to have CREATE EVENT sql statement inside. (Quotation from https://dev.mysql.com/doc/refman/5.6/en/create-event.html  "You can create an event as part of a stored routine, but an event cannot be created by another event")

Could you please let me know what is the error here?

Thanks.


texts:
I currently have a PostgreSQL slave node that is presenting problems, that don't arise in the master node. Seems to be related with the node sync process.

Complete stacktrace:

org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery
  Detail: User query might have needed to see row versions that must be removed.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:542)
    at cl.waypoint.mailer.reportes.BasicReport.getSingleColumn(BasicReport.java:518)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.updateIgnicion(StatusSemanalClientes.java:448)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes.access$2(StatusSemanalClientes.java:447)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:414)
    at cl.waypoint.mailer.reportes.StatusSemanalClientes$TempAndDoorLocator.call(StatusSemanalClientes.java:1)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)

DB Version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

OS Version:

Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

The question is: how can I debug the actual cause of the problem and/or solve/avoid it? Don't hesitate to ask for more info if needed to pinpoint the problem, right now just don't know what else to provide.

PS: Error does NOT appear if the offending query is repeated, so it's not strictly related to the query itself, but perhaps with a table update/sync transient.


texts: Performance Improvement went wrong in Production, worked fine in Test

texts:
We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit to have much lesser IO and time, it worked well in test in terms of data and performance however its failing in production and returning different data, we had to rollback the changes. Need your expert advise on this.

This was tested for data in Test environment for more than 3 months and we never had any data issue. Started failing sparingly in Production immediately after the deployment and was producing inconsistent data.

Existing Query:

SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10))
FROM table_name WITH(NOLOCK)
WHERE s_mode='value'

Proposed Query:

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK)
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

The DDL of table is as below:

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED
(
    [RefNo] ASC
)

Providing only those columns from definition which is used in the query.

Udt_RefNo is a user defined datatype as:

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO

Version of SQL Server: Microsoft SQL Server 2014 (SP3) Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit).

Non-clustered index covering columns as shown below:

CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
    [S_Mode] ASC,
    [S_Status] ASC
)
INCLUDE (   [ref_clnt])

Please find the query plans as requested:

Previous Query
Proposed Query

Comparison of Number of Reads after enabling statistics IO and time:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 161 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Vendor has come back with suggestion as

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

Issue is - how to test them as all of them seems to work for most of the scenario however fails only in few cases. I am not very much aware of the application and its vendor supported one, so can't get much details like how does business logic work for underlying procedure.


texts: How to delete the old backup differencial files?

texts:
I'm (trying) managing a large number of sql server instances among a number of machinces running SQL Server 2005 and 2008. Almost all the DB are in Simple Recovery Model.
I'm doing full backups every Sunday and differencial backups every day.
Even though I am using compressed backups, there are databases which are nearly a TB. Therefore, I need to have a proper use of the backups regarding the space they are allocating.

My goal: I just need to have one full and one differencial backup for each database every single moment.

My question : What actions should I perform in order to achieve my goal? I guess something i need to do with the expiration of the backups and the "Clean up history", but I am not sure what exactly i need to do.


texts: Maintenance plan for creating transaction log backups

texts:
I have a database that is set to the 'Full' recovery model, and I'm creating a maintenance plan to backup the Transaction Log every 15 minutes. A full backup is then taken once every 24 hours.

I was wondering: will the maintenance plan for the transaction logs create a lot of different files every 15 minutes, or will it by default append to the same file for 24 hours?

Also: what is preferred? I couldn't find anything on this subject (or maybe I was Googling the wrong words).

Thanks for any pointers anyone can give me.

EDIT: I also see that the checkmark "Verify backup integrity" is OFF by default. Is there a risk with turning it on? It seems to be that it should be on by default, so is there a reason for not using this?


texts: How to check actual execution plan of a query after it has been executed

texts:
In SQL Server, how can we see actual execution plan of a query, after it has been executed?

As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is that correct?

Note: We do not have any third party monitoring tools.


texts: Collect data with central management servers and keep them in local table

texts:
I have 50 servers. I manage them from a central management server.

I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect data from each server and store/handle it locally.

Is this possible and if so, how?


texts: SSMS 18.4 Error opening database properties dialog box Azure SQL DB Managed Instance

texts:
I have three databases in an Azure SQL DB Managed Instance. I am using SSMS 18.4. One database I can right-click, choose Properties, and the dialog box comes up normally. The other two databases give me the following error.

I have spent a couple of hours Googling with no results. Anyone have any ideas?


texts:
Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks.

Time: 0.466s

Time without sub-selects: 0.058s

SELECT
    gallery.go_id AS id,
    (SELECT meta.value FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_text" LIMIT 1) AS `sort_cache_sort_title_text`,
    (SELECT CAST(meta.value AS SIGNED) FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_int" LIMIT 1) AS `sort_cache_sort_title_int`
FROM
    arGalleryOther gallery
WHERE
    gallery.go_visible = 1
    AND
    gallery.go_added <= "2017-11-09 17:55:46"
    AND (
        gallery.go_maingal = 9
        OR
        FIND_IN_SET(9, gallery.go_othergal)
    )
ORDER BY
    gallery.go_added DESC,
    sort_cache_sort_title_text ASC,
    sort_cache_sort_title_int ASC
LIMIT
    4

CREATE TABLE `arGalleryOther` (
  `go_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `go_code` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `pr_id` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `go_name` varchar(75) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `go_name_noint` varchar(75) CHARACTER SET latin1 DEFAULT NULL,
  `go_name_int` int(5) unsigned DEFAULT NULL,
  `go_maingal` int(10) unsigned NOT NULL DEFAULT '0',
  `go_othergal` text CHARACTER SET latin1 NOT NULL,
  `go_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `go_visible` tinyint(1) NOT NULL DEFAULT '1',
  `go_horiz` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT 'h',
  `go_vert` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT '*',
  `go_override` tinyint(1) NOT NULL DEFAULT '0',
  `go_thumb` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
  `go_keywords` text CHARACTER SET latin1 NOT NULL,
  `go_html_page` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `go_phototype` enum('H','V') CHARACTER SET latin1 NOT NULL DEFAULT 'H',
  `go_notes` text CHARACTER SET latin1,
  `go_nudity` enum('YES','NO') CHARACTER SET latin1 NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`go_id`),
  KEY `idx_visible` (`go_visible`),
  KEY `idx_added` (`go_added`),
  KEY `idx_maingal` (`go_maingal`),
  FULLTEXT KEY `idx_othergal` (`go_othergal`)
) ENGINE=MyISAM AUTO_INCREMENT=4527 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

CREATE TABLE `meta` (
  `meta_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `model` varchar(50) NOT NULL,
  `model_id` int(11) unsigned NOT NULL,
  `key` varchar(50) NOT NULL,
  `value` text,
  `serialized` enum('false','true') NOT NULL DEFAULT 'false',
  `type` varchar(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_key` (`key`) USING BTREE,
  KEY `idx_model_id` (`model_id`),
  KEY `idx_model_key` (`model`,`key`,`value`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=368839 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT


texts:
I got so error:

  Database 'MyDb' is being recovered. Waiting until recovery is
  finished.

I have had a critical operation at that time.

What is the cause of this situation? And how can it be avoided?


texts: How to calculate count of rows of Memory-Optimized Table?

texts:
Performance is important for me so I don't use select count(*) from MyTable.

I use the following query to calculate rows count:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name;

Basically it works well for pretty much all sql server versions. It doesn't work for Memory-Optimized Tables. I create a table using the following query:

CREATE TABLE [dbo].[MemoryTableSchemaAndData]
(
    [column_id] [int] NOT NULL,
    [column_varchar] [varchar](100) COLLATE French_CS_AS NULL,

 PRIMARY KEY NONCLUSTERED
(
    [column_id] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO [dbo].[MemoryTableSchemaAndData] VALUES
(100, '-'),
(101, '-'),
(102, '-');

The query below shows that index_id equals to 2 for Memory-Optimized Table:

select object_Name(object_id), *
from sys.partitions
where OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized') = 1

But sys.partitions doesn't contain rows count for Memory-Optimized Tables at all. The following query as a prove:

SELECT rows, *
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
WHERE [Tables].name = N'MemoryTableSchemaAndData'

--it returns rows 0

How to calculate rows count for Memory-Optimized Table ?

ps: already tried this following solution but it doesn't work.


texts: SQL Server Timeout: Gathering server side information

texts:
We use SQL Server 2008 for our database. We use a combination of Classic ASP and .NET to connect to said database. We get random timeouts on queries that are extremely simple and when we run said queries they appear to be quick and have the correct indexes.

What I'm wondering is if there is a way to have SQL Server gather/keep data on these timeouts and send it to us. I've read in the past that you can do this for deadlocks.


texts:
Is there any way I can at least export a table schema from MSSQL or MySQL to a DTD file? Also the same for the data of the tables to XML.

I am really not getting how to write DTDs, so I badly need a generator, and seeing how I have the DB set up, I'd say that's the easiest way?


texts:
We have a database with a few larger tables were one (yes only one) table grows extremely. It contains only 50 gigabytes of data while the .ibd file (we use innodb_file_per_table) takes up about 1.2 terabytes of space after only one week.
There are many many update querys run against the table which this file belongs to so the problem is probably InnoDB saving all the undo states (or whatever this is called).

The server runs on Percona Server 5.7.18

The server settings that we use:

innodb_log_files_in_group = 2
innodb_log_file_size = 10G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

binlog_format = MIXED
gtid-mode=ON
enforce_gtid_consistency=ON

log_slave_updates = 1
expire_logs_days = 7
sync_binlog = 1
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 10000
innodb_thread_concurrency = 0
transaction-isolation = READ-COMMITTED
thread_cache_size = 256
table_open_cache = 10000
max_connections = 10000
open_files_limit = 1024000
innodb_lock_wait_timeout = 30
innodb_buffer_pool_size =  650GB
max_heap_table_size = 32M
tmp_table_size = 32M
innodb_log_buffer_size = 64M
query_cache_type = off

I know that we could use OPTIMIZE TABLE or pt_online_schema_change to get the used space back. But doing this once a week is not really an option on a production database which is essential for the company.

Are there any settings that I don't know of or missed that will prevent the .ibd file from blowing up this fast?


texts: Simultaneous SQL full backups of newly created databases

texts:
I would like to take a backup of newly created databases but within a minute if the user creates three databases the script can able to take only one database but for one new database which was created 15 mins before it is working.
Anything I need alters to be able to take simultaneous multiple new database backups within the specified time (here the script job runs every 15 mins)?
Below is the script.
-- Alert on the new database(s) created in the past 15 mins
-- Replace with your recipients in line #24

-- declare the variable for interval Minutes to detect the new database(s)
DECLARE @IntervalInMinutes TINYINT -- check new database(s) in the past 15 mins(s)
SET @IntervalInMinutes = 15 -- replace with the number of mins you wish to check for new database(s)

select GETDATE()
-- check for new database(s)
IF EXISTS(
    SELECT 1
    FROM master.sys.databases db
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb')
BEGIN -- new database(s) exist(s)

DECLARE @DBname varchar(64)

SELECT  @DBname =  [name]
    FROM master.sys.databases db
    WHERE DATEDIFF(MINUTE, db.create_date, GETDATE()) <= @intervalInMinutes
        AND db.name <> 'tempdb'

DECLARE @Path varchar(64)
declare @dates varchar(64)
set @dates = CONVERT(date, GETDATE(),112);
SET @Path  = 'H:\Fullbackups\'+ @DBname +'_'+ @dates+'.sqb'

BACKUP DATABASE  @DBname TO
DISK =@Path
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
--GO

P.S. I have not provided the dbmail and alert script here.


texts: Solution for deploying multiple instances of Django Server with Database replication

texts:
have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple instances of this across a client site. I need to keep all databases consistent across the instances with each instance talking to its own database (essentially a multi-master setup)

I have been looking into various replication solutions (mySQL Cluster, PGCluster) but these all seem to require that I have each instance accessing the database through one location, whereas I want each instance to read /write to its own database and the databases to be kept consistent across all devices. Does anyone have any ideas of a solution that may work for this or somewhere else I could look?

Its also worth noting that the databases are write heavy rather than read heavy


texts: SQL Server - Data Warehouse large log file, how to safely shrink?

texts:
My DW database is about 20GB size in data file and nearly 120GB in log file.

Data staleness is until midnight (so "yesterday's news") and multiple databases are being replicated daily from production servers. I'm also transforming the data at this time to get more analytical/synthetic/ information in one place for various consumers (accounts, marketing, management reports), so even complete data loss means about 30 min setback for all Stored Procs to re-run and repopulate all tables again. That means, yes, that point in time recovery is not (and will not be) a requirement.

Obviously then, the schema itself is much more valuable than the data and that's what must be protected (and is). Nonetheless, the DB is in full recovery mode and backups are being run daily, in addition to ad-hoc ones when I expect to be "breaking things".

It's possible the Snapshot isolation I've enabled on it had big impact on the size as well, but ultimately it proved pointless as I'm also using data across 12 databases (for now) and setting them all in similar way is impossible (various compatibility levels; don't want to meddle with that). So happy to switch it off again.

But, having said all that, I can't afford having the database inaccessible for long, as a number of people use it to do their work, even if it's still in early development phase. SSRS runs several dozens of subscriptions off it as well (RS is on a different server, if that's helps). It is also being exposed to a BI software for a number of web users (Tableau), so if we don't disrupt that, too, it would be good.

Obviously - again - large log files are due to large number of queries being cancelled, erroring out and/or simply crashing. And since I'm the only user who has insert/update/delete permissions, which is mostly done outside of working hours anyway, transactions are committed already for a quite a while.

So, my question is: how do I safely shrink the database to something more compact? And definitely in a "rinse and repeat" pattern for foreseeable future? No point wasting space to backup data I'm not really required to keep.

Additional info: this also may apply to the remaining 12 DBs (replicated from production servers).

Main reason for asking is that I may need the database to be more portable so that I can work on it offline and since other DBs are already quite large space really counts. And I'm not really a DBA, so I'm not sure of the best course here.

EDIT:

After reading linked articles and answers to similar question there is one, additional question that bugs me: If i do a log backup (as only then log files will stop growing) and then delete those backups (as I don't really need them - or do I?) will the log file start growing again? Do I understand correctly that I need to shrink and switch to simple recovery model afterwards to prevent that?


texts:
Are there any tables that I can get all Oracle supported system privileges and object privileges? I know there is a view called all_sys_privs in Oracle. But I can't find more information about it.

Thanks.


texts: SQL Update column with another table column

texts:
I'm using Postgres 9.4, and already seen others asking the same (simple) question, but theirs answers do not solved my problem and I can't see why. Suppose that I have these table A with common data and what type these data correspond, and consequently, a indicator (which I want to add that is in another table B):

 gid, type,  indicator
    1       a      ''
    2       b      ''
    3       e      ''
    4       a      ''
    5       d      ''

And this table B, which describes types and their respectives indicators

 type, indicator
    a       alfa
    b       alfa
    c       beta
    d       beta
    e       gama

I'm doing this query, but getting indicator=alfa for every row.

UPDATE A
SET    indicator = B.indicator
FROM   B
JOIN   A aa ON B.type = aa.type

The result that I was looking for A is something like this:

 gid, type,  indicator
    1       a      alfa
    2       b      alfa
    3       e      gama
    4       a      alfa
    5       d      beta

Hope that was clear


texts: Transaction log reported full but is not

texts:
Batch jobs error emails report:

  The transaction log for database 'WebDB' is full.

No, it isn't. Its 90% free. The transaction log backups run normally. My hardware guy tells me the hardware is running perfectly, with plenty of space. SQL Server 2008 SP3, but not R2.

I'm a developer, we no longer have a DBA. So it falls to me to try and fix this.


texts: What is Oracle equivalent for Linked Server and can you join with SQL Server?

texts:
In SQL Server I can create a view that is a join between two tables that are in completely different servers using Linked Server. If I change one of the servers from SQL Server to be Oracle, can I still do the same?

I need the join table to be in Oracle


texts:
I need to implement Change Data Capture(CDC) for PostgreSQL 9.2. I planned to use Debezium for this purpose but looks like it is only supports PostgreSQL 9.4+ https://debezium.io/documentation/reference/1.2/connectors/postgresql.html. Earlier versions do not support logical decoding.
Please suggest how to implement CDC for PostgreSQL 9.2. Is it only possible with triggers or how?


texts: Why monitor bytes sent and received on mysql

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

Thanks.


texts: How can I force a bushy tree join in Oracle SQL?

texts: As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?


texts: There is ONE index rebuild which everyday is blocked causing the job to fail.  It is the same index and same stored procedure which blocks.  We used an automated stored procedure to rebuild or reorg each index based on fragmentation.  How can I fix this issue?  Any ideas are greatly appreciated.


texts: Some view to find out which jobs are in the queue to be executed?

texts:
A environment start several refreshes along the day.

If I change the job_queue_processes to 20 for example, it start several new jobs. Is there some view that I can see which jobs is waiting and will be executed if I change the job_queue_processes?
Observation: the jobs have been created with dbms_job.


texts: Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view?

texts:
Sorry for the long question, but let me give you my scenario.  I have a DB that houses data exported from another DB.  The containing tables are copies from the other (non-SQL) database.  Each column name in these export tables is named using the following pattern:

UserFriendlyName_OriginalColumnName

Since our user's are primarily interested in the UserFriendlyName portion of the column, we have created a View for each table, where the name of each field is strictly the UserFriendlyName portion of the column name.

Most of these Views are strictly queries of one data table.  For some of these views, however, I link to one other data table to pull out a description field or two.  Note, this is never more than one linked table.

Performance wise, which method if better?  If I am linking one data table to another for a description field, can I link straight to the description tables View so that I don't have to alias the column?  Is it significantly better to use two data tables in the View and alias each column from the parent tables?

This may seem a bit arbitrary but this data exportation project is getting bigger and bigger.  I'd like to formalize my process in how I link my information together.  The goal is to strictly use the Data Views if and when possible.  However, if this adds a sizable amount of performance degradation, I'd rather stick with always using Data Tables in all of my Data Views.


texts: How can I check if my user has create index permission?

texts:
I'm working on a .NET library that is used with a third-party product I support.  My library may be used with many different versions of the product.  I don't control the schema of that product, but I can safely add additional indexes on standard tables.  My library is installed into other projects in such a way that we decided it needs to bootstrap itself at startup, rather than counting on whoever uses it to perform additional install steps.

My library has some startup code that depends on data from a standard table.  Some versions of the product are missing an index which, when missing, makes my library's startup painfully slow.

Some versions of the product do include the index, but the name of the index may vary.  I've already worked out how to detect if there is an index on the table that includes exactly the fields I want or not.  I also know how to create the index I want when it's not present.  (Maybe because this is the very first run, or maybe because someone dropped it.)

So I know whether to create the index, but I don't know if my connection has the permissions to do so.  I don't control the connection string -- I just use whatever is provided to me.

select @hasIndex = 1
    where {table has an index on the column I want}

if @hasIndex = 0
begin
    if {magic here: user has permission to create indexes}
    begin
        exec ('create index ix_TableName_FieldName on TableName(FieldName)');
    end
end

I could try to create the index and just eat the error if it fails.  My .NET app can ignore the problem and continue, but it will still show up in the SQL errorlog, which could raise unwarranted concern.  Besides, it feels sloppy.  There's gotta be a good way to not try something that is going to fail, right?

Apologies if this is a duplicate.  I searched around quite a bit. The nearest match I found was about granting permissions (one time, manually) to the user in order to restore a database.  I'm trying to check permissions (each startup, unattended) to keep things in line going forward.


texts: Read uncommited does not work in UPDATE WHERE NOT EXISTS

texts:
I'm not able to force SQL Server (2016) to respect values updated in the same statement.

What I need to do is to update old values (column a in table #t in demo code below) with mapping table (#u) to new ones (column new_a in table #u)

After the update rows containing old values would be deleted.

Here is the demo code:

DROP TABLE IF EXISTS #t
DROP TABLE IF EXISTS #u

CREATE TABLE #t (a int, b int)

CREATE UNIQUE CLUSTERED INDEX t_idx ON #t
(
    a ASC,
    b ASC
)

CREATE TABLE #u (old_a int, new_a int)

INSERT INTO #t (a, b)
VALUES
(1,1),
(1,2),
(2,1),
(2,2)

INSERT INTO #u (old_a, new_a)
VALUES
(1, 3),
(2, 3)

UPDATE t
SET t.a = u.new_a
FROM #t AS t WITH (READUNCOMMITTED)
JOIN #u AS u
ON u.old_a = t.a

WHERE NOT EXISTS (
    SELECT 1
    FROM #t AS t_top WITH (READUNCOMMITTED)
    WHERE 1=1
    AND t_top.a = u.new_a
    AND t_top.b = t.b)

which results in

Cannot insert duplicate key row in object 'dbo.#t' with unique index 't_idx'. The duplicate key value is (3, 1).

And while I can do it other ways (eg. disable unique index, then delete duplicates) I would like to know why SQL Server does not want to read freshly updated values.

Also, is it possible to somehow update with commits after each row? Preferably without using stored procedures.

SQL Server version is 2016

EDIT:
SQL Server sees the values when checking for FK violations, but not in WHERE EXISTS clause

EDIT 2:


texts: Closure of attributes definition clarification

texts:
A databases book I am using states [1]:

  The closure of {A1, A2, ..., An} under the FD's in S is the set of attributes B such that every relation that satisfies all the FD's in set S also satisfies A1 A2 ... An -> B.

I'm not trying to nitpick, but shouldn't it say "...the set of all attributes B..." or am I reading it too strictly? The blockquote above seems to indicate any attribute functionally determined by {A1, A2, ..., An} is a closure, yet the algorithms given later in the text seems to define a closure as all attributes functionally determined.

[1] Ullman, Jeffrey D., and Jennifer Widom. A first course in database systems. 3rd ed. Upper Saddle River, NJ: Pearson/Prentice Hall, 2008.


texts:
I have below table structure where one department can have many employees.

Department
    id
    dept_name
    ...

Employee
   id
   emp_name
   dept_id(foreign key to department table)
   ....

Consider  I have 30 departments and each department has 10k records.

Now I fire below query

select * from department dept, Employee emp where emp.dept_id=dept.id and dept.id=10;

My question is there any benefit on creating index on foreign key column dept_id of table Employee ?

Per my understanding, there should be benefit of creating index on column dept_id as without index DBMS has to scan all records of Employee
table to find dept_id=10. If there is index then it can quickly find the range of record having dept_id=10 with the help of non clustered index.


texts:
I'm working on a request to start logging some additional data for PCI compliance.  I need to log who, when, and what changed on a set of tables.  I set up SQL auditing which would work fine except for the fact that the values of what is being changed is coming through as @1, @2 etc instead of the actual data.  What are my other options for logging the needed information?  CDC does not track who made the change.

This is currently running on SQL Server 2012 but a possible upgrade to 2014 is possible if that would provide a better solution.

Thanks,
Tim


texts:
I have a SQL with multiple join left, because some data is empty.
However, I need to sort the data using a string column from a join table.

I thought about creating an index for this table, but this index does not affect this SQL.
How can I lower the cost of this SQL?

SELECT
*
FROM
sales s
LEFT JOIN salesman sm ON s.idsalesman = sm.id AND sm.account = s.account
WHERE s.situation = 2
AND s.account = 123
ORDER BY sm.name DESC
OFFSET 0
LIMIT 30;

CREATE INDEX idx_salesman_name ON salesman (name  NULLS FIRST);

This is the Explain Analyze:

Limit  (cost=5228.94..5229.01 rows=30 width=2153) (actual time=7.435..7.459 rows=30 loops=1)
   ->  Sort  (cost=5228.94..5240.47 rows=4615 width=2153) (actual time=7.434..7.455 rows=30 loops=1)
         Sort Key: sm.name DESC
         Sort Method: top-N heapsort  Memory: 87kB
         ->  Hash Left Join  (cost=36.57..5092.64 rows=4615 width=2153) (actual time=0.992..6.634 rows=153 loops=1)
               Hash Cond: ((s.account = sm.account) AND (s.idsalesman = sm.id))
               ->  Index Scan using idx_sales_conta_situation on sales s  (cost=0.43..5021.83 rows=4615 width=1252) (actual time=0.096..5.489 rows=153 loops=1)
                     Index Cond: ((account = 123) AND (situation = 2))
               ->  Hash  (cost=35.33..35.33 rows=54 width=901) (actual time=0.811..0.811 rows=55 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     ->  Index Scan using idx_salesman_account on salesman sm  (cost=0.29..35.33 rows=54 width=901) (actual time=0.052..0.748 rows=55 loops=1)
                           Index Cond: (account = 123)
 Planning time: 5.513 ms
 Execution time: 7.854 ms
(14 rows)

PostgreSQL Version: 9.5.14


texts: Insert Does Not Increment Primary Key Correctly

texts:
I need to figure out how to insert into this table without breaking the application that runs on it. When I run an insert statement outside the application, I select the max uid (which is the indexed primary key) and add one, then insert the data with that uid.

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

Something does not make it down to the application though and it will not allow new inserts into the table through the application until I delete the rows I inserted through SQL. It gives an SQL error that the primary key customer_salesrep_uid is already taken. The application does not seem to know about the inserts I made outside of it and the increases to the uid field. There must be a counter somewhere that I need to update? The index should update by itself on insert.

Update 8/10 re comments:
Thank you all for your advice. I will try the profiler and also will try inserting in test with proper locks, with a lower uid than max, and with a much higher uid than max and see if I can glean any further clues from that.

Table Structure:

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
    customer_salesrep_uid   int no  4   10      0       no  (n/a)   (n/a)   NULL
    company_id  varchar no  8                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    customer_id decimal no  9   19      0       no  (n/a)   (n/a)   NULL
    salesrep_id varchar no  16                  no  yes no  SQL_Latin1_General_CP1_CI_AS
    commission_percentage   decimal no  9   19      4       no  (n/a)   (n/a)   NULL
    primary_salesrep_flag   char    no  1                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    row_status_flag int no  4   10      0       no  (n/a)   (n/a)   NULL
    date_created    datetime    no  8                   no  (n/a)   (n/a)   NULL
    created_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS
    date_last_modified  datetime    no  8                   no  (n/a)   (n/a)   NULL
    last_maintained_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS

No Identity columns

Indexes:

    index_name  index_description   index_keys
    ak_customer_salesrep    nonclustered, unique, unique key located on PRIMARY company_id, customer_id, salesrep_id
    pk_customer_salesrep    clustered, unique, primary key located on PRIMARY   customer_salesrep_uid


texts:
Could anybody tell me how to do insert statement with inner join.

i am trying to insert
ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And i also want to fetch Category_id to the Category_id_FK.

I am using SQL Server 2008


texts: Are semicolon-delimited Postgres commands run serial or parallel?

texts:
INSERT INTO ...; UPDATE ...; are these commands run sequentially (to completion) left-to-right, or in parallel? Or "it's not that simple"?

TL;DR I'm trying to circumvent 9.4.4's lacking UPSERT by inserting first if missing, then updating both new and existing after:

-- First bulk create any missing tags
INSERT INTO user_tags (user_id, tag_id, score)
SELECT :user_id, t.tag_id, 0
FROM (
    SELECT tag_id
    FROM post_tags
    WHERE post_id=:post_id
    EXCEPT
    SELECT tag_id
    FROM user_tags
    WHERE user_id=:user_id
    ) t;
-- Then increment their score
UPDATE user_tags
SET score = score + 1
WHERE tag_id IN (
    SELECT tag_id
    FROM post_tags
    WHERE post_id=:post_id
    )
    AND user_id=:user_id;


texts: Best Approach to Migrating Oracle to MySQL - Run on EC2 in AWS

texts:
I'm trying to migrate an Oracle schema as well as all of the data within the DB to a MySQL database.  I want to then make an EC2 app on AWS to kick off whatever I've written for this.  Are there any approaches that one might say is better than another (for instance, use Ruby/Rails instead of Perl)?  Is there a tool that I might be able to wrap into an app and place it on my EC2 instance?

Full disclosure, I'm pretty much a complete newbie when it comes to AWS, so if there is something really basic that I'm missing (you could use some type of Linux-based MySQL Workbench-like tool) I would definitely appreciate that.

I realize this is a bit specific, thank you in advance!


texts:
We do not have access to our servers with operating system console. Our production environment is Ubuntu 12.
Is it possible to use mysqltuner from remote host?
If yes, please provide guidelines. We are using mysql5.6


texts: Unable to start Oracle Database Server : Get an error as I try to start it

texts:
I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop :

but as I click this icon I see this dialog box :

What is it ? How do I start my Oracle Database Server ?


texts: Log-in as service account in SQL Server Management Studio

texts:
The stored procedure that performs a CTE in production is being executed using a service account. However, the stored procedure returns empty and I tried checking this using SSMS but I my domain account has no execute functionality in production.

I tried logging into SQL Server Management Studio using the service account but I ran through some problems. First was resolved by following this link - The certificate chain was issued by an authority that is not trusted

However, once that is executed, it returns as if no user name was provided:

Question is, can I login using a service account in SQL Server Management Studio?


texts:
I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key.

After a lengthy discussion in which I tried to explain that he can't guarantee ordering based on the primary key the final conclusion was that he wasn't going to push for the MSSQL queries to be changed.
But he was still going to change the DB2 queries.

I couldn't immediately find an article disproving that DB2 orders queries by the primary key, and am currently wondering whether or not it does.

So my question is, how does DB2 order a query if there is no order by clause? Does it use the primary key?
How can you guarantee data is coming out ordered correctly, without an order by clause, in a parallel system?


texts:
I have this query that throws two results:

SELECT id FROM table1 WHERE id like 'nm041033%'

nm0410331
nm0410331

And this slightly different query that throws only one result:

SELECT id FROM table1 WHERE id='nm0410331'

nm0410331

I tried to check the ASCII of the last character and got the same:

SELECT id,ascii(substr(id,9,1)) FROM table1 WHERE id like 'nm041033%'

nm0410331 49
nm0410331 49

I guess it is a rare encoding problem. How can I solve it?

PS: The field id is a primary key. The charset is latin1_general_ci, and the values were inserted using PHP utf8_decode().

UPDATE: I changed the charset to ascii_general_ci, and now this query gives me zero results:

SELECT id FROM table1 WHERE id='nm0410331'

However, those two ids are not the same yet. If I use SELECT DISTINCT or GROUP BY I get two rows.

PS: The last character isn't the number you can type with the keyboard.


texts: MySQL 5.7 No Longer Returns Meaningful Error Messages

texts:
When I select from a table that doesn't exist:

When I select a nonexistent column from an existing table:

I used to receive meaningful error messages (ie 'Error Code: 1054. Unknown column 'column' in 'table''), but now only the error number is returned, without an explanation of what happened. This happens to everyone that connect to this particular MySQL database.

Does anyone know how I can fix this without reinstalling MySQL? I'm running MySQL 5.7.9 Community Server (64-bit) on Windows Server 2012.


texts:
My title probably isn't the best, but essentially I have a table that stores a host, vulnerability finding reference, and status.  I am trying to query on those, but where I don't have an entry I want to force it to show me a defaulted value for the status.  Here's an example...

SELECT f.host_id, f.`status` FROM findings f WHERE f.finding_id = 1 AND f.host_id IN (1,2,3)

So if there is an entry for 1 & 2, but not 3, I will want an entry for 3, but with a defaulted status value of 'No Data'.

TIA

EDIT:
I am trying to avoid doing 3 queries because I may have dozens of hosts in that list and that would be a major performance hit!


texts:
I created a SQL Server audit and when I view the audit logs I only see today’s audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit for one year? Does SQL Server store audits for only a day?

CREATE SERVER AUDIT [Audit-20130618-142022] TO FILE
(
  FILEPATH = N'C:\Documents\Audit\Audit' ,
  MAXSIZE = 0 MB ,
  MAX_ROLLOVER_FILES = 2147483647 ,
  RESERVE_DISK_SPACE = OFF
)
WITH
(
  QUEUE_DELAY = 1000 ,
  ON_FAILURE = CONTINUE
);


texts: Return results of DBMS_SQL.DESCRIBE_COLUMNS in Oracle 11

texts:
Database: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

I would like to create a script that will return the results of the DBMS_SQL.DESCRIBE_COLUMNS function to my c# application.  Here is the code I have so far:

DECLARE
    refCur sys_refcursor;
    colCount number;
    descTable dbms_sql.desc_tab;

FUNCTION get_ups(foo number) RETURN sys.dbms_sql.desc_tab PIPELINED AS

    BEGIN
    OPEN refCur FOR
    SELECT * FROM MYOWNER.MYTABLE;

    DBMS_SQL.DESCRIBE_COLUMNS(
        dbms_sql.to_cursor_number(refCur),
        colCount,
        descTable);

    FOR i IN 1 .. colCount LOOP
    PIPE ROW ( null );--descTable(i) );
    END LOOP;

        RETURN;
    END get_ups;

BEGIN

  SELECT *
  FROM table(get_ups(0));
END;

Here are my constraints:

I do not have create permissions on the database.
I need to be able to describe arbitrary queries, not just tables.
I'd like to consume the result as a dataset in C#. (It's for an ETL tool.)


texts: How to get transaction command text from SQL server Log file

texts: I got a list of the transaction from fn_dblog and would like to get the command text of the specific transaction by [Transaction Id]. Would you please guide me about that


texts: Unable to create connection on MySQL Workbench Edition in Mac?

texts:
I have installed MySQL Workbench on my Mac. I am trying to connect the MySQL. I am not able to connect.

I have attached the screenshot.

I am getting Access denied for user 'root'


texts:
Does the NEWID() function generate UUID version 4?

Link to Wikipedia with versions

What is the situation with the NEWSEQUENTIALID() function?

Does the data always provide us with the UUID standard and version? Maybe it should be generated on the application side?


texts:
Our company is considering a new backup product that is an all in one sort of thing (file system, AD, SQL, etc). The product relies on VSS snapshots to create the full backups for the SQL databases. This causes an I/O freeze on the databases that usually just lasts a second or two, but can take up to 60 seconds (per Microsoft, I believe).

My question is how bad are I/O freezes for databases?  Also, is there any way to determine how long the I/O freeze will be?  I would think it would depend on a number of factors including: the size of the database, activity on it at the time, CPU usage, disk I/O, etc.

I have only been a DBA for less than a year and have not seen this myself, but my coworkers informed me that we have customers who do bulk data loads and in the past even a 1 second I/O freeze on the database will cause their jobs to fail.

We have voiced our concerns to management, but it is likely they will purchase the product.

Thanks for your help!


texts: SQL Server 2012 - Insufficient memory to continue the execution of the program. (mscorlib)

texts:
I have a sql file, which is up to 180 Mb. When I try to open and execute it on Microsoft SQL Server Managment Studio 2012, I am getting this error.
 . What do do ? I researched on Google. And there were a few answers. One solution they say was running a C# script. Here is it . But I don't know how to run, and anyway if that script will work or not. Thanks for help.


texts: In what case is a count(x or null) needed in Gaps and Islands?

texts:
In this answer, Erwin Brandstetter says:

count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern Postgres, the cleaner, equivalent syntax would be:
count(step) FILTER (WHERE step) OVER (ORDER BY date)

I'm unsure of why count(step OR NULL) is preferred though. In my query, I do the following. I renamed my variables to match his while maintaining the syntax.
CASE WHEN lag(id_type) OVER (ORDER BY date) <> id_type THEN 1 END AS step

We're counting the values returned by that. Note, that the case can only return 1, or null.

if the two ARE NOT equal, 1 is returned.
if they are equal, it returns null which isn't counted.

Erwin's answer has:

This assumes involved columns are NOT NULL. Else you need to do more.

So I'm even more confused. What is the point of adding count(step OR NULL) what is this protecting our query against?
Could anyone break this down and perhaps show two examples with data wherein only one of them - the one with - count(x OR NULL) works?


texts: MySQL - automatically update all references to specific table to use on delete cascade?

texts:
I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific and expects lower. This table is referenced by a ton of other tables however, some of which may have references back to these users.

Is there a ready way to adjust the foreign keys for all tables in my database which reference a given column 'username' in table 'logins' to have the on delete cascade attribute set? I don't see a ready way of doing so.

What I need is to change all the other tables with foreign keys depending on this table to utilize an on delete cascade. I'm hoping to do so without manually changing a hundred tables.

The select I'm utilizing is

delete from user where user_name REGEXP '^.*[A-Z].+$';

The issue is that the application has created duplicate user IDs in the user_id field. I only want to retain the ID number for properly created usernames.

The select above targets exactly what I want to get rid of. I don't have access to the application source which created these entries.


texts: Which factors impact the time for SQL Server Recovery to complete

texts:
In a recent planning AWS RDS failover we experienced long recovery times of around 20 minutes. AWS provides some hints around why.

large transactions or a lengthy recovery process can increase failover
time.

The database itself doesn't make much use of large transactions, so I don't think this is the culprit. Therefore I'm interested to understand what factors might result in a lengthy recovery process?

Does the SQL Server version have an impact (We are on 2016)
Does the size of the database have an impact?

The recovery process took around 20 minutes and we'd like to take steps to reduce this to around 10 minutes.
Update:
One thing I didn't mention is, due to years of bad design about 50% of the database size is due to storing blobs of files, such as html or XML. Could this have any impact on recovery time?


texts: Audit OSQL command shutting down a SQL Server

texts: I need to determine when, and by whom, a SQL Server was shutdown using the osql command. The osql command takes user name and password input. Are there any specific Windows Event logs generated to identify the user, time etc.?


texts: Power BI Report Server keeps asking for credentials locally

texts:
I've installed PBIRS on my laptop to do some development.
Each time try to connect it prompts with dialog wanting me to login.

If I try and connect from a remote PC using the same domain login, it recognises me and logs me in without any problems.
What do I need to change so that PBIRS accepts my connection?
PBIRS May 2020
SQL Server 2019
HP Elitebook 16Gb RAM
Windows 10.


texts: Organizing a comments table: How to justify the choice?

texts:
Business domain description

Some of the entity types of interest with respect to the relevant business domain are money, user and progress.

The users may need to post comments about money and progress.

In the future, maybe there will be more entity types related to comment, not only money and progress.

My considerations

I have three solutions:

One comments table for all, keeps the "commented entity id" and the "type of entity" of relevance.

CREATE TABLE comment (
    user_id …, -- the identifier of who leaves comment
    text …, -- text of the comment
    created_at …, -- tells when comment has been left
    subject_type …, -- 'money' or 'progress'
    subject_id … -- identifier from 'money' or 'progress', depends on the 'subject_type' value
);

One comment table for all. Each table retains a foreign key in the comment_id column. The table has a "complex" primary key (id, created_at), and the id values are not unique.

CREATE TABLE comment (
    id…, -- not unique
    created_at…, -- indicates when the comment was left
    user_id…, -- identifier of who leaves comment
    text…, -- the text of the comment
    CONSTRAINT comment_pk PRIMARY KEY (id, created_at) -- "complex" primary key
);

When I need to get all comments associated with a given entity instance, I obtain them by means of an operation like SELECT * FROM comment WHERE id = comment_id.
Separate tables, e.g. comment_money, comment_job, ... All the tables will look the same:

CREATE TABLE comment_<entityName> (
    id…,
    entity_id…, -- id of the commented entity
    user_id…, -- who leaves comment
    text…, -- text of the comment
    create_at… -- when the comment was left
);

I prefer the third variant despite the fact that I have to create more tables, because the logic will be really simple and this approach is easy to manage and support.

Question

I will pass the model of database (see "business domain description") to a teacher and I'm not sure how to justify my choice. I think that the teacher can say that my approach is bad because all my tables have the same columns and structure.

What would be good arguments?
Maybe there are more solutions?


texts: Why do I get "Truncated incorrect date value" when using str_to_date()?

texts:
I have data in one of my tables as "Sun Dec 14 22:49:13 2014 -0800" and need to load it into another table but skipping the time filed i.e. HH:MM:SS.

I used:

insert into final_data
select Book_Name, Author_Name, Author_email,
str_to_date(substring_index(date_string,' ',4),'%a %b %d %Y'),
Total_Sold
from data;

  ERROR 1292 (22007): Truncated incorrect date value: 'Sun Dec 14 22:49:13'

I can use str_to_date(substring_index(date_string,' ',5),'%a %b %d %T %Y') but that will add HH:MM:SS in the column as well.
Is there a way to load the data skipping HH:MM:SS ?
i.e instead of 2014-12-14 22:49:13 it diplays 2014-12-14


texts: Combined column data display only unique value

texts:
This is my source table. I want to combined column data and display unique data only.

SQL> SELECT * FROM DUMMY;

PLAN_CD              PLAN_NM              CLASS
-------------------- -------------------- ------
D01501               DENTAL               AA
D01501               DENTAL               AB
D01501               DENTAL               AC
V01501               VISION               AA
V01501               VISION               AB

And i want to ouput like this.

PLAN_CD              PLAN_NM
-------              --------
D01501               DENTAL,AA,AB,AC
V01501               VISION,AA,AB

I am using oracle 10g.Please give me query for oracle 10g.

Thanks in advance.


texts: How to decide the parameter processes count?

texts:
I am using oracle 11g database and application deployed on weblogic 12.2.1 while I tried to access my application today, I faced an issue  as "Listener refused the connection with the following error:

  ORA-12516, TNS:listener could not find available handler with matching protocol stack".

Verified the process parameter limit which was almost near the max limit so I altered the process value and increment it to 500 using the below alter command:

alter system set processes=500 scope=spfile;

Now, everything is fine. But I provided the process count on a vague idea.

My question is:

How do I decide the processes count. Is that based on my user count?
Do oracle have any calculation method for this or any other dependency?


texts: Get Nested/Hierarchical Data from SQL Table

texts:
I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.

The TagAssignment table stores the relationship between a Tag and a date. So a tag can be mapped to a single date(period).

I want to output a nested hierarchy from sql.

SQL Tables:

-- tag assigned to a specific date
CREATE TABLE [dbo].[TagAssignment](
    [TagAssignmentID] [int] IDENTITY(1,1) NOT NULL,
    [TagID] [int] NOT NULL,
    [Period] [date] NOT NULL
);

-- task(s) mapped to tag(s)
CREATE TABLE [dbo].[TagTaskMapping](
    [TagID] [int] NOT NULL,
    [TaskID] [int] NOT NULL
);

-- tag table
CREATE TABLE [dbo].[Tag](
    [TagID] [int] IDENTITY(1,1) NOT NULL,
    [TagName] [nvarchar](150) NOT NULL
)

TagAssignment mapping table data:

TagAssignmentID TagID   Period
24                3    31/05/2017
14                2    31/05/2017

TagTaskMapping table data:

TagID   TaskID
  2       1
  2       2
  2       3
  3       1
  3       3

Here is my query...

DECLARE @Period datetime = '2017-05-31'
;WITH CTE_TagAssignment
AS
(
    -- GET TAG(S) Assigned to selected PERIOD
    SELECT
         ta.TagID
        ,t.TagName
        ,null as 'Task'
    FROM dbo.TagAssignment ta
    INNER JOIN
        dbo.Tag t
         ON t.TagID = ta.TagID
    WHERE ta.Period = @Period

    UNION ALL

    /**USING RECURSION!!!!**/
    -- foreach above tag assigned to a period, get it's associated task(s)
    SELECT
        ttm.TagID
        ,null AS 'TagName'
        ,ttm.TaskID as 'Task'
    FROM CTE_TagAssignment cta
    INNER JOIN
        dbo.TagTaskMapping ttm
        ON cta.TagID = ttm.TagID
)
SELECT *
FROM CTE_TagAssignment
OPTION (MAXRECURSION 100);

However, I get this error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

This is the hierarchy output I would like...

TagID   TagName   Task
2       Level 5
                   1
                   2
                   3
3       Level 3
                   1
                   3


texts: Select all from A where attribute exists in B

texts:
I have a question involving relational algebra. Suppose I have two relations A and B, where B has a foreign key that references A.
Using relational algebra, how do I select all tuples from A where variable X exists in B? Example:

Relation 1
Cars(car_id,model)

Relation 2
Ford(car_id,year)

How do I select from Cars, all the tuples where id exists in Ford? Example:
Cars  car_id  model
      -------------
       1    a
       2    b
       3    c

Ford  car_id   year
      1        2000
      3        2002

What I would like is: select all from cars where id exists in Ford, which returns:
Result:  car_id  model
        --------------
         1    a
         3    c


texts:
The vacuum of two pg_catalog tables runs into the following error and stops there.

  found xmin X from before relfrozenxid Y during vacuum

I could find a lot of discussions about this problem but solutions are hard to find. Is there any solution for this problem other then restoring the database from a backup?

postgresql 9.3.22 (I know it is old, the server is being phased out but needs to continue working for now).


texts: Does SQLite choose wrong query plan when rowID is the second column of an index?

texts:
I was testing the performance of my DB and found that SQLite wasn't choosing the right query plan for my query.

My schema:

CREATE TABLE event (
    ID       INTEGER PRIMARY KEY NOT NULL,
    netAddr  INTEGER             NOT NULL,
    date     INTEGER             NOT NULL,
    value    INTEGER             NOT NULL);
INSERT INTO event ( netAddr, date, value )
    WITH RECURSIVE cnt(x) as (
        VALUES(1)
        UNION ALL
        SELECT x+1 FROM cnt LIMIT 1000000
    ) SELECT 250, x, x+1000 FROM cnt;
CREATE INDEX myIdx ON event ( netAddr, ID );

The query:

SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

It ran too slow and I inspected the query plan:

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=?)

SQLite refuses to use the second column of the index even though it is sorted the way that the query wants it to be.

Then I tested with a different index:

CREATE INDEX dateIdx ON event ( netAddr, date );
EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND date > 100 AND date < 110
ORDER BY date DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date>? AND date<?)

The query plan is now as I expect it to be.

Why doesn't SQLite use the whole of the index i.e. SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)?
What is wrong with having the rowID as a second column in an index?
Is there a way to make it work without adding another column in the table or in the index? (I have little storage for the DB.)

EDIT:

Tried with SELECT value FROM event WHERE id = (SELECT id FROM event WHERE netAddr = 250 AND id > 100 AND id < 110 ORDER BY id DESC LIMIT 1) LIMIT 1; and SELECT value FROM event WHERE netAddr = 250 AND ID > 100 AND ID < 110 ORDER BY netAddr DESC, ID DESC LIMIT 1; as ypercubeᵀᴹ proposed - the results are the same as the "slow" query.

Tried to change the name of ID column to be something else - the results are the same.

EDIT 2:

SQLite version 3.8.2 and 3.8.8.1


texts: MySQL 5.1.x: Performance improvement in Enterprise edition Vs community edition

texts:
My application is running on MySQL 5.1.58 community edition.
I tried to search enterprise edition of MySQL 5.1.X but only MySQL 5.6.X version of enterprise edition is available.
Is it possible to download MySQL 5.1.x enterprise edition - trial version? I want to compare the performance testing with the community edition of MySQL 5.1.X


texts:
I was cleaning up database (SQL Server 2012 SE) by deleting old data and truncating some unnecessary tables. After deleting and truncating data performance improved and had 67% free spaces out of 16 GB database. Then run following command to shrink database with 10% free space.

DBCC SHRINKDATABASE (databasename, 10);
GO

After that database reduced to 7GB but having performance problem, see that attach screenshot. By the way, I also rebuild indexes but no luck. Getting low application performance index (Apdex) alert from New Relic app monitoring tool.

Look at the DMV query results, at least tweenty queries taking more then 1000ms! I had only 2 queries that exceed 1000ms limit.


texts: Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

texts:
I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}).

There're some rare cases when I want to ensure that each translation is unique per locale. I know that I can create an index for each locale:

CREATE UNIQUE INDEX ON categories ((title ->> 'en'));
CREATE UNIQUE INDEX ON categories ((title ->> 'ru'));

This solution has one downside that I would need to create a new index for every column which has such constraint if I decide to add a new locale. Is there any way to specify a uniqueness constraint that will hold for every key-value pair of a JSONB object?

EDIT 1 (in response to the Evan's answer). There seems to be some sort of confusion cased by the term "unique/duplicate translation per locale", so I will try to clarify it. "Unique translation per locale" means: for every locale of each record in the table, there is no other record that has exactly the same translation for that locale. "Locale" is the key of the JSONB object that holds translations. "Translation" is the value of the JSONB object.

Here are some examples: the code

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hello"}');

should yield a uniqueness constraint violation error because it has duplicate "Hello" translation for the en locale, while the code

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hi"}');

should not, because it has different translations for the en locale.


texts:
As you can find here Best Practices For Horizontal Application Scaling, vertical scaling is much more expensive than horizontal scaling:

  vertical scaling cost increase exponentially whereas with horizontal
  scalability cost is linear.

Why is it so?
Vertical scaling means "adding more power to existing machines", while horizontal scaling means "adding more machines".

In my opinion, upgrading existing machines should be cheaper, as some parts already exist and you only have to buy additional processors, RAM, etc., whereas buying whole new machines should result in higher costs.


texts: Almost sequential GUIDs vs random GUIDs?

texts:
I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server.

In my webb-app I have many clients who will create items client side in javascript and save them only now and then.

The client code will create sequential GUIDs but items won't allways be saved straight away.

Am I loosing much of the benifit of using sequential GUIDs when the rows aren't saved in a perfect order?

Also, are there in the same way problems with occasionally mixing in normal random GUIDs?


texts: How to restart server without rebooting system

texts:
Local server
PostgreSQL\9.6
PgAdmin 4 v3.2

A least once an hour pgadmin gives me an out of memory error followed by "Not connected to the server or the connection to the server has been closed.". The only way I know to recover is to reboot my system.
I know I may have other issues, like why am I running out of memory and why am I losing the connection but for right now I just want to know if there another way of restarting the server afresh without rebooting?
If so, please explain in simple newbie terms :)


texts:
I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a table called corrections. All my basic objects are compiling fine, just trying to build the assignment_tbl is a problem as im not sure how to use the nested table statement.

CREATE TYPE correction_info_objtyp AS OBJECT
(
   correction_date   DATE,
   raised_by         VARCHAR(50),
   problem           VARCHAR(255)
);

CREATE TYPE correction_info_ntabtyp AS TABLE OF correction_info_objtyp;

CREATE TYPE assignment_status_info_objtyp UNDER status_info_obj
(
   corrections     correction_info_ntabtyp
);

CREATE table assignement_tbl
(
  assignment_id       NUMBER(6),
  assignment_title    VARCHAR(40),
  elements            elements_v,
  assignment_status   assignment_status_info_objtyp
)NESTED TYPE assignment_status STORE AS assign;

-- I know this nested type isn't going to work because it's not a table,
-- But inside the assignment_status_info_objtyp is correction_info_ntabtyp which is
-- a table and needs the nested type statement

I've tried to follow the example here with the planets and satelites, where they have a nested table in a nested table, but for my situation i have a nested table in a type because I only ever want one instance of assignment_status in the assignment.

Any help, hints or even a link to an example or a page in a book would be of fantastic use.

Exhausted first 20 results of Google already.


texts: What is Server and Client type subscriptions in Merge Replication? How to select one of these during merge replication setup?


texts:
Im working on a project for my company which sells huge amounts of weather data to customers through a Web API. As part of that API, there is a backend service which needs to fetch data from MYSQL database (ver 5.1.1, InnoDB). The data set can reach amount of 50 000 rows and 12 columns if interval is set to whole year, which is very common. MYSQL database runs on Red Hat Linux 64bit, 4 core server computer.

Query which calculates, for example, daily averages on set of 50000 rows executes in 3.5 sec. However, if i run 4 of those queries in parallel, execution time for each of them reaches average 8sec per query. Im also monitoring client connection on DB and I can clearly see there are 4 threads executing each query. CPU usage on server during execution goes from 0 to cca 50% usage.

My question is, why execution time rises if I run same query in parallel? Im I missing something or this is expected behaviour?

More server info: CPU: Intel Core I5-4440 @ 3.1 GHz (4 cores), Memory: 8 GB

EDIT: Increase of innodb_buffer_pool_size from 530 MB to 5.5 GB solved problem!


texts: Error while configuring mirroring, Microsoft SQL Server, error : 1418

texts:
I have a SQL Server 2008 Enterprise SP1 installed on my both principle and mirror server. SQL Server and SQL Server agent service is also started by same ID having sysadmin rights.

But when I am trying to establish mirroring between them, it is reflecting an error message, Microsoft SQL Server, Error:1418

I have checked the endpoint of both server that are in started state. and also dropped and create endpoints again. please note i am not using the witness server here.

Principle server endpoint port:- 5022
mirror server endpoint port :- 5023
mirror server at endpoint port 5022 also didn't work.
Both 5022 and 5023 ports are opened on firewall

but when I execute the following script on both server, asynchronous(high safety mode) mirroring established.

TO SETUP MIRRORING in synchronous(high safty) ON PRINCIPLE SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://principle server IP:5022'

TO SETUP MIRRORING ON MIRROR SERVER:-
ALTER DATABASE SMPP
SET PARTNER = 'TCP://mirror server ip:5023'

I also tried to put hostname of principle and mirror server instance in above script but no luck.

Please suggest any working solution as soon as possible.


texts: Postgres suggests "Truncate table ... at the same time"

texts:
When running:
TRUNCATE TABLE YYYYY RESTART IDENTITY

I am seeing this error message:
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "XXXXX" references "YYYYY".
HINT:  Truncate table "XXXXX" at the same time, or use TRUNCATE ... CASCADE.

Of the two suggestions in HINT:

Using TRUNCATE ... CASCADE makes sense, and works, but is less explicit because one must inspect YYYYY to see where the cascade goes. That makes me want to try the other option:

Truncate table "XXXXX" at the same time, but my question:
What does it mean to truncate a table at the same time?

I tried adding a TRUNCATE XXXXX... (and wrapping them both in a BEGIN / COMMIT), but that yields the same error.


texts: SQL Server Audit Specifications Not Writing to the Security Log

texts:
I've come across an issue where SQL Server database audit specifications have stopped writing to the security event log.  The audit object will write events to a SQL audit file or the Application event log but they won't write to the Security event log.

I'm not getting any error messages in any event logs or SQL logs and I've confirmed that permissions for the SQL Server service accounts have been configured correctly.

Some servers have multiple instances of SQL Server and there are server & database audits on all of the instances.  Some of the SQL Instances are 2012 SP4 and the others are 2016 SP2 CU6.

Does anyone have any ideas on what might be causing this behaviour?  Should I rollback the permissions and re-apply them?


texts: pg_restore error: Extensions installed out of sequence

texts:
I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command:

$ pg_dump -c -U postgres -Fc -f test.dump testdb

When I attempt to restore the database on a clean PostgreSQL 9.5 with pg_cron already installed via yum, I'm seeing error messages that the pg_cron extension could not be installed because the plpgsql extension does not exist:

$ pg_restore -Fc -d testdb -c -U postgres test.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4; 3079 18235 EXTENSION pg_cron
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "pg_cron" does not exist
    Command was: DROP EXTENSION pg_cron;

pg_restore: [archiver (db)] could not execute query: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.
    Command was: CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA testschema;

pg_restore: [archiver (db)] Error from TOC entry 3795; 0 0 COMMENT EXTENSION pg_cron
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "pg_cron" does not exist
    Command was: COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';

pg_restore: [archiver (db)] Error from TOC entry 3537; 0 18239 TABLE DATA job postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "job" does not exist
    Command was: COPY job  FROM stdin;

WARNING: errors ignored on restore: 4

While looking at text output from pg_dump, I noticed the CREATE EXTENSION commands are out of order--the file attempts to install pg_cron prior to installing plpgsql:

DROP EXTENSION plpgsql;
DROP EXTENSION pg_cron;
-- Name: pg_cron; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA testschema;
-- Name: EXTENSION pg_cron; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Is there a way to force pg_dump to ensure the CREATE EXTENSION commands are saved in the proper order?  Do I need to change my backup process to create a backup per schema and always restore pg_catalog first?


texts: How to recover Postgres replication slave after fatal error

texts:
On 4 Debian 8 Jessie servers, I have PostgreSQL 9.4.3 master + 3 slaves. After substantial data changes on master, slave logs showed this error:

LOG:  started streaming WAL from primary at 182/0 on timeline 1
FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000018200000000 has already been removed

What steps do i need to restore/rebuild the slaves?


texts: Can SQL Server full-text search tokenize words within words?

texts:
Suppose I have the following schema and data in SQL Server 2012:

create table Exclamations
(
    ID int not null primary key identity(1,1),
    Exclamation nvarchar(150) not null
);

insert into Exclamations(Exclamation) values
    ('Ohmygosh!'),
    ('Ohmygoodness!'),
    ('It''sabird!'),
    ('It''saplane!'),
    ('Whattheheck!'),
    ('Lookoutbelow!'),
    ('Whatintheworld?'),
    ('Isheforreal?'),
    ('Takeahike!');

create fulltext index on Exclamations(Exclamation) key index ID;

And suppose I want to do full-text queries against my exclamations, but I want to be able to query for words within words using a high speed index. Is that possible? Perhaps with a custom dictionary, or custom word-breaking/boundary behavior?

For example, I want to be able to query for the word "out" and have it quickly locate row #6 ('Lookoutbelow!') without having to scan through all the rows using a LIKE-ish operator. This also implies some false positives, for example, suppose I want to query for the word "he" and find row #8 ('Isheforreal?'); the query would also return row #5 ('Whattheheck!') since it contains the same "he" substring. That's OK.

Note that I do not need the tokenizer to index every possible combination of characters. I don't mind if nonsense words like 'sapl' within row #4 are not indexed. I only need it to tokenize the words it already recognizes (and possibly some additional words that I would add, but that is only a nice-to-have.)

It is important to specify that for the purposes of this question I cannot pre-parse the exclamations to insert obvious tokenizing boundaries such as spaces, hyphens, or periods, before inserting into SQL Server. I want to know if SQL Server can somehow do that itself.

EDIT: After some more reading, it appears that what I want is generally called N-gram tokenizing. But I want it limited to tokens that are already present as, or variants from, words in the dictionary.


texts: Dump Oracle database into a textual SQL script

texts:
I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In MySQL and in PostgreSQL, this is the normal database dump format.

Does it exist on Oracle? How can it be done?

It is absolutely not a problem if it is not a standard Oracle thing. I need a solution to the problem. Exporting single tables is not enough, I am asking for the dump of a whole DB.

It is a reduced developer environment of a big project (with multi-million row DB), but still having hundreds of tables and a very complex structure. And, I want to play with it fast. While I am playing with it, I also need to be able to execute tricky modifications on that. On a binary dump I can't do that, on a textual I can.


texts:
I'm not sure if this is the right place to ask for tips and improvements in a database design but I don't know about another one.

I want to design a simple University system. Below I'll have included a schema that I've comed up with.

The idea is that each group will have multiple semesters. Number of them will be equal to study_programs.years_of_study * 2.

I want to store each semester for each group because of that fact that disciplines may appear or disappear over time. I'll be handling this change with creation/deletion of lectures. The main idea is that old semesters won't be touched - this way I can retrieve them later.

Is this a good way of keeping history intact? Maybe you can propose another methods? I'll happily listen to all of them. Thank you in advance.


texts:
I'm having some trouble with intentionally removing a secondary from a replica set. I'm trying to simulate unexpected shutdown.

My replica set consists of one primary and 2 secondaries. When I shut down the mongo instance on one of the secondaries and try to restart the service it fails with the message from the log which states:

Tue Dec 24 12:20:08.882 [initandlisten] couldn't open /var/lib/mongo/maars.ns errno:13 Permission denied

Simple chown -R mongod /var/lib/mongo solves the problem, but this shouldn't happend. Am I doing something wrong?

Ask for more info if you need something.


texts: Mysql - Select next x records even if reaching rowset end

texts:
I have a table with an ID field, unique, autoincremental.

I need to, given certain ID, retrieve next 3 records even when given ID is the last one.

Table:

ID
1
2
3
4
5
6
7

What I need:

Given ID is 1 => retrieve 2,3,4
Given ID is 2 => retrieve 3,4,5

.....

Given ID is 5 => retrieve 6,7,1
Given ID is 6 => retrieve 7,1,2
Given ID is 7 => retrieve 1,2,3

How can I do this?


texts: How to plan for change to log backup schedule?

texts:
I've looked everywhere but can't find an answer for this. I know it's an easy question, but I want to be sure I don't screw anything up.

I want to change our log backup from every 30 minutes to every 15 minutes. Our logs are growing too fast.

If I change to every 15 minutes, do I have to set a new start time? Currently is showing to start at 12:00 and end at 11:59.

If I change at 10:00 am, will the existing jobs still run until 12:00? or will nothing run until the new start time is hit?

So for example, if I set a new job at 10:00am, would I also need to change the start time to 10:00am?