texts: Attribute and Value tables on SQL - Is this a good practice?

texts:
I'm not a SQL expert and I'd like to ask for the best practices on the following schema.

On my current project, we're using Attribute and AttributeValue tables quite often. I'm thinking this might not be a good practice since it involves joins when we need that data. Example:

Given the Person table, there's two more tables that basically contain "extra" data about person; those are PersonAttribute and PersonAttributeValue.

I don't have a strong argument to prevent this practice on my team, but I'd like to hear what SQL experts have to say.


texts: Percona pt-table-sync Can't determine Master

texts:
We have following Multi master replication scenario. We are using MariaDB 10.1.34 and Percona 3.0.11

Master: Name: db-master1 (192.168.14.5)

Slave:
Name: db-slave2 (192.168.15.5) Replica Connection name: master1 (192.168.14.5)

SHOW SLAVE 'master1' STATUS\G on slave server is working fine.

I deliberately produced the data inconsistency and deleted 5 records in Test.dummy table on slave. Once I ran the below command to sync:

pt-table-sync --replicate --sync-to-master h=localhost,u=checksum_user,p=checksum_password --check-master --channel=master1

I got the error:

  Can't determine the Master of h=localhost


texts: How to represent recursive relationships in a graph database?

texts:
I am designing a database schema diagram for a Neo4j database. This is what I have so far:

This is the cypher code:

CREATE
  (`0` :Person {FirstName:'string',LastName:'string',DOB:'long'}) ,
  (`1` :Address ) ,
  (`2` :`Phone Number` {Number:'string'}) ,
  (`3` :Record {Description:'string',Date:'long'}) ,
  (`5` :Association {Type:'string',Name:'string',`Nature Of`:'string'}) ,
  (`0`)-[:``Lived At`` {StartDate:'long',EndDate:'long'}]->(`1`),
  (`0`)-[:`Has` {StartDate:'long'}]->(`2`),
  (`0`)-[:`Had` {StartDate:'long',EndDate:'long'}]->(`2`),
  (`3`)-[:`References` ]->(`1`),
  (`3`)-[:`References` ]->(`0`),
  (`3`)-[:`References` ]->(`2`),
  (`0`)-[:`Has` {StartDate:'long'}]->(`5`),
  (`0`)-[:``Found At`` {Date:'long'}]->(`1`),
  (`0`)-[:``Lives At`` {Date:'long'}]->(`1`),
  (`0`)-[:`Had` {StartDate:'long',EndDate:'long'}]->(`5`),
  (`3`)-[:`References` ]->(`5`)

One of the domain requirements is that a person can be related to a person. How do I represent this relationship in my database diagram?


texts: Keeping service up while taking drive offline in clustered instance

texts:
I have clustered instance with two (2) data drives on it. Drive J has around 50 databases on it and drive K has about 15. One of the system administrators was trying to increase the size on K by 20 GB and he instead increased it by 200. Now he needs to take the accidental 180 GB back but the drive will need to come offline briefly. Is it possible to take the K drive offline, along with the databases on it, without taking the whole instance down?

I believe it's possible but I'm concerned about the SQL service cluster resource's dependence on the drive in question. Since the drive is setup as a dependence, would I need to remove the dependence first, then take the drive offline without affecting the SQL Server service status?

Thank you.


texts: Application/MS SQL Server Express Installer

texts:
We're developing a small(ish) application that collects and stores data. It's low-throughput, low-volume, but needs to be able to archive and retrieve data from (almost) arbitrarily earlier times, hence the decision to store the data in a MS SQL Server Express database. To the user, the database is invisible. The setup is:

Application - Visual Studio 10 (yes, 10) C#, .NET Framework 4.0
Database schema (tables, views, queries ...)
MS SQL Server Express 2008 SP2
MS Visual Studio-generated Installer (not the Installshield one), which ...

installs the application
installs .NET 4.0 (if needed)
installs MS SQL Server Express 2008 SP2
does not create the actual database

At this point, we can create the database using MS SQL Management Studio or Visual Studio.  But for production deployment, the creation of the database must be automated, either by the installer or by a start-up functionality of the application: Starting out from a fresh Windows 7 or Windows 10 installation (the PC will not be availlable to us beforehand), running the installer and then starting the application (e. g. by our service personnel) must be enough. Also, the application should not depend on which user is logged onto the PC.

Question: How do I make the database, err ... appear ... on the target PC?

Options I've considered so far:

Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)
Same as above, but install the file into the CommonAppDataFolder, attach it to SQL Server.
Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

Which option would you recommend?


texts: Import a directory of dbf files to sql server

texts:
I am trying to import .dbf files using for each container in SSIS. But i keep getting errors to map the files and other errors.

Here is what did.

Use for each container.
Use a data flow task in the container.
Use OLE DB source for Visual FoxPro (or jet ) and set extended properties as DBase IV and connection string as variable from expressions.
Use another variable for sql server table name, removing .dbf extension.
Use a SQLOLEDB destination and set its table as table, view or variable name. Set the variable to created one in step 4.

As the SQL Server table is not there already, how can I create and import dynamically. There are a lot of .dbf files so I am looking for dynamic creation of the same and import.


texts:
In my example syntax below I want the field delayed to be the difference in days between gotitdate and dunitdate  I know that this syntax will get me the result, and I was going to set this up as an OnInsert() and a AfterUpdate() trigger however my two fields are not required!  I can not alter table structure to make them be required.

Question at hand, how can I use this update syntax to account for a null date, to update to 0 if either field is null or update to the DateDiff() if both fields are not null?  And as always, if there is a better approach for this methodology I am open to suggestions:

Create Table #Moofasa
(
  gotitdate date
  ,dunitdate date
  ,delayed int
)

Insert Into #Moofasa (gotitdate, dunitdate) Values
('07/01/2016','07/05/2016')
,('07/01/2016','07/04/2016')
,('07/04/2016','07/08/2016')

Update #Moofasa
Set delayed = DateDiff(d, gotitdate, dunitdate)
WHERE delayed is null

Select * from #Moofasa

--Drop Table #Moofasa


texts: Ensuring 1 to 1 mapping through a many to many table

texts:
I've currently got a database with two tables, "Product" and "Option". Each parameter is tied to one product. We want to extend this data with a new concept of a "Project". A product will have many projects and each project will list what the default values for each option are for that product. The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

E.g. if a product has options A, B, and C. Each project needs to define the default value for A, B, and C. Adding a new option should require updating all the projects.

Any pointers?


texts: `ts_rank` is returning a numeric/decimal/real that fails a "greater than" check

texts:
The following yields (seemingly exactly) 0.1:
select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'));

Yet the following yields t:
select ts_rank_cd(to_tsvector('cat'), to_tsquery('cat')) > 0.1;

I would expect it to yield t if the operation was >=, not > How can I get the "real" value of the ts_rank_cd so that it returns f?

As background, I'm writing a full text search query that uses keyset pagination with ts_rank, so being able to order results based on a concrete, precise value of ts_rank is important. With keyset pagination, the client provides the last result from the current page as a parameter and asks for the next page. Currently, when my client passes back 0.1, the above > behavior causes erroneous results


texts: UPDATE waits for lock too long after SELECT in MySQL (Innodb)

texts:
I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen (int(1)). Seen column is indexed.

My pseudo code:

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

And when client requests 10+ pictures in parallel (for different Id) then some UPDATE have unexpectedly hung for 1-10 seconds. I see Innodb_row_lock_waits is increased every time in this case.

Without UPDATE my code works very fast all time.

I've tried use transaction, UPDATE DELAYED (I'm not sure that I tried it correctly).

Are there some best practices for selecting and updating same rows? What additional information should I provide to clarify the question?

UPDATE 1 Server Configuration

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41

UPDATE 2

I've changed the SQL code to this but situation did't change. Also I removed INDEX for 'seen' column without any effects to this code.

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;

In mysql-slow.log I see next entries:

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;

UPDATE 3

Amount of rows in pics table are about 70.

SHOW CREATE TABLE pics;

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8

SELECT COUNT(1) RowsReturned FROM pics WHERE id=315; (It took about 0.000035s)

RowsReturned 1


texts: How to index for a query with independent range conditions?

texts:
What is the best way to index a table that I query using independent range conditions?
E.g. WHERE X < ? and Y > ?


texts: How do I retrieve columns inserted in subquery 1, and return them with columns inserted in subquery 2?

texts:
Stack:

React Native
Node 10.x
pg-promise 8.x
PostgreSQL 10.x

A location row is upserted before creating a user row:

const insertUserSql = `
  with l as (
  insert into locations (
    data,
    placeid,
    user_count
  ) values (
    $1,
    $2,
    1
  )
  on conflict
  on constraint placeid
  do update set
    user_count = locations.user_count + 1
  returning
    placeid, data, uuid
)
insert into users as u (
  password,
  email,
  first_name,
  last_name,
  location_uuid
) VALUES (
  crypt(
    $3,
    gen_salt('bf')
  ),
  $4,
  $5,
  $6,
  (select uuid from l)
)
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid
`;

In the insert user subquery I want to return the location table columns
placeid, data

However, if I append:

...
RETURNING
  u.uuid,
  u.email,
  u.first_name,
  u.last_name,
  u.created,
  u.active,
  u.last_login,
  u.location_uuid,
  l.placeid,       <------ this
  l.data           <------ this

I receive the following error:

 name:  error
 message:  missing FROM-clause entry for table "l"
 code:  42P01
 stack error: missing FROM-clause entry for table "l"
    at Connection.parseE (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:553:11)
    at Connection.parseMessage (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:378:19)
    at Socket.<anonymous> (/home/imsov/git/d4d/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:182:13)
    at addChunk (_stream_readable.js:283:12)
    at readableAddChunk (_stream_readable.js:264:11)
    at Socket.Readable.push (_stream_readable.js:219:10)
    at TCP.onread (net.js:639:20)

