texts: Extract rows of a table, that include less than x NULLs

texts:
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).

My database is similar to this structure:

 c1        c2      c3      c4        c5
-----------------------------------------------------
2          3       NULL    1         2
2          NULL    NULL    1         2
2          3       NULL    NULL      2
NULL       3       NULL    1         NULL
2          3       NULL    1         2

I tried the query, which doesn't return an error, but no rows are selected:

SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);

I expect this query to return the 1st and the fifth row, but the result contains 0 rows.

I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:

CREATE TABLE test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);


texts: parallelism in sql server in user defined function

texts:
a four page long query (that creates two temporal tables, and then joins them) in sql server runs in a dozen of seconds (thanks to parallel execution). The same query packed into a user defined function (with temp tables replaced by table-variables as those are not supported in UDF) takes a dozen of minutes to compute (thanks for no-parallelism).
If I inspect the query plans, they are exactly the same, except the UDF version does not do parallel execution with SQL Server mentioning (I omit all the details as the query plans barely fit into A0 size page):
 <QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

is there any "cheap" way to tell the sql server to try to parallelize the SQL Statements inside the UDFs?
The prototype of the function is:
CREATE  FUNCTION import (@ts_start datetime2(3), @ts_end datetime2(3))
RETURNS @imports TABLE (id uniqueidentifier)

while previously those two parameters where given in a declare variable:
declare
      @ts_start datetime2(3) = '2020-06-22 00:00:00'
    , @ts_end datetime2(3)   = '2020-06-23 00:00:00'

Any way to parallelize the sqls inside a udf in sql server?


texts: How to represent class table inheritance (current DBMS-specific way please)?

texts:
I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server.

I found a recommendation for MySQL:

How do I map an IS-A relationship into a database?

Basically, it is:

use identical primary keys between the parent table (Account) and the subtype tables (CatAccountDetails, DogAccountDetails)
add a subtype discriminator column to the parent table and the subtype tables
include both the shared primary keys and subtype columns in a foreign key constraint from subtype to parent type tables

Adding a type column that is constrained to a single value (e.g. a 'C' column in CatAccountDetails) feels just a little hackish, so I wondered if there is a feature in SQL Server to enable this scenario?

Also, if this is the way to do it, should I define something additionally to prevent this 'unused' column from degrading performance when my ORM (Entity Framework) requests a table join, like create an additional foreign key on just the PK?

The main problem is that CatAccountDetails will have different columns from DogAccountDetails. Cats have both different and far more properties than dogs in my application. Side note, 'cats' and 'dogs' represent three categories of website members in my current scenario.


texts: Replace value of strongly typed xml element in SQL Server with XQuery

texts:
Given a element, defined within an XML Schema Collection as such:

<xsd:element name="xid">
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="32" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

How would you update the element using XQuery?

