texts:
I'm "about" to implement a feature on one of my apps which requires storing a lot of data (PlayLists).

Assume the following scenario:

A user can have multiple playlists
These playlists have up to 500k entries (maybe even more)
I must be able to get, remove, re-sort or query the whole playlist or by using indexes
Currently a playlist for each user; but
I'm about to implement a feature that will allow users to have more than 1 playlist.
About 3500 entries per playlist. Some can go as high as 590k.

Playlist entries are represented by the following class:

public class Item {
  private Long owner; // User
  private String data;
  private String name;
  private String author;
  private int index; // Index of this item on the list
}

TL;DR: List< Item > for every user (more than 1 per user) where this list can extend to 500k+ entries.

Currently, I have SQL-based storage for this, however, it's slow as heck, specially when I need to shift indexes - slow up to the point of my db driver thinking that the connection failed and leaked.

To explain "shift indexes": Let's say I'm removing an item at the 3rd index (i = 3), this means that all other items after it need to shift one index down, so I can later on request index 3 and get the item that was previously index 4.

Currently, I decrement the column value Order for that playlist for all elements after the one I deleted.

Current PlayList Items Table design

Id (Ordering/Index) | PlayList (Id) | Owner (User) | Name | Author | Data

Question

Which would be a better/more efficient way of storing this kind of "bulk" data, while still being able to query and modify it?

I'm ok with changing/implementing a new database software for this kind of data, since it's really big (I currently use Redis and MariaDB). However, I would love to avoid having to.


texts: Import a sample database into SQL Server on AWS RDS free tier

texts:
I asked this on stack overflow but someone there suggested I try here as I might get better answers. So here goes...

I'm signed up to Amazon Web Service (AWS) free tier, which allows you to run some very limited instances of EC2 and RDS for free.

I've created an RDS instance running SQL Server 2012 Express - this does not allow remote desktop access, so I also have an EC2 instance running Windows Server 2012 base, and I have installed SQL Server Management Studio and I'm able to connect.

I would like to import some kind of sample database - for example, Northwind, AdventureWorks, or anything that has at least a handful of tables and relationships. The reason is I want to just experiment and practice on a few things (for example, I want to try out the EXPLAIN feature to get to learn how the relational algebra works and how SQL plans out various test queries).

My problem is that Amazon's RDS does not allow me to restore a .BAK file, as local disk access is not allowed, and neither is FILESTREAM, so I can't just download a sample database backup and restore it.

Amazon documents its workaround here, which involves creating a local instance of SQL Server on the EC2 instance (my machine with SQL Server Management Studio installed, which I have RDP access to), and use the Generate and Publish Scripts Wizard to import data from the EC2 to the RDS. My problem here is that I don't have enough disk space to install a local database instance - as I'm on the free tier I'm limited to 30GB total disk space for the server, so the OS + SQL Server Management Studio leaves just a couple of gigs. So I'm totally stuck.

Does anyone have any genius ideas here? Maybe someone has a simple bulk insert script with some data + schema I can use (I can't find any) or some other solution? The database doesn't have to be big, just something to work with. Of course I could always pay to increase my disk space, and go through the whole tedious process outlined by Amazon, but I'd rather not! Also, someone has suggested (on stack overflow) that I create a DB on my home PC and create some kind of remote database connection, but I'm trying to avoid installing tons of heavy software on my home PC, it's the whole reason I'm experimenting with AWS. I'm aiming to have almost nothing installed at home, cloud all the way!


texts: How to prevent crashes connected with dropped tables during mysql import?

texts:
I have made a mysql dump with mysqldump, tables are dropped and re-created with

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (

However for a read-intensive database I sometimes get crashes connected with table "users" missing completely - this must happen when table is dropped and immediately created again. How do I make mysql wait until table is created?


texts: How to get sum & latest activity in same result?

texts:
Table_1
Name   |   Activity  | LogTime
A      |    0        | 2018-12-17 10:16:04.877
A      |    1        | 2018-12-15 10:16:04.877
A      |    0        | 2018-12-16 10:16:04.877
A      |    0        | 2018-12-10 10:16:04.877
A      |    0        | 2018-12-10 10:10:04.877
B      |    1        | 2018-12-16 10:16:04.877
B      |    0        | 2018-12-17 10:16:04.877
C      |    1        | 2018-12-14 10:16:04.877
C      |    1        | 2018-12-12 10:16:04.877
C      |    1        | 2018-12-18 10:16:04.877

Desired Result

Name |   TOTALActivity_0  | TOTALActivity_1  |  LatestActivity_0_Logtime  | LatestActivity_1_Logtime
A    |      4             |     1            |  2018-12-17 10:16:04.877   |  2018-12-15 10:16:04.877
B    |      1             |     1            |  2018-12-17 10:16:04.877   |  2018-12-16 10:16:04.877
C    |      0             |     3            |  NULL                      |  2018-12-18 10:16:04.877

I am able to get result as below

Name |   TOTALActivity_0  | TOTALActivity_1
A    |      4             |     1
B    |      1             |     1
C    |      0             |     3

Using below query

SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1
 FROM Table_1
 GROUP BY NAME

If I tried as below

 SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
  CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
  CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME,Activity,LogTime

Then it is giving Single-single & redundant records.
Then I tried with again with below query

 SELECT
  NAME,
  SUM(CASE WHEN Activity = 0 THEN 1 ELSE 0 END) TOTALActivity_0,
  SUM(CASE WHEN Activity = 1 THEN 1 ELSE 0 END) TOTALActivity_1,
  NULL AS LatestActivity_0_Logtime,
  NULL AS LatestActivity_1_Logtime
 FROM Table_1
 GROUP BY NAME
 UNION
 SELECT NULL,NULL,NULL,CASE WHEN Activity = 0 THEN LogTime ELSE NULL END AS LatestActivity_0_Logtime,
  CASE WHEN Activity = 1 THEN LogTime ELSE NULL END AS LatestActivity_1_Logtime
  FROM Table_1

Then result is again undesirable as below

Name |   TOTALActivity_0  | TOTALActivity_1  |  LatestActivity_0_Logtime  | LatestActivity_1_Logtime
A    |      4             |     1            |  NULL                      |  NULL
B    |      1             |     1            |  NULL                      |  NULL
B    |      1             |     3            |  NULL                      |  NULL
NULL |     NULL           |   NULL           |  2018-12-17 10:16:04.877   |  2018-12-15 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-16 10:16:04.877   |  2018-12-16 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-10 10:16:04.877   |  2018-12-18 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-10 10:10:04.877   |  2018-12-15 10:16:04.877
NULL |     NULL           |   NULL           |  2018-12-17 10:16:04.877   |  2018-12-16 10:16:04.877
.
.
.
.
.
.

Please Help to get distinct & merged result as desired


texts: PostgreSQL has the option to turn off fsync and synchronous commits (http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html). Does SQL Server has this option somewhere?


texts: SQL Server 2019 running 100% on only 1-2 cores with MAXDOP 8 to 14

texts:
Here is the background on this issue.
I am inserting slightly over 1 million records into a table from within a C++ program using ADO.  One row at a time is read in from flat file and converted info a format that ADO will accept to add/update into SQL Server.
Now before you ask, this is a legacy application and it will not be converted to .NET per the clients request.
I have an i7-5960x CPU running/overclocked to 4.4GHZ with 32GB memory and no spinning disk, all Samsung SSD.
I have SQL Server 2019 (Dev Edition) with a MAXDOP of 8 on a system with 8 cores and 8 logical cores.
When running the Add/Update process I have a total CPU use of 10%-20% with 1-2 cores running at 100% (Memory use is 10G out of 32G) causing the Add/Update process to slow processing drastically. SQL Server is showing 132 threads and using 75-80% of the total CPU use. Even if I increase the MAXDOP to 14 it doesn't stop SQL Server from using 100% of any of the CPU cores.
If I comment out the function to write the data to SQL Server the app will process the data from a flat file (37 fields 387 chars total) to the format needed to add/update the data for SQL Server the function will process at least 10,000 records a second. When I run the process to write to SQL Server it will start out processing/writing about 600 records a second to SQL Server then it slowly degrades to about 220 records per second after about 100,000 records.
So the question is, can I restrict SQL Server to a max use on a per core (Not total CPU use) basis or force it to spread the threads/processing over all MAXDOP cores. When it does max out one core it will do it for about 5 minutes then switch to another core and max it out at 100% and the previous core will drop to almost nothing.
Thanks for reading this so far, if you can offer any help it would be really appreciated.


texts: I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

texts:
I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong.

I encrypted table in Source DB and back up certificate, then exported my encrypted table result to CSV from source DB.

In the destination DB, I Created master Key, and using certificate backup (of my source DB) I created certificate in destination and then my symmetric key.

Now I imported the CSV result into my destination DB table and running my decryption script. But it gives me NULL value. (Decryption works fine in source DB, same is not working in destination).

Below is my sample script,

    ----AT MY SOURCE DB----
CREATE TABLE tbluser
  (
     id          INT,
     NAME        VARCHAR(200),
     encryptname VARBINARY(200)
  )

INSERT INTO tbluser
            (id,
             NAME)
VALUES      (1,
             'Raj'),
            (2,
             'Vimal')

CREATE master KEY encryption BY password = 'M@sterKey123'

CREATE certificate testcert1 WITH subject = 'Test my Certificate';

BACKUP certificate testcert1 TO FILE = 'D:\DESKTOP\Certificate\TestCert1.cer'
WITH private KEY ( FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', encryption
BY password = 'Certific@te123' );

CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY
certificate testcert1;

OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

UPDATE tbluser
SET    encryptname = Encryptbykey(Key_guid('SymKeyTest1'), NAME);

CLOSE symmetric KEY symkeytest1;

OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

SELECT TOP 5 NAME,
             CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName
FROM   tbluser;

CLOSE symmetric KEY symkeytest1

DECLARE @str       NVARCHAR(2000),
        @path      NVARCHAR(200)='D:\DESKTOP\Certificate',
        @TableName NVARCHAR(100)='tbluser'

SET @str = 'BCP Test.dbo.' + @TableName + ' OUT ' + @path + '\'
           + @TableName
           + '.csv  -c -t^| -T -S MAILPTP45\SQL2012'

EXEC Xp_cmdshell
  @str

----AT MY DESTINATION DB----
CREATE master KEY encryption BY password = 'M@sterKey123'

CREATE certificate testcert1 FROM FILE = 'D:\DESKTOP\Certificate\TestCert1.cer'
WITH private KEY(FILE = 'D:\DESKTOP\Certificate\pkTestCert1.pvk', decryption BY
password = 'Certific@te123')

CREATE symmetric KEY symkeytest1 WITH algorithm = aes_256 encryption BY
certificate testcert1;

CREATE TABLE tbluser
  (
     id          INT,
     NAME        VARCHAR(200),
     encryptname VARBINARY(200)
  )

BULK INSERT [tbluser]
  FROM 'D:\DESKTOP\Test\tbluser.csv'
  WITH
    (
      fieldterminator = '|',
      rowterminator = '\n'
    )
OPEN symmetric KEY symkeytest1 decryption BY certificate testcert1;

SELECT TOP 5 NAME,
             CONVERT(VARCHAR(50), Decryptbykey(encryptname)) DecryptedName
FROM   tbluser;

CLOSE symmetric KEY symkeytest1


texts: Why does this query not use the index that exists on SURNAME?

texts:
There is an index on SURNAME in the PATIENTS table, we run the following query but the index doesn't get used.

DECLARE @SURNAME VARCHAR(30) = 'test'

SELECT SURNAME
FROM PATIENTS AS PAT
WHERE (PAT.SURNAME LIKE @SURNAME + '%' OR @SURNAME IS NULL)

It seems to be because we check if the variable is NULL as if I remove that then I get a constant scan, compute scalar, index seek and nested loop.

We need to check if the parameter is NULL as we then ignore that in the where clause.

Is there a way to do this and get SQL to use the index?


texts: Load balancing after clustering is failed in SQL Server cluster

texts:
This is what I have been asked today on my interview for a SQL Server DBA position:

  It is an active - active cluster configuration. The primary node is in NY and the other node is in Rio. The NY server is failed and the failover cluster took over the activities. But the Rio server is becoming overloaded because of the additional activities. What will you do to solve the overload problem?

Please help as I am a newbee for the clustering concept.


texts:
I have php application like airbnb. I want to store search values each time user search for listings.

my number 1 solution would be storing it in mysql table, but I'm not sure its wise to do, if site has lots of searchs wouldn't it will slow things down.

should I use mysql or something else?


texts: Continuously backup/restore selected tables

texts:
I'm backing up production data locally to perform experiments and investigate production bugs.

I need only a handful of tables from product DB. I'm using generic mysqldump and now facing a problem where backing up & restoring takes around 20 minutes.

My question is: is there a way to incrementally amend the data I already have with what appeared on production since the last time I dumped the selected tables?


texts: Cassandra in multiple data center - EC2MultiRegionSnitch

texts: I was reading article from : http://www.datastax.com/docs/1.0/cluster_architecture/replication#networktopologystrategy and I have a question about - EC2MultiRegionSnitch. I did not understand it well. According to article: if application requests data from node, but that node does not have data, but data is in another node in other data center, will application be able to get it?


texts: Check progress of alter index reorganize / rebuild

texts: How can I check the progress / status when I submit an alter index reorganize / rebuild ?


texts: Red Gate comparison not working after upgrading SQL Server 2016

texts:
I am using SQL Compare Red Gate Red Gate to make comparisons between 2 DBs on 2 different SQL Servers.

For this, I have  created a SSIS package which encompasses an Execute Process Task. I have passed following as an Executable:

C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe

and as an argument:

sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\"
  /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp\"

This Execute Process Task is to create a .snp file of server 1 and to compare it with server 2. This used to work fine before. However, recently, I have upgraded SQL Server 1 from 2012 to 2016.

I have also re-created a linked server connection for Server 1 on the server where the comparison package is being run. However, I am getting an error. The description is:

  [Execute Process Task] Error: In Executing "C:\Program Files (x86)\Red Gate\SQL Compare 10\SQLCompare.exe" "
  sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\" /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp"" at "",
  The process exit code was "70"
  while the expected was "0".

Please help.

I tried running it via command prompt as follows:

cd C:\Program Files (x86)\Red Gate\SQL Compare 10

sqlcompare /Server1:\"server_ip\" /Database1:\"db_name\" /Username1:\"user_id\"
  /Password1:\"password\" /MakeSnapshot:\"E:\Folder\AUDITserver1.snp\"
  /Options:CaseSensitiveObjectDefinition

And now facing an error as follows:

  Unexpected error: A duplicate object name has been found.
  This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set.
  RedGate.SQLCompare.Engine.SqlCompareException
     at #mWo.#KYo.#0cp(DBPopulationOptions #YBCc)
     at #mWo.#HPud.#crc(ConnectionProperties #x1lc, #en #LfCc)
     at RedGate.SQLCompare.Engine.Database.#crc(ConnectionProperties #Ap8b, #en #5
  OPb)
     at #namb.#n9G.#W8G(DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at #i9G.#n9G.#W8G(DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at #i9G.#n9G.#1tD(String #p9rc, DataSource #ovUb, #en #5OPb, Boolean #q9rc)
     at RedGate.SQLCompare.CommandLine.CommandProcessor.#lMPb()
     at RedGate.SQLCompare.CommandLine.CommandProcessor.#kcmb()
     at RedGate.SQLToolsCommandLine.CommandProcessor.#OUb()
     at #i9G.#OsPb.#6sc(String[] #ead)
  Caused by:
  Index was out of range. Must be non-negative and less than the size of the colle
  ction.
  Parameter name: index
  System.ArgumentOutOfRangeException
     at System.Collections.BitArray.Set(Int32 index, Boolean value)
     at #8rg.#5sg.set_Item(DatabaseLevelPermissionAction #pYrc, PermissionType #iT
  c)
     at #mWo.#KYo.#MCCc()
     at #mWo.#KYo.#ICCc()
     at #mWo.#KYo.#0cp(DBPopulationOptions #YBCc)


