texts:
For reasons unknown to me,  the control flow doesnt reach "ITS CORRECT BUT PRIZE IS MONETARY" block, that corresponds to variable bounty not being NULL. I'm pretty confident that SELECT Bounty FROM Questions WHERE QuestionID = NEW.QuestionID must return INT UNSIGNED.

So, is my syntax wrong? Am I using some kind of bad IF condition?

The trigger code: pastebin

CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`
FOR EACH ROW
BEGIN
DECLARE bounty INT UNSIGNED;
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` =
 NEW.`QuestionID`);
 IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0
THEN     // IF ITS CORRECT
  IF bounty IS NULL THEN    // FIELD `Bounty` being NULL means that a prize is set.
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE
      BEGIN
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY
             `PrizeID` LIMIT 1;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);
      END;
    ELSE    //IF NOT LEAVE A MESSAGE!
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );
    END IF;
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN  // DO WE HAVE ENOUGH BUDGET TO PAY?
      BEGIN
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, CAST(bounty AS CHAR(50)), NOW(), FALSE);
      END;
    ELSE   // NO MORE MONEY, I'M AFRAID
     INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY",
 NOW(), FALSE );
   END IF;
  END IF;
END IF;
END;

Updated my code like so due to Jacob H's suggestion. No results.

  CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`    //CLAIM PRIZE AND NOTIFY!
FOR EACH ROW
BEGIN
DECLARE bounty INT UNSIGNED;
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`);
IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0 THEN     // IF ITS CORRECT
   BEGIN
  IF bounty IS NULL THEN    // IF ITS A PRIZE CLAIM IT
    BEGIN
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE PRIZES?
      BEGIN
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY `PrizeID` LIMIT 1;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);
      END;
    ELSE    //IF NOT LEAVE A MESSAGE!
      BEGIN
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );
      END;
    END IF;
    END;
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    BEGIN
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN
      BEGIN
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`,CAST(bounty AS CHAR(50)), NOW(), FALSE);
      END;
    ELSE
      BEGIN
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY", NOW(), FALSE );
      END;
    END IF;
    END;
  END IF;
  END
END IF;
END;


texts: Update MySQL user table of active/inactive members based on array

texts:
I have a MySQL table of unique users. I have an 'active' boolean column (0 or 1) to denote whether the user is currently subscribed to the site. The primary table will hold the 'active' boolean column, and it might contain as many as ~30,000 IDs (active and not). I plan to populate that table via an array of active users.

Right now, I'm dumping all of the active IDs into a temporary table (T2) from the active-user array. From that table, I'm populating the primary table (T1) with any IDs that don't already exist.

INSERT INTO T1 (id_hashed, active_boolean, history, date)
SELECT id_hashed, '1', '6-29-18 on :: history', '$time'
FROM T2 t2
WHERE NOT EXISTS(SELECT 1 from T1 t1 where t2.id_hashed = t1.id_hashed) ";

Using two further queries, I'm also updating the 'active' boolean column in the primary table based on the temporary table of active users.

UPDATE T1
SET active_boolean = 0, history = concat('" . date("n-j-Y",time()) . " off :: ', history)
WHERE active_boolean = 1 AND id_hashed
NOT IN (SELECT id_hashed from T2 )";

UPDATE T1
SET active_boolean = 1, history = concat('" . date("n-j-Y",time()) . " on :: ', history)
WHERE active_boolean = 0 AND id_hashed
IN (SELECT id_hashed from T2 )";

I'm trying to make sure this is scalable, so I'm populating the temporary table (T2) with ~6,000 random IDs and the primary table (T1) with ~12,000 random IDs. Updates to the primary table are taking ~90 seconds with that much data. I'm a bit of a DB noob, so I'm sure there's a more effective way to do this. Thanks for any suggestions.

ETA: Explain Plan for insert into T1

id | 1
select_type | PRIMARY
table | t1
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where; Using temporary
id | 2
select_type | DEPENDENT SUBQUERY
table | t2
type | ALL
possible_keys |
key |
key_len |
ref |
rows | 3003
Extra | Using where


texts: How to setup PGDATA for Postgresql13 on SUSE 15.1?

texts:
I just installed PostgreSQL 13 on a new SUSE 15-SP1 server. With postgres user, I could run initdb -D /usr/lib/postgresql13/data, and then pg_ctl start-D /usr/lib/postgresql13/data. It works fine.
Then I activated PostgreSQL service:
systemctl enable postgresql
systemctl start postgresql

The service started, but it created a new database in /var/lib/pgsql/data!
Searching around, I found that I could configure the service start up options. I created a configuration file:
systemctl edit postgresql.service allowed me to define the following setup in /etc/systemd/system/postgresql.service.d/override.conf:
[Service]
.include /usr/lib/systemd/system/postgresql.service
Environment=PGDATA=/usr/lib/postgresql13/data
END

This is supposed to alter the original service script:
/usr/lib/systemd/system/postgresql.service

[Unit]

Description=PostgreSQL database server
After=syslog.target
After=network.target

[Service]
Type=forking
User=postgres
ExecStart=/usr/share/postgresql/postgresql-script start
ExecStop=/usr/share/postgresql/postgresql-script stop
ExecReload=/usr/share/postgresql/postgresql-script reload

# The server might be slow to stop, and that's fine. Don't kill it
SendSIGKILL=no

[Install]
WantedBy=multi-user.target

When restarting the service:
sudo systemctl daemon-reload
sudo systemctl restart postgresql.service

The status shows that it is still running on the wrong database:
ps -ax | grep [p]ostgres
  6951 ?        Ss     0:00 /usr/lib/postgresql13/bin/postgres -D /var/lib/pgsql/data

My configuration had no effect. What did I miss?


texts: Why does my query run fast in Environment A, but slow in Environment B?

texts:
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!

Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?


texts: Cannot list tables (views, packages etc) from Oracle 11 database using sql explorer, but can see them using PL/SQL Developer

texts:
I am connecting to oracle 11 DB.

When I connect with PL/SQL, I can explore all the objects :

However when accessing this same DB using Oracle SQL Developer, expanding the different objects results in empty list

Any ideas why this might be?


texts:
Just wondering if anyone has configured FIPS?  Based on the information I found in the Microsoft support pages, it would seem FIPS is configured at the Windows level only and that all instances on my server would then be enabled for FIPS?

At this point, I am only interested in one of the instances using FIPS.

Everything I have read so far, indicates I may need to split this out  to a new server.


texts: Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)

texts:
I'm migrating an ancient SQL Server 2000 database to 2012 (with a quick pass through 2008 R2 since 2012 is not compatible with 2000). The DB contains hundreds of views and stored procedures, and in my testing I'm now starting to see that some views are migrated in some old version. I did not even know that there were two (or more) versions of views in 2000, but it seems so.

In the cases I've found, so far, the effected views has been using the CASE-select, and I'm starting to think that it has been edited by using a "double click on the view" rather than "design". The version that is migrated to 2012 (and 2008) is clearly from a few years old, and after resaving the offending view in design-mode in 2000, and migrating again, it gets the correct version.

But I can't do this manually for hundreds of views. Is there any way to force an version-sync of sorts in 2000? Any other ideas on a solution is much welcomed.

[Edit/Additon]

I've kept testing, and I have now narrowed down where the problem occurs. The SQL Server 2000 backup restores fine in SQL Server 2008 R2, and the view (in question) is up to date. But then I run a few checks and updates, and after running sp_refreshview on all views, it reverts back to an old version (that was not even visible in 2000 this time).

In summary: why does sp_refreshview, from SQL Server 2008 R2, revert some of my views back a this-was-the-original-design-of-the-view-in-2001 state?

Having found more info I had an easier time finding similar problems: http://sqlblog.com/blogs/davide_mauri/archive/2010/10/12/bug-with-sp-rename-sp-refreshview.aspx
But I'm still unsure on how to fix this issue since I don't know how many views are effected with this "bug".


texts: SQL Server transaction log - shrinking and reattaching

texts:
I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server.

Then I do some delete operation from some big tables. After delete I want to shrink the database.

Then detach shrinked db, copy it on first (production) server, detach the big database and then attach the small one.

Now comes my problem: on the big (production database) the activity continued, and new records was inserted, updates done, deletes etc.

Will I be able to restore those operations from the transaction log on the new created small database? Is there a checkpoint before the first backup action? Will there be conflicts between the small DB transaction log and the big one's?

Any help (ideas, articles) would be greatly appreciated.

Thank you.


texts: SQL linked server returning error suddenly upon query

texts:
We are facing error when trying to connect to and execute queries using a linked server. The SQL Server and the Linked Server are able to ping and telnet each other. Also testing the link returns positive. However, the following error is returned when we execute a query accessing the linked server:

OLE DB provider "SQLNCLI11" for linked server  returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server  returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.

I'm trying to prove that this is a network issue, but since the communication channel is open and ping and telnet work, the task fell back in my basket.

Any advice will be greatly appreciated.


texts: migrate users from mariadb10.1 to mysql5.7

texts:
My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7).

Dumping / restoring our application schemas looks straightforward. However I'm trying to determine how I can migrate my user accounts given the following:

mysql.user table structure differs between the two; I can't dump and import mysql.user
pt-show-grants, for some users, does not output statements to create the user. For instance, for some users, pt-show-grants only outputs something like GRANT INSERT, SELECT, UPDATE ON `schema`.`table` TO 'username'@'x', which fails because the user 'username'@'x' doesn't exist yet on the MySQL5.7 instance.
mariadb10.1 does not have a 'show create user' or equivalent statement.

Has anyone made this kind of move before and encountered this issue? I am hoping to avoid a wild-goose-chase for dozens of passwords in order to recreate the users manually; some of these users predate me and I have no record of the assigned passwords.


texts: Master node of galera cluster is not in sync, how should I sync them?

texts:
master node server id 10
SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+----------------+
| Variable_name             | Value          |
+---------------------------+----------------+
| wsrep_local_state_comment | Donor/Desynced |
+---------------------------+----------------+

non primary server id 11
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

remote node, server id 20
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

but I also checked if these nodes have diverged,
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_last_committed';
+---------------+-------+
| Variable_name | Value |
+----------------------+----+
| wsrep_last_committed | 8 |
+----------------------+----+

all these 3 nodes returns the same value .
so I would presume they have not diverged but primary node  is not in sync ? what does that mean ?
how can I fix this currently I get this error:
ERROR 1047 (08S01): WSREP has not yet prepared node for application us

I added wsrep_sst_donor=192.168.5.12 # this non primary node server id 11.
in the dsynced master node my.cnf
but master still shows:
MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_sst_donor';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| wsrep_sst_donor |       |
+-----------------+-------+

very scared if sudo systemctl restart mariadb would be safe !


texts: Efficiently storing query-affecting settings in a database

texts:
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).

My example

I've got a massive table of sales orders. Let's simplify it to two columns:

SalesOrder
 OrderID      int      Primary Key
 OrderDate    date

I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:

Admin_Configuration
 SettingName   (PK)|      SettingValue
 ------------------|------------------
 DaysBack          |          30

It allows me not to hardcode this "30" in query definitions.
Then I would run queries like:

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -A.SettingValue, GETDATE());

Performance

It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.

My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.

Trying a scalar function yielded even worse results.
I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.

Here is the original query plan (with a join):
4 seconds elapsed

Here is the plan with subquery, as gbn suggested:

SELECT orderid
FROM [SalesOrder] as s
WHERE s.OrderDate > dateadd(DAY,
  (SELECT -NumValue
   FROM Admin_Configuration where SettingName = 'DaysBack'
  )
  , getdate())

7 seconds elapsed, slightly slower

As for the ideal performance: I just hardcoded 20, the query took 2 seconds.
It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.

SELECT OrderId
  FROM SalesOrder as S
  JOIN Admin_Configuration as A
    ON A.SettingName = 'DaysBack'
  WHERE [OrderDate] > DATEADD(DAY, -20, GETDATE());

Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case


texts:
I am not sure if my title is correct, but I will try my best to explain the current problem.

Current Scenario:

I have a php web application with MySQL database (let's call it DB1) which is stored in Machine A.
I have another php web application with MySQL database (let's call it DB2) which is stored in Machine B,
ASP.Net Application with SQL Server database (let's call it DB3) which is stored in Machine C which will serve as a ledger.
They are connected via VPN.

I have completed this:

Let's say the transaction will go like this, a new transaction occurs, it will validate records on both code behind A and push to through Web Service on Machine B for validation and posting(Transaction ID, and corresponding Transaction Details)
I will then get the response from B and if successful I will proceed to the problem below.

I have been reading and trying the following:

MySQL connector ODBC: It is working coz I use it to Linked Server MySQL in SQL Server.
Linked Server on SQL Server: I am not sure though if this is the solution for my current problem, but at the moment I can view and query DB1 from DB3, but the trigger should be in DB1 to push the record to DB3 everytime there is a new record inserted on DB1.
Transactional replication: Not sure if it will work out
SSIS: I have tried to add both DB1 and DB3 by creating Integration Service and used mysql connector odbc and added dsn(both user and system) but there is an error like what I have mentioned below
MySQL Data Controller: Been reading, have not tried due coz I need to recompile feetds lib with distributive pack of MySQL
MySQL Federated Table: But this only works for MySQL(Both end)
Incremental load:

Problems:

How can I 'push' the record from DB1 (MySQL) to DB3 (SQL Server) and if successful get a valid response(ID preferably so that I can have a reference on both DB1 and DB3 (via trigger in DB1)
I have tried SSIS, successfully added ADO.Net Source for 127.0.0.1 for DB3 (the SSIS resides on Machine B), but when I am adding a connection to the DB1 that resides on Machine B I get an error("Error[IM002][Microsoft][ODBC Manager] Data Source Not found and no default driver found")
I have tried Linked server but somehow it only shows DB1 from DB3, any software or addons/plugins so I can view DB3 from DB1 ad push the records from DB1?
I need to have it as a transactional replication (somehow I read it is possible but at the moment I can only search for SQL Server to SQL Server)
I cannot put the inserting of new records/updating records on Code Behind(which is possible and easier) for security reasons (just in case there is/are changes on either DB). Same goes for the usage on Web Service for this part.
Performance likewise, will it be wise to do this if the number of transaction is large?(probably 360,000 inserted records in a daily basis)

Any detail explanation is greatly appreciated. Thanks

Taken from my question in: https://stackoverflow.com/questions/17583924/push-data-from-mysql-to-mssql


texts: I have a MongoDB cluster, I accidentally hosed a shard, I don't have a backup. It's pretty easy to recover the data, but after issuing the removeShard command, it says it's draining. However, the shard is unreachable, and will forever be unreachable (I no longer have access to the cloud IP address, and the data is gone). I just want to tell mongo to forget about the shard forever. Is this possible?  i.e. Can you remove a shard from mongo without the config controller having access to it?


texts: Making an Oracle XE instance network wide available

texts:
Im fairly new to Oracle so please bear with me.
I want to install an Oracle 18c XE DBMS on a server and access it's databases remotely via DBeaver. First of all there is some confusion on which client to use.
So far I've read the documentation and this is what I found regarding finding a client:

Oracle Client Software
The remote computer must have Oracle client software installed. It is through Oracle client software >that Oracle command-line utilities (and your applications) connect to the database. Oracle Database XE >accepts connections from all of the following types of Oracle client software:

Oracle Database Express Edition Client (Oracle Database XE)

When you install Oracle Database XE, Oracle Database Express Edition Client is also installed on the >same computer. You can install Oracle Database XE separately on remote computers. It is available at
http://www.oracle.com/technetwork/database/express-edition/Opens a new window

Instant Client

Instant Client is available at
http://www.oracle.com/technetwork/database/features/instant-client/index-100365.htmlOpens a new window
Oracle client software for Oracle Database Enterprise Edition or Standard Edition (all supported >releases of Oracle Database)

All Oracle client software includes Oracle Net, which is the Oracle network software that enables >client >applications on one computer to connect to databases on another computer over a network.

Question 1:
Since Im not going to access the databases programmatically and actually want to use a GUI or SQLPLUS, does that mean I have to fully install Oracle 18c XE on all clients?
For testing purposes I have set up two devices:

Device1 - private installation of Oracle 19c (IP: 192.168.178.201)
Device2 - future server with an installation of Oracle 18c XE (IP:
192.168.178.54)

Device1 is connected with network 192.168.178.0 via vpn and both devices can ping each other.
Now from my understanding I should only have to configure my DBeaver client connection like this:

But the connection times out...
Question 2:
Is my configuration of the tsnames.ora and listener.ora wrong?
tsnames.ora:
 # tnsnames.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xepdb1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora
# listener.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\install\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\install\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


texts: Oracle Database 11g Failing to mount on windows server 2012

texts:
I have an Oracle 11g database installed on Windows Server 2012. It's failing to mount after shutdown. The reason why i want to mount it is that i want to enable Archive log mode, and i have been able to do the basic steps of Enabling Archive Log Mode on Linux but the same steps are failing on mount stage:

Below are the steps i have done:

SHUTDOWN IMMEDIATE
startup mount

It is here where the error below pops up:

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

PS C:\Users\inhousedwh> sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 17 17:46:02 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys@rvas as sysdba
Enter password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor


texts: Optimise huge Postgres table for fast read

texts:
I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column.

The table is effectively a blacklist. When a user of my software saves certain data, this table is looked-up to make sure the value they're saving doesn't exist in it.

What can I do to optimise Postgres or that table to make that "read" as fast as possible? The table will only ever be written to approximately once a year.


texts: warning sign on the select operator - what does it mean?

texts:
while comparing the execution plan of 2 stored procedures, on the second one I get a warning sign (marked by the red arrow).

what does it mean?


texts: SQL Server : how to decide if FULL backup or DIFFERENTIAL

texts:
The following scenario (on SQL Server 2008 R2 and 2012 servers)

A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only checking for the existence of any row for that respective DB in the msdb.dbo.backupset, a differential backup has been initiated - a row has been found. It failed, because the DB was not the one matching the full backup.

Now, to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

Can anybody tell me if the following is a sensible thing to do:

SELECT
    sysdb.name
FROM
    sys.databases sysdb
INNER JOIN
    sys.master_files sysmf ON sysmf.database_id = sysdb.database_id
INNER JOIN
    msdb.dbo.backupset bck ON bck.backup_set_uuid = sysmf.differential_base_guid
WHERE
    sysdb.name = @dbname
    AND bck.database_name = sysdb.name
    AND bck.type = 'D'

Thank you very much in advance for a feedback,

Cheers


texts:
From what I understand you InnoDB supports

Row Level Locking
MVCC (Multiversion Concurrency Control)

Row Level Locking

  Locking is for when multiple writers are trying to update the same
  rows. Only one writer can update a row at a time, and the first one to
  update the row locks it until they commit the change. Other writers
  have to wait until the first writer commits. But at least with
  row-level locking, they only have contention if they're updating the
  same row.

  A read lock can be used to prevent other users from reading a record (or page) which is being updated, so that others will not act upon soon-to-be-outdated information.

  https://stackoverflow.com/questions/6321647/innodbs-row-locking-the-same-as-mvcc-non-blocking-reads
  https://en.wikipedia.org/wiki/Lock_(database)

Multiversion Concurrency Control

  Writers don't block readers
  Readers don't block anyone, and don't get blocked by anyone.

  (https://stackoverflow.com/questions/22923127/mvcc-row-locking-vs-textbook-transaction-behavior)

Those two are the opposite of each other.

My question is: when does locking or mvcc occur? Where do I need to specify which one the database should use?


texts:
We are trying to determine our best MAXDOP and Cost Threshold settings for an EDW box.  I setup a job to clear the wait stats before the build started and then capture the wait stats after the build was complete.

Our server is Windows 2008 and SQL 2008 Enterprise.  (Yeah I know, it is are oldest box and we are working on that).  There are 8 CPU and 132GB of memory for the box and 118GB max memory for SQL.

The wait stats after the run are below.  Would you tweak cost threshold (Current 50) or change maxdop (Current 4).


texts:
How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ?
At the moment my log file is over 500GB.

/var/lib/postgres/


texts:
I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition.

For example if I wanted to select a group where at least one row has a fold_change value greater than 4, I would expect this to work:

HAVING ANY(rnaseq.fold_change) > 4

But I get the error

  ERROR: syntax error at or near "ANY"

I suspect this is because Postgres requires the constant value to be on the left, but if I do it the other way around I get this error

4 < ANY(rnaseq.fold_change)

  ERROR: op ANY/ALL (array) requires array on right side

The only way I can get it to work is using array_agg like this:

4 < ANY(array_agg(rnaseq.fold_change))

But that syntax is fairly confusing compared to the first example and having to manually create an array for each group doesn't sound efficient at all.

Is there a proper way to do HAVING ANY in postgres?


texts: Max Workers available and yet pending task high

texts:
While exploring the data from system health extended events for event so server diagnostics I see for column query processing XML as below:

Max workers - 2944 ( based on 80 schedulers on my server)

Max workers created - 3097

Max workers available/idle - 2262

Pending tasks - 89

MS reports this as warning if there is any pending tasks greater than 1.

I am not able to understand this data from system health events that why there would be pending tasks when worker threads are already available as shown above and how are workers created more than maxworkers available as 2944 compared to created as 3097

And when should it be a concern or I should say better to interpret this information from query processing.

Update--

Wait stats since 04/20

CXPACKET  - 26.8%
PREEMPTIVE_OS_ENCRYPT_MESSAGE - 18.24%
LATCH_EX  -  17.9%

For threadpool

waiting task count=10407
wait time ms=29989ms
max wait time=641ms
signal wait time =1ms

Thanks


texts: Add variable value in single quoted string. Expand variable within single quotes

texts:
I have tried different variations of the below commands but non work.
I simply want to run the following command using a variable within the location string:

CREATE TABLESPACE data OWNER appuser
  LOCATION '/var/lib/pgsql95/:variable/pg_tblspc/data';

What has been tried (variations):

EXECUTE CREATE TABLESPACE data OWNER appuser
  LOCATION ''/var/lib/pgsql95/' || base || '/pg_tblspc/data'';';

and

PREPARE tablespace (text) AS
  CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/$1/pg_tblspc';

EXECUTE tablespace('base');

and

\set myvariable 'base'
CREATE TABLESPACE aspire_data OWNER aspireapp
  LOCATION '/var/lib/pgsql95/':myvariable'/pg_tblspc';


texts:
I have a SQL Server 2012 table which has customer id, amount, and a reset columns. I’m trying to calculate the running total and also calculate the running total after reset. Once the reset "flag" is set then I want to calculate the running total after the running total.

I have the following code and not sure if there's a way to calculate running total after a break point.

WITH a
AS
(SELECT
        *
        ,SUM(amount) OVER (ORDER BY id) AS RunningTotal
    FROM Table1)
SELECT
    *
    ,CASE
        WHEN resetYN = 1 THEN 0
        ELSE Amount + LAG(RunningTotal, 1) OVER (ORDER BY id)
    END AS ResetRunningTotal
FROM a


texts: Why is my query taking so long if my field is correctly indexed?

texts:
I’m trying to figure out why my query is taking so long.  I have indexed my property_id field but still get awful results when querying it.  I have the following Explain and wonder if anyone can shed some light on it?

production=> explain analyze select * from item_events where property_id = 503;

 QUERY PLAN
------------------
Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=137.932..97996.513 rows=23140 loops=1)
  Recheck Cond: (property_id = 503)
  Heap Blocks: exact=15371
  ->  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=123.178..123.178 rows=23141 loops=1)
        Index Cond: (property_id = 503)
Planning time: 0.154 ms
Execution time: 98141.818 ms
(7 rows)

I have the following index that exists:
“index_item_events_on_property_id_and_captured_at” btree (property_id, captured_at)

And here is explain(analyze, buffers):

production=> explain (analyze, buffers) select * from item_events where property_id = 503;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on item_events  (cost=576.74..57210.03 rows=22750 width=259) (actual time=559.704..97900.064 rows=23140 loops=1)
   Recheck Cond: (property_id = 503)
   Heap Blocks: exact=15371
   Buffers: shared hit=6523 read=8942
   ->  Bitmap Index Scan on index_item_events_on_property_id_and_captured_at  (cost=0.00..571.05 rows=22750 width=0) (actual time=556.513..556.513 rows=23140 loops=1)
         Index Cond: (property_id = 503)
         Buffers: shared hit=1 read=93
 Planning time: 0.150 ms
 Execution time: 97934.782 ms
(9 rows)


texts: Is there a way to configure a distributed environment with mongodb?

texts:
Can I configure a distributed environment system with mongodb? (For example, master and slave structures)

My job is to insert the data into the master and read the data,
Slave nodes read data by dividing it, send it to the master node,
It is a method to combine the data received from the slave nodes in the master node.

I have now set up the slave and master. (mongod --master, mongod --slave)


texts: Cannot Change Azure SQL QueryStore Parameters

texts: I change QueryStore parameters (such as MAX_STORAGE_SIZE_MB) on any of our Azure SQL Server managed instances. But then the parameter change back to default the next day. What could be the reason(s)?


texts: database design - psychometric testing layout

texts:
Good Afternoon,

I'm working on the table layout for a SQL database that will store individual and test information.  This includes individual identifying information like Name, medical id, date of birth, etc, and test specific information - specifically raw and standard scores from a variety of psychometric tools.  For the purposes of this question, I'll be providing the following sample tables:

Indiv_Information;
Test_Log;
Test_1;
Test_2;
Test_3

I have some questions about the best way to link individual information to these test scores.  Option 1 looks like this:

In this example each test logged is linked to the medical id of the individual who took the test.  My only concern with this layout is that if I want to create a display a list of all tests a user has taken I have to scan through all of these tables, and currently there are 20+

Option 2: Create a test log table and link that to multiple test tables that contain the scores for each test.  Looks like this:

It's much easier to generate a list of all tests each individual has taken with this setup, but I'm not sure this is a good setup for reporting.  The main problem here is that the field "assessment_id" is linked to multiple tables, and the only way to specify which score data to join to the test log table is to use "test_name_id" direct traffic.  I don't even know if this is possible.

I'm looking for opinions on which of these two layouts to choose.  Am I close to something optimal, or completely off base.  I'm not too experienced at database design yet.

Cheers, and have a great weekend!


texts: In SQL Server 2005 and above, how can I find when an index was created?


texts:
In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x.

I know this can be solved by running ALTER TYPE x OWNER TO <database owner> but what I'm curious about is how I can check the current owner of this specific datatype. \dT+ x does not give me information about the current owner.

How can I extract more information about custom data types, including the current owner?


texts:
I'm trying to implement a writeback measure group for required quantities by month and part number. I'm puzzled by the behavior I am seeing in Excel 2013, but I don't know if SSAS or Excel is responsible.

If the dimension selection in Excel is not the native granularity of the the underlying Measure Group, 'Publishing' the data distributes the data as equally as it can among the dimension members. Which is not necessarily wrong, but a bit weird and definitely unexpected. e.g.

If I publish a value at the year level, it will divide it equally into quarters and months.

If I don't include the part dimension it will divide the quantity equally into all part numbers.

I need to prevent data from being published anywhere but at the intersection of Part and Month. My only thought so far is to control write access through the Role with an expression that includes the Measure and Dimensions, but I'm not having much luck with that due to my limited MDX skills. Is there any other mechanism to modify writeback behavior?


texts: Get name and definition of all table types

texts: I know how to create a table type (to be used as a table-valued parameter) - but once created, how can I view it?


texts: ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

texts:
I've a strange behaviour of Sql Developer with OS Authentication, 4.1.3 32/64bit, gives ORA-01017 while sqlplus is logging in without any problem.

This is the example answer sqlplus gives me:

~ sqlplus  /@XXXX

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 27 08:50:10 2016

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

after that running SELECT user FROM DUAL; return my domain user.

While this is what i get from sql developer configured on Databases -> Advanced.

as it is shown it returns Status : Failure -Test failed: ORA-01017: invalid username/password; logon denied

And when I try to connect from the Connection section I get a little more detailed error:

An error was encountered performing the requested operation:

ORA-01017: invalid username/password; logon denied
01017. 00000 -  "invalid username/password; logon denied"
*Cause:
*Action:
Vendor code 1017

Thank you in advance for any idea or help


texts: Finding violations of the symmetry constraint

texts:
Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up exactly twice in the table.

Sometimes, this constraint is violated, i.e., a pair of friends shows up only once in the table. How do I write a query that will identify all such cases (ideally, using reasonably standard SQL)? In other words, I would like the query to return the list of rows in this table, for which there is no corresponding row with the swapped fields.

An additional question: is there any way to enforce this referential integrity in MySQL?


texts: How do I specify a linked server to a remote database over tcp/ip?

texts:
I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is attempting to use named pipes for the connection.

I created a system DSN using the SQL Server Native Client 10 and fed it the IP, Database, User, and Password. Tested it and it was fine.

Went to Linked Servers and Create New.
Specified a name "MAIN"
Selected SQL Server Native Client 10 in the combo box
Entered SQL Server in Product Name Field
Entered my DSN name in Data Source Field
Entered SQLNCLI10 in Provider Name Field
Entered my database name in the Catalog Field
Went to the security options and specified my local to remote login pairing.

After clicking OK, I get an error that it can't find the server and it shows that it is attempting to use named pipes.

What should I do to correct this?


texts:
Is there a SET command or similar I can enable to get the same error handling as a try/catch block? Specifically halting the script/procedure on the first error.

The only way I've currently found so far is to wrap the whole query in a try/catch such as

BEGIN TRY
    -- procedure body goes here
END TRY
BEGIN CATCH
    THROW;
END CATCH

While this hack does work to enable the correct error handling behaviour it just feels wrong to have a catch block that just re-throws the error.

In this particular case there's nothing more I need to do with the error other than halt the script and throw the error back out to the script runner.


texts: SQL Server Replication Snapshot Agent login fails

texts:
I have setup TRANSACTIONAL REPLICATION from ServerA to ServerB

ServerA = Primary Server   =  SQL Server 2008 R2 Enterprise
ServerB = Secondary Server =   SQL Server 2008 R2 Standard Edition.

I have used a Domain Account for Snapshot Agent and Log Reader Agent, called SQLAgent.

The initial snapshot is initialised successfully, from there its all down hill.

Error Log:

  Login failed for user 'ServerName\SQLAgent'. Reason: Token-based
  server access validation failed with an infrastructure error. Check
  for previous errors. [CLIENT: ]

Replication Monitor:

Publication Status:  OK
Agents: for both Agents Snapshot and Log Reader Status shows it is Running..... and Last Run Property says Starting Agent...

As it is obvious from the error message the Login is failing to get access to SQL Server, So just for test purpose I gave this SQLAgent account sysadmin role. Yet the error persist.

All the material I can find online, suggests Giving more permissions to login will solve the issue. But if sysadmin role hasnt solved it, I have kind of ran out of ideas now.

Can someone please suggest what is that I am missing?

how can I fix it?


texts: Restore PROD database to QA with AlwaysOn

texts:
I want to create a script/job that will run weekly to backup my PROD database, and restore it to my QA server, then run a custom maintenance script against it to cleanup and prepare the data to be analysed.

The problem is that my QA server is running AlwaysOn Availability Group.
I have access to Powershell, "DBATools.io" scripts, SQL Server Agent, and a remote SQL server if it can help. All under SQL2016.

I also just discovered today the SQL2016 feature called AUTOMATIC SEEDING. I tried it, and it allows me to bypass the complicated "Add database to AG" steps.

So, up to now, I can simplify the steps down to this:

ALTER AVAILABILITY GROUP [SQLQA] REMOVE DATABASE [ProdData];

RESTORE DATABASE [ProdData]
        FROM DISK=N'\\nas\Backup\ProdData.bak'
        WITH FILE=1, NOUNLOAD, REPLACE, STATS=5,
            MOVE N'ProdData' TO N'S:\Data\ProdData.mdf',
            MOVE N'ProdData_log' TO N'T:\Log\ProdData_Log.LDF';

-- Missing a step here to physically remove the old database from the secondary server, at this point in "Recovery pending". If the old database is still there, the AUTOMATIC SEEDING will not work, nor the manual steps of adding a database to AlwaysOn for that matter.

ALTER AVAILABILITY GROUP [SQLQA] ADD DATABASE [ProdData];

-- Run custom maintenance/cleanup SQL code here

The big question is: what are my options to get rid of the old secondary target database?

Alternatively, anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Thanks in advance.


texts:
I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine.

Problem started 3 days back, there is long list of queries in mysql status showing table level lock, it's taking long time to open the application. To keep the application running I set wait timeout = 8. Application is running but not executing long queries.

Kill    22  DELAYED localhost   eximstats   Delayed insert  3   Waiting for INSERT  ---
Kill    155 DELAYED localhost   eximstats   Delayed insert  58  Waiting for INSERT  ---
Kill    23123   office_eoffice  localhost   office_nw   Query   0   Sending data
select data_type, fund from transaction where ID = '539329' AND productCode = 'RC11' AND folioNumber
Kill    23124   common  localhost   common  Sleep   0   --- ---
Kill    31879   office_eoffice  localhost   office_nw   Query   10  Sending data
SELECT * FROM transaction WHERE transDate = (SELECT MIN(transDate) FROM transaction WHERE ID = '4473
Kill    32289   eximstats   localhost   eximstats   Sleep   3   --- ---
Kill    32635   office_eoffice  localhost   office_nw   Query   0   statistics
SELECT * FROM clients_map WHERE map_from = '434301' AND arn_id = '396'
Kill    32643   common  localhost   common  Sleep   0   --- ---
Kill    32644   root    localhost   None    Sleep   0   --- ---
Kill    32645   root    localhost   mysql   Query   0   ---
SHOW PROCESSLIST
Kill    32651   common  localhost   common  Query   0   statistics
select nav, nav_date from nav_new where productCode='CB87' and nav_date ='2004-10-11' order by nav_

What is the permanent and long term solution?


texts:
I have hosted one web application on a staging server where I have deleted "root" users from the database. I don't want to use a common name for superuser. I have also created another admin user(Same as root user).
After this change I have tested my application and did not find any issue. But I reviewed articles where people mentioned that they were getting errors after deleting the root user.
My queries are ,

Is it safe to delete the root database?
Will it create any issue in future if remove root user?

I am still doing testing on staging server. Once this poc get done , I am planning to delete the root user from the production server as well.
EDIT
This is a new web application which I am going to host on production server and there is no admin script running.


texts:
I have table with 2 name columns:

CREATE TABLE Test
(
  TestID int identity primary key clustered
  , Name_Eng nvarchar(50)
  , Name_Nat nvarchar(50)
)

Now I need a query to get this Name column separated by ,, like this:

    DECLARE @NameColumns NVARCHAR(1024)

            SET @NameColumns = STUFF(
      (SELECT   ',' + 'Test.' + name AS [text()]
       FROM     ( SELECT    c.name
                  FROM      sys.columns c
                            INNER JOIN sys.tables t ON t.object_id = c.object_id
                  WHERE     t.name = 'Test'
                            AND c.name LIKE 'Name_%'
                ) AS D
                FOR                  XML PATH('') ,
                                         TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1,
                                     N'')

select  @NameColumns

But this query has a warning in the execution plan:

Is there any way to remove this warning?


texts: Raspberry as data aggregator node, transfer to database if Raspberry is online

texts:
I have never used SQL and currently I do read and watch various turorials and the manual of PostgreSQL. I would appreciate if someone could give me a brief overview how an experienced user would set up such a system:

I do use a Raspberry Pi to store data from different sensors locally (currently file based). The next step is to store the data in a database (e.g. PostgreSQL) on a webserver. I would like to use this data for further statistical analysis and online plotting (e.g bokeh and django). Data will be sampled at 2 Hz for several hours (e.g during a sports match). Afterwards, there will be long periods (weeks) without any data being collected. I expect to have 5 to 10 Raspberries connected to the database (a table for each Raspberry) at the same time.

As far as I understood, I could use replication to synchronize my master database (on the Raspberry) with some other servers. However, my Raspberry is not a server and not always online. I do not want to use an external hard disk because the Raspberry is battery powered and power consumption should be low. Additionally, the data is of no use on the raspberry because computational power is too low.

In essence I would like to write the newly gathered data to my web based database upon an external trigger (e.g. if a button is pressed and an Internet connection is available) and delete it on the Raspberry if it becomes too large.

Would you use a database on the Raspberry to store the sensor values and their timestamps upon insertion, or would you stick with file based storage ?

If you would use a local database, what kind of replication mechanism would you use ? As far as I understood the documentation right, Slony might be suitable. However, I am not sure if this is an overkill.


texts: Just update one column, strange execution plan

texts:
I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime.
The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query.
There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

I would not expect the query to update any index, because last_read is not indexed in any way, and book_id is not changed.

What am I missing?


texts:
I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB.

Forgetting about lock escalation, I just want to check the default level of lock granularity in SQL Server 2008? How do I do that?


texts: Optimizations for new SQL Server 2000 database that will act as email queue AND API database

texts:
Due to limitations put on me I've got to put my outgoing Email Queue in the same database as my customer information.

What are some optimizations I can make, specifically to the Email portion that will make this process smoother:

Grab emails where "sending" is set to False
Set "sending" on all those records to True
Process them one at a time, if they fail mark "sending" to False on the record
If they succeed, delete the record and add a new one to "Email Archive" table

This process will run once every 5 minutes. At the moment there will only be one app sending the emails but in the future there may be more.


texts: Cannot see SQL Agent Job history for more two days

texts:
I have a job which runs every night around 02:00 AM.

But I cannot see the job history for more than two days.Can you kindly help me on this.

The limit size is very high and we hardly have one job which runs each day.

Still the issue. Please advice.


texts:
In SQL Server 2019, is it possible to create a database user attached to the login sa? Like this:

but I get this error:

It says:

Failed at creating the user "ssrs". The use of special principal "sa" is not possible.

What I would like is to create an user which has rights on 3 databases. It will be user for SSRS basic authentication. When I ask for an SSRS report, SSRS will ask for credentials (login/pwd) and pass it to SQL Server, which will compare then to those of the sa account. It is what I want to achieve.
I saw on internet a tutorial dedicated to SQL Server configuration in order to use it with SSRS, and in this tutorial a new local login was created, with login/pwd credentials. But I can't create a new login due to company limitations.
Do I really need to create a new Login to have an user which can connect with login/pwd credentials?


texts: SQL Cluster IP should this be the same as the WFC cluster IP?

texts:
Been building my first SQL Server cluster.

One question I have: Should the SQL Cluster IP address be the same as the WFC cluster IP address?

Thanks!


texts: Table from one of the SELECTS cannot be used in field list

texts:
I'm trying the following query in a MySQL based DB:

SELECT
    alert.name, alert.new_state_date, alert.prev_state_date
FROM
    grafana.alert
WHERE
    alert.id = 1
UNION SELECT
    annotation.epoch
FROM
    grafana.annotation
ORDER BY annotation.id DESC
LIMIT 1;

My intent is to just get the data showing in the same panel so I can stop switching back and forth. However, I get the following error:

  Error Code: 1250. Table 'annotation' from one of the SELECTs cannot be used in field list


texts: Trouble with inheritance: Separate table implementation for disjoint entities and common ID

texts:
I currently have four disjoint entity types (competition, talk, award, publication) that are to be converted to tables. In order to manage certain other data for each, i.e. documents related to each entity (many documents per entry), should I create a separate table to get a common ID and then assign them resulting in costly joins or is there another method? My database course faculty taught me to make separate tables for disjoint relationships but I don't know what is the best choice here.

I understand this is a common question but I unfortunately am unable to find a proper solution or advice anywhere.

I apologize if this is a duplicate. Thank you for taking your time to answer.


texts: How to resolve the corrupted innodb table

texts:
I had a corrupted InnoDB MySql database after the customer downloading the wamp server on the computer again for wrong! so I worked on a lot and I got back most of the data, but one of the tables not working correctly so when I open that table I get this error message:

#2006 - MySQL server has gone away

I tried and changed max_allowed_packet = 100M in mysql/my.ini, but I still getting the same error message when I open that table.
Note: .ibd file of that table is available


texts:
I'm building a new reporting server using SSRS 2016 and have been given one specification I'm not sure is even possible. My supervisor wants a pop-up message whenever someone exports a report that notifies them that any data is only valid up to the time of export and any exported reports will not be supported by IT.

I get what he's after and why but I can't find anything online about how to implement this or even if it's possible. Any help would be greatly appreciated.


texts:
I have 1 user table and want to associate it with multiple other tables depends on if user have added such information (modules) and want to know what is the best way to do it.

main table (id,name,email,gender, etc ...)

other tables

conditions (id, user_id, name, etc ... )
results (id, user_id, desc, etc ... )
tests (id, user_id, date, etc ... )
etc

Currently, I'm thinking of adding the a column to the main table (called modules) and add the table names as a comma separated string or array.

(id,name,email,gender, etc ... , modules)

Retrieve the field for each user, find the table names on the list and start looking for rows on each table that match the user id.

I want to know if there's a better way to do this. Right now it seems very inefficient and will require many queries for each user.

Update:
I want to create something like a profile page that go through the different tables and list all the conditions/results/etc for a particular user in its own widget/box/div.


texts: Does logical reads in RAM appears in wait stats or where?

texts:
Concerning execution model of SQLOS (RUNNING state, RUNNABLE queue, WAITER list), what will be the state of a task when there is a logical read of pages in RAM currently going on?

If it's a WAITER list, what would be the most prevalent wait type?

Can I somehow measure the time that such operation takes?

I know lots of logical reads slow down your queries, lots of table / index scans (resides already in buffer pool) slow down your queries - I just want to know how do they appear in the statistics/dmv's or how to distinguish it from other "classic" wait types.


texts: Mongodb remove reference of corrupt shard

texts:
I have a sharded Mongo 3.0.14 setup

2 QueryRouter
1 Config server
3 Cluster (2 data node + 1 arbiter) each with 2TB

How to remove 3rd cluster without draining process? I am okay with the data loss on cluster 3


texts: Select value in group based on every/any conditions

texts:
I have a table entity_translations, which relate O->M to string_translations via entity_translation_fields. Each row in string_translations has an additional status that can be obtained via a join with current_string_translation_status.
I need to add an additional column to the result of a select from entity_translations that will be determined based on the following conditions:

If all string_translation records that correspond to an entity_translation record have status = 'READY', then that entity_translation gets status 'READY'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'FAILED', then that entity_translation gets status 'FAILED'.
Else, if any string_translation records that correspond to an entity_translation record have status = 'IN_PROGRESS', then that entity translation gets status 'IN_PROGRESS'.
Else, that entity translation gets status 'ADDED'.

What I want
Given schema and data in this fiddle, I would like to have the following result:
|  id | entity_id | entity_type |      status |
| --- | --------- | ----------- | ----------- |
|   1 |         1 |        post |      FAILED | <--- Because status for string translation 2 is "FAILED"
|   2 |         2 |        post | IN_PROGRESS | <--- Because status for string translation 5 is "IN_PROGRESS"
|   3 |         3 |        post |       READY | <--- Because status for string translation 6 is "READY"

Here's the query I used to get the data that I would like to aggregate into the above desired result:
SELECT *
FROM `entity_translations`
JOIN `entity_translation_fields`
    ON (`entity_translation_fields`.`entity_translation_id` = `entity_translations`.`id`)
JOIN `string_translations`
    ON (`string_translations`.`id` = `entity_translation_fields`.`string_translation_id`)
JOIN `current_string_translation_status`
    ON (`current_string_translation_status`.`string_translation_id` = `string_translations`.`id`)
ORDER BY `entity_translations`.`id` ASC, `string_translations`.`id` ASC;

Markdown tables weren't rendering for some reason, so I just added it as code.
What I tried

One way could be to join entity_translation with string_translation (and its status view), then group by entity_translation's PK. But I don't know how I could then determine the values from the status column that is not in GROUP BY. If ANY() and ALL() were aggregate functions, I could use them in multiple nested IF conditions; but they're not.
Perhaps, another way is to use a subquery to add a column to string_translation. But I don't understand how to then use ANY or ALL with yet another subquery.

Here is the whole setup. It has some extra things that are not relevant to this current problem. The query that is relevant is the one that says the following on top:
-- Get data for all entity translations, including their current status <----- PROBLEM


texts: How to use MySQL stored function argument to refer to column name?

texts:
How can I use a function argument s to stand in for a column name in a query?  I'm using mysql 5.5.  I tried

SELECT count_distinct(last_name)

but it returns

ERROR 1054 (42S22): Unknown column 'last_name' in 'field list'

The function was defined as

CREATE FUNCTION count_distinct (s CHAR(20) )
RETURNS INT
READS SQL DATA
RETURN( SELECT COUNT(DISTINCT s) FROM president);

I expected it to return a value of 37 as

SELECT COUNT(DISTINCT last_name) FROM president

returns 37.
The 'president' table is from the sampdb database from MySQL by Paul Dubois.


texts:
When I install the server for the first time, when almost finished it reports:

(from log file "%temp%\bitlock_installer1376.txt")

Loading additional SQL modules...
Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.3\installer\server\loadmodules.vbs" "postgres" "****" "C:\Program Files\PostgreSQL\9.3" "C:\Users\ibrahim\PostgreSQL\9.3\data" 5432
Script exit code: 2

Script output:
 Installing the adminpack module in the postgres database...
    Executing 'C:\Users\ibrahim\AppData\Local\Temp\rad4E73F.bat'...
psql: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

Failed to install the 'adminpack' module in the 'postgres' database
loadmodules.vbs ran to completion

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo "C:\Program Files\PostgreSQL\9.3\installer\server\loadmodules.vbs" "postgres" "****" "C:\Program Files\PostgreSQL\9.3" "C:\Users\ibrahim\PostgreSQL\9.3\data" 5432 : Program ended with an error exit code

I cannot start pgAdmin neither doint anything.

In file "pg_hba.conf" I have:

host        all           all            127.0.0.1/5432           md5


texts: How to check if feature (JSON) exists, and/or version

texts:
This is a cross-posting from an original question at How to check version is greater than some base value?

When calling MySQL SELECT @@VERSION; or SELECT VERSION();, I get for instance '5.7.11-log'. Is there a "well-known" way to check if the version is greater (or smaller) than some major.minor.patch version? If not a well-known way, is there a way without using a temporary table or a user-defined function (there probably is, but for a reason or another currently eludes me)? I would use this to check if there's support for JSON type type that was introduced in version 5.7.8.

Upon researching this more, it appears this is a bit tougher nut for my skills to crack. For instance, I could write something like

SELECT
    SUBSTRING_INDEX(@@VERSION, '.', 1) AS major,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@@VERSION,'.', 2), '.', -1) AS minor,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@@VERSION,'.', -2), '.', -1) AS patch;

but that isn't entirely satisfactory (e.g. see -log) and it doesn't directly check the existence of the feature.

Is anyone aware of better ways?

<edit: To amend Rick's answer a bit, the comments syntax can be used to recognize constructs too, not just versions. For instance, in this particular case like this SELECT /*!JSON_TYPE*/('["a", "b", 1]');. Here's a link to the documentation: http://dev.mysql.com/doc/refman/5.7/en/comments.html.


texts: Why is % forbidden char in THROW message?

texts:
Simple question:

THROW 50000, 'Because of this ->%<- is this message invisible.', 1;

you can't pass %s, %d parameters as in RAISERROR
you have to use FORMATMESSAGE
but THROW failed anyway if % is not doubled (correct displaying)

Tested on Microsoft SQL Server 2012 (SP1) 11.0.3381.0 (X64), 11.0.3000.0 (X64)

Real world example:

DECLARE @ProcParam VARCHAR(30) = 'Hehe%x!';
DECLARE @Message VARCHAR(500) = FORMATMESSAGE('Procedure input %s is wrong', @ProcParam);
PRINT 'Message: ' + @Message;
THROW 50000, @Message, 1;


texts: Mysql+auth plugin - logging external username

texts: When using an an auth plugin (specifically pam) does the general query log record the external username for connections?


texts:
I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61.

I want to upgrade the master server to a stronger machine with the following:

2 processors - 6 cores each
48GB of RAM

To utilize both processors I need to install the InnoDB Plugin

I have a few questions:

Is it possible to install the InnoDB plugin just on the master and not on the slave?
Are there any other implications to installing the plugin?
What parameters do I need to change in my.cnf to better utilize the plugin and the multi core options?
Any recommended tutorial for installing the plugin?


texts: Design: How to account for a custom product in a transaction item table

texts:
This question is related to Database Design: inventory and sales system?

So for example a burger is a Product which is made up of Component bun, meat, cheese, pickles etc. When this burger is sold it can be customized by adding/removing/modifying components.

How is this customization accounted for as a line item? Would there be another table ProductCustomization with a reference to the Product and the Component and a column to indicate the type of customization? What about if you want to modify the Composition of a Component in the Product (ex. 3 pickles per burger).

An example of a customization would be ordering a burger with no pickles, or adding extra cheese.


texts:
I'm looking for a way to dynamically build up the number of columns in a query.

Imagine a school assessment database.
I desire a way to get, for each question the percentage of students who got a certain mark. The problem is there is a dynamic number of marks available for each question.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE mark
(
  id       INT   NOT NULL AUTO_INCREMENT
    PRIMARY KEY,
  mark     FLOAT NOT NULL,
  question INT   NOT NULL,
  student  INT   NOT NULL
);

CREATE TABLE question
(
  id          INT   NOT NULL AUTO_INCREMENT
    PRIMARY KEY,
  maxMark     FLOAT NOT NULL,
  description TEXT  NOT NULL
);

INSERT INTO question (description, maxMark) VALUES ('Question 1', 2);
INSERT INTO question (description, maxMark) VALUES ('Question 2', 3);

INSERT INTO mark (mark, question, student) VALUES (1, 1, 1);
INSERT INTO mark (mark, question, student) VALUES (2, 1, 2);
INSERT INTO mark (mark, question, student) VALUES (3, 2, 1);
INSERT INTO mark (mark, question, student) VALUES (0, 2, 2);

Query 1:
The closest I have been able to come up with is:

SELECT
  question.description,
  avg(mark),
  avg(mark) / question.maxMark  AS percent,
  question.maxMark,
  SUM(CASE WHEN mark = 0
    THEN 1
      ELSE 0 END) / count(mark) AS zeroPercent,
  SUM(CASE WHEN mark = question.maxMark
    THEN 1
      ELSE 0 END) / count(mark) AS fullPercent
FROM mark
  INNER JOIN question ON mark.question = question.id
GROUP BY question

Results:
Which produces the below result:

| description | avg(mark) | percent | maxMark | zeroPercent | fullPercent |
|-------------|-----------|---------|---------|-------------|-------------|
|  Question 1 |       1.5 |    0.75 |       2 |           0 |         0.5 |
|  Question 2 |       1.5 |     0.5 |       3 |         0.5 |         0.5 |

Desired result:

+-------------+-----------+-----------+-----------+-----------+-----------+
| description | avg(mark) | percent 0 | percent 1 | percent 2 | percent 3 |
+-------------+-----------+-----------+-----------+-----------+-----------+
| Question 1  | 1.5       | 0         | 0.5       | 0.5       | NULL      |
+-------------+-----------+-----------+-----------+-----------+-----------+
| Question 2  | 1.5       | 0.5       | 0         | 0         | 0.5       |
+-------------+-----------+-----------+-----------+-----------+-----------+

Here is something similar, but it builds up the query based on distinct results, Mine needs to build it off question.maxMark.


texts: SQL Server Backup - move to new server - Media Failure

texts:
I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise.

We want a local duplicate of the DB to test and build a new report.
Backing up the relevant DB, and then transferring over FTP (binary-mode) to here in the USA ends up saying in SQL Server 2012:

  Media family on device is incorrectly formed. SQL server cannot process this media family.

I also tried transferring using File Transfer 1.2j (from Sourceforge), as well as splitting them into zips/7zip chunks and copying them slowly through RDP.
None of them load on our SQL Server 2012 machines. This is the key difference between my case and other SO posts I've read. They're going backwards in version. I'm going forwards, which is supposed to be supported.
I've also verified the files on both ends using certutil to generate SHA-1 hashes.
Lastly, the backup CAN be restored in the UK into itself! So the backup isn't corrupt when coming from SQL Server. I'm also 99% sure it's a copy-only backup so it doesn't need any other differential files.

So the only thing left I see is corruption across the internet (yet SHA-1 says otherwise), or, we're not saving correct backups, or, somehow our USA SQL doesn't want to load the older UK SQL. Does localization even matter, especially if they're both English?


texts: Ensure that only one record has a specific value

texts: Let's say there's a table that stores jobs and their state (something like CREATE TABLE jobs (id INT, status VARCHAR(255)), how to ensure that only one job could be in running state i.e. how a query that tries to start a job should look like?


texts:
I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump.

Is there any tool out there that will allow me to compare my SQL dump with the live database and import any new data?


texts: What is the difference between MySQL and PostgreSQL in terms of writing SQL queries?

texts:
Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on?

I'm asking this because I'm familiar with PHP with MySQL (phpMyAdmin) but not with Postgres (phpPgAdmin)


texts: How to change results cell padding in Azure Data Studio

texts:
In Azure Data Studio, I am trying to change the padding on the cells in the results grid.

According to the settings page, this is editable in the settings.json:

However, when I navigate to that file there is no mention of padding anywhere. Plus, searching online for the correct option to use gives no results.


texts: Copy Database error Index was out of range. Must be non-negative and less than the size of the collection

texts:
Trying to "Copy Database" from SQL Server 2016 to SQL Server 2012 through SSMS. It fails, and destination server's event log says

  Index was out of range. Must be non-negative and less than the size of
  the collection. Parameter name: index

Edit Nov. 24, 2016
Tried Copying from SQL Server 2016 to another SQL Server 2016 and got same error.


texts: MariaDB is Forgetting Previous Auto Increment IDs

texts:
I'm using MariaDB 5.5.39, and it seems like it is forgetting the max id of the auto-incremented primary key if I delete all rows from the table and then too much time passes before another row gets inserted. Here's the schema according to HeidiSQL:

CREATE TABLE `submissionqueue` (
    `SubmissionID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `JSON` TEXT NULL,
    PRIMARY KEY (`SubmissionID`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=354;

The original AUTO_INCREMENT was probably 1, HeidiSQL is probably just indicating what the code would be to create the table right now.

I'm using this table as a queue to hold submissions until approval, where the id column is auto incremented.

Later, the queue is processed leaving this table empty. When a new submission occurs, if the table is functioning correctly, a new row is inserted into this table and it receives a new auto increment ID that is higher than the IDs of all previously inserted rows (even though most "previous rows" were deleted long ago).

Over time, more than 600,000 rows have been processed from this queue. So you'd expect the auto increment id to be in the 600,000s.

However, for some time now, the auto increment IDs keep becoming reset. And when I go to process this queue, the auto increment IDs have started over at 1.

It seems like, when I empty that table, and too much time passes before another row is added to it, MariaDB is forgetting the max auto increment ID that was previously in that table and instead the IDs start over at 1.

This is a problem because during processing, any rows deleted are first moved to another table with the exact same schema. Because the auto increment IDs have started over, the program errors out because the ids are no longer unique in respect to all previously processed rows.

For years this program worked flawlessly, but at some point the database started forgetting the correct auto id, starting over at 1 instead.

This table, I'm experiencing this with, has never been dropped. The only thing I do, is remove all the rows that are in the table each time the queue is processed. After this, if I immediately go insert a row, it will received the proper Auto Increment ID (respecting all the previous ids that use to be in the table). Again, however, if too much time passes before an insert occurs, the increment starts over instead of respecting previously existing row's auto IDs.

I'm just using plain old delete statements like:

delete from tableName where id < 600000


texts: Why am I getting PLS-00103 when I try to create a table in a PL/SQL procedure?


texts: Conflicting 'LIKE' conditions in MySQL CASE statement

texts:
I have two conditions that are causing the CASE statement not to work as intended. I've tried changing which condition comes first, but it still only proceeds with one of them. Any suggestions?

CASE statement that returns only the value before the first '(' and replaces all instances of 'Metallic' with 'M':

CASE
WHEN `order_option`.`value` LIKE '%(%)%' THEN SUBSTRING_INDEX(`order_option`.`value`,' (', 1)
WHEN `order_option`.`value` LIKE '%Metallic%' THEN REPLACE (`order_option`.`value`,'Metallic','M')
ELSE `order_option`.`value`
END AS `Option`,

Current data

`value`
--------
Red Metallic (999)
Blue (745)

Expected results

`Option`
--------
Red M
Blue

Thank you in advance!


texts:
I just set up a PostgreSQL 9.1.2 database server on debian squeeze.
I configured a standby server (hot standby) on a second identical server (squeeze, PG 9.1.2).

On the master I set these parameters (postgresql.conf) :

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /mnt/pg_replication/myserver/%f && cp %p /mnt/pg_replication/myserver/%f'
archive_timeout = 30
max_wal_senders = 3
wal_keep_segments = 100

On the slave, I set the following parameters (postgresql.conf) :

hot_standby = on

On the slave (recovery.conf) :

standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=repluser password=xxxx'
restore_command = 'pg_standby -d -s 2 -r 30 -t /tmp/pgsql.trigger.5442 /mnt/pg_replication/myserver/ %f %p %r 2>>/var/log/standby.log'
recovery_end_command = 'rm -f /tmp/pgsql.trigger.5442'
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /mnt/pg_replication/myserver/ %r'

This works well but I noticed that the replication delay can take up do 1 minute.
Is there a way to reduce the delay for changes to apply on the standby to a few seconds ? I reduced archive_timeout to 30 seconds but I don't know if this is the right way to make the replication faster...

Another question : how can I monitor the replication delay between the two servers ? I tried the following query on the master but it returns 0 rows... :-?

postgres=# SELECT * from pg_stat_replication;
(Aucune ligne)

Did I missed something ?

Thank you,
Nicolas

EDIT 2012-01-03 : I changed my recovery.conf to this and it is now working well :

restore_command = 'cp /mnt/pg_replication/myserver/%f %p'
standby_mode = on
primary_conninfo = 'host=master_ip port=5432 user=repluser password=xxxx
application_name=mycluster'
trigger_file = '/tmp/trig_mycluster'


texts: mysql - insert multiple values sharing same foreign key

texts:
I have the following tables:

create table countries (
    country_id tinyint unsigned,
    iso_alpha3_code varchar(4)
);

create table provinces (
    province_id bigint unsigned,
    province_name varchar(50),
    country_id tinyint unsigned,
    foreign key (country_id) references countries(country_id) on delete restrict on update cascade
);

For inserting records into provinces, the corresponding country_id is needed:

insert into provinces (province_name, country_id) VALUES
    ('Alabama', (select country_id from countries where iso_alpha3_code = 'USA')),
    ('California', (select country_id from countries where iso_alpha3_code = 'USA')),
    ('Alaska', (select country_id from countries where iso_alpha3_code = 'USA'));

How to insert these records without repeated-select statements to get the foreign key? Can something like CTE be used?

PS: I found this, but it's for postgreSQL.


texts: Can I grant access to a MySQL user for a single partition of a table?

texts:
Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it...

Here's a test case:

CREATE TABLE testing (
    id INT UNSIGNED NOT NULL
)
PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);
INSERT INTO testing (id) VALUES (2),(20),(200),(2000),(20000),(200000);
SELECT * FROM testing;

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT USAGE ON *.* TO 'foo'@'localhost';
GRANT SELECT ON my_test.testing TO 'foo'@'localhost';
FLUSH PRIVILEGES;

-- Now use foo@localhost: you shouldn't see 2k, 20k and 200k values
SELECT * FROM testing;

What would the proper GRANT syntax be?


texts: MySQL Performance comparison between loading comma separted vs fixed-width large file

texts: I have a file with 50 Gb, 7 columns and fixed-with. I couldn't succeed to load the data in my database (MariaDB 5.5.31, Windows 7, MYISAM) that is: after 15 hours I stopped the loading process. Does comma seperated file may perform better than fixed-width?


texts: Which changes to a table in SSMS cause a DROP/CREATE and can this always be avoided with TSQL?

texts:
I'm aware that there are some circumstances where making certain changes to a table (for example, changing the type of a column) through the GUI of SSMS will cause the table to be dropped and re-created.

My question is, does a complete definition exist of exactly what changes cause this behaviour? And can this behaviour always be avoided by running an ALTER TABLE instead of using the SSMS GUI (and if not, which can be avoided and which cannot)?


texts: ODBC Timeout error with SQL server connection

texts:
I have an odd problem.  The company has used an ODBC connection for a desktop MDB program to connect to SQL Server.  The ODBC connects using a SQL Server account to authenticate.  It has worked without issue in the past.

Recently, it appears any new domain account does not connect.  They always fail for an connection timeout error.  I've tried creating brand new account, I've tried creating an account by "Copy as" and they still get a timeout error.

One additional twist, the first time I discovered this error, last week, was when I was moving a user to a different computer.  The ODBC connection worked on her old computer but when logging on the new one, it timed out but still worked on her old computer.  I also tried logging the user in on MY computer and her profile does a timeout.

I've tried rebooting the Domain Controller as well as the SQL Server.  No changes on the network or any server.


texts: Selecting data from another table using a foreign key

texts:
I have two tables A & B.
Table A
id | name | num

Table B
id | date | roll

id in table B is a foreign key which relates to the id in table A.
I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A.
NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.


texts: How to read metadata from Sqlite database

texts:
Sometimes I use SQLite database but it's quite common that I don't know anything about its structure.
I need to retrieve all metadata like the name of a database or table, the data type of a column, primary keys, foreign keys, or access privileges.
MSSQL has information_schema for this purpose but is there something similar for SQLite?


texts:
I am trying to troubleshoot an e-commerce site problem that seems to be database related. In my investigation so far, I noticed that the server seems to have an unusually high numbers relating to Aborted_clients: 65206
and Aborted_connects: 394618

This is on a server that was restarted two weeks ago.

I'm not a DBA, but I suspect that this has something to do with the issue. Can anyone fill me in on what these values mean and if it could be causing availability issues?

Thanks


texts: Does having unused columns effect the indexing of the table

texts: I have a table where there are some columns which are empty and they are not used. That table has to be indexed multiple times based on the application. Does having these empty columns in my table slow down the indexing of the other columns or it does not have any effect?


texts:
I would like to know whether replication or clustering for MySQL is it possible over a WAN environment?

For example

Site A and Site B are both at different remote place.
Both have MySQL installed.
Both are using different internet modems.
Both are running Windows.

Can they replicated each other's date?

Are there any guides that I can follow to setup clustering over WAN?


texts: MySQL Query Optimization : Bets and Averages


texts: Do cross-joins get optimised by the client library?

texts:
Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.

I came across Joins are for lazy people on SO, and it's made me question an assumption I'd made.

  Joining in the app server can be more
  efficient if joining on the database
  causes severe redundancy in the result
  set sent over the network. Consider
  tables A and B, where each row in A is
  associated with 20 rows in B, B has
  only 100 rows, and we want to fetch
  the first 1000 rows from A with
  associated rows from B. Joining in the
  database will result in 20 * 1000
  tuples sent across the network. If the
  join is done in the app server (first
  fetching the entire B table into
  memory), a mere 100 + 1000 rows are
  sent across the network.

I kinda assumed that a cross-join would be optimised in the client library, so that less data was sent over the network, and then merged, so that the app didn't need to glue the data together. Am I plain wrong, or does it vary depending on the database?


texts: Find repeating tickets with more than one criteria

texts:
Lets say I have 2 tables: atms & tickets
each atm has many tickets.

ATM table:

ID    | NAME
-------------------------
A0109 | ATM 1
A0227 | ATM 2
etc.  | etc.

Ticket table:

ID    | ATM     | DATE_SUBMITTED        | PROBLEM_NAME     | STATUS
---------------------------------------------------------------------
1     | A0109   | 2014-02-01 17:00:00   | OUT_OF_PAPER     | CLOSED
2     | A0109   | 2014-02-08 17:00:00   | BLANK_SCREEN     | OPEN
etc.  | etc.    | etc.                  | etc.             | etc.

In general repeating rule (more than 1 ticket per ATM), I can use HAVING condition where COUNT(tickets.id) > 1 and GROUP them by ATM ID. But the problem is, the "repeating" criteria is different. The condition considered as repeating is any ATM that has more than 2 tickets in 1 week or more than 3 tickets in 1 month. How can I create SQL query for this problem?

Any help would be appreciated,
thanks in advance


texts:
I have certain synonyms in my schema which represent views from another schema. Now in oracle sqldeveloper, I am unable to see the actual SQL for those views but I see only an SQL like:

CREATE OR REPLACE SYNONYM "ABC"."ABC_CHAR" FOR "XYZ"."ABC_CHAR";

Now my question is: is there a way for me to see the Actual SQL for the View XYZ.ABC_CHAR that is in the other schema?


texts: How to open Oracle listener for three database instances?

texts:
I have 3 database instances:

orcl
JIP02T
JIP04T

When I start the database, does it start all 3 instances?
When I start the Oracle listener, does it start a listener for all 3 instances?

When I echo $ORACLE_SID:

[ora112@localhost admin]$ echo $ORACLE_SID
orcl

I can only see orcl, and the listner is started only for one database instance. I can connect to only one database instance using SQL Developer.

How can I start the database and listeners for other database instances as well?


texts: MariaDB configuration for many updates, writes

texts:
Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 million rows, and we are running updates every second. So I am trying to create an optimal configuration for writing. A simple update record sometimes need 2-3 seconds. Should I change something to increase update queries speed?

[mysqld]
# MyISAM
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY
innodb                         = FORCE
innodb_strict_mode             = 1
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve

# BINARY LOGGING
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS
max_connections                = 500
max_heap_table_size            = 32M
open_files_limit               = 65535
query_cache_type               = 0
query_cache_size               = 0
table_definition_cache         = 4096
table_open_cache               = 10240
thread_cache_size              = 50
tmp_table_size                 = 32M

# INNODB
innodb_buffer_pool_size        = 12G
innodb_buffer_pool_instances   = 12
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_log_files_in_group      = 2
innodb_log_file_size           = 1G

# LOGGING
log_error                      = /var/lib/mysql/srv.defrop.com.err
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
long_query_time = 2

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                 = 0

#custom
performance_schema = on

Example log of query

# Time: 190415 10:36:59
# User@Host: defrop_defdb[defrop_defdb] @ localhost []
# Thread_id: 2420  Schema: defrop_defrop  QC_hit: No
# Query_time: 2.404021  Lock_time: 0.000053  Rows_sent: 0  Rows_examined: 2
# Rows_affected: 1
SET timestamp=1555317419;
UPDATE `backlinks`
SET
`backlinks`.`crawler_id` = '10.0.0.28',
`backlinks`.`used_time`=NOW()
WHERE
`backlinks`.`campaign_id`=710
AND `backlinks`.`googlebot_id` IS NULL
AND `backlinks`.`used_time` IS NULL
LIMIT 1;


texts: Can Fill Factor be changed on specific tables without index rebuild?

texts:
I've been digging around without any luck.

SQL Server 2008: Is it possible to adjust the fill factor on a specific clustered index/table without having to rebuild the entire table?

For example, if it has 400 million records, can we adjust the fill factor and have SQL Server use the new fill factor on all pages that haven't already exceeded that limit (as well as new splits), and then adjust pages that are over limit during index maintenance?

Is it possible to pre-define the fill-factor that an index should be built to during the next index maintenance?


texts: SQL Server Availability Group does not update DB owner automatically

texts:
I learned a hard lesson this week. It turns out what while table updates/ schema updates do get replicated across databases on an Availability Group, changing the DB Owner on the primary replica does not change the DB owner on the secondary replica automatically.

Thus if you perform a failover, the database owner could now be out of sync with the master table's SID.

Should my plan of action when e.g. making a security related change be to

1. Apply change to primary replica
2. Fail over the Availability Group
3. Apply change to the new primary replica

Am I correct in making these statements? Is it only DBOwner/ Schema changes that I must be aware of when I am making changes.


texts: Clustered Index during data load in MS SQL Server

texts:
Until today, I was under impression that it's been considered as a best practice to disable the indexes in sql tables during the data load. And with the fact that one cannot disable the clustered indexes in MS Sql server, you would have to delete that and then perform the data load operation. But today, I performed the data load operation of around 30M records with 10-15 columns of different column types and I was surprised to see the result where keeping the clustered index out performs the load compared to dropping the clustered index, loading data and then creating the clustered index.
In my test, the data which was being inserted was presorted in the source table and that might be one reason for the difference, but I just want to understand is it still considered as a best practice to drop the clustered index prior to data load and recreate it after or keeping the clustered index is the optimal way?


texts: Optimize MySQL Query with multiple subqueries

texts:
I have the following Query:

SELECT
      p.id AS post_id,
      p.status,
      p.user_id AS post_user_id,
      ( SELECT  pl.id
            FROM  postlocks AS pl
            WHERE  pl.post_id = p.id ) AS postlock_id,
      ( SELECT  COUNT(rn.id)
            FROM  renews AS rn
            WHERE  rn.post_id = p.id ) AS renew_count,
      ( SELECT  rn.status
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS last_renew_status,
      ( SELECT  TIMESTAMPDIFF(SECOND, rn.timestamp, CURDATE())
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS date_diff_from_renew,
      TIMESTAMPDIFF(SECOND, p.timestamp, CURDATE() ) AS date_diff_from_post
    FROM  posts AS p
    HAVING  (postlock_id IS NULL)
      AND  p.status='success'
      AND  ((date_diff_from_renew IS NOT NULL
                      AND  date_diff_from_renew > 172800
                      AND  last_renew_status='success')
              OR  (date_diff_from_renew IS NULL
                      AND  date_diff_from_post > 172800)
           )

It takes about 3 seconds to execute this query.

Here is the EXPLAIN:

+----+--------------------+-------+-------+---------------+---------+--    -------+----------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref      | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+
|  1 | PRIMARY            | p     | ALL   | NULL          | NULL    |     NULL    | NULL     | 2988 |             |
|  5 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  3 | DEPENDENT SUBQUERY | rn    | ref   | post_id       | post_id | 4           | rto.p.id |    2 |             |
|  2 | DEPENDENT SUBQUERY | pl    | ALL   | NULL          | NULL    |     NULL    | NULL     |    1 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+

Is there any way to optimize this query, or do I need to do a total rewrite another way?

Thanks.


texts: Why are there empty transactions in the MySQL binlog?

texts:
I am using Percona Server for MySQL 8.0.x. I have setup replication between two instances with GTID mode turned OFF. Almost all UPDATE and INSERTS are wrapped in the transaction from the application side.

On the slave server when looking at the binlog coming from master, I noticed there are lots of empty transactions. What are those and why are they present in the binlog when there is nothing to be executed in that transaction.

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
# immediate_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
/*!80001 SET @@session.original_commit_timestamp=1587391567871055*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1587391567/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;


texts:
I'm currently facing this problem in one of my Production environments.

I have an AG with around 130 DBs with readable secondaries enabled. However, there are not routing lists defined and applications are not using it. From time to time, tempdb on my secondary fills up completely.

Doing an evaluation what I was able to get was.

tempdb size: 255GB

free pages  free space in MB
27571456    215402.000000

version store pages used    version store space in MB
5130624                      40083.000000

To see what is using row versioning I am using the following query.

select
    hostname,
    elapsed_time_seconds,
    session_id,
    is_snapshot,
    blocked,
    lastwaittype,
    cpu,
    physical_io,
    open_tran, cmd,
    DB_NAME(b.dbid)
from
    sys.dm_tran_active_snapshot_database_transactions a
join master..sysprocesses b
    on a.session_id=b.spid
order by
    a.elapsed_time_seconds desc

From this point I am stuck with ideas on where to look the problem. I also didn't want to restart the secondary as this is not going to fix the issue.

Any ideas that someone could share with me?

Regards,
Marcos Freccia


texts: Deadlock detected on CREATE TABLE in PostgreSQL

texts:
I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C.

Scripts

CREATE TABLE "TableA"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableA_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableA_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE "TableB"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableB_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableB_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

This table exists in moment of above scripts execution

CREATE TABLE "TableC"
(
  "Id" uuid NOT NULL,
  CONSTRAINT "PK_TableC_Id" PRIMARY KEY ("Id"),
)
WITH (
  OIDS=FALSE
);

So when I take a look at the logs I get the following error, and relation 87066 is the TableC. Note that TableA & TableB are created in separate transaction using the READ COMMITTED isolation.

  Process 464 waits for AccessExclusiveLock on relation 87066 of
  database 86965; blocked by process 1180. Process 1180 waits for
  AccessExclusiveLock on relation 87066 of database 86965; blocked by
  process 464.

When I take a look at PostgreSQL documentation it says that one should execute create tables in same order in order to avoid the deadlocks and they are executed in the same order so no deadlock should occur.

Is there any reason why this ends with deadlock? I think that process 464  should wait for 1180 if 464 was the first one to execute ?

Edit

One other note is that I'm running one insert in the same transaction before create table script.

INSERT INTO public."TableD"("Id", "TableCId", ....)
VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)

PostgreSQL 9.3


texts:
I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years.

This year I did not do a backup and accidentally dropped the database of 2011.  The schema is always the same for all the databases, but of course the entries are different for each year.

After dropping the database of 2011, the folder no longer exists in C:\Program Files\MySQL\MySQL Server 5.0\data, but I see ib_logfile0 and ibdata1 files under the above path.  Is there any way I can restore the dropped database?

There was no backup done for 2011 and I do not know what is binary logging.


texts:
I have Problem:

  #1066 - Not unique table/alias: 'clients'

SELECT request.*,
       clients.ClientName AS ToClient,
       clients.ClientName AS FromClient,
       drivers.DriName, requesttype.ReqTName
FROM request
    INNER JOIN clients ON clients.ClientID = request.ReqToClient
    INNER JOIN clients ON clients.ClientID = request.ReqFromClient
    INNER JOIN drivers ON drivers.DriID = request.DriID
    INNER JOIN requesttype ON requesttype.ReqTID = request.ReqTID


texts: What happens when a stored procedure executes one of two different queries based on an input parameter?

texts:
I have encountered a stored procedure that performs one of two different queries based on an input parameter.  Does this remove the plan from cache and force the query plan to be recompiled if the input parameter changes?  What are the performance implications of implementing control flow in this way.

Example:
CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT ColA FROM TableA
 END
ELSE
 BEGIN
  SELECT ColB FROM TableB
 END
END