The element is found within the ns namespace in the schema collection. I have been trying to update the element the below query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com";
     replace value of (/ns:*/ns:xid)[1] with "X00011793" cast as element(ns{http://www.anon.com}:xid,#anonymous) ?')
 where id = 11793

but this generates the following error:

  Msg 9301, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: In this version of the server, 'cast as
  ' is not available. Please use the 'cast as  ?' syntax.

If I remove the cast entirely and use this query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com";
     replace value of (/ns:*/ns:xid)[1] with "X00011793"')
 where id = 11793

I get this error:

  Msg 2247, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: The value is of type "xs:string", which
  is not a subtype of the expected type "<anonymous>".

If I issue this query:

update cm.item
   set data.modify(
      'declare namespace ns="http://www.anon.com/";
       replace value of (/ns:*/ns:xid/text())[1] with "X00011793"')
 where id = 11793

I get this error:

  Msg 9312, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: 'text()' is not supported on simple typed
  or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found
  '(element(ns{http://www.anon.com/}:xid,#anonymous) ?) *'.

I am targeting SQL Server 2008 R2.

Thanks!


texts: DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces?

texts:
We are in the process of tuning a massive application our company has just written. Part of that tuning includes tuning the databases. We run DB2 LUW on AIX. Specifically we are at 9.7 Fix Pack 4 (although we hope to migrate to 10.1 Fix Pack 2 sometime this year).

We initially built out tablespaces only needed by the size of the data (ie, if all the tables fit in a 4K tablespace, we only created 4K tablespaces, our thought being why create tablespaces to take more disk if they are unused). This was the same for temporary tablespaces as well.

The developers have told us that they have achieved better performance by adding in a 32K temporary tablespace. So all their tables are going into 4K, 8K, or 16K tablespaces. Yet they added a 32K temporary (which has its own bufferpool) and they claim they halved some of the transaction times of their application.

When I think about it, I suppose that makes sense. I am guessing the optimizer sees the 32K space as a great place to perform joins/sorts and has more memory at its disposal being 32K vs 4K.

My fellow colleage said he (regardless of the above), he had read somewhere that DBAs should always place temporary tablespaces into their own bufferpools. When I asked him for the links to read up on it, he couldn't remember.

I am wondering...should temporary tablespaces always get their own bufferpools? Does this help with join/sort performance? Is that a good practice?

And secondly on the side, does it make sense to always create a 32K bufferpool and 32K temporary tablespaces for those join/sorts?


texts: Can 2 different versions of MySQL be run side by side?

texts:
I have Debian 10 installed and have an instance of MySQL 8 running.

I'd like to install 5.7 to try and bring up the database of a machine that failed, where I have the data directory fully intact, so I can inspect it and save the schemas, data, triggers, functions and stored procedures.

Does this question make sense?


texts:
I have data in two tables. One has order details, the other has product details. The order_details table references the product table via the product_id column. The product table has columns product_id and product_name.

I count sales per product ID like this:

SELECT product_id, COUNT(*)
FROM order_details
GROUP BY product_id
ORDER BY COUNT(*) DESC

and get output like this:

product_id
123         2500
421         1700
111         1040

Now I want to have the names from the product table instead of the IDs:

apples  2500
banana  1700
pear    1040

How can I do that?


texts: What causes 'Unknown token received from SQL Server'?

texts:
I have a SQL Server 2005 box with a classic ASP front end (IIS6) calling a stored procedure that uses dynamic SQL. It works except for a single day of data that somehow throws this error even though it runs fine in SSMS.

Since it works for other date ranges and works within SSMS (with this date range), I'm guessing there must be something in the dataset that's triggering the error. However, calls to it using the date range plus filters that result in no data returned still triggers this error, so I don't think it's related to what's being passed back to the IIS app.

Any suggestions on how I can track this down would be appreciated.

Thanks


texts: PostgreSQL network compression for result set?

texts:
I was reading about network compression with PostgreSQL to see if there was anything that offered that which would permit big result sets to transfer over the wire to remote web servers. It seems there is nothing of this sort. Is there a solution out there to do that, that isn't SSL?

SSL

It seems at one point there was a solution involving SSL. Having read the threads pertaining to that, it seems that the solution is no longer advisable as it leaves the system vulnerable to a CRIME worldwide bug.

SSL Compression doesn't work
sslcompression / PGSSLCOMPRESSION not behaving as documented?

Though not advisable, it seems that you can re-enable SSL Compression at least currently with OPENSSL_DEFAULT_ZLIB but that it will be removed in the future and that it's currently disabled at compiled time in openssl.

no-zlib         [default]
no-zlib-dynamic [default]


texts: Moved database (percona mysql) to larger disk, now everything is missing (though the files exist)

texts:
I dont know anything about databases or MySQL beyond the absolute basics so lets start with that. I moved the /var/lib/mysql from the / to a mounted volume which I remounted as /var/lib/mysql after moving the files to temp storage.

Ok, now when I go to my sites, its like they dont have any database attached to them. Wordpress wants to reinstall, other sites cant connect to their databases either.

When I go to the file system directly (/var/lib/mysql) and look for the files, I see that they are in their respective directories (I even restored from a backup just to be extra sure the files were there).

I have tried to repair and check the tables. But it keeps saying that some of the tables dont exist.

I am totally lost and frustrated.


texts: Logical condition when declaring handler procedure

texts:
I have a procedure in MySQL that executes a handler for SQLEXCEPTION to read the errno code and insert it to a table.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

I'm expecting something like this

DECLARE EXIT HANDLER FOR SQLEXCEPTION OR SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

Is there a chance to use OR or AND logical operators in sql handler?

Or, can I get some advice to get the WARNING and ERROR messages in one DECLARE line?


texts: Query to find users with passwords same as their username in Oracle

texts:
I'm writing an Oracle audit script where I need to find users in a database who have set their passwords to be the same as their username, e.g. dbaadmin/dbaadmin. The query would need to get the list of users in the database & then check if their passwords is same as their respective usernames.

Can anyone help me out with this query?

PS: I'm a Database newbie.


texts: MaxDB - Strange behaviour when selecting from a CLOB-Row

texts:
I am using "sqlcli" for Linux to write the output of the following query into a file:

  select clob_field from table (...)

in SAPGui (original user input) the contents are displayed as:

First line
Second line
Third line

in Database Manager the contents are displayed as:

First lineSecond lineThird line

But in my output-file it is displayed as:

First line\rirst line
\nrst Line
Second line \rst line
Second line
\nt line
Second line
Third line

My desired output-file should look like:

First line\nSecond line\nThird line

I am seriously out of ideas of what's happening here or how to fix output. MaxDB also does not seem able to perfom any kind of "CAST"-function.
Replacing "\n" and "\r" doesn't work either, since it does not replace special characters.

Any ideas?


texts: Is it safe to delete dummy tables Oracle 11g XE

texts:
i created a database schema in 11g express edition, however there are some APEX tables and some dummy data...

Is it safe to delete them? also i would like to know what does apex means and why they have a dollar sign, thanks...

APEX$_ACL                      TABLE
APEX$_WS_FILES                 TABLE
APEX$_WS_HISTORY               TABLE
APEX$_WS_LINKS                 TABLE
APEX$_WS_NOTES                 TABLE
APEX$_WS_ROWS                  TABLE
APEX$_WS_TAGS                  TABLE
APEX$_WS_WEBPG_SECTIONS        TABLE
APEX$_WS_WEBPG_SECTION_HISTORY TABLE

I used SYS to create a new user, the new user was created along with a workspace and a database schema, please see the images above.


texts: Why is PostgreSQL very slow when I execute a SELECT query with OR in WHERE clause?

texts:
The query is like:

select count(*)
from test1 as r
WHERE r.testid in (select p.testid from test2 p where p.testname IS NULL)
OR r.testid > (SELECT MAX(p2.testid)FROM test2 p2);

but when I rewrite it as UNION it works. What is the problem of this query?


texts: Get data from multiple databases at once

texts:
I need to UNION data from tables with the same name in multiple databases. All databases have the same prefix: CDNXL_(NAME OF DB)

How would I execute this query against more than one database at at time?

SELECT
    prac_id,
    prac_name,
    prace_surname,
    prac_IDcard,
    prac_workplace
From prac_nag.CDNXL_ (NAME OF DB)
where prac_IDcard = @parameter

Can this be done with  'IF' or 'WHILE'?

-- Edit
Now I can see my mistake.

I have got 46 Databases with prefix CDNXL_ but one is DB with CDNXL_Configuration, and doesn't have got table was I wanna. How can I skip/ignore this DB?


texts: How to add foreign key "with more than one column" in SQL Power Architect

texts:
In the following picture, I have a model that I'm building in SQL Power Architect:

I already have table 1 referencing a column (CDCONTR) from table 2. But I want to make the foreign key to reference two columns from table 2, I mean, both the column CDCONTR and CPF must come from table 2. How can I do this?

I'm using SQL Power Architect 1.0.7. About my RDBMS, I'm just using this power arch.


texts: How to insert values in mysql taking previous row into account

texts:
I have this table, players, with the following columns:

id
name
player_number
round

and I would like to auto fill the player_number (1 and 2) and increment the round when inserting a new player 1.

How can I do this without having to retrieve last player and make a new query ?

When I insert a new player, if it has no player, make round 1 and player 1. When adding a new player it makes him player 2 and round 1. After this adding a new player makes player 1 and round 2 and so on...


texts: Can a SELECT statement block an ALTER TABLE?

texts:
I see in Postgres docs:

  Two transactions cannot hold locks of conflicting modes on the same table at the same time

During maintenance (schema migration) we saw cases when we can't ALTER TABLE because of hanging SELECT in transaction (pg_locks + pg_stat_activity).

In order to proceed migration we revoke CONNECT from "rogue" users and kicked all their sessions.

Is that correct that:

BEGIN;
SELECT ...;

will block ALTER TABLE until COMMIT or ROLLBACK?


texts: I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. The delete operation runs for 2 days now and I got an error "Lost connetcion to mysql server during query" a few hours ago. The mysql processlist command shows that delete operation with status "Updating", so it seems like the query is still running though the command line has shown me this error. How can I trace that down? I basically don't know if it is still deleting or got stuck. I am afraid killing the process because I do not want to trigger a rollback.


texts: MySQL Replication: Seconds Behind Master keep growing

texts:
We have set up a new slave server for our production database. Since than the Seconds_Behind_Master keep growing slowly.

This is the slave output:

           Slave_IO_State: Waiting for master to send event
              Master_Host: notimportant
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.003790
      Read_Master_Log_Pos: 42585179
           Relay_Log_File: mysqld-relay-bin.002798
            Relay_Log_Pos: 32374374
    Relay_Master_Log_File: mysql-bin.003492
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 32374215
          Relay_Log_Space: 31350956440
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 1448477
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
         Master_Server_Id: 6524
              Master_UUID:
         Master_Info_File: /var/lib/mysql/master.info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Reading event from the relay log
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set:
            Auto_Position: 0

Show processlist on SLAVE:

|    1 | system user |           | NULL | Connect | 2143813 | Waiting for master to send event | NULL             |
|    2 | system user |           | NULL | Connect | 1448477 | Reading event from the relay log | NULL             |
| 1628 | root        | localhost | NULL | Query   |       0 | init                             | SHOW PROCESSLIST |

On MASTER side:

|  7947837 | repl       | host-vm:59420     | NULL          | Binlog Dump | 2143817 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |

On MASTER the show slave hosts:

+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|      3410 |      | 3410 |      6524 |
| 347643210 |      | 3306 |      6524 |
+-----------+------+------+-----------+

Does anyone has an idea what's going on and how can I fix it?


texts: What happens if I interrupt or cancel `pg_upgradecluster`?

texts: I'm upgrading a PostgreSQL 9.5 cluster to 9.6 using pg_upgradecluster. It's taking an awful lot of time, so I'd like to cancel the process, delete unneeded databases, and start again. What are the risks?


texts:
I have a many-to-many table that contains id's from 2 related tables.

For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK?

Or should I create the PK on both id columns?

What are the benefits of either method?

Here is the table design with the surrogate key (CodeGroupMappingID)

CREATE TABLE [dbo].[CodeGroupMappings](
    [CodeGroupMappingID] [int] IDENTITY(1,1) NOT NULL,
    [CodeID] [int] NOT NULL,
    [GroupID] [int] NOT NULL,
     CONSTRAINT [PK_CodeGroupMappings_CodeGroupMappingID] PRIMARY KEY CLUSTERED ([CodeGroupMappingID] ASC)
)


texts: Availability Group being PARTIALLY_HEALTHY

texts:
I'm having a strange issue with a SQL server hosting 11 Availabilty Groups.

All those groups have 1 or 2 databases in it and they are synchronous commit with the automatic failover.

I noticed recently that one of the database had a state "Syncronizing" (which is not expected for synchronous commit). When I looked at the AG health state, it was "PARTIALLY_HEALTHY".

I looked at the extended event and did not see anything interesting.

I tried the "Resume" command to fix it but it didn't work. I ended up changing my AG configuration from synchrounous commmit to Asyn and set it back to sync which does resolve my issue (DB went back to synchronized).

One day later, I had a look at the same server, to make sure everything was still fine and found out that another database (in another AG) was showing "Synchronizing"...

So their must be an issue with my server... I look at my databases and at the last_hardened_lsn, they are not sync (primary vs secondary).

The server is sleeping, no activity at all right now...

I try the alter database x set hadr resume again and it doesn't change anything.

Is there a limitation to the number of AG on an instance ?

Any idea where I should look at to find the explanation for those strange issue ?

Here's what's in the dm_hadr_database_replica_states dmv:

Thank you everyone !


texts: Difference between reorg-rebuild and index re-create in Sybase ASE

texts: Could someone please explain what the main differences are between reorg-rebuilds and index re-creation in Sybase ASE?


texts: How can I connect to a network MySQL server using ODBC?

texts:
Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC.

I need to connect to FOO in a MySQL client rather than SAS. Unfortunately for me, I'm not able to figure out how to do so using Oracle's MySQL Workbench, Microsoft SQL Server Management Studio (if even possible with Oracle rather than MS SQL), or SQL Workbench J using an Oracle driver.

The information required by Oracle's MySQL Workbench and others seems to go beyond the information that's available in Windows' ODBC Administrator profile for FOO (or any other ODBC DSN). The New Connection wizards request an IP address and port number, whereas the information I have on the ODBC connection is just:

DSN: FOO
User: Username
Password: Somepassword
Schema: Schemaname
Type/driver: Oracle
TNS Service Name: Somename

I downloaded and installed MySQL Connector/ODBC, but it seems that's mainly for creating ODBC connections rather than using them. I did figure out how to use ODBC to migrate a server in MySQL Workbench, but that's not what I want to do -- I just want to query the server.

How can I connect a SQL client (ideally MySQL Workbench, but I'm flexible) using only this ODBC connection/information?


texts: Maintaining mongodb for a huge list of message "documents"?

texts:
Intro:

I am working on an app right now for live messaging and it's almost complete. The database strategy is in order. So when a new message is created it is sent as an object fitting my mongoDB model, nice.

Theory:

I am no expert (obviously), but I have a few things in mind. One, somehow limit the list of documents to maybe 1,000 and export the rest somewhere (not a million dollar idea, but just brainstorming). That's really it lol!

Question:

So I am asking what is the best way to manage a huge list of documents, most at the tail end never being needed (just saved to go back on some conversations). Thinking about it most old messages when you scroll far up (skype, text, etc) disappear after awhile. So I am thinking my theory isn't so bad?

Is there a standard for handling a huge list? Should I just leave it alone, I feel like it would become hard to manage?


texts:
We are storing assets in an asset table, using auto Increment to assign an ID to an asset.

I have been asked what happens when an asset is replaced eg, water heater leaked, replaced with a new one.

I suggested that in the interface a button to replace the item, would create a new row, with some of the details copying over (like location) and on the old record having a replacedBy column that just lists the new ID. then when querying for all in service assets, use WHERE replacedBy IS NULL

but i was then told, they would like to keep the old ID number. i thought for a second of coping the record in entirety and "inverting" the id, eg 55 would become -55, essentially archiving it, then making changes on the original record. but then what happens if the new one is then replaced.

i could just copy the record to the next auto increment value and having a field to say it is replaced by the original but then i have an old record with a higher id than a newer record, something inside me just does not sit well with that (probably some ocd thing)

is there a way to auto decrement from 0, eg replaced items would be copied to the next negative value, so everything under 0 would be old data and everything over 0 would be current stuff?

there are a lot of asset tables so i would like to avoid having a table for each asset just to store replaced assets.


texts: SQL Server Agent Notification Email if a Step has an error, but the job is succesfull?

texts: Is it possible, that I can receive Email from Server Agent, if a step in an Agent has an error, but the Job is successful? In the history, I can see, that for example one Step has an Error, but all others were successful.


texts: Is there any performance difference in using a unique index on primary key?

texts:
I know there are lost of similar questions to this one, but none that really answers this:

Is there ANY difference in performance in using an unique index in a primary key, using a MyISAM table on a server running MySQL 5.5?

If you need more informations, I can provide all.
I just want to know if it is worth (lets say, if the 'seek' time decreases by more than 5 milliseconds) to do that change or if I'm committing a hideous crime.

And yes, this is a serious question.


texts: MySQL stored routine performance while using PREPARE

texts:
Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. All databases have same schema structure.

Stored routine body:

PROCEDURE `sp_name`(IN db_name  VARCHAR(50)
                    ,IN stardate DATE
                    ,IN enddate DATE)
......
SET @sql_query = CONCAT(
'SELECT
    .....
    .....
    .....
FROM ',
db_name, '.view
WHERE
    ....  stardate, .... enddate .....
GROUP BY
    ..... ,.....');

PREPARE sql_query FROM @sql_query;
EXECUTE sql_query;
DEALLOCATE PREPARE sql_query;

Now I am facing performance problems. While executing the stored routine from each database, execution time is less then 1 sec. With the prepared statement, it is 5-6 sec. Could someone please tell me if there is space for any improvements?


texts:
I am a developer but have to do DBA stuff now and then.

We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB.

So I tracked the issue to the file size, the Autogrowth was set to 10% for Log File

According to the Start Kit by BrentOzar, they recommend setting the file size to:

256 MB for Data File
128 MB for Log File

Which I have done.

Currently the DBs are in Simple Recovery Mode - Which I intend to change to Full Recovery Mode (and then take regular log backups)

But because the Log Files have grown so large, there is no space on the Hard Disk - I need to make up some space,

The Question is:

Is it okay for me to Shrink some of the log files while they are in Simple Recovery Mode? Because there is alot of free space in some of them

like this one has 99% free space

Another:


texts: Does MariaDB support ST_Distance_Sphere?

texts:
Running mariadb, I'm getting

  ERROR 1305 (42000): FUNCTION ST_Distance_Sphere does not exist

When I run a basic query like

SELECT ST_Distance_Sphere( POINT(0,0), POINT(0,1) );

However, I'm using a pretty new version.

Server version: 10.1.25-MariaDB-1 Ubuntu 17.10

What's going on here.


texts: select json_object() into list postgres function

texts:
Here i am trying to get all the select values in a varible.

 DECLARE
   ite json;
 BEGIN
   select INTO ite from json_object('{1}','{1}');
 RETURN ite;

when i call this function i got the result null.


texts: Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle

texts:
I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as platform. They had this server crashing once in a while, so they increased the resources but they still have high CPU usage. From my understanding, high CPU usage itself does not imply an issue, but when I drilled down a bit, it got more interesting:

There was a query which had a significant amount of elapsed time per each execution. As they used Oracle Standard edition, I was unable to use the benefits of AWR but, as I checked the query, I saw something very strange.

The query is as follows, which uses function foo:

update LIST_JOURNAL
   set STATUS       = '4',
       END_DATE     = :b1,
       END_TIME     = :b2,
       END_TYPE     = '1',
       USER_ID  = 'SYSTEM',
       ELAPSED_TIME = FOO_FUNCTION(START_DATE, START_TIME, :b1, :b2)
 where (((TERM_ID = :b5 and NODE_CD = :b6) and GROUP_CD = :b7) and
       STATUS < '4');

They used only CHAR, VARCHAR2 and NUMBER as their datatypes (don't know the reason, maybe due to some migration from another database) and there is this FOO_FUNCTION in this query that calculates the interval between two timestamps, say one of them is a record in database and one of them is sysdate, (date and time are stored with CHAR and NUMBER datatypes, not DATE or TIMESTAMP) the function converts sysdate to string using TO_CHAR, then concatenate with TIME field, then do a TO_DATE, then subtract and then multiply to seconds in a day (60*60*24) and return the results. (For contract reasons, I cannot disclose the function code)

The function looks very strange to me and I want to flag it as a very important CPU bottleneck my report for database performance boost. I haven't seen the rest of the functions but I'm pretty sure wherever DATE is needed, something similar happens.

But I'm a bit reluctant to do so, as I've not seen any evidence in Oracle docs or somewhere else that this might be a problem. (I know, the function is so oddly written that my reluctance looks ridiculous) Also there is a good chance that software vendor does not accept this as a change.

My questions are :

Is the above function can be a CPU intense one? Can refactoring and using DATE and TIMESTAMP data types help boost the performance? Why? I can deduct some reasons, but as I need to present this, if there are some documents that have discussed the issue, I would be most grateful to see the links.
The table itself does not have any indexes - except for primary key - If we are stuck with the current code and situation, can an index help boosting the performance? How effective and sustainable can that be? I know I cannot expect detailed answers, but the table contains about 3.7M rows and I think for an update query, some indexes can help.

And my general questions:

Is there a performance downside for using CHAR and NUMBER instead of DATE and TIMESTAMP?
What would you recommend to my client?

Update:

I finally got the results. We added the following composite index :

create index I_LIST_JOURNAL ON LIST_JOURNAL (TERM_ID, NODE_CD, STATUS);

We instantly got some good results, CPU got down to 80 percent and execution time of a couple of queries decreased substantially (The query in question had average execution time of 15 million seconds, now it's about 2400 seconds average) but again, after a couple of days, it went up to 99 percent again..
I am looking to do another iteration of stat gathering and explain plan. But I'm not sure whether this helps reducing the load or not. I am thinking of adding a couple of other composite or simple indexes on the tables which have long running queries to eliminate "Table access full" in explain plan, but given the situation above and the tools I have, is there any other method I can use to get better results?


texts: Transactional Replication: Maintaining schema changes

texts:
Whats the best approach to not to break replication but still apply schema changes to Primary and Secondary ? (not just DDL, new articles etc)

We have a huge database, so we had to go with replication using a back-up. The process is time consuming (2-3 hours). Each release we have tons of schema changes, as we are in active dev mode, we don't want to waste 2-3 hours to redo the process all over again and again.

Any suggestions or ideas ?


texts: SQL Server 2008 R2 on production -- is it worth to upgrade to SP1/SP2

texts:
One of my company's mission-critical database is running SQL Server 2008 R2. SELECT serverproperty('ProductVersion') returns 10.50.1600.1. So, yeah, RTM.

Now, long story short, this database is not yet migrated to our SAN Storage, so we can't have easy snapshot of the system. If we want to make a reversible change, we'll have to backup the database's instance willy-nilly. And of course, this might need some downtime/service interruption, and is thus a hassle.

Currently there seems to be no significant problems, except some slowdowns which we attribute to suboptimal queries. And our Dev/DBA sorely misses OPTION (RECOMPILE), which Microsoft took out from SQL Server 2008 R2 RTM.

So, I would like to know:

Upgrading the server to SP1 or SP2: will it be worth the hassle?
I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM.
Should we decide to apply SP1/SP2, will there be critical hotfixes to apply afterwards?
(E.g., something similar to the regression with SQL Server 2012 SP2 could lead to data loss).

Thanks for any help/explanation.


texts:
We use oracle 10g. We found the following error in a trace file:

   *** 2012-05-07 11:50:56.421
   ksedmp: internal or fatal error
   ORA-07445: An unexpected error: core dump [ACCESS_VIOLATION] [_evaopn2+153] [PC:0x1BBDFA9] [ADDR:0x0] [UNABLE_TO_READ] []
   Current SQL statement for this session:

We took a lot of time to find the cause of this problem but we've been unsuccessful. We were using 9i and it did not have this problem. It only showed up after we recently upgraded to 10g.

What is the problem? How can I resolve it?


texts:
I'm reading through the 98-364 exam book and there's a question I can't quite answer:

  "What command would you use to remove all records from the Contact
  table that do not have a country of USA and at the same time free the
  space used by those records?"

If "that do not have a country of USA" wasn't in there, it would simply be "The Truncate Command" as it would wipe out all data in that table, and free the space.
To my knowledge the delete command doesn't free the space, and I can't find a command that both deletes and frees the space while target specific records.

Does the book have a typo here? Or is there a command anyone knows of?

Note: It's "What command...", not commands.

Edit: (Stuart) There's been a lot of useful answers here, and it seems to me that the book is somewhat ambiguous. Outside of the 'Which Command would you use", and in the event this comes up on the exam, what would the "expected" answer be?
I would assume they are looking for "The delete command with where clause". Simply because they only suggest three methods in the book up to this point (Delete/Truncate/Drop).


texts: How can I clear buffer for a SQL Server DB Hosted On Azure?

texts:
I am trying to fine tune my queries and Stored Procedures. The Issue that I am facing is that there is no way to clear the Database buffers in Azure. Which means that I cannot use DBCC DROPCLEANBUFFERS on Azure.

Is there some workaround by which I can clean the buffers so that I can test the Stored procedures' actual time? I was thinking of reduing the Page Life expectancy (though I am not sure if I can actually do it).

I tried changing the tier's of the DB as it was mentioned in one article that it might clear the buffer, but it didn't have any effect.

I need this as the local machines (I5 with 8 GB ram) are too fast and the query which takes a second to execute on the local machine takes around 30 - 40 seconds on an Azure S2(20 DTU) SQL DB.


texts: Why can't SQL Server optimizer use foreign key

texts:
I have the following tables(See below for complete reproducible SQL):

ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)

I have the following query:

SELECT *
FROM
ToBeTransferredTemp a
INNER JOIN codes c on a.type = c.idstring

There is an index on ToBeTransferredTemp.type and unique index on CODES.idstring.
There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

You would think SQL Server would gather from this, that the join will produce the same number of rows as is present in ToBeTransferredTemp. But it doesn't.
There are 8500 rows in ToBeTransferredTemp, and the estimated number of rows after the join is 450, and "actual number of rows" is, not surprisingly, 8500 rows.

Any idea why SQL Server misses so terribly? I've checked that the foreign key constraint is trusted by SQL Server.

Could it be that SQL Server 2017 don't take foreign key and unique constraints into account when estimating cardinality of joins?

I know I could fix this easily by just doing a LEFT OUTER JOIN, but it feels so clumsy. I'm really interested to know why SQL Server can't figure this one out.

EDIT: queryplan:
https://www.brentozar.com/pastetheplan/?id=ByL54PtNB

EDIT: SQL needed to reproduce. Note that you might need to re-run the query a few times. Sometimes the values form statistics that make it seem to work

CREATE TABLE dbo.TEST_CODES
(
    key1 varchar(60) NOT NULL,
    key2 varchar(60) NOT NULL,
    key3 datetime NOT NULL,
    Filler4 varchar(100),
    IDSTRING varchar(100)
);
ALTER TABLE [dbo].[TEST_CODES] ADD  CONSTRAINT [PK_CODES_TEST] PRIMARY KEY CLUSTERED
(
    key1 ASC,
    key2 ASC,
    key3 ASC
)

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.TEST_Codes (IDSTRING);

CREATE TABLE dbo.TEST_ToBeTransferredTemp
(
    ID varchar(100) NOT NULL,
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp_TEST PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type])
        REFERENCES dbo.TEST_CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.TEST_ToBeTransferredTemp ([type]);

INSERT INTO dbo.TEST_CODES
SELECT TOP (18139)
    CAST(NEWID() AS varchar(60)),
    CAST(NEWID() AS varchar(60)),
    GETDATE(),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100))
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.TEST_ToBeTransferredTemp
SELECT TOP (8002)
    CAST(NEWID() AS varchar(100)),
    (SELECT TOP 1 IDSTRING FROM TEST_CODES order by newid()),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
     FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

SELECT *
FROM
TEST_ToBeTransferredTemp a
inner join TEST_codes c on a.type = c.idstring


texts: How to add additional tables/fields into a query with multiple inner joins?

texts:
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?

SELECT desired_categories.*
FROM
    ( SELECT nid, title, COUNT(1) AS all_categories
        FROM node
        GROUP BY nid
    ) AS A

INNER JOIN
    ( SELECT nid, COUNT(1) AS good_categories
        FROM node
    INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
        WHERE category.dir_phys_category_tid
        IN (498,499,500)
        GROUP BY nid
    ) AS B USING (nid)

INNER JOIN
    ( SELECT DISTINCT nid, title
        FROM node
    ) AS desired_categories USING (nid)

WHERE all_categories = good_categories;

I have about 5 fields from 5 other tables I need access to.  All tables use the node id nid to indicate their relationship.  I suspect it's possible to simply add a join in for each table but I haven't had any luck yet.


texts:
where clause not show expected result.

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) )
        OR( (I.PracticeId=0 OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

I want show result when

  @practiceid=1000

than only 1000 practices show and when

  practiceid <>1000

than show practiceid in (0,@practiceid)

but when i pass @practiceid=1000 than its also get 0 practiceid


texts:
Is it enough to have the entire index in memory/ram or does mongodb even try to allocate as much ram as possible to store even the data for fast reads?

I'd like to run mongodb + other applications and it looks like mongodb is the only one which does not allow me to define a range of RAM, to lets say "max_memory_allocated_or_reserved=8GB".

If there is no way to do so, I should explain to oom-killer that mongod is the "bad" process which is not best practise in my opinion...


texts: SQL Server - DELETE from subquery/derived table

texts:
Is there a way I can turn the below SELECT statement into a DELETE?

I would like to delete the corresponding returned records from the [ETL].[Stage_Claims] table.

Since I used derived tables I can't reference the Stage_Claims table.

To summarize, the 2 physical tables used in the below query have identical structures. Only difference is DUPS_Claims is a subset of Stage_Claims.

DUPS_Claims contains duplicate records found in Stage_Claims. If a record exists 3 times in Stage_Claims, we will have that record 3 times in DUPS_Claims as well.

Stage_Claims contains all records including the duplicate records in DUPS_Claims.

I would like to remove the duplicate records from Stage_Claims leaving only 1 unique record for every duplicated record.

Stage_Claims has just short of 1 million rows so I do not want to use Row_Number / Partition on the entire table as it takes over 2 minutes to run.

The below query I have runs in about 15 seconds and successfully identifies only the duplicate records  (not including the original unique record we want to keep) but I have not been able to figure out how to delete the records that are returned from SC.

Is it possible or should I just take a different approach?

SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1


texts:
I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan".

What exactly does "Loose Index Scan" mean?


texts: Count the number of occurences in a query result

texts:
I am running the below query on my sql server 2012 database.

select Appointment.AppointmentDate, Appointment.AppointmentTime,   Auctioneer.AuctioneerName, Auctioneer.AuctioneerSurname, Buyer.BuyerName,   Buyer.BuyerSurname
from Appointment
inner join Auctioneer on Appointment.AuctioneerId=Auctioneer.AuctioneerId
inner join Buyer on Appointment.BuyerId=Buyer.BuyerId
where AppointmentDate between '2017-01-01' and '2017-12-31'

The Result of this is the below:

+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| AppointmentDate | AppointmentTime | AuctioneerName | AuctioneerSurname | BuyerName | BuyerSurname   |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| 2017-10-23      | 13:00:00        | Mary           | Borg              | David     | Borg           |
| 2017-10-24      | 15:30:00        | Mary           | Borg              | Joseph    | Sammut         |
| 2017-11-03      | 09:30:00        | Joseph         | Smith             | Mark      | Psaila         |
| 2017-11-03      | 10:45:00        | Joseph         | Smith             | David     | Borg           |
| 2017-11-15      | 10:10:00        | Mary           | Borg              | David     | Borg           |
| 2017-08-02      | 08:30:00        | Daisy          | Webb              | Josephine | Grima          |
| 2017-04-15      | 14:00:00        | Sam            | King              | Mary      | Santucci       |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+

I need to add a column which counts the number of occurrences of the 'AuctioneerName' & AuctioneeSurname' in the result.

Example:  Mary Borg   Count 3, Joseph Smith Count 2 etc...


texts: Azure SQL DB migration from Visual Studio raises collation error

texts:
I want to run a migration in my Azure hosted database. The migration works perfect in several local machines. When I try targeting the Azure database it raises the following exception:

  Column X is not of same collation as referencing column Y in foreign
  key Z.

I´m trying to run the migration from VS 2015 package manager console as I do with the development machines.

Is this the correct approach to run migrations in the Azure DB? I´m missing something?

I did some research and got that Azure DB collation is SQL_Latin1_General_CP1_CI_AS and Local machine´s DB is Modern_Spanish_CI_AS

I don't want to move data from one database to the other so this is not the real problem. I think the problem came from Visual Studio because it is applying the Modern_Spanish_CI_AS to all the SQL scripts that is running.

How can I change this option?

I´m working with asp.net mvc 5 and EntityFramework code-first


texts: Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

texts:
my.cnf (5.5 and 5.1 is the same) :

back_log=200
max_connections=512
max_connect_errors=999999
key_buffer=512M
max_allowed_packet=8M
table_cache=512
sort_buffer=8M
read_buffer_size=8M
thread_cache=8
thread_concurrency=4
myisam_sort_buffer_size=128M
interactive_timeout=28800
wait_timeout=7200

mysql 5.5:

..mysql5.5/bin/mysqlslap -a --concurrency=10 --number-of-queries 5000
      --iterations=5 -S /tmp/mysql_5.5.sock --engine=innodb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 15.156 seconds
        Minimum number of seconds to run all queries: 15.031 seconds
        Maximum number of seconds to run all queries: 15.296 seconds
        Number of clients running queries: 10
        Average number of queries per client: 500

mysql5.1:

..mysql5.5/bin/mysqlslap -a --concurrency=10 --number-of-queries 5000
      --iterations=5 -S /tmp/mysql_5.1.sock --engine=innodb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 13.252 seconds
        Minimum number of seconds to run all queries: 13.019 seconds
        Maximum number of seconds to run all queries: 13.480 seconds
        Number of clients running queries: 10
        Average number of queries per client: 500

Why mysql 5.5 slower than 5.1 ?

BTW:I'm tried mysql5.5/bin/mysqlslap and mysql5.1/bin/mysqlslap,result is the same


texts: Select images where ALL images are greater than pixel width

texts:
I will try to explain this clearly.

I have a table of 200,000 records that stores pixel sizes for our product images. A variety of image sizes have been saved over 20 years, and products do not necessarily have images in all folders.

I am trying to find out how many products have all of their images over 800 pixels wide to inform design decisions for a new website.

The fields are:

ID           (unique reference of product)
ImgNo         (id of each image under product: 1 to 36)
CDN_W         (width of image on CDN storage)
LG_W          (width of image in LARGE folder)
LGR_W         (width of image in LARGER folder)

Data looks like this:

ID 123
ImgNo 1
CDN_W 800
LG_W 400
LGR_W 1280

ID 123
ImgNo 2
CDN_W 600
LG_W 600
LGR_W 1280

ID ABC
ImgNo 1
CDN_W null
LG_W 800
LGR_W null

etc...

THE TASK

I need to query the data to find all products where ALL of the images in a particular folder are over 800 pixels wide. There are 3 folders, any of which could provide the best set of images over 800 pixels. I only want products where all images are over 800 - if there are 5 images and 1 is below 800 then that product should not be valid.

Ideally I'd like to identify the TOTAL number of products that are valid, and separately the actual list of products IDs.

Is there a way to do this in MS SQL?


texts: Sybase ASE 15.5 : Create a custom function for partitionning

texts:
I want to create a partitioned table to store messages.

If the message belongs to "an even week number" => go to the partEven partition !
If the message belongs to "an odd week number" => go to the partOdd partition !

Here is my broken code :

CREATE TABLE myTable (
    id NUMERIC(18,0) IDENTITY,
    message_date_time DATETIME,
    PRIMARY KEY (id)
    )partition by list (message_date_time)
        (partEven datepart(week, message_date_time) % 2 == 0,
         partOdd datepart(week, message_date_time) % 2 == 1
        )

Could someone help me with the syntax ? (if it's possible this way)

It seems that I'm only allowed to route records on the "message_date_time" constant but I want to do it on the result of a function applied to "message_date_time".

Please help :)


texts: MySQL SUM with multiple GROUP BY on same table

texts:
What is the best way to produce several sum results based on different group by conditions on the same table in MySQL?

I believe I'm doing a bad select, but I can't figure out a better way.

Imagine a have a orders table where I track the client that made the order, it's value and some qualifiers for the order (the types). I want to sum the total orders for a given client grouped by a combination of the order types:

delimiter $$

CREATE TABLE `orders` (
  `idorder` int(11) NOT NULL,
  `value` double DEFAULT NULL,
  `idclient` int(11) DEFAULT NULL,
  `type1` int(11) DEFAULT NULL,
  `type2` int(11) DEFAULT NULL,
  `type3` bit(1) DEFAULT NULL,
  PRIMARY KEY (`idorder`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

My select:

SELECT T0.idclient, T1.condition1, T2.condition2, T3.condition3 FROM
(SELECT 1 AS idclient) AS T0
LEFT JOIN
(SELECT idclient, SUM(value) condition1 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 0) AS T1
ON T0.idclient = T1.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition2 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 1 AND type3 = 1) AS T2
ON T0.idclient = T2.idclient
LEFT JOIN
(SELECT idclient, SUM(value) condition3 FROM `test`.`orders` WHERE idclient = 1 AND type1 = 1 AND type2 = 0 AND type3 = 0) AS T3
ON T0.idclient = T3.idclient;

Am I missing something?

Output:

+----------+------------+------------+------------+
| idclient | condition1 | condition2 | condition3 |
+----------+------------+------------+------------+
|        1 |         10 |         20 |         30 |
+----------+------------+------------+------------+
1 row in set (0.00 sec)

Data:

INSERT INTO `orders` VALUES (1,5,1,1,1,'\0'),(2,5,1,1,1,'\0'),(3,20,1,1,1,''),(4,5,1,1,0,'\0'),(5,25,1,1,0,'\0');


texts: When to use lateral joins vs correlated subqueries in Postgres?

texts:
These two queries:

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts, (
                SELECT ts AS ts_next
                FROM switches
                WHERE username = a.username
                AND ts > a.ts
                ORDER BY ts ASC LIMIT 1
        )
        FROM switches a
        WHERE task IS NOT NULL
) c
ORDER BY username, ts
;

and

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts
        FROM switches
        WHERE task IS NOT NULL
) a
LEFT JOIN LATERAL (
        SELECT ts AS ts_next
        FROM switches
        WHERE username = a.username
        AND ts > a.ts
        ORDER BY ts ASC LIMIT 1
) b
ON true
ORDER BY username, ts
;

give the same results.

When and why should I use one form over the other?

(The switches table is from a time/task tracking service, where only task switches are recorded, but task durations need to be calculated.)

The query plans are different, is one better than the other?

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Sort  (cost=27.96..28.01 rows=19 width=37)
   Sort Key: a.username, a.ts
   ->  Seq Scan on switches a  (cost=0.00..27.55 rows=19 width=37)
         Filter: (task IS NOT NULL)
         SubPlan 1
           ->  Limit  (cost=1.38..1.38 rows=1 width=8)
                 ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                       Sort Key: switches.ts
                       ->  Seq Scan on switches  (cost=0.00..1.36 rows=4 width=8)
                             Filter: ((ts > a.ts) AND ((username)::text = (a.username)::text))
(10 rows)

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=28.34..28.39 rows=19 width=45)
   Sort Key: switches.username, switches.ts
   ->  Nested Loop Left Join  (cost=1.38..27.93 rows=19 width=45)
         ->  Seq Scan on switches  (cost=0.00..1.24 rows=19 width=37)
               Filter: (task IS NOT NULL)
         ->  Limit  (cost=1.38..1.38 rows=1 width=8)
               ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                     Sort Key: switches_1.ts
                     ->  Seq Scan on switches switches_1  (cost=0.00..1.36 rows=4 width=8)
                           Filter: ((ts > switches.ts) AND ((username)::text = (switches.username)::text))
(10 rows)


texts:
I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the running total not including the current row?

I assume you would need to use the ROW or RANGE argument. I know there is a CURRENT ROW option but I would need CURRENT ROW - 1, which is invalid syntax. My knowledge of the ROW and RANGE arguments is limited so any help would be gratefully received.

I know that there are many solutions to this problem, but I am looking to understand the ROW, RANGE arguments and I assume the problem can be cracked with these. I have included one possible way to calculate the previous running total but I wonder if there is a better way:

USE AdventureWorks2012

SELECT s.SalesOrderID
    , s.SalesOrderDetailID
    , s.OrderQty
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID) AS RunningTotal
    , SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
                         ORDER BY SalesOrderDetailID) - s.OrderQty AS PreviousRunningTotal
    -- Sudo code - I know this does not work
    --, SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID
    --                   ORDER BY SalesOrderDetailID
    --                   ROWS BETWEEN UNBOUNDED PRECEDING
    --                                   AND CURRENT ROW - 1)
    -- AS  SudoCodePreviousRunningTotal
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID
    , s.SalesOrderDetailID
    , s.OrderQty


