texts: find the beginning and ending values of sequential runs

texts:
This article does the inverse of what I need:  Find which numbers in [1, 161] are not in the result set?

Given the sample data they used:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

this query answers the question, "What are the gaps?":

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);

+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+

What I want to know is what are the islands?
I would like to see output like this:

+-------+------+
| start | end  |
+-------+------+
|     1 |    2 |
|     4 |    4 |
|     6 |    8 |
|    10 |   10 |
+-------+------+

but I can't quite figure out the query to do so.  I am using MySQL 5.6.

Anyone have a solution?


texts:
Maybe double post but I could not find any answer here.

I saw couple of posts how to name my tables and I understood that (semi-officially) it should contain lower cases, should be underscored and singular (f.e. my_table)

My question arose when I had to do the following.
I have an app where I have user, platform and user_info, where platform is what the user is working on (f.e. MAC, PC, LINUX). If I need to create junction table between user and platform, how do I name it in order the next person browsing the schema to understand that user and platform have a junction table named user_platform, but to not be confused when he sees the table user_info?

BR


texts: Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

texts:
The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked:

  To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately.

Exactly what error would that be?

As this is an acceptable condition, I want my Java code to check for such an expected error and then work around it.


texts:
I am facing a problem in writing a sql query for multiplication of two matrices.
Consider that i have a 3x3 matrices A and B, having the following schema:

A(i,j,val) and B(i,j,val) where

i int: Row index

j int: Column Index

val int: Cell value

The product of matrix A and matrix B is matrix C having cell at row i and column j. I want a single sql query to get the matrix product of A and B.


texts: Top N earliest punched in Employees across departments in a month

texts:
Find two of the earliest punched in Employees across departments in a month The dataset.

Id   EmpName         EmpPunchInTime        EmpDept
1      Dharma        26-JAN-2018 08:45     Engineering
2      Gumpina       26-JAN-2018 08:46     Finance
3      Kumar         26-JAN-2018 08:47     HR
4      Kamal         26-JAN-2018 08:48     Engineering
5      Muruges       26-JAN-2018 08:49     Finance
6      Gumpina       25-JAN-2018 09:01     Finance
7      Kumar         25-JAN-2018 08:42     HR
8      Biju          25-JAN-2018 08:40     Engineering
9      Divya         25-JAN-2018 08:49     HR
10     Vali          24-JAN-2018 08:52     Engineering

The expected output is,

Id   EmpName       EmpPunchInTime        EmpDept
8      Biju          25-JAN-2018 08:40     Engineering
1      Dharma        26-JAN-2018 08:45     Engineering
2      Gumpina       26-JAN-2018 08:46     Finance
5      Muruges       26-JAN-2018 08:49     Finance
7      Kumar         25-JAN-2018 08:42     HR
9      Divya         25-JAN-2018 08:49     HR

FYI I the query which I used

SELECT *
FROM (SELECT *
            ,DENSE_RANK() OVER ( PARTITION BY EmpDept ORDER BY EmpPunchInTime) RANK
      FROM EMPLOYEE_PUNCH) emp_pun
WHERE RANK <= 2

I have tried to solve by using dense rank(partition by EmpDept, order by EmpPunchInTime) but am not seeing desired results.

Note: If the same employee in a department punches early in most of the days(like Kumar of HR dept here in this dataset) then pick the earliest date among his dates and the employee who punches next to him.


texts: SSMS returns results immediately; ADO takes longer

texts:
I am running a query that returns ~250k results.

It is basically SELECT * FROM my_schema.my_view (and the view is somewhat complex, and all table references in it are two-part)
I am not passing any parameters
I have tried setting ARITHABORT to ON and using OPTION (RECOMPILE)
Within 2 seconds of executing, SSMS has returned ~23k results
SSMS takes ~1m to return all ~250k results
With ADO and the same query, it takes ~1m to obtain the first result from the DataReader
PARAMETERIZATION is set to SIMPLE

Code example (using Dapper):

foreach (var result in connection.Query("SELECT * FROM dbo.my_view"))
{
    // process each result in here
}

I have read http://www.sommarskog.se/query-plan-mysteries.html and about a couple dozen other SO/DBA answers trying to make sense of this but I just can't seem to get my results streaming any faster. It almost seems like when I use ADO, SQL Server tries to store all of the results in a temporary table before returning.

What else can I check or try to get my results streaming immediately like they do in SSMS?


texts: How to connect to specific session id & serial# ? Blocked sessions

texts:
I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How?

Background: using V$SESSION_BLOCKERS I see sessions are blocked by each other by INSERT statements to one of two tables: _REQUEST and _RESPONSE

Blocked sessions wait event: enq: TX - row lock contention

Query 1:

insert into _REQUEST (creation_date, IS_PROCESSED, name, packet, PARENT_SKID, BATCH_SKID, retry_delay, revision, SERVICE_NAME, ttl, type, REQUEST_SKID)
values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 )

Query 2a (from DB trigger, :B1 is a payload):

SELECT COALESCE(ERROR_DETAILS, ERROR_MESSAGE, FAULT_REASON, SOAP_ERROR) AS ERROR_MESSAGE, ERROR_CODE
FROM XMLTABLE( '//*:DataHandlerWebServiceException | //*:Fault | //*:PLMDataHandlerError'
     PASSING XMLTYPE(:B1 )
     COLUMNS ERROR_DETAILS VARCHAR2(256) PATH '*:errorDetails'
            ,ERROR_MESSAGE VARCHAR2(256) PATH '*:errorMessage'
            ,ERROR_CODE VARCHAR2(8) PATH '*:errorCode'
            ,FAULT_REASON VARCHAR2(256) PATH '*:Reason/*:Text'
            ,SOAP_ERROR VARCHAR2(256) PATH '*:COMMON_LOG_MESSAGE/*:LOG_MESSAGE' )

Query 2b (we already know APPEND is ignored here, see Note 1):

INSERT /*+ append */ INTO _response( RESPONSE_SKID, REQUEST_SKID, HAS_FAILED, WAS_TIMEOUT, IS_PROCESSED, SOAP_REQUEST_TIMESTAMP, SOAP_RESPONSE_TIMESTAMP, RESPONSE_HTTP_STATUS, ERROR_MESSAGE, RESPONSE_BODY )
values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )

How is it possible that INSERTs, to different tables, are blocking the sessions? I was looking into V$ACTIVE_SESSION_HISTORY but don't find anything yet (I'm not a DBA yet)

Note 1: APPEND hint ignored


texts: How to organize binary tree in database?

texts:
Here is the situation, I need to organize some users like an binary tree to explain organization workflow, but organization can change.

Here is how its look like:

         1
      /     \
     2       3
    / \     / \
   4   5   6   7

Its sounds easy:

Lets imagine this is the user table:

id | user  | position | who_contracted
1  | ana   | 1        | null
2  | bob   | 2        | 1
3  | joe   | 3        | 1
4  | Nik   | 6        | 3
5  | Ahn   | 4        | 1
6  | Rob   | 5        | 2
7  | Chloe | 7        | 1

Ana contracted 4 people in organization, IDs [2,3,5,7]
They are organized as they come to business.

But, when Ana was inviting they, Joe[3] invited Awn[5] to business. Its seat goes to be seat [6].

When Ana[1] invite 1 more, the next opened seat is 4, because each invited people get first seat from left to right down the user who contracted him

It makes easy to build an strong sales team.

Now, I need to know how to find next seat to user in position 3.

I know how to do it with math, but do not know how to organize correctly the database.

For sample, if User id 3 invite one more, will get the seat 12, but if user id 6, invite first, seat will be 12 also.

I need to calculate just in time when insert into table.

Any ideas??

ps. One more thing, I'm trying a way, that if I delete user 3, the users down come to up automatically, user on seat 6 will up to seat 3, because it is on low position compared with next user position, that is 7.


texts: Transferring full database to another machine oracle 10.2 xe

texts: We are having oracle 10.2 xe working on windows xp sp3(32bit). We need cloning/copying full database to windows server2008r2 machine(64bit). Any help.


texts: Finding empty columns of a table in PostgreSQL

texts: What query would return the name of the columns of a table where all rows are NULL?


texts:
When creating a database structure, I tend to create unique composite keys for every group of data that needs to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.

On one hand, the benefit is that I can avoid the insert of 'bad records'.
Are there cases where the usage of these keys becomes too much or is sign of bad db design?


texts: splitting an string in an array into an array Mongodb

texts:
I have the current document:

{
    "_id" : ObjectId("5c95b81b10ce2a953215c269"),
    "name" : "Œufs & Cie",
    "city" : "Brossard",
    "state" : "QC",
    "postal_code" : "J4Y 0G4",
    "review_count" : 12,
    "categories" : "Restaurants, Breakfast & Brunch, Canadian (New), American (Traditional)",
    "attributes" : [
        {
            "GoodForKids" : "True",
            "Alcohol" : "'none'",
            "Ambience" : "{'touristy': False, 'hipster': False, 'romantic': False, 'intimate': False, 'trendy': False, 'upscale': False, 'classy': False, 'casual': False}",
            "HasTV" : "False",
            "RestaurantsPriceRange2" : "2",
            "RestaurantsAttire" : "'casual'",
            "NoiseLevel" : "'average'",
            "Caters" : "False",
            "BikeParking" : "True",
            "RestaurantsDelivery" : "False",
            "OutdoorSeating" : "True",
            "RestaurantsTakeOut" : "True",
            "RestaurantsGoodForGroups" : "True",
            "RestaurantsReservations" : "False",
            "WiFi" : "'no'"
        }
    ],
    "hours" : [
        {
            "Monday" : "6:0-15:0",
            "Tuesday" : "6:0-15:0",
            "Wednesday" : "6:0-15:0",
            "Thursday" : "6:0-15:0",
            "Friday" : "6:0-15:0",
            "Saturday" : "7:0-16:0",
            "Sunday" : "7:0-16:0"
        }
    ]
}