I don't want to make two "trips" to the DB (which would be effective but inefficient).


texts:
I am trying to update latitude/longitude where missing using a merge statement. It errors: ORA-30926: unable to get a stable set of rows in the source tables. I thought that meant that the inner query matched more than one record in the outer table. I do not see how this query could produce that problem:

merge into people a using (
select address, city, state, zip,
max(latitude) latitude,
min(longitude) longitude
from people where latitude>0 and longitude<0
and address is not null
and city is not null
and state is not null
and zip is not null
group by address, city, state, zip
) b on (a.address=b.address and a.city=b.city and a.state=b.state and a.zip=b.zip)
when matched then update
set a.latitude=b.latitude, a.longitude=b.longitude

As you can see, I've ensured that none of the keys I'm matching on are allowed to be null. I'm grouping by them to ensure I get one and only one row for each unique set of values. So, I don't see how I could get more than one match on the join.


texts:
I used sp_detach_db to detach SQL Server 2005 databases. This works properly in Windows XP and Windows 7.

However, the same fails in Windows Server 2008.
The error message states:

  The process cannot access the file SimpleProject_device.ldf as it is being used by another process

But, I am able to delete both .mdf and .ldf files manually inspite of getting this error. I guess, if the database was still attached, delete operation should not have been allowed by the OS.

Edit:
The problem was that the Connection object had been disposed before detach. (God knows how).
Therefore, I have added a condition to ensure that the connection exists before trying to detach the database.

Currently, I am getting another error.

An error occurred during database detach:  "A transport-level error has
occurred when sending the request to the server. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the
pipe.)",30791590,8688858,5/22/2012,9:37:05 PM

Does anyone know why I'm getting this error?


texts: How to fix mongodb log when the log stops capturing events?

texts:
I see mongodb.log file hung at a certain date and stopped logging events since then. The type of deployment I'm referring here is a sharded cluster deployed through Automation.

We could clear the log files and bounce the cluster so the mongo Automation generates new mongodb.log files but I'm not interested in doing so. I'm trying to find out what could have caused the logs to go unresponsive. Any thoughts ?


texts: VMWare VMotion causing performance to increase?

texts:
We've been experiencing strange issues for awhile now with our virtual environment running SQL Servers.

We randomly get calls from users about very poor performance on the SQL boxes. Sure enough when I look I see the CPUs are pegged at 100%. I perform a VMotion to another host and as soon as it finishes moving over to another host performance immediately returns to normal.

I've been working with the VMWare admins and they have assured me that VMotion would not affect anything on the SQL Servers. It's almost as if the move to another host is causing an execution plan change or the like. I don't understand, however, why out of nowhere CPU usage jumps through the roof unless it's a bad query plan recompile due to parameter sniffing, but I would think that a VMotion would not fix that since it's supposed to be transparent.

The VM farm is comprised of 19 Dell servers (sorry I don't know the exact model) with 2 physical sockets and 12 cores on each socket.

Has anyone else observed this behavior before? I'm wondering if it's something to do with capacity as there are some large VMs for the hosts to handle it seems (there are 14 80GB, 12 core VMs floating around). Even with those VMs on the farm I can see in the Vsphere console that the hosts aren't being over-utilized (memory does creep up to the 80% mark a lot of the time, but no ballooning).

Also, this occurs on all different versions of SQL (2008, 2008R2, 2012, and 2014).

Thanks a lot for any insight!


texts: DB mail stopped working on Clustered instance

texts:
Database mail stopped working after enabling 'Hide instance' and 'Disable remote connection' on SQL 2008 R2 two node clustered instance however DB mail is working fine on SQL 2016 two node clustered instance after applying same SQL hardening.

I am not really sure if SQL hardening is causing this issue

I found following error in event log:

  Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
  Message: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)


texts: Error with my trigger. What should I do?

texts:
This trigger fails when it's called:

create or replace TRIGGER UNIQUE_PHYSICIAN_TRG
    BEFORE INSERT ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    BEGIN
      <<COLUMN_SEQUENCES>>
      BEGIN
        IF INSERTING AND :NEW.UNIQUE_ID IS NULL THEN
          SELECT UNIQUE_PHYSICIAN_SEQ.NEXTVAL INTO :NEW.UNIQUE_ID FROM SYS.DUAL;
        END IF;
        IF INSERTING AND :NEW.FSA IS NULL THEN
          SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;
    END IF;
      END COLUMN_SEQUENCES;
    END;

The line:

  SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;

seems to be the root of the problem. Is something wrong syntactically?


texts: postgresql: hindi encode and decode error

texts:
select encode('नमस्त', 'base64');

        encode
──────────────────────
 4KSo4KSu4KS44KWN4KSk

select decode('4KSo4KSu4KS44KWN4KSk','base64');

              decode
──────────────────────────────────
 \xe0a4a8e0a4aee0a4b8e0a58de0a4a4

Why does not this return the Hindi text?

collate: en_US.UTF-8
Ctype: en_US.UTF-8


texts: What will be inside the last differential backup file?

texts:
I am new in DBA and while learning different types of backup, one question came into my mind. So the question is, what will be inside the last differential backup, just the sum of the previous differential backups or the sum of previous backups plus t-log backups? Let's assume I have the following backups:

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
diff_backup_2018_05_09_006000.bak
tran_backup_2018_05_09_006500.trn
tran_backup_2018_05_09_007000.trn
tran_backup_2018_05_09_007500.trn
diff_backup_2018_05_09_008000.bak


texts: ALTER DATABASE script preventing restore of MySQL database in another server

texts:
While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines.

/*!50003 DROP FUNCTION IF EXISTS `f_my_function` */;
ALTER DATABASE `my_database` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;

The database character set is set to utf8.
Presence of this line is preventing the restore of the database in another system.

What is the fault?


texts: InnoDB storage for multiple BIT(M) columns?

texts:
Let's say if I have a table with four BIT(1) columns. Will InnoDB use 4 bytes to store these columns, or will it be smart and use 1 byte?
(Referring to MySQL 8, if it matters.)


texts: Investigating high SQL waits, especically LCK_M_U

texts:
Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly.  To clarify it is not overall performance but rather fairly frequent time outs (from the client application).  Previously I had looked to the memory as a cause but this has now been resolved and we are still getting the same behaviour.

Looking at the graphs from Management Data Warehouse I can see that LCK_M_U/X/IX are causing the majority of our waits around the time a user experiences a timeout. Everything I am reading states I need to look at the queries and processes running but I have yet to find anything aimed at a level I can understand.  The locks, as you can see in the picture below, seem to spike which coincides with the error on the users side.  Is there a clever DMV or some such that I can address to try and work out what query is being run that is creating the lock?  Is it a case of trawling through a trace to find the details?  Any guidance greatly appreciated and apologies if the information is not clear.


texts: will alwaysOn deal with certificates? do I have to do anything?

texts:
in a database that is part of an availability group (alwayson), in the current primary server I create a certificate and sign some stored procedures using it.

all is working fine so far.

use mydatabase
go

-- check if certificate exist, create otherwise
IF NOT EXISTS(SELECT 1 FROM SYS.CERTIFICATES WHERE NAME = 'dyntsqlcert')
BEGIN
    CREATE CERTIFICATE dyntsqlcert
    FROM FILE = 'C:\Certificates\dyntsqlcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\Certificates\dyntsqlKey.pvk',
    ENCRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa',
    DECRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa');
 END

--check if a user for the certificate exist, create otherwise
IF NOT EXISTS(SELECT * FROM SYS.database_principals where name ='dyntsql')
BEGIN
    CREATE USER [dyntsql] FOR CERTIFICATE [dyntsqlcert];
END

-- Granting permissions to the tables
        GRANT SELECT ON [app].[applicant] TO [dyntsql];
        GRANT SELECT ON [usr].[user] TO [dyntsql];
        GRANT SELECT ON [usr].[userCountry] TO [dyntsql];
        GRANT SELECT ON [app].[country] TO [dyntsql];
        GRANT SELECT ON [app].[application] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocument] TO [dyntsql];
        GRANT SELECT ON [upl].[document] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocumentImages] TO [dyntsql];
        GRANT SELECT ON [upl].[ref_documentType] TO [dyntsql];
        GRANT SELECT ON [app].[program] TO [dyntsql];
        GRANT SELECT ON [app].[ref_applicationStatus] TO [dyntsql];
        GRANT SELECT ON [app].[ref_CPIStatus] TO [dyntsql];
        GRANT SELECT ON [app].[applicationItemGroupChange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        --GRANT SELECT ON [APIA_Repl_Sub].[repl].[flightChangesHistory] TO [dyntsql];
        GRANT SELECT ON [app].[applicationstatechange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPlacementInfo] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPreDepartureQuestions] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        GRANT SELECT ON [app].[ref_visaStatus] TO [dyntsql];
        GRANT SELECT ON [agy].[agency] TO [dyntsql];

--Adding Signature to SPS'
IF NOT EXISTS
(
    SELECT 1
    FROM sys.crypt_properties cp
    JOIN sys.objects o ON cp.major_id = o.object_id
    LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint AND
    cp.crypt_type IN ('SPVC', 'CPVC')
    LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint AND
    cp.crypt_type IN ('SPVA', 'CPVA')
    WHERE o.name = 'usp_sel_workqueue_applicationsWithNewMedia'
)
BEGIN
    ADD SIGNATURE TO [app].usp_sel_workqueue_applicationsWithNewMedia
    BY  CERTIFICATE [dyntsqlcert] WITH PASSWORD = 'oH8F*%G9pnyqfa';
END

question is,
in the event of a failover, when this current server becomes the secondary, alongside its c:\ drive, would the certificate still be valid?

