texts: SQLite database: many-to-many optimization (simple but large example) without IDs available?

texts:
I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of query, and minimize size - but sometimes this feels like I'm asking for too much! Using the typical Students:Classes table as an analogy, my table has the following constraints:

Student Table: Student Names, Text, (6 billion unique entries)
Class Names Table: Class Names, Text (100,000 unique entries)
Lookup needs to be fast on Student Names so an index, unique, or primary key constraint needs to be used
Size needs to be manageable. Both examples below are fine space-wise but much larger would be a problem
Must use SQLite for this problem, but if the answer can be solved more easily with another DB system, I'd like to hear about it

I've tried to implement a few optimizations, including the executemany function for insertions, beginning transactions and committing in large batches, and some PRAGMAS like synchronous = off and journal = DELETE, but they seem to have minimal impact.

In most examples, a bridge table is used to relate a unique integer ID in Student Table (student_ID) to a unique (class_ID) which I implemented with the following pseudo code. Since I do not know the student IDs/class IDs ahead of time, I derive them from the rowIds:

1: Unique constraints and bridge/junction table, selecting rowID for studentID/classID after they've been inserted

CREATE TABLE StudentTable (StudentName TEXT UNIQUE, ID INTEGER PRIMARY KEY) # ID = alias to rowid
CREATE TABLE ClassTable (ClassName TEXT UNIQUE, ID INTEGER PRIMARY KEY)
CREATE TABLE StudentClasses (studentID INTEGER, classID INTEGER, PRIMARY KEY (studentID, classID) WITHOUT ROWID)

(For each row):
INSERT OR IGNORE INTO StudentTable (StudentName) VALUES (?) , Student_Name #All student names
INSERT OR IGNORE INTO ClassTable (ClassName) VALUES (?) , Class_Name #all class names
SELECT ID FROM StudentTable WHERE StudentName = "Student_Name" #multiple names in row sometimes, so I select all here
SELECT ID FROM ClassTable WHERE ClassName = "Class_Name" #same as above
INSERT INTO StudentClasses VALUES (?,?) , student_id, class_id #insert all relationships from rowid derived IDs

#Results:
6 billion student names
100,000 class names
8 billion student:class relationships in StudentClasses

Run time: Very slow due to unique constraints on Student Names and Class Names (estimated to be ~1 week, aborted before completion)
Size: Minimal (constrained to unique entries on text, and integers for relationships)

2: Single table with index on Student Names

#(Simplified)
CREATE TABLE StudentClasses (StudentName TEXT, ClassName TEXT)
#Insert all relationships
INSERT INTO StudentClasses (StudentName, ClassName) VALUES (?,?) Student,Class
#After all insertions:
CREATE INDEX StudentIndex ON StudentClasses(StudentName)

#Results:
8 billion relationships between student and classes
Additional index on Student Names

Run time: 8x faster than example 1 (~15 hours)
Size: 1.5x larger than example 1 (additional text (?) created with indexing the text field)

Aside from these two approaches, is there something I'm missing? In particular, I understand that a normalized relationship with the junction table being a series of integer IDs is the most space efficient, but it is so incredibly slow to construct (at least the way I'm doing it) that I have to avoid it.

I'd like to hear about any better approaches you can think of. For my particular project, I'm stuck with SQLite, but if you know of other DB managers that are better at handling this situation, I'd like to learn about the alternatives.

Thanks for any and all insight into this!


texts: How to set 'Enable identity insert' for all the tables at once during importing data in SQL Server?

texts:
I am trying to copy a database from a remote server. I don't have access to its backups or file system. My destination database on my dev machine needs to get all the data plus the same tables structures including primary keys with identity settings and the rest of the constraints.

The Import/Export facility has the option to set the identity insert as shown below but I have to do this manually per table. I have over 300 tables to copy and most of them have identity primary keys. This is going to be very painful process doing the copy multiple times.

Is there a way to set this identity insert setting all at once for all the tables? Or some other reliable way of copying databases in such a setup?
I am using SSMS 7.3 against SQL Server 2014.


texts: SQL Server File or Filegroup Creation Date

texts:
Is there a way to find out when a File was added to a filegroup in SQL Server. Or how to determine when a filegroup was created?
I've tried querying the sys tables looking at the lsn information and the file_guid.

thanks


texts: Unexplained sustained space used in the TempDB log file

texts:
A few days ago the amount of used space in both the tempdb data and log files started growing rapidly.  After 30 minutes, the tempdb log remained around 99% space used for about 7 hours.  Towards the end of the 7 hour period, I:\Databases\templog.ldf: Operating system error 112(There is not enough space on the disk.) encountered. was logged followed by about 30 instances of The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' over a 3 second period, and then suddenly the tempdb log file returned to 1% used space.  This usage data is recorded in logs every 3 minutes via a SQL agent job.
I also have a SQL agent job that runs sp_WhoIsActive every 1 minute and captures the output.  I don't see anything that stands out in this data that explains what was consuming or holding space in the tempdb.  There are some maintenance jobs running for several minutes at a time, typically running no longer than 5 or 10 minutes each.  Even if one of those jobs or another session used up tempdb log space, I would expect the space to be reclaimable after any transactions complete or at least after the session ends.

It's a 200 GB drive dedicated for tempdb data and log files.
Both the data and log files grew during this time until there was no more space on disk.
There's a tempdb_2.ndf file I don't recall seeing before (maybe it's always been there...).  tempdb.mdf and tempdb_2.ndf are the same exact size, around 29 GBs each.  templog.ldf is using 137 GBs.
Running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64)

I'm trying to understand what happened and how to prevent it again.

Is it possible for tempdb log space to not be reclaimable for hours even if there are no sessions running for anywhere near that long?
What caused tempdb log space used to suddenly free up 3 seconds after the above error messages (not enough space on disk and tempdb transaction log is full)?
Is sp_WhoIsActive logging not enough to capture what process was holding onto the tempdb log space, what other additional logging can I do?
If the tempdb log space used was actually reclaimable but a glitch in SQL Server prevented it from clearing out, is there a T-SQL command I can run to try and free up available space in the log?

Any thoughts or suggestions are very appreciated.


texts: (Beginner) MYSQL: What can I do about this where statement?

texts:
Suppose I have some table named tableA and column1 in tableA has some null values and strings.
Also suppose I had created some temporary table named tempTable that has only 1 column. And that 1 column contains null values and strings as well.
I have a statement similar to the following:
select *
from tableA
where tableA.column1 in (select * from tempTable);

However, I want it to also select the contents when tableA.column1 is null. The in operator ignores that case.
Is there a different operator that considers null?
Or is there anyway I can apply certain constraints in the where clause under true or false conditions.
Like for example in the incorrect statement below:
select *
from tableA
where (if condition1, then tableA.column1 =.... elseif condition2 then tableA.column1 =....);


texts: Recommended innodb_file_per_table value for AWS MySQL RDS database

texts:
We have the following scenario: Our databases are hosted on AWS MySQL 5.6, and we are planning the following migration: we currently have 10 distinct instances with about 50 databases split between them, and we desire to migrate to a single instance containing all 50 databases. Each database has about 600 tables. All bases have the same structure, although the volume of data and traffic is different for each one of them. The main purpose of the change is to stack up the resource consumption curves of our different clients, setting up a server properly with fewer resources than the sum of the 10 current instances.

A point that is not standardized between instances, and what I need to define at this point, is the innodb_file_per_table parameter (which will define whether to use a shared tablespace, or individual tablespace for each table). I could not find enough information to decide on it.

The materials I've read talk about advantages for disaster recovery, however our AWS environment already has adequate redundancy, so I want to decide according to the performance (latency, IOPS, amount of open connections, ...). Our application does not perform so much DROP TABLE operations, and we are aware of the specifications of memory, CPU, network, etc., necessary for our centralized instance. Our focus is mainly quick response for our clients.

Based on the "Potential Disadvantages of File-Per-Table Tablespaces" section of the documentation at https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html, using a shared tablespace can bring more speed in writing operations.

Further information is that after we complete the unification of the databases and make some adjustments, we have plans to upgrade from MySQL 5.6 to MySQL 5.7. Thus, the issue of the limit of 10000 tables for instances above 200 GiB is not at all relevant.

We thank you in advance for any suggestions on the best option to adopt, or the indication of materials that help in this decision.


texts: How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

texts:
I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time?

The query at issue is the following one:

  SELECT id_empresa,
         referencia,
         archivo,
         status
    FROM Outbound_call_files
   WHERE movido=0
     AND status NOT IN('Peticion')
ORDER BY id_empresa, status;

The execution plan is:

| id | select_type | table               | type | possible_keys | key           | key_len | ref   | rows    | Extra       |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | Outbound_call_files | ref  | motivo_status | motivo_status | 1       | const | 2561294 | Using where |
+----+-------------+---------------------+------+---------------+---------------+---------+-------+-

SHOW CREATE INDEX returns the following:

| Outbound_call_files |          1 | motivo_status      |            1 | movido      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            2 | id_empresa  | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| Outbound_call_files |          1 | motivo_status      |            3 | status      | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |

I can see the cardinality is too low.

The structure and constraints of this table are shown below:

