texts: Restoring full and differential backups from production to a report server

texts:
Running this SQL query:

   RESTORE DATABASE Demo
   FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak'
   WITH  NORECOVERY

   RESTORE DATABASE Demo
   FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak'
   WITH RECOVERY

I'm getting these errors:

  Msg 3117, Level 16, State 4, Line 1
  The log or differential backup cannot be restored because no files are ready to rollforward.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I have also tried adding WITH REPLACE but no luck. This is meant to restore to a report server, not to the original server that the backup was taken from.

I have tried Googling, however everything I read just says to make sure you are using WITH NORECOVERY.


texts:
I'm using SQL Server 2012 Express and I'm trying to run a command to create a new table from another

I'd like to do something like this

SELECT
   [RowID], [Account], [Broker], [AlgorithmName], [AlgorithmId],
   [Time], [XMLValue], 'Status' AS [ReportType] AS [varchar](100) NOT NULL
INTO [dbo].[temp_AlgorithmLog]
FROM [dbo].[AlgorithmLog_OLD]

The alias column is the part that doesn't work. 'Status' AS [ReportType] AS [varchar](100) NOT NULL

I would like to use a SELECT INTO rather then CREATE TABLE and INSERT INTO SELECT

Any suggestions on how to do this if it can be done this way?

Thanks in advance


texts: Unstructed data field: Query all values from array of objects by key

texts:
I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like:

"nickname": "value"
"fullName":  "value"
"office": "value"
"unity":  "value"

and a few, more elaborated structure items like:

"address":
    {
        "city": "value",
        "state": "value",
    },

and

"personalVehicle":
    [
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"
        },
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"
        }
    ]

Where, as you can see, personalVehicle is a key that stores an array of objects, in which every object has it's own simple key/value items.

I can query specific key values from address for all registries: SELECT data->'address'->'city' as city FROM person

+------------+
|    city    |
|------------|
| "city1"    |
| "city2"    |
| "city3"    |
+------------+

Here is the situation: I can query all info about the vehicles with SELECT data->'personalVehicle' as vehicles FROM person

+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vehicles                                                                                |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| [ { "brand": "Toyota", "model": "Corolla", "plate": "AAA-1111", "color": "Red" }, { "brand": "Ford", "model": "Focus", "plate": "ZZZ-9999", "color": "Blue" } ]  |
| <null>                                                                                |
| [ { "brand": "Hyundai", "model": "Tucson", "plate": "ABC-1212", "color": "Grey" } ]                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

But I cannot retrieve an specific key for all objects, when the objects are inside of an array; in which case, I need to specify the index: SELECT data->personalVehicle->0->model as model from person

+-------------+
|   model     |
|-------------|
| "Toyota"    |
| <null>      |
| "Hyundai"   |
+-------------+

This guy up here, is the first index of the array, that is, the first car. I need to get the models for all N number of cars that the person might have.

How do I do that? Query that without specifying the index?


texts: How To Log Select Statements from 1 User On 1 Table Into a Permanent Log Table

texts:
We have a centralized authentication library that 100+ applications use to authenticate users. That library has its own DB credentials. It would be extremely useful to log every time that library/user accesses a record in our software_user_permission table. I would want the log to exist not in a file but in a new permanent table.

What would be the best way to achieve this? or am I thinking in the wrong direction?

I want this to happen on the DB side without the library having to insert into the log table itself. Is this possible or commonly done with a trigger? What about a SQL Audit? The last thing I can think of is the library referring to a stored procedure that handles both the select and log statement.

Thanks!


texts: Different MYSQL performances on test/prod system

texts:
I have been met with a very bizzare performance issue and wanted to see if anyone here would have any input on the problem.

To put it simply, we have hugely different query performances on our test and production server.

Hardware configurations are similar and are the following (both are VMs):

Machine     CPU      RAM    Storage
Production  8xCore   16GB   550GB
Test        2xCore   16GB   240GB

Neither disk usage is close to 90% (possible fragmentation issues to filled disk space). CPUs are the same:

Intel(R) Xeon(R) Gold 6132 CPU @ 2.60GHz

I have been running tests on both machines to try and track the root of problem, and all i have so far is pointing to IO (mostly read) issues. I could be (and probably am) wrong here and that's why i wanted to get your input.
Problem here is that i do not have access to the hardware of said VMs so that is a little constraining.

Tests i have done were all using the same MYSQL Docker image (version 5.7.21), running on completely same database (same dump file was loaded, difference was 112 KBs) and using same mysql configuration:

[mysqld]

query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K

join_buffer_size = 8M
innodb_buffer_pool_size = 10G
innodb_log_buffer_size = 256M

innodb_log_file_size = 2047M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=1G

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

#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id        = 1
sql_mode=
group_concat_max_len=15000

innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64

I have pastebined full configuration so i can share it if needed (show variables;)
I have run some more complex procedures involving combination of SELECTs and DELETEs, but performance outliners can be seen even on simple query such as SELECT COUNT(*);

Before each test, mysql server was restarted as to empty the buffer pool.
Test machine:

mysql> select COUNT(ID_) FROM ACT_HI_DETAIL;
+------------+
| COUNT(ID_) |
+------------+
|    9411231 |
+------------+
1 row in set (1 min 20.91 sec)

IOStat output during query execution

Prod machine:

mysql> select COUNT(ID_) FROM ACT_HI_DETAIL;
+------------+
| COUNT(ID_) |
+------------+
|    9411231 |
+------------+
1 row in set (8 min 48.95 sec)

IOStat output during query execution (last rows)

This performance difference is apparent over all use cases, especially during application load. Differences in write speed are of lesser factor, but still present.

Any help would be appreciated. Thank you!


texts: Database cannot be opened. It is in the middle of a restore?

texts:
I've set up log shipping using standby mode （The "Disconnect users in the database when restoring backups" is unchecked).

I tested running the following code on secondary server/database.

declare @a int
while 1=1
begin
  select @a = count(*) from ....
end

However, the code stops with the following error when log shipping restore job start to run.

  Database cannot be opened. It is in the middle of a restore.

How to avoid the issue?


texts: In which cases is a full table scan preferable to an index scan with low cardinality?

texts:
Are there real-world scenarios where to perform a FTS is preferable to scanning an index with very low cardinality? Why would MySQL do so?

Doesn't an index scan always involve reading less data (pages)?


texts: Essbase installation on windows 10 : problem with prerequisites

texts:
I would like to install essbase on windows 10 pro, but I have some problems.
I followed this tutorial : here by changing the OS to windows and version to the latest. I picked the  files corresponding to those given in the tutorial, but I get this error message:.
(it says : some checks failed. and below : the user must belong to the Administrators group.)
To follow the prerequisites, I installed oracle database, and created a loopback network adapter, but I still have the same issue, and on internet I didn't find is log files exist.


texts:
We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables.

When I try to retrieve a top level object which contains many thousand rows of nested child objects, it took more than 4 seconds on an average. Looking at the trace files and query execution plan (and reading), I decided to make covering indices by doing this (to avoid Key Lookups) apart from the clustered index it already has on its Pkeys.

CREATE NONCLUSTERED INDEX [IX_MyTable_Cover] ON [dbo].[MyTable]
(
   Col0,Id
) Include ([Col1],[Col2],[Col3],[Col4])ON [PRIMARY]

Above is just an example, in my scenario, I have included about 18 non key columns. This resulted in 3x boost in response times. But when I do an insert of top level object (with nested children) the response time has increased by 4X. I understand more indices means, bad Insert Performance, but how I do I find the tradeoff in my case?.

Update:
Here is my table structure with indexes

**Table A**
{AId, col1, col2, col3 ... col20}

Primary Index on AId

**Table B**
{AId, BId, col1, col2, col3 ... col20}
Primary Index on AId
Non-Clustered Index on BId

**Table C**
{BId, CId, col1, col2, col3 ... col25}
Primary Index on CId
Non-Clustered Index on BId

**Table D**
{BId, DId, col1, col2, col3 ... col 30}
Primary Index on DId
Non-Clustered Index on BId
Covering Index
CREATE NONCLUSTERED INDEX [IX_E] ON [dbo].[E]
(
   DId
) Include ([Col1],[Col2],[Col3] .. [Col30])ON [PRIMARY]

**Table E**
{BId, CId, EId, col1, col2, col3 ... col30}
Primary Index on EId
Non-Clustered Index on BId and CId
Covering Index
CREATE NONCLUSTERED INDEX [IX_E] ON [dbo].[E]
(
   EId
) Include ([Col1],[Col2],[Col3] .. [Col30])ON [PRIMARY]

I query for the top level object A which has all its children nested inside (usually it has 1000 rows of Table D and 1000 rows of Table D). I use Entity FrameWork to generate the query


texts: Does SQL Server allow moving backwards with standby file?

texts:
I have a database in standby mode.

-- STEP 1
-- Restore path F1 > T7
RESTORE DATABASE [TestRestore] FROM DISK = N'C:\Temp\F1.BAK' WITH FILE = 1,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO

-- STEP 1
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T7.TRN' WITH FILE = 1,
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', NOUNLOAD, STATS = 5
GO

And now I'm gonna do log shipping. My database is accessible(read only)so it will be easy to determine specific point in time where I need to stop at.

-- STEP 3
-- Restore T8
USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1,
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf',
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:26'
GO

-- STEP 4
-- ckecking
SELECT * FROM TestRestore.dbo.TranRecord
GO

I can perform the same transaction log restore repeatedly until I arrive at the required point-in-time by incrementing the time specified in the STOPAT clause.