texts: index being used during equality operator but not comparison operator

texts:
Using a btree as my index. From what I understand(http://www.postgresql.org/docs/9.0/static/indexes-types.html), btree works for comparison operators. For equality operator it works fine

explain analyze select latitude, longitude
from internal.location_log
WHERE entry_timestamp = 32903023;

Index Scan using internal_location_log_entry_timestamp_idx on location_log  (cost=0.42..8.44 rows=1 width=16) (actual time=0.063..0.063 rows=0 loops=1)
   Index Cond: (entry_timestamp = 32903023)
 Planning time: 0.104 ms
 Execution time: 0.088 ms

however for comparison operator like

explain analyze select latitude, longitude
from internal.location_log
WHERE entry_timestamp >= 32903023;

Seq Scan on location_log  (cost=0.00..17969.96 rows=821837 width=16) (actual time=0.012..347.941 rows=821957 loops=1)
   Filter: (entry_timestamp >= 32903023)
 Planning time: 0.209 ms
 Execution time: 537.699 ms

Am I missing something?


texts: oracle - does this have the potential to cause a lock?

texts:
Note how no commit was done in between each statement. Could this cause a table lock or blocked session?

delete from tableblah;
insert into tableblah
(select * from othertable);


texts: Proper way to handle NULL (empty) values for user-defined table types passed as params to stored procedure

texts:
My stored procedures accepts table value parameters for querying data.

Usually those are custom user-defined table types for where clause. Business logic of stored procedures push me to these @tables be optional.

Sometimes there is data inside @table, sometimes there is not.

Here is way how I handle these

select
     ... etc
from tebel ...
    ...etc
where
     (table1.id in (select id from @UserDefinedTableTypeParam ) or (select count(*) from @UserDefinedTableTypeParam ) = 0)
     and (table2.id = @OtherIntParam or @OtherIntParam = 0)

I see that my code is ugly but I do not know is there better way to avoid  using count(*) inside subquery of where clause ?

My focus is to keep performance of my T-SQL code at respectable level.
Does this @UserDefinedTableTypeParam confusing SQL execution plans and the query optimizer?


texts: Detached DB showing up in back-up history

texts:
I found a script to show me the databases that are not backed-up in the last 24 hours: Script to retrieve SQL Server database backup history and no backups

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date),
GETDATE()) AS  [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING
(MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24,     GETDATE()))
 UNION
 SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
 master.dbo.sysdatabases.NAME AS database_name,
 NULL AS [Last Data Backup Date],
 9999 AS [Backup Age (Hours)]
 FROM
 master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
 ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
 WHERE msdb.dbo.backupset.database_name IS NULL
 AND  master.dbo.sysdatabases.name <> 'tempdb'
 ORDER BY
 msdb.dbo.backupset.database_name