CREATE TABLE `Outbound_call_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_empresa` int(11) NOT NULL,
  `id_campana` int(11) NOT NULL,
  `referencia` text NOT NULL,
  `movido` tinyint(4) NOT NULL,
  `archivo` varchar(50) NOT NULL,
  `status` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_empresa_campana` (`id_empresa`,`id_campana`),
  KEY `motivo_status` (`movido`,`id_empresa`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10144111 DEFAULT CHARSET=latin1

Executing profiling, this was the result:

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000070 |
| checking permissions |  0.000008 |
| Opening tables       |  0.000019 |
| System lock          |  0.000010 |
| init                 |  0.000030 |
| optimizing           |  0.000018 |
| statistics           |  0.000090 |
| preparing            |  0.000022 |
| executing            |  0.000004 |
| Sorting result       |  0.000006 |
| Sending data         | 12.512944 |
| end                  |  0.000017 |
| query end            |  0.000006 |
| closing tables       |  0.000011 |
| freeing items        |  0.000019 |
| logging slow query   |  0.000004 |
| logging slow query   |  0.000067 |
| cleaning up          |  0.000006 |
+----------------------+-----------+

Note: The database server has 150G RAM and the buffer pool is configured in 100G.


texts: unable to connect to oracle in linux terminal in oracle VM ,TNS listner not connected

texts:
i installed oracle VM , new to oracle vm.
Can some help me in connecting to oracle in Linux termial  and TNS listner is not responding with IO failure on sql developer when pressing test button .please sugggest how to resolve the issue.

https://infoallsite.wordpress.com/

you can see the images here :


texts: SQL Server and Network considerations to transfer backup file to a shared folder

texts:
I'm facing with a situation where backups are being transferred manually to a Shared Folder and my goal is to optimize all of this.

What I have to deal with is:

8 Windows 2008 R2 Servers
2 Windows 2003 Servers
All databases are MS SQL Server 2008
1 Windows 2008 R2 Server explicitly for backup
All Servers are on local not on Domain

What I've tried so far:

I had a query that copies the backup to a Network Shared Folder hosted by the Backup Server but it fails to run since the SQL Server service is executed by a different user (sqlservice), I login to MS SQL Server with (sql_admin) and my Windows login is (DBAJhonDoe)
I made a batch file and try to accomplish the same but nothing changes. It still fails for the same reason.
I made a login for Sql Server with my Windows user with sys_admin privileges but the same thing happens.

Considerations so far:

The Backup Server has the same user (DBAJhonDoe) with the same password
All the Servers with Sql Server has the (DBAJhonDoe) user as well

Since I have to make this works, I have to make a plan and present it to the IT Department, so I need to be specific for what I need.

What I believe is I need to change a few things:

Make the Servers connect to the Domain and create a Domain Group named Servers.
Add the Domain group Servers to the users allowed for the Shared Folder.
(I don't know if it's necessary) Make the MS Sql Server service run by one standard user in all the Servers.
Restart the Server and place the script in a Job.

Is there anything I am missing?
Is there anything I can be doing better?
Is there something I can make to be even more reliable since my Goal is to accomplish a self driven backup server that stores backup from all other Servers.


texts:
I installed MaxScale 2.4.4 with Galera Cluster. I can access to galera with maxscale user but I can't login with other users to maxscale. I created a user in galera and another user with same username with maxctrl but it return Access denied error. How I can create users for applications in maxscale 2.4.4.

maxscale.cnf

[maxscale]
threads=1

[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
password=qwe123
disable_master_failback=1

[Galera-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
password=qwe123

[Galera-Listener]
type=listener
#router=readwritesplit
service=Galera-Service
protocol=MySQLClient
port=3306

[Write-Service]
type=service
router=readconnroute
router_options=master
servers=server1, server2, server3
user=maxscale
password=qwe123

[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=server1, server2, server3
user=maxscale
password=qwe123

#[Splitter-Service]
#type=service
#router=readwritesplit
#servers=server1,server2,server3
#user=maxscale
#password=qwe123

#[Splitter-Listener]
#type=listener
#service=Splitter-Service
#protocol=MariaDBClient
#port=3306

[CLI]
type=service
router=cli

[CLI-Listener]
type=listener
service=CLI
protocol=maxscaled
address=0.0.0.0
port=6603

[server1]
type=server
address=192.168.122.93
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.122.17
port=3306
protocol=MySQLBackend

[server3]
type=server
address=192.168.122.13
port=3306
protocol=MySQLBackend

I found enable_root_user=1 for login to maxscale with root user but I don't know where should I add it;

maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
│ Server  │ Address        │ Port │ Connections │ State                   │ GTID │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server1 │ 192.168.122.93 │ 3306 │ 0           │ Master, Synced, Running │      │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server2 │ 192.168.122.17 │ 3306 │ 0           │ Slave, Synced, Running  │      │
├─────────┼────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
│ server3 │ 192.168.122.13 │ 3306 │ 0           │ Slave, Synced, Running  │      │
└─────────┴────────────────┴──────┴─────────────┴─────────────────────────┴──────┘

maxscale runs at 192.168.122.222 and I want all applications connect to this address with thier own user;


texts: How "frequently used data" and cache work in databases

texts:
I know that the databases can store the most used data in the RAM memory for fast access, but when, and what data ?

1- The query is executed multiple times in this table

id, name, gender
-- ----- -------
1 , Bill, Male.
2 , Todd, Male.

SELECT * FROM people WHERE gender = 'Male';

What is the minimum value it can save in memory ( column, row, page, table ) ?
Having PK or FK make any difference when its can be save ?
If i UPDATE the table or row, the memory have to be read again ?

2 - And a query with JOIN in tables like this ?

people
id, name, gender_id
-- ----- -------
1 , Bill, 2
2 , Todd, 2

people_gender
id, gender
-- -------
1 , Female.
2 , Male.

SELECT id, name, gender FROM people p JOIN people_gender pg ON p.gender_id = pg.id

The database read once and save the value of people_gender.id, then it can read only 4 bytes more 2 bytes in people.gender_id = 6 bytes OR it have to JOIN and read every row, even when the value is well know (1+4+1+4 = 10) ?
The JOIN is worth of it or the first case is better for performance ?

The true base can be in Sql Server up to 16 GB RAM or Postgresql down to 1 GB RAM, it have a column that save a state of an event like WORKING, WAITING, STOPPED and CANCELED ( actually not in English, five states with 8-10 chars each ), its so common updating and/or returning this data in the table Event, that i starting by testing performance things here.

I working in a software, that growing a lot in last years and now is facing performance problems, this is work for the DBA ... but we do not have one :D.

My first question in the dba meta, sorry any mistaking, any tip is welcome.

Thank you in advance.


texts: Domain Admins can't connect to SQL Server

texts:
I have a Windows Server 2012 R2 domain controller and a database server (W2K12R2/SQL Server 2012).

As allowed usergroup I have given the group "domain-admins" sysadmin rights.

But somehow any domain-administrator can't connect to that SQL Server, but when I add the domain-administrator user directly as allowed user it does work.
The active directory does work. RDP-Login works. Am I missing something?

Does anybody know how to grant access to the whole group "domain-admins"?

The error message is:

Servername: SRV-DB
Error Number: 18456
Schweregrad: 14
Status: 1
Line of Code: 65536

In the event log it says (translated):

  Error checking the token based server access with infrastructure failure.

State is "1" . I log on to that db-server with RDP as an domain-admin. I have to explicitly start SSMS with "run as administrator". Then I can access the "localhost". If I leave out starting SSMS as admin then I can't do that. I thought being in the group "domain-admins" would be enough.


texts: How to call stored procedure inside trigger?

texts:
I have made stored procedure that will update product quantity as per product id.

That works fine for me.

Following is code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updatequantity`(IN id int, IN qty int)
BEGIN
    update product_master
    set prod_qty = (prod_qty - qty)
    where prod_id = id;
END

it works greatly for me. Now I want to put trigger on product_master table that will first check that enough quantity is there or not in stock ?

Suppose order quantity is 100 and total available product quantity is 50 then procedure should not be called instead some error message should be displayed.
If enough quantity is there then only stored procedure should be called.

Anyone have idea that how to proceed for that ?

Thank You.


texts:
DC1: (servers are in WSFC): DB_Server1 and DB_Server2

DC2: DB_Server3

DC1 and DC2 servers are located in different geo locations. Is it possible to configure SQL 2017 Always-On/SQL 2017 Distributed Always-On in above environmnet?
As per my understanding, the servers should be in WSFC, but DC2 DB server is not in cluster, its standalone machine. If it's not possible to configure SQL always-On in above env, please suggest which SQL HA/DR solution i can use...


texts: Filtered index is not used when variable in WHERE condition

texts:
Why MS SQL Server refuse using supporting filtered index in this scenario?

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

Everything goes as expected when using literals.

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';


texts:
So I inherited a server that has serious write latency issues according to a script provided by glen berry, I also noticed that the tempdb files are spread across multiple drives.

What could be the benefits of having Tempdb.ndf files on different drives? is there any? also the highest latency comes from the drives that host those tempdb files


texts: Update one column based on count of records of grouped value in another column

texts:
Given a table

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 );

INSERT INTO TABLE_ITEMS
VALUES
 (0,1), (1,1), (2,1), (3,1), (4,1),
 (0,2), (1,2), (2,2), (3,2), (4,2), (5,2),
 (0,3), (1,3), (2,3);

and so on...

I need to know how to run a query which will update the rows with 0 as the serial_num to be the next number in the serial_num sequence for the given item_group

The result will look like this:

   those were the zeroes
   ↓
(**5**,1) (1,1) (2,1) (3,1) (4,1)
(**6**,2) (1,2) (2,2) (3,2) (4,2) (5,2)
(**3**,3) (1,3) (2,3)

I want to update an item in serial_num=0 to be the last serial_num plus 1 (if the last one was 70, it needs to be 71).

How can I do it?


texts: How to access anonymous user account in MySQL?

texts:
I have followed the MySQL on Ubuntu 18.04 installation tutorial on Digital Ocean, and during the process of running mysql_secure_installation noticed the following:

  By default, a MySQL installation has an anonymous user,
  allowing anyone to log into MySQL without having to have
  a user account created for them. This is intended only for
  testing, and to make the installation go a bit smoother.
  You should remove them before moving into a production
  environment.

Question is, how do I access this anonymous user account ?  I've installed the database server simply for course. The machine is on LAN, there's no critical data on it, so I understand in production removing anonymous user is desirable but on personal machine for  learning it'd be nice just have one simple account to practice SELECTs and JOINS.