I want to be able to split the "Ambience" field in "attributes" into a an array. However, I have tried this command:

  db.list.aggregate([{$project:{attribute:{$split:["$attributes.Ambience",
  ", "]}}}])

but I get the error that$split requires a string but it found an array.

I have also tried:

  db.list.aggregate([{$project:{attributes.Ambience:{$split:["$attributes.Ambience”,
  ", "]}}}])

And I got another error saying that I'm missing a ":" after property id @ 1:40.

Does anyone know how I can split the a string in an array into another array? Or how to dynamically find all the possible strings that can be turned into an array so I can turn them into an array?

**UPDATE:
I checked the type of attributes.Ambience by running the command:

  db.list.aggregate(  [  { "$project": { "fieldType": {  "$type":
  "attributes.Ambience"  } } }  ])

and I got the result :

  { "_id" : ObjectId("5c95b81910ce2a953215362a"), "fieldType" : "string"
  }

And what I want the resulting attributes.Ambience field to look like is

"attributes" : [
            {
                ...
                "Ambience" :
                [
                    {
                       "touristy": False,
                       "hipster": False,
                       "romantic": False,
                        etc....
                    }
                ],
                ...
            }


texts: Why is my postmaster process (sometimes) becoming unmanageable after a WAL base restore?

texts:
TL;DR: Un-stoppable, unusable postmaster is being spawned when Postgres is started right after restoring its data directory from a WAL base backup. Why?

Context:

We run postgresql 8.4, on CentOS 6, using the PGDG packages. We have a script for use on developer test environments that restores a nightly backup of our production server's data directory (created between calls to pg_start_backup and pg_stop_backup). The script decompresses the file, and uses restore_command to reapply any WALs that were generated during the time that the backup was taken on production.

It usually works fine, and restores hundreds of times faster than an SQL-based restore of a pg_dump'ed file.

Problem:

Sometimes, after it unzips the data dir, the script starts postgres by running /etc/init.d/postgresql start (which is a symlink to /etc/init.d/postgresql-8.4. This makes it a predictable init script for when we eventually upgrade to 9.*). It reports "OK", as in: it started correctly. Then WALs don't restore; it hangs indefinitely waiting for a recovery.done file to appear.

What I've Tried:

When I ran /etc/init.d/postgresql status during the indefinite hang, the init script reports dead but pid file exists.

Then I ran ps -ef | grep post. Oddly, the postmaster process and archivers etc were running. All of the invocation parameters were correct (right datadir etc etc).

When I ran psql, it detected a running postmaster and an initted postgres DB, but did not detect the main DB--the one we care about restoring via the WAL script.

I then checked the perms on the data dir, and everything looked OK.

Running /etc/init.d/postgresql stop reported "OK", and killed the archiver/watcher processes, but the postmaster stayed running.

The same thing happened when I tried killall -r '*.postmaster*.'.

The only thing that worked to resume the stuck WAL restore was a killall -s 3 -r '.*postmaster.*' (Signal 3 is SIGQUIT), and then a /etc/init.d/postgresql start.

I checked pg_startup.log and the daily files in pg_log during the unmanageable state, and everything looked fine. pg_startup.log registered a successful start as the last entry.

Possible Causes:

A couple of (I think minor) things are nonstandard about our config.

Our init script is symlinked, as I said before, to a version-agnostic script at /etc/init.d/postgresql. This points wherever we want it to. At present it points to /etc/init.d/postgresql-8.4.
Our postgresql.conf file lives in /etc/ (with an owner and group of the postmaster user), and has a symlink into the data directory. Our WAL restore script ensures that the symlink is re-created before attempting to start postgres.
We recently upgraded our infrastructure from Postgresql 8.4.11 to 8.4.12. We are testing the new version for stability. Our production servers are running 8.4.11. However, we are pulling data off of them via pg_dump, scrubbing it, and then 'packaging' it for WAL restore elsewhere (on 8.4.12), so we're not restoring WALs across incompatible versions of Postgres.

Question:

Why is it doing this? Is one of the possible causes listed below possibly to blame?


texts: sp_BlitzCache throws Table DML warning on temp tables

texts:
I have been using sp_BlitzCache to troubleshoot some performance issues on a client's SQL Server 2012 instance.  I am running:

EXEC sp_BlitzCache @ExpertMode=1, @SortOrder = 'reads';

And one of the procedures high up on the dummy board is being flagged with a "Table DML" warning.

The only thing that I can see going on is the creation, use and dropping of a temp table named #Fred.

Besides the name of the temp table ;) , should I be concerned about this warning?  Based on the details in the link https://www.brentozar.com/blitzcache/table-dml/ it seems like this is a false positive of sorts.  If not, what concerns are there with temp table usage such that a warning is raised?

Btw #Fred replaced @Fred due to table variable warnings.


texts: How can I get all of a few users' roles?

texts:
I need to see all of a user's roles, including the roles inherited from a role.

Consider for example, this arrangement:

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

Then I want the result

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

I'm using Oracle 11.2g, but I also want answers for future versions of Oracle. I'd like for this question to remain relevant if Oracle introduces new features that make this easier (like a view that just gives this to us straight up without writing our own queries).


texts: Proper tuning for 30GB InnoDB table on server with 48GB RAM

texts:
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the following values:

InnoDB buffer pool to 10GB
log file size 5M

Note that my key_buffer_size of MyISAM is 8GB and I'm also allowing Memcache to use 8GB, and have Redis installed (not sure how much he takes).

Are there any more values I should try to configure?


texts: Amazon RDS - Can't connect to MySQL database server?

texts:
So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. The database is up and running, and I've created a security group, as well as an EC2 security group. Within the Ec2 security group, I've opened up port 3306 (the MySQL port), and added it as a rule. I've then applied this rule. I've then connected the security group to the EC2 security group, and I connected the database to the security group. Thus the database should have port 3306 open (and for all I know it does!).

So here's the issue. I'm trying to connect to the MySQL server based on the IP of the database I'm given by Amazon Web Services. However, when I attempt to connect via the Terminal command shell, the connection times out, saying that the MySQL connection failed.

Here's what I type in: "mysql -h *****.rds.amazonaws.com" where the asterisks are part of the IP address.

The error is as follows: "ERROR 2003 (HY000): Can't connect to MySQL server on '*****.rds.amazonaws.com' (60)"

I've also tried specifying a username and password to the command, but the same error occurs.

Any thoughts as to why the connection is failing?

Thanks, any help appreciated!

Jake


texts:
We are working on a Proof Of Concept using Polybase with Cloudera.

In the polybase documentation it says we can connect to Cloudera 5.5:
https://msdn.microsoft.com/en-us/library/mt143174.aspx

We are looking at Cloudera 5.9 - does polybase work with cloudera versions over 5.5?


texts: SQL Server 2008R2 - how to install replication components

texts:
I am trying to install the Replication components for SQL Server 2008R2, on technet website it says:

On the computer that is running IIS, insert the SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005 CD and run Setup.exe.
In the SQL Server Installation Wizard, on the Components to Install page, click Advanced.
On the Feature Selection page, click Client Components, and then click Connectivity Components to install the SQL Server replication components

But the CD is lost, is there anyway to download the components or the setup files online?

Select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


texts: How to connect to a SQL Server installed on Windows Server 2012 running on VPS?

texts:
I want to connect to my SQL server installed on a windows server 2012, that is running on a VPS(Virtual Private Server) remotely, I have done the following steps:

Enabling SQL Server to allow remote connections
SQL Server Configuration Manager>SQL Server Network Configuration>Protocols For MSSQLSERVER>TCP/IP Set Enabled
Create a new Inbound Rule for port 1443 in windows firewall

Still I cannot connect to SQL server remotely via SQL server management studio.

Thanks


texts: Trick for AUTO_INCREMENT on composite keys in InnoDB

texts:
In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table:

CREATE TABLE `ExampleTable` (
     `KeyPartA` mediumint(8) unsigned NOT NULL,
     `KeyPartB` smallint(5) unsigned NOT NULL,
     `Data` text NOT NULL,
     PRIMARY KEY (`KeyPartA`,`KeyPartB`)
) ENGINE=InnoDB;

Now to simulate AUTO_INCREMENT, I learned, it is possible to create the following trigger

DELIMITER $$

CREATE TRIGGER ExampleAutoIncrement BEFORE INSERT ON ExampleTable
FOR EACH ROW BEGIN
        SET NEW.KeyPartB = (
             SELECT IFNULL(MAX(KeyPartB), 0) + 1
             FROM ExampleTable
             WHERE KeyPartA  = NEW.KeyPartA
        );
END $$

DELIMITER ;

Now inserts work correctly, generating auto-incremented values. Unfortunately I can not think of a sensible way to get the newly created key value.

Neither LAST_INSERT_ID() nor its C counterpart return it, as there is no actual AUTO_INCREMENT column. Also it is not possible to set the value with LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1) inside the ExampleAutoIncrement trigger, because MySQL resets the value after the trigger finishes.

The only way I thought of was to issue a SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ? after an insert, which defeats the whole purpose of the trigger. I.e. I could just as well do this before the insert and increment manually.

Is there any way to get the incremented key value from the trigger or some other means of simulating the AUTO_INCREMENT I want?


texts: Splitting logging into a separate database

texts:
In a postgresql database for a asp.net mvc website I have 3 tables (visits, uservisits, hits) that have update/insert query's running on them on every pageview. Rows in visits (for all visitors) and uservisits (for authenticated visitors) are updated for the duration of each visit and hits has a new row inserted every time.