texts: SQL Server Permission Denied Error When I'm DBOwner?

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

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


texts: How to get next value of Sequence dynamically in Oracle

texts:
I used to a SQL guy and trying my hands on Oracle front.

I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to handle this in Oracle.

I wrote something same for SQL like this:

SET @v_SQLString = 'SELECT @v_new_seq = next value for '+@v_table_name
SET @v_ParmDefinition = '@v_new_seq NUMERIC(19) OUTPUT'

EXECUTE sp_executesql
    @v_SQLString,
    @v_ParmDefinition,
    @v_new_seq = @v_new_seq OUTPUT

Looking for something same in Oracle.


texts: DBCC MEMORYSTATUS, IO error - how to troubleshoot

texts:
SQL Server 2017 CU 20.
DBCC MEMORYSTATUS information:
Process/System Counts                    Value
---------------------------------------- --------------------
Available Physical Memory                14566158336
Available Virtual Memory                 140223047512064
Available Paging File                    13847318528
Working Set                              5941112832
Percent of Committed Memory in WS        100
Page Faults                              600534997
System physical memory high              1
System physical memory low               0
Process physical memory low              0
Process virtual memory low               0

(10 rows affected)

.....
Buffer Pool                              Value
---------------------------------------- -----------
Database                                 28633748
Simulated                                7030142
Target                                   568983552
Dirty                                    238055
In IO                                    0
Latched                                  22
IO error                                 13 <<== ???
Page Life Expectancy                     15

(8 rows affected)

Sometimes I got worse results for IO error:
Buffer Pool                              Value
---------------------------------------- -----------
Database                                 29362571
Simulated                                7100326
Target                                   568983552
Dirty                                    306750
In IO                                    0
Latched                                  21
IO error                                 102 <<== ???
Page Life Expectancy                     391

I couldn't find anything about IO error (googling).
Server attached to SSD storage.
Nothing wrong in SQL Error log (like flushes etc - nothing).
No system errors in RunTimeLog (extended events session for error > 16 severity).
VMware also couldn't detect any problems.
What is this "IO Error" and how to find any details about it?
P.S. I checked through the 150+ servers, and found 20+ servers with the same memorystatus results with IO error in buffer pool section. Any thoughts?


texts:
There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when.

Is there any way to find it out?
Or enabling an option to be able to track this in future?


texts: I want a select to give me id's that are not in a consecutively order

texts:
I have an Oracle db with a table like this:

+----+-------+
| ID | INDEX |
+----+-------+
| 1  |   0   |
| 1  |   1   |
| 2  |   4   |
| 2  |   5   |
| 2  |   6   |
| 3  |   0   |
| 3  |   1   |
| 3  |   5   |
| 3  |   6   |
| 4  |   0   |
| 4  |   1   |
| 4  |   2   |
+----+-------+

I'd want a select to give me all the ID's with INDEX that does not start with 0 or are not in the correct order. From my example I'd want to get 2,3.
THANKS!


texts: How do I create fractal index using tokudb engine?

texts:
I install MariaDb and configured it to use TokuDb Engine.

I need to use Fractal-Index Built-in on TokuDb Engine.

I have create my index as follow:

ALTER TABLE `table__name` ADD INDEX ( `column` );

I Do this following TokuDb wiki.

When i go on phpmyadmin as index type i see "BTREE".

Need I specify type on creation of index? What should I put as type?


texts: How can you stop MySQL slave from replicating changes to the 'mysql' database?

texts:
I have my slave set to not replicate the 'mysql' database as described in this SHOW SLAVE STATUS\G;

           Slave_IO_State: Waiting for master to send event
              Master_Host: 127.0.0.1
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 1660
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 478
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      **Replicate_Ignore_DB: mysql**
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1660
          Relay_Log_Space: 633
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0

Now, if I go to the MASTER server and issue a GRANT and FLUSH PRIVILEGES:

GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
FLUSH PRIVILEGES;

I then go back to the SLAVE server and issue:

SHOW GRANTS FOR `foo`@`localhost`;

and receive the response:

+-------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How can I stop the slave from replicating changes the mysql database? I figured 'replicate_ignore_db' would have sufficed.


texts:
If I have an expensive function call that takes a JSONB and returns an INT, e.g.

CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS

How do I go about ordering the aggregate result from calling it on a set of JSON data, e.g:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY ???)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I get an error if I try to assign a name to the result:

SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

ERROR:  42601: syntax error at or near "id"
LINE 1: SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)...
                                                 ^
LOCATION:  scanner_yyerror, scan.l:1134

I found I can do it by calling the function twice, but I hate that:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY my_expensive_function(data))
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I also found that this seems to work, but it is a bit cumbersome also:

WITH data_set AS (
    SELECT my_expensive_function(data) AS id
    FROM JSONB_ARRAY_ELEMENTS(json_set) data
) SELECT ARRAY_AGG(id ORDER BY id) FROM data_set;

UPDATE: I should have specified - Is there any magic I can do in a single query or do I have to use WITH/subquery?  Why can't I add a name to the result of the function?


texts: MongoDB data directory space is increasing

texts:
I have a worry, mongoDB data directory space is increasing very rapidly:

I have a two data files and each one has reached 2GB limit and starts spawning 2 GB file multiple files. Is there any where I can compress the data?


texts: Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

texts: I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ubuntu?


texts: maintaining full text index on large table

texts:
I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching
on a handful of columns.
As this is a reporting table and not the source transactional table, sometimes new records will be added, and other times existing records will have to be removed due to changes going on in the source table.
My question is in regards to the best way to build (ongoing) the reporting table and maintain the full text index for this table.
Once the full text index is added, should I:

leave the index alone, and delete/add records as appropriate
leave the index alone, truncate the reporting table, and then insert all appropriate records
other?

I've come across these articles so far while researching, but best practice for this scenario is not readily apparent.

https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?redirectedfrom=MSDN&view=sql-server-ver15
https://techcommunity.microsoft.com/t5/datacat/best-practices-for-integrated-full-text-search-ifts-in-sql-2008/ba-p/305000
https://social.msdn.microsoft.com/Forums/en-US/c743f6e4-88c6-478b-954b-ba8207e391c5/sql-2005-full-text-index-rebuild-nightly-best-approach?forum=sqldatabaseengine


texts: Amazon EBS Type for MySQL Replication Server

texts:
I have the following topology

City 1                                                City 2
Master <----> Distribution Master (EC2 Instance) <---> Slave

Master : Write heavy
Distribution Master: Read only
Slave: Read only

There won't be any application in front of Distribution Master.

Distribution Master is slave of Master and Master of SlaveServer
. Master and Slave are in different cities and the replication needs to be Asynchronous.

The EC2 instance just gets the data from Master and replicates to the Slave when a connection is established.

Do you think I need SSD volumes for the Replication Master?
Or will a cold HDD (sc1) will be enough?

With a cold storage solution I can get more space for a much lower price.


texts: Custom view built on top of pg_event_trigger

texts:
I'm using Postgres 11.5 and have been checking out event_trigger. Amazing. For the uninitiated, event triggers are like table triggers, except for (most) DDL operations. So, CREATE FUNCTION, ALTER FUNCTION, DROP TABLE, and much more. Given that DDL operations are transactional in Postgres, I guess is was manageable to add global traps for these operations.

With an event trigger, you can capture and respond to various DDL events, with limits. So, pretty interesting if, for example, you're trying to track changes to some part of the catalog.

There's a built-in system view named pg_event_trigger that shows what's defined. Like most things pg_catalog, the columns have short, dense names and list the old values of reference objects in other catalog tables. I tend to create my own views for this sort of thing and stash them in a schema named dba. In case it saves someone else a few minutes down the track, here's one version for event triggers:

select evtname as event_trigger_name,
       evtevent as event_name,
       evtowner::regrole as event_owner,
       evtfoid::regproc as function_name,

       case when evtenabled = 'A' then 'Always'
            when evtenabled = 'D' then 'Disabled'
            when evtenabled = 'O' then 'Origin and Local'
            when evtenabled = 'R' then 'Replica'
       end as session_replication_role_mode,

       coalesce(evttags::text,'No TAG filter') as event_tags

 from pg_event_trigger;

That's just the query part of what goes into the view.


texts: Oracle Single table with json vs set of tables using joins

texts:
I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database schema i faced problem to go with single table or set of tables.

Single table in format of (ID, DATETTIME, JSON ) which has all the data dumped as json in JSON column. this will avoid any joins also cater for future event format changes.

Set of tables ( 4-6) data dived on them. query will be using several joins and several unions. Format will be hard to change.

What would be the better approach in terms of performance. For large data set how efficient json over joins ?


texts: Replicate table and content to another using T-SQL

texts:
i am trying to replicate all table structure and data from database A to database B, but unsuccessful.

What is the better way to do it using T-SQL?


texts: Root Privileges Moving From MySQL to Mariadb

texts:
I am experimenting with migrating my database from an older version of MySQL to a Mariadb Docker container. I am simply dumping the entire database and restoring it within Mariadb.

The only issue I've run into thus far is that there are a number of privileges that exist in Mariadb that do not exist in MySQL. As a result, my migrated root user is missing some privileges and is therefore unable to do the following:

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

How can I grant my migrated root user the privileges which are unique to Mariadb when restoring from my dump wipes out the initial mariadb root user?


texts: Make DB tables customizable from an Admin

texts:
I want to build an commercial product promotional mobile application in which the administrator of the app will be able to customize some texts / pictures or whatever from the database that dynamically populates the application.

  For example:

    Lets assume that the database has a description and a picture for the
    product XYZ. The administrator of the specific instance of the app
    decides to change the description of the product through the provided
    CMS for ONLY this instance of the app.

The application will run on tablets that the users will be able to use inside a store, so the administrator of each instance of the app will be the store owner or someone that works there. All the texts / pictures that appear to the application will be fetched from the database.

The application will support both customers login and administrator login. I mean that if the admin of a specific store changes the content of a customizable DB table, all the users that login from this instance of the app will see the customized content.

How is this possible? I first thought to COPY the table that i want to be customizable every time a ADMIN is registed and have a foreign key pointing to the administrator from admin table. Is this an acceptable solution?

PS. I use mysql.


texts:
Referencing:

CREATE TABLE words (
  word_id int(11) NOT NULL,
  word varchar(25) NOT NULL,
  PRIMARY KEY (word)
)

CREATE TABLE synonyms (
  source_index int(10) unsigned NOT NULL,
  destination_index int(10) unsigned NOT NULL
)

Querying:

SELECT w.word, z.word
 FROM words w
 INNER JOIN synonyms y
  ON w.word_id=y.source_index
  INNER JOIN words z
  ON z.word_id=y.destination_index
 WHERE w.word='kind'

The problem is a query on a table with less than 120,000 entries takes 400+ seconds. I was hoping this would be more efficient than having a second table with a similar word list for the synonyms, but so far it is proving otherwise. I have no qualms with keeping synonyms a separate word table as it isn't quite a duplicate of words. I am not finding anything applicable on tuning such queries for lower time online. Is there a way to  tune this for reasonable speed (<100 msec) or am I better off without the 'split self reference'?


texts:
In my SQL Server, I can’t see that options (Basic Availability Group, Database level health Detection, Per DatabaseDTC Suport) at New Availability Group – Specifiyname. I just see to write the [Availability group name].

So, when I execute New Availability Group Wizard, it didn't succeed.

This is the error:

  The specified command is invalid because the Always On Availability Groups join availability group (basic) feature is not supported by this edition of SQL Server.


texts:
I have a drugs catalogue table which is regularly updated. I need to see whether these drug names (e.g. ‘Paracetamol 500mg’) are referred to anywhere within a lengthy text field (up to nvarchar (2000) characters long) in another  table called Logic.
There are no discernible joins or IDs between these two tables.
There are about 1500 drug names which regularly need to be checked against the text in the Logic table.
I have tried everything from fuzzy joins to soundex but am not getting the required results

select drugname
from #drugs t1
where exists (select logic from #logic t2
              where t1.drugname <> t2.logic
                and SOUNDEX(t1.drugname) =
                    SOUNDEX(t2.logic))

Is there a simple way to do this?
Thanks in advance


texts:
I am working on a project of classified ads site.

I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories.

Now should i use few tables and use Json ?

Or should i create separate tables for each item (142 tables) ?

Currently i have decided 142 tables to save myself from relations and joins and heavy coding.

Which way is correct or suggest another one. What are the pros and cons ? I have search a lot but didn't got anything that answers my question.


texts: Query cycles between suspended and runnable without ever completing

texts:
I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running state.

Here is the estimated execution plan: https://www.brentozar.com/pastetheplan/?id=Sy6gPl-6L

While running the trace on this particular process, I see, it holds lock on tempdb and also on one user database but not running any statements on them. Simply its state is changing in between suspended & runnable. When I kill and re-run, it is getting executed normally. I see there are no processes or jobs conflicting with this insert statement.

The wait types are "io_completion" and "sos_scheduler_yield" while the query got stuck. I have used sys.syprocesses and sp_who2 active also dm_exec_requests to monitor. I have run trace while the process is running, once the process went into suspended state showing up "io_completion" to "sos_scheduler_yield" and vice versa.

I am not able to figure out why it is happening. Could you please put some light on it and advise any solutions.


texts: How to improve the estimated cardinality of inner join lateral

texts:
I'm currently running a query that has the following structure:
select
  v1.*,
  v2.*,
  ...
from t1
  inner join lateral f1(t1.id1, t1.id2) as v1 on true
  inner join lateral f2(t1.id1, t1.id2) as v2 on true
  ...
where
  t1.id1 = 123 and t1.id2 = 234;

