texts: Is it worth to optimize queries with EarlyTermination Timeout?

texts:
Right after running Ola's IndexOptimize to rebuild all indexes and statistics, I cleared the data and plan cache and ran a very complex ugly query to get a baseline of it's performance. The query actual plan shows ReasonForEarlyTermination=Timeout and skews for Est Rows and Actual Rows (actual rows factor 10,000 compared to est.).

I wonder if this imbalance of the number of rows maybe a result of the Timeout - I thought it should be appropriate after the index & statistics rebuilding?

Does optimizing queries that end with ReasonForEarlyTermination=Timeout make any sense at all, if it's impossible to change the query itself? Since I planned to do some index tuning and re-running the query, isn't it impossible to be sure about the results of the possible improvements, because the next query plan creation will certainly timeout again and so might be different, with different results?

Tec. Details:
Sql Server 2008 R2 Dev. Ed. 10.50.6000.34 DB Comp. Lvl 80.


texts: How to repair mongodb collection when index is corrupt?

texts:
Mongo version: 2.6 I think one or more of my MongoDB index is corrupt which is not letting me delete the corrupt collection or make a backup with mongodump

Below is the error i get when i try to execute drop

>db.notifications.drop()
    "$err" : "no NamespaceDetails for index: { name: \"_id_\", ns: \"Bnk.notifications\", key: { _id: 1 }, v: 0 }",
            "code" : 17329

What can be done to get this collection removed or repaired if possible ?


texts: Is there any reason to back up these system-created certificates?

texts:
I understand the need for backing up keys and user defined certificates, but is there any reason that we should back up these certificates that are system-created?
MS_AgentSigningCertificate
MS_PolicySigningCertificate
MS_SchemaSigningCertificate34284BF7CE6208DC9D835E9F45ADD94F0757439E
MS_SmoExtendedSigningCertificate
MS_SQLAuthenticatorCertificate
MS_SQLReplicationSigningCertificate
MS_SQLResourceSigningCertificate


texts: Can anyone explain TokuDBs seemingly large amount of data_free for an append only table?

texts:
I have a large table I've been maintaining replicating in parallel in innodb in one slave and tokudb in another.

I'm seeing promising results in expected compression ratios but am noticing something very strange about the data_free reported from information_schema.tables for the tokudb copy.

This is an append only table.  No updates or deletes.  I'll let the following table explain what I'm seeing

engine  data_length(GB) index_length(GB) data_free(MB)  Size on disk (GB)
InnoDB  728.40          195.63           61.00          939
TokuDB  453.42          119.07           27507.60       253

Can someone explain why the data_free is magnitudes larger than the innodb version.   Is data_free for tokudb expected to mean the same thing as it does for innodb?


texts: What's the difference between functions and stored procedures?

texts:
I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are.

Is that true?  If not, what are the differences between them?

In addition, I know that stored procedures are only complied once and then executed directly. What about functions?


texts: Can we use innodb to store latitutude and logitude and retrieve 20 closest points well?

texts:
I've heard that innodb Why innodb doesn't store geospatial data? store geospatial data but cannot index it.

Not really sure what it means. Is it a good idea to ask 20 closest businesses if point is stored in innodb?


texts: Can I Configure Enterprise Manager Cloud Control on Standard Edition

texts:
Is it possible to configure enterprise manager cloud control on Oracle 12c Standard edition?
Will it administer multiple databases (Non-CDB)?
What about administering databases on different servers?


texts: What is the recommended way to join junction tables for efficient ordering/pagination?

texts:
Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem.

Database: PostgreSQL 9.6

Simplified schema:

CREATE TABLE article (
  id bigint PRIMARY KEY,
  title text NOT NULL,
  score int NOT NULL
);
CREATE TABLE tag (
  id bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE article_tag (
  article_id bigint NOT NULL REFERENCES article (id),
  tag_id bigint NOT NULL REFERENCES tag (id),
  PRIMARY KEY (article_id, tag_id)
);
CREATE INDEX ON article (score);

Production data info:

All tables are read/write. Low write volume, only a new record every couple minutes or so.

Approximate record counts:

~66K articles
~63K tags
~147K article_tags

Average of 5 tags per article.

Question: I want to create a view article_tags which includes an array of tags for every article record, can be ordered by article.score and paginated with or without additional filtering.

In my first attempt I was surprised to see that the query took ~350 ms to execute and wasn't using the indexes. In subsequent attempts I was able to get it down to ~5 ms but I don't understand what is going on. I would expect all these queries to take the same amount of time. What crucial concept am I missing here?

Attempts (SQL Fiddles):

multi-table joins (~350 ms), (~5 ms if ordered by article.id!) -- seemed like the most natural solution
subquery join (~300 ms) -- also seemed like a natural solution
limited subquery join (~5 ms) -- super awkward, can't be used for view
lateral join (~5 ms) -- is this really what I should be using? seems like a misuse of lateral
...something else?


texts: MySQL difference between wait_timeout and connect_timeout

texts: I have a my.cnf file. In it I see wait_timeout as well as connect_timeout. What's the difference between these 2?


texts: Can I estimate the row count of a large mysql table using the disk space?

texts:
Sysadmin here (not a developer by trade), so go easy please ;)

I have a mysql table, on a linux server that uses innodb_file_per_table.

When I try to do select count(foo) from table; It doesn't finish.  I gave it a good 5 minutes before interrupting.

The table.ibd file is about 43G (45176848384).  Since the count() seemed like it was never going to finish, I decided to try the following to get an estimate of the row count:

I dumped the first 10,000 rows of the table and imported it to a local database that also has innodb_file_per_table and I hope all else equal.

That file, after I imported it, (with only 10,000 rows) is about 2M (2097152)

So:

45176848384/2097152
21542
21542*10000
215420000

Then, is it correct to assume that the ~43G table is around 215 million rows?  Are there other considerations I'm overlooking?

Or is there a better way for me to determine how many rows are in this table?


texts:
I've read some posts related to issues similar to this, but without success, so I'm trying to post my specific problem.

I have to load a big CSV file (25Gb) into a MySQL table. I'm doing this with this environment:

MacOS Mojave 10.14.3.
16 GB DDR3 RAM Memory.
75 GB free space in my hard disk.
MySQL 5.7 for MacOS 10.14 with default configuration.

At first I tried with this:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

But my computer run out of memory and free hard disk space and the process crashed after more than 30 hours!!

I tried this with the same results:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

Finally, after some investigation I tried with this MySQL configuration file:

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
secure_file_priv="/usr/local/mysql/data/"
innodb_buffer_pool_size = 6G
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity_max = 2000
innodb_io_capacity = 1000
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

And this code (using MyISAM engine):

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50)) ENGINE = MYISAM;
CREATE INDEX index_3 ON mytable (field3);
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;

The process has a much better performance, it doesn't crash but it takes 4 hours to complete.

The issue is that I've asked one of my coworkers to try this with the exact same process, the same environment and the same configuration (the only difference is that he has 150GB of free hard disk space) and the process takes only 1 hour!

So, my doubt is:

What could be the difference between his Mac and mine to have such a different performance? What could I check?
Is there anything I could change in MySQL configuration to improve the performance of this process?
Is it normal that the process requires all of my free hard disk space? During the process I monitor the free space and it keeps decreasing from 75GB to only 500MB (aprox), when I start getting memory warnings.

EDIT to add answers to Rick's questions:

Both of us have SSDs.
I haven't set key_buffer_size, so I guess it has its default value (8388608 according to the documentation of MySQL 5.7).
That TEXT column has all of its values between 5 and 12 characters
Things go sour during create index: with load+create index the load command takes 10 minutes approximately and the create index takes hours; with create index+load, the create index is automatic and load takes hours (normal).


texts: Create integer id columns from existing string columns (integer coding?)

texts:
I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this:

| ID_STRING_A | ID_STRING_B |
|   'AAAA'   |    'BBBB'    |
|   'BBBB'   |    'CCCC'    |
|   'AAAA'   |    'DDDD'    |

Now I want to compute an integer representation for the both-column-elements and store them into additional columns. The result should look like this:

| ID_STRING_A | ID_STRING_B | ID_INT_A | ID_INT_B |
|   'AAAA'    |   'BBBB'    |     1    |     2    |
|   'BBBB'    |   'CCCC'    |     2    |     3    |
|   'AAAA'    |   'DDDD'    |     1    |     4    |

My frist approach based on the answers is:

Unfortunately, my update part seems to be highly iniefficient although there are indices on ID_STRING_A/B. While the query itself is done in minutes, the update part seems not to end. Here's the code:

ALTER TABLE mytable ADD COLUMN ID_INT_B integer;
ALTER TABLE mytable ADD COLUMN ID_INT_A integer;

UPDATE mytable SET ID_INT_A = g.ID_INT_A , ID_INT_B = g.ID_INT_B FROM
(
    WITH T( n , s ) AS
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY s ) , s
        FROM
        (
            SELECT ID_STRING_A FROM mytable
            UNION
            SELECT ID_STRING_B FROM mytable
        ) AS X( s )
    )
    SELECT m.ctid AS id_ , m.ID_STRING_A AS ID_STRING_A , m.ID_STRING_B AS ID_STRING_B , T1.n AS ID_INT_A , T2.n AS ID_INT_B FROM mytable AS m
    JOIN T AS T1 ON m.ID_STRING_A = T1.s
    JOIN T AS T2 ON m.ID_STRING_B = T2.s
) AS g
WHERE mytable.ctid = g.id_


texts: SQL Server Linked Server with Microsoft OLE DB Provider for Visual FoxPro?

texts:
We were having some issues with our old Microsoft Visual Fox Pro ODBC driver that we shoehorned into Windows 7 and is no longer supported by Microsoft and has not been for quite a long time...

