texts:
I have a domain where I deal with data which has a parent/child relationship, of arbitrary depth.

Also, full time traversal needs to be enabled, to show the state of the data at a specific point in time.

Currently I am looking at two different types of databases, and was curious as to which would be more appropriate, or how I would overcome some issues in either of the solutions.

Any links to articles regarding this information would also be appreciated.

Solution 1: rdbms

good fit for time versioning with a solution like temporal tables in sql server (although the db needs to be open source and free, so I would have to add this to something like PostgreSQL)
not that great a fit for arbitrary hierarchical data; need to implement adherent list/nested set

Solution 2: graphdb

natural fit for hierarchical data
I don’t know a proper way to model time versioning in a way which is performant.

So I am looking on some feedback on the advantages/disadvantages of either database type, and how to overcome some of the shortcomings of either.

I personally was leaning towards an graphdb solution, where I add a start_time and end_time attribute to all nodes and relationships, but I am not sure about the performance and if there are any better ways to get time versioning.

The two main considerations are of course performance and simplicity of the query.

I realize this is a rather open question; I am merely looking to see if I am overthinking this or perhaps fail to take some other positives/negatives inti account.


texts:
In case of async commit setup and the backup preference set to secondary replica.
I have observed, in case of high unsent log size, log backup on secondary replica starts failing for that database with error:

  Connection With Primary Replica Not Active

The unsent log size was ~4GB. There was no connectivity issues.

What factors impact log backups in multiple secondaries and async commit mode?


texts: Speeding Up Query on a Time Series Table. It's Slow to get (MAX - MIN) Aggregation Value

texts:
Question

How to improve this query to increase the speed of the query shown below from 30+ seconds to milliseconds? I am using PostgreSQL: v 9.6.6.

Context

I have a time series table buildings.hispoint that stores historical data for data points in a table buildings.point.

I need to aggregate the Max(value) - Min(value) as aggregation_value for different time ranges (e.g. year to date) for a large collection of data points.

This proves to be very slow and needs improvement.

Table Structure of buildings.hispoint (20,210,129 rows)

CREATE TABLE buildings.hispoint (
    id int,
    value float,
    datetime timestamp,
    point_id …
    CONSTRAINT foo FOREIGN KEY (point_id)
    REFERENCES buildings.point (point_id),
    …
);

Query

SELECT COUNT(datetime) AS id,
MAX(value) - MIN(value) AS aggregation_value
FROM buildings_hispoint
WHERE point_id = 44
AND buildings_hispoint.datetime BETWEEN '2018-01-01 00:00:00' AND '2018-05-02 09:18:14';

QUERY PLAN

Aggregate  (cost=160967.11..160967.12 rows=1 width=16) (actual time=21713.720..21713.720 rows=1 loops=1)
  Buffers: shared hit=7817 read=66145 dirtied=1
  ->  Bitmap Heap Scan on buildings_hispoint  (cost=5705.82..159708.13 rows=167864 width=16) (actual time=161.733..21585.478 rows=19783 loops=1)
        Recheck Cond: (point_id = 44)
        Rows Removed by Index Recheck: 3474664
        Filter: ((datetime >= '2018-01-01 00:00:00+00'::timestamp with time zone) AND (datetime <= '2018-05-02 09:18:14+00'::timestamp with time zone))
        Rows Removed by Filter: 306305
        Heap Blocks: exact=46580 lossy=26488
        Buffers: shared hit=7817 read=66145 dirtied=1
        ->  Bitmap Index Scan on buildings_measurementdatapoint_ffb10c68  (cost=0.00..5663.85 rows=306589 width=0) (actual time=139.360..139.360 rows=326088 loops=1)
              Index Cond: (point_id = 44)
              Buffers: shared read=894
Planning time: 40.504 ms
Execution time: 21717.750 ms


texts: Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

texts:
I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the Query Plan Hash.

Usually I can use RedGate's SQL Search on the query text to find the entity I'm looking for but that was a no-go in this case.
Unfortunately that was a day ago when the trace ran, and when I check the sys.dm_exec_cached_plans DMV, nothing is turning up for my plan handle.

Are there any other ways I can backtrace this query?

Update: Here's how the Query Store is currently configured on the database in question. Note sure if it can be useful for my case?


texts: Using SQL Server native backup and Veritas Backup Exec to backup the database and transaction log on the same database

texts:
I have a customer who use the Microsoft SQL Server native backup and Veritas Backup Exec to backup the database and transaction log on the same database.
The native backup runs at 5 AM while the Veritas Backup Exec runs at 12 AM and 12 PM. Can the same database be backup by two different backup software and restored correctly?
Sorry if I have missed out some facts.


texts: What is the correct way to present the disks to ASM?