texts: How do I create a table best suited for storing address information

texts:
I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this

 Table Address
    ID | Address Line1 | Address Line2 | Address Line3| City | Zip | ..|

The queries that I expect from my client may contain AddressLine1 and Zip or AddressLine1, Zip and City in which case the above table structure would not the most ideal. I don't want to have to scan the entire table for every query. I know for sure that the client queries will at least contain zip or city, am thinking that I have to create and indices on Zip and City. Should I consider anything else in my design?

I considered partitioning the table, but am going to start with a blank database and rows get added over a period of time, am not sure if I can dynamically create those partitions based on City or zip.

Also my expectations is that the database has to be optimized for reads more than writes

Any ideas?


texts: Resume job 'A' at some step till job 'B' is completed

texts:
I have two SQL Agent jobs 'A' and 'B'
Job 'A' has 10 Steps and it starts at 5:00 Am as per schedule,
job 'B' has 5 Steps and it Starts by using trigger in step 3 of job 'A',
job 'B' should be Completed before starting of step 8 in job'A',
But sometimes Job 'B' is not completed when step 8 in job'A' starts.

So, my requirement is, i want to add one step before step 8 in that i want to check the status of job'B' when job'B' is completed then step 8 should start else it should not start and it should wait till job'B' completed

How can i achieve this?

Thank you.


texts: Multiple sub query in insert statement for postgreSQL

texts:
I am trying to insert into table with multiple queries. And its not working, here is my work.

    insert into post_topics_ta (id,post_id,topic_id,confidence_score)
SELECT * FROM post_topics WHERE post_id in
(SELECT id FROM posts WHERE date_posted::date BETWEEN '2019-12-30' and '2020-01-26')

But this is not inserting any values into the target table. Could anyone please let me know how to achieve this?

Thanks in advance!!!


texts: Using only vanilla PostgreSQL, how do I predict a future number based on existing data?

texts:
I have a table called "events", with a timestamp column.

Based on that timestamp column's current records, I want to determine when the next record in the future is likely to occur.

I don't want to install any software, extensions, or pay for any APIs or register any accounts. I'm talking about the most simple and easy-to-express method to get a fair estimate of which future timestamp will correspond to the next record.

Is there some kind of basic mathematical formula for this? The expression should not know anything about what kind of data is in the table, nor care about it, and just use some kind of math such as taking the sum() of all the existing timestamps and dividing them by the position of the moon or something.

Anything which will produce a seemingly sensible estimate. "For entertainment purposes only", rather than life-critical medical decisions.


texts: Unable to drop table/database when using innodb_force_recovery = 3

texts:
I'm attempting to follow several guides to recover a corrupted database, by setting innodb_force_recovery to 3, dumping the database, dropping the database, and then importing it back in.

I'm able to dump the database, and importing it to a different server seems to display all tables and their data correctly.

Though when trying to drop the corrupted database, I'm shown "Lost connection to MySQL server during query". I'm able to perform other queries, such as SELECT COUNT(1) FROM (as per this guide), but drop/truncate any table or the entire database shows the error.

Setting innodb_force_recovery to anything other than 3 (higher or lower) does not allow me access to mysql.

I should mention that the db is ~20gb, and some tables are large (up to ~200m rows). When trying to run the SELECT COUNT(1) FROM as mentioned before, on the largest tables, after some time I'm getting the same "Lost connection to MySQL server during query" error. Also potentially of note, one of the large tables shows that error immediately, while the others take 20-30s to show the error.

Any advice is appreciated, thanks in advance.


texts:
Hope this is not as stupid a question as I think it is. But I'm in a loop and unable to find a solution.

I installed Postgres in centos machine. The installation worked fine (apparently). Now I have 3 Linux users - root, admin and postgres. In order to allow all connections I updated the pg_hba.conf file with necessary changes. Now, if I try to restart (from root or postgres user), I get below error:

-bash-4.2$ systemctl start postgresql-9.5.service
  ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
 Authentication is required to manage system services or units.
 Authenticating as: Admin (admin) Password:
 ==== AUTHENTICATION COMPLETE === Job for postgresql-9.5.service failed
 because the control process exited with error code. See "systemctl status
 postgresql-9.5.service" and "journalctl -xe" for details.

Please find below outputs:

-bash-4.2$ journalctl -xe
Hint: You are currently not seeing messages from other users and the system.
  Users in the 'systemd-journal' group can see all messages. Pass -q to
  turn off this notice. No journal files were opened due to insufficient permissions.

-bash-4.2$ systemctl status postgresql-9.5.service
 ● postgresql-9.5.service - PostgreSQL 9.5 database server    Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled; vendor preset: disabled)    **Active: failed** (Result: exit-code) since Wed 2018-07-25 12:58:51 IST; 1min 26s ago
 Docs: https://www.postgresql.org/docs/9.5/static/   Process: 109849 **ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)**   Process: 109843 ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)  Main PID: 102339 (code=exited, status=0/SUCCESS)

I searched further and various links (most notably this ) pointed to the fact that I needed to run something like the below, which I did:

  /usr/pgsql-9.5/bin/postgresql95-setup initdb

But that would give error:

  Data directory is not empty!

The only way out of this is to delete the directory /var/lib/pgsql/9.5/data completely, which would leave me with no DB and no pg_hba.conf

pg_hba.conf:

Relevant error messages:

2018-07-25 12:53:06.218 IST >LOG: invalid authentication method "0.0.0.0/0" <
2018-07-25 12:53:06.218 IST >CONTEXT: line 80 of configuration file "/var/lib/pgsql/9.5/data/pg_hba.conf" <
2018-07-25 12:53:06.218 IST >FATAL: could not load pg_hba.conf <
2018-07-25 12:58:50.617 IST >LOG: invalid authentication method "0.0.0.0/0" <
2018-07-25 12:58:50.617 IST >CONTEXT: line 80 of configuration file "/var/lib/pgsql/9.5/data/pg_hba.conf" <
2018-07-25 12:58:50.617 IST >FATAL: could not load pg_hba.conf


texts: How expensive are SELECT FROM LIKE statements from a design perspective?

texts:
I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files drastically very very soon. I am investigating for quick solutions to release the pressure.

One option is to move the file's content (UTF8 text) into a database and perform SELECTs to replace the file search (by name). The select statements would be the following:

SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE 'criteria%';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%othercriteria';
SELECT TOP(1) MyContent FROM MyTable WHERE MyContentName LIKE '%andanothercriteria%';

We are talking about a total of 200K requests per day on a 800K rows table (which I could easily split in two if it helps). MyContentName is part of the key and will be indexed. There is either one entry matching the criteria in the table or none.

I am no DB admin expert. Is this something a MySQL instance on a shared server can support or are my expectations too high?

I know the typical answer is: I should test. Unfortunately, because of the emergency, I don't have time to perform tests. I need to find a quick solution, even if temporary, even if it degrades service response delays a little.

I am looking for experienced DB admin's opinion on this strategy. Tips and suggestions are welcome too.


texts: Initialize, Start Server and Create Database in Single Script

texts:
I am writing a provision script for a dev environment with the following:

sudo su - postgres <<EOF
cd ~/data
pg_ctl -D . initdb
pg_ctl -D . -l run.log start
createdb mydb
pg_ctl -D . stop
EOF

It fails with the following error:

createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?

Manually executing the commands works. I have noticed that when createdb succeeds, there are several postgres processes, often 5 or 6, so I added the following:

...
pg_ctl -D . -l run.log start
until [ `pgrep -c postgres` -gt 4 ]; do true; done
createdb mydb
...

But it hangs, meaning pgrep never gets over 5 counts inside the script runtime. I have also tried using a subshell:

...
(pg_ctl -D . -l run.log start)
wait
createdb mydb
...

With no success.


texts: Deny a Sql Server user from a specific hostname

texts:
Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)?

I have a CRM web application. That app connects to my database with a sql server auth user. There are several different developers working on that CRM web app. They know the user name and password from the web config file they develop. Some of them can connect to my database with that user and execute some DDL and DML commands and I want to block them by their client hostname. If a developer connects to db from his/her machine with the app user credential, I want to block them.


texts:
What are the performance impact of using Round instead of Convert

DECLARE @example AS FLOAT
SET @example=969.3333
SELECT CONVERT(DECIMAL(4,1),@example)
SELECT ROUND(@example,1)


texts: How to accumulate aggregate group by nested object keys in MongoDB

texts:
I am looking for an aggregate pipeline in MongoDB which can achieve this requirement:

I have a collection like:

  {"filter": {
    "_id": {
      "$gt": 10
    }
   }
  },
  {"filter": {
    "_id": {
      "$gt": 20
    }
   }
  },
  {"filter": {
    "_id": {
      "$lt": 30
    }
   }
  },
  {"filter": {
    "name": {
      "first": 'Lee'
    }
   }
  },
  ...

A filter field is a nested object defined in my collection. I want to find a way to accumulate all nested keys in the filter field. Take above as an example, I want to get the result:

_id.$gt: 2
_id.$lt: 1
name.first:1
...

the key in the result list is the nested keys defined in filter and the value is the number of duplicates of the nested keys.


texts: Sqlite Create Table with Check column from other table

texts:
I have two tables:
position
CREATE TABLE IF NOT EXISTS position(
  id_position INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  min REAL NOT NULL CHECK (min > 0),
  max REAL CHECK (max > 0),
);

min, max links to salary
employee
CREATE TABLE IF NOT EXISTS employee (
  id_employee INTEGER PRIMARY KEY AUTOINCREMENT,
  id_position INTEGER NOT NULL,
  name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  data_of_birth TEXT NOT NULL CHECK (DATE(data_of_birth) IS NOT NULL AND data_of_birth < DATE('now','localtime')),
  salary REAL NOT NULL CHECK (salary >= position.min AND salary <= position.max),
  FOREIGN KEY(id_stanowisko) REFERENCES stanowisko(id_stanowisko)
);