The result shows me the DB that no logner exists. These are the DB that i have deleted/detached a while back. Why are these showing up?


texts: How to find the query that is still holding a lock?

texts:
Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row.

For each lock acquired, is there any way to determine which SQL statement (delete, insert, update or select) caused that lock?

I know that the most_recent_query_handle column of the sys.dm_exec_connections DMV gives us the text of the last query executed, but several times other queries ran before under the same session (SPID) and are still holding locks.

I already use the sp_whoisactive procedure (from Adam Machanic) and it only shows the query that is on the input buffer at the moment (think DBCC INPUTBUFFER @spid), which not always (and in my case usually never) is the query that acquired the lock.

For example:

open transaction/session
exec a statement (that holds a lock on a resource)
exec another statement on the same session
open another transaction/session and try to modify the resource locked at step 2.

The sp_whoisactive procedure will point out the statement at step 3, which is not the responsible for the lock, and thus not useful.

This question came from doing an analysis using the Blocked Process Reports feature, to find the root cause of blocking scenarios in production. Each transaction runs several queries, and most of time the last one (that is shown on input buffer at BPR) is rarely the one holding the lock.

I have a follow-up question: Framework to effectively identify blocking queries


texts: Service Broker - A previously existing connection with the same peer was detected during connection handshake.

texts:
There are good questions about service broker here, here and here.