But, for example, do I need to repat the whole restore again in case I've realized that I've restored too far ahead ?(I mean delete the current restored db and do restore F1 > T7 > T8 again and stop at exactly where I need). Or it is possible to undo just the last log shipping ?(in my example it's STEP 3).

Msdn gives it pretty controversial connotation.

  Specifies a standby file that allows the recovery effects to be
  undone.


texts: What's the best way to backup DB files via network on Linux and Solaris machines?

texts:
I wonder what the best way to backup files via the network.

I have Solaris machines with Oracle 10i on a Veritas cluster. Machines are connected to EMC storage.

The /data/oracle directory is mounted on the EMC storage.

What I want is to backup the /data/oracle directory (70G) on some backup machine via the network (include soft-linked files). The reliability of copying files is very important.

I checked and found some ideas to do that.

For example the first option is to use rsync:

rsync   -WavH –progress   /data/oracle $backup_server_ip:/Backup_dir

The second option is transfering the files with tar and ssh, dealing with compression on both sides of the pipe:

cd /directory_that_we_want_to_backup
tar cpf - . | bzip2 -c | \
  ssh  $backup_server_ip  "cd /Backup_dir && bzip2 -d | tar xpf -"

and so on.

I need advice on what is more reliability from the options above, and maybe on what other good options there are for this.


texts: Can we protect data form Database Administrator by encrypting the data?

texts:
How can we protect data on my database which is MySQL form database administrator stealing it?

Will encrypting it prevent it from being stolen? If yes, how as he already has access to the database he can de-crypt it?


texts: SQL Server 2016 - row level security by AD group membership

texts:
I'm wondering if it is possible to use row level security based on a users AD group membership.

I would love to have something that could support users in different groups and some super users who have membership of all groups.

I suspect the answer is no, but any tips would be appreciated!


texts:
We've been having this nightly backup job on SQL Server 2012 but it appends to the .bak file instead of overwriting, so while googling I discovered master.dbo.xp_delete_file

I noticed that I can delete several backups at once prior than date parameter

I'm thinking in keeping 3 days thus deleting like this:

declare @dt datetime
select @dt=getdate()-3
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\',N'bak',@dt,1

All seems ok, but for the backup, can I create filenames like this? (note the @today in CONCAT() )

declare today varchar(10);
SELECT @today=CONVERT(char(10), GetDate(),126);

BACKUP DATABASE perfMaster TO DISK = CONCAT('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_',@today,'.bak'
WITH NOFORMAT, NOINIT,
NAME = N'Full Database perfMaster Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 20

Or what methods do you employ to accomplish backups with some days history?


texts:
I experience a strange problem on our 3 node MariaDB cluster (10.2.24). Queries send over the command line are received by the cluster but don't get executed. MySQL does not return any errors or warnings. When I execute the query in a mysql shell all queries are executed without any problem.

Bash example (not working)

# mysql -uUSER -p -hDBSERVER -e "UPDATE table SET field='test' WHERE status='test';" database

or

echo "UPDATE table SET field='test' WHERE status='test';" | mysql -uUSER -p -hDBSERVER database

Result

 # mysql -uUSER -p -hDBSERVER -e "SELECT field FROM table WHERE status='test';" database

 +---------------+
 | field         |
 +---------------+
 | production    |
 +---------------+

MySQL cli example (working)

mysql> UPDATE table SET field='test' WHERE status='test';

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Result

mysql> SELECT database.field FROM table WHERE status='test';

+---------------+
| field         |
+---------------+
| test          |
+---------------+

lsb_release

Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:        16.04
Codename:       xenial

uname

4.4.0-148-generic

MySQL version

mysql --version
Ver 15.1 Distrib 10.2.24-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Does anybody know what's going on here or how I can solve this problem?


texts: I am studying ways to implement audit tables to my PostgreSQL database and would like to know if it is possible to make a trigger for the data to be written to a table in a database other than the source database.


texts: Change column without interrupting service

texts:
We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with InnoDB storage engine for the table.

However, the table must be fully available for SELECT and UPDATE queries while doing the change. Every value in the column is a lot smaller than 512 bytes (in fact, no value is larger than 11 bytes). Is it somehow possible to change the column type without any downtime or at least very small downtime?


texts: How to list all forced query plans and cancel one

texts:
Using SSMS 2016 Query Store I forced a few weeks ago a specific query to use a specific plan. Now every now and then I have the error:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

I assume SET FORCEPLAN is passed automatically since the code executing that query doesn't.
So I would like to review my forced plan, maybe cancel it. But I can't find that query in the query store anymore.

Hence my question: is there a way to list all forced query plans and then how can I cancel one?


texts:
So we followed an best practice by Andreas de Ruiter (https://blogs.msdn.microsoft.com/andreasderuiter/2015/04/20/transforming-data-in-a-data-warehouse-through-sql-views)

The Idea behind it is, that you write SQL Views which are easy to maintain and deploy. You use stored procedures to use these views and create tables from it.
He suggests dropping or truncating the tables to maintain consistency over the data. I think it's the easiest approach without writing any complex update logic.

On these tables we have the need to set specific user permissions. By dropping the table we would lose them. So for us the question is, how could we drop the table and recreate the existing permissions on the newly created table?


texts: Shrink database is not shrinking as much as expected on a 1.5 TB database.

texts:
I'm working in an Enterprise system with a 1.5 terabyte database that is on a single filegroup, with the log in Simple Recovery Mode.  Since we have many teams working on different projects in parallel, we have about 20 copies of this database in different environments as well as the main DEV, QA, Staging and Production copies.

For DEV, QA and other environments we do not need the full database so we developed a script to remove old data from the top 20 tables.  This reclaims about 1 terabyte of free space.  Across all environments we will save at least 15 terabytes of disk space on the SAN.  I know this shrinking process causes index fragmentation - but don't mind rebuilding all indexes after the database has been shrunk.

We have two problems with Shrink database:

It seems to take forever; more than 48 hours.
It does not shrink the database as much as expected. The shrink process completes without error but leaves 100's of MB's of free space in the database.

I have tried:

DBCC SHRINKFILE ( 'XXXX_Data', NOTRUNCATE )
GO
DBCC SHRINKFILE ( 'XXXX_Data', TRUNCATEONLY )
GO
DBCC SHRINKFILE ( 'XXXX_Log' ) GO

I have also tried creating a new filegroup and moving data to it - with no result.

Has anybody had to do this type of thing before on a similar sized database?  Any recommendations on how best to do this?  Or does anybody know why the database shrink does not work?


texts: MySQL Database inside a VM on a high availablity cluster

texts:
I'm looking to do an infrastructure overhaul.Currently I have a ton of small databases scattered amongst our assets and I wish to consolidate them.

Is consolidating all databases to 1 tuned database a good idea? Will I get greater performance if I only have a single database VM on a cluster?

The tuning options I've been looking into are:

innodb_buffer_pool_size=8000M
innodb_flush_method=O_DIRECT
innodb_file_per_table
log_slow_queries=/var/log/mysql.slow.log
thread_cache_size=4
query_cache_limit=1M
query_cache_size=128M
max_connections = 400
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k
innodb_flush_log_at_trx_commit=2

Thank you for your time.


texts: PHPMyAdmin #2002 error - fixed by restarting mysql but it occurs again

texts:
I have a LAMP server running MySQL and PHPMyAdmin. Yesterday I got the following error trying to login to PHPMyAdmin:

#2002 - No such file or directoryThe server is not responding (or the local server's socket is not correctly configured).

By following the instructions that I found on previous posts I fixed the problem by killing the active mysql process and restarting mysqld. Today the same error appeared. The server was not rebooted. How can I prevent this problem to occurr again?
EDIT: some more information.
Ubuntu 16.04

PHP 7.0.33
mysql  Ver 14.14 Distrib 5.7.29


texts: Does bool_and short-circuit function calls with arguments?

texts:
Suppose I have the following query:

SELECT a.*
FROM a
CROSS JOIN b
GROUP BY a.id -- Primary key
HAVING bool_and(expensive_comparison(a, b))

What will PostgreSQL do with this query? Will it process expensive_comparison on all pairs, or will it stop and return immediately for a given for a given a.id?

Is there a more efficient, equivalent way to write this query without resorting to procedural code?

I'm using PostgreSQL 9.3, but if it's changed in any newer versions, that would be relevant info.


texts: Mysql Replication Master/Slave and Master/Master

texts:
Looking for some insight and opinions from some DBAs, as I've been tasked to look into using Master/Master replication for a single table in a database.

We currently run mysql replication using a Master/Slave setup for all of our DBs. Is it possible to run Master/Master on a single table within a database while the remaining tables are replicated using Master/Slave? Is this even possible, or just a nightmare?

Another way I've seen it done, is to run 2 different mysql daemons (running on different ports), one using master/master and the other running master/slave. This method though seems like it would require more maintenance. Again, any comments?

Thanks for your help.


texts: How to automate insertion of new column in Mysql table?

texts:
I have a legacy database, and several tables need a new column added that will act as the primary key for the table.  The trick is there are several tens of thousands of existing rows, and the data for the new column will be the concatenation of two field values within the table.

Specifically, the new column can be temporarily created via

SELECT *, CONCAT(ContractNum, '-', PlanId) AS ConPlanId FROM ContractPlans

Using a batch script, how can I populate the exising column fields which are currently null?


texts: Set the Priority for two different "after insert" triggers in SQL Server

texts:
I'm trying to create triggers by functionality,instead of DML operation.

For exp,I have 2 triggers A and B. A is for "AFTER insert, update , DELETE" , and B is for "AFTER INSERT". how can I know which one(A or B) is ran first after insert a new record?


texts: How to determine if a postgres database needs to be vaccumed?

texts:
Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed?

UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to be more experienced with postgres than they are. They are getting update times of several hours in their tests with 500K rows in a table. They had not done any changes to their GUCS for shared buffers, work_mem ... etc I just went through with them and had them do that. In some of their tests postgres reports 3674 seconds for updates that take 2.66 seconds in oracle and 33.8 seconds in MySQL. I want to be able to answer the question would Vaccum make a difference to their tests, but to be able to get real data back to them about the impact of vacuum on their tests.

Postgres 9.2 on CentOS 6 Intel Dual Process Quad Core Xeon K5570 8GB RAM


texts: Modelling a Database for Recording Taxes

texts:
I need to cater to a complicated system of taxes. Following are the possible scenarios:

Simple flat tax rate on net amount e.g. 5%, 12%, 18%, etc.
A compound tax (e.g. 5%) which may be composed as below:

State Tax on net amount  - 2.5%
Central Tax on net amount - 2.5%

There might also be a case of a compound tax, where taxes are applicable not on net amount, but on the previous (net amount + tax). Eg:

Net Amount = 100
Row (1) Plus Tax 1 (5%) = 100 + 5 = 105
Row (2) Plus Tax 2 (5%) on i.e. 5% on 105 = 105 + 5.25 = 110.25

Over and above all this, I need to allow the user to add user defined taxes, to cater to those tax situations which my in-built taxes don't cover.

SOME REQUIREMENTS:

From (4), it is clear there will be some system-defined taxes and some user-defined taxes.

Another requirement is that in the case of a compound tax, I need to keep track of taxes paid for each component individually. That is, in the examples above, I need to keep track of Central Tax, State Tax, Tax 1 & Tax 2 separately.

I was leaning towards a self-referencing many-to-many relationship like so:

taxes (tax_id, tax_name, tax_rate, ...)
tax_components (tax_id, fk_tax_id, order, ...)

The order column would be used to describe the computation order, as in case (3) above.

Example tables for case (2) above:

taxes
----------------------------------------
| tax_id | tax_name         | tax_rate |
----------------------------------------
| 1      | GST 5%           | 5        |
----------------------------------------
| 2      | State GST 2.5%   | 2.5      |
----------------------------------------
| 3      | Central GST 2.5% | 2.5      |
----------------------------------------

tax_components
------------------------------
| tax_id | fk_tax_id | order |
------------------------------
| 1      | 2         | NA    |
------------------------------
| 1      | 3         | NA    |
------------------------------

Problem with this design can be best described in one word - Complicated. It will create a complicated hierarchy of taxes with even more complicated queries.

Can anyone offer a simpler design?


texts:
I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance:

  Because rows are small (many fit a page) multiple locks may compete for one PAGELATCH.
  We can ”waste” a bit of space to get more performance. Solution: Pad
  rows with CHAR column to make each row take a full page.

Does anyone use this approach? What results have you got?

Thank you.

ADDED:

Warning. The linked article is not a guide to action. Thomas Kejser describes tests on terabyte-sized databases, no more.


texts:
I want to know which users have SYS and SYSDBA privilege before export/import operation.

I searched a lot but I could not find.


texts:
Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds instance, instance snapshot in AWS have 700GB. MySQL version 5.6, using innodb.
At moment it's been 20 hours sience restoration is running, my estimations are that its going to finish in 4 - 6 hours, so the complete restore will be about 24 hours and that is way too much downtime for production environment.
My question: How to tune mysql in order to speed up restoration?
Thank you in Advance.

P.S
Dump was done in 3 and a half hours.


texts: How can I programmatically grant (and revoke) temporary database access in MySQL?

texts:
Suppose I have a datastore comprising many databases. For the purposes of this question, let's assume MySQL, but I suspect it might apply to many technologies. The stored records aren't particularly special. They're not credit card numbers, financial transactions, or any type of data covered by existing American law or particular compliance levels. They are, however, production.

From time to time, users, be they developers, data quality specialists, or support personnel, need access to the data store. Traditionally, credentials were handled with RO users, but that method is inherently insecure insofar as the passwords require frequent rotation, whenever someone leaves the company or the credentials simply age. Most times, users need temporary access -- an afternoon, day, or week at most. Temporary credentials are a perfect fit, but a nightmare to manage by hand.

How can I effectively grant and revoke temporary access to my datastore, preferably programmatically? I can envision any number of scripts that leverage, say, a highly privileged user to add and drop temporary users, but it all feels like reinventing a wheel that should already roll. Google results are surprisingly unhelpful here, making me think the problem is either too easy to discuss or too difficult to solve.


texts: Writing a trigger for every time a user logs in

texts:
I want to write a trigger which runs every time a user logs in and saves the name of each user, for example.

I have written trigger in this way:

create or replace TRIGGER LOGON_TRG
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO t_log(ID,NAME) VALUES (S1.NextVal,ora_login_user);
END;

Does anyone have any solutions for this?


texts: What is the optimum method of finding the per-item earliest date from a clustered index

texts:
I have a SQL Server 2012 table which contains columns like this:

ID int NOT NULL,
EventDate datetime NOT NULL,
... 32 other columns...

where the table has about half a billion rows across a range of about 10000 distinct ID values.

The table has a unique clustered index like this:

CREATE UNIQUE CLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] (
    [ID] ASC,
    [EventDate] ASC
)

I need to find the earliest per-ID EventDate, which I can obtain using a query such as:

SELECT ID, min(EventDate) FROM [dbo].[MyTable] GROUP BY ID

However this query takes just under 2 minutes to complete.

I can't share the specifics (query plans etc.) of the problem I'm looking at due to NDA constraints, but I can advise that I'm seeing a clustered index scan, so it's checking all the rows in the table. Given that the data is organised in EventDate sequence I'd expect that the retrieval could be much quicker, but I'm not sure quite how. Any other ID specific range query responds in a few milliseconds and the table has recently been rebuilt and reindexed, so I don't think there are any statistic updates which would help.

Can anyone suggest a better method of determining the minimum per-ID EventDate value which avoids scanning the entire clustered index?

I do have a table with the (10 thousand) distinct id values.


texts: How can I eliminate this scalar function or make it faster?

texts:
I'm working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql server 2016)
Really not sure the best ways to modify this process.
Simplified select statement.
Select vendorpartid,
dbo.ufn_StockUomQuantityToOrder(vpp.priorityLevel, vpp.monthlyUsageRate, vpp.minimumPurchaseUomOrderQuantity, vpp.purchaseUomConversionFactor, vpp.orderFrequencyDays, vpp.boxQuantity) stockUomQuantityToOrder
FROM vendorpartpriority vpp

ALTER FUNCTION [dbo].[ufn_StockUomQuantityToOrder] (
            @priorityLevel decimal(38, 10),
            @monthlyUsageRate decimal(38,10),
            @minimumPurchaseUomOrderQuantity decimal(38, 10),
            @purchaseUomConversion decimal(38, 10),
            @orderFrequencyDays decimal(38,10),
            @boxQuantity int
        )
        RETURNS int
        WITH SCHEMABINDING
        AS
        -- Calculate the quantity that needs to be ordered
        BEGIN
            DECLARE @quantityToOrderDecimal decimal(38, 10) = NULL;
            DECLARE @quantityToOrderInt int = NULL;
            DECLARE @orderFrequencyMinimumQuantity decimal(38, 10) = NULL;
            DECLARE @minimumStockUomOrderQuantity decimal(38,10) = NULL;

            --set the default order quantity
            SELECT @quantityToOrderDecimal = (-1.0 * @monthlyUsageRate * @priorityLevel);

            --get the minimum order quantity in stock UOM
            SELECT @minimumStockUomOrderQuantity = (@minimumPurchaseUomOrderQuantity * @purchaseUomConversion);

            --calculate the order frequency minimum
            IF(@orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL)
                SELECT @orderFrequencyMinimumQuantity = (@monthlyUsageRate * @orderFrequencyDays / 30.0);

            --do we need to meet a vendor minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @minimumStockUomOrderQuantity)
                SELECT @quantityToOrderDecimal = @minimumStockUomOrderQuantity;

            --do we need to meet an order frequency minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @orderFrequencyMinimumQuantity)
                SELECT @quantityToOrderDecimal = @orderFrequencyMinimumQuantity;

            --convert to the int
            SELECT @quantityToOrderInt = CAST(CEILING(@quantityToOrderDecimal) AS int);

            --did we come up with a number that needs to be an increment of boxQuantity
            IF(@quantityToOrderInt IS NOT NULL AND @boxQuantity > 0)
                BEGIN
                    --get the partial box quantity if any
                    DECLARE @partialBox int = @quantityToOrderInt % @boxQuantity;

                    --remove the partial box and add a full one (if we are not of box increments)
                    IF(@partialBox <> 0)
                    BEGIN
                        SELECT @quantityToOrderInt = (@quantityToOrderInt - @partialBox + @boxQuantity);
                    END
                END

            RETURN @quantityToOrderInt;
        END;