I should be testing this instead of asking but I don't have a good testing environment in hand.


texts: MySQL: Get number of rows of table with specific name for all databases

texts:
I am trying to build a mysql query (I want to use it in phpMyAdmin) to get the number of rows of all tables where the table name contains the word "user" or "member". But instead of questioning a single db, I want to run this on all dbs.
As a result, i want to have a table with the columns: DB name, Table Name, Numer of Rows.

Any tips to achieve that would be much appreciated!


texts:
I just finished re-building my (5.7) slave from a hot backup. When I issue
 systemctl start mysqld

it just "sat" there for over 20 minutes. It usually takes less than 10 seconds for the service to start. When I look at my server status, I see two mysql processes
mysql     1938     1  0 15:25 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      4024  3122  0 15:37 pts/0    00:00:00 grep --color=auto mysqld

I stopped and restarted my service and there are two different PIDs showing up again
 [root@bi-db002-prd ~]# systemctl status mysqld -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2020-08-17 15:46:45 UTC; 45s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7066 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  Control: 7088 (mysqld)
   CGroup: /system.slice/mysqld.service
           ├─7088 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 17 15:46:45 bi-db002-prd systemd[1]: Starting MySQL Server...

I have never seen that. Is there something incorrect with my configuration?


texts: Is there a concept of an "include file" in SQL Server?

texts: I have a set of scripts that need to be run in a certain order. I would like to create a "master file" that lists all of the other files and their correct order. Basically like an include file from C++ or ASP/VBScript.


texts: Strategies/Resources for implementing data lineage in an ETL process?

texts:
I'm struggling to articulate my need here.

I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack.

I'd like to be able to provide our finance team and auditors with the ability to trace the final results back through to the source systems, ideally with metadata like which cost calculations were used.

The Kimball Group and the Data Warehouse Toolkit book all hint at lineage (Subsystem 29!), but I am finding very few resources that help guide an actual implementation. There are a plethora of sites indicating that it is important and should be considered with data governance, and a few sites that are purely academic and go into the science of it but don't really provide practical guidance.

I'm likely not following best practices with this question, but I don't know how else to phrase it. As I said earlier, we are full Microsoft Stack, but I'm open to any resources even if for other tools.

Any and all help is appreciated. Thank you.


texts:
I am using SentryOne, and have what I believe to be a deadlocking process.  It shows it with a database id of 32767 and an object id of -710746692. I looked in sys.databases and sys.objects and didn't find anything for these. I did a search for the Database ID and believe it to be the hidden Resource Database that sql server has.  However, a google search didn't return anything meaningful for the object id.

What is Object ID -710746692?


texts: SQLite database replication on the same server

texts:
We have an application written in C and we are looking for a way to do transparent replication of SQLite database on one server (but on different disks). I read lots of guides about replication across multiple servers and most of what I've found is about RDBMSs such as MySQL database.

This post is the only one I've found about replication specifically on SQLite, where some solutions were suggested. For example, someone suggested using Litereplica which seems to be exactly what I am looking for, but after spending about 2,3 days, it didn't work (or maybe I couldn't use it properly). Another solution was Rqlite, but that is not on the same server.

I am not a SQLite expert and I just started using it, and I really stuck on this. Is there any other solution to replicate the SQLite database on the same server? For example, what we need is e.g., what we write in the main db, will be written in the replicated db/dbs as well. Also, for read, if the main db did not work, it should read from the slave/backup db...

UPDATE_1

I used the ATTACH to copy all the data from the main db to the backup db. Here is what I've done in SQLite command prompt:

ATTACH '/root/litereplica/litereplica-master/sqlite3.6/fuel_transaction_1_backup.db' AS backup;  //This attached a new empty db to the main db

CREATE TABLE backup.fuel_transaction_1 AS SELECT * FROM main.fuel_transaction_1;

Now, Is there any way to sync this two databases? so that e.g., if I delete a row from a table in main db, it deletes the same row from a table in backup db?


texts: PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

texts:
Could I create a VIEW from the JSON data type but have it as a relational structure?

Example:

SELECT *
FROM json_test;

Results

[
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Swim",
        "address": "Church Street",
        "sex": "M"
    },
    {
        "name": "Roy",
        "Country": "USA",
        "hobby": "Cricket",
        "address": "Amsterdam",
        "sex": "F"
    },
    {
        "name": "Anam",
        "country": "Greece",
        "hobby": "Polo",
        "address": "MG Road",
        "sex": "M"
    }
]

Then to create the VIEW would be something like (not sure if/how to do this)

CREATE VIEW normalized AS
SELECT name, country, hobby, address, sex
FROM JSON data

Then I could query the view with something like this

SELECT *
FROM normalized

Result set

name  | country | hobby   | address       | sex
------+---------+---------+---------------+----
 Roy  | USA     | Swim    | Church Street | M
 Roy  | USA     | Cricket | Amsterdam     | F
 Anam | Greece  | Polo    | MG Road       | M
(3 rows)


texts: How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?

texts:
I have a table with below structure and data :
create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

Sample data:
item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

This is my sample procedure with in which I'm trying to use bind variables .
create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

The problem is that when all Input parameters have a values , the procedure works properly , without any error , but when only one or two parameters have value , I receive this error : ORA-01006: bind variable does not exist,. How can I solve this problem? Some parameters might have value and some may not .
Thanks in advance


texts: Is there performance loss in out of sequence inserted rows (MySQL InnoDB)

texts:
I am trying to migrate from a MySQL AWS RDS instance with a huge SSD and too much excess space down to a small one, and data migration is the only method. There are four tables in the range of 330GB-450GB and executing mysqldump, in a single thread, while piped directly to the target RDS instance is estimated to take about 24 hours by pv (copying at 5 mbps).
I wrote a bash script that calls multiple mysqldump using ' & ' at the end and a calculated --where parameter, to simulate multithreading. This works and currently takes less than an hour with 28 threads.
However, I am concerned about any potential loss of performance while querying in the future, since I'll not be inserting in the sequence of the auto_increment id columns.
Can someone confirm whether this would be the case or whether I am being paranoid for no reasons.
What solution did you use for a single table that is in the 100s of GBs? Due to a particular reason, I want to avoid using AWS DMS and definitely don't want to use tools that haven't been maintained in a while.


texts:
The following URL varchar(2000) row holds URL values in a 3rd party database that I'm accessing directly, i.e. going around the HTTP API.

MySQL [database]> desc TABLE;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| URL                | varchar(2000) | NO   |     | NULL    |       |

There can be a variable amount of slashes in the row's values.

Example:

MySQL [database]> select URL from TABLE where URL LIKE 'https://%//%';

returns a single row:

https://foo.bar.com//...

My server(API) issues a SQL query that plans to enforce the uniqueness of the URL's $DOMAIN/$FIRST_PATH.

To illustrate since my terms are not technical:

SELECT URL FROM TABLE WHERE URL LIKE 'https://foo.bar.com/bippy/%'

So, if this database had a single row:

https://foo.bar.com/bippy/v1

then the query should find a "duplicate" in:

https://foo.bar.com/bippy/version1

since https://foo.bar.com/bippy/version1 starts with https://foo.bar.com/bippy, an already existing URL's prefix.

However, I'm concerned that this approach is fragile - especially if the input URL is https://foo.bar.com///bippy` - that wouldn't be caught by the query.

I'm considering calling java.net.URI#normalize to remove duplicate /'s in the URI from my API. However, I'm not sure that it's robust.

import java.net.URI
new URI("http://foo.bar.com//bippy///").normalize

  java.net.URI = http://foo.bar.com/bippy/

What's a sound way to find URL "duplicates", i.e. $DOMAIN/$FIRST_PATH approach in my database table?


texts: Assign a unique count to each row in the resulting table of a select statement

texts:
Let's assume we have a simple database that has the following schema: Article(Id, Body, AuthorId) and Author(Id, Name). Suppose that we want to develop a page where each author will be able to view his own posts only. There is only one requirement:

When the author wants to see his posts, the SQL query should allow him to see a counter for each item, starting at 1.

Example
Here's a simple set up:
(1,"Hi",1), (2,"there",1), (3,"test",2), (4,"foo",1).