So when our anti-virus software finally stopped it from working, we needed to find another way to make it work.  The Anti-virus company couldn't help us; they said it was a Microsoft problem, but they did recommend a couple of related KBs from Microsoft, which stated that we should use the Microsoft OLE DB Provider for Visual Fox Pro.

After making sure that the Microsoft OLE DB Provider for Visual Fox Pro was installed, I created a linked server in Microsoft SQL Server Management Studio:

Right clicked the following element in the left hand pane, <SQL Server Instance> -> Server Objects -> Linked Servers and clicking New Linked Server ... from the context menu.
Then in the resulting New Linked Server dialog:

On the General page:

Entered a name in the Linked server: textbox
In the Server type: radio button, selected Other data source
In the Provider: drop down selected Microsoft OLE DB Provider for Visual Fox Pro
In the Product name: textbox entered VFPOLEDB
Entered the path to the data in the Data source: textbox.
Clicked OK

I was then able to query the data in the Linked Server, and in order to make this accessible to MS Access on the workstations over an ODBC connection (as well as make it more secure) I created VIEWs with only the necessary fields for each of the tables in question using the following syntax:

USE <name-of-database-to-store-views-in>
GO

CREATE VIEW [dbo].[name-of-view-likely-the-same-name-as-referred-to-in-access] AS
SELECT *
  FROM [name-of-linked-server]...[name-of-dbf-file-without-extension]
GO

Then on the workstations with the MS-Access Database that used to use the VFP ODBC driver, I created a new ODBC connection to the SQL Server containing the Linked Server, so I could access the VIEWs I just created.

Then in the MS-Access database I added the linked tables (VIEWs really) from the ODBC connection, they were imported being named as dbo_<view-name> and very similar to <old-linked-table-name>.  So I renamed the <old-linked-table-name> with a old_ prefix, and renamed the new ones to <view-name> removing the dbo_ prefix.

It now appears that the Microsoft Access database is able to access those tables correctly, via Linked Table, via ODBC, via SQL Server, via VIEW, via Linked-Server, via Microsoft OLE DB Provider for Visual Fox Pro without the anti-virus getting in the way; however, I'm using test data to do this at the moment and NOT production; production has several other people accessing it, modifying it, etc...so provided that it turns out to be the case that the MS-Access database is just pulling data, and not writing it out, do you think this will work alright in production?  And what if it is writing the data out?  Will I be okay then too?


texts:
I have a table with many entries each describing the commerce state of an item.

The table has the following structure:

CREATE TABLE CommerceState
(
    Id INT NOT NULL PRIMARY KEY
    , ItemId INT NOT NULL
    , Listings INT NOT NULL
    , UnitPrice BIGINT NOT NULL
    , Quantity INT NOT NULL
    , [Timestamp] DATETIME NOT NULL
);

At certain intervals I'm saving the current state of an item into this table. It can happen that two consecutive updates contain the same data and just differ in the timestamp.

What I want to do now is to remove those kind of duplicates. I couldn't come up with a script so far. I just got solutions to completely delete all duplicates without respect to the actual timeline.

To visualize what I would like to achieve:

Before:

+ Id + ItemId + Listings + UnitPrice + Quantity + Date                    +
| 1  | 1234   | 10       | 100       | 50       | 2015-10-22 15:55:00.000 |
| 2  | 1235   | 10       | 25        | 150      | 2015-10-22 16:00:00.000 |
| 3  | 1234   | 9        | 100       | 50       | 2015-10-22 16:05:00.000 |
| 4  | 1235   | 5        | 25        | 30       | 2015-10-22 16:10:00.000 |
| 5  | 1235   | 10       | 25        | 150      | 2015-10-22 16:15:00.000 |
| 6  | 1235   | 10       | 25        | 150      | 2015-10-22 16:20:00.000 |
| 7  | 1234   | 10       | 100       | 50       | 2015-10-22 16:25:00.000 |
| 8  | 1234   | 10       | 100       | 50       | 2015-10-22 16:30:00.000 |
| 9  | 1234   | 10       | 100       | 50       | 2015-10-22 16:35:00.000 |
| 10 | 1234   | 9        | 100       | 50       | 2015-10-22 16:40:00.000 |

After:

+ Id + ItemId + Listings + UnitPrice + Quantity + Date                    +
| 1  | 1234   | 10       | 100       | 50       | 2015-10-22 15:55:00.000 |
| 2  | 1235   | 10       | 25        | 150      | 2015-10-22 16:00:00.000 |
| 3  | 1234   | 9        | 100       | 50       | 2015-10-22 16:05:00.000 |
| 4  | 1235   | 5        | 25        | 30       | 2015-10-22 16:10:00.000 |
| 5  | 1235   | 10       | 25        | 150      | 2015-10-22 16:15:00.000 |
| 7  | 1234   | 10       | 100       | 50       | 2015-10-22 16:25:00.000 |
| 10 | 1234   | 9        | 100       | 50       | 2015-10-22 16:40:00.000 |

Any ideas/suggestions on how to write a T-SQL script that does exactly this?


texts: MySQL: How to drop all tables starting with a prefix?

texts:
In my database there is a lot of tables starting with _elgg , now I want to drop all tables with this prefix. Can anyone give me a solution ?

Thanks in advance!


texts:
I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this...

{
  "a": {
    "id": 1,
    "b":{
      "c1": {
        "d1": 1,
        "d2": 2
      },
      "c2": {
        "d1": 1,
        "d2": 2
      },
      "c3": {
        "d1": 3,
        "d2": 4
      }
    }
  }
}

...which I've tentatively assigned to a schema resembling the below ERD

+---+    +-----+    +---+    +---+
| b |<--<| b_d |>-->| d |>-->| c |
+---+    +-----+    +---+    +---+

full db_fiddle

I'd like to be able to recompose the normalised data into the original object but I've been unable to do so. Instead of getting "c" objects as direct key-value pairs, I'm getting whole objects. For example...

select
    b_d.b_id,
    json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns...

[{
    "c1 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c2 ": {
        "d1": 1,
        "d2": 2
    }
},
{
    "c3 ": {
        "d1": 3,
        "d2": 4
    }
}]

...which you will note has an additional nesting level versus the source object. I suspect I've gone wrong out of the gate with json_build_object but so far have been unable to successfully reconstruct the "b" object as it appears in the original json document.

What do I modify about the query and/or the underlying schema to normalize and denormalize this object?


texts: Can you upgrade a single SQL Server 2008 Failover Cluster Instance to SQL Server 2012?

texts:
Or do you have to upgrade every FCI on the Failover Cluster? The documentation doesn't seem to be clear.

We have a 7 instances on a 3 node cluster. We want to upgrade only one of the instances from SQL Server 2008 to SQL Server 2012. Can we perform an upgrade or do we need to backup the instance as best we can, uninstall that instance, install SQL Server 2012 then add that instance back?

I know you can run SQL Server 2008 and SQL Server 2012 on the same failover cluster...

The installation makes it seem like you can upgrade a single instance, but I'm still apprehensive since we haven't tested and no documentation seems to explicitly state it's possible.


texts: MySQL - ERROR 1045 (28000): Access denied for user

texts:
I just installed a fresh copy of Ubuntu 10.04.2 LTS on a new machine.  I logged into MySQL as root:

david@server1:~$ mysql -u root -p123

I created a new user called repl.  I left host blank, so the new user can may have access from any location.

mysql> CREATE USER 'repl' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

I checked the user table to verify the new user repl was properly created.

mysql> select host, user, password from mysql.user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| server1   | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| localhost |                  |                                           |
| server1   |                  |                                           |
| localhost | debian-sys-maint | *27F00A6BAAE5070BCEF92DF91805028725C30188 |
| %         | repl             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-----------+------------------+-------------------------------------------+
8 rows in set (0.00 sec)

I then exit, try to login as user repl, but access is denied.

david@server1:~$ mysql -u repl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$ mysql -urepl -p123
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)
david@server1:~$

Why is access denied?


texts: CONCAT_WS causes "Data too long" which works partially?

texts:
I've got a simple table with several columns and I'm trying to merge two of them into one. I'm working with MySQL 5.5 and I can reproduce the error one a specific data set by running the following (anonymized) query through MySQL Workbench:

UPDATE person SET a = CONCAT_WS('\n', a, b)

It clearly produces the following response message:

Error Code: 1406. Data too long for column 'a' at row 34

Because both a and b are VARCHAR(200) the response is fair, but I would expect and hope that since it produced an error, no changes would be commited. However, apparently it does. I'm not able to reproduce it on a smaller dummy dataset. I am using auto commit.

Am I simply wrong in my assumption that erronous queries don't change any values? Could it be some feature of either auto commit, or other setup of my MySQL server?

Please ask if any obvious detail is missing from my specification.


texts: Restore SQL Server 2008 database onto SQL Server 2014?

texts:
I have a backup file that I need to restore daily on schedule based.

But the  backup file is from SQL Server 2008 and restore destination is SQL Server 2014. And also the backup file is from cross network. I have copied to local shared folder and trying to restore it.

The code I used:

USE [master]
RESTORE DATABASE MyDB FROM
DISK = N'\\Mylocation\DBRestores\MyBackupFile'
WITH
    FILE = 1, -- 1 = .bak, 2 = .trn type backup
    MOVE N'MyDB_data' TO N'S:\MSSQLsql\mssql\DATA\MyDB.mdf',
    MOVE N'MyDB_log' TO N'L:\MSSQLsql\mssql\DATA\MyDB_log.ldf',
    NOUNLOAD,
    STATS = 5
go

but I get an error

  Msg 3241, Level 16, State 0, Line 3
  The media family on device '\Mylocation\DBRestores\MyBackupFile' is incorrectly formed.
  SQL Server cannot process this media family.

  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

Any help? thanks.


texts: Order of tables affects the use of index