I'm worried that the logging query's will cause unnecessary traffic. I also found this article

  "Three things you should never put in your database" that states "Storing your logs in a database isn't a HORRIBLE idea, but storing them in the same database as your other production data is"

which makes me think that maybe I should split the database so that these 3 tables are separate.

I will rarely read from the logging tables but there will be heavy read operations on other tables in the database such as searching text. I'm using caching throughout but for text searches it is likely that many unique combinations of keywords will be used and therefore caching will have limited effect there. The load will be heavy so the question is would this optimisation be worth it or is it premature optimisation?


texts:
Neither

[vagrant@localhost ~]$ sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,"test","test@gmail.com");"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com)' at line 1

Nor

[vagrant@localhost ~]$ sudo mysql -e "USE books; INSERT INTO authors (id,name,email) VALUES(1,"test","gmail.com");"
ERROR 1054 (42S22) at line 1: Unknown column 'test' in 'field list'

works for inserting values in MySQL, while the columns exist:

[vagrant@localhost ~]$ sudo mysql -e "USE books; SHOW COLUMNS FROM authors;"
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| email | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Version

[vagrant@localhost ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.73 Source distribution


texts:
I am trying to restore the Latest content to one of my Primary Replica Database which is part of AOAG.

My question is during the Restore operation,

1) Will the synchronization still happens between Application and DB server ?

2) During the DB restoration will the DB which is part of AOAG is locked out so that the synchronization with Secondary AOAG replica not happens ?


texts: When will 'Max_execution_time_set_failed' status variable be incremented in Mysql?

texts:
Server: Mysql 5.7

By documentation, it has been said that

  The number of SELECT statements for which the attempt to set an
  execution timeout failed.

Can anyone explain the scenarios or share queries for which there is a possibility of this failure and thereby incrementing this variable. I have attempted myself but I couldn't find it.


texts:
Apologies in advance if this is a dumb question.

Background:
I am a student intern, I've been given the task of adding a new feature the companies client administration system. Basically it involves "purging" subscriptions, they want selected subscriptions to be removed from the client admin system but remain in the database for recording purposes. We currently have around 600,000 subscriptions. They wish to "purge" 10,000 subscriptions right away and another 30,000 over the next few months, so around 6% of the subscriptions table.

What I am wondering is what is the best way to accomplish this. I have come up with two options;

a) Add a new table to the database to store the "purged" subscriptions with 3 columns, purgedId, subscriptionId which is a fk referencing the subscription, and purgedDate. Then in any sql that references the subscription table left outer join the purged table and use "where purgedId = null".

b) Add a new column to the subscription table to store the purgedDate and add "where purgedDate = null" to all of my subscription sql.

Option a) seems like a waste, to add a whole new table to store essentially one attribute.
Option b) seems like a waste, to add a column that is only used by 6% of the rows in the table.

On another note, is an outer left join the best way to exclude the purged subscriptions?

Thanks in advance for any assistance you are able to provide and once again sorry if this is an elementary question.


texts: Best mysql partition for stats table - by date or productid?

texts:
we have a large stats table which logs hits per day, per product.
   date | datetime
   productid | int
   hits | int
   channel | enum

About 200M rows+
We have two ways of querying this date - It always has productid in the WHERE (or multiple productIDs), then optionally with a date specified too. Most of the queries will have both.
We allow users to query on the usual date 'buckets' eg Today/Yesterday/Last 7 days/Last month/Last 3 months/This year/Last year. They can also pick arbitrary dates.
So I'm tossing up between partitioning on date vs productid. My thinking is that if we're querying on a number of productIDs at once (using IN()) then this could mean spanning multiple partitions. However, as most of the queries will be within a year, that partitioning by date would be best?
As most queries are for short periods (eg days/weeks), perhaps we have something like:
Last 3 months
Last 3-6 months
Last 6-12 months
Then yearly buckets.
Our data goes back to 2005.
Thanks for advice.


texts:
I am attempting to import excel sheets into SSMS and I am having trouble with data types.  I was running into problems with columns with both text and numbers in them.  I want these columns to import as varchar, but some of the columns were importing as "float" and I was getting an "unknown type conversion" error that would crash the package.  Changing the mapping seemed to cause more problems than it solved.  The process that I have found that seems to work most of the time is as follows:

Import a sheet with only headers and sample data (to "seed" the data type) into a new table
Use Design view to correct data types as necessary
Import the sheet with all the headers and data

This has worked for 9 tables.  However, I am now running into the following problem: I followed steps (1) and (2) above.  When executing step (3), I am getting "unknown column type conversion" errors for columns without any data in them.  For some reason, the Import tool interprets a couple of empty columns as "DateTime" when the data type of the table column is "float".  This is causing the import to fail.  I changed the data type in Excel properties to "Number" but that does not seem to make a difference.  I have two questions:

How can I get the data imported with the correct type?
Why does the Importer get it wrong sometimes?  Other columns without data are recognized as "float".


texts: MySQL BETWEEN command - performance (order)

texts:
I want to create a table which will contain approx 20,000 records of three fields each.  Two of the fields will contain numeric values.

I want to be able to issue a SELECT BETWEEN command. One record will be returned.

I anticipate that certain records will be returned more frequently than others.  Will I gain any performance increase by placing these records near the start of the table? (I'm assuming that MySQL reads a table sequentially).


texts: Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2

texts:
I am noticing different behavior in mariadb 10.1 & 10.2 reg.connection aborts.
I noticed connection getting aborted after 7 minutes(wait_timeout) of any query execution. There are few random connection aborts happening in my application too. But I don't see this behavior(connection abort logs) happening in mariadb 10.1.
I am aware that the default value for variable "log_warnings" has been changed from 1 to 2 in MariaDB 10.2. But even with log_warnings value as 2 in MariaDB 10.1, I don't see connection aborts happening.
For eg. In below query connection Id : 2218 is created
MariaDB [dummy]> show global variables like '%log%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2218
Current database: dummy

From general query logs, the query executed time is 11:53:15
201222 11:53:15     6 Query     SHOW MASTER STATUS
                    6 Query     SHOW SLAVE STATUS
                    6 Query     UPDATE `oam`.`heartbeat` SET ts='2020-12-22T11:53:15.000830', file='mariadb-bin.000001', position='58352014', relay_master_log_file=NULL, exec_master_log_pos=NULL WHERE server_id='1'
                 2218 Connect   root@localhost as anonymous on dummy
                 2218 Query     show databases
                 2218 Query     show tables
                 2218 Field List        mytable
                 2218 Query     show global variables like '%wait%'

From Error logs, the connection closed at 12:00:16
2020-12-22 12:00:16 140673402672896 [Warning] Aborted connection 2218 to db: 'dummy' user: 'root' host: 'localhost' (Got timeout reading communication packets)

wait_timeout is set to 7mins(420s)
MariaDB [dummy]> show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 420   |
+---------------+-------+
1 row in set (0.01 sec)

I could see the same behavior happening for any query execution (Connection aborting after 7mins) in MariaDB 10.2 but it is not happening in 10.1 even when the log_warnings level set to 2
Am I missing any configuration in 10.1 to observe these logs? or Any behavioral change that happened in 10.2 regarding this?
--EDIT--
We have the same wait_timeout configured in 10.1 as well (420s). In fact, we have kept most variable's values as same which might be the reason for the error as per the link https://dev.mysql.com/doc/refman/5.6/en/communication-errors.html.
| max_allowed_packet  | 33554432   |
| max_connections       | 516   |
| thread_cache_size      | 256   |
| interactive_timeout    | 420   |


texts:
We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables).

In our current DB architecture, we have one single database & all level 1 entities separated by db schemas. All level 2 entities are being used only for logical grouping & don't contain any data as such. Then comes all level 3 entities. All daily level data is stored with respect to these entities. There is a dailyfacts table in each schema which stores data for all level 3 entities under that level 1 entity. Plus there are some other additional table are being used to store data for level 3 entities.

Currently our business is growing very rapidly & with this DB architecture we are not able to handle data properly. SQL timeouts & deadlocks have became daily headaches. Also if load on one entity increases then it starts affecting other entities as well. It very hard to scale horizontally with current DB architecture.

So after long discussion with team, we came to conclusion that we should create separate databases for each level 3 entity & one master database which will maintain all business model hierarchy. But the problem with this approach is that we will end up creating almost 100 databases(as per current number of entities & it will keep on increasing as & when new customer comes). & maintaining these many databases will become pain in the a** after sometime.

I am not an expert on DB architecture. So I want to know,

Is it OK to have these many databases in production environment? Are we moving in right direction?
If it is OK then what all things we should look at before/after migration? (all are newbies here)
Is there any other alternative to solve above problem?

We are using,

DBMS : SQL Server 2014
OS : Windows Server 2012 R2
Processor : Intel Xenon CPU E5-2630 v3 @2.40GHz, 16 Cores
RAM : 128 GB

Thanks in advance.


texts:
I am having trouble dropping a database on my server
innodb-file-per-table is set to true
We previously had a huge table (1TB) which we used a drop table command. This result i believe was successfully completed but left a file called #sql-ib116.ibd in the directory.
We have completely cleared out the db, but when i issue a drop database dbname, it seems to be stuck in the "closing tables" phase
I then tried to create a frm equivalent and drop the table using this guide to no avail
https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/
Im running out of server space and would ideally like manually remove the idb file
If i delete the file from the datadir, will this cause issues? im not worried about the data in this idb file. I also many other databases in this mariadb instance, will deleting affect the remaining?
Any ideas would be fantastic!
Thanks
p.s. i just ran the command SELECT * FROM information_schema.INNODB_SYS_TABLESPACES where name regexp 'dbname/'
and i can still see the table
dbname/#sql-ib116
dbname/#sql-ib53761
when i do show tables it still returns no values


texts:
Is there some way to audit, for example, a select in a specific table just by one specified user? something like this:
audit select on foo.table by soe;

this command return:
ERROR at line 1:
ORA-01708: ACCESS or SESSION expected

also, it's not possible using the suggestion:
audit select on foo.table by session by soe;

ERROR at line 1:
ORA-00933: SQL command not properly ended


texts: How to specify the path of an attachment stored on a different computer in the same network

texts:
I'm trying to send an email through SQL Server. The sp_send_dbmail has a parameter to specify the path of a file to send as an attachment. It works perfectly when the file is in the same server where SQL Server is hosted. However, I need to send a file that is stored on another server or computer in the same network.

How can I specify the path for such file?

Edit: I see no errors in the server agent logs nor dbmail.
I ran this query: Select * FROM sys.dm_server_services and gave the account under the service_account column full control of the file in the remote server.

This is how I'm calling the sp:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Profile',
    @recipients = 'email@email.com',
    @subject = 'Testing Attachments',
    @file_attachments = '\\servername\c$\Temp\OutputReport.pdf'

Error:  Msg 22051, Level 16, State 1, Line 0

Attachment file '\\servername\c$\Temp\OutputReport.pdf' is invalid.


texts:
input:
Declare @OUT xml
select @OUT = N'
<row>
  <kind>MainCat</kind>
  <sortid>1</sortid>
  <kind_id>1</kind_id>
  <PPoet>حافظ</PPoet>
  <MTitle>حافظ</MTitle>
  <Row>1</Row>
</row>'

select T.X.value('@Kind', 'nvarchar(50)') as kind,
T.X.value('@sortid', 'int') as sortid
from @out.nodes('/row') as T(X)

this select return NULL!


texts:
i had a table like following:

OBJECTID            FRUIT
----------------------------
XA13AC              Apple
HAS13X              Grape
UTHQ1G              Orange
YQ1GXQ              Kiwi
OQP1GV              Apple
GPQ1VA              Grape
GQ1GQ1              Manggo
GYX1GQ              Banana
OPI1G2              Apple

Table:

CREATE TABLE `FRUITS` (
  `OBJECTID` varchar(6) DEFAULT NULL,
  `FRUIT` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data:

insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('XA13AC','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('HAS13X','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('UTHQ1G','Orange');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('YQ1GXQ','Kiwi');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OQP1GV','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GPQ1VA','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GQ1GQ1','Manggo');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GYX1GQ','Banana');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OPI1G2','Apple');

i tried following SQL not working:

SELECT *,
             (
                CASE FRUIT
                WHEN @curType
                THEN @curRow := @curRow + 1
                ELSE @curRow := 1 AND @curType := FRUIT END
              ) + 1 AS GROUP_NUM
FROM FRUITS
JOIN (SELECT @curRow := 0, @curType := '') r
GROUP BY FRUIT

expected result:

OBJECTID            FRUIT       GROUP_NUM
-----------------------------------------
XA13AC              Apple           1
HAS13X              Grape           2
UTHQ1G              Orange          3
YQ1GXQ              Kiwi            4
OQP1GV              Apple           1
GPQ1VA              Grape           2
GQ1GQ1              Manggo          5
GYX1GQ              Banana          6
OPI1G2              Apple           1

trying to group the fruit by name and its increment group_num on different fruit name.

any help would be great.
Thanks


texts: Cannot connect to WMI provider, SQL Server Configuration manager

texts:
I cant connect to SQL Server Configuration manager.
I have installed SQL Server 2014 x64 Standard edition and SQL Server Management Studio ,which I can open and work with. Just to point out, that I am doing clean installation of windows, so there is no other SQL instances on this computer.

But I cant run SQL server Configuration manager here is the full error

So, there is an article on Microsoft web site that says way issue is present and gives an workaround.

Basicly it says

  This problem occurs because the WMI provider is removed when you
  uninstall an instance of SQL Server. The 32-bit instance and the
  64-bit instance of SQL Server share the same WMI configuration file.
  This file is located in the %programfiles(x86)% folder.

But there is no other SQL instance, and I haven't removed anything. But when I go to %programfiles(x86)%\Microsoft SQL Server\ there are files and folders present. So, my guess was OK It installs something for 32-bit. Stupid of me, but I have run the command in CMD.exe

  C:\Windows\system32>mofcomp "%programfiles(x86)%\Microsoft SQL
  Server\120\Shared\sqlmgmproviderxpsp2up.mof" Microsoft (R) MOF
  Compiler Version 10.0.10586.0 Copyright (c) Microsoft Corp. 1997-2006.
  All rights reserved. Parsing MOF file: C:\Program Files
  (x86)\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof MOF
  file has been successfully parsed Storing data in the repository...
  Done!

Needles to say, the same issue Cannot connect to WMI provider.. is still present.


texts:
Does the target table need to be identical to source table schematically? Specifically: 1) do they need to have the same number of columns and do the column types must be the same?, 2) do the column NAMES need to be the same also?

General MERGE syntax is as follows:

MERGE INTO <target table> AS TGT
USING <SOURCE TABLE> AS SRC
 ON <merge predicate>
WHEN MATCHED [AND <predicate>]
 THEN <action> -- one with UPDATE one with DELETE
WHEN NOT MATCHED [BY TARGET] [AND <predicate>]
 THEN INSERT... –- if indicated, action must be INSERT
WHEN NOT MATCHED BY SOURCE [AND <predicate>]
 THEN <action>; -- one with UPDATE one with DELETE


texts: How can I use a SQL query to retrieve data on a database sever?

texts: I have connected to a SQL server in Excel . I want to be able to import data from a table located on the server. However this table is too big to import into Excel, I'm talking millions of rows. I only want to import certain data into Excel, for example only import the data where the date is between a certain range. Where can I do this in Excel? If there are any alternatives to retrieving data from a large database please let me know.


texts:
I'd appreciate some help with something I am trying to achieve please.

The parts that my company make are built from sub assemblies and sub assemblies of sub assemblies.
This looks like:

             1
         /   \  \
        /     |   \
       2      3    7
      / \    / \
     4   5  4   6

Where 4 & 5 come together to make 2, 4 & 6 make 3, then assemblies 2, 3 & 7 make 1.

My table looks like this;

+------+-----+-------+----------+----------------+
| ROOT | SUB | LEVEL | LEADTIME | TOP LEVEL PART |
+------+-----+-------+----------+----------------+
|    1 |   1 |     0 |        4 |              1 |
|    1 |   2 |     1 |        2 |              1 |
|    1 |   3 |     1 |        5 |              1 |
|    1 |   7 |     1 |       20 |              1 |
|    2 |   4 |     1 |        4 |              1 |
|    2 |   5 |     1 |        1 |              1 |
|    3 |   4 |     2 |        4 |              1 |
|    3 |   6 |     2 |        5 |              1 |
+------+-----+-------+----------+----------------+

Each part has a lead time of how long it takes to make it. What I want to do is pass a top level part number and it calculate the overall lead time for the part.

Originally I thought I could just group by the Level and take the maximum lead time per level and add them together but that wouldn't be accurate. Level 1 Part 7 takes 20 days to make so we would be able to be making all of the other parts whilst 7 was being made. The calculation needs to be based on the part that consumes its parent (for want of a better phrase).

What I need to do is calculate the lead time for every route through the tree and add up the lead times as it goes. So I would like to return a table something like;

+----+----------------+-------------------------+------+------------+
| ID | Top Level Part | Parts Nodes Combination | Days | [BASED ON] |
+----+----------------+-------------------------+------+------------+
| 1  | 1              | 1 >> 2 >> 4             | 10   | 4 + 2 + 4  |
| 2  | 1              | 1 >> 2 >> 5             | 7    | 4 + 2 + 1  |
| 3  | 1              | 1 >> 3 >> 4             | 13   | 4 + 5 + 4  |
| 4  | 1              | 1 >> 3 >> 6             | 14   | 4 + 5 + 5  |
| 5  | 1              | 1 >> 7                  | 24   | 4 + 20     |
+----+----------------+-------------------------+------+------------+

I can then find the MAX of the days column which will tell me that the overall lead time for Part 1 is 24 days.

I really hope I've made my post understandable and all is formatted correctly. I've tried to read up on recursive SQL queries but I'm struggling to get my head around them. The most number of levels I am ever likely to have is 4 so I'd hope it wouldn't be too pc intensive. If so, I'd much rather be able pass all of my parts through, rather than doing them one part at a time (hence why I've got a top level part column in my table).

Any help or advice would be greatly appreciated.


texts:
I want to deploy tools to monitor my Production Percona servers (5.6 on Linux). I never used Nagios or any tool before. I did go through this great link
but I wanted to clarify few things before deploying them:

Those mysql exporter and node exporters of Prometheus which I need to deploy in my database host - what would be overhead of them? Resource intensive?
I did go through Nagios as well - Is Nagios better than Grafana and is it possible to use Grafana with Nagios?

Any better suggestions would really help me as well.


texts: Query Store Force Plan feature doesn't work

texts:
Query Store Force plan feature doesn't appear to be enforcing the plan.

I'm aware of Query Store - Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query optimizer may continue to choose incorrect indexes, loop choices etc.

Basically: it doesn't honor my forced plan choice. I've forced many plans and It Just Doesn't Work.

There are 0 failure counts or reasons when I look at sys.query_store_plan force_failure_count.
Extended event query_store_plan_forcing_failed doesn't yield anything. 0 Events.

For example, a plan which was forced on 20.09. Only 1 compilation happened to use forced plan.

The plans differ wildly, one using Hash Match join with INDEX 1, the other using Loop Join with INDEX 2.

Version: Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)

What am I missing here?


texts: ibdata1 back to it's original size after deleting it

texts:
I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size.

  As you want to reclaim the space from ibdata1 you actually have to
  delete the file:

  Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
  Drop all databases except the above 2 databases
  Stop mysql
  Delete ibdata1 and ib_log files
  Start mysql
  Restore from dump

Source


texts:
Today I test the new feature about "replication features" of PostgreSQL9.1,
First I have installed the Hot-standby of PostgreSQL9.1 successfully. It is not a problem。
What I want to test is that I want to use command "pg_ctl start promote " to active  standby node
instead of creating a trigger file 。 Yes, after I execute the command, the standby node then becomes
primary and allow write/read operations。  But the initial primary node can not switchover to the
standby role automatically. I am not sure If PostgreSQL9.1 is able to use "pg_ctl promote" do  switchover
between primary and standby node 。Can any body explain this ?


texts: Integration Services License requirements

texts:
Not sure this is something which people will know on here are not.

We currently have a BI setup where we have Integration services on one SQL instance and the data warehouse database on a separate SQL Instance.

My question is, as no users physically connect to the Integration services SQL instance and it is purely used to execute package from the integrations service catalog via SQL agent, do we need to License every user i.e. Cal's for that SQL instance.

Basically i'm wanting to know if I could utilize SQL 2016 for an integration services instance and use features like Json manipulation etc and then load this data into the 2014 version instance...but what level of licensing will I need?


texts: Null Or Multiple Parameters When Querying

texts:
I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as

@Values VARCHAR(10) = NULL

OR

@Values VARCHAR(10) = '1,5,10'

IF OBJECT_ID('TEMPDB..#Parameters') IS NOT NULL DROP TABLE #Parameters
CREATE TABLE #Parameters ( Id INT )

INSERT INTO #Parameters
SELECT
    [Value]
FROM
    FunctionThatReturnsStringToTable(',',@Values)

I need to figure out a way to make this work in SQL like so

SELECT
   SomeColumns
FROM
   TABLE
WHERE
   COLUMN = IIF(@Values IS NULL, COLUMN, (SELECT Id FROM #Parameters)

Which I know will not work. Is there any other way around this?


texts: Does the order of columns in a table's definition matter?

texts:
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an element of good style.

That is clear.

What is not clear, however, is whether the logical ordering of columns in a table has any impact on their physical ordering at the storage layer, or if it has any other impact that one might care about.

Apart from the impact on style, does column order ever matter?

There is a question on Stack Overflow about this, but it lacks an authoritative answer.


texts: Finding code tab's associated schema in MySQL Workbench

texts:
I have a single-tenant setup of MySQL databases; i.e. a more or less identical schema instance for each client.

When I'm tasked with modifying the code of stored routines, I use MySQL Workbench. After some time I usually find myself having code tabs with similar or identical code for several distinct database schemas.

How do I know to what schema a certain stored routine tab belongs?


texts: What is the easiest way to start a mongodb copy server?

texts: Our mongodb server is running on AWS EC2 - it is using up a lot of the cpus of this server - which is also our Apache server - How Can I easily migrate the mongodb to another EC2 dedicated server?


texts: Mongoimport monitoring successfully entered row/data

texts:
How to monitor mongoimport hass successfully saved data into our database? Let's say I have 20,000 rows of CSV data. When I press the button to start mongoimport, the user will see the progress.

Is there a way to see each row successfully inserted to the DB, visually?

mongoimport --db dbname --collection collectionname  --type csv --headerline --file filename --maintainInsertionOrder


texts: Script to see running jobs in SQL Server with Job Start Time

texts:
how to write script to see running jobs in SQL Server with Job Start Time?

SELECT sj.name,
 sja.run_requested_date,
 CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj
ON sja.job_id = sj.job_id
WHERE sja.run_requested_date IS NOT NULL
ORDER BY sja.run_requested_date desc;


texts: INSERT INTO... SELECT over a LinkedServer in smaller batches

texts:
I've written the following code to move data from one database table, to another (over a LinkedServer);

SET NOCOUNT ON

DECLARE @ApplicationURL varchar(50),
    @LinkedServer nvarchar(50),
    @DatabaseName varchar(50)

SET @ApplicationURL = 'Test'
SET @LinkedServer = 'ABC123'
SET @DatabaseName = 'Test'

/*--------------------------------------------------------------------
    Table:  Notifications
--------------------------------------------------------------------*/
EXECUTE (
'INSERT INTO dbo.Notifications
(
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    [IsActive],
    [UserId],
    [SourceNotificationId]
)
SELECT
    [Subject],
    [Body],
    [PriorityId],
    [StartDate],
    [EndDate],
    n.[IsActive],
    [DS.DataMigration].[Migration].[ufnGetNewUserId](n.[UserId]),
    [NotificationId]

FROM ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Notifications] n
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Users] u ON n.UserId = u.UserId
    JOIN ['+ @LinkedServer +'].['+@DatabaseName+'].[dbo].[Applications] a ON u.ApplicationId = a.ApplicationId
WHERE A.ApplicationURL = ''' + @ApplicationURL + ''';')

This works as expected, however some tables are likely to have over a million rows, and I do not want to try and move them all at the same time, in one transaction. In my intestigation, I found this post; Breaking a big insert query into smaller ones

I would like to use this method, but I've been unable to find out how to impliment this with Dynamic SQL. Has anybody done something like this before?

Thanks!


texts:
So I have a blog and some of my entries are set to "members", but I want to set them to "everyone".  I originally thought it was only a few entries and so I thought to change them manually using PhpMyAdmin, but quickly realized I had more than I thought.

When using PhpMyAdmin I noticed when making the change manually (point and click) this was the command it was using (see below).  I'd like to repeat this but for ALL entries.

Is there a wild card I can use for the entry_id value?

UPDATE `database_name`.`blog_entry`
SET `allow_view_entry` = 'everyone'
WHERE `blog_entry`.`entry_id` = 4624;


texts: Using a join operation with conditionals

texts:
I have two tables that I'm working with in my database. I am using SQL Server 2014.

One is 'versions', that contains version_id, version_number,
start_date, end_date and server_id.
The other is 'features', which contains feature_id, feature_info and
version_id. version_id is a foreign key to the version_id in
'versions', where it is a PK.

I'm trying to get something like:

"X, Y, Z are in version 1.0.
"....... are in version 1.2.

and then if a version does not have a new feature:

"Version 2.2 has no new features."

Right now, I don't have any versions without features, but I plan on adding some to test this out.

Here is the output I'm currently getting with my code (I don't care about the singular/plural issues, i.e. 'Version 1.7 has email client'):

Version 1.0       has web functionality, Version 1.1       has user interface, Version 1.7       has email client, Version 1.7       has voice control functionality, Version 2.0       has bug fixes.

There happens to be 7 spaces in between commas. This is less important, but would still be nice to be able to fix.

*Also, if I wanted to turn this into a stored procedure, would I just add, 'create procedure x', followed by 'begin' and 'as' after my variable declaration, and then an 'end' at the end of the code?

DECLARE @result varchar(1000)

SET @result = ''

BEGIN TRY

    SELECT @result = @result + 'Version ' + version_number + 'has ' +      feature_info + ', '
    FROM features join versions on versions.version_id = features.version_id
    SELECT substring(@result, 0, len(@result) - 1)

END TRY

BEGIN CATCH
    PRINT 'THere was an error getting feature info'
END CATCH


texts: SQL Server 2005 query with multiple conditions, some indexed, some not

texts:
I have a table with 50 million rows. It is indexed on zip. A query

Select *
from table
where zip = '12345'

returns 20,000 rows in about 2 seconds

When I add an additional condition it jumps to 15 minutes...

Select *
from table
where zip = '12345'
  and otherField = 'Y'

My expectation is SQL would use the index, identify the 20,000 rows and then scan all for that non indexed condition. Maybe a few seconds at max. Instead it seems to be scanning the whole database table.

I cannot add another index... There are 150+ columns we might test, most have Y/N values.

Is there a better way to write this type of query?

Could I have a setting wrong in the server config?

Thank you!


texts: Find time of execution of old query in Oracle

texts:
I have a delete query which was run on a table in Oracle 11g Enterprise Edition about 2 months back. I need to find the exact time of when the query was executed.

I found the query in the DBA_HIST_SQLTEXT view. But I do not see any date/time field in the table to find the exact time of execution. Is there any way that this can be found out?

I also tried to extract the same from AWR report. But snapshots are available only till 1.5 months back.


texts: postgres - c  =  not working for postgres 11 in docker

texts:
I'm running a Postgres inside a docker. I want to change the default config of Postgres so I'm running :

docker container run -d postgres -c max_connections=200 -c shared_buffers = 1GB -c effective_cache_size=3GB -c maintenance_work_mem=256MB -c checkpoint_completion_target=0.7 -c wal_buffers=16MB

But when I'm connecting to Postgres running:

 docker exec -it container_name psql

And then the result of :

SHOW max_connections;

is

 max_connections
-----------------
 100
(1 row)

And it's not just max_connections. None of the parameters are changed. And I don't know what is the problem with what I'm doing?

Update: I already have a running docker container and I want it to apply these parameters to it without needing to restart it.


texts: Move new data records automatically to another database in oracle xe

texts:
What's the best way to move all the data from a table on my local oracle database to an identical table on another pc? I will have to transfer the data to the second database as soon as it's written to the local one. The local table should not contain any data, unless it's not possible to insert it to the remote database. If the connection is lost, all new datasets must be stored in the local table.
I tried to solve this by using a trigger, but it didn't work as expected. It works just fine if the remote database connection is valid, but it performs an entire rollback (including the original insert) if the connection is lost. Because of that, the data isnt even inserted to the local database. Another huge problem is that it takes about 40 seconds every time to return ORA-12170 (Connection timeout). Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time?

create or replace TRIGGER DATA_TO_SERVER
AFTER INSERT ON LOCAL_TABLE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SAVEPOINT sp;

  INSERT INTO SERVER.SERVER_TABLE@SERVER_LINK
  SELECT *
  FROM LOCAL_TABLE;

  DELETE
  FROM LOCAL_TABLE
  WHERE ID IS NOT NULL;

COMMIT;

EXCEPTION WHEN OTHERS THEN
    ROLLBACK to sp;
    RAISE;

END;


texts: GCP Cloudsql Mysql Replica unresponsive after mass delete on Master

texts:
We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both primary and non-clustered indexes... It's a row-based replication with automatic disk increase on both instances... It's not a HA configuration so it's not a failover replica...
What we're trying to do... We're trying to purge the master table back to N number of days... This is done for multiple reasons so let's say this is a client requirement...
What's the issue... Whenever we're purging the master table it just stalls the replica, it deletes a certain number of rows on the replica and then just passes out... The number of records in a single purge is around 5 million rows... The time the purge starts on the master, the lag starts... It's a totally repeatable issue... we know it's caused because it's a row-based, sequential replication so
What we've tried so far...

Increasing the size of the replica, we've given it 104 GB RAM but
the lag doesn't go...
Restarting replica
RESET SLAVE
Trying enabling parallel replication https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication... every single time I tried this it failed with an 'Unknown error occurred'...
Trying setting it to a Statement-based replication by the SET binlog_format="STATEMENT" command but the "root" user doesn't have the privilege and gets an 'access denied' error...

Now the question...
what am I missing in my:

explanation
mysql configuration
method

Thanks


texts: How to get a new pid file of Mysql and restart Mysql Successfully?

texts:
Due to lost of pid file of my server, I can't stop mysql using normal commands like

service mysql stop

which will show

ERROR! MySQL server PID file could not be found!

So I try to stop all mysql processes and restart mysql mannually

I've searched the Internet and found one solution, but it doesn't work for me.

I run

ps aux | grep mysql

and the result shows

root      6490  0.0  0.2 112720  2332 pts/0    S+   03:48   0:00 grep --color=auto mysql

So I type

kill -9 6490

but the result is

-bash: kill: (6490) - No such process

It seems every time I try to kill it, the pid changes.

How can I really stop all mysql processes? Then be able to get a new pid file? New to linux and mysql, thank you in advance!

Update:

Thanks to Henryk Gerlach's answer, it seems I have shut down all Mysql processes, but after I restart my server the pid file doesn't auto-generate. How should I do to really make my Mysql back to normal??


texts: jdbc+mysql: "Deadlock found when trying to get lock; try restarting transaction"

texts:
After recovering from a disk-full problem, I started getting these errors in my logs:

java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1684)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at com.dappit.Dapper.adminer.pixelMaster.storage.RemotePixelStorage.flushBufferToDb(RemotePixelStorage.java:120)
    ...

The Java code (RemotePixelStorage.java:120):

(119) connection.setAutoCommit(false);
(120) int[] returnCodes = pstmt.executeBatch();
(121) connection.setAutoCommit(true);
(122) connection.commit();

Each statement is a call to the stored procedure add_pixel, which adds an element to a table and removes older elements if necessary.

What does this jdbc exception mean? How can I overcome this issue? Should I simply try to commit a few times with a catch block?


texts: mysqlpump --set-gtid-purged=OFF still adds @SESSION.SQL_LOG_BIN=0;

texts:
Whenever I add --set-gtid-purged=OFF, it still adds @@SESSION.SQL_LOG_BIN=0; to the output.

According to the documentation, shouldn't it get removed?

--set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.

Dump created by MySQL pump utility, version: 8.0.16, Win64 (x86_64)

SET @@SESSION.SQL_LOG_BIN= 0;

Is this correct behavior for this tool?


texts: Get all of the rows involved between tables relationships to delete them

texts:
Continuing with the question Get all of the rows involved between tables relationships, now I have to delete all the rows for those codes. I explain:
I have a SQL Server 2016 with the following tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregation and AggregationChildren represent relations between rows in table Code.

An aggregation will have 1 or more aggregation children. And also, a code in aggregation children could have 0 or more aggregation children.

It is something like this:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

All of the above are Code.Serial values.

All of the Code.CodeId values for those serials are in Aggregation table for Code1, Code2 and Code3.

And, for those serials, Code2, Code3, Code4, Code5, Code6, Code7, and Code8 are in AggregationChildren table.

I need to delete the rows for those serials from Aggregation and AggregationChildren tables.

Using the answer from the previous question, I have tried to do this:

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 130
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

Delete ag from dbo.Aggregation ag
inner join rc
on ag.AggregationId = rc.Id;

But I don't know how to use the DELETE FROM with the rc statement. But I think I only need the Codes that are in Aggregation and AggregationChildren. Maybe I need to modify the With statement to get first the rows in Aggregation table because I have an ON DELETE CASCADE in AggregationChildren table.

How can I get all the codes to delete from Aggregation table?


texts: Identifying rows which don't match a master row

texts:
I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by locationID, have the non-matching rows because they might need maintenance.

I've got a simple EXCEPT query where I compare a table where each row is the configuration from each server; table1 has one row per server with all configuration plus locationID which is a column that tells me which server it is. I compare these all to a table1_master table which has the right settings, but I exclude the locationID since it won't match.

Simple query below:

SELECT everything, but, locationID
FROM table1
EXCEPT
SELECT everything, but, locationID
FROM table1_master

There's only one master row I compare all servers to, and I don't select it's locationID here.

This is an example of the rows I'm comparing. Each has a primary key, a single column varchar and a giant list of that's dozens of columns. I want to compare all columns except LocationID, but I need LocationID to identify the rows.

LocationID             setting    setting    setting     setting
CS02      C            Y           Y         Y           Y
CS03      C            Y           Y         Y           Y
CS06      C            Y           N         Y           Y

In this example say CS02 is my Master record, so since all settings are the same in CS02 and CS03, those rows don't show up, but CS06's does. But in my EXCEPT query, I'm not actually catching LocationID so I don't actually know which row was returned.

This returns the rows I need but NOT the locationID, so I don't know which rows are wrong. Is there any way I can include locationID in the results set while kicking out the matching rows?

The solution I thought of was to make a row for each server in the table1_master table, so each locationID is represented, but they all have the same data other than that. My EXCLUDE query should then return the locationID and my info, but is that the best way to do it?


texts: BCP utility:“Copy direction must be either 'in', 'out' or 'format'”

texts:
I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error:

  Copy direction must be either 'in', 'out' or 'format'.

The command did not change, only the location of the job and version of the instance.

bcp "SELECT * FROM [dbo].[Table]" queryout "\\filerepository\ftp\text_file.txt" -T -c -t"|" -S"ServerName" -d"MyDB"

Also, I ran this locally using the 2012 bcp.exe and received the same error. When I compare the books online article for the bcp utility, I do not see any changes which would affect the syntax of this command.

I would appreciate any advice.

Updated: The PowerShell Script

$ErrorActionPreference = "stop"
$serverInstance = "Server"
$databaseName = "DB"

# construct query
$query = @"
SELECT *
FROM [dbo].[Table]
"@

$query = $query -replace "`n|`r|`t"," "

# output results
$extractPath = "\\filerepository\ftp\"
$fileName = "CTBRI_$(Get-Date -Format "yyyy-MM-dd")_text_file.txt"
$path = Join-Path $extractPath $fileName;

$cmd = "`"$query`" queryout `"$path`" -T -c -t`"|`" -S`"$serverInstance`" -d`"$databaseName`""

bcp $cmd

if($LASTEXITCODE -ne 0)
{
    throw "bcp did not exit with a success code of 0. Hopefully there are more detailed messages above."
}


texts: Enable sysadmin user to restart SQL server from management studio

texts:
I have SQL server login with sysadmin role, but I cannot stop and start SQL server when I connect from remote management studio, just from local server management studio. How can I enable it?
I use windows server 2008r2, SQL server 2008r2.


texts:
Our development organization has created an application employing FILESTREAM, which through the pilot has been incorporated into a schema within our Data Warehouse Staging database.  Going to production, the development and BI teams have determined that they want it separated out into a separate database, and they'd like to separate it in the current pilot environment (DEV).

How can I best move (or at least copy) the existing FILESTREAM data from the current database into a new one?

To clarify: I'm not looking to move the database, but to pull the FILESTREAM (and the entire schema) out of one database and put it into another.


texts: How to get job start time inside of a TSQL agent job step?

texts:
Is there a good way to query the job start time inside of a given sql agent job step (TSQL)?  This will help with step 3 in the following scenario.
Job Foo (Run Daily @ 11:30pm)
Step1 - StartTime = Saturday 11:30pm TSQL - (15 minute duration)
Step2 - StartTime = Saturday 11:45pm TSQL - (30 minute duration)
Step3 - StartTime = Sunday   12:15am TSQL - Exit early / don't run unless Foo job was started on a Saturday.


texts:
I have two tables t_core_blueprints,t_ci.

CREATE TABLE `t_core_blueprints` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

  `last_modified_on` bigint(20) NOT NULL,
  `parent_blueprint` int(11) DEFAULT NULL,
  `icon` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `is_ci_part` bit(1) DEFAULT NULL,
  `archive` bit(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_ci_part` (`is_ci_part`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `t_ci` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_unicode_ci,
  `record_id` text COLLATE utf8_unicode_ci,
  `last_modified_on` bigint(20) DEFAULT NULL,
  `archive` bit(1) DEFAULT NULL,
  `blueprint_id` int(11) DEFAULT NULL,
  `license_group` int(11) DEFAULT NULL,
  `s39` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK362C11874EFF37` (`blueprint_id`),
  KEY `record_id_index` (`record_id`(64)) USING BTREE,
  KEY `processed_remote_ci` (`archive`,`s39`,`blueprint_id`),
  KEY `lastModified` (`last_modified_on`),
  KEY `Test1` (`archive`,`last_modified_on`),
  KEY `Test2` (`blueprint_id`,`archive`,`last_modified_on`),
  CONSTRAINT `FK362C11874EFF37` FOREIGN KEY (`blueprint_id`) REFERENCES `t_core_blueprints` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I am running two queries

SELECT tci0_.id AS col_0_0
FROM t_ci tci0_
CROSS JOIN t_core_blueprints tmblueprin1_
WHERE tci0_.blueprint_id=tmblueprin1_.id
  AND tmblueprin1_.is_ci_part=0
  AND tci0_.archive=0;

EXPLAIN Plan for this query:-

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tci0_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,AssetID,lastModified,Test1,Test2
          key: processed_remote_ci
      key_len: 2
          ref: const
         rows: 889058
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmblueprin1_
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,is_ci_part
          key: PRIMARY
      key_len: 4
          ref: emdb_1547105779479.tci0_.blueprint_id
         rows: 1
     filtered: 65.71
        Extra: Using where

SELECT tci0_.id AS col_0_0
FROM t_ci tci0_
CROSS JOIN t_core_blueprints tmblueprin1_
WHERE tci0_.blueprint_id=tmblueprin1_.id
  AND tmblueprin1_.is_ci_part=0
  AND tci0_.archive=0
  AND tmblueprin1_.name LIKE '%%';

EXPLAIN Plan is:-

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmblueprin1_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,is_ci_part
          key: is_ci_part
      key_len: 2
          ref: const
         rows: 69
     filtered: 11.11
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tci0_
   partitions: NULL
         type: ref
possible_keys: PRIMARY,AssetID,processed_remote_ci,lastModified,Test1,Test2
          key: Test2
      key_len: 7
          ref: emdb_1547105779479.tmblueprin1_.id,const
         rows: 93585
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

I am not able to understand just adding a condition tmblueprin1_.name LIKE '%%' in the second query made its execution faster. Can you explain what is happening here?


texts: New to databases - does my ERD reflect good database design?

texts:
I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields:

{filePath,artist,title,album,track_number,length,date,artworkUrl}

Since I'm trying to use good DB design techniques, I've now created a DB with three tables. One for the audio file itself, one for the artists, and one for the albums:

I'd really appreciate it if some more knowledgeable people could evaluate my design and, give me some pointers. Thanks in advance!


texts: Oracle 11GR2 - When will datafile autoextend?

texts:
Let's say I have a single datafile, DF1. After DF1 becomes 50% full, I add a new datafile DF2 of equal size. Both datafiles have autoextend set to ON. When file DF1 becomes full (and therefore, DF2 will be around 50% full), will it autoextend DF1 despite having free space in DF2? Or will it instead leave DF1 as is (100% full) and start allocating extents from DF2 only?

Here's a visualization

When I add new datafile:
DF1: xxxxxxx00000000
DF2: 000000000000000

After DF1 becomes full:
DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxx00000000

Will it start to do this?

DF1: xxxxxxxxxxxxxxxxxxx
DF2: xxxxxxxxxxx0000

Or will it start to do this?

DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxxxxxxxxxx


texts: What does "*password------------" mean in a profile deadlock report?


texts: Error trying a JSON_VALUE example in DB2 11.1.4.4

texts:
I'am having a similar problem as described in this question.
Error trying a manual's example in DB2 11.1.4.4
DB2 Version: DB2 LUW 11.1.4.4
When running the following sample from the DB2 LUW 11.1 Manual
VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER));
Im getting the error:

SQL0171N The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.JSON_VALUE" is incorrect. Parameter name:

Upon checking the Database Code Set is IBM-1252. Supposedly, I should have UTF-8 code set for this to work. I can not change this code-set.
My question is, given my predicament, is there a way to cast so that I can make use of this functionality?


texts:
Let's imagine a table structured like this:

Bucket
Red
Blue
Green

First
1
3
4

Second
6
5
2

What I'm trying to achieve is based on the values within each bucket, I'd like to generate another set of fields with the highest ranking, second highest ranking, and third highest ranking colors (assume there are more than three colors as well). We are limiting to top 3.
Essentially, what my final output should look like is this:

Bucket
Red
Blue
Green
Rank 1
Rank 2
Rank 3

First
1
3
4
Green
Blue
Red

Second
6
5
2
Red
Blue
Green

Hoping this isn't a redundant question.


texts: Update column from Base64 to string (natively in SQL)

texts:
I've been trying to figure this out for two days now and would really appreciate some help. I've imported data from a csv where one field contained html data encoded in base64.

The idea is to loop over every row and run FROM_BASE64 on it.

How do I structure a query that:

Loops over all lines
Calls FROM_BASE64 for each line
Runs UPDATE (or similar functionality) on that same row and column

I'm running MariaDB (MySQL equivalent).


texts: tuning huge delete operation on sql server table

texts:
I am performing a delete operation on very large sql server table based on query as discussed below.

delete db.st_table_1
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

stg_table and stg_table_1 doesn't have indexes on service_date.

both of these tables are loaded with million rows of data and delete operation is taking lot of time. Requesting your suggestion to improve the performance of this query.

I referred to strategy described in below question but couldn't understand how to implement it.

How to delete large amount of data in sql server without data loss?

requesting your kind suggestion on this.

Update:

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

how about if go with above logic to delete the millions of records. any advantages and disadvantages with that.


texts: truncating transaction log for simple recovery databases

texts: We have a couple of databases which are in simple recovery model. When the weekly rebuild job runs, the tlog drive is becoming full every time. How do I control the size tlog from every growing?Because these are in simple recover - should I be resizing the tlog autogrowth in chunks of 512mb instead of 10%. Would that help?


texts: Mongo Super User won't connect to another database other than admin

texts:
Why is that?

$ mongo admin -u admin -p password --host 192.168.99.100
MongoDB shell version: 3.2.9
connecting to: 192.168.99.100:27017/admin
> show users
{
    "_id" : "admin.admin",
    "user" : "admin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readWriteAnyDatabase",
            "db" : "admin"
        }
    ]
}
> db^C
bye
cesco@laptop: ~/code/go/src/bitbucket.org/cescoferraro/cluster/containers on develop [!$]
$ mongo iot -u admin -p password --host 192.168.99.100
MongoDB shell version: 3.2.9
connecting to: 192.168.99.100:27017/iot
2016-09-25T22:22:36.829-0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1441:20
@(auth):6:1
@(auth):1:2

exception: login failed


texts: metadata could not be determined because stored procedure contains dynamic SQL

texts:
I have a stored procedure that contains some dynamic SQL. I am trying to execute the stored procedure using OPENROWSET but am getting the error:

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;' in procedure 'nachoTest'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

My real stored procedure is a bit more complex but this is a working example of the error:
The stored procedure:
CREATE PROCEDURE nachoTest  @table_name NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @row_count_table INT
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = N'SELECT @row_count_out = COUNT(*) FROM ' + @table_name
    EXEC sp_executesql @SQL, N'@row_count_out INT OUTPUT', @row_count_out = @row_count_table OUTPUT;

    SELECT @row_count_table AS row_count, @table_name AS table_name
END
GO

Calling it with OPENROWSET
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC dbo.nachoTest ''e_MFA_All_Tokens''')

I have read a bit online but cannot figure out how to use WITH RESULT SETS to fix the code. Ideally I'd like to fix the stored procedure but if I have to change the OPENROWSET call then that is okay too.


texts: SSIS 2012 Project Connection Manager does not expose ServerName property for configuration in SSMS 2012

texts:
We have deployed one SSIS 2012 project with two ADO.Net Project Connection Managers. (There are five packages within the project.)

The project has a related Environment, with a variable containing a server name. I want to map the variable to the project connection manager ServerName property.

When I right-click on the SSIS project node in SSMS 2012, launching the Configure dialog box, I can locate the Connection Manager, and highlight it to see its properties, but ServerName is not visible in the Properties list.

Usually, the ServerName property is listed under the RetainSameConnection property. It's visible on other Project Connection Managers in the same project.

Why would this property be hidden, just for one Connection Manager?


texts: Why does a query on a sparse index perform much faster than a query on a partial index in MongoDB?

texts:
I created a sparse index in my MongoDB Atlas like so:

db.detection_events.createIndex({"emp_id": 1}, {sparse: true})

When I performed this operation:

db.detection_events.count({"emp_id": {$exists: true}})

I got the result extremely quickly. Upon readon the docs for sparse indeces, MongoDB recommends to use partial indeces instead. I created a partial index like so:

db.detection_events.createIndex({"emp_id": 1}, {partialFilterExpression: {emp_id: true}})

And the same query takes a long time to execute. I got the partial index code from here.

Why does the partial index have a much higher query time than the sparse one? Is it because I created the index incorrectly?


texts: Stored procedure to set value to a column must accept returned values from functions

texts:
I created a very simple table in Data Mart to store ETL diagnostic information:

CREATE TABLE [internal].[etl_status]
(
    [property_name] VARCHAR(100) NOT NULL PRIMARY KEY,
    [property_value] VARCHAR(MAX) NULL
)

Then I created a stored procedure to handle insertions/modifies into this table. Besides convenience, I had to do this because Microsoft are yet to add T-SQL statement activities to Azure data factory.

Problem is, I can't figure how to make the store procedure accept T-SQL function as parameters. In fact I believe it might not be possible. So here is my little workaround:

CREATE PROCEDURE [internal].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
AS

DECLARE @query AS VARCHAR(MAX)

-- Case statement to handle functions passed as propertyValue parameter
-- NOT IDEAL, see solution by Scott Hodgin below
SET @propertyValue = CASE @propertyValue
    WHEN 'GETDATE()' THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME2(2), GETUTCDATE()))
    ELSE @propertyValue
    END

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'

EXEC(@query)

This works as expected. But with the current solution I would need to have a CASE statement for each function I would like to handle.

EDIT: The suggestion below by Scott Hodgin is an acceptable solution. However I added a default value of FALSE to @propertyValueIsFunction as follows: @propertyValueIsFunction BIT = FALSE.


texts: MySQL Error: FUNCTION LEVENSHTEIN already exists

texts:
I've got an ExpressionEngine database and I exported a couple of tables from it, then dropped those tables. When I try to re-import the tables in PHPMyAdmin, I get this error:

SQL query:

-- -- Database: `my_db` -- DELIMITER $$ -- -- Functions -- CREATE DEFINER=`my_username`@`%` FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS int(11) DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; DECLARE s1_char CHAR; DECLARE cv0, cv1 VARBINARY(256); SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0; IF s1 = s2 THEN RETURN 0; ELSEIF s1_len = 0 THEN RETURN s2_len; ELSEIF s2_len = 0 THEN RETURN s1_len; ELSE WHILE j <= s2_len DO SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; END WHILE; WHILE i <= s1_len DO SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1; WHILE j <= s2_len DO SET c = c + 1; IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF; SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; IF c > c_temp THEN SET c = [...]

MySQL said: Documentation

#1304 - FUNCTION LEVENSHTEIN already exists

I get this error even if I drop all tables from the DB and try to import anything. The only way I can get the error to go away is to totally delete the database and re-create it.

What's causing that error and how can I stop it from happening?


texts: PostgreSQL equivalent of MySQL query variables?

texts:
Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL:

setting variables in MySQL like

set @aintconst = -333
set @arealconst = -9.999

It seems not.
Assigning variables from SELECT queries and using those variables subsequently in my SQL like:

 select @pfID := id from platform where bios like '%INTEL%'
 select @clientID := id from client where platformID = @pfID

I'd be very grateful for pointers, especially on (2).


texts: Best way to merge tightly coupled SQL Server Instances

texts:
I´m looking for some recommendations or best practice for the following scenario:

I need to merge several SQL Server 2014 instances into one (we are switching from many instances to a single clustered instance).
The scope of this merge is of about 10 servers, and 400 databases.
The source instances are very tightly coupled. There are many linked servers, and a lot of stored procedures, views, and functions that use them.
Some SPs also perform operations in the file systems of other servers in the scope of this migration.
As far as I know, we may be forced to change some DB names because we would have name collisions between databases on the destination instance.

I have scripted all DB objects so I can do some text search to find all occurrences of queries that use a given linked server.

So far I believe my options are:

1- The long one. Change the queries that use linked servers so it will query to the same instance (just to another DB in the instance).

2- A short one. We believe we can create DNS aliases that would make the old machine names point to the new clustered instance.

Then we would create several linked servers in the clustered instance pointing to the same instance. The linked servers would have the same names as in the source servers. This should make the old SPs, functions, and views work without any change (except when we need to change a DB name).

Does anyone know a better option or have any recommendations?


texts:
I'm writing my own python wrapper on top of official mysqldb connector. I'm trying to design method that would dynamically extract row data knowing KEY ID.

 SELECT * FROM cameras;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
|        3 | Nikon                |         1 | 10x10      |
+----------+----------------------+-----------+------------+

knowing table name ("cameras" in example above), and primaryID key integer value (but not primaryID column name), how can i get a row ?

if I had primary key column name, than i could construct:

mysql> SELECT * FROM cameras WHERE cameraID=2;

But How can I ask for it without column name ? Is there something like "WHERE ID=#" or "WHERE 'COLUMN KEY'=#"?

Edit:
I was trying to create multistatement:

mysql> SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
+----------+
| columnID |
+----------+
| cameraID |
+----------+

but mysql did not allow me to use variable as column name directly:

mysql> SELECT * FROM cameras WHERE ', @columnID, '= '2';
Empty set (0.00 sec)


texts: How can I "buffered" mine Oracle logs using LogMiner with 'committed data only' option properly?

texts:
Using Oracle LogMiner with committed data only flag,
According to the Oracle documentation, all the committed records with the same commit scn, will be joined together and will appear in a sequence.

I want to scan the log, but not all in a one session, because it will take long time to load, so I'm getting some number (say 1000) and read this amount each time with committed only flag.

My questions is: when I give a sequence between start scn to end scn like this:

DBMS_LOGMNR.START_LOGMNR (
startScn=>start_value,
endScn=>end_value,
options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+COMMITTED_DATA_ONLY+CONTINUOUS_MINE+DBMS_LOGMNR.NO_ROWID_IN_STMT
);

Lets say I have a transaction which committed in the given range, but started in some scn before the range, does it still should give all the committed rows together, including the starting scn, or just all the committed rows with the same commit scn, that are in the given range?

If the 2nd is correct, Do I get the pre-range committed records at all? since it still uncommitted in the previous range, and now it is committed, so when I have a long transactions it look like a flaw in the mechanism.

Thanks for any thought


texts: Is there a difference between granting SELECT to a user and adding them to the db_datareader role?

texts:
I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database.  I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.

But that got me thinking, what is the difference (if any) between granting a user SELECT permission at a database level, or adding them to the db_datareader role?  And is there any best practice one way or the other?


texts:
I've got table of scores where each entry corresponds to a particular (student, subject) pair.

CREATE TABLE score (
  id serial PRIMARY KEY,
  value integer NOT NULL,
  subject_id integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s1_id FOREIGN KEY (subject_id) REFERENCES subject (id),
  CONSTRAINT s2_id FOREIGN KEY (student_id) REFERENCES student (id),
);

I want to pick the top 5 subjects with highest overall scores, and then compute the average score of each student across those 5 subjects. Some students may not have entries for some subjects. Those values would be given a default score.

Here's what I have:

SELECT student_id, AVG(COALESCE(score.value, default_value)) FROM
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
LEFT JOIN score ON score_sort.subject_id = score.subject_id
GROUP BY student_id

The inner query works correctly to select the top 5. But the LEFT JOIN in outer query does not select the rows where a student does not have a score. What am I doing wrong here ?


texts: What is the current recommended way of changing the collation of an Azure SQL database?

texts: We would like to switch to using the Latin1_General_100_CI_AI_SC_UTF8 collation in our Azure SQL database. Altering the collation of individual columns aside, if this were on-premise SQL Server 2019, changing the collation of the database would just be a matter of running ALTER DATABASE CURRENT COLLATE Latin1_General_100_CI_AI_SC_UTF8 (with the database in single-user mode). I understand that ALTER DATABASE with COLLATE is not supported on Azure SQL, the collation can only be set when the database is created. I found this workaround from 2 years ago, but exporting the entire database to on-premise and importing it back to Azure seems impractical for a large production database. Are there any other ways of doing it as of now?


texts: One of my databases in my DR is fully corrupted ( I have checked running validate database using RMAN). Now to recover all the data files which are corrupted I need to copy the data files from my primary to the DR site which is taking way too long, what are the other solutions to resolve corruption in my DR?


texts: Is it more efficient to subtract a value from a date or use DATEADD with a negative value

texts:
I have a stored procedure that I need to make more efficient. It currently looks like

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < DATEADD(DAY, -1 * @MaxAgeInDays, GETDATE())

    IF @@ROWCOUNT > 0
        GOTO delete_more

The first step was to extract some variables

DECLARE @Now DATETIME = GETDATE()
DECLARE @EarliestDate DATETIME = DATEADD(DAY, -1 * @MaxAgeInDays, @Now)

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < @EarliestDate

    IF @@ROWCOUNT > 0
        GOTO delete_more

But then I thought that perhaps it might be faster if I change @EarliestDate to

DECLARE @EarliestDate DATETIME = @Now - @MaxAgeInDays

Which one would be faster or more clear to both the database, and a dba?


texts: db2 performance issue - large number of IOPS how to determine problem?


texts:
I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following

Users

+-----+------------+---------------------------------------------------------------+
| uid | timestamp  | profile_img                                                   |
+-----+------------+---------------------------------------------------------------+
|   1 | 1322858590 | /photos/356a192b7b/profile.jpg                                |
|   2 |   13700000 | /photos/356a192b7/profile.jpg                                 |
+-----+------------+---------------------------------------------------------------+

Friends

+------+------+
| uid1 | uid2 |
+------+------+
|    1 |    2 |
+------+------+

now I want to select all online friends (online = timestamp > current time - 30 seconds)

My try to create a query to select online friends is

SELECT  f.uid1 as friend1,
    f.uid2 as friend2,
    u1.profile_img profile_img1,
    u2.profile_img profile_img2
FROM Friends f
INNER JOIN Users u1 ON u1.uid = f.uid1
INNER JOIN Users u2 ON u2.uid = f.uid2
WHERE
    f.uid1 = 1 OR
    f.uid2 = 1;

I have no idea how to compose the timestamp check, considering that

friend1 can be me, but it can also be my friend.
friend2 can be me, but it can also be my friend.

Is there a solution for this or should I think a bit more about database schema?


texts: Mysql: Stored procedures vs. inline SQL?

texts:
We are evaluating whether we should write stored procedure OR write inline queries. I read this link to get an opinion.

I want to know pros and cons from mysql perspective as the above question is for sql-server.