But check in salary column doesn't work. I have an error no such column: position.min.
Any solution?


texts: how to change primary node on mongo replica set without losing cursor

texts:
Today, the primary machine's loading is extremely high on a replica set.
Users complain about query/insert operations are very slow.
So I tried to change the priority of node to make secondary/primary exchanged:

cfg = rs.conf()
cfg.members[0].priority = 1
cfg.members[1].priority = 0.5
rs.reconfig(cfg)

Then, 2 users complained about their program get exception from running cursor lost.

Is there any way to avoid the running cursor lost when primary node changed?


texts: Are InnoDB tables harder to backup and slower?

texts:
I am trying to optimize our database tables. I have some sql skills, but complete newbie at db administration. Our db operations are pretty slow, and I have been assigned to review the storage engines and create indexes. So, here's the thing... Most of our tables are MyISAM. And most of the tables are used for both read and write, constantly. The tables are also very related, and most selects pull from at least 4 or 5 tables. After hours spent reading recent blog posts and stack exchange answers - looks like InnoDB would be the way to go.

But, our db is hosted, and after discussing briefly with our host about switching to InnoDB, he came up with the following complaints

InnoDB is slower at most things
InnoDB is hard to backup
InnoDB only shines when you have complex joins on tables with udpates and inserts.

So here is my question - are his complaints valid? Should I only select a chosen few tables to convert to InnoDB, the ones with really complex joins?

Thanks


texts: How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

texts:
Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query notifications, ...) in our applications.

Or so we thought.

Unfortunately, those new features require CONCAT_NULL_YIELDS_NULL to be set to ON---not only when using the new features but basically whenever the database is accessed: If I create an indexed view V on tables A and B, CONCAT_NULL_YIELDS_NULL must be set to ON not only when working with (new) view V, but also when inserting/updating values in (old) tables A and B.

Now we have a huge legacy application with a lots of SQL statements scattered all over the code: A quick search for SELECT over the code base yields ~10.000 results. The DB layer executes SET CONCAT_NULL_YIELDS_NULL OFF after opening the DB connection, so all SQL statements were written under the assumption that A + NULL = A rather than A + NULL = NULL.

Is there any "slow upgrade path" to move from CONCAT_NULL_YIELDS_NULL OFF to ON without a one-time-effort to check, rewrite and test Every. Single. SQL statement?

I was thinking about parsing "old" SQL statements in the DB layer and replacing expression1 + expression2 with ISNULL(expression1, '') + ISNULL(expression2, ''), but for that I'd need to (1) implement a parser for the subset of T-SQL that we currently use and (2) determine the result type of T-SQL expressions, so that I know whether + means string concatenation or addition. Both should be doable but sound like a huge amount of non-trivial work.

Is there any other option that I've missed? Or did, by any chance, someone already implement a converter from non-CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements to CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements?


texts: postgres service suddenly not working after the computer crashed while working

texts:
I've been working with postgres on a linux server for the last couple of months. Last week, half of our servers crashed and my one with them. I was in the middle of uploading data to the postgres database with osm2pgsql when this happened. After we revived the server, postgres has stopped working.

commands:

service postgresql start
(Alternatively, /etc/rc.d/init.d/postgresql start, same thing happens)
Starting postgresql service:  [  OK  ]

service postgresql status

postmaster dead but pid file exists

Trying to start the postmaster using

su postgres
postmaster -D /var/lib/pgsql/data

yields nothing either.

As for the pid file, /var/run/postmaster.5432.pid exists, and when I remove it then the status changes to "postmaster is stopped", but if I try to start it again:

service postgresql start
Starting postgresql service: [FAILED]

If anyone has any idea what I could do to get it working, where the problem might be. What really gets me is that it was working until last week, I even rebooted a backup and nothing seems to have changed, I'm really at my wit's end with this problem!


texts: Has anyone had luck sending dbmail via relay to an external server on an Azure VM (which appears to block SMTP outbound regardless of rules)?

texts:
Typically the servers we manage we handle a lot of the alerting for the SQL noise via operators/profiles/DBMail which gives us the most raw information about any issues that crop up on the server.

It appears that within Azure VMs, outbound via port 25 is disabled and can not be enabled (as far as my luck has gone, that is). This prevents the typical use of a relay to our mail server to then send mail to our ticketing boards.

Has anyone run into anything similar, and if so, are there any good workarounds that you found? I am sure this question is a bit confusing so please ask for whatever relevant info is needed.


texts: Can the view "v$database_block_corruption" get refresh after the corrupted block get fixed?

texts:
I have a table that contain several corrupted data blocks and I use the stored procedure reside in the “DBMS_REPAIR” to mark the data blocks as corrupted data blocks so that I can keep query to the table. After the stored procedure is executed, everything goes fine, I use rman to check the corruption by command:

validate datafile number

and the output still keep reporting the corruption, nothing change in the v$database_block_corruption ,so howv$database_block_corruption can be refresh, did I have to rebuild the table by such statement ：

create table TABLE_BAK as select * from TABLE_CORRUPTED;
drop table TABLE_CORRUPTED;
RENAME table TABLE_BAK to TABLE_CORRUPTED;