texts:
I'm trying to optimize a large set of queries, and I don't understand why the order of tables in the WHERE clause makes such a big difference (3x).
I thought that the query planner didn't care about the order of tables? Is there a way to avoid having to manually determine the best order for each query?
-- Original query, 680ms
EXPLAIN ANALYZE
SELECT neighbor.cluster_id,  ST_Distance(node.geog, neighbor.geog,False)::int as dist
   FROM nodes_africa neighbor,  nodes_africa node
   WHERE node.node_id=2039682 AND ST_DWithin(node.geog,neighbor.geog,3000.0,False);

-- Identical query, but the order of tables is flipped, 203ms
EXPLAIN ANALYZE
SELECT neighbor.cluster_id,  ST_Distance(node.geog, neighbor.geog,False)::int as dist
   FROM nodes_africa node,  nodes_africa neighbor
   WHERE node.node_id=2039682 AND ST_DWithin(node.geog,neighbor.geog,3000.0,False);

In the first case, the index on node_id is ignored in favor of a parallel sequential scan. In the second case, the index on node_id is used.
This isn't an issue of the results being cached—the same happens if I run the second query first.
However, the issue does go away if I remove the ST_Distance() call, and both execution times are the same.
Any suggestions for what is going on? Is this a PostGIS issue given the point about ST_Distance?
I'm running PostGIS 3.1 and PostgreSQL 13.1.
For reference, here's the results from EXPLAIN ANALYZE:
Gather  (cost=1000.54..621201.44 rows=56796 width=12) (actual time=630.099..674.508 rows=947 loops=1)
   Workers Planned: 6
   Workers Launched: 6
   ->  Nested Loop  (cost=0.54..614521.84 rows=9466 width=12) (actual time=546.731..571.040 rows=135 loops=7)
         ->  Parallel Seq Scan on nodes_africa node  (cost=0.00..328448.88 rows=1 width=32) (actual time=545.259..568.158 rows=0 loops=7)
               Filter: (node_id = 2039682)
               Rows Removed by Filter: 2428038
         ->  Index Scan using nodes_africa_geog_idx on nodes_africa neighbor  (cost=0.54..49382.29 rows=1700 width=40) (actual time=9.902..18.914 rows=947 loops=1)
               Index Cond: (geog && _st_expand(node.geog, '3000'::double precision))
               Filter: st_dwithin(node.geog, geog, '3000'::double precision, false)
               Rows Removed by Filter: 256
 Planning Time: 4.764 ms
 JIT:
   Functions: 70
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 18.188 ms, Inlining 492.795 ms, Optimization 774.073 ms, Emission 393.804 ms, Total 1678.859 ms
 Execution Time: 680.148 ms
(17 rows)

 Nested Loop  (cost=0.98..1469449.74 rows=56796 width=12) (actual time=190.809..199.453 rows=947 loops=1)
   ->  Index Scan using nodes_africa_pkey on nodes_africa node  (cost=0.44..8.46 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=1)
         Index Cond: (node_id = 2039682)
   ->  Index Scan using nodes_africa_geog_idx on nodes_africa neighbor  (cost=0.54..49382.29 rows=1700 width=40) (actual time=0.634..8.531 rows=947 loops=1)
         Index Cond: (geog && _st_expand(node.geog, '3000'::double precision))
         Filter: st_dwithin(node.geog, geog, '3000'::double precision, false)
         Rows Removed by Filter: 256
 Planning Time: 4.760 ms
 JIT:
   Functions: 10
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 3.315 ms, Inlining 18.839 ms, Optimization 121.072 ms, Emission 49.780 ms, Total 193.006 ms
 Execution Time: 202.901 ms
(13 rows)


texts: Multi-row insert vs transactional single row inserts

texts:
On Postgres (>10) is there any performance gain on using multi-row insert:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4);

over:

begin;
insert into tbl (c1, c2) values (v1, v2);
insert into tbl (c1, c2) values (v3, v4);
commit;

I've shamelessly copied the code snippet from the below question, but this question is different as it is about transactions Multi-row insert vs multiple single row inserts


texts:
say that i do inner join in query... the result will give columns from both table.
Is there a way to specify that i want only columns from one of the tables without specifying all columns names manualy?


texts: “using stale statistics instead of current ones because stats collector is not responding”

texts:
My postgresql 9.5 logs shows every minute the message:

  using stale statistics instead of current ones because stats collector is not responding

Except a post on serverfault which is not helpfull and seems related to standby databases setup (which is not my case), I don't find anything to solve this.

What's the meaning of this? How can I solve that?

Additional information based on Greda's reply:

I have 4 CPU, and in routine less than 5% of 1 CPU is used
I have 32Gb RAM, and in routine ~ 500Mb are used
This is not a VM
This runs Ubuntu 14.04.3 LTS/Linux 3.13.0 SMB x86/64

I have also tried to tweak the postgresql.conf parameters as follows

shared_buffers: 1024MB
work_mem: 10MB
maintenance_work_mem: 1024MB
effective_cache_size: 4GB

The error message is still there.

P.S: I fully understand that's not a severe error, but it floods my logs, and I have the fear to not see important messages...

Some additional observation:

VACUUM FULL VERBOSE ANALYZE doesn't helps
I have a never ending proccess connected to the database. When this is running, the VACUUM FULL VERBOSE ANALYZE seems blocked.

Can that never ending process be the root causes of my problem? In that case, since I need to have that proccess running, do I have to tweak something on the server?

Edit:

I had a never ending process connected to the db. I've tried to stop it, and it doesn't helps.

My postgresql.conf file is available there


texts:
Using postgresql 10

I have a table gpspoints which among other things contains

id    dist
 1    NULL
 2     2.3
 3     4.4
 4     1.7
 5     1.2
 6     2.7

and so on

Dist is distance in meter since the previous point
In addition, I have a table trip that gives the first and last id from the gpstable for a trip. Each point may belong to several overlapping trip.

The trip table is then like:

 id start  end
  1   1     4
  2   2     3
  3   3     5

When I do my query I know that start < end and there will never be more than one defined trip for each gpspoint

I would like to add up the length of the trip for each point that is defined as a start point, i.e.

start  length
 1       9.4   # (2.3 + 4.4 + 1.7)
 2       6.1   # (4.4 + 1.7)
 3       7.3   # (4.4 + 1.7 + 1.2)

Writing this, I realize that a possible solution would be to make a new table containing the trip id and the gpspointid for each trip. But as the gpspoint table has a few millions rows and there may be up to a few hundred trips that each point is a member of (Each of the trips may contain a few hundred points) and the trips are clearly defined by their start and end point, my question is still

Is there a way to sum a number between two given id with varying distance between the ids? (if there always had been the same number of points in a trip, I could have made the sums using windowing functions)


texts: How to limit rows in PostgreSQL update statement

texts:
My table employees contain more than ten million data. i want to update 10k rows in commission_pct column where commission_pct is null and set zero(0) value.

in oracle I can easily solve this by using rownum.

update employees
set commission_pct=0
where commission_pct is null and rownum<=10000;

but postgresql does not support rownum.

how to solve this in postgresql?


texts: Postgres low cardinality column index for paginated resultset

texts:
I have a postgres DB with large table having over 200 million entries. There are 3 columns in this table with very low cardinality (max. 30). I need to use these columns as predicate independently in a paginated resultset. The application will filter the resultset based on one of these columns.
CREATE TABLE metadata (
id bigserial PRIMARY KEY,
created_ts timestamptz NOT NULL DEFAULT now(),
updated_ts timestamptz NOT NULL DEFAULT now(),
status int4 NOT NULL,
product_type int4 NOT NULL,
application_type int4 NOT NULL,
app_source varchar(35) NOT NULL
);

select * from metadata where product_type=10 order by id desc limit 10 offset 100;

select * from metadata where application_type=10 order by id desc limit 10 offset 100;

select * from metadata where app_source='app_form_1' order by id desc limit 10 offset 100;

In the above table the fields product_type, application_type and app_source are all low cardinality columns. Also the above query can be a combination of all the fields with AND across all the predicates. Due to low cardinality the B-Tree index on these columns are not working and I can't create a bitmap index in Postgres.
This kind of a query I'm assuming is common among applications that see a decent traffic. What are the suggested ways (or best practises) of designing efficient queries for this use case.


texts: Normalization: Should I do this in 2NF or 3NF?

texts:
Consider a database storing the data of invoices, and each invoice takes the following form:

Invoice_Num: 1480
Handled By: John Keaton
Staff Num: 263
Staff Position: Trainee

Item_Num    Item_Desc         Unit_Price   Qty
========    ==============    ==========   ===
 15003      Blue Ballpen         2.00       5
 15012      5ml Gluestick        0.80       3

Assumptions:

Each invoice is handled by 1 and only 1 staff.
Each item in the same invoice is unique.

So, in 1NF, the data can be represented as:

Invoice [ Invoice_Num, Staff_Num, Staff_Name, Staff_Pos, Item_Num, Item_Desc, Unit_Price, Qty ]

where Invoice_Num and Item_Num form the primary key.

Now we know that Invoice_Num -> Staff_Num, and Staff_Num -> Staff_Pos, and Staff_Num -> Staff_Name.

But then when the database is normalized to 2NF, should we move the Staff_Pos and Staff_Name to another table? If we use the transitive property, we have Invoice_Num -> Staff_Pos, and Invoice_Num -> Staff_Name, then we should move them to another table to eliminate partial dependency. However, with the use of transitive property, should these 2 dependencies be considered transitive dependency instead, and hence moving Staff_Pos and Staff_Name to another table should be done in 3NF?


texts:
I have three tables:

FILM(ID,TITLE)
ACTOR(ID,NAME,GENDER)
CAST(ACTOR_ID,FILM_ID,ROLE)

Foreign keys: ACTOR_ID, FILM_ID