This works fast enough, but I've noticed the following problem with explain analyze.
For every inner join lateral, it seems to multiply the amount of estimated rows by 1000 even though the joins don't add new rows. This leads to the query plan having more rows that the jit_above_cost which results in the JIT running and adding significant delay (450ms on a 40ms query).
I'd like to avoid disabling jit_above_cost as I'm sure that it can be useful for optimizing queries, but I've tried searching for ways to improve the cardinality estimate, but I've had not luck.


texts: Translate SQL Server update statement to MySQL

texts:
I have a development environment with MySQL and a production environment with SQL Server.

This update statement works with SQL Server:

UPDATE THIS
SET <list_of_column_assignments>
FROM <table_name> THIS
JOIN ( VALUES (<csv1>, <csv2>,...) ) VALS ( <column_names> )
ON <list_of_primary_keys_comparison>

How do I translate this to a MySQL statement?

I'd like to have the same code for both environments (if possible).

Example that works on SQL Server:

UPDATE THIS
SET THIS.DSEndGeoSys = VALS.DSEndGeoSys,
    THIS.DSPrecisaoGeoSys = VALS.DSPrecisaoGeoSys,
    THIS.NRLatGeoSys = VALS.NRLatGeoSys,
    THIS.NRLngGeoSys = VALS.NRLngGeoSys
FROM TBEstabelecimento THIS
JOIN
(
    VALUES
        (
            'Icém - SP, 15460-000, República Federativa do Brasil',
            'APROXIMADA', '06291846001771', -20.4053212, -49.1625989, '1'
        )
) VALS ( DSEndGeoSys, DSPrecisaoGeoSys, NRCGCCEICPF, NRLatGeoSys, NRLngGeoSys, TPInscricao )
ON
(
    THIS.TPInscricao = VALS.TPInscricao
    AND THIS.NRCGCCEICPF = VALS.NRCGCCEICPF
)


texts: Can we create index for key/value of JSONB data type?

texts:
Can we create an index for a key/value of a JSONB data type?

For example, for these schema:

CREATE TABLE x (
  id BIGSERIAL,
  data JSONB
);
CREATE TABLE y (
  id BIGSERIAL,
  data JSONB
);

The slow query:

SELECT *
FROM x
  LEFT JOIN y
    ON (y.data->>'x_id')::BIGINT = x.id

How to create an index for y.data->>'x_id' that can be used for that kind of query?


texts: when we restore and when we recover a database?

texts:
Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Question: Why copying/moving a database to a new location is a restore not a recover?


texts: Indexing an attribute of the elements of an array of another array in a jsonb field

texts:
I have the following document that is stored in a jsonb field in a postgres 11 DB.

{
    "bundles": [
        {
            "id": "9c9c1b73-b058-3f22-a48b-725ce39eb861",
            "items": [
                {
                    "id": "6645c9e2-b87a-445e-96d8-585240a73b0b",
                    "reservation_id": "4ca47003-c1b2-4b11-9623-7d27a6af8bbd",
                    "source_stock_location_id": "c8ec143a-b311-4d28-80d4-a5d42e6ff7fe"
                }
            ],
            "specification": {
                "customer": {
                    "segment": "D",
                    "priority": 42
                },
                "export_between": {
                    "latest_exclusive": "2019-12-20T10:39:22.427Z"
                }
            },
            "is_part_of_split": false
        }
    ]
}

We have now the use case to query the get the row that contains a certain item by id. An example of a simple full scan query would be:

SELECT *
FROM (
  SELECT *, jsonb_array_elements(jsonb_array_elements(event->'bundles')->'items')->>'id' elem
  FROM bundle
) AS items
where elem = '6645c9e2-b87a-445e-96d8-585240a73b0b';

where event is the field containing the jsonb and bundle the name of the table.

This is a very expensive query and we are investigating about the possibility of creating a proper index.

We have struggle a bit with it and so far we have come to the following proposal:

CREATE OR REPLACE FUNCTION bundle_items(jsonb)
  RETURNS jsonb AS
$func$
   SELECT a->'items'
   FROM   jsonb_array_elements($1) a
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX items_idx ON pdh.bundle_load_test USING GIN(bundle_items(event -> 'bundles'));

which works pretty ok with a query like:

select *
from bundle
where bundle_items(event -> 'bundles') @> '[{"id": "4ca47003-c1b2-4b11-9623-7d27a6af8bbd"}]';

Now the question is if this could be improved, the query pattern would strictly query by id, so just indexing the ids of the items would be enough. But so far I couldn't figure out a way to just index the id.


texts: Is it useful to have a 0..1 to 0..N here?

texts:
I came across this example from school.

Doesn't this imply that you could have something like: 0 messages are answer to 0 messages?
It confuses me, because wouldn't a 1 to 0 or more messages suffice?


texts: Splitting database based on country with shares users

texts:
99.9% of queries will be filtered for a specific country (either USA or Canada for now), so I would like to split the database country. This way, a query would not have have to step over all the rows for the other country, there's one less column to index.

All countries should share users so there are no duplicates, but Postgres cannot create a foreign key to another database. stackoverflow.com and stackexchange.com share users, amazon.ca and amazon.com share users. How do they do it?

The alternatives I have in mind so far:

Split by Schema instead of Database. I think this is the best option if there are no performance sacrifices relative to having a separate Database.
Don't share usernames; a user in Canada could have the same username and primary key as a user in USA.

I think this would be a common issue for websites, but I haven't found any tech blogs discussing it. I would appreciate it if someone could share their experiences with this, what to do/what not to do.


texts:
A MySQL database needs to contain highly sensitive data that cannot be left unencrypted at rest.  But yet the database needs to remain searchable by an app.  What are the options for encryption at rest with MySQL?

I have looked into CryptDB but it has not been supported since early 2014, and CryptDB also does not integrate with Java naturally.  Might be a lot of work to get CryptDB to work with Spring and eclipse, only to find that it is less and less supported as time passes by.  And it seems like other row-level encryption methods are not yet viable, unless someone has suggestions.

So what are the database level encryption options for MySQL?  And what are the file system encryption options related to a MySQL database running in CentOS 7?

The MySQL database interacts with a Spring MVC app exclusively.

Note: This is running on a dedicated physical box that ONLY has CentOS 7 installed.  So we cannot use any Windows-specific tools.


texts: CPU Time Slower in SQL Server 2014 than in SQL Server 2008 R2

texts:
We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v).  We were doing some performance testing and to our surprise it seems that CPU times are worse in the new server system compared to the old, crackling one.
These are the systems:

Old Server:

Runs in Virtual Machine VMWare ESX 5.1
Windows Server 2008 R2 32 bits
SQL Server 2008 R2
Quad Core Xeon ES-2620 2Ghz
5600MB RAM

New Server:

Runs in Virtual Machine Hyper-V UEFI Release v1.0
Windows Server 2012 R2
SQL Server 2014
Quad Core Xeon ES-2640 2.6Ghz
32766 MB RAM

A little history of what we did:

Two different benchmark tests shows that CPU is about 80% faster, memory access is about 100% faster and IO disc access is about 1000% faster in the new server.
Testing queries are using multiple joins with non-clustered indexes.
In both servers the testing database is exactly the same.
In both servers energy power option is set as High Performance and also checked with CPU-Z that the processor is always at max speed.
In both servers “locked pages” are enabled.
In both servers the statistics are properly updated and query plans are the same when executing the test queries.
To make sure that the plan are always the same we’re executing the queries in SQL Server 2014 with compatibility mode 100 (SQL Server 2008), but as well without this compatibility-mode the outcome is the same.
Max memory options is set to default value in both servers (2147483647) and we have also checked the parallelism (max degree is set the same for both and also the cost)
The new server is dedicated (there is nothing else running there)

After all these checks we’re getting better time results for cached queries (caused only by CPU time) still in the old server.

Test query:

SELECT ROW_NUMBER() OVER (
        ORDER BY c.field1
            ,c.field2
        ) AS RowNumber
    ,a.t1_t3ID
    ,c.field1
    ,c.field2
    ,a.t1_t2ID
    ,b.field1
    ,a.BeginDate
    ,a.EndDate
    ,c.field3
FROM table1 a
INNER JOIN table3 c ON a.t1_t3_ID = c.t3_PKID
INNER JOIN table2 b ON a.t1_t2ID = b.t2_PKID
WHERE '20140101' BETWEEN a.BeginDate
        AND a.EndDate

Query Execution Plan (is the same for both servers, with the same percentages):

Query Execution Plan Here

The Results for CPU time on both servers:

Old server:

CPU time = 93 ms,  elapsed time = 65 ms.
CPU time = 94 ms,  elapsed time = 99 ms.
CPU time = 94 ms,  elapsed time = 70 ms.
CPU time = 93 ms,  elapsed time = 63 ms.
CPU time = 93 ms,  elapsed time = 67 ms.

New server:

CPU time = 125 ms,  elapsed time = 309 ms.
CPU time = 141 ms,  elapsed time = 304 ms.
CPU time = 139 ms,  elapsed time = 288 ms.
CPU time = 156 ms,  elapsed time = 277 ms.
CPU time = 142 ms,  elapsed time = 323 ms.

  Note: we have also checked that the CONVERT_IMPLICIT is not affecting
  performance in this case.

  What we observed is as well if we force to use only one processor-core
  in the old server CPU time keep roughly the same. If we do same in the
  old server the execution-plan is switching to table-scans and CPU time
  increase up to 800ms.

Somebody have an idea what still can be checked/tested or at least have an explanation on this observation? Or is it for sure the virtualization systems (vmware vs. hyper-v) which cause such a difference in CPU time for databases?


texts: Am I clearing the query cache correctly?

texts:
I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working.

I ran a query the first time and it took about 3.5 seconds and all additional times it took approx 0.25 seconds; however I made some changes to it and now want to test it again without relying on the cache.

I ran:

RESET QUERY CACHE;

...and then reran the query, but it doesn't seem to have had much affect as the execution times I am getting on the first run are approx 0.35 seconds and once again approx 0.25 seconds on any additional runs.

Is there another way to do this or do you think the first ever run was an outlier?