By the way: I don`t have any backup to restore the data block.


texts: Most Efficient Way to Retrieve Date Ranges

texts:
What's the most efficient way to retrieve date ranges with a table structure like this?

create table SomeDateTable
(
    id int identity(1, 1) not null,
    StartDate datetime not null,
    EndDate datetime not null
)
go

Say you want a range for both StartDate and EndDate.  So in other words, if StartDate falls in between @StartDateBegin and @StartDateEnd, and EndDate falls in between @EndDateBegin and @EndDateEnd, then do something.

I know there are a few ways to probably go about this, but what is the most advised?


texts: Finding the Table and Column Based on a Known Value

texts:
I've inherited a rather large SQL Server 2012 database -- about 2TB, tens of millions of rows in some tables, and about 350 different tables. I have basically no references nor documentation.

There's a type of file I need to generate which contains information stored somewhere in this database. I have one example of such file, so I have concrete values, but no idea as to where each of its fields came from.

For instance, I have an entry in the output file labeled UUID which has a value of a03dc6109c6f53ce93203f1b85c7d31d. After much uncomfortable digging, I found a column in one of the tables called "custom_value_1" with the value a03dc610-9c6f-53c-e932-03f1b85c7d31d, which I'm pretty sure semantically corresponds to what I wanted. But I have many such values to find, and it seems pretty unlikely that I'll be able to stumble across them all.

So how can I search all the columns across all the tables in a database for a particular value? I'd love to be able to say something like:

SELECT * FROM * WHERE * = 'HB194';

and have it search every column (skipping those of inapplicable types; no timestamps if I'm searching for a string) of every table for the value 'HB194'. Obviously something more complex will need to be employed, but I don't think I have the time (nor the fortitude) to manually craft a specific select query for each of the 350 tables for each of the dozen or so values I'm looking for.


texts: What Microsoft DBA certificate should I acquire?

texts:
I am  thinking about getting Microsoft DBA certification, but trying to find out which exactly certificate should I study for gets a little confusing.
My company uses on premises SQL Server 2017 but seems like Azure Database becomes pretty popular. So is on premises going away?
Is "MCSA: SQL 2016 Database Administration" would be the one I need?
If I get "MCSA: SQL 2016 Database Administration" will I be able to administer Azure DB? How different is that?
Thank you


texts: information_schema is full of NULLs after database import

texts:
I am using MySQL workbench to dump 4 databases with tables that reference each other. As a note, there are no current problems with the databases at all - everything works as expected. When I try various data exports from MySQL workbench and then import it to another PC with MySQL workbench, everything appears to be fine except that queries to information_schema are returning results full of NULL
On my working setup, if I run this query
SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
        FROM information_schema.TABLE_CONSTRAINTS i
        LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
        WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
        AND i.TABLE_SCHEMA = 'details'
        AND i.TABLE_NAME = 'locales'

I will get a whole bunch of information about fkey relationships.
After I dump and import these databases to another PC, and run this query again, I get the same amount of rows but all data is NULL. When I investigate the tables in MySQL workbench, they all show proper foreign keys! But I need the above query to produce correct results because I use it in my PHP application to display foreign keys to the user. What's worse - if I make two new tables and set up fkeys between them, information_schema still returns more rows full of NULL values using the same query as above. It's like the whole thing is broken for the database.
How can I get the above query to not return rows full of NULLs? What could be wrong?


texts: SQL Server unique index, per subset of data, based on values of another column

texts:
Say I have a Customers table, and an Emails table. Emails table has these fields:

Id
CustomerId
EmailValue

Now, I want to create a constraint so that no duplicate emails should be registered per customer. Ways that I can think of are:

Creating a function to ensure uniqueness of each new email value, and call that function in a check constraint
Create a trigger, and on each insert or update ensure that the email is not duplicate per customer
Forget about database at all, and make this part of the business in my application tier

However, I recently got familiar with SQL Server's filtered indexes. Though I can't find out if it can help me in this specific case or not. Most of the examples I've seen is to filter out NULL values in order to make a unique index for a nullable column.

Is there a way I can use a filtered index to achieve what I want?


texts:
I use below command to do RMAN backups on rhel 5.9,  but archivelog backups always ends up in $ORACLE_HOME/dbs

Backup as compressed backupset database plus archivelog format '/path'

How to use the same path as mentioned in the above command to get both backupset of database in same location or other desirable location


texts: PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

texts:
If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method:

Introduce 2 new columns as C and D2.
Copy the contents of D to D2.
Delete D.
Rename D2 to D.

The new order would be A, B, C and D.

I thought this was a legitimate practice as (so far) it produced no issues.

However, today I came across a problem when a function carrying out a statement on the same table returned the following error:

table row type and query-specified row type do not match

And the following detail:

Query provides a value for a dropped column at ordinal position 13

I tried restarting PostgreSQL, doing a VACUUM FULL and finally deleting and re-creating the function as suggested here and here but these solutions did not work (aside from the fact that they try tackling a situation where a system table has been altered).

Having the luxury of working with a very small database I exported it, deleted it and then re-imported it and that fixed the issue with my function.

I was aware of the fact that one should not mess around with the natural order of columns by modifying system tables (getting hands dirty with pg_attribute, etc.) as seen here:

Is it possible to change the natural order of columns in Postgres?

Judging by the error thrown by my function I now realize that shifting the order of columns with my method is also a no-no. Can anyone shine some light as to why what I am doing is also wrong?

Postgres version is 9.6.0.

Here is the function:

CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '

-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),

-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
authentication_date = current_timestamp,
facebook_id = facebookid
WHERE EXISTS (SELECT * FROM select_user)
AND
useremail = users.email
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id),

-- Third, insert the user (if user does not exist):
insert_user AS
(INSERT INTO
users (authentication_code, authentication_date, email, key, name, facebook_id)
SELECT
GEN_RANDOM_UUID(),
current_timestamp,
useremail,
GEN_RANDOM_UUID(),
COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
facebookid
WHERE NOT EXISTS (SELECT * FROM select_user)
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id)

-- Finally, select the authentication code, ID, key and Stripe ID:
SELECT
*
FROM
update_user
UNION ALL
SELECT
*
FROM
insert_user' LANGUAGE "sql" COST 100 ROWS 1
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

I performed the renaming/reordering on both column facebook_id and stripe_id (a new column was added before these, which is the reason for the renaming, but is not touched by this query).

Having the columns in a certain order is purely out of interest for order. However, the reason for asking this question is out of concern that a simple renaming and deleting of a column may trigger real issues for somebody using functions in production mode (as happened to myself).


texts: MariaDB I/O capacity with innodb_io_capacity parameter

texts:
I use hdparm to test my SSD hard drive I/O benchmark :

[root@mail ~]# hdparm -t -T /dev/xvda2

/dev/xvda2:
 Timing cached reads:   12906 MB in  1.99 seconds = 6477.79 MB/sec
 Timing buffered disk reads: 1650 MB in  3.00 seconds =  550.21 MB/sec

I saw innodb_io_capacity parameter for MariaDB is :

Storage type The innodb_io_capacity value
HDD 200
RAID 10 HDD 1,000
SSD 5,000
Fusion IO 10,000

Could some body please tell me which value i should use in this case ?


texts: SSRS: Oracle Data Sources Breaking After Server Updates\Restart

texts:
Our SSRS instance has several Shared Data Sources that hit Oracle databases. Whenever we do patching or the server restarts, these Data Sources seem to break. Any reports hitting them come up with the below error message:

Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

This has been happening for years, and the weird part is, to fix it, the DBAs have been removing the password from the Data Source, and recopying it, and then it works fine. So it's not like something isn't installed, like the error message claims. It is almost like the server is not storing the passwords correctly. We do have several Data Sources that use SQL Server accounts, but they don't have any trouble.

Has anyone dealt with something like this before?


texts:
In Vertica, the SELECT privilege on all system tables in V_CATALOG and V_MONITOR is granted to PUBLIC. Therefore, by default all users can query them.  And not all system tables seem appropriate to be public.
For the most part, for each of the entries, the results appear appropriately limited to a users specific grants.   For instance, select * from v_catalog.columns; returns just columns for which the user has been granted  SELECT for the table and USAGE for the schema, and fortunately SELECT * FROM v_catalog.passwords; returns no rows at all.
But, some seem overly broad, such as SELECT * FROM v_catalog.users returns all users for all schemas.
Is there any downside to REVOKE SELECT ON v_catalog.users for a user intended to have only SELECT access to specific tables in a specific schema?  Or further revoke SELECT on V_CATALOG and V_MONITOR entirely for public users?
Limit access to specific database only, and restrict access to system tables


texts: Next quarter without using Quarter function

texts:
I have a query which runs on particular dates. Whenever the query runs, it has to pick up the next quarter dates.

1st quarter Jan-March
2nd quarter April-June
3rd quarter July-September
4th quarter October- December

Suppose  my query runs in April. I need to pick the data from July 1st to September 30th, without using the MySQL QUARTER function.


texts: Dont' see option to download/upload some SSRS reports

texts:
I have a SSRS 2016 instances with 100s of reports on one of my server. I want to migrate these reports from this instance to an existing new instance which already has some reports for different business unit. So basically I am downloading all RDLs and uploading those to a new instance.

While working on this task, I found one weird thing while downloading reports was for some reports download option is not present. My question is how to download these reports and why am I seeing this behavior?


texts:
Which is faster

SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique

or

SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID


texts:
I have a trigger from a co-worker that isn't working as desired.  When an INSERT is done the insert never completes and the following two lines keep appearing in SQL Profiler over and over.  What is being done wrong?

  SP:StmtStarting   WHILE (SELECT count(*) from INSERTED ) > 0
    22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707   SP:StmtStarting    WITH CTE AS           (     SELECT
  c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
        FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
        WHERE c.emp = d.emp
        and (
        b.app_group = d.GRP_NAME or
        b.app_name = d.GRP_NAME
        )     ) SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = 'ERROR';
  --EXEC msdb.dbo.sp_send_dbmail  --
          --@profile_name = 'AdventureWorks2012 Administrator',
          --@recipients = 'danw@Adventure-Works.com',
          --@body = 'Don''t forget to print a report for the sales force.',
          --@subject = 'Reminder';

  22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707

Trigger script:

USE [DATABASE_NAME]
GO

DROP TRIGGER [dbo].[SENDTXT_MSG]
GO

CREATE TRIGGER [dbo].[SENDTXT_MSG]
ON [dbo].[DB_LOG]
AFTER INSERT
AS
DECLARE
@appname varchar(50)
BEGIN
WHILE (SELECT count(*) from INSERTED ) > 0
WITH CTE AS                  (
       SELECT c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
      FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
      WHERE c.emp = d.emp
      and (
      b.app_group = d.GRP_NAME or
      b.app_name = d.GRP_NAME
      )
         )
SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = ‘ERROR’;
   --EXEC msdb.dbo.sp_send_dbmail  --
        --@profile_name = 'AdventureWorks2012 Administrator',
        --@recipients = 'danw@Adventure-Works.com',
        --@body = 'Don''t forget to print a report for the sales force.',
        --@subject = 'Reminder';

insert into test (test) values (@appname);
END
GO


texts:
This is running on an AWS AMI EC2 Linux instance in a PHP script using dblib and PDO to connect to remote Sybase databases (Sybase version 11.0.1.2596). The server has freetds with these settings:

Compile-time settings (established with the "configure" script)
                        Version: freetds v0.91
         freetds.conf directory: /etc
 MS db-lib source compatibility: yes
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes

Sometimes when we run queries on the sybase computers we get an error like this (the token can vary):

TDS: unexpected token 95 [0] (severity 0) [(null)]

The query sometimes runs even after this error and sometimes fails. I have searched online but I can't find what this error means or how to fix it. Here is an example of one of the queries:

-- 999 | Hammil | 2017-04-05 08:23:29
SELECT
    999 as 'site_id',
    (select rest_name from micros.rest_def) as 'location',
    now() as 'dt_run',
    emp_seq,
    employee_number as 'obj_num',
    last_name,
    first_name,
    check_number,
    xfer_from_check,
    xfer_from_type,
    xfer_to_check,
    xfer_to_type,
    check_id,
    table_number,
    table_name,
    group_number,
    cover_count,
    order_type,
    order_type_name,
    business_date,
    check_open_date_time,
    check_closed_date_time,
    table_open_date_time,
    printed_count,
    sub_ttl,
    tax_ttl,
    auto_svc_ttl,
    other_svc_ttl,
    paymnt_ttl,
    amount_due_ttl,
    sp_error
FROM
    micros.micros.v_R_guest_checks x
where
    x.check_closed_date_time is null;

Sometimes we get this error:

SQLSTATE[HY000]: General error: 20047 DBPROCESS is dead or not enabled [20047] (severity 1) [(null)]

When this happens, I can run

nc -v -w 4 xxx.xxx.xxx.xxx 2638

and it returns

Connection to xxx.xxx.xxx.xxx 2638 port [tcp/sybaseanywhere] succeeded!

but I will continue to get the error for a few minutes and then it goes away.


texts: Why doesn't nodetool work on my Cassandra Cluster?

texts:
I have never been able to get nodetool to operate correctly to give me information about my cluster.  We have a simple 3-node cluster of Cassandra 3.11.1 nodes running under CentOS 7.3 -- using the gossip snitch and replication.  We have a special place on the file system where we're putting the node configurations, but these are appropriately set with environment variables.  A "generic" configuration is in the environment for client applications.

These are attempts at using nodetool (I have replaced the exact server name with /hostname/.)

  % nodetool -h /hostname/ status
  nodetool: Found unexpected parameters: [/hostname/, status]
  See 'nodetool help' or 'nodetool help '.

maybe the help is wrong and flags go at the end?  Running this:

  % nodetool  status -h /hostname/

gives me usage information.

My favorite is this:

  % nodetool help status
  nodetool: For input string: "help"
  See 'nodetool help' or 'nodetool help '.

I can connect with cqlsh.  I get the same response from nodetool whether I'm on a remote host or one of the cluster nodes.  Python clients connect fine and we've been able to create keyspaces and bulk load data from remote hosts.

I am sure there is some configuration I have overlooked but I can't find any information about what it might be in the docs.

Any suggestions on how to get nodetool working?


texts: Hosting Multiple MongoDB Databases on the same MongoDB Instance/Cluster

texts:
In MongoDB there are the following conecpts:

A MongoDB database exists as a logical unit on a MongoDB Instance which is either a singleton or a cluster running within some number of mongod processes on some number of servers.

There is lots of Q&A around answering the question: Should one run multiple production mongod processes on the same server? to which the answer is: In general no.

However, what about the question of having multiple production databases on the same instance?  I.e.:

Set up a production grade mongodb cluster,
Create a number of databases by doing something like

use someDatabase;
db.someDatabase.insert({});

3.Creating a User/Role that is limited in access to someDatabase

The above described configuration could obviously suffer from the noisy neighbor problem (& related DoS attacks), but otherwise is the above considered a normal and standard way of providing a logical & security barrier between tenants? What are the pros/cons of doing so? If I needed to migrate one of these logical DBs to its own MongoDB instance, would that be possible?


texts: Select Multiple Values from Same Column; one sql statement and split into separate columns

texts:
This post derives from following -
Select Multiple Values from Same Column; one sql statment

Based on this query -

 SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id)
 FROM t
 WHERE meta_key  IN('first_name','last_name','street_add','city','state')
 GROUP BY user_id

It will give me a comma separated column, which is great and gives me the data, but is there a way to separate the fields into columns?


texts: PostgreSQL becomes very slow after heavy utilization

texts:
I have built a Python web application and I am hosting it in a 2GB RAM Debian Server. My application uses PostgeSQL 9.1 with PostGIS.

One of my tasks involves importing data from big csv files. Importing is a very complex process (I store timeseries data into blob objects, this involves reading the blog object from the DB, opening it, adding more data into it and inserting it back in the database).

Before I start the importing process I benchmark the database and I get these:

tps = 158.493676 (including connections establishing)
tps = 165.895254 (excluding connections establishing)

After the importing ends (it takes more than 20 hours) I get these results:

tps = 4.289660 (including connections establishing)
tps = 4.307630 (excluding connections establishing)

Finally, after database restart (and apache restart) I get these:

tps = 56.106018 (including connections establishing)
tps = 56.840143 (excluding connections establishing)

It seems to me that the more I heavily use the database the slower it becomes.

Is this something I am doing wrong? Is this fixable?

This is my postgresql.conf file: http://dpaste.com/3ZKJX8Z


texts: How to drop all connections to a specific database without stopping the server?

texts:
I want to drop all connections (sessions) that are currently opened to a specific PostgreSQL database but without restarting the server or disconnecting connections to other databases.

How can I do that?


texts: How to select records by nth minimum/maximum value of date column in postgresql?

texts:
I have a table as follow:

create table x(id int, work date);

this table has the following records:

insert into x values(1,'2017-01-01'),(2,'2017-01-01'),(3,'2017-02-02'),(4,'2017-01-15'),(5,'2017-01-15');

Now, I want to select the records with first, second and third date values.
My desired output would be as follows:
If I want to select record with first date value then the output would be:

id      work
1     2017-01-01

If I select record with second date value then the output would be:

id      work
 4    2017-01-15

Any help is appreciated in advanced.


texts: Questionnaire database design - which way is better?

texts:
I have ONE long html page, several sets of questions divided into small sections (approx. 15 sub-sections in one page), the total of questions are about 100 questions: varies from input, multiple choice, checkboxes, radio buttons, textarea, and file upload. One question could contain many answers which obtained either from group of checkboxes, group of select list, group of multi-select, or all of them combined into one answer. I thought I would use this database design below but found out lately that it isn't the good approach after all.

One customer could only have one set of question: one customer per 100 questions.
For the old approach I don't keep question in the database but assign as constant in PHP coding instead. Problem is I have to compare the question in PHP to get it synchronize with the answer in the database. If one question had been altered/deleted/moved from PHP, I would definitely get lost to match it with the answer in Questionnaire database. Better solution?
Could I be able to keep multiple answers obtained from multiple elements in form into one field as one answer? How could I retrieve this field and display it again for customer viewing on form?
Which option down below should I go for?

OPTION 1: Old Approach (1 table)

TABLE: Questionnaire

ID (PK)
CustomerID
Status
A1
A2
A3
.
.
.
A100

OPTION 2: New Approach (2 tables)

TABLE: Question

QID (PK)
Question (varchar)

TABLE: Answer

AID (PK)
CustomerID
QID (int)
Answer (varchar)

Or OPTION 3?


texts: Best database to store and retrieve Word and Excel documents

texts:
My current project is to create a barcode system (for lack of a better word) that is linked to a database (was not given more information or directions).  My task is to have a barcode scanner interpret a barcode, query a database that stores MS Word and Excel documents (.docx/.xlsx), then automatically retrieve and display the document.  I have not received much direction other than when a barcode is scanned, to have the corresponding document to be retreived and displayed automatically.  There is currently no database at our location and we store all of our information in Excel spreadsheets.

The process that I am attempting to automate is to remove the "human element" from opening the correct document(checklists and reports) as well as saving the document with the proper naming format and location (excel VBA script).

There is no reason why this should be impossible, but I am not sure which (R)DBMS to use.  We are not looking to spend extreme money (no oracle 11g or 12c), but we are essentially starting from scratch and our spreadsheets are spread over the entire networked drive. I know there are options like IBM DB2 or MySQL or MS SQL Server or MongoDB...but I am not sure which one would be the best "bang for my buck" and give the least amount of headaches.


texts: Category entity relationship / subtype relationships design

texts:
I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.

Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.

Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.

The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.

The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).

Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.

Am I going down the right path or should this be avoided.


texts: Simple way to use SP_EXECUTESQL for loop through all databases

texts:
I have a BIG problem running queries on all databases. I have a lot of scripts that works, but I would like to understand better how to use SP_EXECUTESQL instead of SP_MSFOREACHDB.

Normally when I'm in a hurry I do this:

Then I just copy the results, and I execute it. It works great.

I would like to know how to transform this in a SP_EXECUTESQL loop statement.

I have this that uses a cursor, but is there a way to use SP_EXECUTESQL without a cursor?

create table #temp (
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
                    )
go

DECLARE @DB_NAME NVARCHAR(100)
DECLARE @CMD NVARCHAR(MAX)
DECLARE CR_FEATURE CURSOR FOR
SELECT NAME
    FROM SYS.sysdatabases

OPEN CR_FEATURE

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @CMD='insert into #temp  select '''+@DB_NAME+''' ,* from sys.dm_db_persisted_sku_features'
    EXEC SP_EXECUTESQL @cmd

FETCH NEXT FROM CR_FEATURE INTO @DB_NAME
END

CLOSE CR_FEATURE
DEALLOCATE CR_FEATURE

DROP TABLE #temp

I could even create a simple query to run that simple select, but I would like everything in the same query window:


texts: Bulk insert statement fails in SSMS with Operating system error code 5 (Access is denied.)

texts:
This issue was discussed in several places and it seems that general consensus is that if Windows Authentication is used then SSMS will access source file using Windows Domain user credentials.

However, Process Monitor shows otherwise. When I log into SSMS as a Windows Domain user, SSMS still uses SQL Server service account to access file in question, which sits on a network share. SQL service account does not have any access rights to the share, hence statement fails.

My understanding after reading quite a few answers for similar questions is that this should happen only if SQL Server Authentication is used when logging into SSMS, in which case SSMS uses SQL Server service account to access files. Whereas for Windows Authentication SSMS is supposed to use relevant Windows Domain account under which SQL statement is executed.

I wonder why SSMS uses SQL Server service account even when user uses Windows Authentication? Any thoughts/comments on this please?

The statement I'm attempting is:

bulk insert [database].[schema].[table]
FROM '\\server\share\file_name.csv' with( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

This query shows I'm connected to the server via NTLM authentication:

SELECT dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

Thank you.


texts: Is there any way to get recently inserted value in a table without using aggregate functions?

texts:
I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 million rows . We splitted up every month tables as like below format TABLE_MONTH_YEAR . I am running this query in current month table only .

I tried this query .

select max(login_time) , user_id from ZmediaUserDetails group by user_id

user_id is an indexed column .

login_time is an  timestamp .

I am running this query in production setup with master/slave setup . I ran this query in a slave machine . I waited for nearly a hour . Still it was not finished. I checked the slave status using show slave status it shows Seconds_Behind_Master: 4886 . So I killed that query .

I can't create an index in either master or slave . Because , It will lock the whole table .

Please give your suggestions .


texts: How to convert the JSON date with to_date function

texts:
How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date?

That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from oracle.

  My real problem is much more comprehensive that looks to be. It evolves the APEX_JSON usage, but it is not the question.


texts: SQL MS ACCESS: Split row into multiple based on column value

texts:
I have table (SQL MS ACCESS)

COUNTRY   NO_OF_PAGES
FRANCE    2
UK        4
GERMANY   1

I want output

COUNTRY   NO_OF_PAGES
FRANCE    1
FRANCE    2
UK        1
UK        2
UK        3
UK        4
GERMANY   1


texts: Why can a differential backup not be copy-only?

texts:
I have noticed that copy only backups does not allow for differential backups.
What is the reason the copy-only backup option is disabled for differential backups?


texts:
I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in.

Somehow, MySQL considers that "14.975" is different than "14.975".

If I try taxRate < 14.975 or taxRate = 14.975, it does not enter the condition; but taxRate <> 14.975 and taxRate > 14.975 does.

I have the feeling I'm missing something obvious, but I just can't figure it out.

My test code:

  DECLARE totalTax DECIMAL(20,6) DEFAULT 0;
  DECLARE taxRate DECIMAL(10,3) DEFAULT 0;

  SET @roundMode = 0;
  SET @roundPrecision = 2;

  /* caculate TPS only*/

  CALL calculateTotalTaxOnAmount (200, 4, NULL, totalTax, taxRate);

  IF (totalTax <> 10) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA';
  ELSEIF (taxRate <> 5) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA';
  END IF;

  /* caculate TPS and TVQ */

  CALL calculateTotalTaxOnAmount (100, 4, 87, totalTax, taxRate);

  IF (totalTax <> 14.98) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA_QUEBEC';
  ELSEIF (taxRate <> 14.975) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA_QUEBEC';
  END IF;

My function:

CREATE PROCEDURE calculateTotalTaxOnAmount (
    IN amount DECIMAL(20,6),
    IN countryID INT(10),
    IN stateID INT(10),
    OUT totalTax DECIMAL(20,6),
    OUT totalTaxRate DECIMAL(10,3)
)
BEGIN
    DECLARE break BOOLEAN DEFAULT FALSE;
    DECLARE taxBehavior INT(10);
    DECLARE taxRate DECIMAL(10,3);

    DECLARE TaxCursor CURSOR FOR
        SELECT (t.`rate` / 100),
            tr.`behavior`
        FROM `ps_tax_rule` tr
        JOIN `ps_tax` t
            ON tr.`id_tax` = t.`id_tax`
        WHERE tr.`id_country` = countryID
        AND tr.`id_state` IN (0, stateID)
        AND t.`active` = 1
        ORDER BY tr.`id_state`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;

    SET totalTax = 0;
    SET totalTaxRate = 0;

    OPEN TaxCursor;

    tax_loop: LOOP
        FETCH TaxCursor INTO
            taxRate,
            taxBehavior;

        IF (break) THEN

            LEAVE tax_loop;
        END IF;

        IF (taxBehavior = 2) THEN /* cumulative tax */

            SET totalTax = totalTax + (amount + totalTax) * taxRate;

        ELSE /* standalone tax */

            SET totalTax = totalTax + (amount * taxRate);
        END IF;
    END LOOP tax_loop;

    CLOSE TaxCursor;

    IF (totalTax <> 0) THEN

        SET totalTaxRate = totalTax * 100 / amount;

        SET totalTax = roundMoney(totalTax);
    END IF;
END //

Note: If you want to test it locally, replace roundMoney(totalTax) by round(totalTax, 2)


texts:
With the following query I am getting the error unknown column a.id in where clause. I'm basically trying to add a limit of 5 to what would have been two left joins. Is it possible to rewrite this query so it can work?

SELECT
    CONCAT_WS(' ',(
        SELECT GROUP_CONCAT(body,' ') FROM (
            SELECT c.body FROM c WHERE c.id IN (
                SELECT id_c FROM b WHERE b.id_a=a.id
            )
            LIMIT 5
        ) c
    )) AS contents
FROM
a

Full SQLfiddle at http://sqlfiddle.com/#!9/c1822/3

Find_in_set works http://sqlfiddle.com/#!9/2d43bb/1 but it is extremely slow with large datasets


texts: Getting all undeclared variables from query

texts:
Imagine you try to run the following query:

SELECT *
FROM dbo.Customers c
WHERE id = @id
    AND dateTimeCreated = @date

You'll get an error message along the lines of Must declare the scalar variable "@id".

As I understand it, this happens during the initial parsing of the query, before any code is executed.

Is it possible to get SQL Server to report all the missing variables at once, or is this just how the query parser works?


texts:
once again, I come here for your rescue.

how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of attendance for each student

update student_activity c join
(select * from  student_activity a
where not exists
        (select * from student_activity b where a.student_id = b.student_id
                                            and a.class_type = b.class_type
                                            and a.date > b.date)) r
on r.student_id = c.student_id and c.class_type = r.class_type
set c.first_attendance = c.date
;


texts: Can't see backup files in the shared folder

texts:
Yesterday we tried to perform a restore operation but faced with an issue where SQL Service Account couldn't see backup files that were located in shared folder. The SQL Service Account that we use is actually domain account that has full permission to the shared folder. I want to point out that till yesterday everything worked correctly. Fortunately we found the cause of the problem: someone from sysadmin team had changed the password from active directory and, I guess, that was the reason why SQL Service Account wasn't able to see backup files in shared folder. Sysadmins changed the password back to the previous version and the problem was solved.
My question is: shouldn't SQL Server Account still be able to see(or access) that shared folder despite password change because SQL Server were still running and SQL Server Service Account had full permission on that shared folder?
P.S I tried to simulate this scenario in my lab environment as well. When I changed the SQL Server Account's (domain account) password, it was still be able to access the shared folder as I expected.


texts: Transaction terminated running SELECT on secondary AG group

texts:
Using SQL Server 2016, Always-On

Running a simple SELECT on a table within a secondary Availability Group.

select distinct some_column
from some_table oo (nolock)
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

SSMS eventually sends back this error:

  Msg 3948, Level 16, State 2, Line 14061 The transaction was terminated
  because of the availability replica config/state change or because
  ghost records are being deleted on the primary and the secondary
  availability replica that might be needed by queries running under
  snapshot isolation. Retry the transaction.

The same SELECT with (NOLOCK) works fine on the primary AG.

With or without the NOLOCK it fails most of the time 9/10 and sometimes executes correctly but that's rare. No changes were made to the AG at all.

There are. no changes in the AlwaysOn_Health extended event session.

The low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states is NULL for some and for others there is a value. For those with a value, this is also the primary.

Using the script provided by Nic:

;
WITH    PrimaryStats
          AS ( SELECT   DB_NAME(database_id) AS DatabaseName ,
                        low_water_mark_for_ghosts ,
                        ar.replica_server_name ,
                        ar.availability_mode_desc
               FROM     sys.dm_hadr_database_replica_states hdrs
                        JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
               WHERE    ar.replica_server_name = @@SERVERNAME
             )
    SELECT  DB_NAME(database_id) AS DatabaseName ,
            hdrs.low_water_mark_for_ghosts AS LowWaterMarkSecondaryReplica,
            ps.low_water_mark_for_ghosts AS LowWaterMarkLocalReplica,
            ps.low_water_mark_for_ghosts - hdrs.low_water_mark_for_ghosts AS GhostWatermarkDiff,
            ar.replica_server_name AS ReplicaNode,
            DATEDIFF(SECOND, last_redone_time, GETDATE()) AS RedoDiffSec,
            last_redone_time
    FROM    sys.dm_hadr_database_replica_states hdrs
            JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
            JOIN PrimaryStats ps ON ps.DatabaseName = DB_NAME(database_id)
    ORDER BY
            DatabaseName ASC,'NODE ' + right(ar.replica_server_name ,1);

The output is:

When the LowWaterMarkSecondaryReplica is NULL, its because that's the instance I'm currently executing this query from.


texts: Set user variables from tables being queried in MySQL without locking

texts:
I have a data warehouse in AWS Aurora, with a pretty traditional Date Dimension (from a DateSK, gives your RelativeDays, Weeks, Months, etc).

First, I have a simple function to convert any date to the DateSK:

CREATE FUNCTION `toDateSK`(`myVarParam` DATE) RETURNS int(11)
BEGIN
    IF myVarParam IS NULL
    THEN SET myVarParam = NOW(); END IF;
    RETURN cast(date_format(myVarParam, '%Y%m%d') AS UNSIGNED);
END

I created a function to get the current date in RelativeDays for some date math.  Function is:

CREATE FUNCTION `thisDay`() RETURNS int(11)
BEGIN
    SET @now := toDateSK(NOW());
    SET @res := (SELECT RelativeDays
                 FROM dimDate
                 WHERE DateSK = @now);
    RETURN @res;
END

I wanted to create a view:

CREATE OR REPLACE VIEW viewFirstActivity AS
SELECT
    da.AccountSK     AS AccountSK,
    min(fa.DateSK)    AS DateSK,
    min(dd.FullDate)  AS FullDate,
    min(dd.RelativeDays) AS RelativeDays
FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
GROUP BY fa.AccountSK;

However, this causes tons of table waits if used in a way like this (I'm pulling this from memory):

SELECT * FROM viewFirstActivity WHERE AccountSK="12345" AND RelativeDays > thisDay()-3;

(I know this logically doesn't make sense, but I don't have the original query at my fingertips.  The key point is the function was used both in the view, and in the select query.  This seemed to create lots of locking, even though the dimDate table (InnoDB) was only being read).

If I reworked the query as follows (not using a view):

SET @var := thisDay()-3;
SELECT * FROM (
    SELECT
        da.AccountSK     AS AccountSK,
        min(fa.DateSK)    AS DateSK,
        min(dd.FullDate)  AS FullDate,
        min(dd.RelativeDays) AS RelativeDays
    FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
    WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
    GROUP BY fa.AccountSK) AS tmp
WHERE AccountSK="12345" AND RelativeDays > @var;

This seems to remove the blocking, but now I can't use a convenient view.

I know I'm missing something basic here, but is it easy to explain what it is?


texts: In MongoDB, $setIsSubset does not make efficient use of index

texts:
Data:
student        class
s1          ["english"]
s2          ["maths", "geography"]
...
sN          ["history", "english"]

The field "class" is indexed, and values of it are all in array.
Query1:
db.students.explain('executionStats').aggregate([
  { $match: { $expr: { $setIsSubset:  [ "$class", [ "maths" ] ] } } }
])

,
Query2:
db.students.explain('executionStats').aggregate([
  { $match: { "$class": "maths" } }
])

The first one does a COLLSCAN or IXSCAN with indexBounds class : [MinKey, MaxKey], depending on some conditions (e.g. the number of documents in the collection), while the second does a IXSCAN with indexBounds class : ["maths", "maths"].
Given that the array of the "class" field can have multiple values, I need the $setIsSubset semantics here. I found this. However, the double negation does a COLLSCAN instead of using the index as well. The corresponding query is as follows:
Query3:
db.students.explain('executionStats').aggregate([
  { $match: { class: { $not: { $elemMatch: { $nin: ["maths" ] } } } } }
])

It seems like MongoDB only does simple matching during the IXSCAN. Otherwise, it should be able to avoid doing a IXSCAN with [MIN, MAX] which essentially fetches all keys in the index. It can be possibly even slower than the COLLSCAN.
Wondering if there is anyway to achieve the $setIsSubset functionality, which can utilize the index efficiently.


texts: Are Availability Group Databases on a Secondary replica automatically set to read-only by the AG?

texts:
I have read through the MS docs on read-only routing, but I am not talking about that.

Currently, if I execute an UPDATE against a database that is on a Secondary replica and is part of the Availability Group, I receive a message:

Msg 3906, Level 16, State 2, Line 6
Failed to update database "dbName" because the database is read-only.

My question is whether this configuration is established by default (and unavoidable) by virtue of the database being part of the AG and on a Secondary at the moment?

Some interesting extras:

The following SQL, run on that Secondary, shows I have UPDATE, ALTER and a whole lot more permissions against this database:

SELECT *
FROM fn_my_permissions(null, 'DATABASE')
ORDER BY subentity_name, permission_name;

The following SQL explicitly shows I have permission to UPDATE a table in that database:

SELECT *
FROM fn_my_permissions('dbName.dbo.tblName', 'OBJECT')
ORDER BY subentity_name, permission_name ;

In spite of this I am actually prevented from updating that table. This implies:

The output of fn_my_permissions is not strictly correct
The database "read-only" setting overrides individual permissions

I understand the setting in play is configured via:

ALTER DATABASE dbName SET READ_ONLY

and

ALTER DATABASE dbName SET READ_WRITE WITH NO_WAIT

The main reason for the question is to understand whether the "read only" configuration is a DBA/designer's responsibility during design and deployment of the AG, or an expected (and guaranteed?) feature by simple virtue of using an AG.


texts: How to select only the first distinct value in a column, but have blanks in that column in the other rows otherwise

texts:
Is there any way that I can turn a table like this with a query like

SELECT name,child from People

|----------------------|
| name    |   child    |
| John    |   Jack     |
| John    |   Jerry    |
| Carol   |   Carol    |
| James   |   Carl     |
| James   |   Lawrence |

into this?

|----------------------|
| name    |   child    |
| John    |   Jack     |
|         |   Jerry    |
| Carol   |   Carol    |
| James   |   Carl     |
|         |   Lawrence |

Thanks!


texts: How to use a record variable in the from clausule

texts:
I write a SQL function that receives and return a record. I need to use the received record in a from clause. I am using unnest(array[the_variable]).

Is there a specific function or structure to do this? (something like record_to_recordset(the_variable))

Here is my example:

create type calculate_info_type as (super_code text, super_name text);

create function calculate_info(p_country country)
   returns calculate_info_type
   language SQL as
$SQL$
   SELECT super_code, super_name
     FROM unnest(array[p_country]), -- <=== HERE!!
          lateral (select
            prefix is not null as has_prefix
          ) g1,valores que tenga la tabla
          lateral (select
            case has_prefix when true then iso||'-'||prefix else iso end as super_code,
            case has_prefix when true then name else name||' without phone prefix' end as super_name
          ) g2
$SQL$;

I put a complete running example here: https://rextester.com/XIRSW33377


texts: Results of using a comparison operator in the ORDER BY clause

texts:
There is a table x:
select * from x;

+------+------+
| a    | b    |
+------+------+
| aaa  | 999  |
| bbb  | 888  |
| ccc  | 777  |
+------+------+

What do the following queries do?
select * from x order by a < b;
select * from x order by a = b;
select * from x order by a <> b;
select * from x order by a <> b, b;

What is the name for this operator?
I have not found anything in the MySQL documentation on this topic.


texts: Accidentally change database name (SQL Server 2008)

texts:
It didn't happen to me YET. But I was thinking about this.

I was messing around with my training environment, and by mistake I clicked in the database name, and then I touched the letter A.

Let's assume that I hit enter. Now the database is called A, and I don't remember the original name. CTRL + Z doesn't work.

What to do in a case like this, in a production environment?

I know it could not happen, because the database isn't set as SINGLE USER. But if it happens. What to do? For argument's sake, let's say it's a database that nobody is using right now.


texts:
I am new on mysql and I am try to create a before delete trigger on mysql.

I have a table call "persons" and I need delete a record on another table called "ldap_entries"

the table "persons" have a ID, unique and this id it is on "ldap_entries" but the columns it is called "keyval".

so I am try to do like this: but not working

CREATE TRIGGER `before_delete`
BEFORE DELETE ON `persons`
FOR EACH ROW
BEGIN
  DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id
END $$

Here is my describe output:

mysql> describe persons;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | NO   | UNI | NULL              |                             |
| surname    | varchar(255) | NO   |     | NULL              |                             |
| package    | varchar(64)  | NO   |     | NULL              |                             |
| macaddress | varchar(21)  | NO   |     | NULL              |                             |
| customer   | varchar(255) | NO   |     | NULL              |                             |
| address    | varchar(140) | NO   |     | NULL              |                             |
| phone      | varchar(9)   | NO   |     | NULL              |                             |
| email      | varchar(50)  | NO   |     | NULL              |                             |
| created    | date         | NO   |     | NULL              |                             |
| updated    | timestamp    | YES  |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.00 sec)

mysql> describe ldap_entries
    -> ;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| dn        | varchar(255)     | NO   | UNI | NULL    |                |
| oc_map_id | int(10) unsigned | NO   | MUL | NULL    |                |
| parent    | int(11)          | NO   |     | NULL    |                |
| keyval    | int(11)          | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Also in response to the comments:

I don't know if InnoDB is being used.
There isn't a FK relationship between the tables.


texts: I need help to migrate denormalized table content to normalized form- MS Access

texts:
I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships

However, for ease of collecting this information, we are gathering this in a flatfile in excel.(Employeeid, Fname, LName, ITskills(in multiple rows), projectName)

Our plan is to import this flatfile into MS Access into a Staging Table and then write a query to read the staging table and insert the equivalent records in the 5 tables- and creating the identifier keys systemically. (Employee, Skills, Project, employeeSkillsMapping, skillsProjectMapping)

We tried converting from flat-file to 5 tables in excel manually and importing- getting very challenging to implement the relationships. Any suggestions/ links on how to get this done- how will the keys get generated systemically in all the tables.


texts:
I'm a newbie in database design but I need to design one.
I need to have a record of the devices connected to a network in which certain device could be connected to many devices or any.

In the device's table I've got the following columns:

Device ID(char 25),
IP address (char 25),
SN (PRIMARY KEY char 20)

All that information is taken from two CISCO commands (CDP show neighbors detail and show inventory) the info is placed in two text files.

The first file has the information of the device I'm connecting to via telnet and looks like this:

SN: FDO1129Z9ZJ
Device ID: Barragan_3750
IP: 148.227.2.158

The second file contains information about devices connected to this device, something like this:

Device ID: BIOTERIO
IP address: 148.228.83.189
Interface: GigabitEthernet1/0/6
Port ID (outgoing port): GigabitEthernet0/1
SN: P7K08UQ

Device ID: LAB_PESADO
IP address: 148.228.130.158
Interface: GigabitEthernet1/0/11
Port ID (outgoing port): GigabitEthernet0/1
SN: FNS174202FT

Device ID: Arquitectura_Salones
IP address: 148.228.1420.11
Interface: GigabitEthernet1/0/9
Port ID (outgoing port): GigabitEthernet0/49
SN: FNS14420420

I'm not sure how to represent the relationship because Interface (char 25) and Port ID (char 25) should be relationship's attributes.

Could you help me?


texts:
I have created a dumpfile using EXPDP process.

I am trying to create an external table using the above said dumpfile.

I am using the following statement to create the external table:

CREATE TABLE HR.DATA_LOAD
(
"EMP_NO" NUMBER(10,0) NOT NULL ENABLE,
    "EMP_NAME" VARCHAR2(20 BYTE),
    "DEPT_ID" NUMBER(10,0),
    "DEPT_NM" VARCHAR2(20 BYTE),
    "DOJ" VARCHAR2(20 BYTE),
    "LOCATION" VARCHAR2(20 BYTE),
    "PHN_NUM" NUMBER(20,0),
    "EMAIL" VARCHAR2(20 BYTE),
    "COL1" VARCHAR2(20 BYTE),
    "COL2" VARCHAR2(20 BYTE)
)
ORGANIZATIONAL EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY DATA_PUMP_DIR
  LOCATION ('exp_dumpfile.dmp')
);

After execution of the above statement the table is created but when I issue SELECT statement to see data I get the following error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39324: cannot load data from Data Pump dump file

Am I doing something wrong or is it the case that I can use a dumpfile generated by Datapump method to create an external table?


texts:
I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice).

Suppose I have 3 tables: posts, post_categories and categories. I'm implementing some kind of page with filters where I can filter posts by categories. User can select multiple categories. When he selects more than one, they should be summed. I can't get it working. What I have now is simple IN() SQL clause:

SELECT posts.id, post_categories.category_id FROM posts
JOIN post_categories ON posts.id = post_categories.post_id
WHERE post_categories.category_id IN (1,2,3) LIMIT 10;

But it is not matching all ids, it is OR and I need AND. What I need here is to find all posts that have categories with id=1 AND id=2 AND id=3, but instead this query returns posts that have at least one category from the IN() list.


texts: Will performance be better if I specify a filter multiple times?

texts:
Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN?

To clarify using code:

select *
from #main_select
left outer join
(
  select
    main_id
   ,sum(aggregated) as agg
  from #left_table
  group by main_id
) as grouped
on #main_select.id=grouped.main_id
where #main_select.id = 1

--same reuslts

select *
from #main_select
left outer join
(
  select
    main_id
   ,sum(aggregated) as agg
  from #left_table
  where main_id=1 -- in this case I added where same as Left outer join
  group by main_id
) as grouped
on #main_select.id=grouped.main_id
where #main_select.id = 1