In none of those questions I see the following error message:

  Service Broker login attempt by user 'MYCOMPANY_USA\sqlservice.'
  failed with error: 'A previously existing connection with the same
  peer was detected during connection handshake. This connection lost
  the arbitration and it will be closed. All traffic will be redirected
  to the previously existing connection. This is an informational
  message only. No user action is required. State 80.'.  [SERVER:
  10.31.8.216]

But on this question there is a very similar message, but no answer.

I got this message by running the following script:

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'error_reported'
)

SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp],
       data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number],
       data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message]
FROM cte_HADR
WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 28048
order by data.value('(/event/@timestamp)[1]','datetime') DESC

What could be possible things to look at?

we do have more than one service broker sessions


texts: ALTER TABLE ... ADD COLUMN ... NOT NULL is not instant in PostgreSQL 11?

texts:
I have read the documentation and it states that adding a column with a non-null and non-volatile default value should not require a table rewrite. However, this does require a rewrite and blocks all SELECTs:

ALTER TABLE sales ADD COLUMN bar DECIMAL(18, 4) NOT NULL DEFAULT 0;

On a 300gb table this will block for several hours as it's rewritten. Am I missing something?

postgres=> select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

I'm not sure if it makes a difference, but it's an AWS RDS instance.


texts: How to Extract SubString between two positions