And I have created a btree on columns (ACTOR_ID,FILM_ID) of CAST table.

Could I use the INLJ (index nested loop join) algorithm to make the following join?

...
FROM FILM,CAST
WHERE FILM.ID = CAST.FILM_ID ;


texts: Vertical data set to horizontal data set for many columns

texts:
I have read about pivot function but I am not sure if If it helps me in this situation. Essentially I have to produce my vertical data set horizontally. For instance the first table shows the vertical data set and ideally i would like to convert that into how the second table looks.

Any help would be appreciated!


texts: Inability to query underlying table of a schema-bound view

texts:
I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE STATISTICS XXX.
At statistics update, I get following error:
Msg 402, Level 16, State 1, Procedure ZZZZ, Line 5 [Batch Start Line 0]
The data types datetime and time are incompatible in the add operator.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ZZZZ' because of binding errors.

I know that the message is quite explicit (the view, which was syntactically correct on 2008R2 is not anymore on 2019). I don't understand why a view defined WITH SCHEMABINDING that is not valid would prevent the update of statistics on an underlying table.
Moreover, I get the same error message when querying the underlying table with a WHERE clause, except if I force a FULLSCAN with following hint:
OPTION(TABLE HINT( $mytable, FORCESCAN ))

I know SQL Server will fire an error in case of DDL change to underlying schema-bound objects, but I don't understand why, if the view is invalid, the underlying objects cannot be used as usual.
I'm sure there is an explanation (like the protection mechanisms for schema-bound views cannot be processed due to the invalidity of the view), but I can't find it for sure in the documentation.
I ran a DBCC CHECKDB without problem. I changed the compat level to 150.


texts:
I'm trying to migrate a filesystem to ASM but when I start with RMAN backup, I face the following error, which is weird because I haven't performed any backups before on the same database.

RMAN> BACKUP AS COPY DATABASE FORMAT 'DATA';

Starting backup at 28-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\ADMINISTRATOR\ORADATA\ECC\SYSTEM01.DBF
output file name=C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA tag=TAG20150728T143422 RECID=2 STAMP=886257290
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=C:\APP\ADMINISTRATOR\ORADATA\ECC\SYSAUX01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/28/2015 14:34:59
ORA-19504: failed to create file "C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Please advise


texts:
We run a Cassandra cluster with 2 DC, 3 nodes each (2 RF). The state of the cluster is quite bad (never repaired) and the disks were getting full so we added additional nodes which after the bootstrap procedure joined successfully the cluster.
According to the Cassandra documentation is supposed to run cleanup after adding a new node, but in our case:

Since we never run repair (and now it too late to run since disk usage in old nodes is > 90 %) Is it safe for the data to run:

nodetool cleanup

in each node in order to free space?

Cheers,

Jbrl


texts: MariaDB replication - enabling compression

texts: We've got a MariaDB database that's already being replicated and we wanted to turn on compression.  I found the slave_compressed_protocol variable and see that it has to be turned on both on the master and the replica (it's currently off on both).  My question is whether there is a specific sequence to this?  Slave first then master?  Also, I saw online that I need to stop and then start the slave after making the change, but do I need to do something similar on the master for it to take effect?  I want to do it safely so I don't mess up the contents of the replica DB.  Thanks.


texts: MySQL auto increment column increases after insertion error occurs

texts:
I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row inserted in this statement.

Thereby, this behavior causes the next valid insertion skip an available and possible value for the auto_increment.

Example:

1st Insert | Result: Success | id(auto_increment_column): 1       |
2nd Insert | Result: Failure | -------- No rows were recorded---- |
3nd Insert | Result: Success | id(auto_increment_column): 3       |

I want to know if someone had this problem before and if this is really a problem.

Cheers.

P.S.:

Errno: 1452 - fk constraint fails. This means that I'm trying to reference some column value in another table and this value doesn't exists there.

Database: MySQL Ver 14.14 Distrib 5.6.13, for Windows 64-bit
Storage Engine: InnoDB


texts: Access 2010 ODBC Connection to SQL Server 2005

texts:
I'm trying to code an ODBC connection to SQL Server in Access 2010 and hoping someone can help me, as I've lost the plot.

This is my code, it simply accesses one SQL Server database/table and insert into another when a button is clicked:

sql-casecodes is an ODBC table link

DoCmd.RunSQL "insert into [sql-casecodes] (CaseCode,CaseName) values select casecode, name FROM [ODBC;Driver=SQL Server;Server=SQL1;Database=Clients;Trusted_Connection=Yes].clients_case"

When the code is run, Access gives me a run-time error of

  '3134' syntax error in INSERT INTO statement.

But the code looks ok?

I don't want to be using system ODBC links as I want to learn how to code it in


texts:
Considering the following two (equivalent) SQL statements.
Why do their execution plan differ (especially in performance) that much?
I'm using Postgres 11.9 in this example.
select p.name
from person p
where exists (
    select 1
    from person_task pt
    join task t on pt.task_id = t.id  and t.state <> 'closed'
        and pt.person_id = p.id -- Using ON
)
limit 10

-- EXPLAIN (ANALYZE):

Limit  (cost=0.00..270.98 rows=10 width=8) (actual time=10.412..60.876 rows=10 loops=1)
  ->  Seq Scan on person p  (cost=0.00..28947484.16 rows=1068266 width=8) (actual time=10.411..60.869 rows=10 loops=1)
        Filter: (SubPlan 1)
        Rows Removed by Filter: 68
        SubPlan 1
          ->  Nested Loop  (cost=1.00..20257.91 rows=1632 width=0) (actual time=0.778..0.778 rows=0 loops=78)
                ->  Index Scan using person_taskx1 on person_task pt  (cost=0.56..6551.27 rows=1632 width=8) (actual time=0.633..0.633 rows=0 loops=78)
                      Index Cond: (id = p.id)
                ->  Index Scan using taskxpk on task t  (cost=0.44..8.40 rows=1 width=8) (actual time=1.121..1.121 rows=1 loops=10)
                      Index Cond: (id = pt.task_id)
                      Filter: (state <> 'closed')
Planning Time: 0.466 ms
Execution Time: 60.920 ms

select p.name
from person p
where exists (
    select 1
    from person_task pt
    join task t on pt.task_id = t.id and t.state <> 'closed'
    where pt.person_id = p.id -- Using WHERE
)
limit 10

-- EXPLAIN (ANALYZE):

Limit  (cost=2818814.57..2841563.37 rows=10 width=8) (actual time=29.075..6884.259 rows=10 loops=1)
  ->  Merge Semi Join  (cost=2818814.57..59308642.64 rows=24832 width=8) (actual time=29.075..6884.251 rows=10 loops=1)
        Merge Cond: (p.id = pt.person_id)
        ->  Index Scan using personxpk on person p  (cost=0.43..1440340.27 rows=2136533 width=16) (actual time=0.003..0.168 rows=18 loops=1)
        ->  Gather Merge  (cost=1001.03..57357094.42 rows=40517669 width=8) (actual time=9.441..6881.180 rows=23747 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Nested Loop  (cost=1.00..52679350.05 rows=16882362 width=8) (actual time=1.862..4207.577 rows=7938 loops=3)
                    ->  Parallel Index Scan using person_taskx1 on person_task pt  (cost=0.56..25848782.35 rows=16882362 width=16) (actual time=1.344..1807.664 rows=7938 loops=3)
                    ->  Index Scan using taskxpk on task t  (cost=0.44..1.59 rows=1 width=8) (actual time=0.301..0.301 rows=1 loops=23814)
                          Index Cond: (id = pt.task_id)
                          Filter: (state <> 'closed')
Planning Time: 0.430 ms
Execution Time: 6884.349 ms


texts: Oracle to SQL Server Conversion help: sys_connect_by_path, listagg, regexp

texts:
I've been tasked with converting a process from Oracle to Sql Server 2012.

I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server logic.

sys_connect_by_path example:

SELECT sys_connect_by_path(field1, '/') || '/' || field2 || '/' path
  FROM data
 WHERE connect_by_isleaf = 1 connect BY prior field2 = field1 start
 WITH field1 NOT IN (SELECT field2 FROM data)

I know that I need to use a CTE for this example (possibly recursive) but I can't seem to get my logic correct

regexp example:

select field1, field2, regexp_substr(path, '[^/]+', 1, column_value) elem
  from grouped,
table(cast(multiset(select level from dual
connect by level <= regexp_count(path,'/')) as sys.odciNumberList))
where regexp_substr(path, '[^/]+', 1, column_value) is not null

listagg example:

select field1, field2,
       dense_rank() over (order by listagg(elem,',') within group (order by elem)) grp
  from split
group by field1, field2

From what I understand, listagg in SQL is done by the STUFF function. That seems simple enough to understand but in this case I'm not sure how I make it work

Could I get some pointers?


texts: Insert from another table, then update using Identity

texts:
I am changing the database schema and migrating data that was originally stored in one table to two tables.

Old schema:

CREATE TABLE [dbo].[X](
    [Id] TINYINT NOT NULL PRIMARY KEY,
    [SomeData] VARBINARY(MAX) NULL,
)

New schema:

CREATE TABLE [dbo].[X](
  [Id] TINYINT NOT NULL PRIMARY KEY,
  [SomeDataId] BIGINT,
)
CREATE TABLE [dbo].[SomeData](
  [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY,
  [Binary] VARBINARY(MAX) NOT NULL
)

I know the steps are:

Create the new column [SomeDataId] on [X]
Copy data from [X].[SomeData] to [SomeData].[Binary]
Remove column [SomeData] on [X]

I am having issue on how to complete step 2 in T-SQL only. I know I can:

Use SCOPE_IDENTITY() to get the inserted identity seed
Use OUTPUT INSERTED.Id to get the inserted identity seed
Use INSERT INTO ... SELECT ... FROM to copy data from one table to another

However I cannot figure out how to update the relevant row on [X] with the identity seed just inserted, without using some kind of for-loop logic which sounds silly.


texts:
When I query the configuration of the default trace, it shows enabled:

exec sp_configure 'default trace enabled';
-->
name                    minimum  maximum  config_value  run_value
default trace enabled         0        1             1          1

But when I query sys.traces for the path, it returns an empty rowset:

select * from sys.traces;

What could explain the absence of the enabled trace?


texts: What are some key configuration settings for using an InnoDb table for session storage?

texts:
I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper locking. I have a server already dedicated to the database with comfortable amount of free ram so I'd like to consider using an additional instance of MySQL purely for session storage on the same machine. Currently my sessions folder on the file system is ~131Mb and has ~23k files. I'm planning on using InnoDb with SELECT ... FOR UPDATE to implement the session locking.

Is running two instances of MySQL optimized for different purposes on the same server a bad idea? (If so, why?)

For the sessions-only instance what are some optimization tips? I want it to be persistent, but don't mind losing a little bit of data in the event of a disaster whereas with the main app db I don't want to risk any data loss.

If I can spare say 256Mb of ram for this MySQL instance (main instance has ~6Gb), how should I allocate it?

Disable query cache or no?

What settings can I use to reduce disk writes (since I don't care about durability so much with this instance)? E.g. innodb_locks_unsafe_for_binlog, innodb_flush_method, etc..


texts:
I am working on backup+recovery scenario and I came across a situation where I could not figure out how I would have to deal with it.
We have couple databases where sometimes there may not be any activity for 20 to 30 minutes. We have Transaction Log Backup setup every 15 minutes.
My questions are:

Would there by any TR Log Backups for that time period?
What would happen if I happen to restore those transaction log ?

I'm sorry, I tried looking for answer on different places and books, but could not find it. If someone can please help me out.
Thank you in advance.


texts: Calculate percent changes in contiguous ranges in PostgreSql

texts:
I need to calculate price percent change in contiguous ranges. For example if price start moving up or down and I have sequence of decreasing or increasing values I need to grab first and last value of that sequence and calculate the change.

I'm using window lag function to calculate direction, my problem- I can't generate unique RANK for the sequences to calculate percent changes.
I tired combination of RANK, ROW_NUMBER, etc. with no luck.

Here's my query

WITH partitioned AS (
  SELECT
    *,
    lag(price, 1) over(ORDER BY time) AS lag_price

  FROM prices
),
sequenced AS (
  SELECT
    *,
    CASE
      WHEN price > lag_price THEN 'up'
      WHEN price < lag_price THEN 'down'
      ELSE 'equal'
    END
     AS direction
  FROM partitioned

),
ranked AS (
  SELECT
    *,
    -- Here's is the problem
    -- I need to calculate unique rnk value for specific sequence
    DENSE_RANK() OVER ( PARTITION BY direction ORDER BY time) + ROW_NUMBER() OVER ( ORDER BY time DESC) AS rnk
    -- DENSE_RANK() OVER ( PARTITION BY seq ORDER BY time),
    -- ROW_NUMBER() OVER ( ORDER BY seq, time DESC),
    -- ROW_NUMBER() OVER ( ORDER BY seq),
    -- RANK() OVER ( ORDER BY seq)

  FROM sequenced
),
changed AS (
  SELECT *,
    FIRST_VALUE(price) OVER(PARTITION BY rnk ) first_price,
    LAST_VALUE(price) OVER(PARTITION BY rnk ) last_price,
    (LAST_VALUE(price) OVER(PARTITION BY rnk ) / FIRST_VALUE(price) OVER(PARTITION BY rnk ) - 1) * 100 AS percent_change
    FROM ranked

)
SELECT
    *
FROM changed
ORDER BY time DESC;

and SQLFiddle with sample data


texts: Does pt-table-checksum point out rows that are different between Master and Slave?

texts:
Trying to verify replication integrity of my replica. And I found that pt-table-checksum is one of the best ways of doing it.

I have a simple Master->Slave set up. Have created a sample table with 10 rows on the Master, which has successfully replicated on the Slave. Intentionally on the slave I have changed a column in one of the rows of that table. Executed -

pt-table-checksum "h=host,u=user,p=password,P=3306" --function=MD5 --databases=test --tables=tentotal

And indeed in my checksums table that pt-table-checksum created, I could see different checksum for master and slave for that table. Excellent so far!

Output was -

TS                  ERRORS DIFFS    ROWS    CHUNKS  SKIPPED  TIME  TABLE
09-05T12:17:30      0      0        3       1       0        0.182 test.tentotal

Strangely the DIFF column says 0 which is incorrect. What am I missing here?

Percona tool kit version: 2.1.3


texts: Add credentials & set a specific role for a database

texts:
How can we add login credentials & roles for a particular database?

There is ServerObject,replication & management folders in MS SQLmanagement studio. What's the significance of these folders?

How can we add roles in MS SQLmanagement studio not for a particular database?

What are the main things that we should give more care when creating a database?


texts:
OK, I know the title is confusing, let me rephrase with a description of the problem. The environment is SQL Server 2012.

I have a table with Customers

Id
1

And a table with their transactions

Id, CustomerId, Amount
1, 1, 10
2, 1, 20
3, 1, -5

So the balance for a customer is the SUM of the amount column. In this example, customer 1 has 10+20-5=25 balance.

We have an external system where we need to transfer funds using a webservice (so a ditributed transaction is not possible). We must first make sure that the user has enough balance. So he may transfer 20 with the following insert

4, 1, -20

But if he then tried to transfer another 20, he should fail since now he only has 5 left.

The problem is when the two requests for withdrawal come at the same time at the server. So the balance check is executed with a few milliseconds difference, and returns 25 to both requests (since the insert is not executed yet because the web service has not returned in the first request)

My question is, what lock should I use, in order to ensure that the second transaction fails? I do not want to do a table lock since this table is used by thousand other users. How else can I be sure that the second transaction waits until the first is complete? Also, using code level lock would not do since we are using a webfarm and two seperate servers could be used for the two requests.

Thanks,
Themos


texts: Why cannot I call a table function in iSeries DB2 that I just created?

texts:
Using iSeries Navigator 'Run an sql script' window I created a new table function.
When I go to the Functions branch under the schema where it was created, I see the function among the other few. But when I call the function in the same Run an sql script window, I get an error:

SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), 'ANY')) AS T

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] FNREPORT in TESTDAT type *N not found.