By executing some sort of SQL statement: `select count, body where authorid=1;
we want to get:
(1,"Hi",1), (2,"there",1), (3,"foo",1).

Question
As you can see, the suggested statement wont work because count does not make sense yet. My question is, is there any way to assign a local count for each item  to produce the result above?


texts: SQLCLR TVF that calls web service is getting error 401: Unauthorized

texts:
I am calling a web service from a SQLCLR TVF in SQL Server 2008 R2.

On the IIS server this service has open permissions. When tested using GET or POST requests on a browser the web service works as expected.  However, when the function calls the service, it returns:

  The request failed with HTTP status 401: Unauthorized.

In the typical anecdotal way, when I run the TVF on my machine to access the service on my machine, it works.  (proof that code is correct)

This error occurs when using a SQL Server on the network accessing another ISS server on the network.  Both servers are in the same domain.

How to determine the missing permissions that prevent this TVF from working?


texts: Foreign key as part of composite primary key or not?

texts:
I'm designing a relational database. This is what I'm trying to design:

I will have production orders and products.

A production order will have one product, but a product could be on zero or more production orders (In fact, there will be always at least one production order for each product. It has no sense to have a product that it's not going to be produce on database).

In other words, with a production I will produce a product, but I will have a lot of production orders to produce the same product.

We are going to distinguish each production order by its name (alphanumeric) and it must be unique.

I have two questions:

Is the production order's name its primary key? I think I can use a integer as primary key.
Does product's key must be part of production order's primary key? I don't think so but I'm not sure.

By the way, I'm going to use SQL Server.


texts: Extract start and end per group of rows, where only the end can be identified

texts:
I have a table that looks like this.

id  date_from           date_to             description           vehicle
1   2015-10-21 08:00    2015-10-21 10:00    GARAGE TO CLIENT 1    1
2   2015-10-21 12:00    2015-10-21 13:00    CLIENT 2 TO GARAGE    1
3   2015-10-21 14:00    2015-10-21 15:00    RET GARAGE            1
4   2015-10-21 18:00    2015-10-21 19:00    GARAGE TO CLIENT 1    1
5   2015-10-21 20:00    2015-10-21 21:00    CLIENT 2 TO GARAGE    1
6   2015-10-21 21:00    2015-10-21 22:00    RET GARAGE            1

I need to get the total time that the vehicle is in use.
He stops being in use on first line and stops on the first description that starts with "RET".

A table like that would return

vehicle   starts              stops
1         2015-10-21 08:00    2015-10-21 15:00
1         2015-10-21 18:00    2015-10-21 22:00


texts: Having Sharepoint on a different Collation than the system Databases?

texts:
After installing Sharepoint I realized that the collation of all Sharepoint databases is Latin1_General_CI_AS_KS_WS while my server collation is Latin1_General_CI_AS. From official documentation I see that all CI collations are supported. Anyway, would it be helpful for me to rebuild the system databases into the same collation to avoid conversion problems in temp db?

Quote

  We support any CI collation for the SQL instance (for master, tempdb
  databases). However we recommend using Latin1_General_CI_AS_KS_WS as
  the instance default collation (master, tempdb databases).

I don't find the best practice here, please help me.


texts: How to use GROUP BY and result with highest id value

texts:
I used the following query to get the result

SELECT  `subjects`.*,`staffs`.`name`
    FROM  `subjects`
    LEFT JOIN  `staffs`  ON `staffs`.`uid` = `subjects`.`teacher`
    LEFT JOIN  `subject_student_selective`
       ON  `subject_student_selective`.`course` = `subjects`.`classid`
      AND  `subject_student_selective`.`subject` = `subjects`.`subject`
      AND  `subject_student_selective`.`student` = '12947'
    WHERE  `subjects`.`classid` = '26'
      AND  `subjects`.`active` = '1'
      AND  (`subjects`.`type`='normal'
              OR  `subject_student_selective`.`id`>0
           )
    GROUP BY  `subjects`.`subject`
    ORDER BY  `subjects`.`sort_order`

This gave the result but, I need to get the result with highest ID'ed Group By subject.


texts: Recovering a dropped table in PostgreSQL

texts:
I have :

CREATE TABLE ketoan_vn.Customers
(
  CustomerId character varying(10) NOT NULL,
  CustomerName character varying(150),
  CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
);

I inserted 2 rows into table ketoan_vn.Customers:

INSERT INTO ketoan_vn.customers(
            customerid, customername)
    VALUES ('C0001','Marry'),
    ('C0002','Tom');

I then dropped ketoan_vn.Customers :

drop table ketoan_vn.Customers

Now I want to recover the data of the dropped table.  Is this possible with PostgreSQL 9.1?


texts:
We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there were the .frm files. I searched the entire HDD for the .MYD files but the only ones are from the old database. It is on a CentOS 6.6 system running MySQL 5.5.

Where would those files be located? At first I was worried that they might just be held in cache, and that if the server lost power all would be lost, but not so sure now.

Wow, awesome commands Rolando, here are the results which looks like the majority is not InnoDB, but I would have assumed it still would need to store the data in the same place, even if it was in a different format. Our old DB is 1.7GB when I use the du -h in the /var/lib/mysql and the directory I care about is less than 1MB

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MEMORY         |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB         |             0.151 GB |             0.059 GB |             0.210 GB |
| MyISAM         |             1.381 GB |             0.227 GB |             1.608 GB |
| Total          |             1.532 GB |             0.286 GB |             1.817 GB |
+----------------+----------------------+----------------------+----------------------+

+-----------------------+------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE         |
+-----------------------+------------------------+
| INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend |
| DATADIR               | /var/lib/mysql/        |
| INNODB_FILE_PER_TABLE | OFF                    |
+-----------------------+------------------------+


texts: Move column to another table by reducing?

texts:
I'm not very experienced with database so sorry for lacking correct terminology.

I have a structure similar to

Employee
-Id
-Location
-Name
-StoreId

Store
-Id
-Name

And I want to simplify/refactor so that the store has the location rather than the employee. And all employees should all have the same location if they have the same storeId.

How can I move location to Store while dropping location on Employee and setting it to the correct value?
If somehow the locations are mismatching, can I use the one with the most hits?

Something like

ALTER TABLE store
    add column location ???

alter table employee
    drop column location


texts: Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB

texts:
I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB.

What can be the reason?

You can see screenshot here

Here is Estimated execution plan of the view.Execution Plan
and here is actual one Actual Eplan

I am using SQL Server 2008 R2 E2. And I am using this query to know memory utilization and other things.

    select
                db_name(sp.dbid) [DB Name],sp.spid,sp.blocked,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
                ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
                ,es.status,es.cpu_time,qmg.requested_memory_kb,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
                ,es.reads,es.writes,es.logical_reads

    from
                    sys.dm_exec_requests er
    inner join      master.dbo.sysprocesses sp
    on              er.session_id=sp.spid
    inner join      sys.dm_exec_connections ec
    on              er.session_id=ec.session_id
    inner join      sys.dm_exec_sessions es
    on              ec.session_id=es.session_id
    inner join      sys.dm_exec_query_memory_grants qmg
    on er.session_id=qmg.session_id
    cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
    cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp
    Order by cpu_time desc

"Select * from View" consumes 4GB memory but when I execute SQL statement of view. It takes 70 MB only. I am measuring requested memory and granted memory. Using above script.


texts:
I have three select statements which have the bulk of their columns which are the same except a few. So here is an example

select
*orgid='0'
,'P' as OrgType*
, Lang
,*cast(Null as varchar) as BoardName
,cast(Null as varchar) as SchoolName*

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM *TABLEA*
WHERE *[Include_All] = 1*
Group By *[Lang]*

select
orgid=boardname
,'B' as OrgType
, Lang
, BoardName
,cast(Null as varchar) as SchoolName

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM TABLEB
WHERE [Include_Board] = 1
Group By [Lang], BoardName

select
orgid=SchoolName
,'S' as OrgType
, Lang
, BoardName
,SchoolName

,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3

FROM TABLEC
WHERE [Include_School] = 1
Group By [Lang], BoardName

So as you can see, the calculated columns are the same in each "grouping". The problem is that I have about 70 calculated fields (which are the same) in all three select statements. This makes reading the script horrible as i have to endlessly scroll.

The main changes are the first couple of columns in the select statement and then the way it groups by at the end.

Right now my current solution is to set strings and execute them. IE, I'll have

header1 = "SELECT FROM all..."
header2 = "SELECT FROM board.."
header3 = "SELECT FROM school.."
footer1 = "GROUP BY lang"
footer2 = "GROUP BY lang, board"
footer3 = "GROUP BY lang, school"
Common = CalculatedFields

EXECUTE (header1 + common + footer1)
EXECUTE (header2 + common + footer2)

but this just makes my script all red because its one large string. Not only that I have to break it up into different strings because I have a lot of text.

Any ideas on how to best do this while maintaining readability and clarity?


texts:
I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery.

Right now, I'm taking a full backup of the databases and logs every day at 5am. Some of the log files have ballooned up to 300gb and even after taking a backup they don't reduce in size. I can get them to reduce in size by running something similar to:

BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log2.trn';
DBCC ShrinkFile([db1_log], 0);

BACKUP LOG db1 TO DISK = '\\server\share\db1_log3.trn';
DBCC ShrinkFile([db1_log], 0);

When I check the LSNs of the backup files I see something like:

RESTORE headeronly FROM DISK = N'\\server\share\db1_log1.trn'
FirstLSN:  15781000014686200001
SecondLSN: 15802000000665000001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log2.trn'
FirstLSN:  15802000000665000001
SecondLSN: 15805000000004100001

RESTORE headeronly FROM DISK = N'\\server\share\db1_log3.trn'
FirstLSN:  15805000000004100001
SecondLSN: 15808000000004200001

I don't believe I'm breaking my log chain by shrinking the log files. Reading up on this, I do believe I'm hurting my performance because those shrunk log files have to re-grow themselves.

Questions:

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?
At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?
My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?


texts:
I have a table with 5 columns as A, B, C, D, and E. The functional dependencies are:

AB -> C,D,E
BCD -> A
BCE -> A,D
BD -> E

I think AB is the candidate key. I can't find any other candidates. Since I have a two-column key, BCE cannot be a candidate. Am I Right? What I am trying to do is to normalize this table to BCNF but I couldn't figure this table out. It looks like there is no repetitive data and also there is no transitive dependencies. This table looks like BCNF but I think I am missing something. I find it difficult to understand BCNF so can you please help?

Thanks


texts:
I have reach a support line that gave me a T-SQL Script for one DB that I now need to apply many more times.

The original script goes like:

IF EXISTS  Procedure
THEN DROP
GO

CREATE PROCEDURE [dbo].[Procedure]
...
END
GO

exec Procedure 'With Arguments'

What I'm trying to do goes like:

DECLARE @db_name VARCHAR(50) -- database name
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
where create_date > DATETIMEFROMPARTS(2017,12,27,0,0,0,0)
AND create_date < DATETIMEFROMPARTS(2017,12,30,0,0,0,0)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE('USE ' + @db_name)
    exec Procedure 'With Arguments'
    FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

But I' m not getting the expected results. I believe that is because I'm not creating the procedure inside each DB.
What is the clean way to do the "If Exists" and "Create Procedure" repeatedly?

I also used :

USE master
CREATE PROCEDURE sp_procedure AS ...

and

USE aDB
exec sp_procedure 'With Arguments'

But it says sp_procedure does not exists...

I'm working with windows SQL server 2014

Help appreciated.

EDIT:
The end code after reading all the answers and marking as System was:

...
BEGIN
    SET @CMD = 'exec ' + @db_name + '.dbo.sp_procedure ''With Arguments'';';
    exec sp_executesql @CMD;
    ...


texts: Blocking caused by Query Store. Unable to clear or disable

texts:
I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the SPID b/c it isn't a user process. According to SP_WHO2, the command is "Query Store ASYN". I've tried purging the data and disabling the query store via Script and through the UI. Nothing seems to work and it just spins and then starts to cause more blocking. Is anyone else having this issue? Can anyone please help me figure out how to disable the query store successfully? SP_WhoIsActive @show_System_SPIDS = 1 results below (query store results only)

UPDATE - This is now caused TempDB drive to fill up. Going to try a reboot in a few hours and see if that resolves the issue. Will keep you posted.

Thanks,
Nate


texts: Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?

texts:
I'm running a simple DB on SQL Server 2012 Express.

Just today, when I backup the database, the .bak file size doubles to what it was from the previous backup just minutes before. I have done several backups today (through SQL Server Management Studio -> backup type: full), and with each one the .bak file keeps on doubling.

In SQL Server Mgmt Studio, when I right click on my DB -> 'Reports' -> 'Backup and Restore events' -> expand 'Successful Backup Operations':

The report here shows that the latest backup size was recorded as 55MB, but when I go to the actual .bak file it is 260MB. Each other backup today was recorded also as 55MB size, while their corresponding .bak files are multiple times larger (and growing with each backup operation).

What could be going wrong? I haven't made any changes to the database since this started happening.


texts: SQL query not displaying a row when one of the variable gets 0 values

texts:
I have this query that is set to return a % but when one of the sender_marks in the list values gets 0%, the mark is not displayed with 0 but disappears.

SELECT  p.SENDER_MARK,ROUND (Removed/TotalTrains*100,2) TrainRemovals
FROM
(SELECT COUNT(DISTINCT TRAIN_ID) AS Removed,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND REMOVED_FROM_DISPATCH_IND = 'Y'
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK ) p,
(SELECT COUNT(DISTINCT TRAIN_ID) AS TotalTrains,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK )b where  p.SENDER_MARK=b.SENDER_MARK


texts: How to use LOW_PRIORITY for heavy queries?

texts:
I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources.

I do not want them to stop end users from basic tasks when performing such heavy UPDATE tasks.

Is LOW_PRIORITY for this purpose? or I should follow another strategy?

UPDATE LOW_PRIORITY ....

Note that my engine is innoDB.


texts: dbatools - Copy all jobs to the other node - selected jobs only

texts: Is it possible to copy all sql server agent jobs using dbatools from one node to another, except a particular job? or perhaps just copy a set of jobs?


texts: 32 bit vs 64 bit system - Informix Client SDK

texts: If I create an Informix SSIS package in my 32 bit system using 32 bit Client SDK and deployed it in 64 bit server. Will it create issue?


texts: How can I generate scripts for all functions in pgAdmin?

texts:
I am using pgAdmin version 1.10.5, and I've checked the properties and menu options but couldn't find anything on the subject.

For example, I just want to generate a script that includes the functions that I've created for my schema. If I right click on the database node and choose backup it has an option to create a script for the schema, but it creates all the objects. And I want only the functions.


texts: Can I copy installed mysql from ubuntu to centos7 directly without installation?

texts:
I'm trying to copy an installed MySQL from Ubuntu to Centos7 because I do not want an installation on Centos7 any more. This looks like it works but I'm not sure if there any potential issues which I do not know.
Has anyone tried this operation before?
The purpose to try this copying way is that I'm writing an one click installation sh script which including serveral softwares, such as JDK, MySQL, nginx, elasticsearch  and some Java, python code. Coping existing MySQL is obviously the easiest way to finish MySQL installation script, since I need to install MySQL and create accounts and DB's in traditional way .
The steps for my copying MySQL directly from Ubuntu to Centos7 are:

create user/group MySQL on Centos7
copying MySQL installed directory from ubuntu to the same directory on centos7, such as /usr/local/mysql
copying my.cnf file from Ubuntu to /etc on Centos7
copying mysql.server to /etc/init.d/mysql
grant permission to mysql.server and chown the owner of /usr/local/mysql  to mysql:mysql
start/stop MySQL service manually, it's looks fine

BTW, my Centos7 machine is new installed, which means I did not installed any other software on it, and my Centos7 can not reach internet so I could not install MySQL through apt, yum ways. I need to download installation package from website firstly, then I can install MySQL through rpm, or tar in the traditional way.
What I want to say is that no matter the apt, yum, rpm, or tar way,  it's complicated to write these steps in my one click installation script, so I choose copying directly, but I  have some worries about it since I do not know if there are some potential issues.


texts:
I have installed MySQL Cluster necessary packages and I am now using Auto installer to config the nodes properly. when I try to start the Cluster In Deploy Configuration part, all the nodes (1 management node, 4 data nodes) starts correctly except SQL node and I got below error.

Command `/usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf', running on 192.168.120.107 exited with 1:
2016-04-03 16:32:49 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-04-03 16:32:49 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11472 ..