GO

2019 isn't an option now. I have dabbled with the in-line table value, but I'm not quite sure how to get rid of the declares.
I'm not 100% sure on the nulls. My gut says I shouldn't be passing any values that are nulls, but looks like input table has quite a few in different columns.
Right now I'm trying to make some glaring performance changes until we get time to dismantle the whole process.


texts:
I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as does 'Partial Cache'. At this point, I cannot trust the 'Full Cache' in production if it fails mysteriously.

The expected result looks like this-

1        808    larry   curly   moe
2    808    larry   curly   moe
3    314    foo bar baz
4    314    foo bar baz
5    314    foo bar baz
6    314    foo bar baz

Full cache produces this instead-

1   808 larry   curly   moe
2   808 larry   curly   moe
3   314 foo bar baz
4   314 foo bar baz
5   314 null    null    null
6   314 null    null    null

The '314' example is an actual result. The first two records lookup correctly, the second two fail. If they were cache misses, they should fail for all four rows.

The failures are repeatable- the same items fail in the same sequence every run. The environment is an isolated test environment with three fixed databases that aren't changing between runs. Selecting Partial Cache is noticeably slower, implying that there's a significant number of cache misses. None of the tables are particularly large, a thousand rows or so.

What's going on? Should I just abandon the hope of ever using Full Cache?


texts: Can I remove binary log files not listed in mysql-bin.index?

texts:
We've had some problems with one of our more elderly MySQL servers (version 5.045).  The database is back up and running OK but there were over twenty thousand binary log files in the mysql folder.

I logged into MySQL and issued the following command after checking that data replication was all working OK...

purge binary logs to 'mysql-bin.024717';

The command completed without error and the resulting mysql-bin.index looks like this...

./mysql-bin.024717
./mysql-bin.024718
./mysql-bin.024719
./mysql-bin.024720
./mysql-bin.024721
./mysql-bin.024722
./mysql-bin.024723
./mysql-bin.024724

When I left mysql a lot of files had gone, but now there are still over a thousand binary log files left, all from before mysql-bin.024717 and most of them only containing 117 bytes.

Given that they are no longer referenced in the mysql-bin.index file is it OK for me to just go ahead and remove them with rm at the OS level or do I need to do something else to clean them up?