Using SquirrelSql client I can see the function in the schema as well, but cannot call it due to the same error.
I can call other functions in the schema, but not the one I just created. What can be the reason?

I can call Generate SQL, copy/paste the schema and function name into query window, and then it will throw the above error.

Update:

Full source code of the function

CREATE FUNCTION TESTDAT.FNREPORT (
    DATESTART DATE ,
    DATEEND DATE ,
    ICLASS CHAR(3) )
    RETURNS TABLE (
    A INTEGER )
    LANGUAGE SQL
    SPECIFIC TESTDAT.FNCMSREPORT
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT
    DISALLOW PARALLEL
    SET OPTION  ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX
    BEGIN ATOMIC
        RETURN
        SELECT 1 AS A FROM SYSIBM . SYSDUMMY1 ;
        END  ;

Just to clarify that the solutions suggested below did not work.
If the query above was changed to

SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), CAST('ANY' AS CHAR()3)) AS T

or parameter ICLASS removed, or all parameters removed from the function definition, the exact same error was reported.


texts: PostgreSQL. Is using random tag in dollar quoting without escaping input safe?

texts:
Is that safe to run such query without escaping input? Assume noone can guess the random string I generate.

SELECT * FROM foo WHERE bar = $long random tag$ THIS NOT ESCAPED $long random tag$

What drawbacks are?
For example if the tag will change for each transaction, will it have performance penalties?

EDIT: Doing escaping correctly is also sometimes tricky - remember PHP mysql_escape and mysql_real_escape and sometimes can be fooled or just forgotten. Proposed approach can be automatic.

EDIT 2:
Any ideas if that can be made on pure Postgres SQL? Remember that dollar quoting can be nested.


texts: Do foreign keys affect performance in Oracle?

texts:
Regardless of whether you create indexes for your foreign keys (which if course you should), does having foreign keys have any impact on performance in Oracle databases?

One specific area i was wondering about was whether they help the database produce better query plans


texts: Is there a way to get a count about grouped rows?

texts:
I am trying to get a count about a grouped rows. Here is a description of the table of relevance:

column_name     data_type
-------------   ---------
id              integer
title           character varying
release_year    integer
country         character varying
duration        integer
language        character varying
certification   character varying
gross           bigint
budget          bigint

I am to trying to determine how many different years exist during which more than 200 movies were released.

Here is the query I have tried so far:

SELECT count(DISTINCT release_year)
FROM films
GROUP BY release_year
HAVING COUNT(title) > 200;

...which gives 13 rows of 1s.

The question is: how to get a number 13?


texts: Can you enforce uniqueness in MySQL based on column in foreign table?

texts:
I am refactoring one of my database tables and I've come up with an issue.

Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c.  I have a uniqueness constraint on the combination of these three columns.  Column my.d is a foreign key to an auto-incremented ID column in another table foreign.

I realized that this other table also has a column foreign.a and both column as are complete duplicates, i.e., if you join the tables together on d, my.a will always equal foreign.a.  Apparently this is part of the requirements.

I would like to remove column my.a from my table, but that will remove the uniqueness constraint.  Is there a way to somehow set up a uniqueness constraint on the combination of foreign.a, my.b, my.c?  Or am I better off just leaving the duplicate column in place?


texts:
I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this :

I have a function which runs on 4 shards. It looks like this :