texts:
Hello I would like to know which is the correct way ... or the most recommended way to present the disks to ASM, for example: I have 3 disks mapped to a server (rhel7) orasys, orafra, oradata, with dm-multipath I configure the multipath and I put aliases (orasys, orafra and oradata respectively). The doubt I have from there, I have read in the guides, that the disks are formatted, leaving for examples: orasys1, orafra1, oradata1 and these are the ones that are presented to ASM as /dev/mapper/* and with the respective permissions ... but when I do it that way it gives an error finishing the installation of the grid because it cannot create the diskgroup, however if I do not format them, that is, I present them directly as orasys, orafra and oradata I have no problem.
The database is oracle 19c and I don't use asmlib but udev rules.
Thanks.


texts: Run a program on a remote computer after a SQL job has completed

texts:
We have a SQL Agent Job that performs a few tasks every night (schedulled to run using SQL Agent). The database server is SQL 2012 if that makes a difference.

We have another process (a .net program) that needs to run on a different server after the job has finished on each server. The program cannot be installed on the database server.

Is there a way to get a SQL Agent Job to run a program on another computer after it has finished, or is there an easy way to indicate to a program on another computer that the job has finished?


texts:
I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id.

The attributes date, and entity_id would make a unique pair.

Data is made into range partitions on the date field across several tablespaces.  However, the script to create the index is a normal CREATE INDEX script which does not mention any tablespace or partition name.

I would like to create a combined index on both columns as the queries could use both columns, however the index data file exceeds disk space availability.  I am not sure of the choice of index type.

I do not want to touch the existing index.

create table interval_date
(
  person_id   NUMBER(5) NOT NULL,
  last_name   VARCHAR2(30),
  dob         DATE
)
partition by range (dob)
INTERVAL(numtoyminterval(1,'MONTH'))
STORE in (uwdata)
(
  partition p1 values LESS THAN (to_date('2008-03-15','YYYY-MM-DD'))
)


texts: Are implicit joins as efficient as explicit joins in Postgres?

texts:
It's often convenient to write:

SELECT *
FROM t1   # ... +many more tables
INNER JOIN t2 ON (t1.id = t2.col)
INNER JOIN t3 ON (t1.id = t3.col)
INNER JOIN t4 ON (t1.id = t4.col)
...

as a cross join with conditionals:

SELECT *
FROM t1, t2, t3, t4   # ... +many more tables
WHERE
       t1.id = t2.col
   AND t1.id = t3.col
   AND t1.id = t4.col
   # +include matches on columns of other tables

However, a naive implementation of a cross join would have a higher time complexity than the inner join. Does Postgres optimize the second query into one with the same time complexity as the first?


texts: Root cause for Invalid arguments in call (ORA-17068)

texts:
We are getting Invalid arguments in call when we try to establish the connection with Oracle DB occasionally ( One hour in a day).

I did some research about this in Internet, still i am not getting clear understanding about the root cause of this exception.

Please note:

This is happens in PROD environment for sometime, after which its getting resolved without any code fix, the major concern here for us is that we don't have a clear understanding on its root cause.

Question 1

Invalid arguments in call :  is this message talking about the connection parameters that is sent to establish connection?

Question 2
If question 1 is true, why is it happening occasionally? Its highly unlikely that our application sends different parameter for establishing connection as its a binary (Connection parameters are constant).

NOTE: Documentation for ORA-17068 this error code is not available in oracle site

EDIT:

Complete Exception trace:

java.sql.SQLException: invalid arguments in call
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:236)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.apache.tomcat.dbcp.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(DriverAdapterCPDS.java:205)
    at org.apache.tomcat.dbcp.dbcp.cpdsadapter.DriverAdapterCPDS.getPooledConnection(DriverAdapterCPDS.java:150)
    at org.apache.tomcat.dbcp.dbcp.datasources.InstanceKeyDataSource.testCPDS(InstanceKeyDataSource.java:836)
    at org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource.registerPool(PerUserPoolDataSource.java:492)
    at org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource.getPooledConnectionAndInfo(PerUserPoolDataSource.java:382)
    at org.apache.tomcat.dbcp.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:701)
    at org.apache.tomcat.dbcp.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:676)

Thanks in advance.


texts: Getting 0KB of size file while backup mysql database using mysql dump

texts:
I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions?

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname='mydatabase';
$toDay = date('d-m-Y');
    //exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost --single-transaction $dbname > D:\Sql_Backups/".$toDay."_DB.sql");
    //exec('mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname > D:\Sql_Backup/file.sql');
      exec ("mysqldump --routines --h $dbhost --u $dbuser --p $dbpass --single-transaction $dbname > D:\Sql_Backup/db3_backup.sql" >


texts: MySQL user is not expired but asking for Password RESET


texts: Deleting from multiple tables with foreign constraints in mysql

texts:
I have three tables as seen below.

what my requirement is, when I delete a row from a donate_club table, I need to delete all the entries related to this row from 'donationRequest' and 'committments'. Here 'commitments' table has a foreign key relation ship with 'donatinRequest' table. So when I delete an entry from 'donationRequest' table the corresponding entries are being deleted from 'committments' table also. That works fine. Also 'donationRequest' table has a foreign key relationship to 'donate_club' table, so when I delete an entry from 'donate_club' it also deletes related entries from 'donationRequest' table. It is also working fine. Now my requirement is when I delete an entry from 'donate_club', I need to delete all the related entries from both 'doantionRequest' and 'commitments' table.

Now the situation is, when I delete a row from 'donate_club', it automatically deletes related entries from 'donationRequest' table but it doesnt delete anything from 'committments' table eventhough 'committments' table has an entry related to the deleted row from 'donationRequest' table

EDIT:
donate_club table create query

CREATE TABLE `donate_club` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstName` varchar(100) DEFAULT NULL,
 `secondName` varchar(100) DEFAULT NULL,
 `email` varchar(100) DEFAULT NULL,
 `password` varchar(20) DEFAULT NULL,
 `mobile` varchar(20) DEFAULT NULL,
 `bloodGroup` varchar(10) DEFAULT NULL,
 `age` varchar(10) DEFAULT NULL,
 `gender` varchar(20) DEFAULT NULL,
 `country` varchar(50) DEFAULT NULL,
 `location` varchar(50) DEFAULT NULL,
 `latitude` varchar(50) DEFAULT NULL,
 `longitude` varchar(50) DEFAULT NULL,
 `profilePicFIleName` varchar(100) DEFAULT NULL,
 `profilePicURL` text,
 PRIMARY KEY (`id`),
 UNIQUE KEY `mobile` (`mobile`),
 UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8

donationRequest create query

CREATE TABLE `donationrequest` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `latitude` varchar(50) DEFAULT NULL,
 `longitude` varchar(50) DEFAULT NULL,
 `mobile` varchar(15) DEFAULT NULL,
 `contactPerson` varchar(50) DEFAULT NULL,
 `bloodGroup` varchar(10) DEFAULT NULL,
 `bloodBank` varchar(200) DEFAULT NULL,
 `location` varchar(100) DEFAULT NULL,
 `postedDate` varchar(30) DEFAULT NULL,
 `userID` int(11) DEFAULT NULL,
 `fcmToken` varchar(1000) DEFAULT NULL,
 `requiredDate` varchar(20) DEFAULT NULL,
 `need` varchar(20) DEFAULT NULL,
 `unit` varchar(5) DEFAULT NULL,
 `patient` varchar(50) DEFAULT NULL,
 `status` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `userID` (`userID`),
 CONSTRAINT `fk_to_donate_club_id` FOREIGN KEY (`userID`) REFERENCES `donate_club` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=332 DEFAULT CHARSET=utf8

committments create table query

CREATE TABLE `commitments` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userID` varchar(50) DEFAULT NULL,
 `fcmTockenPatient` varchar(10000) DEFAULT NULL,
 `fcmTockenDonor` varchar(10000) DEFAULT NULL,
 `committedDate` varchar(15) DEFAULT NULL,
 `patientName` varchar(100) DEFAULT NULL,
 `byStanderName` varchar(100) DEFAULT NULL,
 `byStanderMobile` varchar(15) DEFAULT NULL,
 `bloodGroup` varchar(5) DEFAULT NULL,
 `location` varchar(100) DEFAULT NULL,
 `hospital` varchar(100) DEFAULT NULL,
 `requestID` int(11) DEFAULT NULL,
 `isFinished` tinyint(2) DEFAULT NULL,
 `receiverID` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `requestID` (`requestID`),
 CONSTRAINT `fk_to_donation_request` FOREIGN KEY (`requestID`) REFERENCES `donationrequest` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8


texts: Update using NonClustered index results in large number of logical reads?

texts:
While doing some debugging of a frequently called stored procedure, I was testing adding a column to one of the nonclustered indexes to make it cover, but noticed that doing so would dramatically increase the number of logical reads the stored procedure performed (compared to when it used a clustered index scan). Running multiple iterations of the same stored procedure in parallel also resulted in worse CPU time. I was able to reproduce this behavior with multiple builds of SQL Server 2012. Can anyone explain why this is happening?

Example table

CREATE TABLE dbo.TestTable (
    ID INT PRIMARY KEY IDENTITY,
    Condition BIT NOT NULL,
    OtherColumn INT NOT NULL
);

CREATE NONCLUSTERED INDEX NC ON dbo.TestTable(Condition);

-- Generate 1000 rows with Condition = 0 and 1000 with Condition = 1
DECLARE @i INT = 0;
WHILE @i < 1000
BEGIN
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (0, @i);
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (1, @i);
    SET @i = @i + 1;
END;

Example update

UPDATE dbo.TestTable SET OtherColumn = 1 WHERE Condition = 1;

Results with the nonclustered index enabled (nonclustered index seek) - Paste The Plan

  Table 'TestTable'. Scan count 1, logical reads 2005, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.

Results with the nonclustered index disabled (clustered index scan) - Paste The Plan

  Table 'TestTable'. Scan count 1, logical reads 7, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0.


texts:
I'm trying to write a small program that is meant to connect to a database over TCP/IP using raw sockets with Python. I can't open a connection so tried to see what's happening on PostgreSQL's side but I can't get logging to work.

So, how can I enable connection logging? If logging is not possible how can I debug connection attempts in the database server?

Update

I tried to open a connection using netcat but it always gets closed, I still don't know why.

$ nc -vvv 127.0.0.1 5432
localhost [127.0.0.1] 5432 (postgresql) open
user=myuser
sent 17, rcvd 0


texts: Are heaps considered an index structure or are they strictly a table structure without index?

texts:
Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505

Heaps: Are they considered an index structure or are they strictly a table structure without index?


texts: Msg 823, Level 24, State 2 for Clustered Column Store Index in SQL Server 2014

texts:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TempTable1 ON dbo.TempTable1 ON[PRIMARY]

After firing this query I found the error.

Msg 823, Level 24, State 2, Line 20
The operating system returned error 1(Incorrect function.) to SQL Server during a read at
offset 0x0000018dc18000 in file 'D:\SQL2014\Data\TempDB.MDF'. Additional messages in the
SQL Server error log and system event log may provide more detail. This is a severe
system-level error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be
caused by many factors; for more information, see SQL Server Books Online.


texts: Install Developer Edition ASE in Vagrant/Virtual Box

texts:
It is possible to deploy a development version of Sybase in a Vagrant/Virtualbox instance?  I was planning on using a CentOS vagrant box to create a development environment but am not sure if it's even feasible to run ASE in a virtual environment.

If possible, are there any caveats to be aware of?


texts: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

texts:
Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD.
I referred below mentioned threads, but issue is still not resolved:

  https://stackoverflow.com/questions/5661610/tns-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

  https://stackoverflow.com/questions/3267850/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

Contents of my tnsnames.ora:

ORCL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=localhost)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl)
    )
  )

Output of 'lsnrctl services':

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-FEB-2012 23:53:33

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

All my oracle services are running. I tried to start database using startup command:

C:\Users\PPPP>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 15 23:56:59 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 214624
Session ID: 5 Serial number: 3

In listener.log file, I see this error:

Thu Feb 16 00:13:28 2012
16-FEB-2012 00:13:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)*            (USER=PRITHVIRAJ-PC$))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)    (HOST=127.0.0.1)(PORT=59161)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in     connect descriptor

Please help me to sort out this issue.


texts: SQL Server 2019 Always On using Distributed Network Name

texts:
We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When we set up the cluster, it was set up to use a Distributed Network Name instead of a static cluster IP address. Thanks to this we shouldn't need an internal load balancer according to these notes: https://github.com/MicrosoftDocs/azure-docs/issues/34648.

I'm struggling to understand exactly how this works though. Based on what I read, it seems like our connection strings will point to the DNS name of the cluster (let's call it AgCluster). If I look in DNS, there is an A record for AgCluster pointing to sql1 and another pointing to sql2. When I use AgCluster in my connection string it seems to always connect me to the primary server, even if I have ApplicationIntent=ReadOnly set. When I query @@SERVERNAME I always get the same server.

So with the Distributed Network Name setup, what should I use in my connection strings to make sure read/write queries go to the primary and read only go to a secondary? Any guides on setting this up in general would be helpful. Thanks!


texts: I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.


texts:
I have configured GoldneGate and Extract, Data pump and Replicat everything is working fine. However Update is not happening. Below are my Extract, Data Pump and Replicat file.

Extract File:

extract ext3
userid ogguser, password ogguser
exttrail /u01/app/oracle/product/11.2.0.4/ggsrc/dirdat/lt
DDL INCLUDE MAPPED
table gg01.t2;

Data Pump File:

extract dpump1
userid ogguser, password ogguser
rmthost targetGG, mgrport 7810
rmttrail /u01/app/oracle/product/11.2.0.4/ggtrg/dirdat/rt
passthru
table gg01.t2;

Replicat File:

replicat rep3
assumetargetdefs
userid ogguser, password ogguser
map gg01.t2, target gg01.t2;

Let me know, what parameters need to be configured to get update working.

Error which i get:

2016-11-28 21:45:32  WARNING OGG-01004  Aborted grouped transaction on 'GG01.T2', Database error 1403 (OCI Error ORA-01403: no da
ta found, SQL <UPDATE "GG01"."T2" x SET x."NAME" = :a4,x."ADDRESS" = :a5,x."AGE" = :a6,x."GENDER" = :a7 WHERE x."NAME" is NULL AN
D x."ADDRESS" is NULL AND x."AGE" is NULL AND x."GENDER" = :b3 AND ROWNUM = 1>).

2016-11-28 21:45:32  WARNING OGG-01003  Repositioning to rba 7748 in seqno 5.

2016-11-28 21:45:32  WARNING OGG-01154  SQL error 1403 mapping GG01.T2 to GG01.T2 OCI Error ORA-01403: no data found, SQL <UPDATE
 "GG01"."T2" x SET x."NAME" = :a4,x."ADDRESS" = :a5,x."AGE" = :a6,x."GENDER" = :a7 WHERE x."NAME" is NULL AND x."ADDRESS" is NULL
 AND x."AGE" is NULL AND x."GENDER" = :b3 AND ROWNUM = 1>.

Thanks!


texts: `t2.t1_id IN (select t1.id from t1 ...)` is slower then `t2.t1_id IN (1,2,3,...,150)`

texts:
Query with subquery is much slower:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN
    (SELECT `t1`.`id`
     FROM `t1`
     WHERE `t1`.`site_id` = 5978)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 9 seconds

subquery

SELECT `t1`.`id` FROM `t1` WHERE `t1`.`site_id` = 5978

returns 150 ids like: 1, 2, 3, ... 150

without subquery:

SELECT COUNT(*)
FROM `t2`
WHERE `t2`.`t1_id` IN (1, 2, 3, ..., 150)
  AND (t2.created_at >= '2016-02-13 08:00:00')
  AND (t2.created_at <= '2016-02-14 07:59:59');

returns 34038 in 0.1 seconds

EXPLAIN for query with subquery:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_t1_on_site_id_and_created_at
          key: index_t1_on_site_id_and_created_at
      key_len: 4
          ref: const
         rows: 158
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 4
          ref: production.t1.id
         rows: 622
     filtered: 11.11
        Extra: Using where; Using index

EXPLAIN for query without subquery:

  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: range
possible_keys: ...,index_t2_on_t1_id_and_and_created_at,...
          key: index_t2_on_t1_id_and_and_created_at
      key_len: 9
          ref: NULL
         rows: 68239
     filtered: 100.00
        Extra: Using where; Using index

Tables:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
...
  PRIMARY KEY (`id`),
  KEY `index_t1_on_site_id_and_created_at` (`site_id`,`created_at`)
)

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
...
  PRIMARY KEY (`id`),
...
  KEY `index_t2_on_t1_id_and_type_and_created_at` (`t1_id`,`created_at`),
)

MySQL 5.7.11 (the same behaviour in 5.6.23)

Is there any way to make the first query faster?


texts: Display two digits after decimal point in SQL from average value

texts:
This is my code

SELECT  AVG(`page_rate` )
FROM `pages`
   INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id`
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

How can I display only 2 digits after decimal point?

I tried this:

SELECT AVG( cast(`page_rate` as decimal(10,2)))
FROM `pages`
    INNER JOIN `languages` ON `pages`.`lang_id`=`languages`.`id`
WHERE `abbr` IN ("EN-UK", "EN-US", "EN-AU")

but result is 20.166667 - not only 2 digits


texts: What is the command to get the name of tablespace of a given table in DB2 database ?


texts: Primary replica set server goes secondary after secondary fails

texts:
I have a 2 servers replica set that, after the secondary fails the primary goes into secondary mode while the secondary is in STARTUP2 (recovering). The problem with this is that I can't use the collection stored in that replica set freely, I'm getting errors trying to use the collection:

pymongo.errors.OperationFailure: database error: ReplicaSetMonitor no master found for set: rs2

Sometimes if I restart the mongod instances, the server rs2-1 is the primary for a while, but after some time (while the secondary is recovering) I see this in the logs of rs2-1 (the primary):

Tue May  7 17:43:40.677 [rsHealthPoll] replSet member XXX.XXX.XXX.XXX:27017 is now in state DOWN
Tue May  7 17:43:40.677 [rsMgr] can't see a majority of the set, relinquishing primary
Tue May  7 17:43:40.682 [rsMgr] replSet relinquishing primary state
Tue May  7 17:43:40.682 [rsMgr] replSet SECONDARY
Tue May  7 17:43:40.682 [rsMgr] replSet closing client sockets after relinquishing primary

Is there an easy way to make the primary keep being primary after the secondary fails? Am I doing something wrong?

Thanks in advance!


texts: Auto Increment Index Field For Create and Update

texts:
I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created or updated) records. Something that would allow:

Insert Record A (Record A 'touched' set to '1')
Insert Record B (Record B 'touched' set to '2')
Update Record A (Record A 'touched' set to '3')
Update Record B (Record B 'touched' set to '4')

I'm using both Postgresql and Sqlite (production and development) and would ideally like a solution that will work under both. What is the best way to add something like this?

Thanks!


texts: sp_BlitzCache gives a "divide by zero error encountered."

texts:
After updating to First Responders Kit 20180901, I have a job that runs every 15 minutes and calls sp_BlitzFirst:

EXEC [DBA Maintenance].[dbo].[sp_BlitzFirst]
    @OutputDatabaseName = 'DBA Maintenance',
    @OutputSchemaName = 'dbo',
    @OutputTableName = 'BlitzFirst',
    @OutputTableNameFileStats = 'BlitzFirst_FileStats',
    @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
    @OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
    @OutputTableNameBlitzCache = 'BlitzCache',
    @OutputTableRetentionDays = 10;

After updating the SQL Scripts to First Responders Kit 20180901, the Job keeps failing and when I run the Step SQL manually I get below messages:

. . .
Populating Warnings column
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 3972
Divide by zero error encountered.
The statement has been terminated.
Populating Warnings column for stored procedures
Msg 8134, Level 16, State 1, Procedure sp_BlitzCache, Line 4047
Divide by zero error encountered.
. . .
Writing results to table.
sp_BlitzCache Finished

I think the error is happening for the sp_BlitzCache CASE lines

CASE
    WHEN   CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10.
    THEN    ', High Compile Memory'
    ELSE    ''
END

When I SELECT the values in the ##bou_BlitzCacheProcs table, MaxCompileMemory is either NULL or 0 (Zero).

Anybody else having this issue?

Is there a SQL Setting I need to look at?


texts: How to delete rows from relational database

texts:
So I have database with two tables. and they have foreign key relation between them. I want to do some testing but the tables are so big. I want to delete most of the data in these tables and just keep around 500 rows in the first table and the related rows in the second table so testing will be faster. How can I do that?

Database structure:


texts: When should I think about upgrading our RDS MySQL instance based on memory usage?

texts:
It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not a DBA and have no frame of reference. It seems to be doing garbage collection once every 2-3 days now whenever there's only 100mb left.

The server itself has 1.7GB of RAM.


texts: Mariadb - Variable 'innodb_log_file_size' is a read only variable

texts:
Im using mariadb 10.0.29, I need to change the innodb file size to 256M, default size 50M,

My config file is located on

/etc/mysql/mariadb.conf.d/50-server.cnf

I have added the below line, but it won't work.

[mysqld]
innodb_log_file_size=256M

Also I tried set innodb_log_file_size=5033164800;

But it shows

ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable


texts:
I'm looking for a SQL Server function that will let me take an average, excluding outliers. Basically something where:

1, 2, 1, 2, 7

Would average out to 1.5.

I'm going to implement this in a financial analysis Microsoft SQL script.

My query is way too long and complicated to fit here, and it's not something I want to post publicly. AVG doesn't look like it has an option to exclude outliers per se, though it does include a PARTITION BY.


texts: DBeaver: connecting to a PostgreSQL database server through SSH: can't initialize tunnel, auth fail

texts:
I am trying to use DBeaver to connect to a PostgreSQL database server through SSH:

can't initialize tunnel
cannot establish tunnel
com.jcraft.jsch.JSchException: auth fail

I am sure the SSH server is working fine. I manage  to connect to the same PostgreSQL database server through the same SSH server with pgAdmin 3 and RazorSQL. I have checked the IP, username and password, they are correct. I disabled all firewalls. What could be the issue?

My settings:

I use DBeaver 3.7.3 x64 portable version with Windows 7 SP1 x64 Ultimate.

The error log window does not log the error, so I don't have any more information there.


texts: Create a postgres user role that can't access a specific table

texts:
I have a postgres 9.6 database with many tables. I need to create a role that doesn't have access to one of these tables.

I think I need to do a Grant Permissions, but I haven't been able to find anything to exclude a table.

CREATE ROLE noaccess_role;

But that is as far as I've got.


texts: T-SQL to find Database name and if Filestream is enabled for specific Database

texts:
Please help me on below query .

NOTE :  Please excuse my programming skills , the code is of mistakes .

I want the output like Database name in one column and the FileStream enabled or disabled in another column . Just 2 columns I am trying out for . Thanks

To find the Database name and if FileStream is enabled for the Database .

declare @cmd varchar(500)
set @cmd='USE ?
if exists(
 select name, Filestream from sys.database_files where type_desc='FILESTREAM')
 CASE type_desc WHEN 'FILESTREAM' THEN 'Filestream Configured For Datbase' ELSE 'Filestream NOT Configured For Datbase'
PRINT 'Filestream Configured For Datbase'
else
 print 'Filestream Not configured for database'
inner join sys.databases where name not in (''master'', ''tempdb'', ''model'', ''msdb'')')
exec sp_MSforeachdb @cmd


texts: merge 3 mysql tables into one with no duplicates


texts: How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus?

texts:
Being new to Oracle, I'm almost certain I'm missing something simple here.  This is what I'm trying to run:

declare
type string_table IS TABLE OF VARCHAR2(512);
outuser nvarchar2(512);
outrole nvarchar2(512);
result number(38);
usernames string_table;
rolenames string_table;
begin
usernames(1) := 'Administrator';
rolenames(1) := 'Admins';
result := ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES('/', usernames, 1, rolenames, 1, outuser, outrole);
END;

This is the error:

ERROR at line 11:
ORA-06550: line 11, column 11:
PLS-00306: wrong number or types of arguments in call to
'ORA_ASPNET_UIR_ADDUSERSTOROLES'

This is the output of DESC for the function:

FUNCTION ASPNETDB.ORA_ASPNET_UIR_ADDUSERSTOROLES RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 APPLICATIONNAME_               NVARCHAR2               IN
 USERNAMES_                     TABLE OF VARCHAR2(512)  IN
 USERNAMES_ARR_SIZE             NUMBER(38)              IN
 ROLENAMES_                     TABLE OF VARCHAR2(512)  IN
 ROLENAMES_ARR_SIZE             NUMBER(38)              IN
 USERNAME                       NVARCHAR2               OUT
 ROLENAME                       NVARCHAR2               OUT


texts: Catching slow queries with prepared statements

texts:
I use extended events to identify slow queries, defined as queries taking more than 2 seconds to complete, like this:

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
    (
    ACTION
        (
        sqlserver.client_hostname,
        sqlserver.sql_text,
        sqlserver.database_id,
        sqlserver.tsql_stack,
        sqlserver.username
        )
    WHERE
        (
        duration > 2000000
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
    SET filename='D:\SlowQueries.xel'
    )
WITH
    (
    MAX_MEMORY=256 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    );

However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time. How do I expand my SlowQueries to include statements executed via RPC:Completed?
I am 100% sure that slow queries are happening, but not reported by my event.


texts:
A small MySQL 5.5.58 Ubuntu test database was setup with a constraint of 5MB.  It consists of a single table:
CREATE TABLE IF NOT EXISTS scanpoint (
         xxx      CHAR(6)              NOT NULL DEFAULT '',
         YYYYYYY  CHAR(2)              NOT NULL DEFAULT '',
         ZZZZZZ   SMALLINT UNSIGNED    NOT NULL DEFAULT 0,
         AAAAAA   CHAR(3)              NOT NULL DEFAULT '',
         BBBBBB   TINYINT UNSIGNED     NOT NULL DEFAULT 0,
         CCCCCC   TIMESTAMP
       );

I would like to estimate the records capacity of a 5MB database:
CHAR:       11 bytes =6+2+3
SMALLINT:    2 bytes
TINYINT:     1 byte
TIMESTMAP:  7 bytes?

TOTAL        21 bytes per record  => 2.38E5 records = 5MB /21 bytes/rec
QUESTION: Is there any consideration / logic that would adjust the calculation to sharpen the estimate?
UPDATE
xxx records will always have 6 characters.  In similar fashion,  the same other character arrays follow suit.  YYYYYY will always have 2 charaters and AAAAAA will always have 3 characters


texts: How do I handle a database for a form who's structure will likely change in the future?

texts:
I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem.

User Table:

userID
firstName
lastName
phone
email

Form Table currently contains:

userID
question1
question2
question3

I'm struggling with the fact that, due to the marketing nature of this tool, the form structure and fields are almost guaranteed to change sometime in the future. There will likely be fields added/subtracted in the future. Any changes in the future are completely unknown now but could become something to the extent of:

Form Table could change to:

userID
question1
question3
questionA
questionB

Where question2 was removed and two completely new fields were added.

Should each "version" of the form have its own table or just add columns to the existing table as the form changes? Are there better ways to handle this?


texts: SQL Server: Why would an ALTER TABLE to add nullable column run forever?

texts:
SQL Server 2014 Std Edition

We add columns all the time, and the ALTER TABLE commands return instantly.

E.g.

alter table [dbo].[Account] add ChildAccount nvarchar(100) NULL
GO

But at the moment, this alter table seems to run forever.

What would cause this?


texts: What are the performance benefits of segregating database objects into different tablespaces?

texts:
As the question states, what are the benefits of doing this given the following information/constraints:

I will not be using partitioning
I will not be placing the tablespaces and its datafiles into different hard disk drives
My schema has lots (maybe around 50 to 100) of small tables and there are only about 5 to 10 tables that will grow in terms of number of records
Expected growth of these 5 to 10 tables can reach up to 100k rows but will not probably reach a million rows

I am planning to segregate these big tables into their own tablespaces instead of mixing it with the tablespace used for the rest of the tables and indexes. I am also planning to place all indexes on a separate tablespace, so the list of tablespace that my user will be using should look something like this:

Tablespace_BIG_Table_1...10
Tablespace_for_indexes
Tablespace_for_the_rest_of_the_tables

Aside from being "neat and organized" with my tablespaces, are there any performance benefits that I can get from this given that I am not using partitioning and only using a single disk, in addition I am only working with a relatively small set of records?

Or should I just stick with placing everything on a single tablespace?


texts:
I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.

My basic query is:

UPDATE us_demand
SET ...
FROM us_demand_addtl AS sub
WHERE us_demand.geoid10=sub.geoid10;

The tables each have a spatial column that is indexed (but not one of the updated columns), and geoid10 is NOT indexed in either table.

Machine Specs:

2x 7200RPM 1tb Drives in Raid

Intel i74790k - 4.00ghz quad core

32gb of RAM

Analyze Result:

I have two questions.  Why is this operation so slow, and what can I do to speed it up?


texts: Shared access database file connected with MySQL server

texts:
Currently I have an access database in a dropbox folder, used by a lot of people knowing nothing of IT and DBMSes (imagine them being grannies that just learned to use access after a course of five years). This gives quite a lot of problems as the database can only be edited by one person at a time, or it gives "conflict" versions.

Secondly I have a website using a webhost which only supports PHP with MySQL (and MySQLi), but no ODBC (and no option to acquire it, stubborn hosting), on which I would need to use the data of my access file.

Is there any way to combine an access database with a MySQL server without having to install the ODBC connection with the SQL server on every person's computer (I'm not talking about exporting of course)?

Thanks in advance.


texts: Multiple login mappings for linked server

texts:
Can we assign two logins for two different databases on target server.
For example, I would like to create a Linked Server between ServerA and ServerB.

ServerB has two databases on it and both using different logins to access. Say DB1 has Login1 and DB2 has Login2

So can I create something like this:

EXEC master.dbo.sp_addlinkedserver @server = N'db_link', @srvproduct=N'SQLServer OLEDB Provider', @provider=N'SQLNCLI', @datasrc=N'ServerB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'Login',@rmtuser=N'Login1',@rmtpassword='Password1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'login',@rmtuser=N'Login2',@rmtpassword='Password2'

Am I able to access two different databases using this same linked server. I tried but with no success. Is there any way around it?


texts: SQL - query multiplying records returned - Cartesian Product

texts:
I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product:

SELECT * FROM specimen, topography_index, morphology, functions
WHERE
SUBSTRING(specimen.topography_index, 2, 2) = topography_index.topography_index_code
AND
if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code)
AND
specimen.topography_index = '_ORGAN_'
ORDER BY
(specimen.room = 'f') DESC,specimen.snop_code

The number of records being returned is around 59,000.  However, specimen table only has 469 records.  The morphology and functions tables have around 2000 records each, and the topography_index table has 29 records, so I'm guessing that the results are being multiplied by the number of records in 'topography_index' and 'morphology' or 'functions' tables (29 x 2000), whichever one that doesn't have a join in the if clause.

How should the query be written so that this doesn't happen?

Note that GROUP BY is NOT an option as the query is so slow with all the records being returned.

http://sqlfiddle.com/#!2/2bda8/1

Perhaps this could be handled by a sub select statement?

Any help appreciated!


texts: Calculating RAM requirements for an in-memory database

texts:
In general, how much RAM does an in-memory database require? I would assume it requires enough RAM for:

Operating system and general system services, plus
DBMS, plus
the entire database (same amount as stored on disk), plus
temporary database tables
some slack

Considering that the largest component of this would normally be the size of the entire database, then it would require the normal size of RAM required to run a regular on-disk database (e.g. 16 GB) plus the on-disk size of the database (let's say 50 GB for a fairly large DB); in this example it would be at least 76 GB plus room for gradual growth of the database.

I know that the answer would be different for different DBMSs (e.g. SAP Hana or Oracle TimesTen), but I am asking about the general components of the calculation.

Is this analysis accurate, or am I missing some important components of the calculation?


texts:
We are running SQL Server 2012 SP1 on Windows Server 2012. The issue we have is that after some time the server automatically reboots.

Checking the logs did not show any errors. When looking into the SQL Server log we find these logsDate

  24-5-2014 13:42:47
  SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

  24-5-2014 10:01:18
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14401 seconds. Working set (KB): 23684, committed (KB): 99988, memory utilization: 23%.

  24-5-2014 08:01:16
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7200 seconds. Working set (KB): 29868, committed (KB): 99972, memory utilization: 29%.

  24-5-2014 07:01:48
  A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3632 seconds. Working set (KB): 34376, committed (KB): 100004, memory utilization: 34%.

Searching the internet did not help very much. Most written about this is about server 2003.

The problem occurs frequently depending on the usage.

What steps do I need to take to fix this?


texts:
How to update a binary field from 0x54 to 0x46 (T to F)?

Update dbo.table1
set field = convert(varchar,field,0x46)
where fieldID = '1'


texts: SQL Profiler Shows Update (EventClass) with No Errors, But Data in Table is Not Changed

texts:
I am trying to debug a web app that uses a database and I need a little help.  The problem is that part of the code throws a timeout error.  I started using SQL Server Profiler to check the communication between the app and the database.  (I am not really a database expert and this is a new tool for me).

The app is supposed to call an UPDATE on a specific record.  Shortly after, it calls a SELECT on the same record (because it refreshes the screen).  I can see both the UPDATE and the SELECT event classes in the Profiler trace.  (There are a few other event classes between them).  The SELECT is showing with a duration of 30499 and an Error of 2 - Abort.  I am guessing this is the source of my timeout error.

This is where it gets interesting.  By selecting the EventClass in the Profiler trace, I can see the SQL SELECT statement that is being called.  I copied and pasted this into a new query in Management Studio and tried it out.  The SELECT statement worked fine and returned the record before my finger left the f5 key.  However, the data in the record was the old data.  It seems that the previous UPDATE call did not change the record.

Looking at the Profiler trace again, the UPDATE event class shows an Error of 0 - OK.  When I select the UPDATE event class and copy the SQL code into Management Studio it runs fine and does update the record.  Is anyone able to help me work out what is going on please.

If the UPDATE statement is shown in Profiler with no error, then does this mean that it should have updated the database?
What could be causing the SELECT statement to abort (considering that it runs fine when I try it in SQL Server Management Studio)?
Could problems with the UPDATE be causing the record to lock up which stop the SELECT from running?

Any advice to help me debug and fix this would be appreciated.


texts: Using environment variables in data pump query

texts:
I have been trying to use data pump (expdp and impdp) in order to retrieve some data from a database to import it into another.

I am using the expdp using the table and the query parameters in order to select specific data from one table.

Can I use variables from Linux in the expdp and impdp queries?

For example, the directory is not a standard directory. I accept this as a variable from Linux and create a directory in Oracle using a bind variable. The thing is that later on when I want to invoke data pump I cannot have a standard directory but rather a variable. How do I use the variable from Linux in the expdp and impdp query?


texts:
I need to split a database of users by X pool and each pool need to have nearly the same number of users based on their email provider...

For explain to you better, if my database content:
100 users outlook.com, 50 live.com, 10 gmail.com and 1 toto.com
And one ask me to split database by 5 (for example, it's can be 6, 9 or something else)

I need to have pool A / B / C / D with 20 outlook.com, 10 live.com, 2 gmail.com
and the pool E like above but with 1 toto.com

Actually I already splited the database per each email provider but I don't find how can I balancing the pools per ESP for be nearly equal.
The database content more than 500 different domains and some domains have only 2 or 3 users. I have to dispatch all that smartly :p

UPDATE #1
The split can be randomly, the goal if one ask me needs 8 pools it's to provide the pools with each pool content +/- 1/8 of database but with nearly each ESP.

UPDATE #2
About the split, I will create the new columns named Pool_ESP and with data like A, B, C... I need to have a history of action we will do on each pool, example we send message type 1 to pool B, type 2 to pool C...
About distro it can be randomly but by ESP (like above) the goal it is to respect the criteria of balancing (here per ESP and per number of pool they will ask me)


texts: How to make snapshot for new tables only

texts:
If there is a replication running for a large set of data, and I want to add one or more new tables, I normally add the articles to the replication and start the snapshot agent.

Since there's a lot of data, this takes a while, interrupting users that do the normal operations on the database.

Is there a way to have the snapshot agent create the snapshot data only for these new (empty) tables?


texts: Combine two queries from 1 table, 1 to Many


texts: Import data extracted from website directly into PostgreSQL database without using any text or csv file

texts:
I have a shell script which extracts data from the website. I know, I can store this data into text or csv file and then import it into database using PostgreSQL "copy" command.

My interest is not to use this text or csv file in the process. But, directly copy the data into database.

Is there anyway to do this? Any suggestion are appreciated.
Thank you


texts: CREATE DATABASE ... WITH TEMPLATE seems to lose relations in PostgreSQL

texts:
I'm trying to Create a copy of a database in postgresql per the SO answer from 2009, but running into problems.

In Postgres 9.3.9, this creates a database with no relations (the gcis db exists and has tables and data):

postgres=# CREATE DATABASE gcis_rollback WITH TEMPLATE gcis OWNER postgres;
CREATE DATABASE
postgres=# \c gcis_rollback
You are now connected to database "gcis_rollback" as user "postgres".
gcis_rollback=# \d
No relations found.

I get the same using the commandline createdb:

~$ createdb -O postgres -T gcis gcis_rollback2
~$ psql gcis_rollback2
psql (9.3.9)
Type "help" for help.

gcis_rollback2=# \d
No relations found.

Why don't I see a full copy of this DB?

Background - This is a dev server, where I can take down the connections to make a copy.  What I want is just a local copy for ease of rollback purposes while developing/testing DB schema changes using the Perl framework Module::Build::Database to build a patch.

Additional info:

gcis=# \l+ gcis
                                               List of databases
 Name |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------
 gcis | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 35 MB | pg_default |

gcis=# \d
                        List of relations
    Schema     |             Name             |   Type   | Owner
---------------+------------------------------+----------+--------
 gcis_metadata | _report_editor               | table    | ubuntu
...
(57 rows)


texts: In Between causes my query to return "wrong" values

texts:
I have a little problem right here...

So all in all I have exactly two tables:

* resources (ID, resourcetype_ID, chunk_ID, position)

* chunks (ID, x, y)

The first one stores some rows which belongs to the certain chunk.
The second one just stores all the chunks with a different set of x and y as some kind of position.

My goal is to select all the resources that belong to a certain range of chunks.
But I have made a mistake somewhere. I always get the wrong x and y positions, when I select the resources.

SELECT resources.*, chunks.x, chunks.y
FROM resources, chunks
where chunk_ID in (SELECT ID
                   FROM chunks
                   where x between 17185-1 and 17185+1
                     and y between 10846-1 and 10846+1)
group by ID

This query above returns all the resources that belongs to the different chunks. But the returned x and y position is for all of them the same. Why does this happen?

(As you can see the resources with "chunk_ID" = 1 have the same x and y as the resources with "chunk_ID" = 5 ... even if those chunks have completely different x and y pairs...)

Normally all resources should have different x and y values regarding to the chunk they belong to.


texts: mysql error handling stored procedure doesn't catch error

texts:
NOTE: I also tried CONTINUE instead of EXIT with same results.

I am running mysql 5.5 and am trying to create a stored procedure that sends a variable out based on if it supports full text.

delimiter //

CREATE PROCEDURE supports_ft(OUT supports_ft SMALLINT)
BEGIN
   DECLARE EXIT HANDLER FOR SQLSTATE 'HY000' SET supports_ft = 0;
    SET supports_ft = 1;
    SELECT @@innodb_ft_cache_size;
END;

The error I am getting is #1193 - Unknown system variable 'innodb_ft_cache_size'

According to https://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

the error code is Error: 1193 SQLSTATE: HY000 (ER_UNKNOWN_SYSTEM_VARIABLE)


texts: Can't access a specific database from phpMyAdmin

texts:
I've installed WAMP on a computer some months ago. Now I'm trying to use it and I was trying to surf the databases using phpMyAdmin. So I went to localhost/phpmyadmin and it showed me the databases.

The problem is: I can se all databases except for one that displays this error:

  #1045 - Access denied for user 'root'@'localhost' (using password: YES)

My config.inc.php file has no password set.
The weird thing about this is that when I get to the MySQL console (C:\wamp\bin\mysql\mysql5.5.20\bin\mysql) I can surf through the database without a problem.

Any suggestion on how I can see the database using phpMyAdmin?


texts:
I'm working in ssrs 2008 R2. I need to plot the series label on the top of horizontal range bar.

But the label is displayed on the left side of the bar.
I tried CustomAttribute Property Label Property and SmartLabel Property. Always the label is look like this:

Is there any other option to set the label on the top. Thanks in advance.


texts: Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup

texts:
With MySQL 8.0.20 currently installed on Ubuntu, I am unable to use Percona XtraBackup 2.3.7 due to breaking changes introduced in MySQL 8.0.20.
How can we downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu, which I believe is the latest MySQL version currently supported by XtraBackup? dpkg-reconfigure mysql-apt-config does not allow us to select a minor version.
Must the current data be backup using mysqldump before the downgrade, and restored using the dump after the downgrade?


texts: Postgres Join multiple table to replace id of parent table with value

texts:
I have following tables:

dependency
===========
id  service  dependent_service  dependency_type
--  -------  -----------------  ---------------
1   9        2                  16
5   2        7                  15

dependency_type
===========
id  name
--  --------
15  internal
16  external

service
==========
s_id  s_name  description  s_type
----  ------  -----------  ------
2     abc     ABC          1
9     cde     CDE          2
7     xyz     XYZ          3

service_type
==============
id  name
--  ---
1   api
2   db

I am basically trying to replace the ids in parent table (dependency) with the name and value from the main table. Ex. replace dependent_service (2) with value (abc). Similarly for oter fields.
So my result table looks like this:

Result table
===============
r_id  r_service  r_dependent_service  r_dependency_type  r_service_description  r_dependent_service_description
----  ---------  -------------------  -----------------  ---------------------  -------------------------------
1     cde        abc                  external           CDE                    ABC
5     abc        xyz                  internal           ABC                    XYZ

I tried to JOIN all tables using WHERE clause, but its resulting duplicate rows.

Select * from dependency as d INNER JOIN dependencytype as dt ON d.dependency_type = dt.id INNER JOIN
(service as s INNER JOIN servicetype as
st ON s.service_type = st.id) ON 1=1

Can anyone please help me here. Let me know if you need more information.


texts: ‘PRIMARY’ filegroup is full. How do I increase temdb size in SQL Server Express 2017?

texts:
When I reindex a particular table

DBCC DBREINDEX(@TableName, '',90)

I get an error

  Level 17, State 2, Procedure aareindex, Line 21 [Batch Start Line 0]
  Could not allocate space for object ‘dbo.SORT temporary run storage:
  422738479742976’ in database ‘mydatabase’ because the ‘PRIMARY’
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup.

So I want to try increasing the size of TempDb

I can see the size using SSMS by looking at the database properties but I don't see how to set it.

[Update]

select type_desc, name, size, max_size, growth from tempdb.sys.database_files

returns

The file size is already 10236 Mb and the Express limit is 10 Gig
However the Disk Usage report shows that 26% of the diskspace is unallocated, 40% is data and 32% is index.


texts: Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

texts:
I am researching migration of a major system from Oracle to PostgreSQL.  Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically within a Function I can create a TYPE as an array.

The syntax in Oracle is:

TYPE VAR_STRING IS VARRAY(10) VARCHAR2(30);

This creates a 10 element array TYPE named VAR_STRING to hold 10 strings of 30 characters.

I am playing with PG 9.6, and even in the Create Type dialog, it is not obvious to me to make a pre-defined TYPE that can be an array.


texts: Why does PhpMyAdmin put the idexes at the end of a dump?

texts:
When I export a MySQL database with Adminer or with mysqldump, it produces a .sql file with each table definition followed by (if the table is non-empty) an INSERT statement (or multiple statements for large tables). The table definition includes indexes and constraints. It will also include the AUTO_INCREMENT value if that is different from what you'd get after running the inserts.

By contrast, a PhpMyAdmin export defines the table without any indexes or constraints, and then inserts the data. At the end, after all tables have been created and all data has been inserted, are a bunch of ALTER TABLE commands to add keys, then a further set to add AUTO_INCREMENT rules, and finally a further set to add constraints.

Why the difference? Both export styles will produce the same database in the end. I assume that PhpMyAdmin made a deliberate, intentional choice to deviate from the mysqldump style. In my subjective experience (I've not actually measured) PhpMyAdmin dumps are slower to run than Adminer or mysqldump dumps. I imagine that they must have some benefit. What?

I might guess that if you're running a PhpMyAdmin dump on an older version of MySQL which doesn't understand constraints (do any such exist?) you will at least have all your data before hitting an error. Perhaps in this sense the PhpMyAdmin dumps are more robust?


texts: Is there something I can do to make this UPDATE run faster?

texts:
I have a relation EnumeratedSensorReading that includes a siteid and a quantityid column. I want to replace these two columns with a single sensorid column. I have added this column, and it is currently empty.

The mapping from the (siteid, quantityid) tuple to sensorid is provided in a table SiteQuantitySensor. The UPDATE statement is as follows:

UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);

The EnumeratedSensorReading table has about 600K rows and the query seems to run forever. Here is the EXPLAIN output:

EXPLAIN UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Update on enumeratedsensorreading  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
   ->  Merge Join  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
         Merge Cond: ((t.quantityid = s.quantityid) AND (t.siteid = s.siteid))
         ->  Sort  (cost=96208.18..97767.86 rows=623872 width=14)
               Sort Key: t.quantityid, t.siteid
               ->  Seq Scan on enumeratedsensorreading t  (cost=0.00..14828.72 rows=623872 width=14)
         ->  Materialize  (cost=15073092.05..15328879.57 rows=51157504 width=46)
               ->  Sort  (cost=15073092.05..15200985.81 rows=51157504 width=46)
                     Sort Key: s.quantityid, s.siteid
                     ->  Nested Loop  (cost=0.00..654299.55 rows=51157504 width=46)
                           ->  Seq Scan on enumeratedsensorreading  (cost=0.00..14828.72 rows=623872 width=28)
                           ->  Materialize  (cost=0.00..2.23 rows=82 width=18)
                                 ->  Seq Scan on sitequantitysensor s  (cost=0.00..1.82 rows=82 width=18)

As I have very little experience in interpreting these results, could someone help me understand why the statement is so slow, and if there's anything I should do?


texts:
UNION
Suppose 2 SELECT queries are joined by UNION. Can I get output if the 2nd SELECT query has 0 rows and 1st SELECT query returned some rows?


texts: DENY insert,update,delete to user (PostgreSQL)

texts:
I wonder if Postgres support the DENY command. I search in the documentation but I couldn't find anything about deny.

I have GRANT ALL ON table1 TO user1 WITH GRANT OPTION
but I don't want user1 to be able to grant insert/update/delete to user2.
(He can do whatever he want with everyone else!!!)

I want to use this command DENY INSERT,UPDATE,DELETE ON table1 TO user2;

Is there any way to accomplish this?


texts: SQL Server Facets: "Linked Server" exist but "Registered Server" doesn't - why?

texts:
I'd like to monitor multiple SQL Server instances.
Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using "Linked Servers" facet to employ a "Registered Servers" one.

But there is no "Registered Server" facet.
Why? I'd like to grasp this absence from conceptual p.o.v.?
and how to make the same as described in the cited article using registered instead of "linked" servers?

SQL Server 2012 R2, Windows Server 2008 R2


texts: Adding custom meta data to a table in oracle

texts:
I'd like to know if there's a possibility to add some custom metadata to a oracle database table.

The problem at hand is the following:

I'm importing a table from another DB. It might happen that I add additional rows on my side to this table. So far I've written code like

    declare
        type coll is table of varchar2(200);
        source_table coll;
        target_table coll;
    begin
        select column_name bulk collect into source_table from all_tab_cols@remdb where table_name='SOURCE_TABLE';
        select column_name bulk collect into target_table from all_tab_cols where table_name='TARGET_TABLE';
        -- is source_table collection a subset of target_table collection?
        if source_table submultiset target_table then
            dbms_output.put_line('source table is a subset of target_table.');
            -- code for this case
        else
            dbms_output.put_line('source table is not a subset of target_table.');
            -- code here for this case
        end if;
    end;

This is a elegant solution, but it does not deal with the case that the source_table get's some columns removed. I could of course create a table which could store the number of columns in the source table. I then could check for the number of columns in this table and if it's bigger than the current number of columns then some columns have been removed in the source table.

BUT I believe this is not an elegant solution. It adds another table to my Data Warehouse which increases complexity unnecessarily. That's why I wonder if there is a way to add some meta data to the target table itself. I could store the number of cols of the source table there. This would be more elegant, but is it doable?

Or is there another way you can think of?

I suppose it's needless to say, that I begann with oracle very recently.


texts: table is corrupted after getting 1114 error : table is full

texts:
PC is Intel Core 2 Duo with 4G of RAM. Windows 7 32bit
innodb_buffer_pool_size=1G with innodb_buffer_pool_instances=2

I was loading data from CSVs into mysql table using mysql import command. Mysql gave me Table Full Error: 1114 at when my table size was exactly 4G. (Size of .idb file in the ProgramData directory.)
After that error, I am not able to query the able using SELECT command or not even able to use mysqldump on it. When I do so, Mysql57 service stops and I have to restart it. So inshort, I am not able to query my table.
I checked my error log and I got this error message every time, I query the table.
2016-12-23T23:08:40.158223Z 3 [ERROR] InnoDB: Trying to access page number 3 in space 86, space name ccrawlg/ccrawloct16, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2016-12-23T23:08:40.161223Z 3 [ERROR] InnoDB: Server exits.
After searching the web, I seemed to me that my database is corrupted and then I tried inndo_force_recovery=1to6 in my my.ini file but unable to do mysqldump and take a backup.
All the above steps happened twice with me as I retried loading all my csvs after first failure.
When I retried, I noticed that the size of idb file was exactly the same as the previous idb file in kbs when i said - Table is full.

Edit:-

My disk doesn't lack in space, there is sufficient space on the disk. It's just that mysqlimport gave table full error at idb size of 4Gs.
My problem is exactly same as this post here :-
https://stackoverflow.com/questions/38245974/mysql-server-crashes-innodb-outside-the-tablespace-bounds
Import statement:-
`mysqlimport -i --fields-terminated-by="," --lines-terminated-by="\n" -uroot -psomething --local ccrawlg ccrawloct2016 --verbose
Show create table is below

CREATE TABLE ccrawloct2016

(
  'email' varchar(50) NOT NULL,
  'source' longtext,
  `date` varchar(20) DEFAULT NULL,
  `domain` mediumtext,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Link to my.ini file :-
https://drive.google.com/file/d/0BztaImknhgDFWUFsdEE2MXFxWDA/view?usp=sharing

I hope some expert out there could help me get out of this problem. I will provide more info needed.

Thanks in advance,


texts: select from many to many by matching field within a column

texts:
Having following tables:

Order table:

OrderID
1
2
3
4
5

CustomerOrderLink table:

CustomerID  OrderID
1           3
1           4
2           2
2           4
3           3
4           4

Customer table:

CustomerID    Forename  Surname
1             A         Guy
2             Ted       Something
3             Gaby      Guy
4             Katie     Kitty

the task is to select all orders made by more than one person, but only when all the persons making order have same surname. Let's say that when customers have matching surname, they are a family. So to rephrase it: We want to have all the orders all families made together (we consider a family ordering when there are two or more customers with same surname for single order).

So the expected result for dummy data above would be:

OrderID Customers
3       2

I already have a part for selecting orders that are made by more than one customer:

 select OrderID, COUNT(distinct CustomerID) as CustomersCount
 from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1

but I struggle to implement checking, wheter they have matching surname:

select OrderID, COUNT(distinct CustomerID) as CustomersCount left join Customer on Customer.CustomerID = CustomerOrderLink.CustomerID
     from CustomerOrderLink group by OrderID having count(distinct CustomerID) > 1 and ???

I'll be thankful for help.


texts:
I have a question on bridging tables: if I have a bridging table linking two tables (standard) with the only values in the bridging table being the Primary Keys for both tables it is linking, how would I create new records in the bridging table if I wanted to, would this be through simply adding them in to the table directly as long as they existed in both the Parent tables?
Also, under what circumstances can something be deleted from a bridging table (or not be)?
If anyone has any references on where to find more info to read up on this as well, I would appreciate it.
Thanks


texts: Default Trace activity object not in database listed

texts:
I've been collecting information on the default trace however i've noticed something strange.

Below is a what i'm running ,

DECLARE @trc_path VARCHAR (500)

SELECT @trc_path = CONVERT(VARCHAR (500), value)
FROM   fn_trace_getinfo(DEFAULT)
WHERE  property = 2

SELECT StartTime,
       ObjectName,
       DatabaseName,
       (SELECT name
        FROM   sys. trace_events
        WHERE  trace_event_id = fn.eventclass) AS EventClass,
       ObjectType,
       ServerName,
       LoginName,
       ApplicationName,
       HostName
FROM   :: fn_trace_gettable(@trc_path, DEFAULT) AS fn
WHERE  EventClass IN ( 46, 47, 164 )
       AND EventSubclass = 0

I've noticed something strange, I collected some data which suggested there was an alteration to a stored procedure that wasn't in the database listed, it was a stored procedured which lived in a different database. Initially I thought the database listed is the trace data was the the default database of the user running the process but then i saw in another trace collected this wasn't the case.

Can anyone explain why a database might be associated to a foreign stored procedure alter?

Thanks !


texts:
What are the differences between the daily tasks/duties of a Mongo DBA compared to a RDBMS DBA?

For example, some sites claim that a MongoDB DBA would not require to do data modelling or designing the database, as that would be done by a developer or application designer.

This would mean some  tasks are no longer needed to be done wrt MongoDB administrations, which was earlier being done by the RDBMS DBAs.

What other tasks would be required, that are not normally in the schedule of a RDBMS DBA?, and also the task which the RDBMS DBAs used to do but is no longer in the schedule of MongoDB DBAs ?

I'm new in MongoDB administration, so I'm trying to identify these task so that I may not commit the mistake of doing things in my daily task that are not needed or I miss something that I need to.

Can any experienced MongoDB DBAs help me out so that I don't do any foolish mistakes in my work ?


texts: PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

texts:
I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL, -- unique values simulating ID or Timestamp
   id2     NUMERIC NOT NULL -- a kind of Type
);

Populate it with:

INSERT INTO scale_data
SELECT sections.sections, sections.sections*10000 + gen.gen
     , CEIL(RANDOM()*100)
  FROM GENERATE_SERIES(1, 300)     sections,
       GENERATE_SERIES(1, 90000) gen
 WHERE gen <= sections * 300;

It generated 13545000 records.

Composite index on it:

CREATE INDEX id1_id2_idx
  ON public.scale_data
  USING btree
  (id1, id2);

And select#1:

select id2 from scale_data
where id2 in (50)
order by id1 desc
limit 500

Explain analyze:

"Limit  (cost=0.56..1177.67 rows=500 width=11) (actual time=0.046..5.124 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..311588.74 rows=132353 width=11) (actual time=0.045..5.060 rows=500 loops=1)"
"        Index Cond: (id2 = '50'::numeric)"
"        Heap Fetches: 0"
"Planning time: 0.103 ms"
"Execution time: 5.177 ms"

Select#2 --more values in IN - plan has changed

select id2 from scale_data
where id2 in (50, 52)
order by id1 desc
limit 500

Explain analyze#2:

"Limit  (cost=0.56..857.20 rows=500 width=11) (actual time=0.061..8.703 rows=500 loops=1)"
"  ->  Index Only Scan Backward using id1_id2_idx on scale_data  (cost=0.56..445780.74 rows=260190 width=11) (actual time=0.059..8.648 rows=500 loops=1)"
"        Filter: (id2 = ANY ('{50,52}'::numeric[]))"
"        Rows Removed by Filter: 25030"
"        Heap Fetches: 0"
"Planning time: 0.153 ms"
"Execution time: 8.771 ms"

Why plan differs?
Why in #1 it does show like Index condition, but in #2 Filter and number of index scanned cells.
Doesn't sql#1 traverse index in the same way like explain for sql#2 shows?

On real/production DB #2 works much slower, even if search by 2 keys separately is fast

PG 9.5


texts: InnoDB problem when upgrading to Joomla 3.X

texts: we have different Joomla and WordPress sites on 1 server. We want to upgrade a Joomla 2.5 site to Joomla 3.X. The upgrade only works when we remove the dbdata1 file in MySQL(it is automatically created again). But since the dbdata1-file contains data from the other sites as well, deleting it has impact on all sites that are on this server. Does anybody have any idea how we can split the dbdata-file so we have a seperate file for every site? Or a way to just erase the the data of 1 site?


texts:
I have a problem with oracle 11g database,

I have shutdown my computer this morning normally, but in the evening when I tried to connect to my database I fond a problem. I cannot connect as normal user so I tried to connect as sysdba to shutdown the database and remount it (this works normally). Now when I try to open it (the database) I got this error :

ERROR in line 1 :
ORA-00600: Internal error code, arguments : [kcratr_nab_less_than_odr], [1],
[90], [13770], [13771], [], [], [], [], [], [], []

Can any one help me please ???


texts: mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax

texts:
I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts.

MariaDB [dbx]> explain select `id` from `pics` where `user_id` = '3';
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | pics  | ref  | user_id       | user_id | 4       | const | 1087 |       |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [dbx]> analyze select `id` from `pics` where `user_id` = '3';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select `id` from `pics` where `user_id` = '3'' at line 1

I use mysql  Ver 15.1 Distrib 10.0.14-MariaDB, for Linux (x86_64) using readline 5.1


texts: In a WordPress/WooCommerce table the prices are hosed. How to realign them? (a key=value structure)

texts:
We had in our WordPress/Woocommerce table (hence mySQL) ab_postmeta the sale (discount) prices for some products.

We thought that reseting the sale prices via SQL query (and not via WordPress because they were too many) would be ok. What we did was:

Update ab_postmeta set meta_value='' where meta_key='sale_price'

For some parts of WordPress it worked for other ones it did not. At a closer inspection we saw that the ab_postmeta table had also other values which should be reset to the normal price in order to work. Our main difficulty is that the table has a vertical key=value layout with the following structure:

ab_postmeta

meta_id - PK
post_id - FK to the post/product
meta_key - key
meta_value - value of the key (property) above

Basically, we need an Update query which will take the (numeric) value of the meta_key='_max_variation_regular_price' and put it in meta_key='_price' and in meta_key='_max_variation_sale_price' and in other key values for the same post_id. It is ok if we run the SQL query multiple times for each target key from console.

How to do that? (I'm afraid to experiment, because I'm on a live db).

UPDATE:

Here is a sample of data: (what we need to do is: all „263”s must become „329”s - We must copy from one source record in another destination record based on Post_id (must be the same) and meta_key - must have the corresponding values):

UPDATE 2: In fact we need a translation in SQL of the following: "For each post_id take the value from meta_value where meta_key='_max_variation_regular_price' and put it in meta_value where (for example) meta_key='_price' (We will change your example for each destination row we need)


texts:
I have been researching dozens of sites regarding MAXDOP but I seem to be missing something basic. MAXDOP=0 enables SQL to use all available CPU's. MS recommended settings is to set MAXDOP = number of CPU's if less than 8, and MAXDOP = 8 if 8 or more CPU's (noted, there are some finer points, e.g., NUMA, etc., but this is the basic guidance.) If MAXDOP=0 enables all CPU's and a system has 8 or less CPU's, how is a setting of MAXDOP=0 different from setting MAXDOP=#CPU's? Don't they both result in all x CPU's being available? I can see where this makes perfect sense if # of CPU's>8.

Thanks!


texts: I’m administrating an Oracle 12c database which shows the wait type: enq: TM – contention in certain time periods (up to 30% of total waits). During this periods the applications does not allow new logins and users are complaining about timeouts and long response time. enq: TM – contention is related to missing indexes on foreign key constrains. How do I find these missing indexes? Should a DBA solve this or should the software company provide the indexes?


texts:
This morning I had three failed jobs (no biggy) used for custom reporting

DOMAIN\USER is no longer with the company.

  Error Message

  The job failed.  Unable to determine if the owner
  (DOMAIN\USER) of job Email Import Impacting Issues has server access
  (reason: Could not obtain information about Windows NT group/user
  'DOMAIN\USER', error code 0x534. [SQLSTATE 42000] (Error 15404)).

I've changed the owner to [sqlAdmin], which is one of our sqlAdmin owner accounts.


texts:
I want to remove replication as we are not going to use it further. I want to know what steps should we follow to remove the replication like:

Which jobs to stop
Delete subscription and publisher

...and any other things to consider. I am doing it for the first time, so I need help regarding this.


texts: configure new linked server from SQL Server 2016 to Oracle 12c

texts:
I'm having difficulty creating a Linked Server in SQL Server 2016 to talk to a remote Oracle database.

I have installed Oracle Database Client 12c. First I installed InstantClient and then later realizing I needed more functionality installed Runtime. During the setup I accepted C:\Oracle\product\12.1.0\client_1 as the Oracle home directory. For the Oracle Home User, I used the Windows Built-in Account NT AUTHORITY\LOCAL SERVICE.

I used Oracle Net Configuration Assistant to create a tnsnames.ora file with alias = MyAlias and service name = MyServiceName. Net Configuration Assistant was able to test the database connection successfully after I provided credentials for the remote database.

The tnsnames.ora file created by Oracle Net Configuration Assistant was in this folder: C:\Oracle\product\12.1.0\client_1\network\admin

From a Powershell prompt, I ran tnsping MyAlias and got OK (200 msec).

I think (hope!) this means I got the Oracle side correct.

On the Microsoft side, I installed .NET Framework 3.5. Then I downloaded and installed Oracle Data Access Components (ODAC) from here. Specifically I downloaded ODAC122010Xcopy_x64.zip and followed the instructions in the readme file included. The command line options I used during install were:

 .\install.bat all c:\oracle odac true true

The parameters of the above commmand were:

component_name = all (install all Oracle Providers)
oracle_home_path = c:\oracle (should it be the Oracle base or the subfolder?)
oracle_home_name = odac (determines registry key path)
install_dependents = true
machine_wide_configuration = true (this parameter was a guess; I want this to work for all users of this machine)

I then added C:\oracle and C:\oracle\bin to the beginning of the Environment variable PATH. In Powershell this was:

 [Environment]::SetEnvironmentVariable("Path","C:\oracle;C:\oracle\bin;" + $env:Path, [System.EnvironmentVariableTarget]::Machine)

Within SQL Server Management Studio, I can see OraOLEDB.Oracle in the list of Linked Server Providers.

Now trying to create the Linked Server. Here's my config:

For security, I'm trying for now with a fixed security context, using the same credentials that worked when testing the connection with Oracle Net Configuration Assistant.

It doesn't work. Here's the generic error:

  The linked server has been created but failed a connection test. Do
  you want to keep the linked server?

  Cannot initialize the data source object of OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER". OLE DB provider
  "OraOLEDB.Oracle" for linked server "MYLINKEDSERVER" returned message
  "ORA-12154: TNS:could not resolve the connect identifier specified".
  (Microsoft SQL Server, Error: 7303)

What am I missing? What diagnostic tests would be valuable?


texts: How to deal with multiple error columns in data flow

texts:
I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in case an error is encountered. When the error is encountered in the row by row insert, I'd like to kick that out to an error table. My architecture looks like this:

However, I get an error in my Union component because there are multiple ErrorCode and ErrorColumns coming from each flow, one from the batch insert and one from the row by row insert.

I'm attempting to union the error output together in order to not have to duplicate any logic I want to do with my error handling.

What's the best solution for dealing with this? Is there a way to remove columns from a data flow?


texts: Transactional Replication to a Version Two Versions Older--Count by Name or Number?

texts:
According to Microsoft SQL Server documentation, a "Subscriber to a transactional publication can be any version within two versions of the Publisher version." In this context, are versions named versions (e.g. 2005, 2008 and 2008 R2) or numerical versions (like 9.x, 10.x, 12.x)?

I'm trying to figure out whether a 2012 server can publish transactional replication to a 2005 subscriber. If, in the above context, versions are named versions, the answer appears to be no, for 2005 is three versions behind 2012; if they are numbered versions, the answer appears to be yes, for 2005 (9.x) is only two version behind 2012 (11.x).

Based on the example in the documentation, it appears the answer is numerical versions; however, I don't have a system of my own with instances of both versions to test this on and I'd like to be sure I have this down correctly before I advise someone else about it.

If Counting by Named Versions

Versions Back    Can 2012 transactional     Server Version
                 replicate to?
1                Y                          2008 R2
2                Y                          2008
3                N                          2005

If Counting by Numerical Versions

Versions Back    Can 2012 transactional     Server Version
                 replicate to?
1                Y                          10.x (2008 R2 & 2008)
2                Y                           9.x (2005)
3                N                           8.x (2000)


texts:
i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is.

now i want to calculate the percentile ntile(100) for each state.

   SELECT  id, population, state_id,
   ntile(100) OVER(ORDER BY car20) as percentile
   FROM avi_threshold01

when i use this, i think it calculates the ntile over all points and states.


texts: MySQL - SELECT INTO OUTFILE, with a UNION

texts:
Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile?

What I am trying to do, but not working:

(SELECT email FROM table WHERE createdate >= date_sub(now(), interval 1 year))
UNION DISTINCT
(SELECT email FROM table WHERE createdate < date_sub(now(), interval 1 year) AND email not like '%@gmail.com')
INTO OUTFILE '/tmp/emailDump.txt' FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";


texts: Enable Job instantly after AOAG Role change

texts:
We have a archival job which is set up in all three replicas which are part of AOAG (SQL Server 2014).

Here the connection string is AOAG Listener (which connects to only Primary Replica).

I have disabled the job in secondary replicas.

I have seen message_id 1480 (AG Role Change).

So now I should do the below step by step process to auto-enable the job at times of unexpected failover.

Create alert for 1480.
Script out the enabling code for that particular job (T-SQL).
Write a PowerShell code for identifying the event id 1480  for role change and check for the current primary replica (T-SQL Code above) and auto enable (T-SQL script) .  I will let this process run in all the replicas to identify the role change and auto_enable the archival job in secondary sync server AOAG.

Conclusion : In brief, I need to disable job in secondary replicas and enable them automatically as per the replica role change and automatically enable the archival job as per the replica state change to primary.

Please help me to get rid of this confusion.

I have checked the below links but would like to know step by step process.

(sqlmag.com) AlwaysOn Availability Groups and SQL Server Jobs, Part 5: Setting Up Failover Alerts
(mssqltips.com) Configure SQL Server Alerts and Notifications for AlwaysOn Availability Groups


texts:
I need to update a string (VARCHAR2) value in an Oracle database which contains Javascript code.

The update script contains the following string:

c===l&&p(a,b)

which when executed from "Oracle SQL Developer", triggers (on that line) the following popup:

Is there a way to disable the substitution parsing?


texts: T-SQL Query runs quickly on local PC, but slow on SQL Server?

texts:
I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters.

It's been running without issue for months - but has suddenly started running very slowly on the server (4 core, 10GB RAM, disks on fast SAN)

If I run the query locally on my PC (2 core, 2GB RAM, one local disc) then it takes approx 1:20 to run.

Prior to this week, run on the server took approx 0:15 to run.

This week, run on server taking approx 12:30 to run!!!

This is a massive difference, and I have no idea what to start looking for to resolve the issue.

What I HAVE noticed is that this does not seem to be getting cached...OR something is hogging/not releasing cache.

e.g.Run this locally, 1:20...run it again immediately - almost instant.
Run on server 12:30...run it again immediately - 12:30 again?

Any suggestions very gratefully received!


texts: Restoring RMAN backup in Oracle 9i to another server

texts:
I'm trying to do a restore of a cold backup made with rman in oracle 9i, the question is that I managed to successfully restore the pfile and the controlfile but when I try to give:
RMAN> restore database;

Starting restore at [DATE]

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid = 11 devtype = DISK
RMAN-00571: ======================================================================= =============
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ======================================================================= =============
RMAN-03002: failure of restore command at [date]
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Physically the backup pieces are there but when I do:
list backup of database;
it does not show the missing ones.

I must clarify that the restore I'm doing on a different server, but I keep the same directory structure and the same SID as in the original.

Also I copy the backup rman in the same directory.

When this happens in Oracle 11g I simply launch:
catalog start with

But for 9i there is no such option.

Could you recommend me some solution, please?

The rman script that I use is the following:

shutdown immediate;
startup force dba;
shutdown immediate;
startup mount
run {
set command id to 'ORA9';
allocate channel t1 device type disk;
allocate channel t2 device type disk;
allocate channel t3 device type disk;
allocate channel t4 device type disk;
backup filesperset 1
format '/ Backups / FULL_% d_% u'
(
database
include current controlfile
);
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}
alter database open;

If possible, it would help me a lot more, if you could clarify the similar restore procedure in case the backup is done with tape.

Thank you very much.


texts: Why doesn't MySQL coerce the collation to the column-specified, when comparing to a literal?

texts:
I have a table with a collation defined on a column:

CREATE TEMPORARY TABLE test_table (
  utf8_col VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
);
INSERT INTO test_table VALUES('ã');

According to the MySQL coercibility rules, when a value from that column is compared to a literal:

SELECT utf8_col < _utf8mb4'ñ' FROM test_table;

the column collation coercibility value is 2;
the literal CCV is 4

this should cause the comparison to fail, because the column CCV has higher priority, and should use the utf8_bin collation, which can't handle utf8mb4 character sets.

The comparison is successful, however. Why is that?


texts: Calculate the total on events with two time conditions

texts:
I have a table in BigQuery that looks something like this:
    schema = [
        bigquery.SchemaField('timestamp', 'TIMESTAMP', mode='REQUIRED', description='Data point timestamp'),
        bigquery.SchemaField('event_id', 'STRING', description='EventID'),
        [...]
    ]

The table has a fairly large dataset, and I'm trying to find write an efficient query that returns the number of events that happened in the last 24 hours but also within the last N days. That is, two different records with different conditions but the same event_id. I don't care so much about the actual event_id, but rather the distribution.
Ideally, the query would return something like this:
7_days: 20
30_days: 15
60_days: 7

If it's impossible to do this in pure SQL, I also have Pandas available at my disposal.


texts: How could i search between two string type date

texts:
Consider these two tables. I use persian dates, so I have to declare startdate and enddate as a string(nvarchar(10)).

crt table:

id title startdate enddate
--------------------------
1  xyz   1395/01/01  1395/12/29
2  xyy   1394/12/01  1395/05/05
3  cvb   1392/02/03  1394/02/03

season table:

id title startdate enddate
--------------------------
1  spring95 1395/01/01  1395/03/31

I want to write a query that gives me crt records that are valid in a season. IE crt.startdate<=season.startdate and crt.enddate>=season.enddate.

How do I do that?


texts: Parallel replication is not working on a 5.7 slave from a 5.7 master

texts:
Note: Following are the parameters that we turned ON on the 5.7 replica for parallel replication to happen:

slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 10
log_slave_updates = 1
backup retention enabled for 1 day on the slave

On the 5.7 master, the following parameters were set:

binlog_order_commits = 1
binlog_group_commit_sync_delay = 50

We can see from watching the following query that only single threaded replication is happening:

select * from performance_schema.threads where name="thread/sql/slave_worker";

By executing the above query we saw that only one of the slave workers are actually committing to the database and for all the other threads it shows a status of "Waiting for an event from Coordinator"

Would like to know why exactly parallel replication is not working


texts: How do you get a count for an aggregated row?

texts:
I am getting sums for a set of rows, There is an id from each row that relates to 0 to many rows in another table. I am having trouble getting that number into the dataset. So here is what I have which works as I need it to:

SELECT
  sp.advcode         AS Advertiser,
  count(*)           AS Created,
  sum(CASE WHEN i.sent IS NOT NULL THEN 1 ELSE 0 END)    AS Sent,
  sum(CASE WHEN ((i.active = 1) AND (i.sent IS NULL)) THEN 1 ELSE 0 END) AS ActiveUnsent,
  count(i.inquiryId) AS Total,
  sum(CASE WHEN i.campaignLeadId IS NOT NULL THEN 1 ELSE 0 END) AS CampaignLeads,
  sum(CASE WHEN i.active = 0 THEN 1 ELSE 0 END) AS Inactive
FROM inquiry i
LEFT JOIN schoolProfile sp ON sp.schoolProfileId = i.schoolProfileId
WHERE sp.advcode LIKE 'al007'
  AND i.dateCreated BETWEEN '2016-02-02' AND '2016-03-02'
GROUP BY i.schoolProfileId;

I need to count how many times i.inquiryId appears in another table, per returned row. If someone has a clue I would be most grateful.


texts: More efficient/cleaner way of doing a select that traverses a specific column?

texts:
Given the following Table structure:

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y

I want to do a select that "follows" the Target Column, example:

If I pass in ID 1 I want to return row 3 because A would resolve to C if you Select the Target and then Selects the next row where OldUrl = Target
Like this:

a -> b
b -> c
c -> d

SQL for creating the table:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I've managed to do that by using the code below, but my SQL skills are very limited so I just want to know if there is a better/cleaner way of doing this?

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id

Wanted output when passing in Id = 1

3   /c  /d


texts:
I have 2 schemas, lets say sch1 and sch2. Each owned by different user.

I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference sch2.MyTable in sch1 foreign key constraints.

I do NOT want to make sch2.MyTable directly accessible to those, who uses sch2 procedures/views, but want to allow indirect access.

Basically sch2 is much more important. Owners of sch2 have control of sch1 as well, but not vice a versa. On the other hand sch1 has some data that is dependant on sch2 and they should be able to see them and should not allow delete data from sch2, while sch1 still has some dependencies on them.

Is that possible (maybe through synonyms)?

For no it seems- no, not possible. Unless take off part about "Views and Constraints" OR "different schema owners".