Thanks in Advance


texts:
I have one table for User detail in MySql with about 500000 records in it. I have also created fulltext index on firstname, lastname field on this table. but when I am trying to search any single latter/alphabet (e.g. a to z, single character), it is responding very slow in first time. It's taking about 5-6 seconds to respond. after that, it's come down to 800 milliseconds. EXPLAIN command seems ok as It shows "fulltext" in type column, but I couldn't find why it is reacting very slow.

my query is looks like as follows.

SELECT  SQL_NO_CACHE usr.id, usr.uname, ifnull(usr.fullname,'') fullname,
        ifnull(ct.City, '') city,
        MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) ordfld
FROM usertable usr
LEFT JOIN citymas ct ON ct.CityID = upm.CityID
WHERE usr.UserStatus IN(10,11)
AND usr.id <> 1
AND MATCH(usr.fname,usr.lname) AGAINST('a*' IN BOOLEAN MODE) > 0
ORDER BY ( CASE WHEN usr.fullname = 'a' THEN 1
                WHEN usr.fname rlike 'a%' THEN 2
                WHEN usr.lname LIKE 'a%' THEN 3
                WHEN usr.fname like '%a' THEN 6
                WHEN usr.lname LIKE '%a' THEN 7
                WHEN usr.fullname LIKE '%a%' THEN 8
                ELSE 10 END ),
 ordfld DESC,
( CASE WHEN ifnull(usr.cityid,0) = 234 THEN '0' ELSE '1' END ), usr.fullname
LIMIT 20

and explain show me following

1, 'SIMPLE', 'usr', 'fulltext', 'PRIMARY,IX_usertable_fname_lname', 'IX_usertable_fname_lname', 0, NULL        , 1, 'Using where; Using filesort'
1, 'SIMPLE', 'ct' , 'eq_ref'  , 'PRIMARY'                         , 'PRIMARY'        ,          3, 'usr.cityid', 1, NULL

above query is taking too much time, it is responding between 800-900ms.

Any guess?


texts:
I'm using the following command (Windows machine) with the MongoDB shell version: 3.0.7:

mongod --config "G:\NodeApps\mongod.cfg" --install

Contents of mongod.cfg file are given below:

systemLog:
    destination: file
    path:"G:\NodeApps\data\log"
storage:
    dbPath:"G:\NodeApps\data"

Getting the below error:

Error parsing YAML config file: yaml-cpp: error at line 4, column 8: illegal map value
try 'mongod --help' for more information

Similar questions doesn't have the solutions for this.

What I've already tried:

I'm using spaces (not tabs)
I'tried by saving the file in ASCII format, as It was mentioned in one of the posts that Mongod config file shouldn't be saved in non-ACSII format. Not even in UTF-8.

Please help me with this.


texts: Setup amazon aurora as replica to mariadb master

texts:
I am trying to setup replication between MariaDB 10.4.17 and Aurora 5.7.12 MySQL, with Mariadb as master and aurora as replica.
I am following this aws article
I noted the file name and position
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000169 |  1387829 |              |                  |
+---------------+----------+--------------+------------------+

I created mysql dump from mariadb and loaded that into aurora.
after which I created a replication user on the master instance like this
CREATE USER 'replication_aurora'@'%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replication_aurora'@'%';
and called the procedure on Aurora
CALL mysql.rds_set_external_master ('ip of master', 3306,
    'replication_aurora', '<password>', 'binlog.000169', 1387829, 0);

more about the above procedure here
then i called this procedure
CALL mysql.rds_start_replication;

This was supposed to start replication but I am getting this when i run
SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: master_ip
                  Master_User: replication_aurora
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000169
          Read_Master_Log_Pos: 1387829
               Relay_Log_File: relaylog.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000169
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.rds_replication_status,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_configuration,mysql.rds_history
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1387829
              Relay_Log_Space: 2754
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because a fatal error is encountered when it tries to get the value of SERVER_UUID variable from master.
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 210424 15:42:38
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

I am thinking error could be while creating the user I created 'replication_aurora'@'%' instead of creating 'replication_aurora'@'replica ip' is this the issue?
I am unable to check because the connection endpoint aurora gives is more than 60characters long so i cannot create a user with that endpoint on my mariadb master.
Question.

is not giving replica ip in user is the issue?
is compatibility the issue?
should i also create the same user on replica?

I am sort of at a dead end here please help any article or video would be helpful


texts: Failover Cluster Manager > Role > SQL Server (Node1 is owner) > Resources (SQL Server & SQL Agent)

texts:
I created 2 separate WFC, one for database (SQLClustr) and one for application (APPCluster). Each cluster has 2 nodes plus one disk witness in quorum. I can easily failover by rebooting the one of the nodes and the cluster manager perfectly handles the failover.
I have ip for each node, ip for each cluster, and ip for each resource/role.
I am trying to automate the failover, where if I turn off the application service in APPCluster, the other node fails saying that cannot connect to the database server. It hangs.
My question is, what goes in the connection string for the client application, the ip for the SQLCluster or the ip for the SQL resource?
Also, when I manually failover from Node1 to node2, using the the roles pane, the owner node changes and the services and shared disks move over to the node2. But, at the Cluster level, the current Host Server is still node1.
Both clusters are in a VM environment.
v/r;
Jawid


texts: Postgresql Total elements and affiliation users

texts:
I have a two table that contains varchar:
items_tags_sets: item_id, user_id, tag
and
users: user_id, name.
How to make a query that displays a table with columns:

Certain Item
Tags belonging to a specific element.
Total Number of duplicate tags.
The fact that a particular user has set this tag, like other users.

I need a result like this:
----------------------------------------------
| item_id |    tag    | tags_count | its_tag |
|--------------------------------------------|
|   it1   | tag_test1 |      3     |  usr1   |
|   it1   | tag_test3 |      2     |         |
|   it1   | tag_test5 |      1     |         |
----------------------------------------------

Link to code: https://rextester.com/live/KXLJX41855


texts: Why delete with "where in" condition is slow, while looping & using single where is fast?

texts:
I have a table with ~5 million rows. Here is create table statement:
CREATE TABLE `monitor_checks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `url` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_body_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_body_raw` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `request_body_json` json DEFAULT NULL,
  `request_body_form_params` json DEFAULT NULL,
  `request_headers` json DEFAULT NULL,
  `response_protocol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_code` int DEFAULT NULL,
  `response_reason_phrase` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_size` int DEFAULT NULL,
  `response_headers` json DEFAULT NULL,
  `is_successful` tinyint(1) NOT NULL,
  `is_failed` tinyint(1) NOT NULL,
  `fail_reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `response_time` double(8,6) DEFAULT NULL,
  `time_namelookup` decimal(8,6) DEFAULT NULL,
  `time_connect` decimal(8,6) DEFAULT NULL,
  `time_appconnect` decimal(8,6) DEFAULT NULL,
  `time_pretransfer` decimal(8,6) DEFAULT NULL,
  `time_starttransfer` decimal(8,6) DEFAULT NULL,
  `time_total` decimal(8,6) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `monitor_checks_monitor_id_created_at_index` (`monitor_id`,`created_at`),
  KEY `monitor_checks_node_id_foreign` (`node_id`),
  KEY `monitor_checks_monitor_id_is_failed` (`monitor_id`,`is_failed`),
  KEY `monitor_id_node_id_is_successful` (`monitor_id`,`node_id`,`is_successful`),
  KEY `monitor_checks_monitor_id_index` (`monitor_id`),
  CONSTRAINT `monitor_checks_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `monitor_checks_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=224279831 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I won't pretend that I know much about indexes, so maybe it's a complete mess. But still, I have the index: KEY monitor_id_node_id_is_successful (monitor_id,node_id,is_successful)
Now, when I execute this statement:
DELETE from monitor_checks where monitor_id in (~200 ids) and node_id = 1 and is_successful = 1
It takes a lot of time, a few minutes.
Now, if instead, I do 200 delete queries, looks like it takes less than a second.
I'm not even sure, I use Laravel framework and all I did is changed my code so that it loops through all values and deletes one-by-one.
Now if the show processlist I barely can see that the statement takes more than a second, while logs say that the whole loop for ~200 ids took a second.
Why so? I thought that if I use batch delete it should be faster?
UPD:
explain statement for where in returns:

Type: range

Possible indexes:

monitor_checks_monitor_id_created_at_index,monitor_checks_node_id_foreign,monitor_checks_monitor_id_is_failed,monitor_id_node_id_is_successful,monitor_checks_monitor_id_index

key:

monitor_checks_monitor_id_created_at_index

key len: 4

Rows: 73485


texts:
So I have a database of information that is imported from a separate application. This table contains any number of 'rule' results, each rule result is of a certain type ('rule_1', 'rule_2', etc).  This table could contain multiple rule results with the same type.  Each rule result has a rule result value of P (passed), V (violation) or W (warning).

I'm trying to get a count of how many rules (not rule results) are of each rule result value (P, V or W) for a given code (1.2) with the following understanding...If any of the 'rule_1' records are a result of V then the count would be increased for V regardless of if 1 rule result value was V or if 100 are.

In simple terms I'm summarizing the V, W, P counts for the code 1.2, with V > W > P in terms of weighting.

+---------+--------+------+
| rule_id | result | code |
+---------+--------+------+
  RULE_1      V      1.2
  RULE_1      W      1.2
  RULE_1      P      1.2
  RULE_2      W      1.2
  RULE_2      P      1.2

Expected result would be

+---------+---------+---------+
| v_count | w_count | p_count |
+---------+---------+---------+
    1          1         0

Currently I'm using the following SQL statement, my question is, is this the way to do it or am I missing something simple, this statement seems very convaluted.

SELECT  DISTINCT
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "V"
    ) as v_count,
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ruleresult
            WHERE  code = "1.2"
              AND  result = "W"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V"))
    ) as w_count,
    (
        SELECT  COUNT(DISTINCT rule_id)
            from  ada_ruleresult
            WHERE  code = "1.2"
              AND  result = "P"
              AND  rule_id NOT IN (
                SELECT  rule_id
                    FROM  ruleresult
                    WHERE  code = "1.2"
                      AND  result IN ("V", "W"))
    ) as p_count
    from  ruleresult;


texts: Can we do PITR of one specific PostgreSQL database

texts:
I am new to PostgreSQL, so please ignore if my question sounds stupid.
Imagine we have three databases in PostgreSQL and someone mistakenly ran a TRUNCATE command an hour ago on one of the databases. Now we want to do a PITR for that database alone, since there is an issue with only one database.
Is it possible in PostgreSQL to do PITR for one specific database OR it can happen only at instance level?
I read the documentation for Continuous Archiving and Point-in-Time Recovery (PITR). It looks like it can only be done at instance level (I guess we say instance as "Database Cluster" in PostgreSQL world, I come from SQL Server world).

As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset.

If PITR is not possible at DB level, then how Prod DBAs are handling this situation?
Also one last thing, I have a test server with multiple DBs and if PITR is possible at DB level, then please share me the steps so that I can test it myself.
I am mainly interested in supported versions of Postgres. My test server is PostgreSQL 9.6.16.


texts: Refactor SQL to left join on TVF requiring input variable

texts:
I would like to join two tables and a table-valued function. The TVF take a date as input variable.

The expected output is:
Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible

The Tables:

TABLE_A: Date, LimitName, LimitValue, Limit >> main table that
specify limits I want to check
TABLE_B: Date, LimitName, ProductType (one-to-many) >> helper table that specify which products types map into each limit
TABLE_C (TVF): (several identification columns, like ID value, dates) + Date, ProductType, ProductValue, IsProductEligible >> the
main product data to be aggregated and mapped into the limits

I am able to get the expected result using a query as below.

However, because the TVF takes a date as input variable, this query requires specifying the date 3 times. I would like to refactor the query to specify the date once, and have that condition applied to the TVF.

Reading up on this, I think that cross apply and outer apply should be able to do this, but I cannot seem to get syntax that works. I end up having to put where conditions in place, which drops rows (works like inner join rather than left join). I would appreciate some direction on how to make this work.

select
  a.Date
, a.LimitName
, sum(c_elig.ProductValue) as ProductValueEligible
, sum(c_non.ProductValue) as ProductValueNonEligible

-- TABLE_A: Date, LimitName, LimitValue, Limit
from Limits a

-- TABLE_B: Date, LimitName, ProductType (one-to-many)
left join LimitsProductTypes b
on b.Date = a.Date
and b.LimitName = a.LimitName

-- TABLE_C (TVF): <identification columns, like ID value, dates> + Date, ProductType, ProductValue, IsProductEligible
left join (
    -- Find eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'TRUE'
    group by Date, ProductType
) c_elig
on c_elig.Date = a.Date
and c_elig.ProductType = b.ProductType

left join (
    -- Find non-eligible rows, and group by Product
    select Date, ProductType, sum(ProductValue) as SumProductValue
    from Function_DataSet('2019-05-15') --<------------------------------------- TVF, takes a date input
    where IsProductEligible = 'FALSE'
    group by Date, ProductType
) c_non
on c_non.Date = a.Date
and c_non.ProductType = b.ProductType

where a.Date = '2019-05-15'   --<------------------------------------------- want to just specify date here

group by a.Date, a.LimitName


texts: How does the Distributor know you have seeded/initialized your Subscriber using a backup?

texts:
Im building my first transactional replication database for our Sharepoint team.  The Publisher database is fairly large 330GB+  Because the Publisher database is in a different data center than the Distributor and Subscriber database I will be perfoming the initial seed using a database backup instead of using a snapshot.

I understand that the Publisher will know the last log sequence number (LSN) from when the backup was taken.  The Subscriber will be populated from the back up and have it's own last LSN from the restore.

Im not sure how the process will work once I turn the synchronization on after everything has been initialized/restored.

Does the Publisher talk to the Subscriber through the Distributor to know how far out of sync they are?  I thought the Publisher and Subscriber did not have any real direct communication?
How will the proper records be sent to the Distributor from the Publisher when the Distributor is unaware of the initialization from backup.

Extra Details:
Publisher: SQL Server 2008 R2
Distributor: SQL Server 2012
Subscriber: Sql Server 2012


texts: $addToset do not work in update if record not exists

texts:
I am trying to use following query

db.TestColl.update({ "tags" : "silk" , "host" : "host.com"},{ "$set" : { "cat" : "MOVIE" , "$addToSet" : { "articleId" : { "$each" : [ "20442242"]}} , "publishTime" : 1370427540000} , "$inc" : { "count" : 1}},true,false);

The reason is if a record exists and a new article id comes it will be added to the set and count will be updated but if the record does not exists then it should store a record. But it gives me an error that while storage $addtoSet and $each are not valid modifier.


texts: How to prevent duplicate or similar data from getting processed?

texts:
I have a stored procedure which calculates the distance between 2 coordinate points:

CREATE PROC loc.CalcDistanceByPostalCode
(
@From VARCHAR(10),
@To   VARCHAR(10)
)
AS
BEGIN
    DECLARE @data table (From_Place VARCHAR(200), From_Region VARCHAR(200), "To_Place" VARCHAR(200), To_Region VARCHAR(200), Distance decimal(8,2))
    DECLARE @postal1 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))
    DECLARE @postal2 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))

    INSERT INTO @postal1 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude
                         from loc.locations l
                         cross join loc.locations_postal lp
                         where postal_code like @From and lp.city_id = l.ID
    INSERT INTO @postal2 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude
                         from loc.locations l
                         cross join loc.locations_postal lp
                         where postal_code like @To and lp.city_id = l.ID

    INSERT INTO @data
    SELECT
        t1.place_name,  t1.region_name, t2.place_name, t2.region_name,
        loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance
    FROM @postal1 as t1
    CROSS JOIN @postal2 as t2
    WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude

    DECLARE @Distance decimal(8,2) = 0
    select * from @data order by Distance desc
    SELECT @Distance = Distance from @data
    return @Distance;
END

It calculates correct result if @From and @To are different, but gives redundant data if @From and @To are same.

Example: Different @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '400%'`

Result:

From_Place  From_Region To_Place     To_Region      Distance
Delhi       Delhi        Mumbai      Maharashtra    1160.65
New Delhi   Delhi        Mumbai      Maharashtra    1157.80
Delhi       Delhi        Vasai-Virar Maharashtra    1115.68
New Delhi   Delhi        Vasai-Virar Maharashtra    1112.87

Example: Same @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '110%'`

Result:

From_Place  From_Region To_Place    To_Region   Distance
New Delhi   Delhi       Delhi       Delhi       3.51
Delhi       Delhi       New Delhi   Delhi       3.51

Do I need to write extra code to check if Distance is same then compare the From_Place with To_Place with the rows having the same distance? or any optimised way to prevent the duplicate data?


texts: List ranges and the total count based on condition

texts:
Disclaimer: I have already posted this question yesterday here. As there were no response there re-posting it in dba forum now.

Table Schema:

CREATE TABLE [dbo].[TblMaster](
    [SID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [VID] [int] NOT NULL,
    [CreatedDate] [datetime] default (getdate()) NOT NULL,
    [CharToAdd] [varchar](10) NULL,
    [Start] [int]  NOT NULL,
    [End] [int] NOT NULL
)
GO

CREATE TABLE [dbo].[TblDetails](
    [DetailsID] [int] IDENTITY(1,1) NOT NULL Primary Key,
    [SID] [int] NOT NULL,
    [Sno] [int] NOT NULL,
    [ConcatenatedText] [varchar](20) NOT NULL,
    [isIssued] [bit] default (0) NOT NULL,
    [isUsed] [bit] default (0) NOT NULL
)
GO

Sample data:

Insert into dbo.TblMaster Values (1,default, 'CA', 1, 5)
Insert into dbo.TblMaster Values (1,default, 'PA', 1, 5)
GO
Insert into dbo.TblDetails values(1, 1, 'CA1', 0,0)
Insert into dbo.TblDetails values(1, 2, 'CA2', 0,0)
Insert into dbo.TblDetails values(1, 3, 'CA3', 0,0)
Insert into dbo.TblDetails values(1, 4, 'CA4', 1,0)
Insert into dbo.TblDetails values(1, 5, 'CA5', 0,0)
Insert into dbo.TblDetails values(2, 1, 'PA1', 0,0)
Insert into dbo.TblDetails values(2, 2, 'PA2', 0,0)
Insert into dbo.TblDetails values(2, 3, 'PA3', 1,0)
Insert into dbo.TblDetails values(2, 4, 'PA4', 0,0)
Insert into dbo.TblDetails values(2, 5, 'PA5', 0,0)
Insert into dbo.TblDetails values(3, 1, '1', 0,0)
Insert into dbo.TblDetails values(3, 2, '2', 1,0)
Insert into dbo.TblDetails values(3, 3, '3', 1,0)
Insert into dbo.TblDetails values(3, 4, '4', 0,0)
Insert into dbo.TblDetails values(3, 5, '5', 0,0)
GO

Expected Output:

Query I have build till now (result doesn't match the expected output)

Declare @VID INT  = 1

    ;WITH Tmp as
    (
      SELECT
            TM.CharToAdd as Prefix,
            sno,
            sno - ROW_NUMBER() OVER(ORDER BY sno) as grp
      FROM dbo.TblDetails TD
      LEFT JOIN dbo.TblMaster TM on TM.[SID] = TD.[SID]
      WHERE isIssued = 0 and isUsed = 0
            AND  TM.VID = @VID
    )
    SELECT  Prefix,
            MIN(sno) as RangeStart,
            MAX(sno) as RangeEnd,
            COUNT(*) as [Count]
    FROM Tmp
    GROUP BY grp, Prefix

In the TblDetails table want to find the range of available values and its total counts from all records whose bit columns are 0. If bit column is 1 then it means it is already used so I am trying to skip it and list rest as available records. Doubtful whether am I explaining the problem statement well so have provided the sample data and expected output for better understanding. I did try doing some recursive function but the result isn't matching the expected output. So looking for help to resolve this.


texts:
Is it possible to run a Unix command using a query in Oracle?

I want to run simple commands (like df -h) using a query.

Is this at all possible or am I wasting my time? I don't want to use a different language like Java or C to call a procedure, it needs to purely PL/SQL.


texts: Create a table dynamically in SQL Server

texts:
I am trying to create a table dynamically depending upon current date in SQL Server. But I am unable to do so. I am getting error as invalid identifier.

These are the details:

SQL query:

DECLARE @tableToDump nvarchar(100);

SET @tableToDump = 'backupCdc'+cast(cast(GETDATE() as date) as char(100));

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';

exec @DynamicSQL;

Output (error):

  The name 'create table backupCdc2015-09-10 (cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))' is not a valid identifier.


texts: Help with a multi-language medical database

texts:
My project is to design a database for medicines in many countries.

Medicines have various criteria like composition, price, manufacturer.
Database will be multi-language; related to each country.
Database will increase in size over time.
Database will have more than just other data on medicines so it will become more complex over time.

Should I make a separate database for each country?

I use SQL Server 2008.


texts: Creating blank/deleting rows form excel using SSIS

texts:
I have an .xlsx file I import data from nightly.
Once the import is complete I delete the tables(sheets/tabs) in the excel file using an Execute SQL Task so that the file is blank for the user but contains correct header rows:

DROP TABLE `sheet1$`
GO

DROP TABLE `sheet2$`
GO

I then re-create the tables (sheets/tabs) using another Execute SQL Task on completion of the last:

CREATE TABLE `sheet1$` (
`Column 1` LongText,
`Column 2` LongText
)
GO

CREATE TABLE `sheet2$` (
`Column 1` LongText,
`Column 2` LongText,
`Column 3` LongText,
`Column 4` LongText,
`Column 5` LongText,
`Column 6` LongText
)
GO

The problem I have is that say 10 rows existed on day 1. 10 records are imported. The tables(sheets/tabs) are deleted. The tables(sheets/tabs) are re-created but for some reason the number of rows are still part of the sheet. So for instance if on day 2 no new records where added to the file then the import imports 10 blank rows. It's like they are being cleared but excel is holding some sort of reference to data rows.
If the file has not been added to for a few days then it just continues to import 10 blank rows each night filling up the table with nonsense rows.

Obviously I can't use the following otherwise I wouldn't have to drop and re-create.

DELETE * FROM `sheet1$`

What am I missing/doing wrong?

There are many workarounds I can think off such as:

An sql script that deletes NULL rows from the SQL table where they are imported to but this messes up the auto increment ID field which I need to be the same as the record count.

So I could:

Import to an interim temp table and only import rows that contain data to the final table.

OR

After import copy data to another table, delete NULL rows, truncate original table and import back (retains ID increment)
...etc etc

I am sure the workarounds are not needed and complicating the solution.

Thanks


texts: How I can aggregate all my json-generated values into a single json array for pg-diff configuration?

texts:
As I was making my pg-diff config I needed to generate the table configuration in order for the pg-diff to compare both schema and data.
But being lazy TM I wanted the postgresql to do the dirty job of generating the json for me:
"select
        table_name::text as table_name ,
        json_build_object(
            'tableName'::text,table_name::text,
            'tableSchema'::text,table_schema::text,
            'tableKeyFields'::text,jsonb_agg(column_name::text)
        ) as table_info
    FROM
        information_schema.columns
    WHERE
        table_schema = 'public'
    group by
        table_name,table_schema;"

So for each table I generate the following json:
{
  'tableName': "one",
  'table_schema':"public",
  'tableKeyFields':["id","colimn1","column2",..."columnn"]
}

But I want all the generated json objects that table_info contains to generate into a large string value where I can copy-paste it into the pg-diff's pg-diff-config.json config.
How I can aggregate the query's results bellow into a single-column that is a string one so from my tool eg. Dbeaver, Datagrid etc etc to be able to easy copy-paste it into pg-diff's config?


texts: What is the cost of changing the search path in postgres in a schema-per tenant model

texts:
I am working on a multi-tenant storage gateway.

Each tenant has his own schema -- the schema for each tenant is a meta-schema so each tenant schema is identical. The DB interactions only occur through a stored procedure API. The stored procedure layer is on the public schema. The stored procedures rely on the search_path being set to <TENANT_SCHEMA>;public.

The application layer prepares statements against the stored procedures in the public schema.

I have a stored procedure for switching the tenant that looks as follows:

CREATE OR REPLACE FUNCTION domains_switch(domain DOMAIN_NAME) RETURNS VOID AS $$ BEGIN
  IF EXISTS (SELECT NULL FROM public.domains WHERE domain_name = domain) THEN
    EXECUTE format('set search_path=%s,public;', domain);
  ELSE
    RAISE 'domain "%" does not exist',domain USING ERRCODE = 'AX001';
  END IF;
  RETURN; END;
$$ LANGUAGE plpgsql;

plpgsql stored procedures manage plans themselves which means I only need to prepare the api statements once per backend in the gateway. I suspect the stored procedure's internal cached plans are invalidated when I switch the schema.

I am planning to add a layer to the gateway's connection pool management to minimise search_path switches. Is my intuition correct? Is this the right thing to do?


texts:
What is the use of a table with only one column?

TableName
----------
Table_PK

Say I have a table named Academic and the staff has a positionID but this positionID is:

tablePosition
----------------
PositionID

thats it...only one column


texts: Why is removing the Identity property on a column not supported

texts:
I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed.  And that this was "By Design" (not just a missing feature).

Here is an example I found on a blog. It involves updating the system tables.  (And that ability was removed after SQL Server 2000.) I get that doing this via system tables is not a good idea.  I am just wondering why a feature to do this another way is not around.

Working around this is going to cause me a considerable amount of work.  (Copying many hundreds of millions of rows to new tables in a downtime intolerant environment.)

So I thought I would ask "Why".

What changed in Sql Server 2005 and later versions that made this a bad thing?  Or was it always bad, and just not locked down?

What "Best Practice" (or similar principle) would be violated by making an identity column a normal column again?

--

Update to answer the request for "why I am doing this":
This is a very high level summary:  I am going to start adding partitions to my tables.  (So that I can archive/purge old data.)  That is all easy.  But I occasionally need to move a record to a different partition so it does not get removed (when a partition comes up for archival/deletion).  (I am having my partitioning column increase by 2 so that there is always space to move the row to a different partition.)

But if the partitioning column is an identity column, then I have to delete and re-insert the value (there is no way to update the value of an identity column).  Which causes issues with replication.

So I am wanting to use a sequence instead of an identity column.  But that switch is very very hard on large databases.


texts:
I am facing an issue with the following query. When I execute the query, it takes very long. I broke the query into two parts, compared with a shell script, but is there any chance to go with one query?

Any suggestion welcome.

select distinct substring(mobile_num,3,12)
from mobile
where  status ='INACTIVE'
  and date(unsub_date) >= DATE(CURDATE() - INTERVAL 90 DAY)
  and mobile_num not in(select distinct mobile_num from mobile where status='ACTIVE')
order by updtm;

| mobile_num  | varchar(12)
| keyword     | varchar(45)
| sub_date    | datetime
| unsub_date  | datetime
| circle_name | varchar(45)
| type        | varchar(45)
| status      | varchar(45)
| operator    | varchar(45)
| act_mode    | varchar(45)
| deact_mode  | varchar(45)
| id          | bigint(20)
| updtm       | timestamp


texts:
I have been receiving messages in my log file saying :-

"Login failed for user 'sa': Reason Password did not match that for the login provided. [Client:xx.xx.xx.xx]"

the xx are actual numbers.

In the MS firewall, I've added my IP to the "Remote IP address" list on the Scope tab.

When I have gone back to the log file, the message has stopped appearing.  Instead it says "its starting up ReportServerTempDB", does this mean that I've inadvertently by adding my IP to the remote IP list allowed anyone in?

By putting only my IP address in the scope means I've blocked everyone else apart from me connecting to the remote database?

Ultimately, I would like remote access allowed only to my IP.  If that is not possible, I want a block on all remote access.


texts: Couldn't open extension control file plpython3u.control : No such file or directory

texts:
I want to have python3 in my postgresql database for writing stored procedures.

Being in the psql client, when I enter the command create extension plpython3u I get the error:

couldn't open extension control file /usr/share/postgresql/9.1/extension/plpython3u.control : No such file or directory

I have checked, there are plpythonu.control and plpython2u.control in the directory, but not the one for version 3.

However I have installed the packages python3 and python3-postgresql (among others) from depot. I'm using Ubuntu 12.04, kernel 3.2.0.38, with postgresql 9.1 installed.

What should I install (or do) to have the plpython3u.control file on my machine and have python3 available in my database?


texts: At what point does a database update its indexes in a transaction?

texts:
I'm trying to understand the sequence of events in inserts where both an index and a transaction are involved.

For example, Oracle documentation states:

  If you create [or have] one or more indexes before loading data, the database then must update every index as each row is inserted.

But what happens if I create a transaction, insert five rows, then commit? Are indexes updated for every insert, or just at the commit point?

Logic tells me that they would only be updated at the commit point, because an updated index couldn't possibly be of use until those records are committed. But is that true?

If so, when I have 1m rows to insert, for best performance I should do one large commit of all rows and not 10 transactions of 100k records? Of course I realize this risks a greater rollback if row 999,999 fails.

Apologies if my terminology is a little out. I'm not a DBA by trade. I'm not so much interested in a particular database, as databases in general, although Oracle and Postgres are what I use most. I've searched on this topic but can't really find a definitive answer.


texts: How to run procedure in Oracle SQL developer

texts:
Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure

-- start the script

SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON

CREATE OR REPLACE PACKAGE BODY hotel AS

    -- -----------------------------------------------------
    -- Table city
    -- -----------------------------------------------------
    PROCEDURE fill_city(number_city NUMBER) IS
        city VARCHAR2(100);
        postna_st VARCHAR2(4);

    BEGIN
        FOR st IN 1..number_city LOOP
            city:= dbms_random.string('a',100);
            postal_number:= dbms_random.value(1000,9000);
            INSERT INTO CITY(city, postal_number) VALUES (city, postal_number);
        END LOOP;
    END;

BEGIN
    NULL;
END hotel;
/

SHOW ERRORS;

creating the procedure

CREATE OR REPLACE PACKAGE hotel AS
    PROCEDURE fill_city(number_city NUMBER)
END hotel;
/

SHOW ERRORS;

and now how to execute the procedure?

--EXECUTE fill_city(10000) ;
Begin
 fill_city(10000);
End;

I tried both but with luck.

I recived the following error

 Error report:
ORA-06550: line 2, column 2:
PLS-00201: identifier 'fill_city' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Elapsed: 00:00:00.018


texts: Store a small log file as a blob in sqlite

texts:
I am utilizing a small sqlite database to keep track of some jobs that run on a linux box. Most of them have small (less than 50 lines) log files... I'd like to store those log files as a record in my database....

How can I get a text log file into my sqlite database? I want it stored as text in the record, not as a file.


texts: How to determine how much storage to store backups?

texts:
I was thinking about this question ( Here where I work, we have a lot of space so backups are dayli ok ), and i'm thinking about if I was hired in another company, and see that they don't have backups, because there's no space.

How can I say " So, we need xTB of storage for the backups".?

There are some questions here and stackoverflow saying to use sp_spaceused to get an approach. But I think this is wrong because I made 3 tests, and all of them are wrongs. ( In one test, My database has 257MB of size, and reserved space on sp_usedspaceis 109.064 KB. When I create a backup, the backup has 24.729kb).


texts: Within a trigger is there a way to tell if an update or insert came from a trigger or not?

texts:
In PostgreSQL 9.3.5, I'm trying to prevent a trigger from executing again due to an update or insert within the current trigger or another trigger that executes upon update or insert. So I want to know what the source of the update is so I can prevent recursive triggering.

Unless I missed it, I looked here and didn't read anything about this in the manual:
https://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

I don't think this would work because I am thinking it will always return the trigger I am currently in, not the source (trigger or not) of the udpate or insert:

TG_NAME

  Data type name; variable that contains the name of the trigger actually fired

Currently I am doing a lot of old.col_nm <> new.col_nm etc. to try and determine via the values that change if it was one of my triggers or not. It would be nice if I could just say "if an update or insert came from a trigger return new;" and skip the bulk of the trigger.


texts: Partition by year and sub-partition by month mysql

texts:
I have a database with a date column having datatype as date. I am using mysql 5.6

The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub partitions on months of that particular year.

I have tried is,

ALTER TABLE nav_master
    PARTITION BY RANGE( YEAR(nav_date) )
    SUBPARTITION BY HASH( MONTH(nav_date) )
    SUBPARTITIONS 12 (
        PARTITION january VALUES LESS THAN (2),
        PARTITION february VALUES LESS THAN (3),
        PARTITION march VALUES LESS THAN (4),
        PARTITION april VALUES LESS THAN (5),
        PARTITION may VALUES LESS THAN (6),
        PARTITION june VALUES LESS THAN (7),
        PARTITION july VALUES LESS THAN (8),
        PARTITION august VALUES LESS THAN (9),
        PARTITION september VALUES LESS THAN (10),
        PARTITION october VALUES LESS THAN (11),
        PARTITION november VALUES LESS THAN (12),
        PARTITION december VALUES LESS THAN (13)
    );

I am able to execute the above query on empty table. But when I insert or when I execute the above query in same table with some data I get the below error.

#1526 - Table has no partition for value 2014

Not able to understand what the exact error is.

Also let me know if sub partitions help in better optimizing the tables or I should just go with partitions?

Update: I was indeed able to create the partition. The issue now is that there is a NULL partition created. When I had ensured there is no null dates and also all the date conditions were handled while making partitions.

Question is how can I see what data has gone to NULL partition? So that I can get an idea on what the problem is.

I am getting a null partition name using this query.

SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'nav_master' limit 0,180

Using this query to get all rows in partition, question is what to pass as partition name for null?

SELECT count(*) FROM nav_master PARTITION(??);


texts: In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?


texts: SQL Server 2019 AlwaysOnAG DBCC CHECKDB on synchronous replica setup as not readable

texts:
I've seen answers in this exchange that say DBCC CHECKDB should be able to run on AOAG synchronous replicas that are not open for read.  That MS fixed that because it was a bug in 2016.   I'm using SS 2019 CU8 Developer in QA/Test env and it will not allow integrity checks on the synchronous replica.  Note I didn't create the secondary failover/replica as readable as I don't want to license the secondary.
Note I do run integrity checks on the primary - I would also like to run them on the secondary.  After all - they do switch roles every now and then...for server and SS maintenance/patching.
I am using Ola Hallengren's databaseIntegrity Check scripts to run DBCC on all DBs except the secondary replicas in AOAG:  EXECUTE [dbo].[DatabaseIntegrityCheck]
@Databases = 'ALL_DATABASES',
@AvailabilityGroupReplicas = 'PRIMARY',
@LogToTable = 'Y'
Because the DBCC CHECKDB will error on the secondary replica saying it is not open for queries...etc..


texts: MySQL refuses UPDATE with (1071, 'Specified key was too long; max key length is 1024 bytes') sometimes

texts:
I'm using MySQL 5.7.2.23.

I need to copy some columns between two tables (u -> m) and I am using the following update command:

UPDATE m
 JOIN u
  ON u.id=m.user_id
  AND u.package_name=m.package_name
 SET
    m.host_version = u.host_version,
    m.device_model = u.device_model,
    ...
     m.device_name = u.device_name
 WHERE    shard_id = 96

The UPDATE works

Both tables have keys on (id, package_name):

CREATE TABLE u (
  id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version` int DEFAULT NULL,
  shard_id` int NOT NULL DEFAULT '0',
  device_group_id` int DEFAULT NULL,
  PRIMARY KEY (shard_id`,`package_name`,`id`),
  UNIQUE KEY by_id` (`id`,`package_name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE m (
  user_id varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_model varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version int DEFAULT NULL,
  PRIMARY KEY (package_name,user_id),
  UNIQUE KEY by_id (user_id,package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Innodb default row format is innodb_default_row_format=dynamic and
Innodb innodb_large_prefix=ON.

And yet for some shardId-s I get the above error message (key too long).

Explain shows the following

id            | 1
select_type   | SIMPLE
table         | u
partitions    | <null>
type          | ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 5
filtered      | 100.0
Extra         | <null>

id            | 1
select_type   | UPDATE
table         | m
partitions    | <null>
type          | eq_ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 1534
ref           | activitydb.u.package_name,activitydb.u.id
rows          | 1
filtered      | 100.0
Extra         | <null>

I understand that the key is 1534 due to 3-byte UTF-8 encoding and changing the columns to latin1 reduces the key length and solves the problem.

What I don't understand is why MySQL complains only on some shard_id values. The shards that do work seem to have a very small number of rows.

And in general we use the above JOIN for many SELECT queries and MySQL does not complain about key length.


texts: How do I find the least optimised query?

texts:
A library uses a relational database to store data about library books, their members and lending (books, which are currently borrowed). The database relations of this system are given below.

  The tables are shown in the following format:
  Tablename(column(length),column(length),column(length))

Book(bookno(13), title(20), author(30), edition(7))

Member(memno(4), name(26), category(5), address(40), phone(15))

Lending(memno(4), bookno(13), duedate(8))

The library database currently has 10,000 book tuples and 1,000 member tuples. Assume that 400 book borrowings are on record and 10 copies of the bookno "81-203-1257-0" is on lend.

The following SQL query has been written to retrieve borrower's name with due date given the book no "81-203-1257-0".

SELECT m.*, l.*
FROM Member m, Lending l
WHERE m.memno = l.memno and bookno = "81-203-1257-0";

The following are relational algebra expressions to retrieve the information for the above query.

(i) JOIN(Member and Lending Over memno) where bookno="81-203-1257-0")

(ii) JOIN(Member and (RESTRICT Lending where bookno="81-203-1257-0") over memno

(iii) CARTESIAN(Member and Lending) where bookno="81-203-1257-0")

(iv) RESTRICT(CARTESIAN (Member and (RESTRICT Lending where bookno="81-203-1257-0")) where Member.memno=Lending.memno

(v) PROJECT m.*, l.* (RESTRICT Member m and Lending l where m.memno = l.memno and bookno = "81-203-1257-0")

Which one is the least optimised, and how do I compare with it others? I am new  to database and query optimisation.


texts: Case Insensitive ORDER BY clause using COLLATE

texts:
I have spent a long time looking for this, and I am getting mixed messages.
In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause:
SELECT *
FROM orderby
ORDER BY string COLLATE … ;

--  SQLite:     BINARY | NOCASE
--  MariaDB:    utf8mb4_bin | utf8mb4_general_ci
--  Oracle:     BINARY | BINARY_CI
--  MSSQL:      Latin1_General_BIN | Latin1_General_CI_AS

I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL.
Is there a COLLATE clause value that would sort Case Insensitive?
I know there are many questions regarding case sensitivity, but (a) most of them are old and (b) none that I have seen relate to the COLLATE clause.
FWIW, I am testing on PostgreSQL 11.8. I have a test fiddle on http://sqlfiddle.com/#!17/05cab/1, but it’s only for PostgreSQL 9.6.
MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.


texts: How to avoid modifications during pg_dumpall

texts:
I want to do a final pg_dumpall on a server, then copy the output to a different server and restore the db there.

How can I avoid modification to the database during pg_dumpall?


texts: I am trying to make a simple DB in Access for a theoretical pizza shop(the db is for an assignment). What I am trying to do is link one type of pizza to one or more types of toppings. I just wanted a second opinion. Here's the schema:


texts:
I have database but somehow it has been deleted. Can i take the back-up of deleted database.

Please Help.

Thanks In Advance


texts: Connecting 4 tables - finding some differences between them

texts:
I have 4 tables in a Oracle database:

SOL_PUBLIC.SOL_CHEST@RACDB (PUB):

ID     ...
100    ...
200    ...
300    ...

SOL_PUBLIC.COORD@RACDB (PUB):

ID FK_ID_CHEST  x  y
1  100          11 11
2  200          22 22
3  300          33 33

SOL_CHEST (PROD)

ID     ...
100    ...
200    ...
300    ...

SIM_SOL.COORD (PROD)

ID FK_ID_CHEST  x  y
1  100          11 11

so, some rows are missing from PROD part.
I want to get all rows missing from PROD.COORD table that are in PUB.COORD table.

I first tried to get all 4 tables "in the same place"

select *
    from SOL_CHEST PROD,
        SOL_PUBLIC.SOL_CHEST@RACDB PUB
    inner join SOL_PUBLIC.COORD@RACDB coord_pub
        on coord_pub.FK_ID_CHEST=PUB.ID
    inner join SIM_SOL.COORD coord_prod
        on coord_prod.FK_ID_CHEST=PROD.ID

but it gives me

[Error] Execution (67: 40): ORA-00904: "PROD"."ID": invalid identifier

how to do this (connecting the 4 tables and get the rows missing from prod.coord that are in pub.coord)
Thanks!


texts: PostgreSQL DISTINCT ON forces a specific order

texts:
Why DISTINCT ON is sorting the rows? E.g. the query
SELECT DISTINCT ON (a) a
FROM (
  VALUES (2), (3), (1)
) x(a);

will have the result
1
2
3

while without DISTINCT ON
SELECT a
FROM (
  VALUES (2), (3), (1)
) x(a);

I'll get the correct order
2
3
1

How can I preserve the natural order of defined values?  By "natural order" I mean "the order in which the values were defined". I understand that DBMS may change the order if I don't specify it explicitly with an ORDER BY, but I was not expecting that DISTINCT ON my interfere in this way.


texts: How to verify that at least one point fall into a polygon with Postgis?

texts:
I have two tables where table A has a geometry (4326) column that contains surfaces (~20M). A second table B has ~ 500K points (4326). What I want to do is "flagging" (adding a boolean column) every single surface in A that contains at least one of the points in B.

I did this:

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;

But I got all "flagged" values as true (which is impossible).

With joins/lateral I didn't go far too.

Any idea?


texts: Any potential issues/ DoS risks with frequent connections/ queries to a SQL Server db?

texts:
A database team suspects there may be an issue with a third-party ETL application opening, and closing, connections to a SQL server database repeatedly.

In terms of exact numbers --- it opens about 30 connections, runs 30 queries in parallel, each query lasts 10 seconds, therefore 10 seconds later, the queries end, and each connection is closed by the application. There is about 10 seconds of other application logic, then the process starts again. So we're talking 30 connections opening and closing every 20 seconds.

This is also over a remote connection, and it would be the same IP address each time making the query.

Would this cause a crash, memory problem, DoS defense?

I for one thought that databases were set up to handle frequent connections and queries.

Anyway this 'loop' would happen about 1000-2000 times (so connections would be opening and closing for about 8 hours or so).

Is there anything in particular I should be monitoring at the database level or wherever else? Would this number of open/ close connections (even with a lightweight query) impact performance on a sufficiently robust database? What exactly should I be looking for?

A shared database on the same server started becoming slow/ laggy/ - though this began two weeks after this ETL process had completed running. I'm not sure there was direct evidence that this number of connections was the issue, but the DBA thinks it's the most probable suspect. I had mentioned it to him before because I was getting a SQL error message:

  The semaphore timeout period has expired

I personally thought maybe this cryptic error meant there was rate-limiting going on. On second thought it may have been network connectivity, but it provided something to point at conveniently.

My question is what to look at, or monitor, or what evidence or data should be retrieved, to ascertain exactly if this process is causing performance issues? What questions should I ask in the ETL tool forum? I'm trying to figure out if this is a design problem in some regard and less connections should be opened at once, or if this database performance issue is actually completely unrelated.


texts: How do I select data with a case statement and group by?

texts:
I'm trying to manipulate data a little by using a case statement but can't seem to figure it out.

Stops
--------------------
1Stop
1-Stop
1 stop
1 Stop
1stop
1 Stop
2-Stop

Im trying to come up with:

1-Stop.... 6
2-Stop.... 1

What I've tried is:

select CASE when
Stops = '1Stop' OR
Stops = '1 Stop' OR
Stops = '1 stop' then '1-Stop'
ELSE Stops END, count(*)
from table group by Stops


texts: Recommendations for more than one tempdb file

texts:
I would like to split tempdb into two files with same size, to improve performance of the database.

I wonder if these two data files can be on the same disk, separately from the logs and users data files?


texts: User Postgres execute unknown command which take high resource and cpu

texts:
I just install postgres in my host but some time I get CPU and memory very high usage
I checked with following topcommand result
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 5029 postgres  20   0 2438036   2.0g      4 S 185.0 55.0   6224:15 rzQBQuPW
   35 root      20   0       0      0      0 S   4.3  0.0   9:15.50 kswapd0
 2414 baovech+  20   0  579344  70044   2496 R   4.3  1.8   7:27.91 php-fpm
20998 baovech+  20   0  579596  77564   2644 R   3.0  2.0   3:55.35 php-fpm
15749 root      20   0       0      0      0 S   0.7  0.0   0:00.22 kworker/0:2

Is this a virus or something like that?


texts: SQL Server: test # of partitions == partition_functions.fanout

texts:
I am attempting to write a test for already partitioned schemas.

The test should confirm that each schema was partitioned # number of times where  # = sys.partition_functions.fanout.

I have found the following code: (according to my understanding gives the number of partitions per schema)

select distinct t.name
from sys.partitions p
inner join sys.tables t on p.object_id = t.object_id
where p.partition_number <> 1

I am currently attempting to understand how to connect each table to the partition function 'fanout' value.

Ideally I would like to be able to create a table with the following columns:
table name, number of partitions, sys.partition_functions.fanout


texts: Why does Postgres generate an already used PK value?

texts:
I'm using Django, and every once in a while I get this error:

  IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
  DETAIL:  Key (id)=(1) already exists.

My Postgres database does in fact have a myapp_mymodel object with the primary key of 1.

Why would Postgres attempt to use that primary key again? Or, is this most likely my application (or Django's ORM) causing this?

This issue occurred 3 more times in a row just now. What I've found is that when it does occur it happens one or more times in a row for a given table, then not again. It seems to happen for every table before it completely stops for days, happening for at least a minute or so per table when it does occur, and only happening intermittently (not all tables right away).

The fact that this error is so intermittent (happened only 3 or so times in 2 weeks - no other load on the DB, just me testing out my application) is what makes me so wary of a low-level problem.


texts: SQL Query return a column per hour / day / week / month betwen 2 dates

texts:
What I am trying to achieve (Table View)

 Part No   01/02/15  02/02/15  03/02/15  04/02/15  05/02/15  06/02/15
 PU1234        24       25        15         22       27        21
 PU5678        25       22        19         21       24        26

Table of Data

 A   B  C   D           E         Part Name     Date & Time
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:55.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:56.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:57.000
FX01 FN50   X82 OP.70   Packing   PU4238TZA     2015-02-27 03:43:58.000

I want the code to be generic so I can manipulate it to achieve all of the below

Quantity per part number per hour (24 hour period) stored procedure 1
Quantity per part number per day (between two dates) stored procedure 2
Quantity per part number per week (between two dates) stored procedure 3
Quantity per part number per Month (between two dates) stored procedure 4

The code below works (to a fashion) but populates the table with all zero's

`;with cte as(
select
    [CAB Part No.], cast([Date & Time] as date) UtcDay, DATEPART(hour, [Date & Time]) UtcHour, count(*) as Counts
from  [dbo].[SFDLOG]
where [Date & Time] between '2015-02-01' and '2015-02-02'
group by
    cast([Date & Time] as date), DATEPART(hour,[Date & Time]),[CAB Part No.]
)
select dateadd(hour, utchour, cast(utcday as datetime)) as [SFD Hour],
Counts
into #TempDates
from cte

select *
from
(
select [CAB Part No.],[SFD Hour],
    convert(CHAR(10),[SFD Hour], 120) PivotDate
from #tempDates
left join [SFDLOG]
    on [SFD Hour] between '2015-02-01' and '2015-02-02'
 ) x
 pivot
 (
count([SFD Hour])
for PivotDate in ([2015-02-01 00:00:00.000], [[2015-02-01 01:00:00.000], 2015-02-01 02:00:00.000],
 ) p;
 drop table #tempdates`


texts:
I'm trying to create some jobs for our Azure servers (for sql databases).

I'm reading about it on the internet and tried to find some questiosn here with no success.

Theres a link saying I need to download something in the market place. I can't even find such app by looking for it.

Is there a way to create a job as we create on a SQL Server?


texts: How to run Trigger only on certain updates

texts:
I have the following two tables:

CREATE TABLE IF NOT EXISTS `Product` (
    `ProductID` VARCHAR(16),
    `ProductName` TINYTEXT NOT NULL,
    `CategoryID` VARCHAR(3) REFERENCES Category (CategoryID)
    ON DELETE CASCADE ON UPDATE CASCADE,
    `ProductDescription` TEXT,
    `MRP` DOUBLE NOT NULL,
    `SellingPrice` DOUBLE NOT NULL,
    `ProductBrand` VARCHAR(30) NOT NULL,
    `Stock` INT(11) NOT NULL,
    PRIMARY KEY (`ProductID`)
);

CREATE TABLE IF NOT EXISTS `PriceDetails` (
    `ProductID` VARCHAR(16) REFERENCES Product (ProductID),
    `Time_Stamp` DATETIME NOT NULL,
    `Price` DOUBLE NOT NULL,
    PRIMARY KEY (`ProductID` , `Time_Stamp`)
);

And a trigger:

DELIMITER $$

    CREATE TRIGGER price_update
        AFTER UPDATE ON Product
        FOR EACH ROW
    BEGIN
    INSERT INTO PriceDetails
    values(
        NEW.ProductID,
        now(),
        NEW.SellingPrice);

    END$$
DELIMITER ;

This trigger runs every time the Product table is updated.

Now, I want to run this trigger only when the SellingPrice is updated in the Product table.