CREATE OR REPLACE
FUNCTION count_comm(
    id_toto INTEGER,
    OUT out_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

It returns me a SETOF INTEGERS like this :

p_commentaire=> SELECT count_toto(42);
 count_commentaire_from_id_toto
---------------------------------
                         2172022

So basically, each of the result is the COUNT() of each shard, it works great as I said.
The thing is, I wanted to know if there's a way to SUM() all the results to get a total instead of having the 4 results individually ...

Edit : Just to add something, PlProxy needs a 'SETOF' return when you run the query on all shards.


texts: Extracting 'hot columns' into a separate table

texts:
I have this table:

CREATE TABLE fragment
(
  fragment_id integer,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  duration integer,
  -- <10+ more columns>
  revision_1 integer,
  revision_2 integer
)

It is pretty big: 44 million rows, 27 GB of disk space. Daily insert rate is about 70k rows.

The data in this table is almost never updated except for the last two columns named revision_1 and revision_2. They are updated via triggers set on other related tables. Updates come very frequently, especially for new rows in fragment table. Each row can be updated up to 50-100 times. Old rows (let's say 1 week old), however, stop being updated, as they are considered 'processed'.

As far as I know, UPDATE operation in Postgres is implemented as something like DELETE + INSERT. So, when a value in a single column is updated, the whole row is marked as deleted and a new row is created. That's why, I think, my fragment table is autovacuumed every day which takes several hours.

The question is, is it generally a good idea to extract 'hot columns' into a separate table? I mean something like this:

CREATE TABLE fragment_revision
(
  fragment_id integer,
  revision_1 integer,
  revision_2 integer
)


texts:
When should I rebuild the indexes in my relational database (SQL Server)?

Is there a case for rebuilding indexes on a regular basis?


texts:
i have two Oracle 12c instances. currently both are shutdown. when I try to connect with sqlplus / as sysdba it says tns protocol adapter error
contents of tnsnames.ora are

DB12102 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = DB12102)
 )
 )

LISTENER_DB12102 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORADB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = oradb.example.com)
   )
   )

 LISTENER_ORCL =
 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))

contents of listener.ora are

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
)

)

Output of lsnrctl status is

TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network   /admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/id1presasd0100/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)  (PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 -Production
Start Date                21-OCT-2015 02:44:00
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/id1presasd0100/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

still when i try to connect with sqlplus

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 21 02:45:36 2015
 Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 ERROR:
 ORA-12560: TNS:protocol adapter error

Any help on this ?? I repeat none of the instances are running currently. i am trying SQLplus on same machine as database.


texts: MySQL query cache with time based expiration

texts:
The database we use has a read and write intensive load. Some read queries are heavy.

Unfortunately the Query Cache cannot be used as it is invalidated too quickly.

We wanted to avoid the classic Memcached/MySQL couple to simplify the architecture and decrease connections/latency.

Is there a strategy to add a "time based cache" in MySQL? (ideally per collection)

EDIT:

Each query group has a different TTL (from 5 seconds to several days).


texts: Prepared query refused by MySQL when using placeholders

texts:
I'm trying to make a prepared query but MySQL refuses to accept it when I use placeholders:

This query works

WITH x AS (SELECT 'root' as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);

This query also works

PREPARE test_query FROM "WITH x AS (SELECT 'root' as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);";

Adding the ? placeholder fails

PREPARE test_query FROM "WITH x AS (SELECT ? as user_id)
SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id = user.user)
WHERE user.user = (select user_id FROM x);";

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? as user_id' at line 1

MySQL Information: mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

The queries were tested on the mysql database.


texts: Difference between OLTP and Inmon Data Warehouse

texts:
What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB.

I know what a Kimball datawarehouse is (denormalized, wide with surrogate identifier).
Inmon is more normalized.


texts: Ignoring Null (optional) Variables in Stored Procedure

texts:
I am looking for what is hopefully something easily achievable without a ton of coding.

I have a stored procedure that does some work and has about 20 optional parameters:

CREATE PROCEDURE dowork
                 @param1_1=NULL,
                 @param1_2=NULL
                 .....@Param1_30=NULL

I am implementing another procedure, that will call that one, and ideally, I want to offer all the parameters, but only use those that have been set:

CREATE PROCEDURE CallWork
                 @param1=NULL,
                 @param2=NULL
                 .....@Param30=NULL
AS BEGIN
--Do some stuff
EXEC dowork
     @param1_1=@param1,
     @param1_2=@param2
     .....@Param1_30=@Param30

Will the above example work as is, or do I need to create a variable of some sort to hold all the 'set' parameters for example?

The parameters are static. I have used number 1 to 30 as an example, but they are all static. Examples: @StartTime, @EndTime, @Type, @Location etc.

Currently dowork, processes a batches based on the input from the parameters. For certain workloads, I need it to process each batch, one at a time, instead of all at the same time.

By creating an abstracting procedure, I am planning to create a table variable to hold a row for each batch, and then pass them through one by one, allowing more control over each batch that we require in certain circumstances. This will then leave the original procedure to function as it did when required for mass batch jobs.


texts: Find the SQL query that caused an event in the past

texts:
I have a database in which I noticed that one of my tables is missing.

I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it happened and what code was used (or generated by SQLMS) for this action.

How can I do that?


texts:
I have 3 table in my sql database:

Table Room

roomnumber   roomtype
  1           DeluxeRoom
  2           DeluxeRoom
  3           DeluxeRoom
  100         StandardRoom
  101         StandardRoom
  102         StandardRoom

Room is a table which store the room and their type.

Table Reservation

reservationid  checkin       checkout
  1           2017-01-01     2017-01-03
  2           2017-01-10     2017-01-20
  3           2017-02-05     2017-02-08

Reservation is a table where users make their booking.

Table RoomReserve

reservationid    roomnumber
 1                 1
 2                 100
 2                 101
 3                 1
 3                 2
 3                 3

In RoomReserve, for each reservationid, I can input multiple values for roomnumber. I can also input same roomnumber for different reservationid if the checkin and checkout date are not the same.

I am trying to make a query  to count the available rooms when user want to book rooms but the results i am getting is wrong.

Here are the codes i am trying:

SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
        SELECT  *
            FROM  roomreserve r,reservation e, room m
            WHERE  r.reservation_id=e.reservation_id
              AND  m.roomnumber=r.roomnumber
              AND  ('$checkin' BETWEEN start_date AND end_date)
              OR  ('$checkout' BETWEEN start_date AND end_date)
                          );
2)
SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
SELECT  COUNT(roomnumber)
    FROM  roomreserve r,reservation e, room m
    WHERE  r.reservation_id=e.reservation_id
      AND  m.roomnumber=r.roomnumber
      AND  ('$checkin' BETWEEN start_date AND end_date)
      OR  ('$checkout' BETWEEN start_date AND end_date)
                          );


texts: Oracle insert through dblink performance issue

texts:
I'm trying to do an insert as select to a table via dblink. The query looks like this:

    Insert into MARS_SUPERVISORS_PUB@EDHPUB
     select
        {...}
     from edhper a, edhper b
     where {...
              a.idn not in (select s.tarcfsra idn from cfsra s where s.srtcfsra = 'MAPSSPA' and s.stscfsra = 'A')
           ...}

The select itself works quite fast here is its execution plan shows all the joins, but the insert takes very very long and the plan consists only of "REMOTE" steps :

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
  SORT AGGREGATE
   FILTER
    REMOTE
    REMOTE
  FILTER
   HASH JOIN
    REMOTE
    REMOTE
   REMOTE

I wonder if Oracle tries to push both tables a and b through the database link and then joins it there.
Can I instruct the optimizer to do a select inside the current DB and pass only the results to @EDHPUB?

P.S/

If I create a test table locally, insert this select there and then do an insert like:

Insert into MARS_SUPERVISORS_PUB@EDHPUB
select * from TEMP_TBL

it works fast. For me it looks like an evidence that in my previous example Oracle really tries to push unjoined tables thtough dblink.

UPDATE/
I figured out that the insert takes long because of a a.idn not in ... condition.
without it the plan looks like this:

INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
 LOAD TABLE CONVENTIONAL
  SORT AGGREGATE
   FILTER
    REMOTE
    REMOTE
  HASH JOIN
   REMOTE
   REMOTE


texts:
I have a problem on my servers sitting on different domains.

I created a linked server that executes successfully and I can list the catalogue but when I schedule a job to execute I get this error:

  Access to the remote server is denied because the current security context is not trusted.

How can I resolve this?


texts: Oracle 11g r2 standard edition installation - set environment variable PATH and create tnsnames.ora

texts:
I have installed Oracle 11g R2 standard edition (server software, 2GB download from Oracle).

I found that the 'Environment Variable PATH' has not been set. Which of these directory should I use for the PATH value:-

Oracle base
Software location
Database file location

Also do I need to create 'TNS_ADMIN' environment variable and 'TNSNAMES.ora' file

Kindly advice. Thank you


texts: PostgreSQL database does not exist error

texts:
I am getting a "database does not exist error", even when the database exists:

postgres=# GRANT ALL PRIVILEGES ON DATABASE Owino to OdooD;

  ERROR:  database "owino" does not exist

Name    |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges

Owino   | dominus  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres| postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres           +
        |          |          |             |             | postgres=CTc/postgres  +
        |          |          |             |             | orokon=CTc/postgres    +
        |          |          |             |             | powerbidev=CTc/postgres+
        |          |          |             |             | odoodev=CTc/postgres


texts: Anyway to identify mysql slaves have read upto which binary logs

texts:
I have multiple slaves reading from one master.. But few of them are keep disconnecting due to network issue.

Is there any way that i can make sure before setting up the bin log purging script on master that all the slave have read upto certain bin logs which we can purge?


texts:
I am facing below issue with my Server. Does this directly indicate that I have to increase my RAM immediately?