you see all the nodes are started. I use the command it recommended with --explicit_defaults_for_timestamp option like below.

 /usr/local/mysql/bin/mysqld --defaults-file=/home/debian/MySQL_Cluster/49/my.cnf  --explicit_defaults_for_timestamp
2016-04-03 16:42:06 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.28-ndb-7.4.10-cluster-gpl) starting as process 11503 ...

you can see with --explicit_defaults_for_timestamp  option service start correctly but still when I use management node to see the status I see the cluster has a problem see management node :

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1    @192.168.120.111  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2    @192.168.120.117  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=3    @192.168.120.118  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=4    @192.168.120.76  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=50   @192.168.120.79  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

as you can see the SQL node still has not been started correctly :

[mysqld(API)]   1 node(s)
id=49 (not connected, accepting connect from 192.168.120.107)

I don't understand the problem.


texts: Matching two rows in a single table if two specific keys have different values

texts:
I have a table called settings in that there are two columns and data like following.

settings           value
-------            ------
depots             12
projectid          156
client             icici bank
clientvisits       2
DBname             icicibank
progress           true
displayvisits      2
visitsskipped      0

Here , the values of rows clientvisits and displayvisits should be same always.
I want a query which display the only these two fields along with values if their values are different. That means if clientvisits=2 and displayvisits=4 then it should throw an error (I mean it should display the output along with values).

I tried  by using self join  like this  but it is not working.

select * from settings TS,settings TS1
where TS.settings=TS1.settings and CONVERT(integer,ts.value)=1

select * from settings TS,settings TS1
where TS.settings=TS1.settings and ts.setting like 'visitstodisplay'=1

Also, I tried to do some thing else but I didn't get.
Please let me know how to do it.


texts: Moving a SQL Server DB from Windows to Linux fails

texts:
We are trying to move a SQL server DB from Windows to Linux. But we are facing few problems while migration.

We took a Backup of the DB server, from SSMS in Windows
We restored it in the linux running the following commands:

RESTORE FILELISTONLY FROM  DISK =N'/var/opt/mssql/data/backup_file_name.bak'
RESTORE DATABASE DB_NAME
    FROM  DISK = N'/var/opt/mssql/backup_file_name.bak' WITH  FILE = 1,
    MOVE N'DB_NAME' TO N'/var/opt/mssql/data/DB_NAME.mdf',
    MOVE N'DB_NAME_Log' TO N'/var/opt/mssql/data/DB_NAME.ldf',
    NOUNLOAD,  REPLACE,  STATS = 1
GO

Running this I get an error more or less lie this:

  file XX_FlatFline cannot be restored to PATH_IN_WINDOWS {.....mdf}.
  Use WITH MOVE to identify a valid location for the file.

I get error for both MDF and LDF files. The windows path shown above in the error is the location of the mdf and ldf files in the Windows machine.

Can someone help me understand what's going wrong here? Why does the Windows path still matter when I am restoring the dump into a new DB?

Warning: I am a novice in SQL Server.


texts: How to safely insert a jsonb into a table?

texts:
I wrote this prepared statement for inserting jsonb data into a table:

PREPARE posts_insert(jsonb) AS
  INSERT INTO posts(data) VALUES($1) RETURNING ID;

Though I'm concerned if this query has some kind of vulnerabilities. The json comes from the client side of my application, so it could basically contain anything. Is it dangerous to use jsonb (that comes from the user) within queries? What can I do to make it safe or more safe?


texts: SQL Server 2012 hangs at SqlRsConfigAction_install_ConfigNonRC_Cpu64

texts:
For some reason SQL Server 2012 keeps hanging at SqlRsConfigAction_install_ConfigNonRC_Cpu64

There isn't any Windows installer running apart from the SQL Server setup.

Update on Resolution
Install SQL2012 without selecting Reporting Services


texts:
While installing PostgreSQL I keep getting the message:

  "Problem running post-install step. Installation may not complete
  correctly  The database cluster initialisation failed."

This seems to prevent me from using PGadmin correctly.

In the install log I have found the following section which seems to indicate where things went wrong.

I haven't been able to find a solution to this problem.

Executing batch file 'rad7C33E.bat'...
    The program "postgres" was found by "C:/Program Files/PostgreSQL/11/bin/initdb.exe"