texts:
Trying to insert data to a table with values from different tables.

  INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
   select (select class.id as class_ref from class where class.name = 'pe'),
   (select teachers.id as teachers_ref from teachers where teachers.last_name
    = 'lenevo'),
   (select section.id as section_ref from section where section.name = 'grade 4')

It's only inserting class_ref values, what's wrong with my code?

Tried this.

INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref)
    values( (select class.id as class_ref from class where class.name = 'pe'),
           (select teachers.id as teachers_ref from teachers where
            teachers.last_name    = 'lenevo'),
          (select section.id as section_ref from section
           where section.name = 'grade 4'))

teachers_ref can not be null error.


texts: Weak entity having n instances as the 'one'

texts:
I'm struggling with a question, I'm just trying to figure is it even valid , if not where does it violates the rules of a weak entity relationship, I think it's not valid because how can a weak entity have n weak entities.
so is it valid ?


texts: MySQL Workbench copies Schema, but not data, from SQL Server 2008

texts:
I need to copy/migrate a database from MSSQL (SQL Server 2008) into MySQL. I am using the MySQL Workbench kit, and work through the process of the Migration. Each respective DB user has full access, the ODBC drivers are in place as well as the DSN.

It winds up copying the schemata successfully, but not any of the data. There are no errors or warnings (other than charset notices) and I am drawing blanks as to why data will not transfer over. There are not a lot of options in the migration steps, so I don't think I missed anything.


texts: Is it possible to do flashback after truncate?

texts: Is there any possibility of doing flashback after truncate ? Thank you in advance.


texts: How do I find the first row of the longest occurrences of repeating numbers?

texts:
I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,0
2,East,1_1_2,1,0
3,East,1_1_3,1,4
4,East,1_1_4,1,0
5,East,1_1_5,1,0
6,East,1_2_1,1,3
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,4

I would like to find the beginning of the longest string of repeating numbers from that position to the end of the table.

For the example above, the mysql statement should return row id of 8 because from row 8 to the end of the table the value in the occupied and remaining columns are 0 and 4.

Any help on writing this as a mysql select statement would be greatly appreciated!


texts: What are the criteria for a sql staying in the V$SQLAREA?

texts: I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under v$sql and V$sqlarea. What are the criteria for Oracle to keep the query in those views?


texts: Database Design - Storing Stadium Capacity for Current and Past Events

texts:
I am trying to design a database as a personal/passion development project to track current and historical football game information. I am having trouble with table designs that will allow me to store data that might change over a period of time.  For instance, stadium capacity may grow or shrink based on a renovation of the stadium.

In this simple diagram, I have three tables:

But with this design, I will only store the current capacity of the stadium so if Stadium A increases capacity by 5,000 in 2018 - all events before would reflect the current capacity and not the capacity when the event occurred.

My initial thought it that I would need an additional table - maybe StadiumCapacity - that would store the stadium ID, a capacity number, and the years that stadium held that capacity and possible a MostRecent column.

Does anyone have experience with designing a database for sports related information and would this be the most efficient way of tracking this information?


texts: Disk space requirement for REINDEX in Postgresql

texts:
There is a server with postres 13 running out of disk space.
It contains a table metrics that occupies 90% of the entire database.
I have deleted old records in this table.
Due to this table is used in production, I cannot run "VACUUM FULL metrics".
Since I can't afford to block it.
After deleting old records I want to REINDEX CONCURRENTLY bloated indexes to free space.
But first of all I would like to know how much space is needed to reindex them. If the server runs out of space Postgres will crash.
So, is it possible to calculate a space requirement for REINDEX in Postgresql?


texts: How to create index on huge oracle table?

texts:
I tried it with below SQL.

CREATE INDEX idx ON table1 (column1, column2) Local parallel 10;

but its giving me below error

SQL Error: ORA-12801: error signaled in parallel query server ..,
instance .. ORA-27090: Unable to reserve kernel resources for
asynchronous disk I/O Additional information: 3 Additional
information: 128 Additional information: -1374056344
12801. 00000 -  "error signaled in parallel query server %s"
*Cause:    A parallel query server reached an exception condition.
*Action:   Check the following error message for the cause, and consult
           your error manual for the appropriate action.
*Comment:  This error can be turned off with event 10397, in which
           case the server's actual error is signaled instead.
Elapsed: 00:16:10.821

where table1 is a partitioned table.


texts: Block size is for Database or Tablespace?

texts: Forgive me I am just a newbie in the Oracle. I saw there is a db_block_size parameter which can be specified the block size for a database so that the whole database will be read and written data with the specified block size. But what make me confused is we can also specify block size for the Tablespace when we want to Create Tablespace. Please check the Block size clause. My question is are they contradictory? How could they both work in the same Database. Because the TableSpace belongs to a Database. Thanks.


texts: Quick search the most similar objects in the n-dimensional space

texts:
Lets assume that we have a points in the n-dimensional space. So we have a n coords(n columns) which can describe location of the each point.

We need to implement a table which can be used for a quick searching the most similar points, i.e. points which have the smallest distance to the desired point.

E.g. points in the db:

id  c1  c2  c3  c4  c5
1   5   19  42  12  16
2   3   23  38  15  12
3   14  21  32  33  1
4   12  29  21  24  5

If we want to find the best matching for point with coords:

c1 c2  c3  c4  c5
4  20  40  14  15

We will get points with id 1 and 2.

We also have mean coordinate for each dimension(column) and vector for each point in which first element - number of the dimension in which point has the largest difference from the mean coordinate in this dimension, and last - number of the dimension in which point has the smallest difference. Maybe it can be used for the more rapid filtering points which have the biggest distance to the desired point.

So how can I do something like this using MySQL?

I think the composite index and order by abs(cx - $mycx) can be a good solution, but I can't use it because I will have more then 16 columns which I need to include in the one index.

Any help will be very useful!


texts: Full backup job failed after changing backup location

texts:
In AG Environment, for the last 30 days, the daily Full backup job is failing for a few of the larger databases. The rest of the databases are getting through daily so far. We recently changed the backup location path to aws S3 UNC storage.

We are seeing error in the SQL Server error log as:

  The operating system returned the error '59(An unexpected network error occurred.)' while attempting 'DiskChangeFileSize' on

How should we interpret the error message?


texts: Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

texts:
I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When I take that package and run it on the SQL Server agent, it fails saying it had a truncation error on the date field. When I manually look at the data (it’s a mere 159 records) I don’t see any funky data.

Both my account and the service account are server administrators so no difference of access on the server side, and we are both in the local administrator group on the destination file share. I also run 4 other packages that are built the same way, but this is the only one that has the data truncation error only on the agent, and not manually run in bids.

Below is the full text of the error minus the user account information.

  Executed as user:<Domain>\<sql service account>. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6560.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.
    Started:  7:13:49 AM
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0202009
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_OLEDBERROR.
       An OLE DB error has occurred. Error code: 0x00040EDA.
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC020901C
     Source: Data Flow Task 1 chips dest [705]
     Description: There was an error with input column "DATE" (959) on input "Destination Input" (718). The column status returned was: "The value could not be converted because of a potential loss of data.".
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0209029
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
       The "input "Destination Input" (718)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (718)" specifies failure on error. An error occurred on the specified object of the specified component.
       There may be error messages posted before this with more information about the failure.
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0047022
     Source: Data Flow Task 1 SSIS.Pipeline
     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
       The ProcessInput method on component "chips dest" (705) failed with error code 0xC0209029 while processing input "Destination Input" (718). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
       There may be error messages posted before this with more information about the failure.
  End Error
  DTExec: The package execution returned DTSER_FAILURE (1).
  Started:  7:13:49 AM
  Finished: 7:13:53 AM
  Elapsed:  4.197 seconds.
  The package execution failed.
  The step failed.


texts: Running multiple postgres database clusters on the same server

texts:
We are currently running postgres 9.5 on rhel7 and have a one database cluster per server. I'm looking at adding multiple database clusters per host, and am wondering whether there are any recommendations for memory settings when doing this?

for example, a general rule of thumb seems to be that the shared_buffers should be set to 1/4 of your system memory. So my question would be do you divide the 1/4 between the N clusters on the server, or do you allocate 1/4 of the memory per cluster (therefore only have approx 3-4 clusters on each host)

thanks
dave


texts: Reasonable values for thread_cache_size in relation with Connections

texts:
I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients connected?

Thanks!


texts: Suggestion for SQL distributed transactions via MSDTC

texts:
Currently we do not use MSDTC in our company. Our developers would like to start using MSDTC for Distributed Transactions within their applications.

Questions:

Is it okay to use MSDTC for SQL distributed transactions (there would be millions of transactions per day)?
What is the best practice to use it security wise?
Is there any other alternative to do the distributed transactions from the application?


texts: SQL Express 2008 R2 on Amazon EC2 instance: tons of free memory, poor performance

texts:
The old SQL Express 2005 was running on a low-end single Xeon CPU Dell server, RAID 5 7200 disks, 2 GB RAM (SBS 2003).

I have not done any baseline measurements on the old physical server, but the Web app is used by half a dozen people (maybe 2 concurrently), so I figured "how bad can an Amazon EC2 instance be?".

It's pretty horrible: a difference of 8 seconds of load time on one screen.

First of all, I'm not a SQL guru, but here's what I've tried:

Had a Small Instance, now running a c1.medium (High Cpu Medium) Windows 2008 32-bit R2 EBS-backed instance running IIS 7.5 and SQL Express 2008 R2.  No noticeable improvement.
Changed Page File from fixed 256 to Automatic.
Setup a Striped Mirror from within Disk Management with two attached 1 GB EBS volumes. Moved database and transaction log, left everything else on the boot EBS volume.  No noticeable change.
Looked at memory, ~1000 MB of physical memory free (1.7 GB total).  Changed SQL instance to use a minimum of 1024 RAM; restarted server, no change in memory usage.  SQL still only using ~28MB of RAM.