Current Setting SQL Server memory values, out of total physical RAM of 128GB:

Min Server Memory - 0
Max Server Memory - 120 GB

I have mentioned the perfmon counters too below. Kindy advise.

We are getting this alert at least every 2 days alternatively.

  SQL Server:Buffer Manager Page writes/sec has exceeded the threshold
  of 90

Perfmon counter Values :


texts: INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER'

texts:
I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache.
As part of step 1 in the SQL job,i filter only the serial plans and insert it into a table i created.
When i run the job,in step 1 itself i get the below error;

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

I did research and learned that it might be due to the SET options.
So i checked the table i created and it is created with

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

I saw posts where people have the same issue ,and solved it after changing the above SET options to ON.
I have this SET already ,but still same error.i am not sure why i am getting this error.

I have this line in the query where i get the serial plans.

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )

This is how it looks like :

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS (
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
--------------------
--------------------
----------------------
))
INSERT INTO table_name
select * from relop
WHERE relop.total_relop = relop.serial_relop

When i run it on the query window,there is no issues.
When i put that in a job,i get this error.

Any suggestions?


texts:
I'm creating a website in which people can submit there own lists, and then essentially others Up/Down Vote the items within the list. I'd like the list (If the submitter allows "votes") to be ordered by the amount of votes (Easy Enough to do in SQL I know).

However, my issue is with the DB Design, I don't know how to design it to make it work.

So far my Idea is:

Lists Table: ListID, ListDescription, ListItem(x [e.g. 1, 2, 3 4]), CreatedDate, UserID (if it is user submitted).

Votes Table: VoteID, ListID

I can't quite get my head round, how to setup the votes table to record votes for each item in the specified list?

Could anyone help please?

Thanks


texts: SQL Server Log Shipping for Server Migration - What's the final step?

texts:
Working with a server running SQL Server that was the log shipping target for two other servers as part of server consolidation/migration. In other words, server B was log shipping to server A and server C was log shipping to server A and the we failed over both to A and retired B and C.

Server A is happily chugging along. All the old log shipping related jobs were paused and deleted long ago.

Enter a new DB monitoring tool we are trialing (Spotlight Cloud) that throws warnings about our server being a secondary with a high restore latency (2 years! lol). Now, I know this isn't really an issue (afaik) but it does cause me to wonder -- what step did I skip in those failovers?

I still have entries in dbo.log_shipping_secondary. Should I? I have looked through SQL Docs online and don't really see anything covering this scenario as far as final steps.


texts: I'm new to Oracle databases. I have used to SQL Server and the description fields (MS_Description) for tables and columns for documentation purposes. Is there an equivalent for Oracle? What is the best practice for documenting Oracle database?


texts:
I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score

So far so good. The question is: How do I get what's a users rank?
For example, I have a users Name or Id and want to get his rank, where all rows are ordinal ordered descending for the Score.

An Example

Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

In this very case, Assem's rank would be 3, because he got the 3rd highest score.

The query should return one row, which contains (only) the required Rank.


texts: Table for optional parent/child relationship

texts:
Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.

The item can either belong to a parent or child and not both.

I have other tables that are similar to Item and they too can belong to either a Parent or Child.

Should I simply just add 2 nullable FK to them?

Can I enforce that either a Parent or Child must exists using the db?


texts: Can we create SQL Server Aliases in Linux clients?

texts:
Can we create "Server Aliases" in Linux clients (webservers etc.) so that you can change connections without touching the application's DB URL string?
The official documentation seems to talk about a GUI way of doing this in Windows, so I was wondering if something like this exists for Linux. This has lots of benefits, especially during containerization of apps.

My high level objective is to see if I can create some level of indirection so that the application always thinks of DB as DSN=myDsn;Uid=myUsername;Pwd=myPwd; while the real driver redirects it to DSN=realDsn;Uid=realUsername;Pwd=realPwd;. Initially I was thinking of something like socat, but I am not sure how password changes can be handled! "Server Aliases for client" seems to do something like this, hence the question.


texts:
Today I have been having quite an interesting issue, which I can't seem to fix. I am getting drive information for a Failover Cluster Instance SQL Server 2012. These drives are Volume Mount Points. For about 40 instances this runs perfectly except for one FCL this does not retrieve the right drive.

The query I use is

select
 case when type_desc = 'ROWS' then 'DATA' when type_desc = 'LOG' then 'LOG' else 'Unknown' end as DataOrLog,
    mf.database_id,
    mf.name,
    mf.physical_name,
    Drive.volume_mount_point,
    Drive.logical_volume_name
    from sys.master_files mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) Drive
    where mf.database_id not in (1,2,3,4)

The normal result I get for this query is.

This is the result I get with the problem case.

What can be the reason that sys.dm_os_volume_stats can't find the right volume?

Is it some wrong configuration at FCL level?

Why does my sys.master_files show the right drive but the cross applied fileID to sys.dm_os_volume_stats not ? How does this connection work with this DM?

I also checked the drive itself and the file is definitely in the right drive.


texts: What's the Performance Penalty of Throwing an Error in SQL Server?

texts:
I was reading the great book Writing High-Performance .NET Code by Ben Watson and he was saying to try avoid throwing exceptions in .NET code because of the horrible performance they create (having to grab a stack trace etc). So, I was wondering. Does SQL Server create a trace of the error or is it more simple? Are there any performance implications for throwing an error in SQL Server? Does this exception end up causing performance problems when it reaches the C# code?

I thought I would ask before I write test code for it, if anyone knew already. Since writing tests can be tricky and I don't have much time to do it outside of work.


texts: Partition by ascending and descending performance

texts:
I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is...

ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY d ASC, e ASC) AS row_number_start,
ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY d DESC, e DESC) AS row_number_end