texts:
I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains the following line:
Delayed ${shipmentId} for Carrier

Where ${shipmentId} represents the specific Id that was generated for that particular record. Example: 12345
Basically I want to query the column in the table for all the shipmentId values, which unfortunately will involve getting the string positions between Delayed and  for Carrier. I understand this will likely involve using one of Postgres' regular expression functions but I'm not quite sure how to do it, or if there's something a little simpler.


texts: Are LOBs also backed up if I take dump of all partitions of table individually?

texts: I am taking export of a large table in oracle through datapump. I am taking export of all partitions one by one. The table has a LOB column as well. Are LOBs also backed up with partitions? Because partitions seem to be very small as compared to LOBs


texts: Need a function to run different queries based on parameters sent subquery must return one column error

texts:
I am new to advanced sql programming. I am storing constants.
Constants have three possible scopes: local, user and global.

Two tables:

s0constants_user holds set values of constants

s0constants holds the constant definitions and values.

 CREATE TABLE public.s0constants_user (
constant_name bpchar(15) NOT NULL,
empfkey int2 NOT NULL DEFAULT 0,
constant_value bpchar(255) NULL,
locality_name bpchar(30) NOT NULL DEFAULT 1,
CONSTRAINT s0constants_user_pk PRIMARY KEY (constant_name, empfkey, locality_name)

);

   CREATE TABLE public.s0constants (
   constant_name bpchar(15) NOT NULL,
   constant_value bpchar(255) NULL,
   constant_data_type bpchar(1) NULL,
   cttype int4 NULL,
   displayname bpchar(30) NULL,
   actiontype int4 NULL,
   note text NULL,
   const_scopefk int4 NOT NULL DEFAULT 3, -- 1=local 2- user 3=global
   CONSTRAINT "S0CONSTANTS_pkey" PRIMARY KEY (constant_name),
   CONSTRAINT scope_fk FOREIGN KEY (const_scopefk) REFERENCES
   s0constants_scopelk(id)

I realize I could do this with separate tables for each scope but I'd like to make this work even if it is just a learning exercise.

I need a function to return and constant's value and data type given the constant name and empfkey.  There are different queries for each scope of constant.

local - constant_name,
empfkey,
locality_name user - constant_name,
empfkey global - constant_name

Constant names are unique over the three scopes.  I thought a case statement might do it but I'm having trouble limiting it to one row of data and how to set up the sub-queries.
This is my first(Edit SECOND) pass at this but now I have issues with "subquery must return only one column"

    FUNCTION public.const_get( stname text, empid INT, locality text ) returns TABLE(cvalue text, ctype text)
    LANGUAGE sql
     STABLE PARALLEL SAFE
AS $$

   select c.const_scopefk, c.constant_data_type
     ,case
       when const_scopefk = 1 then --local
       ( select distinct scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
           and scu.empfkey = empid
           and scu.locality_name = locality)
       limit 1)

       when const_scopefk  = 2 then --user
        (select scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
           and scu.empfkey = empid
           and scu.locality_name = 1)
           limit 1)

      when const_scopefk  = 1 then --global
        (select scu.constant_value, c.constant_data_type
        from s0constants_user scu

        where (scu.constant_name = constant_name
         and scu.empfkey = 0
        and scu.locality_name = 1)
        limit 1 )
    end
   from public.s0constants c
   where  c.constant_name = UPPER(constname);

$$

EDIT: I've updated table definitions


texts:
For one of my servers with below configuration:

  Total RAM: 32 GB

  CPU: 8

  Max Server Mem: 22.5 GB

  Min Server:: 4 GB

  Edition: Enterprise Edition with 2k8R2 installed on SP3

  Page file: System Managed on the C:\

  AWE Enabled: NO as it being 64 Bit

I am continuously getting the alerts for OS paging High..

When I checked using RAMMAP Tool for memory utilization, I found that there is AWE which using an approx of 23.8 GB and thus blocking any further memory usage:

More over for the user right assignments : "Locked pages in Memory" has not been assigned any rights!

Kindly help me understanding where more I need to investigate, or a solution for this

Thanks!


texts: Using GROUP BY WITH ROLLUP on column that contains NULL

texts:
I have the following table (see it on SQL Fiddle) (I created to break down my problem):

| ID | word    |
----------------
| 5  | "Hello" |
| 6  |  NULL   |
| 7  | "World" |
| 8  | "World" |

Now I want to count the number of occurrences of each word using GROUP BY word WITH ROLLUP. The NULL in the column word of the row generated by the ROLLUP should be replaced by "total":

SELECT
  ID,
  ifnull(word, "total") as word,
  count(*) as occurrences
FROM test
GROUP BY word WITH ROLLUP;

The problem is that it also replaces the NULL in the record with the amount of rows where words is NULL:

| ID |  word | occurrences |
|----|-------|-------------|
|  6 | total |           1 | <- Here lies the problem
|  5 | Hello |           1 |
|  7 | world |           2 |
|  7 | total |           4 |

So I now use count(word) to distinguish whether it's a NULL from my data or it's a NULL created by ROLLUP so I can replace it with either "empty" or "total":

SELECT
  ID,
  if(count(word) = 0, "empty", ifnull(word, "total")) as word,
  count(*) as occurrences
FROM test
group by word with ROLLUP;

This works for my use case but it still has a flaw:
If word is NULL in all rows, count(word) is also 0 in the final row showing the total: (SQL Fiddle with different data (only record#6))

| ID |  word | occurrences |
|----|-------|-------------|
|  6 | empty |           1 |
|  6 | empty |           1 |  <- word should be "total"

The following statement delivers the result I want:

SELECT
  ID,
  ifnull(word, "total") as word,
  count(*) as occurrences
FROM
(
SELECT
  ID,
  ifnull(word, "empty") as word
FROM test
) tmp
GROUP BY word WITH ROLLUP

But since in most answers around here the use of subqueries seems to be discouraged, I am wondering if there is a better solution.


texts: Executing a script via Powershell possibility

texts: Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it from Powershell by calling it? What syntax would I use for myquery.txt to run? Put another way, what's a way to end-run not being able to use a stored procedure on a query that should be made into one?


texts: RDS crosstab: ERROR: permission denied for language c

texts:
We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only superuser can create functions on RDS and my user can't be granted as superuser.

CREATE FUNCTION crosstab50(text, OUT rownumber integer, OUT c1 text,...)
    RETURNS SETOF record
    LANGUAGE c
    STABLE STRICT
    AS '$libdir/tablefunc', 'crosstab';

But we're getting this error during our restore operation:

ERROR:  permission denied for language c


texts: Understanding how sys.dm_exec_requests represents queries being rolled back

texts:
I'm not sure whether this was an actual problem or just a normal situation where I needed to wait for rollback to finish. In either case, some insight would be appreciated.

Background details

I manage a server running SQL Server 2008 R2 SP2 that has a fairly large table:

842 million rows at ~108 bytes each
Clustered index (85 GB)
1 nonclustered index (8 GB)
Page compression applied to both indexes

Admittedly, table design and change control in my current working environment are not good. Someone ran the following batch to convert 9 decimal columns to numeric (18,6) NULL:

ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col1] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col2] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col3] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col4] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col5] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col6] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col7] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col8] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col9] NUMERIC(18,6) NULL

I think the purpose of the changes was purely for standardisation of data types across tables.

This ran for close to 24 hours and got partway through modifying Col4, at which point I killed it.

What happened after I killed the query

Four hours after killing the query, the row for the session in sys.dm_exec_requests showed a status of 'running', a command of 'KILLED/ROLLBACK' and a value of 0 for percent_complete.

I could tell the session was doing something because reads, writes, and CPU usage were still accumulating at a constant rate. The query was not blocked or waiting. There was little other activity on the server and I/O associated with this query was completing in <1ms according to Process Monitor.

Why what sys.dm_exec_requests showed seemed odd to me

This is confusing given that if I look at a killed query on another 2008 R2 server, sys.dm_exec_requests shows the session in a status of 'rollback', a command of 'AWAITING COMMAND', and a number reported for percent_complete.

The only difference I can find between these servers is that the former is 2008 R2 SP2 and the latter is 2008 R2 SP2 CU8.

The actual question!

What accounts for the difference in the way these rollbacks are reported in sys.dm_exec_requests?  For example, does a status of 'running' with a command of 'KILLED/ROLLBACK' indicate that the KILL command is still being processed, whereas a status of 'rollback' indicates that the query has progressed to performing the actual rollback?

The query finally rolled back, by the way. I never saw the status transition to rollback or a percent_complete reported even though I'm capturing sp_whoisactive output every 5 seconds.