So I'm thinking: this database is tiny (28MB), is the whole thing cached in RAM?  Can I confirm? Surely that would speed up performance.  The transaction log is 241 MB.  Seems kind of large in comparison -- has this not been committed?  Is it a cause of performance degradation? I recall something about Recovery Models and log sizes somewhere in my travels, but not positive.

Another thing: the old server was running SQL Express 2005.  Not sure if that has any impact, but I tried changing the compatibility level from SQL 2000 to 2008, but that had no effect.

Anyways, what else can I try here?  Seems ridiculous to throw more virtual hardware at this thing.  I know I/O is going to be rough on EBS volumes, but surely others are successfully running small .NET/SQL apps on reasonably priced instances?


texts:
We're currently running a Galera cluster of 3 DB nodes. The way we're set up (due to ORM and automagical locking), we only have one of the 3 DB nodes being written to, and the other two provisioned for reads.

Galera's set to auto_increment on a value of 3, since there are three nodes in the cluster, in order to ensure that auto_increment values across the cluster are always unique. See this article (this is a Galera default) - https://blog.mariadb.org/auto-increments-in-galera/

If we plan to always only have 1 write DB, would it be dangerous or risky to set auto_increment to 1 globally across the cluster?


texts: Is it possible to set stand-by database to active, do changes and then continue restore chain?

texts:
Basically, I'm using a custom log shipping method on a sql 2012, where the transaction files are manually copied to an offsite location. The files are then restored using something similar:

RESTORE LOG xyz
    FROM MyAdvWorks_log2
    WITH STANDBY = 'c:\undo.bak'

I would really like to be able to set that stand-by database briefly to Active, do various stuff (not just read) and then rollback/discard those changes and allow the restore to continue.

Is that even possible?
If not, can I copy the stand-by server and set it to active (and then when I'm done, delete it)?


texts: To Join two tables without using Primary key

texts:
Table1: Order;
 Column Name: Id  (PK), SecurutyId, UserId. ** But no foreign key in this table.

Table2: Security;
Column Name: Id (PK), SecurutyId (FK), Symbol.

Can I do That?

Select o.SecurityId, o.AccountId, s.Symbol
From Order as o, dbo.Security as s
Where s.Id= o.SecurityId ;

It is Giving me the result i want. Can anybody explain me why it is giving me the result? Because as far i know to join two tables we need one PK references to another table FK. Am i doing the right thing?


texts: Updating Multiple Tables with single Update query

texts:
I have two tables TableA, TableB as follows.

Table "db3004db.tableb"

 Column  |  Type   | Collation | Nullable | Default
---------+---------+-----------+----------+---------
 empid   | integer |           | not null |
 salaryb | integer |           | not null |

Indexes:
    "tableb_pk" PRIMARY KEY, btree (empid)

Table "db3004db.tablea"

 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 empid  | integer |           | not null |
 salary | integer |           | not null |

Indexes:
    "tablea_pk" PRIMARY KEY, btree (empid)

Before Update:

select * from tableA;

 empid | salary
-------+--------
     1 |   1004
     2 |   2007

select * from tableB;

 empid | salaryb
-------+---------
     1 |       0
     2 |       0

Expected Update Query:

set tableB.salary=tableA.salary%1000,
    tableA.salary= tableA.salary/1000
where (tableA.pk = tableB.pk)

After update:

select * from tableA;

 empid | salary
-------+--------
     1 |   1
     2 |   2

select * from tableB;

 empid | salaryb
-------+---------
     1 |       4
     2 |       7

I'm interested in doing this update in Postgres (with single update statement)? If possible , is this query compatible with SQL standard?


texts: Function to update column value if the row was updated

texts:
I am trying to create an function which will increment my version column value (int) if I update any column of my database row.
For instance, if i have a foo table that has columns (id, title, version) and the default version is 1, I want my vesrion to be incremented once I update a title of my single foo row. I wrote the following function but it throws an error once I test it:

create or replace function update_foo_version()
returns trigger as $body$
  begin
    update foo
    set version = version::int + 1
    where old.id=new.id;
    return new;
  end
$body$
language plpgsql;

My trigger for this function:

create trigger update_version
after update on foo
for each row execute procedure update_foo_version();


texts:
When I run the mongod --dbpath /data/db command to start MongoDB, I get the following error message:

oot@vps [/]# mongod --dbpath /data/db
2015-11-17T21:10:54.048+0000 SEVERE: terminate() called, printing stack (if implemented for platform):
0x121d451 0x121c6d8 0x7f77f06c7bd6 0x7f77f06c7c03 0x7f77f06c7d22 0x771858 0x1262158 0x121ceb9 0x76fc75 0x76ff69 0x7f77efdfbd5d 0x766529
 mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x121d451]
 mongod() [0x121c6d8]
 /usr/lib64/libstdc++.so.6(+0xbcbd6) [0x7f77f06c7bd6]
 /usr/lib64/libstdc++.so.6(+0xbcc03) [0x7f77f06c7c03]
 /usr/lib64/libstdc++.so.6(+0xbcd22) [0x7f77f06c7d22]
 mongod(_ZN5boost15throw_exceptionINS_21thread_resource_errorEEEvRKT_+0x1c8) [0x771858]
 mongod(_ZN5boost6thread12start_threadEv+0x138) [0x1262158]
 mongod(_ZN5mongo27startSignalProcessingThreadEv+0x299) [0x121ceb9]
 mongod() [0x76fc75]
 mongod(main+0x9) [0x76ff69]
 /lib64/libc.so.6(__libc_start_main+0xfd) [0x7f77efdfbd5d]
 mongod() [0x766529]

I am new to MondoDB and Linux servers, so really not sure where to start with this, there doesn't seem to be any usefull info in the error.

Has anyone else had anything similar? Or any ideas on where to start with fixing it?


texts: Classic fool who has default Full Backup model and out-of-control log file ---- how to I get this controlled?

texts:
Hey there I'm usually more of a development dba but the old dba left --- and he left about a year ago without a replacement.
I'm finally actually looking under the hood of some of these servers -- I'm not a production dba but I ran some diagnostics I found online and they say I have big problems.
Anyway I'm learning this stuff slowly but this seems like a common issue --- I have Full Recovery mode on a particular database, but never back it up -- as a result the log file grows exponentially -- I take it?
What are my options to get rid of this?
I'm guessing:

If I do a full back up or schedule one, the log file will get cleared after this?

Can I just change to the simple recovery model? I know that has recovery implications - I'm comfortable with data loss as this is a BI database. Will that flush out the log file immediately as well? Do I have to do anything OTHER than a switch to the simple recovery model?

Is there some way to monitor the progress of either of these two options? I'm guessing either will cause the database to start chugging resources doing something or other.
Thanks!


texts:
I have 2 queries given below. Now I want to have one quey by combining the two queries so as to get the table with columns (Tag Number, Primary Name and Discoverd Name)

Query 1:

select  RT.[TagName] as 'Tag Number',
        RA.SourceName as 'Primary Document',
        RA.SourceRevision
from dbo.REP_TTA as RT
join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
where RA.[AssocName]='is a document for'

Query 2:

select RT.[TagName] as 'Tag Number',
       RA.SourceName as 'Discoverd Document',
      RA.SourceRevision
from dbo.REP_TransposedTagAttributes as RT
join dbo.REP_ASS as RA on RA.TargetName=RT.TagName
where RA.[AssociationName]='refers to'


texts:
What does it mean?

2017-04-28T04:37:53.126+0000 D -        [conn1571] thread over memory limit, cleaning up, current: 67k
2017-04-28T04:37:53.911+0000 D -        [conn1585] thread over memory limit, cleaning up, current: 79k

Should I make something to solve it? If I increas server memory will it help?


texts:
PostgreSQL data directory was accidentally deleted.
Right know I'm using photorec to recover all the files in the disk.
I also know from the docs the structure of that directory. There are text files for configuration and there are also files for tables, maps, indexes, toast, and so on grouped in subdirs by existing DBs.
My questions are:

Can a software like photorec, to mention one, recover those postgresql files like it does with image, video, audio, office and pdf files?

Let's say the files gets recovered. How can I restore the proper names of them and put them together under the correct subdir ?

Needless to say that there is no backup of any kind.


texts: Add full text index facility to SQL Server instance

texts:
Can anyone help me to add full text index facility to a SQL Server instance? FYI  it is cluster environment, SQL Server 2012 Developer Edition.

Running select serverproperty('isfulltextinstalled') returns value 0.


texts: mysqldump, preserve update_time and modify_time attributes as listed by show table status from

texts:
Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?

If not, is there an option that does this?

From the manual it looks like mysqldump preserves this data if you export to XML.
EDIT: Well the data is included in the export. Whether it's read in at the other end I'm not sure.

Is there a way to do this with a normal .sql dump?


texts: Turn TDE off when restoring SQL databases

texts:
We are just getting started with TDE.  Eventually it will be in our Prod SQL Server environment and two major non-prod environments, but currently it is only in one non-prod system.

My question is about restoring these databases.  We have several other environments (Enterprise and Standard Edition) and an offsite vendor that we sometimes send database backups to.  For those, we will either have to use an intermediary server to turn TDE off before restoring, or back up with different keys.

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment.  Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward?  I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.
Also note the strange behavior when I restored below.  It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key.  It this correct?

I did the following in our non-prod environment consisting of two database instances on different VMs:

On instance 1:

Enabled TDE on the database DBA,
Backed up the cert, key, and database,
Restored the backup of DBA to a new database DBA2,
Backed up the database DBA2,
On instance 2:
Copied the DBA2 backup from instance 1,
Restored DBA2.

Note that I did not create any certs or keys for DBA2, not did I copy any DBA certs and keys from instance 1 to 2.

Instance 2 has its own unrelated DBA database with TDE certs and keys.  TDE is not enabled on DBA2 after restore.

So it seems like:

the instance 1 DBA key got carried over into DBA2 in step 3, and
the restore may have used the DBA key from instance 2 in step 6, even though instance 1's DBA and instance2's DBA are separate entities.

Here is the encryption status on these databases:

    SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
    FROM sys.dm_database_encryption_keys