The execution plan shows two sort operations, one for each. These sort operations make up over 60% of the total cost of the statement (we're talking tens of millions of rows here, the partitions will usually have 1-100 records per partition, mostly under 10)

so it would be good if I could get rid of one of them. I tried to create an index to replicate the sort; this eliminated one of the sort operations but not the latter. (Note that any index created would only be of use for this process, and would be recreated daily as part of an ETL process.)

From inspecting the execution plan, I believe the problem is that when doing a partition by statement, SQL Server insists on ordering by the partitioning columns on an ascending basis. Logically it doesn't matter if you order ascending or descending, and if the optimiser understood this then it could just read the same index backwards to work out row_number_end.

Is there any way I can make the optimiser see sense here, or can someone suggest an alternative approach to reach the same end goal?


texts: Can I default to T-SQL only view in VS 2012?

texts: When creating new tables in a database project in VS 2012, opened sql files default to a split view. One is the T-SQL text editor view, and the other is the Design view. I've never used the design view, and probably never will. I just prefer the text editor. Is there a way to tell VS to default to the T-SQL view?


texts: SA user password for software which automatically installs SQL Server 2005

texts:
I am testing a software which requires SQL Server 2005 for storing the data. The SQL Server 2005 database is automatically installed when I installed the software. During the software installation I didn't encounter any screen to configure the SQL Server 2005 database so I am assuming the configuration is done by the software installer.

I can reset the password for the sa but theoretically it would stop the application from working. Can anyone push me in a direction by which I can access the database with a client?


texts: MySQL FULLTEXT search (match against) not working

texts:
CREATE TABLE `test` (
  `id` int(11) NOT NULL COMMENT 'unique id',
  `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `text`) VALUES
(1288, 'i just don\'t know what to say.'),
(1340, 'so what?'),
(1344, 'that was probably what influenced their decision.'),
(1345, 'i\'ve always wondered what it\'d be like to have siblings.');

ALTER TABLE `test` ADD FULLTEXT KEY `text` (`text`);

select *,
    match(text) against ('what' IN BOOLEAN MODE) as hasWhat,
    match(text) against ('just' IN BOOLEAN MODE) as hasJust
from test;

The match against "what" always returns 0 even though I'm using "IN BOOLEAN MODE." Why?
With MySQL 5.7.26 but it's also reproducible on http://sqlfiddle.com/#!9/c5c7f90/1 and https://sqltest.net/#1303145.
thanks


texts: TSQL Materiaized/Indexed View Cluster index with subquery

texts:
I have two tables: Listings and Media and Media is a child of Listings.  I am trying to create a indexed view that will include the first record from Media.  I would also like to index my view so that this view is as performance as possible.

View

ALTER VIEW GridListings
WITH SCHEMABINDING
AS

SELECT l.ListingId, l.Title,
    (SELECT TOP 1 ThumbnailUrl
        FROM [dbo].Media
        WHERE ListingId = l.ListingId) as Thumbnail
FROM [dbo].Listings l

Index

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20200528-150854] ON [dbo].[GridListings]
(
    [listingid] ASC
)

Error

  Cannot create index on view "dbo.GridListings"
  because it contains one or more subqueries. Consider changing the view
  to use only joins instead of subqueries. Alternatively, consider not
  indexing this view.

The view I am going to create will include a lot of fields from Listings table, including a spatial field that I will need to filter on.

I have used the above approach to flatten both of my tables into a single list so that its fast to query and filter on which includes the thumbnail from Media.

Am I using the correct approach or is there a better/more performant way of doing it instead of indexed views

I am using SQL Server 2017

Here is the plan without any indexes on the view
https://www.brentozar.com/pastetheplan/?id=BkdOyq6iL


texts:
Is it possible to disable a trigger momentarily but for just one table.

For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but they only affect certain columns in table A.

I now have an update query that uses both tables. I know the updates in table A need to fire the triggers but the updates in table B definitely do not need to fire the triggers. So I would like to disable those triggers until the updates are done.

Is this possible? I'm using MySQL 5.1

[ADDENDUM]

Here is a trigger table A essentially

BEGIN
    IF (OLD.status != 1 AND NEW.status = 2) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        END IF;
    ELSEIF (NEW.status != 3) THEN
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
        END IF;
    END IF;
END

Here are the triggers on table B essentially

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

Here is the stored procedure that's fired from table B

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$


texts: Postgresql cannot restore partial pg_dump "relation "public.my_table_seq" does not exist"

texts:
In my database I dumped some tables via the following psql command:

pg_dump -t my_table1 -t mytable2 > my_dump.sql

And I try to import it into a postgresql docker image as a standalone database using docker-compose.yml:

version: '3.1'
services:
    postgresql:
        image: postgres:9.6
        volumes:
        - './my_dump.sql:/docker-entrypoint-initdb.d/my_dump.sql'
        environment:
        POSTGRES_USER: $MY_DB_USER
        POSTGRES_PASSWORD: $MY_DB_PASSWD
        POSTGRES_DB: $MY_DB_NAME

But I get the following error:

postgresql_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/my_dump.sql
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  |  set_config
postgresql_1  | ------------
postgresql_1  |
postgresql_1  | (1 row)
postgresql_1  |
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | SET
postgresql_1  | ERROR:  relation "public.my_table_seq" does not exist
postgresql_1  | STATEMENT:  CREATE TABLE public.my_table (
postgresql_1  |         table_id integer DEFAULT nextval('public.my_table'::regclass) NOT NULL,
postgresql_1  |         name character varying(50) NOT NULL,
postgresql_1  |         mode character varying(50) NOT NULL,
postgresql_1  |         workers integer DEFAULT 1
postgresql_1  |     );
postgresql_1  | psql:/docker-entrypoint-initdb.d/cue.sql:32: ERROR:  relation "public.my_table_seq" does not exist
cue_docker_postgresql_1 exited with code 3

Meaning that for some reason I fail to dump the sequence as well via pg_dump. So I want to know how I can pg_dump specific tables alongside with their respective sequences used.

What try to do with that is to split an existing database schema into seperate databases due to its sheer size and also for scalability reasons as well.


texts:
MySQL keeps crashing randomly - recent errors in error logs says this

160626 21:50:42 [Warning] IP address '113.108.21.*' could not be resolved: Name or service not known
160626 22:42:57 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:10 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:25 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:41 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:54 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:44:09 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160627  0:45:24 [Warning] IP address '117.79.84.*' could not be resolved: Name or service not known

160624  7:00:31 [Warning] IP address '58.221.62.*' could not be resolved: Name or service not known
160624  7:05:14 [Note] /usr/sbin/mysqld: Normal shutdown

160624  7:05:14 [Note] Event Scheduler: Purging the queue. 0 events
160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16955  user: 'amember'

160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16953  user: 'amember'

160624  7:05:16  InnoDB: Starting shutdown...
160624  7:05:22  InnoDB: Shutdown completed; log sequence number 195606046122
160624  7:05:22 [Note] /usr/sbin/mysqld: Shutdown complete

160624 07:05:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160624 07:05:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

In my.cnf are this lanes, which can resolve this somehow?

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

Server have CPUs with 20cores and 258450MB of RAM


texts: MS Access: Update data from one table to another based on a choice

texts:
Help!
I have a patient form where their referring doctor is chosen and that ID for the doctor is stored in a variable. When I click "Save record" I need it to copy the data form the ReferringDoctors table (name, address, etc) and copy it to the PatientDetails table in corresponding variables based on which doctor has been chosen. I can't figure it out. Help!

Using Access 2013

Re-edit:
The whole idea here it that I have three separate tables - 1 for the DoctorInfo (just address data), 1 for the PatientInfo (just address data), and 1 for the PatientVisit info (why they came that day). The goal is to be able to click a button on the PatientVisit form to generate a letter in Word that includes the address from the doctor, the patient's address and the information from the patient visit.

So far, I have all the information I need stored in the three tables, but I have no clue how to grab the information to generate the letter. For example if Patient 1 is referred by Doctor A on the current visit, how do I generate a letter that has that information? I assumed I would need to copy the info from one table to another to eventually pass it to Word for it to use to generate the letter. Totally wrong?


texts: What is the largest whole number in Oracle SQL table that can be stored without rounding errors?

texts:
I want to "concatenate" numbers to create temporary ids for tuples of objects, but I am worrying about precission loss and rounding errors. Query below suggests that there are no problems with rounding at least up to 10^27.

select t.remvalue, t.logvalue, t.totvalue, (t.totvalue - 1000000000*1000000000 ) as diffvalue18, (t.totvalue - 1000000000*1000000000*1000000000 ) as diffvalue27  from (
select 1 as remvalue, 9 as logvalue, 1000000000 + 1 as totvalue from dual union all
select 2 as remvalue, 18, 1000000000*1000000000 + 2 from dual union all
select 3 as remvalue, 18, 1000000000*1000000000 + 3 from dual union all
select 4 as remvalue, 27, 1000000000*1000000000*1000000000 + 4 from dual
 ) t
--  Rem log  total   difff18       diff27
--4 4   27  1E27    9.99999999E26   4      --diff 27 is correct

How far can I actually go with such "concatenation of integers without running into a problem?


texts:
I am not a DBA, but an end user that mashes together data from various databases within my company to generate insights.

When I first encounter a new (to me) database, I spend time scrolling through the tables/views/columns in SSMS or Power Query to understand what data is in the database, and how it is structured.  This is a manual process, and I may overlook key tables when scrolling through a long list.

Is there an easy tool that can scan a database and visually represent the database structure?  I believe older versions of Visio could reverse engineer a database and generate a database model/entity relationship diagram, but the current version does not. If it matters, the databases in question are all MSSQL


texts: Share single index for multiple linearly correlated columns

texts:
I've got a table with two columns whose values have a perfect linear correlation, for example

CREATE TABLE measurements (
  sensor int PRIMARY KEY,
  num serial PRIMARY KEY,
  time timestamptz DEFAULT now(),
  value float
);
-- many times:
INSERT INTO measurements(sensor, value) VALUES ($1, $2);

Both the time and the num are monotonously increasing, a row with higher num value will also have a larger time value.

Postgres will create a btree index on the primary key columns. Can I somehow tell it to also use the same index when querying for rows by their time instead of by their num? As in

SELECT * FROM measurements WHERE sensor = $1 AND time >= $2 ORDER BY time;

The resulting rows would have exactly the same order as if sorted by num.

Is there a way to let the optimiser know? I've seen many articles on cross column correlation statistics, most of them linked in this StackOverflow topic, but the multi-column statistics seems to only analyze dependencies between individual values, and are unable to do a linear correlation.

I was hoping to achieve the same result as if I created another index on sensor, time, but have postgres need to maintain and store only a single index.


texts:
I have a problem with my SQL Server database. Today I opened it and saw an error – SQL Server Table Corruption Error.

I don't know the reason for this error. I applied SQL Server Management Studio, but it couldn't completely restore my database.

Does someone know methods of repairing SQL Server databases?


texts: Controlling number of idle sessions in postgresql 11.5

texts:
We recently moved from PostgreSQL 9.0 to PostgreSQL 11.5.
We are now having problems with hundreds of idle sessions consuming all the database connections.
We have tried defining idle_in_transaction_session_timeout=30000.  That only managed to abruptly terminate legitimate processes while they were awaiting results from queries
We have tried defining tcp_keepalives_idle=300, tcp_keepalives_interval=30, and tcp_keepalives_count=3  Still, the number of idle sessions are in the hundreds.
When we kill idle sessions, or restart the database, the idle sessions reconnect, and stay idle.


texts: Fastest query for distinct IDs in a many-to-many relationship

texts:
I have this table in PostgreSQL 9.4:

CREATE TABLE user_operations(
    id SERIAL PRIMARY KEY,
    operation_id integer,
    user_id integer )

The table consists of ~1000-2000 different operations each of which corresponds to some subset (consisting of approximately 80000-120000 elements each) of the set S of all users:

S = {1, 2, 3, ... , 122655}

Parameters:

work_mem = 128MB
table_size = 880MB

I also have an index on the operation_id.

QUESTION: What would be the most optimal plan for querying all distinct user_id for a significant part of the operation_id set (20%-60%) like:

SELECT DISTINCT user_id FROM user_operation WHERE operation_id < 500

It's possible to create more indexes on the table. Currently, the plan for the query is:

HashAggregate  (cost=196173.56..196347.14 rows=17358 width=4) (actual time=1227.408..1359.947 rows=598336 loops=1)
  ->  Bitmap Heap Scan on user_operation  (cost=46392.24..189978.17 rows=2478155 width=4) (actual time=233.163..611.182 rows=2518122 loops=1)
        Recheck Cond: (operation_id < 500)
        ->  Bitmap Index Scan on idx  (cost=0.00..45772.70 rows=2478155 width=0) (actual time=230.432..230.432 rows=2518122 loops=1)
              Index Cond: (operation_id < 500)

Is such query plan really optimal in such circumstances? I mean, I'm not sure about correctness of using Bitmap Heap Scan. I'll appreciate any references to relevant articles.


texts:
I have 2 tables

Person Table

PersonID   Firstname LastName
1              a         b
2              c         d
3              e         f
4              g         h
5              i         j

Incident Table

InicdentID PersonID
1             1
1             2
2             3
2             4
3             5

I want to STUFF Name with semicolumn for one Incident

IncidentID Name
1            ab;cd
2            ef;gh
3            ij


texts:
What's the query to set a login user as db_owner during the creation of database.
Following is the set of queries, which I'm using right now,

Create database Test1;
ALTER AUTHORIZATION ON DATABASE::Test1 TO [DEVELOPMENT\d-testuser];

This servers my purpose, but here I have firstly created a database with default db_owner and then altered it. I need a query which will select [DEVELOPMENT\d-testuser] as my database owner during the creation of database itself.