texts: MongoDB, Getting Started and Connecting with Compass

texts:
I'm trying to connect to my mongoDB running as a service on my rPi3B+, using openSuse Tumbleweed 64bit, mongo version 3.6.2, from my laptop sharing the same LAN/Wifi (pi on LAN & laptop on Wifi).

I completed this install guide(https://www.osradar.com/install-mongodb-opensuse-15/) directly after a fresh install of the rPi3 JeOS image provided here: https://en.opensuse.org/HCL:Raspberry_Pi3

Now when I tried to connect to the db service using compass using the same IP I used to connect to the Pi over ssh, and the standard port of 27017. It then reports "MongoDB not running on the provided host and port".

However, I have the shell running on the Pi at the same time, and when I exit the shell so that compass is the only client attempting a connection. When I run sudo systemctl status mongodb the service is active.

When I run netstat -lntu I get the following output:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:27017         0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN
tcp6       0      0 ::1:27017               :::*                    LISTEN
udp        0      0 192.168.1.163:123       0.0.0.0:*
udp        0      0 127.0.0.1:123           0.0.0.0:*
udp        0      0 0.0.0.0:123             0.0.0.0:*
udp        0      0 0.0.0.0:68              0.0.0.0:*
udp6       0      0 fe80::ba27:ebff:feb:123 :::*
udp6       0      0 ::1:123                 :::*
udp6       0      0 :::123                  :::*

When I ping the Pi's IP address using the local IP I get the expected returns showing that it is reachable.

Are there some settings I need to change from within the shell to make it reachable by Compass?


texts: Date conversion and integer truncation during import

texts:
I am doing some data file imports using:
LOAD DATA
INFILE 'apa.txt'
INTO TABLE Test_Stock_Daily
FIELDS TERMINATED BY ','
(symbol, date, open, high, low, close, volume);

The date in the file is formatted mm/dd/yyyy. I need it to be yyyymmdd in the database.
The volume field needs to be truncated by two places so 123456 becomes 1234.
What are the most SQL-centric ways of doing this?
Table definition


texts: SQL Server 2016 ColumnStore Clustered Index - Query plan showing a scan, performance not great

texts:
I have a SQL 2016 table that looks as follows:

I have a columnstore clustered index on the table and no other indexes. I'm executing the following query:

Select *
From dbo.PanelWorkflow
Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400

The query plan looks as follows:

This table has 1.1B rows. The query plan shows a scan and the query takes about 1 minute to execute. ~21K rows are returned. Is this what I can expect from columnstore clustered indexes? Will a scan always be done (assuming no other indexes on the table)? Is this the sort of performance I can expect (acceptable but not great)?

I set Statistics IO on and got the following output:

Table 'PanelWorkflow'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 9733431, lob physical reads 195, lob read-ahead reads 13045645.
Table 'PanelWorkflow'. Segment reads 955, segment skipped 336.

Finally, the query above is strictly a test query to get a feel for how CSIs perform and operate. This is not a production level query.


texts:
Our production environment just froze* this morning for a while when altering a table, adding a column actually.

Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];

* Login into our system requires a select from that very same table, so no one could login during the alter table. We actually had to kill the process to allow the system resume normal operations.

Table Structure:

CREATE TABLE cliente
(
  rut character varying(30) NOT NULL,
  nombre character varying(150) NOT NULL,
  razon_social character varying(150) NOT NULL,
  direccion character varying(200) NOT NULL,
  comuna character varying(100) NOT NULL,
  ciudad character varying(100) NOT NULL,
  codigo_pais character varying(3) NOT NULL,
  activo boolean DEFAULT true,
  id serial NOT NULL,
  stock boolean DEFAULT false,
  vigente boolean DEFAULT true,
  clase integer DEFAULT 1,
  plan integer DEFAULT 1,
  plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,
  facturable integer DEFAULT 1,
  toolkit integer DEFAULT 0,
  propietario integer DEFAULT 0,
  creacion timestamp without time zone DEFAULT now(),
  codelco boolean NOT NULL DEFAULT false,
  familia integer DEFAULT 0,
  enabled_machines boolean DEFAULT false,
  enabled_canbus boolean DEFAULT false,
  enabled_horometro boolean DEFAULT false,
  enabled_comap boolean DEFAULT false,
  enabled_frio boolean DEFAULT false,
  enabled_panico boolean DEFAULT false,
  enabled_puerta boolean DEFAULT false,
  enabled_rpm boolean DEFAULT false,
  enabled_supervisor integer DEFAULT 0,
  demo boolean,
  interno boolean,
  mqtt_enable boolean NOT NULL DEFAULT false,
  topicos character varying(20)[],
  CONSTRAINT pk_cliente PRIMARY KEY (rut),
  CONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia)
      REFERENCES cliente_familia (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pk_pais FOREIGN KEY (codigo_pais)
      REFERENCES pais (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_id_cliente UNIQUE (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cliente
  OWNER TO waypoint;
GRANT ALL ON TABLE cliente TO waypoint;
GRANT ALL ON TABLE cliente TO waypointtx;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat;
GRANT SELECT ON TABLE cliente TO waypointphp;
GRANT SELECT ON TABLE cliente TO waypointpphppublic;
GRANT ALL ON TABLE cliente TO waypointsoporte;
GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce;
GRANT SELECT ON TABLE cliente TO waypointadminuser;
GRANT SELECT ON TABLE cliente TO waypointagenda;
GRANT SELECT ON TABLE cliente TO waypointmachines;
GRANT SELECT ON TABLE cliente TO waypointreports;
GRANT SELECT ON TABLE cliente TO readonly;

CREATE INDEX index_cliente
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default");

CREATE INDEX index_cliente_activo
  ON cliente
  USING btree
  (activo);

CREATE INDEX index_cliente_id_activo
  ON cliente
  USING btree
  (id, activo);

CREATE INDEX index_cliente_rut_activo
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default", activo);

CREATE TRIGGER trigger_default_admin
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_admin();

CREATE TRIGGER trigger_default_grupo
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_clientegrupo();

Should I disable CONSTRAINTS, TRIGGERS, or something else?

Perhaps any DB Tuning?

What else should I provide for further analysis?

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


texts: MySQL Workbench secretly running SHOW INDEX after every query

texts:
A few days ago, I revised some innodb_stats_* parameters and ran mysqlcheck --analyze --all-databases on some Percona 5.5.38 servers. Now, whenever I execute any simple query in MySQL Workbench, it seems to execute a background query of SHOW INDEX ON t1;  Needless to say, this caused some issues when it blocked updates to the table on production servers.

I have ascertained these things:

it happens with any account with sufficient priviledges
it doesn't happen from the command line
it happens for other users
it happens even after I upgrade to 6.2.4 and delete all the settings
it DOESN'T happen against my Percona 5.6 database
SHOW INDEX starts about 5 seconds AFTER my query

I haven't been able to find any explanation for this behavior on the net, so wanted to see if any experienced DBA's have run into this.

Current stats related settings:

innodb_stats_auto_update 0
innodb_stats_method  nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages    8
innodb_stats_update_need_lock    1
innodb_use_sys_stats_table   OFF

Thanks for any pointers anyone can provide.

UPDATE
After looking through the source code, it turns out that the SHOW INDEX happens for every query on a single table. It checks to see if the selected columns contain a primary key or other unique index, so it can decide whether you can edit the data.

The source code contains this comment:

// XXX this can be slow because of the I_S queries, depending on the server

I went to see why my INFORMATION_SCHEMA was slow, and found a helpful post from Percona

It turns out that innodb_stats_on_metadata had gotten turned on during this process, which causes MySQL to recalculate the statistics every time INFORMATION_SCHEMA is accessed. So I just turned it back off.

What a nightmare.


texts: At my wits end trying to create my very 1st MySQL stored procedure

texts:
I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually.
For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that:

I have to change delimiter to something like //
I should use if not exists just in case
Parameters do not start with @, and they have to be parenthesized
I end the block with the temporary delimiter // and restore it to ; afterwards

This is what I arrived at:
delimiter //
create procedure if not exists `logging`.`logEntry`(
   in hostName varchar(512)
  ,in entryDateTime datetime
  ,in entryText varchar(1024)
  ,out return_value int
)
begin
  insert into `logging`.`log` (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText;
  set return_value = last_insert_id();
  select return_value;
end//
delimiter ;

When I run the above code in SquirrelSQL, the server throws the following error:

Error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'if not exists loging.logEntry`(  in hostName varchar(512) ,in
entryDat' at line 1 SQLState:  42000 ErrorCode: 1064

Here is what I tried:

Ran without DELIMITER
Removed if not exists
Removed backquotes around entity names
Inserted values() instead of select even though the standalone insert works

I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?


texts: Is it possible to set optimization level on Microsoft SQL server?

texts:
I would like to ask if there is a way to set the optimization level for SQL Server like it can be done on DB2/Sybase ASE/Oracle. The reason for this question is that I belive that SQL Server may not be choosing hash join for some cases.

Examples for other DBMS:

DB2 - SET CURRENT QUERY OPTIMIZATION
Sybase ASE - SELECT @@optlevel
Oracle - PLSQL_OPTIMIZE_LEVEL


texts: MariaDB FULLTEXT search with short / mandatory words

texts:
Just checking I'm understanding this correctly:
CREATE TABLE customer (
    id INT NOT NULL AUTO_INCREMENT,
    name TINYTEXT NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT (name)
) ENGINE = InnoDB;

INSERT INTO customer VALUES (1, "ABC.DEF");
INSERT INTO customer VALUES (2, "ABC_DEF");
INSERT INTO customer VALUES (3, "ABC'DEF");
INSERT INTO customer VALUES (4, "ABC.DE");
INSERT INTO customer VALUES (5, "ABC.DFF");

Where I've got innodb_ft_min_token_size set to 3 (default is 4).

When running:
SELECT
    c.*,
    MATCH (name) AGAINST ("+ABC +DEF" IN BOOLEAN MODE) AS m
FROM
    customer AS c

Customers 1 and 3 match, because the . and ' are seen as word separators (annoying for O'Brien).
For customer 2, because the underscore gets the whole name treated as a single word, the "DEF" word cannot be found.

If I change the MATCH to "+ABC +DE".
1, 2, or 3 do not match because this is using a full word match ("+DE" does not match "DEF").
4 does not match because... innodb_ft_min_token_size is set to 3?
As in, the 2 letter "DE" word is not in the FULLTEXT INDEX?

If I change the MATCH to use asterisks (e.g. "+ABC* +DE*"), that will use prefix matching.
But will only add customers 1 and 3 to the selection.
Because the 2 letter "DE" word for customer 4 is not in the FULLTEXT INDEX?

If I change the MATCH to use "+ABC.DE*", it matches all of them.
Note how they all get the same rank (even customer 5), and this is no different to "+ABC*", where MATCH seems to be seeing the "DE*" as a separate word, and not matching it against anything.
Whereas "+ABC DE*" is explicitly keeping it as a separate word, and the scores are handled appropriately.

While the individual points make sense, I'm not sure this creates a good system.
For a bit more consistency, I'm wondering if the database should ignore short words (tokens) in the MATCH query, in the same way it does when building the FULLTEXT INDEX.
Only because I don't think "+DE" will ever do anything useful when the min token size is 3; and it's not exactly easy for the developer to identify what the individual words in the FULLTEXT INDEX will be (i.e. to remove them).


texts:
I'm really new to ON DELETE CASCADE. How can I delete child comments when the parent comment gets deleted in a table?

Table Schema

'CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `comment` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=latin1'

Current Query (fails)

ALTER TABLE comments
   ADD CONSTRAINT `delete_child`
   FOREIGN KEY (`parent`)
   REFERENCES `comments` (`id`)
   ON DELETE CASCADE

Error Code: 1452.

Cannot add or update a child row: a foreign key constraint fails
(`App`.`#sql-1405_16b7`, CONSTRAINT `delete_child` FOREIGN KEY (`parent`)
REFERENCES `comments` (`id`) ON DELETE CASCADE)

This SQLFiddle shows my situation. It works in SQLFiddle but not in MySQLWorkbench.


texts: How to create a trigger in a different database?

texts:
Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides.  The databases are on the same server instance. If yes then how?

For example this does not work:

create PROCEDURE [dbo].[CreateTriggrer]
  @db varchar(60)
AS
BEGIN
  SET NOCOUNT ON;
  declare @statement nvarchar(max) = N'CREATE TRIGGER [dbo].[TestTrigger]
   ON  [$database$].[dbo].[TestTable]
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  PRINT ''test''
END'

    set @statement = REPLACE(@statement,'$database$',@db)
    EXEC dbo.sp_executesql @statement = @statement

END

When called like this:

EXEC [dbo].[CreateTriggrer] @db = N'TestDatabase'

It returns this error:

  Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 6
  Cannot create trigger on 'TestDatabase.dbo.TestTable' as the target is not in
  the current database.

Which is fair enough. Is there way to achieve what I want?


texts: Add function parameter to SQL query WHERE clause

texts:
I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function:

CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS
$$
    SELECT .... FROM ...
    JOIN ...
    where true
    ;
$$
LANGUAGE SQL;

I want the Function parameter INPUTS to also be in the WHERE clause so if inputs is

AND speed = 0 AND ....

Where clause looks like

where true AND speed = 0 AND ...

How can i achieve this?

EDIT

Also it is acceptable to have many parameters (a int, b string, c string ..) but then i need to have

WHERE speed = * AND stop = * AND ...

which is not acceptable. How can i achieve this ?

OR can i put a if statement inside it ? Like

Select .. . from ...
JOIN ...
WHERE true
IF (a != null){AND speed = $1}
IF ....
;


texts: How to model a limited history of a table

texts:
Context

I have 3 entities (user, shop and car), a car can only have one regNum, one shopId and one ownerId at a time, that's why they are embedded in the car table.

create table "user"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "shop"
(
  id   bigint primary key,
  name varchar(40) not null
);

create table "car"
(
  id      bigint primary key,
  ownerId bigint,
  regNum  varchar(8),
  shopId  bigint,
  price   numeric(10,2),
  constraint foreign key(ownerId) references "user",
  constraint foreign key(shopId) references "shop"
);

Problem

I want to keep history of the car's regNum, ownerId, shopId and eventually other future fields (but not necessarily all of them).
What's the best solution (scalability/performance/easeOfUse) ? I have found the ones below, maybe someone has faced the same problem, maybe there is another solution ?

Solution 1

I add as much history tables as I have fields to "watch".
This seems to be a normalized way to do the job but it also looks pretty complex to maintain, it's also more greedy as if I modify all fields at once (regNum, shopId and ownerId), I need to insert 3 records (one for each history and so on if I watch other fields later).

create table "carOwner"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(ownerId) references "user"
);

create table "carShop"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop"
);

create table "carRegNum"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  regNum    varchar(8),
  constraint foreign key(carId) references "car"
);

Solution 2

I keep history in a single table, which is a light snapshot of the car table at a given time. It seems easier to maintain but it's not precise as I cannot directly see what changed if I don't have previous record.

create table "carHistory"
(
  id        bigint primary key,
  carId     bigint not null,
  changedAt timestamp not null,
  ownerId   bigint,
  regNum    varchar(8),
  shopId    bigint,
  constraint foreign key(carId) references "car",
  constraint foreign key(shopId) references "shop",
  constraint foreign key(ownerId) references "user"
);


texts: lost all my databases after reinstalling sql server management studio

texts: SQL server management studio was not working so, after googling this issue, I found that I have to reinstall it. So, after re installation, I lost my database and I didn't get a backup of it. So, is there any way to recover it back?


texts: How to restart the distributor agent of transactional replication?

texts:
After troubleshooting query time out error I would like to restart my distributor agent.

So I go to my distributor server, in my distributor DB (called distribution_SA)  I run the following query:

select s.name as [servername],
       d.name,
       d.publication
      ,s.*
   --d.publication, d.local_job, d.publisher_db, d.job_id, s.name
  from distribution_SA.dbo.MSdistribution_agents d
      inner join master.sys.servers s on d.subscriber_id = s.server_id
 --where d.publication = @publisher
order by d.publication, s.name

this gives me the following result (I had to leave the server names on, apologies):
What I want to achieve is the list of jobs that I need to restart in order for the changes that I did on my distributor agent profile to take place.

The problem is that I haven't been able to find these jobs. Where are them?

a) on the distributor

b) on the publisher

c) on the subscriber

Is there any better way of restarting the distributor agent?

I would prefer a T-SQL solution, when possible.


texts: How to do housekeep the database in sql server 2008 by specific date range?

texts: I have database with around 160 GB size. The database contains the data is from 2013 to until today, there are 180 tables, I want to remove the records in specific date range like 2013 to 2014 in all tables. How to do in SQL Server 2008, Is there any query technique or any tool in SSMS?


texts: restricting user just to get aggregated information

texts:
Using mysql, how to restrict a user just to get the aggregated information (eg. sums, averages, count) of the table and only if the result of the number of record is greater than the threshold?

For example, if the threshold is 2, the following query should not be responded if only one supplier is living in London.

select count(*) from s where city = "london"

But this should not be responded.

select salary from s where city = "london"

Because if user knows in advance that there is only one who live in London, he can know his salary. I want to prevent that.


texts: AD groups authentication doesn't work at the console of SQL Server 2017

texts:
I come up with this. A Domain admin windows user is able to login to a SQL Server 2017 instance from any client computer, but it can not login from the console of the server. TCP/IP and Shared memory protocols are enabled and the "Domain admins" group is added as well as a sysadmin.

In order to login from the console I ended up adding the specific domain admin user to workaround the problem at the console but why the same windows user can login from outside the server but it can not do that at the console through AD groups?


texts: Optimal engine for small lookup tables in MySQL

texts:
I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly fewer than 50, max is about 450).

These lookup tables are expected to change only rarely (they come from standard, which changes once in several years) and will be used only to:

fill options in html select
be joined to other records in reports

There will be only SELECT statements on these tables 99 % of times, but there will be quite a lot of them.

I am wondering, which database engine would be most efficient to use?

Here are my considerations:

  MEMORY

  pro: very fast
  con: if the server crashes, all data are lost and need to be recreated
  con: does not support foreign keys

  compressed MyISAM

  pro: fast
  con: does not support foreign keys

  InnoDB

  pro: foreign key support

What I would like to ask, is whether there will be significant advantage in using something different than InnoDB - performance-wise

Thanks, Zbynek


texts:
TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?

Each body type has its own table
Each car in each body type table can has several features

As you can see from my "schema", each car can have several features but I need to connect features table to several car body types tables (many to many relationships). I've thought about it for several days but Im still not sure how to achieve this.

Why all body types are in separate tables? This seems to follow normalization rules, it should also speed up queries because I will never have to query several body types at once.

Is it possible or should I rethink it? Should I just make another table about body types and combine all the cars? I expect to have a maximum of 1M entries (all body types combined). There's going to be a lot more reads than writes.


texts: Formula to change ID in table of postgresql

texts:
I have table in which my id's are completely random. I would like to update table and set id according to type_id.

The formula should looks like that: (type_id * 100) + nextval. Nextval should be connected with id's with same type_id. How can I make it? Name column doesn't matter.

Real state:

id       name     type_id
-------  -------  -------
131        V4       1
263        V2       2
339        V3       2
458        V1       1

Expected:

id       name     type_id
-------  -------  -------
101        V4       1
201        V2       2
202        V3       2
102        V1       1