but was not the same version as initdb.
Check your installation.

Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo "C:\Program Files\PostgreSQL\11/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\JesseDanckaarts\AppData\Local\Temp/postgresql_installer_6f373c7c91" "C:\Program Files\PostgreSQL\11" "C:\Program Files\PostgreSQL\11\data" 5432 "Dutch,Netherlands" 0: Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
 The database cluster initialisation failed.
Executing icacls "C:\Users\JesseDanckaarts\AppData\Local\Temp/postgresql_installer_cb3c83cf49" /inheritance:r
Script exit code: 0

I have tried the following without luck so far:

https://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Common_installation_errors
https://webkul.com/blog/postgresql-windows-installation-problem-running-post-install-step-installation-may-not-complete-correctly/
https://www.2ndquadrant.com/en/blog/postgresql-cluster-initialisation-failures-no-error-windows/

and many more!

If anyone knows what could be the problem I'd be very grateful!


texts: Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name)

texts:
I have a question.

In my database I have an Allergen table that has two elements: Id and Name.

I want that my Allergen table has translations, so what is better ? Create an auxiliary translate table that name could be AllergenTranslate or create languangeId directly in the table Allergen and change its name for AllergenTranslate.

Example:

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
)

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [AllergenId] int,
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY AllergenTranslation table

CREATE TABLE [AllergenTranslation] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)

OR DIRECTLY Allergen Table without Translation table name

CREATE TABLE [Allergen] (
  [Id] int PRIMARY KEY IDENTITY(1, 1)
  [LanguageId] int,
  [Name] nvarchar(255),
)


texts: SQL Server Database Mail - Why can't I run an execute against xp_sysmail_activate?

texts:
SQL Server 2008 R2 Standard x64

I can't send e-mail from this SQL Server (out of MANY that are configured pretty much identically AFAIK). I have created the mail profile and account. However, any time I tried to send an e-mail with sp_send_dbmail (with my account - in sysadmin role) or Database Mail->"Send Test E-Mail", nothing happens. Nothing goes into sysmail_log. The SQL Server error below is generated:

 The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following:  'The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'

Details

msdb.dbo.sysmail_mailitems has a record for each mail
The database mail log is empty  (msdb.dbo.sysmail_log)
msdb.dbo.sysmail_sentitems is empty
I have run sysmail_stop_sp and sysmail_start_sp
"is_broker_enabled" is set to "1" in msdb

How is the execute permission denied when I am part of the sysadmin role? Any suggestions on what could be happening?


texts:
After the server die, I have "old" HD (it is external now) with sock file and data files in mysql directory. Where I should find information how to restore the data in fresh installed MySQL?

What will happens if I replace files and folders brutal way?

UPD:

Old files have a version mysql-5.1.62-osx10.6-x86_64
New installation have a version mysql-5.6.22-osx10.8-x86_64


texts: How? SQL Cluster with multiple active nodes (not failover)

texts:
I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN.  Instead of building a failover cluster (which I have done), I would instead like to setup several nodes that are all active simultaneously.  I want to spread out the CPU/RAM load across the nodes.

Can anyone point me towards a document on this?  I'm only finding info on HA/Failover clusters.


texts: can I get SQL Server to choose between a table and a view?

texts:
Our reports need to be live and quick - don't they all?  The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys.  Our tables are quite large but our daily INSERTs are relatively few.  ie a table may have over 35,000 rows but only 400 INSERTs a day.

The data structures off which our reports are written are views into the live application database (technically a replicated copy).

What would be swell is to, effectively, make copies of these views every night and save those copies in the reporting database.  Then have SQL Server magically determine if the table and/or the view is required to handle the reporting query.  So if the query is looking at last year, it uses the table - if it's the last hour, it uses today.

It is fairly easy for me to write a view that unions the table and the "today data" that is not in the table, thanks to the incrementing PKs.  But the query plan shows the view is always run, even when it's not required.

I hope that's enough detail to highlight what I am trying to accomplish.  Is this possible in SQL Server?  Any other creative ideas to handle this?

Thanks!!


texts:
I want to create a news app that works in the following way:

The site will contain articles, and each article with have multiple versions. Each version is a docx file. The article itself does not have any content apart from the versions. The collection will look like this:

{
   Id:123,
   Versions: [{id:321, docx:"blob"},{id:444, docx:"blob"}]
}

I've read that Mongo allows you to save files in a format called gridFs, but from my understanding it keeps a different collection for the files and does not allow you to keep the files as a part of a custom collection.

Can someone suggest what is the best way to store the data in the way I described?


texts: Adding new databases to existing maintenance backup plan

texts:
I am trying to automate our backup plans to add newly created backups automatically on SQL server 2008. Is there a way to do this?
Since it wont let me comment on this to clarify. I am trying to add new databases that we have created to an existing Management Maintenance Plan on SQL Server 2008. I am trying to make it a script that we can run that will automatically add the database when created. Is this possible?


texts: Best practice for creating tablespaces in Postgresql

texts:
PostgreSQL version : 11.2
OS : RHEL or Oracle Linux 7.6 (Yet to be decided)

I am at the design stage of setting up a production database. In production, the DB will be around 300GB to 400GB in size.

This is what I have in mind. Please let me know if this is a good idea for a PostgreSQL production deployment.

I will have the following file system mounted with the following sizes:

/db ----> 50 GB
/pgdata ---> 500 GB

I will initialize the database cluster in the custom location /db/postgres/pg11/data .
And right from the start I will start creating tablespaces like this:

CREATE TABLESPACE orders_tbs LOCATION '/pgdata/<db_name>/orders_tbs';

...and place business objects in these tablespaces like this:

CREATE TABLE orders (id int, order_item text) tablespace orders_tbs;

Thank You Arkhena, CL

In the above case, I thought of creating a separate filesystem for datafiles (/pgdata) and keeping the config files and logs in /db.
So, my idea was bad.

Since I am in RHEL/Oracle Linux , by default, my $PGDATA will be /var/lib/pgsql/11/data .
But, I prefer to have my $PGDATA in a custom location like /db/postgres/pg11/data

Since the datafiles reside in $PGDATA/base directory , how about creating a disk layout like below using LVM?

A 50GB filesystem for $PGDATA's top parent directory /db and a separate 500 GB filesystem for $PGDATA/base directory ?

[root@localhost ~]# df -Ph
Filesystem                      Size  Used Avail Use% Mounted on
<output snipped>
.
.
/dev/mapper/VolGroup1-LogVol02  50G    23M  49.9G   1% /db
/dev/mapper/VolGroup1-LogVol04  500G  2.7M  499.9G  1% /db/postgres/pg11/data/base

I need to check with our Linux Admin on how the above disk layout can be optimally created without causing any storage bottlenecks.


texts: Mysql Query Optimization [Match Against]

texts:
I have tried all the possible option to optimize the query, but no luck.
Query 1:

SELECT
  *,
  (
    3959 * acos (
      cos(
        radians(51.5073509)
      ) * cos(
        radians(ats_latitude)
      ) * cos(
        radians(ats_longitude) - radians(-0.12775829999998223)
      ) + sin(
        radians(51.5073509)
      ) * sin(
        radians(ats_latitude)
      )
    )
  ) AS `distance`,
  (
    SELECT
      `distance`
  ) < 75 AS `in_user_radius`
FROM
  table_name
WHERE
  `feed` = 'external'
  AND (
    MATCH (`keywords`) AGAINST (
      'word1 word2 word3*' IN BOOLEAN MODE
    )
    OR `title` LIKE '%word1 word2 word3%'
  )
  AND `business_area` RLIKE ('word1')
  AND 1
HAVING
  (
    `distance` < 95
    OR isnull(`distance`)
  )
  AND (
    `in_user_radius` = '1'
    OR isnull(`in_user_radius`)
  )

Query 2:

SELECT
    *,
    MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) AS `score1`,
    MATCH(`title`) AGAINST('"word2"' IN NATURAL LANGUAGE MODE) AS `score_title_exact`,
    MATCH(`title`) AGAINST('word2' IN NATURAL LANGUAGE MODE) AS `score_title_phrase`
FROM
    table_name
WHERE
    `jobfeed` = 'external' AND(
        MATCH(`keywords`) AGAINST('word1*' IN BOOLEAN MODE) OR `title` LIKE '%word1%'
    )
ORDER BY
    `nationwide`
DESC
    ,
    `score_title_exact`
DESC
    ,
    `score_title_phrase`
DESC
    ,
    `score1`
DESC
    ,
    `id`
DESC
LIMIT 48, 12

Below things I tried to optimize:
1) instead of select * I have specified the specific columns.

Observation: Due MATCH AGAINST condition query is taking too much time to execute.

please suggest some solution to optimize the above queries.


texts: Select one name per person where spelling variations exist

texts:
I have a source table that contains Employee IDs and names.  The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle initial is sometimes present).

I want to insert unique values, in alphabetical order, into a new table (see 'target table' for desired results).  I don't care which version of someone's name is inserted into my target table...what I want is one truly distinct value per person in my target table sorted alphabetically by last name.

Source Table:

Emp_ID  Name
123 Jones, John
123 Jones, John P
123 Jones, John P.
456 Lewis, Jerry
456 Lewis, Jerry L
456 Lewis, Jerry L.
789 Hewitt, Jennifer
789 Hewitt, Jennifer L

Target table (desired results):

Emp_ID  Name
789 Hewitt, Jennifer
123 Jones, John
456 Lewis, Jerry L


texts: Data File Size Stays Same Whereas Log File Size Is Increasing

texts:
I am using the following snippet to track the data and log file size increase after certain operations:

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

The problem is, after each write operation, log file size increases as expected but the data file size stays the same for 4 or five operations and then suddenly increases.

Why would this be?


texts:
I have this 2 queries, and I would like to join them

First query

SELECT proveedors.nombre as proveedor, COUNT(productos.id) as distintos
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
GROUP BY proveedors.id;

Second query