where db_name(database_id) like 'DBA%'

Instance 1:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256
DBA2    3                   0                   AES             256

Instance 2:

dbname  encryption_state    percent_complete    key_algorithm   key_length
DBA     3                   0                   AES             256


texts:
Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around 250 distinct COMPANYIDs over those 12 million rows.

Is it possible, when defining the full-text indices, to give each COMPANY its own "branch" in the tree?


texts: Conditionally sum a column based on a mod result

texts:
I have the following query:

SELECT y.datamov,
       y.n_conta,
       y.sinal,
       y.valor,
       y.qtde,
       a.nome,
       '',
       0,
       a.lrdarc
FROM (
    SELECT k.datamov,
        k.n_conta,
        k.sinal,
        k.qtde,
        sum(k.total) AS valor
    FROM (
        SELECT DATE (b.dt_normal) AS datamov,
            b.historico,
            a.obs,
            a.total,
            a.qtde,
            b.sinal,
            c.nome,
            a.n_conta
        FROM caixa.rtitens a
        LEFT JOIN caixa.rtlan b ON (b.numero = a.n_lan)
        LEFT JOIN caixa.rtctares c ON (c.numero = a.n_conta)
        WHERE DATE (b.dt_normal) BETWEEN DATE ('2010-02-13') AND DATE ('2010-02-20')
            AND c.lrdarc != 0
            AND b.sinal = 'E'
        ORDER BY 1, 7, 5
        ) k
    GROUP BY 1, 2, 3, 4
    ORDER BY 1, 2, 3
    ) y
LEFT JOIN caixa.rtctares a ON (a.numero = y.n_conta)
ORDER BY 1, 2, 3

Which returns results like these:

My problem is: I need to conditionally "merge" two rows into one when the mod of the a.lrdarc is equal to 1. If this condition is not filled (ie the mod is 0), then it should show the column without doing anything. I've been trying to do this for a while and until now I have no idea on how to solve this. What can I do?

EDIT: I want something like this:


texts: Way to make differential backup from full different but from same database

texts:
I have the following situation:
Our sql server is running in a cloud service. The database to be backed up have 60gb.
We need to make a local copy of the database for some tests, and it need to be up to date, but 60gb is impracticable to download daily.

We thought to make a full backup, download it one time, make differential backups in following days. Once a week, we can stop the database operations to make the last differential backup from last week full backup and make a full backup, and start again the database operations. In this moment, the local bd, when restored with the last diff backup (made when the db was with stopped operations), have the same data than the last week full backup.

The idea behind this is, if is possible to use the diff backup from last full backup and restore it in our local database. If it is not possible, anyone have some idea of what we can do to get all the data, up to date, from our cloud database?


texts:
In my database, the dates are retained as days elapsed since/before 01/01/1970, which I understand to be a Unix date format, eg. today is 23rd May 2018 and the value is 17674.

I'm trying to run a select statement against a column in a date table to show only the year.

If I run this:

datepart(year, c.cko_date)

I get dates that are in the 1940s (they should be the last couple of years).

When I run

select datepart(year, getdate())

it correctly tells me the date is 2018.

Any suggestions about what I'm doing wrong?


texts: How to use COUNT() properly with a 3 table join

texts:
I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database  I need to find the location (or locations, as it could be more than one) that the driver has stopped at most often.And I need to get the driver’s first and last name, the name of the location and corresponding number of stops and torder the result by the number of stops. So far I have come up with following query which gives all the stop locations for every route for every driver.

SELECT DISTINCT
  SAD_DriverAssignment.driver,
  SAD_DriverAssignment.route,
  SAD_Stop.stopNo,
  SAD_Driver.fName,
  SAD_Driver.lName
FROM
  SAD_Stop INNER JOIN SAD_DriverAssignment
  ON SAD_Stop.routeNo = SAD_DriverAssignment.route
  INNER JOIN SAD_Driver
  ON SAD_DriverAssignment.driver = SAD_Driver.nisNo
WHERE
SAD_DriverAssignment.driver IN (SELECT
                                    SAD_Driver.nisNo
                                 FROM
                                    SAD_Driver)
ORDER BY
   SAD_DriverAssignment.driver ASC

I am just ordering here for clarity. But when I used the COUNT() on stopNo all I get is a one result with only one driver. Can you please explain the proper way to do this.

Following are my table structures.

SAD_Driver

CREATE TABLE SAD_Driver (
 nisNo  CHAR(9)     PRIMARY KEY,
 fName  VARCHAR(30),
 lName  VARCHAR(30),
 licenseNo  CHAR(16) NOT NULL,
 dob        DATE ,
 categories     SET ('AM','P','Q','A1','A2','A','B1','B','B+E','C','C+E','C1','C1+E'),
 trainer    CHAR(9),
CONSTRAINT Driver_Trainer FOREIGN KEY (trainer) REFERENCES SAD_Driver(nisNo)
 ON DELETE SET NULL ON UPDATE CASCADE
);

SAD_DriverAssignment

CREATE TABLE SAD_DriverAssignment (
 workday    DATE,
 driver     CHAR(9),
 route  DECIMAL(4,0) UNSIGNED NOT NULL,
 vehicle    CHAR(7) NOT NULL,
CONSTRAINT FK_DriveDayAssign_Driver FOREIGN KEY (driver) REFERENCES SAD_Driver(nisNo)
 ON UPDATE CASCADE,
CONSTRAINT FK_DriveDayAssign_Day FOREIGN KEY (workday) REFERENCES SAD_Workday(theDate)
 ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FK_DriveDayAssign_Vehicle FOREIGN KEY (vehicle) REFERENCES SAD_Vehicle(regNo)
 ON UPDATE CASCADE,
CONSTRAINT FK_DriveDayAssign_Route FOREIGN KEY (route) REFERENCES SAD_Route(routeNo)
 ON UPDATE CASCADE,
CONSTRAINT PK_Assignment PRIMARY KEY (workday,driver),
CONSTRAINT Unique_Day_Vehicle UNIQUE (workday,vehicle),
CONSTRAINT Unique_Day_Route UNIQUE (workday,route)
);

SAD_Stop

CREATE TABLE SAD_Stop (
 routeNo        DECIMAL (4,0) UNSIGNED,
 stopNo         DECIMAL(3,0) UNSIGNED,
 latitude       DECIMAL(19,16),
 longitude      DECIMAL(19,16),
CONSTRAINT PK_Location PRIMARY KEY (routeNo,stopNo),
CONSTRAINT FK_stop_location FOREIGN KEY (latitude,longitude) REFERENCES SAD_Location(latitude,longitude),
CONSTRAINT FK_stop_route FOREIGN KEY (routeNo) REFERENCES SAD_Route(routeNo)
);

SAD_Location

CREATE TABLE SAD_Location (
  latitude  DECIMAL(19,16),
  longitude     DECIMAL(19,16),
  locName   VARCHAR(40),
  CONSTRAINT OK_Location PRIMARY KEY (latitude, longitude)
);


texts: Can't start Oracle after Virtual Machine restore

texts:
As part of a project for a customer of mine, they've given me the complete backup of a physical server (taken by Veeam) which I have then used to make a Bare Metal recovery onto a test VM host I have here (VMWare Workstation Pro)
That's all gone fine and windows starts up with all data intact. I'm having issues getting Oracle going though (yes, this is Oracle running on Windows)
From what I can tell, all datafiles and log files are in place. However when I try to start Oracle, this happens
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size                  2191576 bytes
Variable Size            1.0972E+10 bytes
Database Buffers         5033164800 bytes
Redo Buffers               26554368 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\DB_DATA\LSTEG\SYSTEM01.DBF'
ORA-01207: file is more recent than control file - old control file

EDIT
After speaking with the customer again, they've told me that the DBF / LOG files are screwed by the backup. Their expectation is that the current database be "trashed" and the schemas to be recreated and then the actual database backup dmp files to be reimported
But how do you remove all existing schemas if you can't even start the db?


texts: Are many two column indexes a bad smell?

texts:
I have table that contains failures. (15000 rows + on a abstract embedded system)

A failure has the following fields:

id PK BIGINT
errorNumber INT
source VARCHAR
raisedAt BIGINT
clearedAt BIGINT
cleared (computed column for sorting purposes = clearedat>0) BOOL

My first use case Is that I want to display all failures, the uncleared ones first.

Select * from FAILURES ORDER BY CLEARED asc, raisedAt desc;

Second use case is by a synchronization job. I want to delete all synchronized and cleared failures, there for all failures with an id smaller X and cleared

DELETE FROM FAILURES WHERE ID <? AND CLEARED;

Third use case is that I want to pick a specific uncleared failure.

SELECT * FROM FAILURES WHERE SOURCE=? AND ERRORNUMBER=? AND NOT CLEARED;

This would lead me to the following indexes:

CREATE INDEX CLEARED_RAISEDAT_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,raisedat desc);
CREATE INDEX CLEARED_ID_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,id desc);
CREATE INDEX CLEARED_SOURCE_SORTED_FAILURES ON FAILURE_MANAGER.FAILURES (cleared asc,SOURCE desc,ERRORNUMBER desc);

I sometimes still struggle with index definition. It seems odd to me that I have 3 different indexes, which all only differ in the second column. Relative to my described uses cases, are these indexes feasible or does this create unnecessary overhead? Are there any check up questions that I can ask my self to figure if this might be the correct setting?


texts:
How can I troubleshoot which processes are keeping connections open?

Background: A QA installation of several software components is generating an ORA-12516 error now and then, and I'm not sure which component is at fault.  The DBA raised the value of PROCESSES and things are working fine, but I'd like to understand where the surprising number of connections is coming from.

