texts: MySQL version appears to be inconsistent?

texts:
I have recently upgraded mysqld (percona server) from 5.5.60-38.12 to 5.5.62-38.14. This is on Debian, and the upgrade was done like this:

# apt-get update
# apt-get upgrade

I am surprised to find that the version is reported differently internally from what I get on the command line:

db3 root = mysql -V
mysql  Ver 14.14 Distrib 5.5.62-38.14, for debian-linux-gnu (x86_64) using readline 5.1
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.60-38.12-log |
+------------------+
1 row in set (0.00 sec)

Is this normal? Or do I need to do something to fix this issue?

Edit: I've allso checked the installed packages:

db3 root = dpkg -l | grep percona
ii  libperconaserverclient18        5.5.62-rel38.14-1.stretch         amd64        Percona Server database client library
ii  percona-server-client-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database client binaries
ii  percona-server-common-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database common files
ii  percona-server-server-5.5       5.5.62-rel38.14-1.stretch         amd64        Percona Server database server binaries
ii  percona-toolkit                 3.1.0-2.stretch                   amd64        Advanced MySQL and system command-line tools
ii  percona-xtrabackup              2.3.10-1.stretch                  amd64        Open source backup tool for InnoDB and XtraDB


texts: What is the proper configuration for max open connection, max idle connection and max connection life postgreSQL?

texts:
We have an app that serves small to moderate kinds of traffic.
our server spec:

6 node of micro/small aws ec2 for client
1 db.r5.2xlarge for writer
1 db.r5.large for reader

our app/client setting:
db.SetMaxOpenConnections(25)
db.SetMaxIdleConnections(10)
db.SetConnMaxLifetime(5 * time.Second)

so the question is:

is this app setting good enough to handle sudden high traffic?
yesterday we have(370k active connection), and the DB wasn't able to withstand the traffic, the CPU is increasing rapidly and we have to spin another read cluster to able to handle the traffic. but, the funny thing is, if I look into DB connection count it only has 133 max, while the capacity for DB.r5.large is 1600 connection(source. so why can't handle the traffic with such a low connection to DB??
there is a strange event during this traffic, 'Read replica has fallen behind the master too much. Restarting Postgres.' is this because we had different specs between writer and reader?


texts: Recovering logical space from a tablespace

texts:
I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space.

A week ago I queried user_tablespaces and dba_data_files and noticed it had 20% of available logical space. I then procedeed with a cleanup and removed many records from tables in this tablespace. We were expecting to see a big increase in available space. Unfortunately when I queried the views I noticed the available space was now 20.5%.

Could this be due to data fragmentation? Can we "defrag" the tablespace somehow and recover the lost space? Or do we need to recreate the tablespace from scratch?


texts: One-directional database replication without delete on target database?

texts:
I have two databases: A small one where data is permanently added and a big one where the data is copied from the small one and is supposed to be stored long term. The small database deletes old data if the database is growing to big. But the big database is supposed to keep this data (that's its purpose).
Is there a way to achieve this by transactional replication?
(I'm not interested in a SQL-Statement solution and regularly calling a job. I know how to do that.)
I've read some stuff about one-directional replication, snapshot replication, transactional replication, and merge replication but I can't figure out if one of those can do what I need. The scenario is not that exotic, isn't it?
I just need someone to point me the right direction.
Any help would be appreciated.


texts: SQL join query to show rows with non-existent rows in one table

texts:
I am trying to get some reporting done for employee time records.

We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of work they've performed and is stored in the Time_Entry table.

Example setup with SQL Fiddle: http://sqlfiddle.com/#!3/e3806/7

The end result I'm going for is a table which shows ALL the Members in a column list and then will show their sum hours for the date queried in the other columns.

The problem seems to be that if there is no row in the Time_Entry table for a particular member, there is now row for that member. I've tried several different join types (Left, Right, Inner, Outer, Full Outer, etc.) but none seem to give me what I want, which would be (based on the last example in SQL Fiddle):

/*** Desired End Result ***/

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
ADavis      | 0               | 11-10-2013    | 0               | 0
BTronton    | 0               | 11-10-2013    | 0               | 0
CJones      | 0               | 11-10-2013    | 0               | 0
DSmith      | 0               | 11-10-2013    | 0               | 0
EGirsch     | 1               | 11-10-2013    | 0.92            | 1
FRowden     | 0               | 11-10-2013    | 0               | 0

What I'm currently getting when I query for specific date of 11-1:

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
EGirsch     | 1               | 11-10-2013    | 0.92            | 1

Which is correct based on the one Time Entry row that is dated 11-10-2013 for EGirsch, but I need to see zeros for the other members in order to get reports and eventually a web dashboard/report for this information.

This is my first question, and while I searched for Join queries, etc. I'm honestly not sure what this function might be called, so I hope that this isn't a duplicate and will help others too trying to find a solution to similar problems.


texts: Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS

texts:
I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the varchar column is not Unicode and that that's the reason it is changing some of the characters to ??.
However, how come existing value written in Japanese is stored in varchar while ideally it should be in nvarchar?
Is there a way to convert nvarchcar to varchar?
The database is out of our control and we cannot change the schema.


texts:
i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given,
is there any way to output table something below output table?

My MySql query following

SELECT a.`person_id` AS STUDENT_ID,q.`questions`,a.`answer` as ANS,q.`question_id`
FROM
questions q
left join answer a on a.`question_id`=q.`question_id`
left join table_info t on q.`question_id`=t.`question_id`

after this query table look like this

 student_id   Question    Answer    Question_Id Ans_id
------------------------------------------------------------
    1           Name        Jonathan     1       1
    1           Age         12           2       2
    1           Sex         Male         3       3
    1           Area        LA           4       4
    2           Name        Mary         1       5
    2           Age         11           2       6
    2           Sex         Female       3       7
    2           Area        NY           4       8
    3           Name        David        1       9
    3           Age         12           2       10
    3           Sex         Male         3       11
    3           Area        NY           4       12

I need output like following

Student_Id   Name       Age     Sex       Area
-----------------------------------------------
1            Jonathan   12      Male      La
2            Mary       11      Female    NY
3            David      12      Male      NY

Questions table:

CREATE TABLE `questions` (
  `question_id` int(11) NOT NULL AUTO_INCREMENT,
  `questions` varchar(250) DEFAULT NULL,
  `questions_cat` int(11) DEFAULT NULL COMMENT 'which category falls this questions. when load questions, then we can load questions by category',
  `question_order` int(11) DEFAULT NULL COMMENT 'sequence/order of the Q',
  `questions_status` varchar(45) DEFAULT NULL,
  `questions_type_id` int(11) NOT NULL,
  `text_question_top` varchar(200) DEFAULT NULL,
  `text_question_bottom` varchar(200) DEFAULT NULL,
  `input_class` varchar(100) DEFAULT NULL,
  `input_id` varchar(50) DEFAULT NULL COMMENT 'eg id="idName"',
  `input_placeholder` varchar(50) DEFAULT NULL COMMENT 'PlaceHolder',
  `input_onchange` varchar(200) DEFAULT NULL COMMENT 'Onchange, like JS',
  `input_other` varchar(200) DEFAULT NULL COMMENT 'text only, eg ng-click="callFunction"',
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  `view_text` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

Answer table:

CREATE TABLE `answer` (
  `answer_id` int(11) NOT NULL AUTO_INCREMENT,
  `answer` varchar(300) DEFAULT NULL,
  `question_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `answer_other` varchar(100) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`answer_id`,`question_id`,`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8

table_info table:

CREATE TABLE `table_info` (
  `tableInfo_id` int(11) NOT NULL AUTO_INCREMENT,
  `table_link` varchar(200) NOT NULL,
  `function` varchar(100) DEFAULT NULL,
  `question_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`tableInfo_id`),
  KEY `fk_tableInfo_Questions` (`question_id`),
  CONSTRAINT `fk_tableInfo_Questions` FOREIGN KEY (`question_id`) REFERENCES `questions` (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8


texts: What is the proper way to relate many to many?

texts:
I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in one table for data integrity and UX purposes. I have a shipment table and I want to join a personal_data.

What I'm thinking is:

shipment has one shipper_information which has one personal_data
shipment has one reciever_information which has one personal_data
shipment has one notify_information which has one personal_data

The thought behind this is it would be easier to separate the specific role in the shipment process.

Or would to be a better idea to relate these in a many to many join table and separate the specific role (shipper, reciever, notify) in the front end?

i.e.shipment has many shipment_personal_data which has many personal_data?


texts:
Our team (Uni students) have a project that we are updating.

Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL.

My question is: How can we import / copy data from MySQL DB to PostgreSQL DB after every X time (12 hours for instance) has passed?


texts: SQL 2019 install....what is AzureAttestService?

texts:
We started the installation and configuration of SQL Server 2019 on Windows 2019 and noticed after install a new service named 'AzureAttestService' was running and set to automatic. Services list the Description and Group field also as AzureAttestService. Below are a few questions:

What is the purpose of this service?
What is the relationship with SQL Server 2019?
Any negatives of stopping and disabling this service?

I've researched, but haven't found much information on AzureAttestService. If anyone has information on this service and can share I'd be greatly appreciative.

Thanks!


texts: How do I find the next saturday in MySQL

texts:
I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP.

So lets say, it is Wednesday (2015-7-22) today. If my trigger runs today, the SP in it must find the next Saturday (2015-7-25).

Plus, even if it is Saturday, but the time is earlier of 9.30 pm, it must find the current day also. After 9.30 pm, it must return the next Saturday.

I would like to the put my whole trigger and sp on here but I don't want to get here crowded. I just need ideas, thanks.

EDIT:

Thanks to oNaye, I coded that:

CREATE DEFINER=`root`@`localhost` PROCEDURE `newGuess`(
IN `muserID` INT,
IN `numm1` INT,
IN `numm2` INT,
IN `numm3` INT,
IN `numm4` INT,
IN `numm5` INT,
IN `numm6` INT)
    begin
set @today = (select weekday(curdate())+1); /*monday is the first day in here*/
if @today<6 or @today=7 then /*it is NOT saturday*/
    set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
end if;
if @today = 6 then /* it is saturday */
    set @current_time = (select curtime());
    set @nextSaturday = (SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY));
    if @current_time < CAST('21:30:00' AS time) then
        set @nextSaturday = curdate();
    end if;
    if @current_time >=CAST('21:30:00' AS time) then
        set @nextSaturday = curdate()+INTERVAL 1 WEEK;
    end if;
end if;
insert into guessesTBL (userID,num1,num2,num3,num4,num5,num6,current__datetime,draw_date) values (muserID,numm1,numm2,numm3,numm4,numm5,numm6,NOW(),@nextSaturday);

end

It worked now.
Solved.


texts: Where can I get SQL Server Transaction Begin Time from?

texts:
I am working with temporal tables in SQL Server and the generated time is the transaction begin time in UTC datetime2

Is there a function or table in SQL Server to get that same transaction begin time to use elsewhere that does not require me to write to and select from a system versioned table first?

I have some non-system versioned tables that are part of the same transaction and I would prefer if the datetime2 recorded against them matched instead of using SysUTCDateTime and having it vary.

I tried to pull it from sys.dm_tran_active_transactions but its datetime and local server time and also appears to be different ?

system versioned:   2017-04-11 14:00:59.4690673

active transations: 2017-04-11 15:00:59.467


texts: I have been asked to make incremental backups of a table based on the value of one of the fields in the table (called groupid). They want a separate incremental backup for groupid=1, groupid=2, groupid=3, etc... I use rman for entire databases or at least whole tables. Can rman backup a subset of a table?


texts: Replication / Bulk Update between Servers

texts:
I'm looking to update several tables between two servers daily to have a replica of the data available.

My question is, what is the least resource heavy and most effective method to implement this?

As a little more background, the operation will be completed out of core hours every day. I understand there's several methods to complete the operation including:

SSIS Package to delete -> bulk update the data (accessing the data through a linked server instance)
Replication between the two sources (my understanding is that they will be in sync therefore wouldn't this be more resource heavy?)

There's not a huge amount of data within the tables (largest table currently stands at c70k records and the growth of the tables is relatively slow).

Both servers are running SQL Server 2008 R2 Standard.


texts: What exactly does PostgreSQL (or other databases) do internally when you "prepare" a query rather than just running it directly?

texts:
When I face a huge number of repeated INSERTs in a loop, I tend to first create a "prepare skeleton query" prior to the loop, and in the loop, I simply "execute" this prepared query and send it all the values. I heard long ago, and can understand in some abstract sense, that this is more optimized than just having the loop with a normal parameterized query in it.
However, I don't understand what exactly PG is doing that makes it so much faster. If it even is so much faster. I frankly have never done any real benchmarks to compare the two different methods.
Also, shouldn't PG be "warmed up" to a query once you repeat it several (let alone many) times in very short succession, and perhaps do the same thing as I do manually, but internally, when I just use a normal parameterized query?
I find myself constantly second-guessing what the database is up to internally. I have no real grasp of how "smart" it is. I fear that I'm doing a lot of things which are meaningless because it's already taken care of internally by its smart code.
Maybe "preparing" and "executing" is an archaic practice which has no real benefit these days?
It sounds like PG is basically allocating resources to "prepare" for upcoming huge amounts of similar INSERT queries, but I don't understand what exactly it would do differently compared to just executing them one by one. I also don't understand what the point would be of a non-INSERT prepared query/statement.
PS: Just to clear up any confusion: I never use non-parameterized queries, whether they are prepared or not. A lot of people confuse "parameterized queries" and "prepared statements". Even I called the latter "prepared queries" in this question...


texts: Is there a way to have a system generated report of list of DBA in SQL Server?

texts:
I'm an auditor, and I want to ask if there is a list of system generated report of DBA users in SQL Server? Per my audit client, there is no way to generate it.

However, I'm thinking that this is a data also in which you can just generate to the SQL Server databases. I just want to validate that the list of DBAs is restricted to DBA of our company for legitimate business need and security purpose.


texts: database design question with Partition and Replication

texts:
I have a main database that runs on a server with good resources and, let's say, a dozen satellites that replicate data. Satellites have less resources than main server.

In that database there is a BIG table, that I want partitioned in ranges because in fact each satellite cares about his own specific range.

PARTITION BY RANGE( ID ) (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p2 VALUES LESS THAN (3000000),
    PARTITION p3 VALUES LESS THAN (4000000),
    PARTITION p4 VALUES LESS THAN ...
);

Let's say satellite 1 cares about table IDs that start with 1, satellite 2 cares about IDs that start with 2 and so on. (all IDs are numbered in millions).

Is it possible to replicate on each satellite only data that are relevant to the satellite? I mean I don't want p2 and p3 on satellite 1, only p1 is relevant for satellite 1. But I don't know how to say to the replication engine that I want only such a part of the data.

Is it only possible to replicate only one partition of the partition scheme?


texts: Good idea to have an index on datetime column?

texts:
Assuming something like so:
select * from table where DatePart(YEAR, dateColumn) = 2012
Reasonable to have  an index on dateColumn?
Edit*
For SQLServer


texts: Delete or hide the row in set text in mysql result

texts: Is there a way to not show the text row in set (0.10 sec) in the result of a mysql query


texts:
Im running a mysql database on a linux server with 24gb ram and im trying to run a performance test of my system.

Now the thing is, when im running it without a slave replication and in particular, without the log bin enabled, the performance are great but as soon as i start enabling the log bin and starting the slave, the performance are sky diving.

My innodb buffer pool is set to 18gb. When i look at the servers during the performance test it seems like the db is not using a lot of its cpu (around 40% most of the times) while the application servers are using most of it for most of the time, for the point of failiure in some cases.

Its worth noting that im not quite a qualified dba but i do have basic understanding about mysql so im sorry in advance if i might need extra guidance here.

Another thing is im currently unable to provide the full my.cnf file but can provide parameter values if you'll need some extra information here.
Thank you very much


texts:
We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please.

We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and SUFFIX_NAME. All these columns (except ID of course) are nullable.

What is the best way to index this table, and what is the best way to query for full name with wildcards?

This is used in a web app, and we need to allow the user to search for something like

Smit*, Bob

and it return "Bob Smith", "Bobby Smithson", etc.

We will not allow the user to sort results.


texts:
Can some please provide a quick guide for backup and recovery of Oracle 11 DB?
I just need to test it very quickly so i don't need something too fancy, but should be scheduled.

Thanks in advance.


texts: Is a single batch request equivalent to a single tsql request?

texts:
I'm looking into some performance metrics and every recommendation includes monitoring  Batch Requests/sec. But, maybe a silly question, does a singe batch request correspond to exactly one tsql statement? Say it contains exactly one select or one update, etc. So, for example, a stored procedure with 3 selects and 2 inserts triggers 5 batch requests. Or, as I think, a batch request can include for example a full stored procedure, a function, several select/update/insert statements, etc.

All I can find is similar to these descriptions:

TechNet Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.
Understanding how SQL Server executes a query Batch Request This request type contains just T-SQL text for a batch to be executed. This type of requests do not have parameters, but obviously the T-SQL batch itself can contain local variables declarations. This is the type of request SqlClient sends if you invoke any of the SqlCommand.ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), ExecuteXmlReader() (or they respective asyncronous equivalents) on a SqlCommand object with an empty Parameters list. If you monitor with SQL Profiler you will see an SQL:BatchStarting Event Class


texts: MS SQL Server: Transactional replication not working but no errors are present

texts:
(MS SQL Server 2008 R2) I have transactional replication setup from server A to server B.
Server A is an Oracle server, so the publisher and subscriber both reside on server B (SQL 2008 R2).
It was working for a while, but then something dropped the privileges (within the Oracle server) of my Oracle user's access to the tables being replicated.

I fixed the privileges (via re-granting those tables SELECT to my Oracle user.)
Then I marked my transaction replication's snapshot for reinitialization and generated a new snapshot.
This appeared to work and brought over the most recent data.

Transactional replication is still not working as any subsequent data changes in the Oracle database aren't getting replicated across.
I don't see any errors in the Log Read Agent Status or Synchronization Status (or the Snapshot Agent Status - which succeeded when I generated the new snapshot.)
I also don't see any errors in any of the jobs that belong to my transactional replication.

Thoughts on how to debug this or what could be going wrong?
I would assume this is an issue on MS SQL Server's side with the transactional replication if the generation of the new snapshot worked fine now after I fixed the privileges in Oracle.


texts: [Solved]MySQL index work in one table and not work in another table when using IN

texts:
I was asked if the index is working or not when we use IN keyword in MySQL like below:

 //suppose col has index and the value is almost unique
 SELECT * FROM yourtable WHERE col IN('xxx','yyy','zzz');

In order to get the accurate result, I do the test myself,I created two tables,and index works on one table but not work on another table,I do not know why.Hope someone helps me understand it.Thanks in advance.

The first table is called system_users :

CREATE TABLE system_users(
  id INT(8) NOT NULL AUTO_INCREMENT,
  NAME CHAR(15),
  gender CHAR(1),
  age INT(2),
  department CHAR(1),
  email VARCHAR(20),
  PRIMARY KEY  (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX name_index ON system_users(NAME);

INSERT  INTO system_users(id,NAME,gender,age,department,email)
  VALUES
(1,'euser_izpwhzzcz','1',37,'1','yvmk0oqm0lf@mail.com'),
(2,'muser_czepyfklv','1',20,'1','a9wq08gcllk@mail.com'),
(3,'tuser_mwrluajek','0',20,'1','o8jqqebi0bt@mail.com'),
(4,'duser_jzgpheafx','0',22,'2','hegehgsbfmg@mail.com'),
(5,'auser_iacjkekhg','0',36,'2','46drnrja29i@mail.com'),
(6,'euser_eikhwvwsi','1',31,'2','56amq6nayde@mail.com'),
(7,'muser_lblozikah','1',29,'3','qlbtvpzgrej@mail.com'),
(8,'cuser_xhsuuzzur','0',35,'3','klrr6kurylg@mail.com'),
(9,'vuser_iwmklbyzp','0',22,'3','ngdrlguhzrp@mail.com'),
(10,'buser_yfnffsfoc','0',37,'4','o1jvffj4ahu@mail.com');

Now,when I use EXPLAIN to analysis below query sql,I found the index works

EXPLAIN SELECT * FROM system_users WHERE NAME IN('euser_eikhwvwsi','buser_yfnffsfoc');

As we see the value of type is range,in this scenario index seems works when use IN

The second table is called test:

CREATE TABLE test(
  id INT PRIMARY KEY AUTO_INCREMENT,
  c1 VARCHAR(10),
  c2 VARCHAR(10),
  c3 VARCHAR(10),
  c4 VARCHAR(10),
  c5 VARCHAR(10)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX c_index ON test(c1);

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','d2','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','e2','e3','e4','e5');

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','b1','c1','d1','e1');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','a2','b2','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','a3','c3','d4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','a4','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','a5','b5','e4','e5');

Now when I use EXPLAIN to analysis below sql,the index seem not work since the value of type is all

EXPLAIN SELECT * FROM test WHERE c1 IN('a1','b1');

Both of the two tables has ordinary column and datas,but when use IN,why the type of one is range while the other is all?

PS:The version of MySQL in my side is 5.0.67,I have run OPTIMIZE TABLE for both table and still get the same result.

Below are the result when execute ANALYZE TABLE

Note:I have found it has something to do with the data amount in my version,if I insert hundreds of data,then the explain result are expected


texts: Read Replicas with multiple instances of MySQL running on different ports

texts:
I have a specific need to create a single linux server which can host 4 MySQL instances with read replication. Here are a few questions I cannot seem to find answers to:

Can you setup read replication through MySQL similar to Amazon RDS, or is
Amazon using a propriety application to handle this read/write
replication? If not, are there standalone packages for this kind of replication?
Can you set individual settings for each MySQL installation? For instance I want a memory limit for MySQL running on port 64506 of 2GB, but MySQL installation on port 3306 should have a 16GB memory limit?


texts: Build JSON object from one-to-many relationship data in a single query?

texts:
I have a PostgreSQL 9.5.3 DB with tables like this:

container
    id: uuid (pk)
    ... other data

thing
    id: uuid (pk)
    ... other data

container_thing
    container_id: uuid (fk)
    thing_id: uuid (fk)
    primary key (container_id, thing_id)

A container can point to any number of things (without duplicates), and a thing can be pointed to by any number of containers.

There may be a large number of containers and things (depends on how many customers we have). Each container would likely only have 1 to 10 things in it. We'll only query for around 20 containers at once, max. A container can be empty, and I need to get an empty array back.

I need to build json objects that represent containers, something like this:

{
    "id": "d7e1bc6b-b659-432d-b346-29f3a530bfa9",
    ... other data
    "thingIds": [
        "4e3ad81b-f2b5-4220-8e0e-e9d53c80a214",
        "f26f49e5-76b4-4363-9ffe-9654ba0b0f0d"
    ]
}

This is working fine, but I'm doing it by using two queries:

select * from "container" where "id" in (<list of container ids>)
select * from "container_thing" where "container_id" in (<list of container ids>)

I then procedurally build the "thingIds" array for each container.

Later I found a solution with correlated subqueries that works nicely for me at the moment.

select *, array(select thing_id from container_thing where container_id = c.id) as "thingIds"
from container c;

Is there a better way to do this, perhaps using a join somehow?
It seems like they always generate a single set of rows, which would mean duplicating the container data for every thing that's being pointed at.


texts: How much space is needed to reclaim space from a table in MyISAM

texts:
MariaDB 10.x | MyISAM

My question is very similar to this one but regarding MyISAM.

I have a DB server that's running very low on disk space (2.5 GB free) and a MyISAM database with individual tables that are 3 GB and 8 GB in size.  I've successfully deleted entries from the tables so I'm confident that some of the space is white space but I'm not sure how much.

I've heard conflicting statements from folks regarding what happens when running OPTIMIZE TABLE.  Some people say it doesn't require any extra disk space... others tell me it will create a new table and copy individual rows into the new table... and only AFTER that's complete will it begin reclaiming disk space.

Can someone please clarify?


texts: How can I optimize a query where a IN clause is replaced with multiples UNION ALL?

texts:
I have a query like this:

select *
from slow_view
where id = 1
union all
....
union all
select *
from slow_view
where id = 1000

-- 50 ids, exec time: 10.615 sec,

This would run in a very acceptable time if it was replaced with:

select *
from slow_view
where id in (1, ...., 1000)

-- 50 ids, exec time: 1.3 sec,

or

with slow_view_cached as (
select * from slow_view)
select *
from slow_view_cached
where id = 1
union all
....
union all
select *
from slow_view_cached
where id = 1000

-- 50 ids, exec time: 1.83 sec,

But unfortunately it is generated by an ORM and I don't have a chance to modify it. It's quite bad as an ORM, I don't think I can do much to improve its behavior.

Is there something I can do to my query or to my view to make it "cached" for all the subqueries in this case? I know I could use a materialized view but it doesn't fit my case very well.

AFAIK in Postgres it's not possible to define indexes on a view, so id is not indexed or unique.

Is there anything else I can try?

I'm using Postgresql 9.5 (but if you have a solution for PG 9.6 or even PG 10 it would be acceptable, since I plan to update very soon).

The code is a bit too complex to post here. In essence this query is generated in the following case. slow_view is an association of a Model m. What I do is basically m.find().populate(slow_view, criteria); The documentation is here.

criteria is excluded from the query for the sake of simplicity, id is the foreign key from _slow_view_ to Model.

I could call a stored procedure from this ORM, but it would mean altering the application a bit too much.


texts: Why do row-versioning isolations use a linked list of versions of record?

texts:
Trying to better understand row-versioning based transaction isolations in SQL Server...

According to Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

"...when a record in a table or index is modified, the new record is stamped with the "sequence_number" of the transaction that is performing the modification"

which makes perfect sense - when a transaction ID is linked to a record change.

What I cannot understand is, from the same source:

"... All the earlier versions of a particular record are chained in a linked list"

Why is there a need in a linked list, i.e. to link the changes of the same record ?

UPDATE:

As stated by Remus Resanu in his answer to "Does Snapshot Isolation only give you what has been committed? (And General Snapshot Isolation questions.)":

"Snapshot will give you the data that was committed at the moment the snapshot was taken. This moment means either when you issued BEGIN TRAN if you use true SNAPSHOT isolation level, or the moment your statement started if you use read_committed_snapshot RCSI"

In other words, there is a need to keep multiple row versions of a record for  multiple/different transactions (with corresponding pointers/links), that is to be accessed directly to a corresponding copy taken when snapshot was taken (though multiple ones per multiple transactions but the single one per one corresponding transaction). This is in perfect agreement with explanation that RCSI takes less space in tempdb but contradicts to statement (or makes ambiguous to my comprehension), referring to both RCSI and SNAPSHOT (note, that it is not making any difference between RCSI and SNAPSHOT according to its context used in that article):

"All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row. Version records must be kept in the version store only as long as there are row versioning–based queries that might be interested in them"

The latter sounds like the space required is still the same both for RCSI and SNAPSHOT?!
and it is stated contradictory in different MSDN articles

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?


texts: Grant usage partially on schema to user on Postgres

texts:
I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema visible for that user.

I need that this specific user can only INSERT in this specific table and is unable of viewing other relations (tables, sequences, etc) in the same schema. By 'unable of viewing' I mean not being able of seeing that these relations exists.

Being even more clear, in the vision of this user, the schema has only that table inside and nothing more.


texts: PostgreSQL 11: how to partition 1000 tenants

texts:
Setup

We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're considering using partitioning to split them per customer.

Problem

1000 tenants (customers) are a lot of partitions - is it reasonable to do this on PostgreSQL?

More details

Currently, the separation between our tenants is via an account_id column on all tables in the DB. There are a few tables that are pretty big. For example, there is an event table (the one I'm interested in partitioning) that contains audit logs and other events for everything that happens in our app.

Here are a few facts about the event table:

It contains about 300M rows + a few of composite/partial indexes.
The count of events by account_id is very uneven, 5% accounts have 50% of the data.
There is a timestamp field and a few others (JSONB, author_id, etc..)
Write operations: inserts mostly and deletes (per account_id). Deletes can potentially be millions of rows. No updates. Deletes of big accounts are rare and not a big performance issue at the moment.
Selects are either for a specific event (by account_id + id) or all events in a given period of time. Period of time is not always set. account_id is always present in the query.

Possible solutions

Partionining by account_id:

Pros:

Deletes will be fast because of DROP TABLE.
Queries should also be pretty decent since all queries contains WHERE account_id = 123

Cons:

Are 1000 partitions too much for Postgres?
Uneven distribution of events per account creating a few super big partitions and a few small ones.

Partitioning by timestamp:

Pros:

Recent data is usually mostly accessed and will make queries with a timestamp faster.
More predictable/even distribution of events.

Cons:

Deletion of a single account might touch a lot of partitions - not a big concern.
Always needs to contain a filter by timestamp - which is not always possible.


texts: Script to identify which AD accounts are no longer active

texts:
Trying to get a script that will identify which AD accounts/Windows authentication on a SQL Server are no longer active.  Say perhaps an employee was released and the login would be removed from AD, but SQL Server still shows it in the logins.

Any assistance would be great.


texts:
I am storing some data in influx and it is quite confusing that influx is 4-5 times slow as Mysql. I try to test by inserting 10000 rows in mysql and then in influxdb.
and the stats are below.

Mysql

real: 6m 39sec
user: 2.956sec
sys: 0.504sec

Influxdb

real: 6m 17.193sec
user: 11.860sec
sys: 0.328sec

my code for influx is given below, I used the same pattern to store in mysql.

#!/usr/bin/env python
# coding: utf-8
import time
import csv
import sys
import datetime
import calendar
import pytz
from influxdb import client as influxdb
from datetime import datetime

host = 'localhost'
port = 8086
user = "admin"
password = "admin"
db_name = "testdatabase"
db = influxdb.InfluxDBClient(database=db_name)

def read_data():
    with open(file) as f:
        reader = f.readlines()[4:]
       for line in reader:
            yield (line.strip().split(','))

fmt = '%Y-%m-%d %H:%M:%S'
file = '/home/rob/mycsvfile.csv'

csvToInflux = read_data()
for metric in csvToInflux:
    timestamp = datetime.strptime(metric[0][1: len(metric[0]) - 1], fmt)

    new_value = float(metric[1])
    influx_metric = [{
        'measurement': 'mytable1',
        'time': timestamp,
        'fields': {
             'col1': metric[1],
             'col2': metric[2],
             'col3': metric[3],
             'col4': metric[4],
             'col5': metric[5],
             'col6': metric[6],
             'col7': metric[7],
             'col8': metric[8],
             'col9': metric[9]
        }
        }]
    db.write_points(influx_metric)

Can someone give me an idea how can I improve it. I think it might be due to cache. is cache option is off by default in Influx db? and can someone guide me to do batch processing in influx. I try to look over SO and google but couldn't solve my problem. I am newbie to influx db. I am trying to make it faster.


texts: Programatically scan a stored procedures parameters

texts:
I have been trying to figure out how to scan a stored procs parameters from the execution plan. I often see most people suggest using the ParameterList within the execution plan xml. This works fine when there is just one statement that uses all of the parameters. However this is often not the case. The stored procedure parameters could be distributed over several queries or not used in a query at all.

In my examples below, I would like to know how to get the distinct parameters and their values from the example using sql xml functions. Also, you will notice that not all of the parameters are output into the ParameterLists.

/***********************************************************************/
/*EXAMPLE: you have to scan all of the selects parameter lists to match up the parameter names to the proc parameters */
/***********************************************************************/
IF OBJECT_ID (N'dbo.TestFooProc') IS NULL
   EXEC('CREATE PROCEDURE dbo.TestFooProc AS SET NOCOUNT ON;');
GO

ALTER PROCEDURE dbo.TestFooProc (
    @p1 int,
    @p2 int,
    @p3 int = 8675309
) AS
BEGIN
    --distribute the parameters over multiple selects
    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p2;

    -- other simulated queries ...

    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p1;

END
GO

EXEC dbo.TestFooProc @p1 = 5, @p2 = 22

SELECT [db_name] = DB_NAME([eps].[database_id]), qt.[text], qp.[query_plan]
FROM  sys.[dm_exec_procedure_stats] eps
CROSS APPLY sys.[dm_exec_sql_text](eps.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) AS qp
WHERE [eps].[database_id] = DB_ID()
    AND qt.[text] LIKE '%dbo.TestFooProc%'

RETURN  -- CLEANUP

IF OBJECT_ID (N'dbo.TestFooProc') IS NOT NULL
   DROP PROCEDURE dbo.TestFooProc;
GO


texts: Sequence according to value from table in postgresql

texts:
I have dictionary_type and dictionary_value tables. Value is connected with type. I would like to create sequence which will set the id of values according to type id.

Expected dictionary_type:

id       type
----     ----
1        type1
2        type2

Expected dictionary_value:

id       type_id     value
-------  -------  -------
100      1         value1
200      2         value2
101      1         value3
102      1         value4
201      2         value5

So baiscly I would like to make it like:
nextval(sequence(type_id * 100)). How can I do it in postgresql?

Have a nice day!


texts: Can I use a Clob as a table key (from Java)?

texts:
Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)?

I know it is probably a very bad idea to use a CLOB as a key, but I do need to use large text as a key on a one-shot operation.


texts: I have read conflicting articles about shrinking a databases. Basically, our database grew 100% overnight from 150 gigs to 300 gigs b/c I believe it was with staging tables I put in. However, all the extra spaces is unused (150 gigs worth of unused data). Should I shrink my db at this point?  What problems could this do if I shrink the database?  Also, any other suggestions would be appreciated.


texts: would you help me understand errors and bad exercise containing in the query?

texts:
A customer called technical support for a DBMS, saying that the DBMS had a severe bug and was crashing the operating system.
On investigation, it was found that the ‘crash’ always happened when a particular query was run. The query referenced two tables, whose DDL follows:

CREATE TABLE transaction_details (
    Customer_no number,
    Card_sequence_no number,
    Transaction_date datetime,
    Product_no varchar(50),
    Units_quantity number
)

This table was keyed on the first three columns.

It contained about 500 million rows, typically looking like

12345678912345, 3, 27.05.2012 15:34:21, P5678K2001XY123456, 12

The customer table DDL looks like:

CREATE TABLE customer (
    Customer_no varchar(25),
    Card_type number,
    cid_status char(1),
    privacy_flag char(1)
)

This table was keyed on customer_no.
Card_type could have values 1-4.
Cid_status had 5 different possible values, the large majority of rows having value ‘A’.
The privacy_flag is a Y/N flag with about 50% of rows for each value.
The table contained about 500,000 rows, typically looking like

12345678912345, 1, A, Y
3

The query text was:

SELECT distinct c.card_type, t.customer_no, t.card_sequence_no,
t.transaction_date, t.product_no
FROM transaction_details t, customer c
WHERE date_trunc('day', t.transaction_date) between '01.03.1999' and '14.03.1999'
AND (right(t.product_no, 6) in ('712213', '716715', ‘717555’,... etc for 400 values)
OR (right(t.product_no,5) in ('81531','76910', ‘98123’,...etc for 150 values)))
AND c.cid_status = 'A'
AND c.privacy_flag != 'N'
and c.card_type like '50'
AND t.units_quantity > 0

The date_trunc function in this DBMS simplifies the full date/time in the column, in this case to the nearest day.
The right function results in a substring of the right-most characters, so in this case,
right(t.prod_no, 6) produces the last 6 characters of the product_no column.


texts: Could not start WAL streaming, replication slot is active

texts:
I want to set up replication between two of my databases.

One is an Aurora database in PG 10 the publisher, and the other one is an RDS database in PG 10.

But I'm facing an issue, I have 500 GB of data to transfer, so I'm adding table one by one to the replication and waiting for the status to be ready before adding another one.

But after a while, I've got this error: ERROR: terminating logical replication worker due to timeout on the subscriber side. And the worker cannot restart because when it tries to restart the following error appears on the logs: ERROR: could not start WAL streaming: ERROR: replication slot "xxx" is active for PID 25860

After that, the WAL file keeps increasing in the publisher database, and the LSN does not move anymore.

The command I made were quite simple:

CREATE PUBLICATION xxx FOR TABLE xxx;
CREATE SUBSCRIPTION xxx CONNECTION 'xxx' PUBLICATION xxx;


texts: Attunity Connectors for SSDT are not showing in VS 2017 (SSIS Toolbox)

texts:
I'm trying to add a connection manager using the new Attunity drivers I downloaded but I'm not seeing it as an option.

This Microsoft page does not list the latest SSDT version (15.x) as one of the compatible versions with the Attunity Connectors  version 5.


texts: SQL Server 2014 Transactional Replication Partition Switching

texts:
I am working with SQL Server 2014 Transactional Replication with tables that have partitions.  The source tables are partition switched when they are loaded and so far I have been able to replicate this successfully.

The replication source I am working with performs some dynamic partition management by creating partitions on the Partition Function/Partition Scheme during the load into the table.  This is not natively supported by Transactional Replication per the documentation found here:

  After the Subscriber is initialized, data changes are propagated to the Subscriber and applied to the appropriate partitions. However, changes to the partition scheme are not supported. Transactional and merge replication do not support replicating the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. The changes associated with them will not be automatically replicated to the Subscriber. It is the responsibility of the user to make similar changes manually at the Subscriber.

This is where I am getting hung up.  We've worked through most of the problems with the loading and switching of partitions and we are now dynamically creating the new partitions, as they come in, on both the replication source (publisher) and replication target (subscriber) through the use of a stored procedure as part of the load to the publisher.  The stored procedure exists on both the publisher and subscriber (manually put there).

When running a test last night, we saw that partitions were dynamically created on the publisher and subscriber, but there was a new error on the Log Reader Agent.  At this point, I don't even know where to begin to start tracking this down.

  Error messages:
  The process could not execute 'sp_replcmds' on 'RNCAZFAST2'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
  Get help: http://help/MSSQL_REPL20011

  No catalog entry found for partition ID 72057598976393216 in database 27. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption. (Source: MSSQLServer, Error number: 608)

I started looking up the error and it looked like it was a bug back in 2005, but we are on SQL Server 2014 Enterprise on an Azure VM.  Any help is greatly appreciated!


texts: How to replace multiple parts of a string with data from multiple rows?

texts:
Here's a fiddle to show what I'm after.

Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in curly braces, mixed in with any other valid text characters.

Example: '{1} / {9} ... {12}'

With a single SELECT statement, I want to return a string whereby all the integers (and their wrapping braces) have been replaced with a value derived from my table; specifically, the text value for the row having an ID that matches the number found in the source string.... and any characters outside the curly braces remain untouched.

Here is an example that fails to complete the task:

select
  replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
from testing;

This would return 1 row per row in the testing table. For the row with id value = 13, the '{13}' portion of the string is successfully replaced, but the '{15}' portion is not (and vice versa on row 15).

I imagine creating a function that loops through all testing rows and repeatedly attempts replacements would solve the problem. Be that as it may, a straight-up SQL statement would be preferable to looping.

Example Data

+----+-------------------+
| id |  display          |
+----+-------------------+
|  1 |  Apple            |
|  2 |  Banana           |
|  3 |  Celery           |
|  4 |  Dragonfruit      |
|  5 |  Eggplant         |
|  6 |  Fenugreek        |
|  7 |  Gourd            |
|  8 |  Honeydew         |
|  9 |  Iceberg Lettuce  |
| 10 |  Jackfruit        |
| 11 |  Kale             |
| 12 |  Lemon            |
| 13 |  Mandarin         |
| 14 |  Nectarine        |
| 15 |  Olive            |
+----+-------------------+

Example use cases

select replace('{1} {3}',null,null)
-- Returns 'Apple Celery'

select replace('{3},{4},{5}',null,null);
-- Returns 'Celery,Dragonfruit,Eggplant'

select replace('{1} / {9} ... {12}',null,null);
-- Returns 'Apple / Iceberg Lettuce ... Lemon'

Clearly, the replace keyword does not do the job.

PS. If a solution required the format of the string to change in order to facilitate this, that is an option.

For example: '#1 / #9 ... #12' (to correlate with the earlier example)

In this format, perhaps we could break the string up into a rowset, based on #, take the left characters until we find a non-numeric, join to the testing table based on the numbers taken, replace the # and numbers with the testing table's display value then stuff all those individually modified tokens back into a single string for xml path?

I am using SQL Server 2016 which does not support string_agg. That said, if there is a solution using string_agg, I am still interested in reviewing it.


texts:
I have couple of questions on how caching of data works
Imagine we have below situation:

Server restarted or we just ran DBCC DROPCLEANBUFFERS

We have a Table1 that is 50 GB and has columns A, B, C, D, E.
Column A is clustered index key, columns B and C have nonclustered indexes on them.

When we do

select top 100 * from Table1

is the whole clustered index (table) being read from disk to memory, even if we need just 100 rows?
Or are just 100 rows (their data pages) read from disk to memory?
Same with the nonclustered index, when we do

select top 100 * from Table1 where column B = 'some value'

does the whole non-clustered index + clustered index get loaded into memory?
Or just 100 rows from the non-clustered index and 100 rows from the clustered index?


texts: Good articles for SQL Server Health Check specifically for Web Applications

texts:
I am searching for Database Health check articles specifically for a web application back-end server and SQL Server 2005/2008.

My current checklist includes:

- Index Fragmentation
- SQL Server logs
- Mirroring Monitor
- Backup completion
- File growth

What else should I look for? Are there good articles specifically for a webapp DBA scenario?

(Note: I don't need to worry about many database users - there is only one user the application itself)


texts: Select two aggregate functions on the same column using different criteria - performance

texts:
Dear stack exchange users, first question here.

The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many finished, unfinished, total money earned, total money pending from a couple of tables

I'm a bit worried about if this is the optimal way to write this query. It looks like I should be able to shorten this since i'm selecting on the same tables, shouldn't it be possible to reuse that in some way?

            SELECT finished, notfinished, totalamount, areas, pendingamount
            FROM
            (
            SELECT COUNT(finished) as finished, COALESCE(SUM(cost),0) as totalamount FROM HiresService as hs
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs.finished = true
            ) t1
            JOIN
            (
            SELECT COUNT(finished) as notfinished, COALESCE(SUM(cost),0) as pendingamount
            FROM HiresService as hs2
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs2.finished = false
            ) t2
            JOIN
            (
            SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
            FROM ServicesArea
            JOIN Area USING(areaid)
            WHERE serviceproviderid IN(
                 SELECT serviceproviderid
                 FROM ServiceProvider
                 JOIN BusinessUser USING(businessuserid)
                 WHERE businessuserid = ?)
            ) t3

Table structure looks like this:

EDIT: (accepted answer)

SELECT finished, notfinished, totalamount, areas, pendingamount
FROM ( SELECT
sum(case when finished = 1 THEN 1 ELSE 0 END) as finished,
sum(case when finished = 1 THEN cost ELSE 0 END) as totalamount,
sum(case when finished = 0 THEN 1 ELSE 0 END) as notfinished,
sum(case when finished = 0 THEN cost ELSE 0 END) as pendingamount
FROM HiresService
JOIN ServiceProvider USING(serviceproviderid)
JOIN BusinessUser USING(businessuserid)
WHERE BusinessUser.businessuserid = ?
) t1
JOIN
(
SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
FROM ServicesArea
JOIN Area USING(areaid)
WHERE serviceproviderid IN(
SELECT serviceproviderid
FROM ServiceProvider
JOIN BusinessUser USING(businessuserid)
WHERE businessuserid = ?)
) t2


texts: Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index?

texts:
I'm going to use a BRIN index to replace the B-tree index on a date/timestamp column in Postgresql-11. I never used it before.
Since that a BRIN index can be more effective if the data is stored physically in the order that the column is being indexed.
I'm doubt about wether or not I should delete all the data, and then re-insert them in the order.
My data is being used for statical analysis, would not change.


texts:
I am having concurrency issues with my inserts in a stored procedure.  The relevant part of the procedure is this:

select @_id = Id from table1 where othervalue = @_othervalue
IF( @_id IS NULL)
BEGIN
    insert into table1 (othervalue) values (@_othervalue)
    select @_id = Id from table1 where othervalue = @_othervalue
END

When we run 3 or 4 of these stored proc's concurrently we get multiple inserts on occasion.

I am planning on fixing this like so:

insert into table1 (othervalue)
    select TOP(1) @_othervalue as othervalue from table1 WITH(UPDLOCK)
    where NOT EXISTS ( select * from table1 where othervalue = @_othervalue )

select @_id = Id from table1 where othervalue = @_othervalue

The question is, is that how to concurrently insert without duplicates in sql server? The fact that i have to use TOP to only insert once disturbs me.


texts: How to convert SQL Server database file to MongoDB?

texts: I have .mdf / .sdf files created in SQL Server. I want to convert them to MongoDB. How can I do this?


texts: MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ' '

texts:
I've been trying to upload a CSV table into my remote server database but I find an error. I use MySQL in a RedHat Linux distrib. My mycsv.csv file looks like this:

aa,ProductDescription,country,potato,L,11/18/2013,N,05

bb,ProductDescription,country,tomato,L,12/31/9999,N,05

cc,ProductDescription,country,curry,M,01/01/2014,Y,05

dd,ProductDescription,country,spicy,V,05/01/2015,N,

As you may see, last line ends with comma "," without having any field (it is empty and should be like this or even better if I can change it to NULL). However when I type the following query:

LOAD DATA INFILE "/home/mycsv.txt"
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

I get the following error in the command line

  Error Code: 1366. Incorrect integer value: '' for column
  'product_life_cycle' at row 4

Does anybody know how to set a null value after the comma in the last line?
(I think this is the problem why I can't upload my table). Thank you very much in advance.

The table definition:

CREATE TABLE master_data
  ( aa varchar(6) NOT NULL,
    bb varchar(50) NOT NULL,
    cc varchar(3) NOT NULL,
    dd varchar(2) NOT NULL,
    ee varchar(1) DEFAULT NULL,
    ff varchar(20) NOT NULL,
    gg varchar(1) NOT NULL,
    hh int(11) DEFAULT NULL,
    PRIMARY KEY (aa),
    UNIQUE KEY aa_UNIQUE (aa)
) ENGINE=InnoDB
  DEFAULT CHARSET=latin1 ;


texts: How to Publish Checked-In changes only in Team Foundation Server 2013 to SQL Server?

texts:
I would like to ask you how to publish checked-in changes only in Team Foundation Server 2013 to SQL Server? It's a new installation of TFS 2013 - installed by me.

Currently when I create new object like table as a part of the project and don't do check-in on this table and next publish the project, such table is published anyway. I was thinking that only checked-in (approved) changes are published to SQL Server (without objects which has no approval).

It's important because I want publish only complete objects to the SQL Server. Without objects which are still in development.


texts: Grant execute on xp_logevent to sa using sa user to do it

texts:
Running a SQL Server 2012 CLR stored procedure with this code:

const string executeLog = "EXEC master..xp_logevent @errorNumber, @message, informational";
using (SqlCommand cmd = new SqlCommand(executeLog, connection))
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@errorNumber", 60001);
    cmd.Parameters.AddWithValue("@message", url);

    cmd.ExecuteScalar();
}

I get this error:

  System.Data.SqlClient.SqlException: The EXECUTE permission was denied
  on the object 'xp_logevent', database 'mssqlsystemresource', schema
  'sys'.

I've tried to do this as sa user:

grant execute on xp_logevent to sa

But I get another error:

  You can not find user 'sa' that does not exist or the user does not
  have permission.

How can I grant execute permissions to sa user?


texts:
I'm looking for a way to list all properties on a replica.

I checked this page: https://lucene.apache.org/solr/guide/7_6/collections-api.html

But no such thing is mentioned there. Only adding, editing or deleting existing properties, but no way of listing all of them.

How can I find out which properties have already been set on a replica?

EDIT: Found them in zookeeper.

Use command:

zkCli.sh get /solr/collections/<NAME_OF_YOUR_COLLECTION>/state.json


texts: mysqldump still fails even with mysqldump --column-statistics=0

texts:
mysqldump still fails even when using mysqldump --column-statistics=0

# mysqldump --column-statistics=0

Just returns

# mysqldump: [ERROR] unknown variable 'column-statistics=0'

Am I missing something obvious here?

EDIT: Even trying to get the version gives me an error.

mysqldump -v
mysqldump: [ERROR] unknown variable 'column-statistics=0'

Mysql version is:

# mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1221
Server version: 5.7.27-0ubuntu0.16.04.1-log (Ubuntu)

Which mysql:

# which mysql
/usr/bin/mysql

It's an Ubuntu server running ServerPilot. The ultimate aim is to run the SiteGround WordPress migration plugin, but it's failing with the Column Statistics error in it's log.

Also tried from the comments:

root@vultr:~# mysqldump --no-column-statistics
mysqldump: [ERROR] unknown variable 'column-statistics=0'
root@vultr:~# mysqldump --skip-column-statistics
mysqldump: [ERROR] unknown variable 'column-statistics=0'
root@vultr:~#

and then even mysqldump on it's own errors:

# mysqldump
mysqldump: [ERROR] unknown variable 'column-statistics=0'


texts: Best database for large volumes of sales transaction data?

texts:
I'm in the process of rebuilding a MVP after having successfully proven it's place in the market. It's a small BI web app that collects POS sales transaction from shops and provides users with interesting sales data - such as sales trends, revenue forecasts and projections etc.

We started off with a MySQL database and only a few store, but now that we've grown to past 25 stores, our transactions table in the database has grown to 20+ million rows and we can see quite a bit of performance decrease - especially when grouping sales by store, month and SKU. With another 50+ stores due to onboard soon, we're soon going to be hitting 100s of millions of records in that table so are looking at other potential database/storage alternatives.

We have thought about utilising a time series database such as influxdb, or even postgres.

Any help or advice on choosing a good database/storage engine for large volumes of sales transactions would be helpful?

Note: Our current DB is a "db.r3.large" Amazon RDS - 2 cpus and 15Gb memory.


texts: all tables used by a stored procedure and all sps that use a particular table - recursive cte

texts:
I am working on a script that gives me a list of tables that use a specific stored procedure.

It also works on all tables used by a stored procedure.

on the example below, all stored procedures that use the table 'ProductItemDetailsDenorm'

USE US15WINMPRODUCT
GO

DECLARE
      @sp_name nvarchar(128) -- NULL shows all stored procedures that use the table @table
     ,@table   nvarchar(128)

SELECT
      @sp_name = NULL
     ,@table   = 'ProductItemDetailsDenorm'

            ;WITH stored_procedures AS (

                SELECT
                schema_name(o.schema_id) as proc_schema,
                o.name AS proc_name,
                schema_name(oo.schema_id) as table_schema,
                oo.name AS table_name,
                ROW_NUMBER() OVER(PARTITION BY o.name,oo.name ORDER BY o.name,oo.name) AS row
                FROM sysdepends d
                INNER JOIN sys.objects o ON o.object_id=d.id
                INNER JOIN sys.objects oo ON oo.object_id=d.depid
                WHERE o.type = 'P'

            )

            SELECT proc_schema,
                   proc_name,
                   table_schema,
                   table_name
            FROM stored_procedures
            WHERE [row] = 1
            AND ((@sp_name IS NULL) OR (proc_name = @sp_name)) AND ((@table IS NULL) OR (table_name = @table))
            ORDER BY proc_name,table_name

            --sp_help 'sys.objects'

This gives me the list below (partial):

The question, the bit I am struggling with is the following situation:

what if inside the stored procedure SP1 there is a stored procedure SP2 that uses the table @MyTable?

I want that to be listed as well.

I thought the solution could be:

  there should be a recursive cte to cater for procedures inside
  procedures that might use the table @table

but so far I have failed to implement it.

recursive CTEs should have 2 parts: anchor and UNION ALL.
how would I apply these two in the current CTE?

any hints?


texts: Does REDUNDANCY retention policy guarantee PITR from the earliest backup?

texts:
From ORACLE-BASE:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
…
The recovery window is used to make sure that there are always sufficient backups to recover the database to any point in time with the last 'X' days. Any backups that fall outside this time period can be deleted. The redundancy option simply states that any backups in excess of the latest 'X' are no longer needed.

In other words, setting the RMAN retention policy to a recovery window explicitly guarantees PITR to any time within the recovery window. It isn't clear from this description whether PITR is guaranteed using the alternative redundancy configuration — does RMAN retain all archivelog backups necessary for PITR to any point since the earliest retained backup?


texts: Is SERVICE_NAME forced to be unique per logical database within a network? Would it have sense the opposite?

texts:
I like to know your opinion on these two sentences to see if I understood correctly the concept.

Put RAC configuration aside, I am talking about logical databases, I understand RAC is like a group of hosts serving same logical database by means of analogous instances.

I am talking about the uniqueness of SERVICE_NAME in a network. I also understand that 2 SERVICE_NAME cant point one database, but I would like to know whether two different DATABASES with their unique DB_NAME and SID may and are likely to be useful in some way if they expose same SERVICE_NAME.


texts:
I've created an MsSQL database to serve a springboot application.

I want to grant something like the following permission to an admin user:

GRANT DROP ON TABLE springbootdb.* TO AdminUser

How can I assign this using Server Management Studio?


texts: Resolving high VLF count when database is part of an Availability Group

texts:
(SQL 2014 Ent Ed.) - do I need to break my Availability Group if I need to reduce the number of VLFs in a database that is participating in an AG?  Does anyone have the process documented?  I am thinking the process goes something like this:

Remove secondary nodes from the AG
Delete the database from the secondary nodes
Remove the database from the primary node of AG
Shrink the log file and regrow to proper size
Add the database back to the AG
Add the secondary nodes back to the AG and allow to sync

Thank you for any direction you can help me with!


texts:
I'm seeing the following errors repeatedly in the error log file for our production MySQL server:

  [Warning] Access denied for user 'root'@'remote.appserver.com' (using
  password: NO)

So, I know where the connection attempt is coming from, but I don't know how to track down the application trying to connect.  Is there any way to determine the application, program, client initiating the request?


texts: SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?

texts:
I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job.

I'm trying to find out if there are any tables that can be used to join the catalog.executions table to msdb.sysjobhistory. I need to relate which job called the SSIS package and then use the data in executions for my analysis.


texts: Find highest level of a hierarchical field: with vs without CTEs

texts:
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.

I have a self-referencing table with a primary key, id and a foreign key parent_id.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| parent_id  | int(11)      | YES  |     | NULL    |                |
| name       | varchar(255) | YES  |     | NULL    |                |
| notes      | text         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Given a name, how can I query the top-level parent?

Given a name, how can I query all of the id's associated with a record of name = 'foo'?

context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by Kattge et al 2011.

Here is an example of the relationships among ids in the table:

-- -----------------------------------------------------
-- Create a new database called 'testdb'
-- -----------------------------------------------------
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `testdb` ;

-- -----------------------------------------------------
-- Table `testdb`.`observations`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Add Example Data Set
-- -----------------------------------------------------

INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),
   (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);


texts: How does the T-SQL optimizer handle "and false"

texts:
In SQL SERVER 2008 I'm thinking about a query such as:

SELECT ...
FROM ...
WHERE ...
  AND @a=1
UNION
SELECT ...
FROM ...
WHERE ...
  AND @a=2

Given that @a is clearly the same value, and therefore either the first SELECT returns results or the second SELECT does, will the query optimizer identify one of the queries as false and not run it?  Would it be better for me to put them in an IF statement? The actual query is a bit more complicated than written of course, so the if would make it like 4 different queries, which would be a pain to maintain.

I'm hoping the optimizer is smart enough to see the 'and false' and not waste time with it.


texts: Why do I have 2 connections when im using 1 client?

texts:
The command:

select * from pg_stat_activity

shows all currently active connections.
The database is hosted by AWS RDS and I am accessing the database with pgAdmin 4, thus I expect one other connection than mine and that is the rdsadmin, but it seems like I have 2 connections.

Output:

Why do I have two connections when using one Client?


texts: In the same query INSERT, can two VALUES being NOW() return a different time?

texts:
I met a query similar to
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())

having column definitions
| Created   | datetime |
| Updated   | datetime |

Will in this case MySQL set the NOW() value to the current time and return that same values to both calls, or will that query run the (improbable) risk of having two slightly different times for Created and Updated?
Note: I can't change the field types.


texts: Using JSONB Column or Another Table to Save Relationsips

texts:
I tried to search here thoroughly but did not find any answers.

I have a PostgreSQL database, which has two main tables:

documents
users

These two tables have different relations. A user can:

like
bookmark to read later
save

... a document.

The question is how should I save these relations?

In my experience with MySQL, the obvious way was to create tables for these many to many relations, containing user_id and document_id.

But as we are using PostgreSQL and it has the amazing JSON support, we are thinking maybe the better approach is to have a user_document table, which contains user_id, document_id and a JSON column containing all the relations.

The JSON would be something like this:

{
   'follow' : {'date' : 1523517140, 'doesFollow' : 't'},
   'bookmark' : {'date' : null, 'doesBookmark' : 'f'},
   ....
}

I have little to zero experience with PostgreSQL and I don't know about the performance of querying on JSONB columns. And I don't know if this approach makes sense at all in PostgreSQL. But it seems OK and if there is nothing wrong with it, maybe it is preferable to the first, normal, approach.


texts: Merging multiple client-specific databases into a single one

texts:
We have multiple databases as various clients. All the databases have the same schema (or structure). Now we want to merge the databases into one. We DON'T want to merge their data. We want a new design that allows multiple clients while retaining as much of the original schema as possible.

Is the simplest way to do it as follows?

Create a client table
On each of the other relevant tables add a foreign key constraint referencing the client table

Note: The database schema ISN'T complex at all.


texts: Total of orders open on a given date for each date in a date range

texts:
This question is similar to Running total with count?, but please allow me to explain some further twists on the issue.

I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand seems to be the most promising in terms of speed.

What's different here, though, is I have to take into account two dates for a single item. So, given an OrderID item within a table of Orders, the OrderID has an Opened Date and a Closed Date. The Opened Date is always populated, but the Closed Date could be NULL.

OrderID  OpenedDate    ClosedDate
654554    12/1/2011     5/4/2012
678451    12/4/2011     3/2/2012
679565    12/8/2011     5/21/2012
701541    5/23/2012     NULL
...

I need to - efficiently - get back how many Orders had what we could term an "Open" status on any given date within a date range. The date range could span a couple of years.
(Yes, I do have a reference table of sequential dates.)

Date         CountOfOpenOrders
12/1/2011    175
12/2/2011    178
12/3/2011    195
12/4/2011    192
12/5/2011    191
...


texts:
I would like to write a procedure for order processing
Single Order can have multiple items, how can I process the order?

Consider below JSON as an example:

{
    "Order":
    {
        "OrderId":"",
        "OrderDate":"",
        "CustomerId":"",
        "Price":300,
        "Items":
        [
            {
                "ItemId":103,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":123,
                "Qty":2,
                "Price":100
            },
            {
                "ItemId":122,
                "Qty":2,
                "Price":100
            }
        ]
    }
}

Corresponding objects:

public class Item
{
    public int ItemId { get; set; }
    public int Qty { get; set; }
    public int Price { get; set; }
}

public class Order
{
    public string OrderId { get; set; }
    public string OrderDate { get; set; }
    public string CustomerId { get; set; }
    public int Price { get; set; }
    public List<Item> Items { get; set; }
}

I found two ways to process the above order

Prepare complete order items as a string like ItemId|Price|Qty,ItemId|Price|Qty,ItemId|Price|Qty (split functionality inside procedure)

@orderDate datetime = null,
@itemStr nvarchar(2000) = null, --(split functionality inside procedure)
@orderPrice decimal(18,2) = 0.00,
@customerId int = null

just find my approach based on my parameters declaration.

First order creation done separately and order under items will be inserted separately.
(programmatic insertion instead of native procedure call, no need to implement split functionality inside procedure).

Looking for best way of order processing (transaction management and performance are major constraints)


texts: Can you implement DR in cloud using Async AG, logshipping with an on-premise FCI solution?

texts:
What are my options for building DR in cloud if I implement FCI for HA on-premise.

The goal is to have a Hybrid on-premise HA + DR in cloud solution.

Can I configure Async AG/logshipping in Azure with an on-premise FCI?


texts: How to set statistics level of a table in postgres?

texts:
I know how to set statistics level for a particular column i.e
alter table table_name alter column column_name set statistics 100;

However, if I wish to set statistics level for a particular table or for a particular schema, is there is one liner command?


texts: Auto rollback Explicit transactions after X amount of time

texts:
Is it possible to have SQL Server auto rollback your transactions after a certain amount of time to avoid the blocking/locking that is caused? I have been guilty of this in the past and I hear it from many others that it is a common mistake.

For example:
I always use a block such as this When doing deletes/updates.

BEGIN TRAN
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This allows me to see how many rows are affected or perform a select on the data and then I can comment out the ROLLBACK which will automatically COMMIT the TRANSACTION.

However, there is a possibility of the of not having the COMMIT highlighted/selected which causes the transaction to hang. What I am looking for is a setting such as:

BEGIN TRAN 15 Seconds
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This will only keep the transaction open a max of 15 seconds.

If this is not possible, does anyone have a better workflow for this?


texts: How to get all records from one table and see if they are used in another without a sub query

texts:
I'm trying to write a SQL statement that pulls down all the records from one table and looks to see if they are used in another. SQL is not my strongest language, so I'm sure this is a pretty simple query. Here are my two tables:

tblOrderContracts
--------------------------------------
OrderContractID           | bigint
OrderID                   | bigint
UserContractID            | bigint
UserName                  | varchar
OrderContractDateCreated  | datetime
OrderContractStatus       | varchar

tblUserContracts
-------------------------------------
UserContractId            | bigint
UserName                  | varchar
UserContractName          | varchar
UserContractFileName      | varchar
UserContractDateCreated   | datetime
UserContractStatus        | varchar

A user can apply any number of contracts to an order, so what I need to do is get a list of all the contracts, and see if they are used in a particular order. Right now, I use a sub query to get the job done, but I don't like using sub queries if I don't need to.

SELECT uc.UserContractId, uc.UserContractName,
(SELECT oc.OrderID FROM tblOrderContracts as oc WHERE uc.UserContractID =
oc.UserContractID AND oc.OrderID = 466 AND OrderContractStatus =
'Active')
FROM tblUserContracts as uc
WHERE uc.UserName = 'vandel212' AND UserContractStatus = 'Active'

Ideally I'd like to get a result set that looks like this where if value is NULL in the OrderID column, that means it's not used:

UserContractId  |  UserContractName  |  OrderID
-----------------------------------------------
36              |  test              |  466
37              |  test2             |  NULL
38              |  test3             |  NULL

Any help is greatly appreciated. Thanks!


texts:
I am trying to do a temporal query similar to below:

MATCH (n:Event)
WHERE datetime(n.date) = datetime("2019-01-01")
RETURN n

But some NaT values have snuck into my n.date property. Is there any way to still do this query without having to update those nodes?

I can't remove the Date field from the nodes that have NaT as a value because the nature of the node the date is actually part of the node key.

TIA


texts: Poor performing subquery with date comparisons

texts:
When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes in a few milliseconds. With the subquery, the execution time is upwards of a minute.

For this query, the result must:

Include only those records within a given date range.
Include a count of all prior records, not including the current record, regardless of date range.

Basic Table Schema

Activity
======================
Id int Identifier
Address varchar(25)
ActionDate datetime2
Process varchar(50)
-- 7 other columns

Example Data

Id  Address     ActionDate (Time part excluded for simplicity)
===========================
99  000         2017-05-30
98  111         2017-05-30
97  000         2017-05-29
96  000         2017-05-28
95  111         2017-05-19
94  222         2017-05-30

Expected Results

For the date range of 2017-05-29 to 2017-05-30

Id  Address     ActionDate    PriorCount
=========================================
99  000         2017-05-30    2  (3 total, 2 prior to ActionDate)
98  111         2017-05-30    1  (2 total, 1 prior to ActionDate)
94  222         2017-05-30    0  (1 total, 0 prior to ActionDate)
97  000         2017-05-29    1  (3 total, 1 prior to ActionDate)

Records 96 and 95 are excluded from the result, but are included in the PriorCount subquery

Current Query

select
    *.a
    , ( select count(*)
        from Activity
        where
            Activity.Address = a.Address
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc

Current Index

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON [dbo].[Activity]
(
    [ActionDate] ASC
)
INCLUDE ([Address]) WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)

Question

What strategies could be used to improve the performance of this query?

Edit 1
In reply to the question of what I can modify on the DB: I can modify the indexes, just not the table structure.

Edit 2
I have now added a basic index on the Address column, but that didn't seem to improve much. I am currently finding much better performance with creating a temp table and inserting the values without the PriorCount and then updating each row with their specific counts.

Edit 3
The Index Spool Joe Obbish (accepted answer) found was the issue. Once I added in a new nonclustered index [xyz] on [Activity] (Address) include (ActionDate), the query times went down from upwards of a minute to less than a second without using a temp table (see edit 2).


texts:
So I have a complicated INSERT query which puts information into a relational table based on information pulled from another table and with given variables.

Here's the query:

INSERT INTO relational (class_id, teacher_id, student_id)
VALUES (
    (SELECT `id`,
        (`name` = "John Smith") as exact_score,
        (`name` RLIKE "[[:<:]]John Smith[[:>:]]") as reg_score,
        (MATCH (`name`) AGAINST ("John Smith")) as match_score
    FROM `teachers`
    WHERE (`title` RLIKE "[[:<:]]John Smith[[:>:]]" || MATCH (`title`) AGAINST ("John Smith"))
    ORDER BY
        CASE WHEN exact_score = 1 THEN exact_score END DESC,
        CASE WHEN reg_score > 1 THEN reg_score END DESC,
        CASE WHEN reg_score = 0 AND exact_score = 0 THEN match_score END DESC),
1, 1);

The regex is required because the name might be Mr. John Smith or John Smith PH.d, and the match is required because the name might be jon smith When the stored name is actually just John Smith.

The select query yields the results I am looking for 90% of the time, but the only real column that I need from it is id. The problem I'm running into is with the INSERT's expected column count vs. the column count that is returned by the query.

Right now the INSERT basically looks like this:

INSERT INTO relational (class_id, teacher_id, student_id) VALUES ({{returned id (desired)}}, {{exact score (undesired)}}, {{reg score (undesired)}}, {{match score (undesired)}}, 1, 1);

The last 1, 1 is provided by variables.

My question is, is there a way to select columns for the sake of ORDER BY, but then to omit them from the results?


texts: Make "NOT NULL" default in Postgresql 9.2

texts:
I use NOT NULL a lot when creating columns.

Is there a way to make this default when defining columns?  In other words, make column is_nullable default to NO. (I realize this would make it harder for me to set it to YES later one.)


texts: CLUSTER using sequential scan followed by sorting

texts:
I'm tring to understand the meaning of the following clause from PostgreSQL 12 docs

  CLUSTER can re-sort the table using either an index scan on the
  specified index, or (if the index is a b-tree) a sequential scan
  followed by sorting.

  When a sequential scan and sort is used, a temporary sort file is also
  created, so that the peak temporary space requirement is as much as
  double the table size, plus the index sizes. ... you can disable this choice by temporarily setting
  enable_sort to off.

Clustering is meant to physically reoder the table based on the index information. So what is going to happen if I set enable_sort=off? Will it still manage to reoder the data properly? If yes, then what is the benifit of having the enable_sort option in the play here at all?


texts:
I've been trying to setup lower_case_table_names for ages in MySQL 8.0 without success. What is the procedure to set this up in 8.0?

In previous versions of mysql I was able to override the variable in /etc/mysql/mysql.cnf by adding the following lines:

[mysql]
lower_case_table_names = 1

But when I try to restart the server / service mysql does not restart.
In the past all mysql servers had this setting working fine I just don`t know how to do the same in 8.0.


texts: 'Incorrect datetime value: for column 'last_login' at row 1'

texts:
i am getting below error:

Last_SQL_Errno: 1292

    Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. Query: 'INSERT INTO app_player (creation_date, last_login, removed_date, app_id, user_id) VALUES ('2013-03-10 02:00:00', '2013-03-10 02:00:00', null, 400, 77870956)'
1 row in set (0.00 sec)

Table structure:

mysql> show create table app_player\G
*************************** 1. row ***************************
       Table: app_player
Create Table: CREATE TABLE `app_player` (
  `app_id` int(10) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `creation_date` datetime NOT NULL,
  `removed_date` datetime DEFAULT NULL,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`app_id`,`user_id`),
  KEY `idx_creation_date_app_id` (`creation_date`,`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql veriosn is : 5.1.52

mysql> show global variables like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| innodb_stats_auto_update                | 1                 |
| innodb_stats_update_need_lock           | 1                 |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_log_update                          | ON                |
| sql_low_priority_updates                | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+


texts: Crow's foot notation--what is the difference between "one" and "one-and-only-one" regarding relationship cardinalities?

texts:
I'm trying to figure out the difference between the single-bar and double-bar cardinality indicators as in the figure below:

Some sources seem to use one notation while others use the other. I can't tell if they actually refer to different kinds of relationships of if the difference is only in notation and they mean the same thing.


texts: What is refreshing my table in SQL Server?

texts: Is there any way to figure out what process or trigger is refreshing a table in SQL Server? I have this table that I thought is a standalone table and all the records I manually inserted were truncated overnight. So wondering if any of you can give details.


texts: What permissions does db_owner not have? (In SQL Server 2008)

texts: Can you list the permissions that db_owner cannot do.


texts:
I am having a problem with replicating a table, and I finally tracked it down to a consistency error on the source table, but I am stumped as to what is causing the consistency error.

I ran DBCC Checkdb and found the following error on the table

  Msg 2570, Level 16, State 3, Line 1 Page (1:5217772), slot 16 in
  object ID 1038014829, index ID 1, partition ID 349502316544000, alloc
  unit ID 349502316544000 (type "In-row data"). Column "MHGPTX" value is
  out of range for data type "varchar".  Update column to a legal value.

I used DBCC PAGE to what row within the table was causing the problem, and tracked down the offending row.

  Slot 16 Column 6 Offset 0x28 Length 150

  MHGPTX = [Error converting to string (length 150 bytes)]

This is where I got stuck, the data in the field looks fine, the data is well under the max size for the column, I can query the row, etc.

I found a few references online to similiar problems, where the problem was resolved by updating the row using the same data, so I tried that, but with no change.

Any advice would be greatly appreciated


texts: TempDB default number of files in SQL Server 2016

texts:
Noticed the docs for SQL Server 2016 CTP2 state "by default, setup adds as many tempdb files as the CPU count or 8, whichever is lower."
https://msdn.microsoft.com/en-us/library/bb510411(v=sql.130).aspx

But I always thought that the "correct" ratio would be half the number of CPU cores, for boxes under 8 cores that is.

Should this be taken as the official recommandation from Microsoft and even applied to 2012/2014 ?


texts: Can I generate a list of all combinations between two columns

texts:
table 1
|id|name  |
------------
|1 |Bob   |
|2 |Frank |
|3 |Paula |

Table 2
|id|item|
---------
|1 |a   |
|2 |b   |
|3 |c   |

i need to write a query to show me all the combinations of the two tables as below:

result
|name|item|
-----------
|bob |a   |
|bob |b   |
|bob |c   |
etc

I am currently using mysql any help would be greatly appreciated


texts:
Is it possible to join new node SQL in Failover cluster with different Edition?
As example:

Cluster Failover running based on SQL Enterprise Evaluation Edition add a new node based on SQL Standard Edition


texts: Get row values only if a particular column's value does not exist

texts:
I have two tables in my database students:

GRADE_REPORT:

+----------------+-------------+------+
|Student_No .    | SectionId   | Grade|
+----------------+-------------+------+
| 11             | 85          | B    |
| 17             | 112         | B    |
| 17             | 119         | C    |
| 17             | 135         | A    |
| 8              | 85)         | A    |
| 8              | 92          | A    |
+----------------+-------------+------+

STUDENT:

+----------------+-------------+------+--------+----------------+
| Studentnumber  | name        | Classification| DepartmentName |
+----------------+-------------+---------------+----------------+
| 17             | Bill Smith  | 1             |      CS        |
| 8              | Bob Brown   | 2             |      CS        |
| 11             | Nick Fiener | 4             |      MATH      |
+----------------+-------------+------+-----+---------+---------+

I am trying to find retrieve the student number, name and major departments of all students who do not have any grade A in any of their courses. So I am expecting the following when I run the query:

+----------------+-------------+----------------+
| Studentnumber  | name        | DepartmentName |
+----------------+-------------+----------------+
| 11             | Nick Fiener | MATH           |

This is my statement so far:

SELECT students.STUDENT.StudentNumber, students.STUDENT.name,
  students.STUDENT.DepartmentName
FROM students.GRADE_REPORT
INNER JOIN students.STUDENT
ON students.GRADE_REPORT.StudentNo = students.STUDENT.StudentNumber
WHERE
( (students.GRADE_REPORT.Grade = "B"
   OR
   students.GRADE_REPORT.Grade = "C" )
   AND  students.GRADE_REPORT.Grade != "A")
GROUP BY students.GRADE_REPORT.StudentNo;


texts:
I have been using mysql master:master replication and was always able to check the status of replication using this command:
mysql > show slave status\G
I was able to see this in the show slave status command:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
The seconds_behind_master always let me know if I was behind on one of my servers.

I am not using galera and am not sure how to verify if everything is up to date.
I have come across this article "Monitoring Cluster Status"
I realize I can use this command to see how many nodes are in the cluster:
SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

But I am not sure if this command if good enough to tell whether or not they are exactly up to date:
SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';
This command currently show synced but I cannot tell if this is the best way to tell if I am good to go.


texts: Auto_Increment After deleted data from the table

texts: How can I reset the auto_increment of a field? I want it to start counting from 1 again after I delete all the data.


texts: Oracle returns an error ORA-01031: insufficient privileges

texts:
Oracle returns an error "ORA-01031: insufficient privileges"
When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
Linux version: SUSE Linux Enterprise Server 11 (x86_64)

I know that Oracle uses OS authentication when running sqlplus / as sysdba, I also checked sqlnet.ora carefully and it contains SQLNET.AUTHENTICATION_SERVICES = (NTS).

I also changed the oracle user's group, oracle user absolutely belongs to dba group because when I run command id the following information returns:

oracle@linux-mage:~> id
uid=103(oracle) gid=106(dba) groups=105(oinstall),106(dba)

Another information is I can logged on through sqlplus sys/oracle123 as sysdba, but I can not shutdown the instance:

oracle@linux-mage:~> sqlplus sys/oracle123 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 5 16:29:57 2011

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

Connected.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL>

i ran strace -o trace.out sqlplus "/ as sysdba" and get the following output message.
the strace message is too big. so i paste it into pastebin. please visit this link

http://pastebin.com/YpjVeG8u

ipcs -s and ipcs -ls information:
    oracle@linux-mage:~> ipcs -s

------ Semaphore Arrays --------
key        semid      owner      perms      nsems

oracle@linux-mage:~> ipcs -ls

------ Semaphore Limits --------
max number of arrays = 200
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 100
semaphore max value = 32767


texts: Backfill new column with values based on foreign key

texts:
I'm new to RDS and am looking for help in updating a new column with ascending values based on a FK.

Can I and should I be doing this as a sequence of SQL commands that calculates my business logic on server-side?  This seems plausible to me because the business logic (ascending based on FK) seems straightforward.  Or should I be running client code to read rows, calculate business logic on client, then write back to db?

Would I be using function to achieve this?

Specifically, I am adding a new column ordinal to my devices table.  The default value is 0 and I would like to update that column to instead count ascending for a particular user.  So, if there are 3 rows for a given user in devices, those 3 rows would respectively have ordinal set to 1, 2, and 3.

CREATE TABLE devices (
    id character varying PRIMARY KEY,
    user_id uuid REFERENCES users(id) ON DELETE CASCADE,
);
ALTER TABLE "devices" ADD COLUMN "ordinal" integer DEFAULT 0 NOT NULL;

I am running PostgreSQL 9.6.6

Thank you

I have made some headway on a function.  For the update step when I set ordinal, is there any way to set the column through the context of the current iteration?  I.e. something like current.ordinal = new_val?

Inspiration: Looping Through Query Results

CREATE OR REPLACE FUNCTION backfill_device_ordinal(uuid) RETURNS integer AS $$
DECLARE
    device RECORD;
    ordinal INTEGER;
BEGIN
    ordinal = 1;
    FOR device IN SELECT * FROM devices WHERE user_id = $1 LOOP
        -- Now "device" has one record from "devices"
        RAISE NOTICE 'Updating device ordinal: user=%, ordinal=%...', device.user_id, device.seat_ordinal;

        /* is this optimal? */
        EXECUTE format('UPDATE devices SET seat_ordinal = %I WHERE user_id = "%s" AND id = "%s"', ordinal, device.user_id, device.id);

        ordinal = ordinal + 1;
    END LOOP;

    RAISE NOTICE 'Done updating device ordinal.';
    RETURN ordinal;
END;
$$ LANGUAGE plpgsql;

SELECT * from get_next_device('00000000-1111-2222-3333-444444444444')