SELECT proveedors.nombre as proveedor, COUNT(productos.id) as instock
FROM proveedors
INNER JOIN productos ON proveedors.id = productos.proveedor_id
WHERE productos.stock > '0'
GROUP BY proveedors.id;


texts: SQL update multiple columns matching a pattern

texts:
I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g.
Those are timefields.

I would like to substract 1 hour from all the rows on those columns. Is there a way to do without running an UPDATE query separately for each column?


texts: PostgreSQL connection URI fails, but parameters work

texts:
Running postgreSQL 13.2 with ssl on.
I need to connect to a database from a third party application that requires a connection URI, but it is not working. So I tested connecting with psql using two different formats with the same credentials. One worked and one didn't.
The following command logs me into mydb as user myuser:
# psql -U myuser -d mydb -h 127.0.0.1 -p 5432 -W
Password:
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydb=>

However this command fails:
# psql postgresql://myuser:MYPASSWORD@127.0.0.1:5432/mydb?sslmode=require
psql: error: FATAL: password authentication failed for user "myuser"

I am using exactly the same credentials. I've verified it more than 10 times. Removing "sslmode=require" does not fix the problem.
My pg_hba.conf file contains:
host   mydb   myuser   127.0.0.1/32   password

I made it the first line in my pg_hba.conf file, so it can't be getting hung up on any other line.
What am I doing wrong?


texts:
I have a table like the one below:

+-------+-----------+-----------+-----+--------+-------------+
| grade | firstname | lastname  | age | gender | student_num |
+-------+-----------+-----------+-----+--------+-------------+
|     2 | Stephen   | Harper    |  12 | male   |           1 |
|     1 | Jennifer  | Solomon   |  10 | female |           1 |
|     4 | James     | Barney    |   9 | female |           1 |
|     3 | Collins   | Balmer    |   8 | female |           1 |
|     4 | Kehinde   | Adefemi   |  12 | male   |           2 |
|     2 | Benjamin  | Salem     |  14 | female |           2 |
|     3 | Praise    | Olawale   |   9 | male   |           2 |
|     1 | Janet     | Pelumi    |   7 | male   |           2 |
|     3 | Ire       | Adora     |  11 | female |           3 |
|     2 | Manny     | Grace     |  13 | male   |           3 |
|     2 | Esther    | Benson    |   7 | female |           4 |
|     3 | Stan      | Collimore |   6 | female |           4 |

Please help me with the SQL that will generate the resulting table below from the one above.

grade|s1_fname  |s1_age|s1_gender|s2_fname |s2_age|s2_gender|
  1  | Jennifer | 10   |female   | Janet   | 7    |  male   |
  2  | Stephen  | 12   |male     | Benjamin| 14   |  female |
  3  | Collins  | 8    |female   | Praise  | 9    |  male   |
  4  | Kehinde  | 12   |male     | Myedan  | 8    |  male   |

where s1 means student1. The 1 should be gotten from from student_num column

where s2 means student2. The 2 should be gotten from from student_num column
...

My database server is MySQL.

I Have tried the code below

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
          'SUM(CASE WHEN `grade` = ',
          `grade`,
          ' THEN firstname ELSE 0 END) AS `s',
          `student_num`, '_fname`'
        )
    ) INTO @sql
FROM students;
SET @sql = CONCAT('SELECT grade, ', @sql, ' FROM students GROUP BY grade');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


texts: Replication to subscriber with different name

texts:
Is it possible in PostgreSQL to set up the following replication scenarios?

Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name primary_product TO server_B (slave) database name primary_product_analytics
Replication between 2 servers which consist of several databases in Server_A, need to be transferred into single database with multiple schemas (custom schema name) on Server_B.

I have successfully setup replication using WAL but I would like guidance on implementing the above scenarios if possible.


texts: Getting Nested query result from Cosmos DB with property named "Value"

texts:
I have Cosmos Db collection, I am trying to query the "Key" "Value" pair from.
Working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Key = "MembershipTypeId"

And got result:

[
    {
        "ed": {
            "Key": "MembershipTypeId",
            "Value": "224"
        }
    }
]

I need to filter by "Key" and "Value", but "Value" appears to be a reserved keyword
Not working query:

SELECT ed
From c
JOIN ed IN c.ExtendedData
Where ed.Value = "224"

Got HTTP 400 with message: Syntax error, incorrect syntax near 'Value'


texts: How to undo every change made to a database in a controlled environment?

texts:
I'm doing system testing of a product, and several tests are severely changing the data of a demo database.

The data is quite complex to create queries which will undo the changes done by the tests; moreover, those tests are subject to change.

Using a backup/restore is not an option, since restoring the database takes 30 seconds, which is too long in a context of automated testing.

Using insert into select is complex too: again, tests are subject to change, and can affect tables they don't affect now; copying the whole database this way is not an option neither, for performance reasons.

Microsoft SQL Server has snapshots feature, but given that I don't have Enterprise version on localhost, I can't use it. I can't use transactions neither, since the system tests involve two applications with a complex interaction between them. Of course, they don't share the same connection.

What can be done to quickly undo the last changes of a database?

To make the problem easier,

The database is in a controlled environment:

It is on localhost, so I am the only one who can access it,
The only changes are from system tests. All the changes can (and are expected to) be lost.
Any recovery model can be chosen.

The preparation (for example some sort of backup) can take long time or even be a manual task, since it would be performed very rarely. It's only the speed of undoing the changes from this point which is important, since it's done after every test which affects the database.


texts:
I can run this command to make changes for 1 DB

USE [master]
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.


texts:
I had a database which i dropped using the below command.

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

Now when i refresh the connection in SSMS i get the error message Cannot open database "dbname" requested by login.The login failed.Login failed for user 'DOMAIN\Windowsusername'.Microsoft SQL Server,Error 4060

I remember that the owner for that database was the same login "DOMAIN\Windowsusername" .
So i think now when i connect using the same user login ,it is trying to connect to the dropped database.
Since the database is dropped i cannot change the owner of the db.
How can i get rid of this error.

Any help will be appreciated.


texts: Remove string after first and second specific character in a string

texts:
I have some text in a field like below;

Confirmed Diagnosis
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes:

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified

I would like to extract any text after Confirmed Diagnosis ================ into a new field, and any text after Suspected Diagnosis ================ into another field.

Thank you all.


texts: SQL Server replication max server name length

texts:
Question
Does SQL Server have a max server name limitation for SQL Server Replication with publishers or subscribers?
Background:
My coworker is worried about going above 15 characters for  a server name. We are currently looking to go to the cloud where it would be really helpful to break the 15 character limit, but my coworker is hesitant about doing so, stating that at least in times past SQL Server replication didn't handle it very well. I've looked as much as I can online and see no current limitations, but unfortunately that doesn't mean there isn't one.
What I know:
I know that you can create a host with a longer name then 15 characters. I also know that in our case, Active Directory truncates the name and appends random characters to the end, but that it stores the actual value as part of the DNS property.
I know that SQL Server @@servername can be longer then 15 characters.
Details:
We have an environment where we are migrating to SQL Server 2019 Enterprise from SQL Server 2014 Enterprise, and so have a mix of both. We replicate from SQL Server 2019 to both SQL Server 2019 to SQL Server 2014 Enterprise. If it matters we are running Windows 2012 R2 for SQL Server 2014 and Windows 2019 Standard for the SQL Server 2019 hosts. We have a central distributor that is running SQL Server 2019 Enterprise.
Thanks in advance for any help that you can give.


texts: Why do I get an incorrect error "ORA-01775: looping chain of synonyms", when the base table does not existing?

texts:
I use Informatica for managing some ETL process that load data into an Oracle 9i Data-warehouse.

Today I got the below error in the Informatica session logs:

Message: Database driver error...
CMN_1022 [DELETE FROM SOME_TABLE
WHERE PERIOD_NAME = 'OCT-12'

ORA-01775: looping chain of synonyms

Database driver error...
Function Name : executeDirect
SQL Stmt : DELETE FROM SOME_TABLE
WHERE PERIOD_NAME = 'OCT-12'
Oracle Fatal Error
Database driver error...
Function Name : ExecuteDirect

Oracle Fatal Error
]

Now it was a typographical error, the table name was misspelt. The Oracle error obviously sent us in the wrong direction... So just wanted to understand why this error was show when there was no issue with synonyms.


texts: SQL Server 2012 not loading its self-generated SSL certificate

texts:
I have a requirement to remove the SSL certificate that is being used by a SQL Server 2012 SP4 database instance for data in transit encryption.
The client wants the SQL Server to load its self-generated certificate instead of the SSL certificate that is currently loaded.

After following the correct steps for removing the certificate by
opening SQL Server configuration manager, selecting SQL Server network Configuration, select Protocols for the database instance, right click, properties, select certificate tab, clicking clear, and then restarting the database instance I get the following error along with a failed start of the database instance which is,

  "Unable to load user-specified certificate [Cert Hash(sha1)
  "***removed****"]. The server will not accept a connection. You should
  verify that the certificate is correctly installed. See "Configuring
  Certificate for Use by SSL" in Books Online."

The thumb print id matches the certificate that we cleared.

It appears that the database instance is failing to load its self-generated SSL certificate or the procedure for removing the currently loaded SSL certificate is not fully removing it.

I've never encountered this problem before. I have been unable to find
any blogs or BOL that address this issue and how to resolve it. Additionally, the certificate value in the registy is blank. Which makes me question how SQL Server even knows to look for the SSL certificate after clearing it.

How do you get SQL Server 2012 SP4 to go from using a loaded SSL certificate to using its self-generated SSL certificate?


texts:
I am having an issue with a circular dependency.  Inside my "Team Engineers" table, there is the name of the Engineer and also a Boss.  A boss is also an Engineer as well.  Whenever I want to update a Boss, I want to update to the whole entire team of engineers that are associated to that boss, but I keep getting a 1451 error every time.  I have attached my DDL as well as a diagram.

create table `Team Engineers`
(
Engineer_Name char(50) not null,
Team char(50) not null,
Role char(100) not null,
Boss char(50) null,
primary key (Engineer_Name, Team),
constraint Team Engineers_Teams_Team_Name_fk
    foreign key (Team) references Teams (Team_Name)
        on update cascade,
constraint Team Engineers_Team Engineers_Engineer_Name_fk
    foreign key (Boss) references `Team Engineers` (Engineer_Name)
        on update cascade
);

create index `Team Engineers_Teams_Team_Name_fk`
on `Team Engineers` (Team);

create index `Team Engineers_Team Engineers_Engineer_Name_fk`
on `Team Engineers` (Boss);

Specific Error Message:

  "[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`F1_db`.`Team Engineers`, CONSTRAINT `Team Engineers_Team Engineers_Engineer_Name_fk` FOREIGN KEY (`Boss`) REFERENCES `Team Engineers` (`Engineer_Name`) ON UPDATE CASCADE)"

I've seen where people say that this error is common because the MySQL DB can't multiple operations at the same time.


texts: Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000

texts:
I tried to add a linked server running on SQL Server 2008 R2 (10.50.4000) from SQL Server 2012 (11.0.5058).

I keep getting this error:

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

Does anyone know how to solve this problem?

Are there any restrictions on building linked server between different versions of the database?

Okay, created this linked server using tsql code

EXEC sp_addlinkedserver
@server='xxxLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='xxxxxx',   -- add here your server name
@location='',
@provstr='',
@catalog='smpro5'  -- add here your database name as initial catalog (you cannot connect to the master database)
-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'xxxLinkedServer',
@useself = 'false',
@rmtuser = 'xxxxSqlUser',             -- add here your login on Azure DB
@rmtpassword = 'xxxxxxxx' -- add here your password on Azure DB

When open the server node and view all tables, get the "Failed to retrieve data for this request" error.

I can access the 2008r2 server via UI, without any trouble.

I do have one concern, that is my main database sits on Amazon cloud. Could this cause any problem?

Okay, thanks for all the answers guys. I think it is the problem caused by the firewall setting on the 2008r2 server. I am going to ask server admin to open that port for me.

Thanks to system administrator, it now works.

Special thanks to  Remus Rusanu, thank you for pointing me to the right direction !!!


texts: SQL Server 2012 Compatibility with New TLS 1.2 Standards

texts:
I'm trying to switch off TLS 1.0 on my SQL Server 2012 server in order to comply with PCI standards.

Initially I had some trouble with the SQL Server service not starting.

I found help for this issue by following answers to a previous question:

SQL Server compatibility with New TLS Standards

I'm having mixed results:

I have downloaded SQL Server 2012 Cumulative Update 7 (CU 6 also works) and the SQL Server Service then starts correctly.

I had a problem not being able to sign in to the DB instance through SQL Server Enterprise Manager which was fixed by installing .Net 4.6.

Next problem, client computer running IIS Application is unable to connect to SQL instance because of a 'handshaking SSL error'. I followed advice and installed the latest SNAC native client.

This was difficult to track down and the latest version available as a download from Microsoft was from 2014. I then obtained sqlnclient.msi dated 9/7/2015 revision number {E6CB4138-3D1C-4ADC-95C4-88322B60FC14} from a sub folder generated by the extract of CU 7 - "Path to Extract Folder \1033_enu_lp\x64\setup\x64".

I've updated this version of the Native client on my IIS server (and .Net 4.6) and I'm still unable to connect remotely to the SQL instance. If I enable TLS 1.0 I'm able to connect.

The exact error I'm getting is 'A connection was sucessfully established with the server, but then an error occurred during the pre-login handshake'.

My diagnosis is I don't have the correct version of SNAC on my machine compatible with TLS 1.2 and the CU 7 as the client and server cannot handshake. However, this sqlnclient.msi was extracted from the CU 7 and I cannot find a more up to date copy.

Has anyone else experienced this problem? What version of the SNAC are you using? Where did you get it?

Thanks


texts: Inserting a new lookup value for the SSIS Lookup Transformation

texts:
This may be obvious, but I've been banging my head for a while... please point me in the direction of an article if there is one.

I'm importing a series of Excel files into a SQL database.

They will have a list of text items with a value:

Name   |   Value
----------------
Name1  |   100
Name2  |   140
Name3  |   200

The target database has a lookup table of "Name" mappings.

What I want to do is find a corresponding NameID for a row, and put that into a column (this is the easy part), however, if there is no corresponding entry in the Name table it should insert the row and get the new ID.

Is there an accepted way to do this?  I'm looking at low volumes, so I don't need it to be particularly performant, just simple and easy to follow.


texts: How MySQL Table cache hitrate should be calculated?

texts:
I have a MySQL 5.7.15 instance which is monitored by nagios with check_mysql_health plugin.

One of the metrics monitored is tablecache-hitrate which is triggering a critical alert. (hitrate ~50%)

My current table_open_cache value is the default of 2K.

My current GLOBAL stats are:

| Max_used_connections                          | 20      |
| Opened_tables                                 | 5278    |
| Open_tables                                   | 2000    |
| Table_open_cache_hits                         | 803395  |
| Table_open_cache_misses                       | 5278    |
| Table_open_cache_overflows                    | 3271    |

The UPTIME at the time of those status were:

Uptime:         20 days 22 hours 30 min 45 sec

My Current tables by storage engine:

+-----------------------+-----------+
|  engine               | count(*)  |
+-----------------------+-----------+
|    InnoDB             |   105     |
|    MEMORY             |   51      |
|    CSV                |   2       |
|    MyISAM             |   11      |
|    PERFORMANCE_SCHEMA |   87      |
|    NULL               |  115      |
+-----------------------+-----------+

That's 256 tables and 115 views. 371 items.

I have a cronjob that runs a whole mysqldump every saturday. I take a status variables on friday and on sunday. The values of last friday and monday are:

Friday:

| Max_used_connections                          | 20        |
| Open_tables                                   | 2000      |
| Opened_tables                                 | 4309      |
| Table_open_cache_hits                         | 513027    |
| Table_open_cache_misses                       | 4309      |

Monday:

| Max_used_connections                          | 20       |
| Open_tables                                   | 2000     |
| Opened_tables                                 | 4924     |
| Table_open_cache_hits                         | 699751   |
| Table_open_cache_misses                       | 4924     |

I've seen a couple of ways to calculate tablecache_hitrate:

Open_tables / Opened_tables
Table cache hit rate = table_open_cache*100/Opened_tables

With those approaches the table_cache_hitrate will lower as the uptime increases, because of new tables created, mysqldump the whole database (opening all tables), etc...

Q1. Does mysqldump bypass table_open_cache? Or it uses already cached tables?

So i think that they aren't a reliable way to calculate tablecache_hitrate.

I guess for MySQL 5.6.6+, it can be calculated based on Table_open_cache_hits and Table_open_cache_misses.

Q2. Is this correct? Or what would be the most accurate way to calculate table_open_cache hitrate?

Regards!


texts: Restoring SQL Server 2012 database onto 2008

texts:
I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: http://learnsqlwithbru.com/2012/01/11/setting-database-compatibility-level-for-sql-server-2012/

However, the restore still doesn't work and I get "specified cast is invalid". Is there any way I can fix this problem?

Note: I don't want to use a Generate scripts option.


texts:
I created a plan guide using the following query:

EXEC sp_create_plan_guide
@name = N'Entity_Property fix',
@stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2',
@type = N'SQL',
@params = N'@P0 nvarchar(255), @P1 numeric(18, 0), @P2 nvarchar(255)',
@hints = N'OPTION (OPTIMIZE FOR UNKNOWN)';

It seems to work fine, but I noticed that there is a little warning icon on the plan in Object Explorer.

It looks like this:

I don't get any warnings when executing the query, and I can't find any information about it when hovering over it or checking the properties of the plan guide.

This is only applied in a test environment but why does it show up and should I be worried about it?


texts: UPDATE does not use INDEX, but SELECT does

texts:
I'm actually struggling understanding the problem here. I read everywhere saying that UPDATE benefits from indexes on the WHERE clause.

However, this query,

UPDATE `documents` SET `read`="1" WHERE `docid` IN (<subquery>)

does not seem to be using an index.
The table documents has an index on docid and on read.

When I do an EXPLAIN, I see possible_keys = NULL and rows = 8011008 (full table). The subquery does use keys and read the proper rows (2 rows).

In the other hand, this query:

SELECT * FROM `documents` WHERE `docid` IN (<subquery>)

does use the index on docid and runs very quickly. It reads a few more rows than needed (as per EXPLAIN), but totally acceptable.

Is there any explanation for this?
I use MariaDB 10.

As a funny note (regarding SELECT), if in the <subquery> I use a UNION, while the subquery seems to get the proper number of rows, it seems the primary query does NOT use the index and does a full table scan.

If in the UPDATE I use JOIN instead of IN, the indexes are properly used. I resolved my problems by using JOIN.


texts:
In my development environment, I am playing around with queries. For that I need to reset SQL Server memory, plans, etc. things which can ensure me that my query is the only stress/working process on server with all values reset

Few of the things which I am doing

Firing checkpoint
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FLUSHPROCINDB
DBCC FREESESSIONCACHE
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

Am I doing it correctly? or do I need more things to clear or see fresh statistics?


texts: Fill-in Missing Dates with calendar table

texts:
Hi i want the output to look like this. Filling the missing dates from the DATE_TBL. I tried UNION but no luck.

From this table: Log_table:

and DATE_TBL