(Am I keeping some open when I shouldn't?  Is there a rogue process in the QA environment?  Etc.)

Assuming I'm not able to instrument the clients which open up connections, can I interrogate the database or its logs?  If so, how?


texts: extract State Entity from String using regex_substr

texts:
Let's say I have the following column of city, state, and zip values

╔═══════════════════════════════════╗
║ CITY_STATE_ZIP_COLUMN             ║
╠═══════════════════════════════════╣
║ ARLINGTON WASHINGTON 98223        ║
║ MONTGOMERY VILLAGE MARYLAND 20886 ║
║ BROOKLYN NEW YORK 11233-0000      ║
║ CHARLESTON MISSISSIPPI 38921-0000 ║
║ AUGUSTA GEORGIA 30909             ║
║ CLINTON SOUTH CAROLINA 29325-0000 ║
╚═══════════════════════════════════╝

I'm trying to figure out how to write a oracle SQL statement to extract just the state.

I can figure out how extract the ZIP using the following:

SELECT REGEXP_SUBSTR(CITY_STATE_ZIP_COLUMN,'(\d)(\d)(\d)(\d)(\d)')
FROM My_Table

HOWEVER, For the life of me, I can't figure out how I can use REGEXP_SUBSTR to get ONLY the state.

Is there a more efficient way to get the state perhaps using a combination of SUBSTR and INSTR functions?


texts: Join queries are not working as expected when trying to compare a count result with a value

texts:
I'm learning SQL from a book and I'm trying to do some exercices on join queries. The only problem that I'm facing is that all of my join queries are not working while they seem well.

These are the tables:

    students(student_id,student_names,student_age)
    courses_students(course_id,student_id)
    courses(course_id,course_schedule,course_room,teacher_id)
    teachers(teacher_id,teacher_names)

The query is "which courses have more than 5 students enrolled?"

Here is what I've done:

select
  course_name,
  count(select count(*) from courses) as count
from students,courses,courses_students
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id
and count > 5

And the other one is "what are the names of students enrolled in at least 2 courses scheduled for the same hours?"

My query:

select student_name,schedule
from students,courses,courses_students
where students.student_id = courses_students.student_id,
courses.course_id = courses_students.course_id
and count > 2


texts: Installation of SQL Server Express LocalDB 2017

texts:
I want to install Microsoft SQL Server LocalDB 2017 on my system. I downloaded SQL Server Express Setup (about 5.1 MB). Then I opened it and chose 'Download Media' option and then chose LocalDB. Then it downloaded SqlLocalDB.msi
setup (about 45.9 MB). I ran the setup and installed it on my computer. I don't know what to do next. Can someone help me, please?


texts: How do I find out if Sql server Agent's login has rights to a DB?

texts:
How do I find out if the SQL server Agent's login has rights to the target DB, that is the DB on which I will be executing a query ?

Also, how do I find the service that's controlling Agent on the job server and find out the login it's using ?

Is there a query for these things ?

Thanks.


texts: Difference between COUNT(ColName) vs COUNT(ALL(ColName))

texts:
Both query return the same output:

select COUNT(id) from Employee

select COUNT(All(id)) from Employee

So what is the difference in SQL-Server 2008R2?


texts: How to get list of enabled/disabled features of SQLAnywhere

texts: I'm looking to get a list of features, which are installed or enabled/disabled on a given SQLAnywhere instance. dblic is only showing the 'possible' feature based on lice, but not the actually available features of database server ... or is it?


texts:
I have a column with 82 rows(from table1) and I want to update just the 76 rows in it with different values from another table(table2). Avoiding 6 specific rows(1, 10, 38, 39, 46, 77). Leaving these rows with null values.

I used cursor to save all the 76 rows from my table2

Cursor my_cur
 Is
  Select * from table2

Then I tried to update my table1

Begin
 For x in  my_cur loop
   Update table1
   Set my_col = x.acct_num
   Where rnum not in ('1', '10', '38', '39', '46', '77');
end loop;
End;

But I ended up updating these rows with same value(only 1 acct_num to all 76 rows). It seems that the last value(acct_num) from my cursor was the only one im always getting.

I want to enter different acct_num to 76 rows and in the same order as in my table2

I added rownum in both of them to have at least be sure what rows to skip. Any ideas how to do it? Thanks.


texts:
These are the tables I have created and inserted values in it:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO Customer (Customer_Name)
VALUES('Ornob')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD (DVD_Name)
VALUES('Rurouni Kenshin')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,4)

In order to display customer names who did not purchase any DVD by using MINUS operator, this is what I have tried:

SELECT Customer_Name
FROM Customer
WHERE Customer_No
IN
(SELECT Customer_No FROM Customer
 MINUS
 SELECT Customer_No FROM DVD_Purchase
)

And I'm getting the following error message:

  Msg 156, Level 15, State 1, Line 123 Incorrect syntax near the keyword
  'SELECT'. Msg 102, Level 15, State 1, Line 124 Incorrect syntax near
  ')'.

As shown in the error message, Incorrect syntax near ')' which I'm unable to figure out.

Other than that, can someone provide me the correct query?


texts: Got timeout reading communication packets vs Got error reading communication packets

texts:
I felt "Got error reading communication packets" is related to max_allowed_packet variable and "Got timeout reading communication packets" is related 'net_read_timout' parameter or network issues.

Let me know whether my understanding is correct or not.

Thanks in advance.


texts: SSRS bulk permission update 2008R2 to 2014

texts:
I'm moving SSRS reports from a 2008R2 server to a 2014 server.  I've moved all the RDLs with a 3rd party tool only to find none of the permissions exist on the target.  I'm attempting to copy the permissions over with Powershell but the script errors when attempting to set the policy with .SetPolicies.  The object members appear to be incompatible between 2008 R2 and 2014.  Is it possible to move the perms from 2008R2 to 2014 via Powershell?

$ReportServerUriSource =
'http://my2008Source/ReportServer/ReportService2010.asmx?wsdl'
$ReportServerUriTarget =
'http://my2014target/ReportServer/ReportService2010.asmx?wsdl'
$InheritParent = $true

 $rsProxy =       New-WebServiceProxy -Uri $ReportServerUriSource -
 UseDefaultCredential
$rsProxyTarget = New-WebServiceProxy -Uri $ReportServerUriTarget -
UseDefaultCredential

$SourceItems = $rsProxy.ListChildren("/MyReports", $true) | `
     SELECT TypeName, Path, ID, Name

$targetFolder = $rsProxyTarget.GetPolicies($SourceItem.Path,
  [ref]$InheritParent)

foreach($SourceItem in $SourceItems)
{
$Policies = $rsProxy.GetPolicies($SourceItem.Path, [ref]$InheritParent)

  if($InheritParent -eq $false)
 {
        $Policy = $rsProxy.GetPolicies($SourceItem.Path,
[ref]$InheritParent)
        $rsProxyTargetPath = $rsProxyTarget.GetPolicies($SourceItem.Path,
[ref]$InheritParent)
        if($rsProxyTargetPath)
        {
          $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)

           }

     }

}

Error:

Cannot convert argument "Policies", with value: "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy[]",
for "SetPolicies" to type "Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy[]": "Cannot convert the
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" value of type
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1er_ReportService2010_asmx_wsdl.Policy" to type
"Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy3er_ReportService2010_asmx_wsdl.Policy"."
At line:21 char:15
+               $rsProxyTarget.SetPolicies($SourceItem.Path, $Policy)
+               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument


texts: Redesign basic Mysql storage architecture

texts:
We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this:

capture_table
----------
client_id
date
time
data1
...
datan
---------

The data is always queried by date.

After the tables were getting too big, we decided to use several tables to divide the data.
We created three PHP scripts that ran every few minutes dividing the original data into 3 tables (MyISAM) per month and creating an index table.

index_table
data_table_2014-01-1
data_table_2014-01-2
data_table_2014-01-3
...
...

Currently we have this structure and about 2.5 million of rows per table for our current number of clients, and growing (DB weights about 15GB).
The queries are done by joining the data from all the tables involved between two dates using UNION ALL, always searching for date and client. Example, search between 2014-01-01 and 2014-01-15:

(SELECT * FROM data_table_2014-01_1 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_2 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_3 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')

Is this the better approach? Could this be achieved in a better way? I was thinking of table partitioning for example...

Thanks a lot.


texts:
I want to take MCSA: Sql Server 2016 Database Administration certification.
I checked this link :

https://www.microsoft.com/en-us/learning/mcsa-sql2016-database-administration-certification.aspx

It says, I have to attend 70-464 and 70-465 exams. Is there any other precondition? I couldn't find clear certification path.

PS: I never attend certifications exam before. This will be my first.

Thanks.


texts: How to set processes parameter in oracle 11g Windows in accordance with RAM in the server

texts:
Could anyone explain me whether there is any limitation in setting processes parameter in Oracle 11gr2 and how to set sga_target according to this.

Right now I have set the processes to 4000 and I have around 300 to 400 users using the database.

My server is running using Win2012 R2 and 32GB of RAM.

Any help will be appreciated.


texts: Postgres - Partition pruning and query performance

texts:
I created a fact table with range partition on a date column and when a SQL statement is executed like this:

 Select *
 from my_table
 Where dt_col between to_date('01/01/2017','mm/dd/yyyy')
                  and to_date('01/31/2017','mm/dd/yyyy')

is taking more time but the same SQL with different format on the date filter (as below) is taking less than the above SQL

Select *
from my_table
Where dt_col between date '01/01/2017'
                 and date '01/31/2017'

and I do see some changes in the cost of the plan even though both SQLs hitting the same partition on the table. Can you explain what's wrong in the first SQL?.

When the same statement is executed thru' DBeaver or PG Admin it completes under 10 mins (returning 20+ Million records) but when it's being executed thru back end process using the JDBC (extracting the data from the table and writing to a file in the server) it's running for more than 2 hours and never completes (had to kill the process).

Any settings need to be changed in the database?.


texts: Database file location change on standby site after restore standby database

texts:
I am configuring Log shipping from SQL Server 2008R2 to 2016 using NORECOVERY option with Log Shopping jobs. I have successfully configured Backup, Copy and Restore jobs.

When I perform a restore on the standby database, then its Database file location changes on standby site.

I was creating a standby database at D:\<FOLDER>, but after restoring the database on 2016 using Restore -> Database, the location changed to
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA.