texts: Permission errors on insert after schema drop/create (Pg 9.0.23)

texts:
We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from the 'live' database.

This works by dropping/creating a 'daily' schema then copying over a subset of tables from the 'live' schema. This is applied by running a simple SQL script via crontab every day (see below).

Issue:

when the web application is started after the daily refresh, clients can insert data into the 'daily' schema as expected
when the web application is started before the daily refresh, we encounter permission errors when trying to insert data, eg

permission denied for relation annotation

Note: restarting the web application allows everything to work fine, but I would prefer to avoid the downtime.

Question:

is this behaviour expected (Pg 9.0.23)?
is there anything I can do to avoid this problem?

The script we are using to provide the daily refresh:

BEGIN;

drop schema if exists genome3d_data_daily CASCADE;

create schema genome3d_data_daily AUTHORIZATION genome3ddaily;

-- recreate sequence
create sequence genome3d_data_daily.annotation_id_seq;
select setval('genome3d_data_daily.annotation_id_seq', orig.last_value) from genome3d_data.annotation_id_seq orig;

-- create new tables (including constraints, indexes, etc)
create table genome3d_data_daily.annotation_type    (like genome3d_data.annotation_type including all);
create table genome3d_data_daily.annotation_segment (like genome3d_data.annotation_segment including all);
create table genome3d_data_daily.annotation         (like genome3d_data.annotation including all);

grant select on all tables    in schema genome3d_data_daily to PUBLIC;
grant select on all sequences in schema genome3d_data_daily to PUBLIC;

grant all privileges on all tables    in schema genome3d_data_daily to genome3ddaily;
grant all privileges on all sequences in schema genome3d_data_daily to genome3ddaily;

-- by default this will point to the original sequence
alter table genome3d_data_daily.annotation
alter column id set default nextval('genome3d_data_daily.annotation_id_seq');

-- insert data
insert into genome3d_data_daily.annotation_type     select * from genome3d_data.annotation_type;
insert into genome3d_data_daily.annotation_segment  select * from genome3d_data.annotation_segment;
insert into genome3d_data_daily.annotation          select * from genome3d_data.annotation;

END;


texts: Composite key as foreign key of multiple tables

texts: On Entity1 table, could I merge the two "a" field in a single field ?


texts: PostgreSQL - Create view with autoincremental column

texts:
I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.

Is this possible to do without effecting the original schema of the legacy data structure?


texts: Is this table in my ERD redundant? (Image)

texts:
I am developing a research article database for a project. My professor approved this design but I feel like the Author's_University table is redundant and that University_ID could just be added to the Articles_Authored table. Can anyone think of a reason to NOT change it?

Thanks for the input.


texts: Is there a way to generate table create script in TSQL?

texts:
Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?

Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?

Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees and I wrote the following script to execute a select statement on all of them:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) =
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

However the script above fails with the following message:

  An explicit value for the identity column in table '#tmp' can only be
  specified when a column list is used and IDENTITY_INSERT is ON.

Adding this:

SET IDENTITY_INSERT #tmp ON

does not help, since, I can't specify the column list and keep it generic.

In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.

So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.

Can anyone think of a relatively easy way to achieve what I want?


texts: How to shrink transaction log for a database that was previously in AlwaysOn Availability Group?

texts:
I need to move databases having TDE enabled to another instance and re-enable the TDE with a different certificate. Generally, this would not be a problem but I seem to have problems if the databases were in AlwaysOn Availability Group environment.

Steps I would normally follow would be:

Remove database from Availability Group.
Set encryption off for the database.
Drop private key from the database.
Take full and transaction log backup.
Set the database to simple recovery mode.
Shrink the transaction log file.

Here is the problem I faced with.

After enabling TDE for the database in the new SQL instance with a different certificate the backup fails with the error

  Cannot find server certificate with thumbprint '0x***********************'.

I believe the reason is that there is a VLF (Virtual Log File) that is marked as active (status 2) and needs to be backed up. Step 4-5 are not resolving this. This VLF seems to be related to the Availability Group and is kept there for the secondary replica is my guess.

Reference:

Removing TDE
Lazy Log Truncation – Clearing of SQL transaction log VLF status deferred

How can I clear the VLF status and shrink the log file?


texts: MSSQL: How to get the data from 1st - 15th and 16th - Last day of the PREVIOUS MONTH

texts:
I have a table that have these values:

From data   | Todate     | is_Complete

2018-11-01 |  2018-11-15   |    True
2018-11-16 | 2018-11-30   |    True

We are executing data every 1st day of the month. So assuming that It's already 1st day of december. I need to check if is_Complete column is True from first day of the previous month and 16th up to last day of the previous month then process my queries but if one of those is False I will not process it.

I am not sure if CAST will work here.

IF both is_Complete == True, then

process queries ELSE 'N/A'


texts: Get every column in database that contains only Null records

texts: I am responsible for a poorly designed legacy application that has 700+ MSSQL tables and plenty of columns that are completely unused. I would like to run a script to return the column and table name of every column that has only null records. Does anyone have any suggestions on I could accomplish this?


texts:
I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there.

# at 1050548
#130617 18:40:03 server id 1  end_log_pos 1050619   Query   thread_id=844988    exec_time=0 error_code=0
SET TIMESTAMP=1371519603/*!*/;
BEGIN
/*!*/;
# at 1050619
# at 1050782
#130617 18:40:03 server id 1  end_log_pos 1050809   Xid = 33121028
COMMIT/*!*/;

It's puzzling because I get expected SQL in the mysqlbinlog output for statements executed in phpmyadmin but those coming from other PHP-based remote web servers appear to not be recorded.

My settings bin logging are:

bin_log = /path_to_sql_incrementals
binlog_ignore_db = mysql

Am I missing a logging option?

Mysql 5.0.95 / CentOS 5.9


texts: Is it possible to select distinct values in a json document using PostgreSQL?

texts:
I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents

{
  id: 1,
  s: "foo"
},
{
  id:2,
  s: "bar"
},
{
  id:3,
  s: "foo"
},

the query must check the "s" key for distinct values and return the documents with id 1 and 2.


texts: How to append some letters or numbers to already existing data

texts:
I would like to append some data to already existing data, something like
720967537 to 255720967537. All the data in my column is in that format. Is there any way I can do that to all of the records that are in my MySQL database?


texts: Which of these two MYSQL queries is better (and what is the default join type)?

texts:
My SQL:

select count('1')
 from plays,
      promotion,
      partner
where
  partner.id = 1
  and promotion.partner_id = partner.id
  and plays.promotion_id = promotion.id

SQL generated by Eloquent (a laravel PHP ORM)

select count(*) as aggregate
from `plays`
inner join `promotion` on `promotion`.`id` = `plays`.`promotion_id`
where `promotion`.`partner_id` = 1

Is my version using inner out outer join?
is the inner join of the generated example better, or is it the same as my version?

There are 3 tables in this example:

plays
  int id
  int promotion_id

promotion
  int id
  int partner_id

partner
  int id

The sql is just to count all the plays which belong to a partner (through the promotion table)


texts:
Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)?

I want to check if certain table is used in any package as I am going to drop it - if the table is used I need to edit the package.

Could you advice?


texts: Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?

texts:
I'm not getting this.
I've got a table with these indexes
PRIMARY     post_id
INDEX       topic_id
FULLTEXT    post_text

Table has (only) 346 000 rows. I am trying to perform 2 queries.
SELECT post_id
FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')

takes 4.05 seconds while
SELECT post_id
FROM phpbb_posts
WHERE topic_id=144017
AND post_id != 155352
AND post_text LIKE ('%http://rapidshare.com/files/5494794/photo.rar%')

takes 0.027 seconds.
EXPLAIN shows that the only difference is in possible_keys (fulltext has post_text included, LIKE does not)
That's really strange.
What's behind this? What's happening in the background? How can LIKE be so fast when not using index and FULLTEXT so slow when using its index?
UPDATE1:
Actually it now takes about 0.5 seconds, maybe table was locked, but still, when I turn on profiling it's show that FULLTEXT INITIALIZATION took 0.2seconds. What's up?
I can query my table with LIKE 10x a second, with fulltext only 2x
UPDATE2:
Surprise!
mysql> SELECT post_id FROM phpbb_posts WHERE post_id != 2 AND topic_id = 6 AND MATCH(post_text) AGAINST ('rapidshare.com');
Empty set (0.04 sec)

so I'm asking, how is this possible?
Additionally,
SELECT count(*) FROM phpbb_posts WHERE MATCH(post_text) AGAINST ('rapidshare.com')

is really slow. Can be fulltext any broken?
UPDATE3:
What the hell?
SELECT forum_id, post_id, topic_id, post_text  FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

takes 0.27s while
SELECT count(*) FROM phpbb_posts  WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;

takes more than 30 seconds! What is going wrong here?


texts: Why is my pg_restore command not working?

texts:
Yesterday, I created backups of my various PostgreSQL databases using the following command:

pg_dump -Fc -d junk -h localhost -p 5434 -U postgres -W > z:\pg_dump\96_junk.dump

I created an individual backup for each database.

Today, I'm trying to restore them to an upgraded database server using the following command:

pg_restore -h localhost -p 5434 -U postgres -W -C -v z:\pg_dump\96_junk.dump

When I run pg_restore using the above it seems that the database was restored. The console screen displays a series of SQL statements and other messages I would expect to see for this database and, at the end, "PostreSQL database dump complete" is the output shown.

When I check the database, the database is not present. What am I doing wrong?

Employed versions info

Backups created with version 9.6.0
Attempting to restore using version 9.6.1


texts: How to recover deleted records by mistake in Mysql

texts: I am using Mysql.And by mistake I deleted very important info.Is there a way to recover or even to see the deleted info over a month ago?


texts: I have a pretty large Mysql table, over 25GB. When extracting an overview with Table Inspector in MySQL Workbench, I noticed the field Data Free Because the textfields are not selectable, I'm posting a picture of it. Does it mean some sort of reserved space? Or is it actually free space due to a size limit? Or something else?


texts: How to instruct Oracle 11g Standby database MRP to bring down as soon as applying the given archive log file?

texts:
Scenario:
Let us consider the following:
Oracle 3 node RAC.
RMAN Managed Recovery and Dataguard are configured between production and standby DBs.
The SCNs of 3 nodes are S1, S2, S3 at some point of time respectively.
Smax=max(S1,S2,S3)
Update1(with reference to Balazs Papp Advice):
log_1_x.arc, log_2_y.arc, log_3_z.arc where log file sequence number x<y<z and they contain the sequence number Smax

Please advise on bringing down Orace 11g Standby database MRP as soon as applying the given archive log file with name, log_3_z.arc!


texts: Postgres with no clients still chatty on network

texts:
This may be a non-issue but I was doing some network monitoring on my Mac and noticed that PostgreSQL (9.4.4) was still sending and receiving packets even when I'm not aware of any clients (localhost or remote) being connected to it.

Is there a way to see what's connected or is Postgres always chatty while it sits idle looking for a friend to talk to?


texts:
I have one master and 2 slaves,one is replicating but another slave which is read-only not replicating.

From Master :

show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |                                                                                |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------------------+
| mysql-bin.002490 | 13000479 |              |                  |
+------------------------------------------------------------------------------

Slave : Master has sent all binlog to slave; waiting for more updates

Slave status:

Slave_IO_State: Waiting for master to send event
     Master_Host: ....
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.002490
      Read_Master_Log_Pos: 13106512
           Relay_Log_File: mysql-relay-bin.000018
            Relay_Log_Pos: 13106725
    Relay_Master_Log_File: mysql-bin.002490
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 13106512
          Relay_Log_Space: 13107019
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
    Seconds_Behind_Master: 0
  Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

slave processlist show below for system user

i. Waiting for master to send event
ii.Slave has read all relay log; waiting for more updates

Still not replicating.what i have to check ?

Any suggestions.

Thanks in advance.


texts: Master - Master replication Duplicate entry error

texts:
I've got a Master - Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days.

My setup is as follows: One server in The Netherlands and one server in Washington D.C. both servers are running Windows Server 2012 R2 and MySQL 5.6.15 X64.

I've followed this tutorial.

My my.ini file contains the following:

Netherlands server

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Washington D.C. server

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Both servers keep stopping the slave and both report the same: Duplicate entry error. For example:

  Error 'Duplicate entry '4353' for key 'PRIMARY''
  on query.

How can I prevent this from happening?


texts: Liquibase on Oracle SQLcl - table and index drops not being propagated

texts:
I've been using Oracle's SQLcl, specifically the lb command to use Liquibase, for the last day or so to use for versioning my development, staging and production databases, which are using Oracle's Autonomous Data Warehouse (version 19).
It works fine when doing the tutorial changes, like the ones listed by Jeff Smith in his video on it here, although the syntax of the commands has changed a bit.
I'm reaching a problem when I want to drop an index, if I just execute the SQL statement DROP INDEX EXAMPLE_IDX in my development environment and then run the lb genschema statement, it doesn't drop the index on my staging environment when I run lb update -changelog controller.xml.
I think this is because it's generating a fresh Liquibase changelog every time I run lb genschema which seems to overwrite the previous changelog.
I'm also having the same behaviour when trying to drop tables etc, and I'm not sure how I am supposed to propogate DROP statements on tables and indexes with the lb tool.
Here's what I'm doing (users/passwords etc all changed).
Open SQLcl to create the staging database's structure (v1)
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

Open SQL Developer on the dev database and delete the index.
DROP INDEX INDEXNAME;

Open SQLcl to update the changelog and the staging database.
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml

After doing these steps, I'm still noticing that the tables and indexes are still there, which means my database behaves differently to what it should be doing!
If anyone has knowledge or experience of using the lb tool, SQLcl, or can let me know some best practices that would be amazing.


texts:
I am having issues with MySQL 5.7 using a lot of memory and a lot of threads on my prod server, and having some issues getting much results out of any changes I have made.

Is there a reason so many threads are in use?
How can I reduce the memory consumption down as its slowing the server a lot.

This is MySQL install on a fresh copy of ubuntu20. Nothing else was installed except mysql.

After adding performance_schema = 0 to /etc/nginx/my.cnf


texts: Why doesn't a delete increase unused space in a table?

texts:
If I populate the following table:

IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL
    DROP TABLE dbo.CleanTableTest;
GO
CREATE TABLE dbo.CleanTableTest
    (FileName nvarchar(4000),
    DocumentSummary nvarchar(max),
    Document varbinary(max)
    );
GO
-- Populate the table with data from the Production.Document table.
INSERT INTO dbo.CleanTableTest
    SELECT REPLICATE(FileName, 1000),
           DocumentSummary,
           Document
    FROM Production.Document;
GO

exec sp_spaceused CleanTableTest
go

Results:

name           rows  reserved  data    index_size  unused
-------------- ----  --------- ------- ----------- ------
CleanTableTest 13    456 KB    440 KB  8 KB        8 KB

But then if I delete all the rows:

delete dbo.CleanTableTest
go

exec sp_spaceused CleanTableTest
go

Results:

name           rows reserved data   index_size unused
-------------- ---- -------- ------ ---------- -------
CleanTableTest 0    88 KB    80 KB  8 KB       0 KB

Why after the delete process of all rows in table the unused space have not changed? It stays at 0 KB.


texts:
I am writing a query for getting average value of event duration for given period of time.
We might consider that we do have table with 2 columns:

start               | end
-----------------------------------------
2019-10-14 18:16:08 | null
2019-10-14 19:39:47 | 2019-10-14 23:28:17
2019-10-16 03:29:33 | 2019-10-16 03:31:08
2019-10-16 21:43:34 | 2019-10-17 02:07:31

The wanted behaviour would be to take avg. time for each day. The first row should be ignored, cuz the task is not finished - it might be still going / infinite. For second row we would get avg. time of

(2019-10-16 03:31:0 - 2019-10-16 03:29:33) + (2019-10-16 21:43:34 - 2019-10-16 23:59:59) / 2 -- 2 events only

And the for 17th day we would get 2:07:31.
So far I've got this query:

SELECT
    DATE_FORMAT(start, '%Y-%m-%d') AS time_label,
    AVG(TIME_TO_SEC(TIMEDIFF(end, start))) AS value
FROM
    time
GROUP BY start

which is not working as expected. I might add WHERE clause but it won't work. I've tested it on much bigger table.


texts: Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?

texts:
I am using:

SQLite version 3.21.0

I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot.

Though, now I am wondering if there are ways to:

Set a column width to be equal exactly to the width of its header? I went through the corresponding section of the documentation — the closest thing it mentions is .width 0, which effect is explained as follows:

  If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data.

if it was only 'the maximum of two numbers: 10 and the width of the header' — it would suffice my needs, though '...and the width of the first row of data.' spoils it for me.
Is there a way to  set width of all columns to be the same, without knowing in advance how many columns are there? E.g. instead of doing .width 5 5 5 for 3-column SELECT, I would like to do something like .width all 5.


texts: Best approach on storing/displaying sanitized data

texts:
Surfing the web I find two approaches on storing data in databases.

Approach 1:
Store data in the database with the html-specialcharacters sanitized

$userdata = $_POST['field'];
store_in_db_via_PDO(htmlspecialchars($userdata), 'field');
display_on_page(get_from_db('field'));

Approach 2:
Store data as raw as possible in database and sanitize on display:

$userdata = $_POST['field'];
$store_in_db_via_PDO($userdata, 'field');
display_on_page(htmlspecialchars('field'));

BTW I'm not speaking about sqlinjection atacks, these are 'caught` by the PDO-driver

Is there an industry standard on storing data, which shouldn't include html-code, in a database?


texts: FILE privilege still allows for file writing on server (localhost)

texts:
I created a user without the FILE privilege, and a web app connected to the DB with this user is still able to write text files on my localhost. To write these files, the program just uses fopen and fwrite.

I'm reading the document here, and it seems to me that FILE allows query-level file-writing/reading LOAD DATA INFILE. Am I interpreting this right?


texts:
I have an expanding MariaDB 3 node cluster and need to expand the available storage. Each node has (to make the maths slightly easier) 120Gb of database storage. As this is replicated, the total DB storage is also therefore 120Gb. If I add a 4th node, I will gain extra resilience and maybe better read speed, but still only have 120Gb storage.

               Server A           Server B          Server C
               +-------+          +-------+         +-------+
               | 120Gb |          | 120Gb |         | 120Gb |
               +-------+          +-------+         +-------+

Cluster1 -->   +-------+          +-------+         +-------+
 (120Gb)       | 120Gb |          | 120Gb |         | 120Gb |
               +-------+          +-------+         +-------+
Total storage 120Gb

I am looking at Sharding, using SPIDER tables within MariaDB, to increase the available space but without losing my current x3 replication. With SPIDER you can storage a table across multiple databases, so I am thinking I need to move to a model like this, adding a 4th server D also with 120Gb, but effectively running 4 database clusters across these boxes...

                Server A           Server B          Server C          Server D
                +-------+          +-------+         +-------+         +-------+
                | 120Gb |          | 120Gb |         | 120Gb |         | 120Gb |
                +-------+          +-------+         +-------+         +-------+

 Spider -->     +-------+          +-------+         +-------+         +-------+
  ( ~0Gb)       +-------+          +-------+         +-------+         +-------+

 Cluster1 -->   +-------+          +-------+         +-------+
  ( 40Gb)       |  40Gb |          |  40Gb |         |  40Gb |
                +-------+          +-------+         +-------+

 Cluster2 -->                      +-------+         +-------+         +-------+
  ( 40Gb)                          |  40Gb |         |  40Gb |         |  40Gb |
                                   +-------+         +-------+         +-------+

 Cluster3 -->   +-------+                            +-------+         +-------+
  ( 40Gb)       |  40Gb |                            |  40Gb |         |  40Gb |
                +-------+                            +-------+         +-------+

 Cluster4 -->   +-------+          +-------+                           +-------+
  ( 40Gb)       |  40Gb |          |  40Gb |                           |  40Gb |
                +-------+          +-------+                           +-------+

 (total 160Gb)

Each cluster (horizonally) would have 3 box replication and storage 40Gb, while each server (vertically) would store up to 120Gb. Each additional box would therefore add one third of 120Gb...

This makes sense to me, I have been testing  and I can get multiple MariaDB instances running per box. It then just a matter of keeping all the plans in order, organising which slices will be on which boxes and keeping track of port numbers (each MariaDB instance has to have a different port number on). I can even run a thin cluster slice with just the logical Spider tables on the same box too.

Is there an easier way? I can find lots of guides for using Spider, but very few for using MaxScale?? Can anyone point me in the right direction please? (I feel like I am having to re-invent too much myself when I'm sure this is not an uncommon situation)


texts: Intentionally slow down importing of (Percona) MySQL 5.7 dump

texts:
I need to move a very large MySQL table (250M rows, 120 columns. 120GB of data, 310GB of index) from one database instance to another (on AWS). For reasons beyond the scope of this question, this is the only option we are considering.

Our set up uses cross-region replication on AWS with many slaves on different regions. Updates to a MySQL table are written to the binlog and replicated to these slaves immediately using a private tunnel.

The host database includes only this one table, and the table's contents are static. The target database includes hundreds of other tables with active transactions that must continue to be replicated to other regions with minimal replication lag. This is the root of the problem: if I simply import this massive dump (mysql -u username -p database_name < file.sql), the writes to this single table with overwhelm the binlog and cause replication lag for other tables on slaves, which is unacceptable.

What I'm looking for is this: Is there a way I can intentionally slow down this import to prevent it from causing a replication lag on transactions on other tables?

I'm looking for the most straightforward approach to accomplish this with minimal effort. You can, of course, write a custom script that batches the writes for you and sleeps for a period of time between each batch, but I'm trying to avoid that if possible.

This is the version of MySQL I'm running on both instances:

$ SHOW VARIABLES LIKE "%version%";

innodb_version  5.7.12-5
protocol_version    10
slave_type_conversions
tls_version TLSv1,TLSv1.1,TLSv1.2
version 5.7.12-5-log
version_comment Percona Server (GPL), Release '5', Revision 'a2f663a'
version_compile_machine x86_64
version_compile_os  debian-linux-gnu


texts: Are there limits to SSRS 2014 RDL files going over to SSRS 2008 (or 2012)?

texts: Is there any incompatibility with reports developed in SQL Server SSRS 2014, and then exported to .RDL files that will be imported into SQL Server 2008?


texts: Show all privileges given to a schema or user in postgres

texts:
I want to know what are the privileges a given schema or user has in Postgres. Like whether it can create a table, view, function, sequences or not.
I found one query but it showed privileges on table only. Please suggest any appropriate query.


texts: SSIS Adding column to excel sheet template destination when importing from SQL Table Source

texts: I have an excel sheet template that has 3 columns. I also have a SQL server table that has the same 3 columns plus an additional 4th column. I need to import all 4 columns from the SQL table into the excel spreed sheet template. My problem is that when I try to MAP the columns in SSIS I can only map the 3 columns that the Table and excel sheet have in common. How can I have SSIS import all 4 columns and not just the 3 columns that the source and destination have in common? Is there a way I can have SSIS add the additional field column to my excel spread sheet destination?


texts: Sql Server Full Text Search one table for text values in another table

texts:
Is it possible to run a full text search on an FTS-indexed column for all values in another table?

Conceptually it'd look like

select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue)

I'm rather new to MSSQL FTS, though I know that the equivalent would work in postgres

select d.* from docs d, tags t where to_tsquery(t.tagvalue) @@ to_tsvector(d.fulltext)

Thanks for the help!


texts: Default Schema not being applied even after setting default schema and default DB

texts:
Hello Stack Exchange!

I have an interesting problem! Let me set the stage for you:

1) Windows Authentication

2) Default Schema identityiq has been set for the user with login XCORP\USERA

3) Default Database identityiq has been set for the user with login XCORP\USERA

4) XCORP\USERA does NOT have sysadmin

5) XCORP\USERA is the owner of the DB

Ok so as I mentioned - it is interesting for the following reasons - I have a DB account (which we will call IdentityIQUSER) which uses SQL Login authentication with the exact same setup that works just fine! (Exact same setup meaning things that I can check and uncheck in nice little boxes) I can make calls with no issues with the SQL account. Only when I use the windows account am I finding problems. After doing some soul searching I ran the following command that led me to the path that something was amiss (possible with the login) Select SCHEMA_NAME(); and interestingly enough what is returned is not identityiq but dbo so I did some more digging and came across a mountain of effective access. I can provide a list if that would be helpful. So here is my question which is three-fold.

Question 1: Is there any effective access that can be granted that would cause DBO to be the default schema (similar to what happens with sysadmin)

Question 2: Can AD groups control what effective access you are getting on a server? If the answer here is yes, does anyone know how I can track down what group in AD is granting these effective access rights?

Question 3: I have never been this deep in the DB - so I may not be looking at the right thing, so if this isn't the right direction to be marching where else should I look?

Also as a side note, I was looking to see if I could override the effective access with explicit access (since I believe Deny takes precedence) and annoyingly when I logged back in all of my access had been restored...I am probably the first person ever to complain about too much access. lol Thank you for taking the time to look.


texts: We are using webjob in azure which imports data every 2 hours into the tables I've created in the cloud. As I read it from here, I can encrypt the db very easily using azure TDE feature. But I am assuming I have to enable data encryption everytime I get new imports from webjob? or am I wrong? Does Microsoft offer live data encryption in azure?


texts: Build a three table join with a recusive table in the middle?

texts:
I have three relevant tables: Parts, PartGroup, and MarkupGroup.

Parts is simple.

PartID          artificial primary key
Part            part number
PartGroupID     Foreign key

sample data:

1   T1000           5
2   wizbang gold    17
3   flux capacitor  2

PartGroup is an Acyclic Directed Graph(tree) modeled with a self-linked parent key

PartGroupID     artificial primary key
Description     name of group
ParentID        foreign key linked to PartGroupID
MarkupGroupID   foreign key linked to MarkupGroup

Sample data would look like this-

1   system      null    null
2   component   null    1
3   software    null    2
4   abc         1       3
5   xyz         1       4
6   123         4       null
7   456         4       null
8   789         5       null
9   a1          6       null
10  b2          6       null
11  c3          7       null
12  d4          7       null
13  e5          8       null
14  f6          8       null
15  alpha       3       null
16  beta        3       null
17  gamma       3       null

MarkupGroup is to apply a markup factor to several PartGroups as a set.

MarkupGroupID   primary key
MarkupFactor    numeric attribute field

sample data-

1   15
2   20
3   25
4   22

I need to write a query that returns the appropriate markup amount for each part in the parts table. There is never a case when more then one markup can apply to a part. I need to recurse up the graph to find the markup, and I won't know at query time how many levels I need to recurse to find a non-null markup.

There is never a case when a part will encounter more than one possible join to a margin on the way up the tree, so there's no accumulation that needs to be done.

The t1000 is of type xyz, which has a foreign key to a MarginGroup, so we can join and get a value. It also has a parent, but that's irrelevant since we have the value we need.

Wizbang Gold is gamma-group software, which has no foreign key, but the parent node 'software' does and we should return that.

A flux capacitor is a component, and it has a direct foreign key to a MarginGroup. Return that value.

So the results would be:

1   T1000           22
2   wizbang gold    20
3   flux capacitor  15

I'm pretty sure I'm going to need a recursive CTE and some flavor of APPLY to get this, but my brain is not working very well at the moment. If not for the recursive nature of the middle table this would be pretty straightforward. Platform is MS-SQL.


texts: generate new column from another table column, treating nulls are unique

texts:
I have a table that looks like the following:

    |device_id | login_id |
    -----------------------
    |    1     |    NULL  |
    |    2     |     A    |
    |    3     |     A    |
    |    4     |    NULL  |
    |    5     |     B    |
    |    5     |     C    |
    -----------------------

I want to generate a new column (person_id) like the following. The column essentially generates a (person) unique identifier. Every new NULL value gets its own unique_id and every distinct non-null login_id gets its own unique person_id.

    |device_id | login_id | person_id |
    -----------------------------------
    |    1     |    NULL  |     1     |
    |    2     |     A    |     2     |
    |    3     |     A    |     2     |
    |    4     |    NULL  |     3     |
    |    5     |     B    |     4     |
    |    5     |     C    |     5     |
    -----------------------------------

Is there a way to do this in pure PostgreSQL without going through a series of queries and temp tables? I'm using PostgreSQL 9.4.  Any help is much appreciated.


texts: How does recovery work when server is shutdown before a checkpoint?

texts:
So for any query that makes changes to data:

SQL Server pulls the page from disk into memory if it doesn't already exist in memory.
Lock the page/row and make the data changes.
Writes the changed to the log in memory.
Release the page/row lock.
Send acknowledgement to user

Then, whenever there is a checkpoint:

Write the log to disk
Write dirty page to disk

Suppose the server gets shutdown due to power failure just before a checkpoint. Then is the data from memory that wasn't burnt to disk lost? Or does SQL Server write the log to disk before sending acknowledgement to user?


texts:
I am using ola-hallengren solutions for database maintenance.
A question about the log, I see in the documentation website:

LogToTable: Log commands to the table dbo.CommandLog.

Value   Description
Y       Log commands to the table.
N       Do not log commands to the table. This is the default.

Does N which is default means to log to output file?
I also see the recommendation is to log to output file, then what is the CommandLog table for?

Which stored procedure has code to log into output file and can set location of output file?

Thanks


texts: Design problem with auto-increment fields

texts:
I've made this design similar to my problem, it's not exact but it close enough to understand my problem:

Some cellular client frequently send their GPS position and the WLAN name that they have in range, sometimes only their position.

I've auto-generated timestamp and auto-incremental idModelGPS from ModelGPS table and I'm using javaDB to write to the DB.

I want to link the ModelGPS to an WLAN name where it happens, but I can't do it because the idModelGPS is autogenerated.  I cannot access it to write in the ModelGPS_WLAN table.

I don't know how to handle this, maybe there is a design error or maybe there is a way to solve this problem that I'm unaware of. I'll be glad to hear your opinions.


texts: Possible to keep max number of records in postgresql?

texts: Basically part of our Postgresql table is used to keep server access logs, and as such sometimes during production this can get pretty large. is there any way of setting in postgresql to have a maximum number of records a table can have and to push off the oldest record?


texts: How to check wether a DB2 source table and their MySQL MyISAM duplicate are identical?

texts:
I want to compare two big tables if they are identical. If I update/insert one table into the other the process takes about 11 hours.

What I am looking for is like a sum function which sum ups a key for each row and then I would like to compare the two sums. I guess I would need to take every of the 40 columns into that key to be sure they are exactly the same.

Any idea how to do this quick? Ah I forgot one big thing: these tables are on different systems stored in different DMS. The source is DB2 and the copy is MySQL.

I read about the Checksum function in MySQL.
- I also read, that it somewhat restricted to MyISAM
- I reade that two tables with the same checksum could still have no identical content.
-> might not be what I am looking for. Other ideas are apprechiated.


texts:
Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (example).

Is there any truth to that?

Is there, for example, some known bug in SQL Server (which version?) that causes queries using NOT IN to have a worse execution plan than an equivalent query that uses

a LEFT JOIN combined with a NULL check or
(SELECT COUNT(*) ...) = 0 in the WHERE clause ?


texts: If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

texts: If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the cursor is finished?


texts: SQL Agent reporting failure when all steps suceed

texts: The SQL Agent is reporting failure when all the steps under it has succeeded. Anyone else seen this behavior ?


texts:
I have been reading the GoldenGate 11g R2 installation guide and it states:

Non-supported objects and operations in Oracle DML:
[...]
Synonyms
[...]

But I see in the next section that DDL operations are supported, so CREATE, ALTER, etc...
So, what is exactly a DML operation against a synonym and how is it not supported?


texts: Is it a bad backup strategy in Oracle to have only Level 0 backups and Archive Logs in between with no incremental?

texts:
If you backup the database with a Level 0 every six hours, then do the archive logs in between, every 15 minutes perhaps, what benefit would I have to incorporating a differential backup?  Essentially, full backup only with archive logs x minutes.  Particularly cumulative.  Is it only because of disk space?

I have read this,

Is it enough to have only full backup and transaction log backup only for a good backup plan?

But it did not help, as it includes a differential.

Edit:  I ask because I thought you had to have a full backup to use a differential in the first place.  So why not just have the full backup to begin with?


texts: Find other waits for the session with CXPACKET using Extended Events

texts:
I have following extended event in SQL Server 2012 to capture CXPACKET waits.

I need to find “other waits” also if the session has a CXPACKET, like the one achieved here using dynamic management view. What is the most light weight way to capture this information?

Also, how to display this information by querying this data aggregating on the thread level or execution_context_id (like total duration of wait for a thread)?

Extended Event

CREATE EVENT SESSION [MyEvent3] ON SERVER
ADD EVENT sqlos.wait_info
      (
      ACTION (sqlos.cpu_id,
              sqlserver.database_name,
              sqlserver.request_id,
              sqlserver.session_id,
              sqlserver.session_nt_username,
              sqlserver.sql_text,
              sqlserver.transaction_id,
              sqlserver.username,
              package0.collect_system_time,
              package0.collect_cpu_cycle_time,
              --package0.callstack,
              sqlos.task_address,
              sqlos.worker_address
              )
       WHERE ([wait_type] = 191) --CXPACKET
       --map_key value 187 in 2008 and 2008 R2 and 191 in 2012 and 2014 RTM.
       AND [database_name]  = N'TestDB'
       )
  ADD TARGET package0.event_file
    (
        SET
            filename           = N'G:\XE\event_session_test3_EF.xel',
            max_file_size      = (20),
            max_rollover_files = (2)
    )
WITH
(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
);


texts:
A client of ours has doubts that a user erased certain information from the database they are using. I know that the database can be audited by having SQL Server Profiler enabled, but in this case, there wasn't any tool enabled to do auditing.

My question is: is there a way to know if some user has committed a transaction on the database without having profiler enabled? Is there any forensic task that can be done to accomplish this?

Thanks.


texts: How do you find where a stored procedure is used (in other stored procedures)

texts:
I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm not breaking any other code when I refactor.

In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.

EDIT: The stored procedures I'm trying to locate are part of a package.

EDIT: I'm running on Oracle 11g


texts:
I am working on a search app that queries a large amount of info (100M+ rows) with multiple filters that can be used and I am having trouble getting it to perform when multiple filters are combined. When I have three or more joins the query takes hours to finish. Is there any way to make multiple joins a lot faster or am I doing something wrong? The following SQL fiddle explains the predicament:

http://sqlfiddle.com/#!9/db1eab8/1

For reference the table structure and slow query is also below:

-- ----------------------------
-- MariaDB 10.1 Table structures (simplified names and columns for this case)
-- ----------------------------
-- Contains the main information rows - this normally has 100M+ rows
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
-- Other columns would normally be here but were removed to simplify this
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains the text content for a - This has the same amount of rows as `a`
DROP TABLE IF EXISTS `a_content`;
CREATE TABLE `a_content` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(2000) NOT NULL,
  `description` varchar(2000) NOT NULL,
  PRIMARY KEY (`id_a`),
  FULLTEXT `f` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
DROP TABLE IF EXISTS `a_hidden`;
CREATE TABLE `a_hidden` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id_a`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains an `a`'s country ID's - This normally has 300M+ rows
DROP TABLE IF EXISTS `a_country`;
CREATE TABLE `a_country` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `country` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_a`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Sample Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1');
INSERT INTO `a` VALUES ('2');
INSERT INTO `a` VALUES ('3');
INSERT INTO `a` VALUES ('4');
INSERT INTO `a` VALUES ('5');
INSERT INTO `a` VALUES ('6');
INSERT INTO `a` VALUES ('7');
INSERT INTO `a` VALUES ('8');
INSERT INTO `a` VALUES ('9');
INSERT INTO `a` VALUES ('10');

INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
INSERT INTO `a_content` VALUES ('6','aa c','aavv');
INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
INSERT INTO `a_content` VALUES ('8','aa df','aa s');
INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
INSERT INTO `a_content` VALUES ('10','ss','aaaa');

INSERT INTO `a_hidden` VALUES ('1','1');
INSERT INTO `a_hidden` VALUES ('3','1');
INSERT INTO `a_hidden` VALUES ('5','1');
INSERT INTO `a_hidden` VALUES ('7','1');
INSERT INTO `a_hidden` VALUES ('9','1');

INSERT INTO `a_country` VALUES ('1',1);
INSERT INTO `a_country` VALUES ('1',2);
INSERT INTO `a_country` VALUES ('2',1);
INSERT INTO `a_country` VALUES ('3',2);
INSERT INTO `a_country` VALUES ('4',4);
INSERT INTO `a_country` VALUES ('5',112);
INSERT INTO `a_country` VALUES ('6',33);
INSERT INTO `a_country` VALUES ('7',44);
INSERT INTO `a_country` VALUES ('8',66);
INSERT INTO `a_country` VALUES ('9',88);
INSERT INTO `a_country` VALUES ('10',99);

Query:

# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
SELECT a.id
FROM  `a`
LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
LEFT JOIN a_content ON a_content.id_a = a.id
INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3)
WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 0 , 50

The Explain of the above query and table structure:


texts:
I need your help with a issue on Oracle Materialized View.

I created the Materialized View using the following command:

CREATE MATERIALIZED VIEW SNAP_TEST
TABLESPACE tbs
NOCACHE
LOGGING
NOPARALLEL
REFRESH COMPLETE
START WITH TO_DATE('10-nov-2011 07:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1
AS
SELECT item
from item@abc;

If I manually refresh it everything works properly. But it doesn't work automatically: I expected an auto-refresh everyday at 7:00, but it never happened.
Why? Do I have to activate something?


texts: I can't open my Pharmacy management software"based on sybase database"

texts:
I am stuck with my pharmacy management software running on Windows XP with a dongle as a security system. The database is Sybase.

I tried many backup copies but failed to open the program. Two windows appear:
I am trying to find an online opener for DB files or any program that can do the job.I downloaded RazorSQL but couldn't deal with it as I am not advanced user, just newbie.How can I fix this issue and open the program or at least retrieve or extract data from [the backup][3] into .txt or .csv format?

I am the pharmacy owner, the management software is licenced, and the database is mine. There is no violation of privacy or intellectual property rights of any person. Nothing confidential to be hidden. I am simply sad that the system crashed, and for some reason I have to wait 3 days until I get technical support to get this issue fixed.


texts:
I'm tuning Aerospike, after I use Recipe for an SSD Storage Engine with this config:

namespace test {
        replication-factor 2
        memory-size 4G
        default-ttl 30d # 30 days, use 0 to never expire/evict.

        #storage-engine memory
        storage-engine device {     # Configure the storage-engine to use persistence
        device /dev/vdb    # raw device. Maximum size is 2 TiB
        # device /dev/<device>  # (optional) another raw device.
        write-block-size 128K   # adjust block size to make it efficient for SSDs.
    }
}

After run the benchmarks first time,my SSD is completely full

quanlm@quanlm2:/mnt/device$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            2.0G     0  2.0G   0% /dev
tmpfs           395M  3.1M  392M   1% /run
/dev/vda1        60G  2.8G   54G   5% /
tmpfs           2.0G     0  2.0G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/vdb         64Z   64Z   59G 100% /mnt/device
tmpfs           395M     0  395M   0% /run/user/1000

So is there anyway to delete the data in my SSD (/dev/vdb) ?


texts: How to circumvent ORA-01775 looping chain of synonyms in Oracle Forms?

texts:
I think I have a database problem... Not necessarily with Oracle Forms (10g)...Let me explain ...

I'm trying to implement this Oracle Forms Menu Security Configuration When I do it, it says that I get ORA - 01775 "Looping Chain of Synonyms".... Since I have NO IDEA what synonym is creating the problem, I was wondering how you guyz can help me fix this issue.... Maybe there is a DBA script somewhere to solve this....

UPDATE

Oracle Database trace  log says the culprit SQL is :

select ROLE GRPNAME ,FLAG GRPFLG from FRM50_ENABLED_ROLES order by ROLE desc  and FRM50_ENABLED_ROLES is created from step 1 here

That statement executes properly where I execute it in SQL*PLUS


texts:
I have the following query:

DECLARE @p__linq__0 UNIQUEIDENTIFIER

SET @p__linq__0 = '... some guid ...'

SELECT TOP 1
    [EventId] AS [EventId],
    [DateCreated] AS [DateCreated],
    [LocationId] AS [LocationId],
    [SourceName] AS [SourceName],
    [SourceState] AS [SourceState],
    [Priority] AS [Priority],
    [EventDescription] AS [EventDescription],
    [FirstTrigger] AS [FirstTrigger]
FROM [dbo].[Watchdog]
WHERE
    [LocationId] = @p__linq__0
    AND
    [FirstTrigger] = 1
ORDER BY [DateCreated] DESC

Watchdog table defines 2 indecies:

Clustered index on EventId primary key column
Unclustered index on DateCreated column

This is actual execution plan for the query:

Reading other posts on how to eliminate key lookup I added another non-clustered index which includes all columns from SELECT

CREATE NONCLUSTERED INDEX [LocationId_FirstTrigger] ON [dbo].[Watchdog]
(
    [LocationId] ASC,
    [FirstTrigger] ASC
)
INCLUDE (   [EventId],
    [DateCreated],
    [SourceName],
    [SourceState],
    [Priority],
    [EventDescription]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

However, this didn't help and actual execution plan is the same. If I look at key lookup the output is actually included in newly added non clustered index.

My question is, why it's still doing key lookup instead of index scan/seek ?

UPDATE

Following some suggestions in the comments, I dropped newly created non clustered index & instead recreated non clustered index on DateCreated column including columns from SELECT.

Now execution plan is the following:

Also query execution time dropped from 1+ minute to few seconds (this table has 18+ million rows).

Does this mean key lookup was done due to ORDER BY on non-clustered index ?


texts: Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

texts:
I am trying to add new articles to an existing publication in SQL Server 2012.

I am adding the article to the publication with the syntax

--add the table into the correct publication
sp_addarticle @publication='<publicationname>', @article='CardBookingType_tbl', @source_object='CardBookingType_tbl'

Previously on SQL Server 2008 I used to use the following syntax to subscribe to 1 table only and then run the snapshot. This no longers work on SQL 2012 as you have to subscribe to all articles.

--add the table into the subscription
EXEC sp_addsubscription
        @publication = '<publicationname>',
        @subscriber = '<Servername>',
        @destination_db = '<dbname>'

GO

In SQL Server 2012, if I add the table via the GUI and then run the snapshot agent it picks up the new table fine. But if I add via the script it appears in the gui but the snapshot reports no new items were detected.

Seems like I am missing a piece of code to flag the subscribers as missing a article?


texts: How can I edit a replication subscription to include new articles?

texts:
I have some replication subscriptions and I need to add some tables to one of the subscriptions. Is this possible?

Publisher/Distributor: Sql 2000
Subscriber: Sql 2008

Thank you.

edit: I found this article. http://blogs.msdn.com/b/repltalk/archive/2011/03/15/how-to-add-an-article-to-an-existing-transactional-subscription-initialized-through-backup.aspx

I'm assuming that this is the only way.


texts:
I am an administrator in SSAS.

I have many data warehouse servers.

In some servers I have SQL Server and SSAS on the same machine.

I successfully backup the SSAS databases and even check if the backups are healthy

Recently, however, one of our servers which is managed by a third party company based in Canada, needed to be replaced, and while migrating all the ssas databases from the old server to the new one

this is the old server:

this is the new server:

I am getting this error message while processing the database in the new server:

  This command cannot be executed on database 'DWCA' because it has been
  defined with StorageEngineUsed set to TabularMetadata. For databases
  in this mode, you must use Tabular APIs to administer the database.


texts: Meaning of "update conflicts" in CollStats result

texts:
db.collection.stats() returns
wiredTiger: {
    .....
    "transaction" : {
        "update conflicts" : NumberInt(1410)
    },
    ....
}

It's  MongoDB 3.6, but I don't use its new features related to client sessions and retryable writes.
Couldn't find much of documentation about meaning of the number apart from

Conflict between concurrent operations.
This error is generated when an operation cannot be completed due to a conflict with concurrent operations. The operation may be retried; if a transaction is in progress, it should be rolled back and the operation retried in a new transaction.

at http://source.wiredtiger.com/3.0.0/group__wt.html#ga5ee3c6fcd074e11efd118f3e68e91db8
Does it mean there were 1410 failed updates since the collection was created, or some of them were successfully "retried" implicitly?


texts: How to get connection details for another connection open to database

texts:
How to get connection details as e.g. collation_connection used for another connection open to database?

To give an example. Let's say I have this activity on my Database

mysql> show processlist;
+------+------+--------------------------------+------+---------+------+-------+------------------+
| Id   | User | Host                           | db   | Command | Time | State | Info             |
+------+------+--------------------------------+------+---------+------+-------+------------------+
| 6649 | xxxx | server1.example.com:56300      | mydb | Sleep   | 4123 |       | NULL             |
| 6651 | xxxx | server1.example.com:56301      | mydb | Sleep   |   28 |       | NULL

Now I need to know the collation of connection with ID 6649 from processlist.


texts: In SQL Server 2008 - is there any script available which will list all tables (incl column names, data type, length) for the entire database - with one script.


texts:
Restore failed for Server 'servername'.  (Microsoft.SqlServer.SmoExtended)
  System.Data.SqlClient.SqlError: RESTORE could not start database 'ECP'. (Microsoft.SqlServer.Smo)

The database is created, but is not accessible using Object Explorer:

  The database ECP is not accessible. (ObjectExplorer)

Thanks for any advice on how I can make the database work.

EDIT: This query

select state_desc from sys.databases where name = 'ECP';

returns RECOVERY_PENDING.

I deleted the database again and had a new try with

restore verifyonly from disk = 'path\file';

This returns

  Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
  The path specified by "F:\ECPDATA1\ECPDATA1.mdf" is not in a valid directory.
  Directory lookup for the file "F:\ECPDATA2\ECPDATA2.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15100).
  Directory lookup for the file "F:\ECPDATA3\ECPDATA3.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "F:\ECPDATA4\ECPDATA4.ndf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Directory lookup for the file "E:\ECPLOG1\ECPLOG1.ldf" failed with the operating system error 21(failed to retrieve text for this error. Reason: 15105).
  The backup set on file 1 is valid.

Since it says, that the backup set is valid, I just have to get the files specified, to make it work, right? But on this server I don't have a drive E: or F:. How can I adjust this in the backup file? Or is it possible (I'm not that familiar with windows-server-2008-r2) to have some kind of symlinks like on linux?


texts: PostgreSQL service stops immediately after installing Windows Fall Creators Update 1709

texts:
The server itself runs fine with

pg_ctl -D <datadir> start

But the service stops immediately ("...Some services stop automatically if they are not in use by other services or programs")

Access rights look OK, local system has full access to <datadir> and its parent directories.

Workaround is easy (manual start), but a service would be nice.

Tests with manual service starts (pg_ctl register) did not help.

I'm using PostgreSQL 9.4 and don't want some fresh or newer installaton since "it's just the windows service" and my version corresponds to installations on production servers.

No other changes.

functioning postgresql service
windows update 1709
service always stopping immediately

Nothing in logs, and pgevent.dll cannot be registered, neither with system32/regsvr32 nor with syswow64/regsvr32 (dllregisterserver entry point not found)

Any hints where I can continue searching for the underlying problem?

Note: I'm a domain user with admin privileges.


texts: Is SELECT * ok in a Trigger. Or am I asking for trouble?

texts:
I'm caught in a debate at work, and I need some advice on possible Pitfalls I could be overlooking.

Imagine a scenario where a Trigger is used to copy Deleted Records to an Audit Table.
The Trigger uses SELECT *. Everyone points and shouts and tells us how bad this is.

However, if a modification is made to the Structure of the Main Table, and the Audit Table is overlooked then the Trigger will generate an error letting people know the Audit table also needs modification.

The error will be caught during testing on our DEV servers. But we need to ensure Production Matches DEV, so we allow SELECT * in Production Systems (Triggers only).

So my Question is: I'm being pushed to remove the SELECT *, but I'm not sure how else to ensure we are automatically capturing Development Errors of this nature, any ideas or is this best practice?

I've put together an example below:

--Create Test Table
CREATE TABLE dbo.Test(ID INT IDENTITY(1,1), Person VARCHAR(255))
--Create Test Audit Table
CREATE TABLE dbo.TestAudit(AuditID INT IDENTITY(1,1),ID INT, Person VARCHAR(255))

--Create Trigger on Test
CREATE TRIGGER [dbo].[trTestDelete] ON [dbo].[Test] AFTER DELETE
NOT FOR REPLICATION
AS
BEGIN
    SET NOCOUNT ON;
    INSERT  dbo.TestAudit([ID], [Person])
    SELECT  *
    FROM    deleted
END

--Insert Test Data into Test
INSERT INTO dbo.Test VALUES
('Scooby')
,('Fred')
,('Shaggy')

--Perform a delete
DELETE dbo.Test WHERE Person = 'Scooby'

UPDATE (rephrase question):

I'm a DBA and need to ensure Developers don't provide poorly thought-out Deployment scripts by contributing to our Best Practice Documentation.
SELECT * causes an error in DEV when the Developer overlooks the Audit Table (this is a safety net) so the error is caught early in the development process. But somewhere in the SQL Constitution - 2nd amendment it reads "Thou shalt not use SELECT *" . So now there is a push to get rid of the Safety Net.

How would you replace the Safety Net, or should I consider this to be best practice for Triggers?

UPDATE 2: (solution)

Thankyou for all of your input, I'm not sure if I have a clear answer because this appears to be a very Grey subject. But collectively you've provided discussion points that can help our developers move forward with defining their Best Practice.

Thanks Daevin for your contribution, your answer provides the groundwork for some test mechanisms that our Developers can implement. +1

Thanks CM_Dayton, your suggestions contributing to best practice can be beneficial to anyone who is devloping Audit Triggers. +1

Big Thanks to ypercube, you've raised plenty of thought around the issues concerning tables undergoing different forms of definition changes. +1

In conclusion:

Is Select * ok in a tigger? Yes, it's a Grey area, don't blindly follow the "Select * is Bad" ideology".

Am I asking for Trouble? Yes, we do more than just add new columns to tables.


texts: Create View For All Table Names Listed In Table

texts:
I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure.  I have a table that holds table names, I need to iterate each table name and create a view with the data from each of those table names.  Like below is what I need to do, BUT my issue is that I iterate each table name and the view is dropped, not appended.

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) >= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

While @@FETCH_STATUS = 0
  Begin
    Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null'
    Print(@sql)
    exec(@sql)

    Fetch Next From c1 Into @employee

End

Close c1


texts: How do I efficiently emulate JSON_TABLE in Mariadb?

texts:
I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very actively worked on right now, which reduces the desire to switch to MySql just for this.
I have a work around query, which gives me what I want, but want to know if it is the most efficient way.
create table natural_numbers (primary key (n))
with recursive nn as (
    select 0 as n
    union
    select n + 1
    from nn
    where nn.n < 100
)
select *
from nn;

with all_values as (
    select json_value(pr.record_data, concat('$.*.A.B.C[',nn.n,'].D.*')) x
    from xxx pr
        cross join natural_numbers nn
)
select *
from all_values a
where a.x is not null
;

My concerns with this are that I am building more rows than I need and then filtering out nulls. In this specific case, I think it will be very unlikely to have more than 6 elements, so I might be able to get away with limiting the process to 6 rows at most, but what is the recommended approach in general without making assumptions on the max number of elements.


texts: Linguistic corpus design: order elements of different types

texts:
I'm trying to design a database for a linguistic corpus search engine. Basically there is a manuscript which contains several songs, and every song in turn contains stanzas and proses. Since stanzas (poetic text) and proses (text in prose) have their own attributes, I decided to "create" two distinct entity types instead of a generic one.

Some of the relevant busines rules are:

manuscript is in a 1 to n relationship with song
song is in a 1 to n relationship with stanza and with prose

My question is: since in one song there could be both stanzas and proses in any order (es: stanza1, stanza2, prose1, stanza3, stanza4, prose2), I need a way to reconstruct their order. What do you think is the best conceptual way to go to implement this? I've come up with some ideas, but I don't know if they are good or not:

"Create" an entity type "position" in a n to 1 relationship with "song", and put in there a composite PK (fksong and an integer storing the relative order of all the pieces) and some kind of enum or boolean to specify the type (stanza or prose).
Just "denormalize", store both stanzas and proses in the same table giving them a type, and order, and store the different attributes in other tables linked in a n to 1 relationship with it.
What I have done in the following logic implementation: create a table "block" in a 1 to 1 relationship with both the tables "stanza" and "prose" and store in it just its auto-incremental ID (referenced as fkblock in "stanza" and "prose") and an integer which represents the relative position of stanzas/proses inside the song.

Is there a better/correct way to do it? Please note I'm using MariaDB.
Thank you in advance.

CREATE DATABASE poed CHARACTER SET utf8mb4 COLLATE utf8mb4_icelandic_ci;

CREATE TABLE manuscript (
  idmanuscript TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (idmanuscript));

CREATE TABLE song (
  idsong TINYINT UNSIGNED NOT NULL,
  fkmanuscript TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (idsong),
    FOREIGN KEY (fkmanuscript)
    REFERENCES manuscript (idmanuscript));

CREATE TABLE stanza (
  idstanza SMALLINT UNSIGNED NOT NULL,
  stanzatext TEXT NOT NULL,
  fksong TINYINT UNSIGNED NOT NULL,
  fkblock SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (idstanza),
    FOREIGN KEY (fksong)
    REFERENCES song (idsong),
    FOREIGN KEY (fkblock)
    REFERENCES block (idblock));

CREATE TABLE prose (
  idprose TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  prosetext TEXT NOT NULL,
  fksong TINYINT UNSIGNED NOT NULL,
  fkblock SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (idprose),
    FOREIGN KEY (fksong)
    REFERENCES song (idsong),
    FOREIGN KEY (fkblock)
    REFERENCES block (idblock));

CREATE TABLE block (
  idblock SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  position TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (idblock));


texts: I tried using trigger concept. If any new row inserts in the user table my trigger will fire and try to create a dynamic database for that new user. But Inside a trigger create database is not working. How can get it done. Can anybody have any suggestions.Please help of on this


texts: Can statistics affect the query logic instead of performance?

texts:
Is it possible that the frequency of rebuilding indexes (which automatically triggers statistics update) can affect the query logic?

One of the developers I work with claims that a stored procedure failed (there was a type conversion error) because the statistics have not been up to date during the query execution and it used the wrong query plan. He also claims that after having updated statistics the procedure works fine.

Is it even possible?


texts:
This is driving me nuts!

I want to rename a column from read-more to read_more in my blog table

I tried all this:

  ALTER TABLE blog RENAME COLUMN read-more to read_more;
  ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT NULL;

And I always get this!

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN read-more to read_more' at line 1

I'm using MySQL 5.5


texts: Should I run a delete first before I execute index rebuild to a large table about 15 Million records

texts:
I need help for a better approach on how to approach this.

Currently we have a table that is huge about 15 million records.  We plan to create an index on this table however due to large size, we decided to delete records first and then create the index.  Is this the right approach?

Badly needed an advise asap!


texts: SQL Server 2017 Express faster than Standard Edition

texts:
What could explain SQL Server Express Edition being much faster than Web / Standard Edition?

These are multiple VMs in cloud hosting. They all experience the same problem. Some are on SQL Server 2012, others on 2017 - same problem. All machines have latest updates.

Machines are good enough for the job: 8G 4 cores and DBs are small in size less than 1GB of data. Same machines. I even tried have both Express and Web Editions on the same machine with only one instance enabled at a time, same problem.

It does not matter if the web application is run locally in the same machine or remotely accessing SQL sever though network, same problem.

The DBs have exactly the same data. I used a backup of Express and loaded it on a Web Edition server. This is a VM with SSD Drives. Install was as standard as possible. I compared settings in SSMS looking at server properties. Files are small and in the standard locations.

Queries are multiple in forms and complexities all run about half the speed in Web Edition. This is a repeatable problem not just once off.

It is not specific to Web Edition and the type of server as this was replicated on development machines on Express VS Developer Edition.

SQL Server settings appear to be the same.

Multiple people have looked at it and we cannot figure out why.

I was told to give example:

    select top (500) *
    from UserLog u
    join staff s on  s.Username = u.username
    join Forms f on f.PageFile = u.PageFile
    left outer join Client c on c.CID=u.CID
    order by u.id desc

Execution plans are exactly the same.

These are the plans for Express and Web Edition. They look the same to me.

Here are the timings as measured by SSMS (latest):

Express

Web Edition

Over multiple tries Express is 50% faster on this very simple query.

Is there a minimum DB size for non-Express Edition? (this would not make sense...)


texts: Does SQL Server optimize two ">" operations on same column

texts:
Can SQL Server optimize the following:
DECLARE @id INT;
DECLARE @d1 DATE;
SELECT a, b
FROM t
WHERE id_column = @id
AND date_column >= CAST(CURRENT_TIMESTAMP AS DATE)
AND date_column >= @d1

Notice that there are two > operators on the same column. Assuming that today is 2020-12-23 and d1 could be either 2020-12-20 or 2020-12-27, I was hoping that SQL Server would automatically use greater of the two dates... 2020-12-23 in the first case and 2020-12-27 in the latter. But I am not sure.


texts: mysql Replication and Point in time Recovery

texts:
I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point in time recovery? The relay logs?

Thanks and regards,
Martin


texts: How to delete rows from the mid of the table without any where condition?

texts:
I have a table testing with two columns

id: auto_increment
name: varchar(20)

I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records from MySQL table (InnoDB engine). How can I achieve this?

This table I have created is just for testing purposes. My actual table contains billions of records. One way I thought as:

delete from testing where id in (select group_concat(id) from testing limit 1, 2);

I think this would be quite slow in case of my actual table.

Can anyone provide any better solution?


texts: Count neighbors and sum of the neighbors values

texts:
I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct adjacent edges.

I have my_table t

id in_node out_node weight

  1  'A'      'B'    17
  2  'B'      'A'     4
  3  'C'      'A'    35
  4  'A'      'D'    26
  ...
  5  'C'      'G'    33
  6  'X'      'Z'    12
  7  'Z'      'Y'    15
  8  'X'      'Y'    42
  9  'K'      'M'    66
  ...
 10  'A'      'Z'    20

Desired behavior

select id, in_count, in_weight, out_count,out_weight from t where  id = 10
  10      3          65          2        27
         2,3,4      4+35+26     6,7      12+15
   (1 is duplicate)

Steps to count number of distinct in_neighbors:

$A: select in_node from t where id = 10
@B: select id from t where in_node = $A or out_node = $A  --select in neighbor ids (1,2,3,4,10)
@C: select in_node as that_node from t where id in (@B) -- ('A', 'B', 'C')
   union all
   select out_node as that_node from t where id in (@B)  --('A','B','Z','D')
(count distinct @C)  --5 now we can subtract two ('A' and 'Z' nods) to get the number of neighbors from in_node side

To count in_weight we should select sum(weight) from t where id in (2,3,4). We shouldn't count weight for id=1 because

select in_node from t where id = 1
union all
select out_node from t where id = 1
--'A','B'

produces the same set as

select in_node from t where id = 2
union all
select out_node from t where id = 2
--'A','B'

while

select weight from t where id = 2

is smaller than

select weight from t where id = 1

Is it possible to do this sort of selects from PL SQL or is it easier to just pass the result set to Java?

This table represents a graph. Vertices are in_node and out_node, id corresponds to the edge and weight corresponds to the weight of the edge. I want to find all neighbor edges along with their weights. If there are several edges connecting two vertices only the the lightest edge should be accounted for.

So, 'in_total' corresponds to the total sum of the weights of "distinct" neighbor edges. By distinct I mean lightest edge connecting two given vertices.

I am modelling maximum travel distance across a graph. Sometimes A-B route is reported several times. In this case I want to only pick one A-B route with the lightest weight.

Total weight of neighbors over the number of neighbors is a useful indicator of connectivity for my problem.


texts: What's the best way to convert dtsx packages to query a new server?

texts:
We just moved our sql server to a hosting company, and now all my dtsx packaged don't work.

I know that in a specific query you can use

    use SecondDatabase

but is there a way to do that with the server, too?

Goal: Not have to rewrite my code and paste [eih-dr01.db.org].livendb.dbo. infront of every table.


texts: Availability Group: ReadIntent connection failing

texts:
I have two nodes configured in an AG group in a dev domain:

node1.DevDomain.com
node2.DevDomain.com

My own workstation is on our production domain:

MyMachine.ProdDomain.com

When I test ApplicationIntent=ReadOnly connections from a machine within DevDomain I am correctly directed to the readable secondary. Note, the connection string specifies the Listener name as I am in the same domain.

However, when I test ApplicationIntent=ReadOnly connections from my own workstation (different domain) I get the connectivity error below. Note, in this case I need to specifiy the Listener IP since the I am outside the domain and the name will not resolve.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is conf
igured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.).Exception.Message

Server: Windows 2012, SQL 2012. My machine: Windows 7.

I believe I've got the Routing Lists and URLs correct (and again, this works fine from within the domain).

I know the nodes of the cluster need to be in the same domain, but I would think I'd be able to connect from outside the domain by specifying the Listener by IP, but maybe not?

I thought perhaps I had a DNS issue in the DevDomain, but everything seems to resolve fine inside that domain.

Can I do this / what do I need to be looking at to resolve this?


texts: connect two sql server and import to CSV file (ETL)

texts:
I have two servers, both has a sql server . Let's say srv1 has a sql-srv1 and srv2 with a sql-srv2 , i want to get some informations within sql-srv2 but making requests from sql-srv1.

my project is to create a script which could export data within sql-srv2 , in creating a bat files and to execute the script every day with task scheduler but first of all i have to connect the 2 sql-server and i don't know guys how to connect it, i'm a beginner.
I just want to create a script like

  sqlcmd -S . -d demo -E -s";" -w 700 -Q "select * from etudiant" > C:\troisiemetest%date%.csv

which can run everyday on the 2 sql servers linked.
Thank you


texts: How to avoid lock wait timeout exceed and improve MySQL InnoDB write speed

texts:
I ran a multi-threaded client spawning 25 threads to make concurrent API calls and insert data to AWS Aurora server.

After some time, I started to see timeout error: lock wait timeout exceeded try restarting transaction.We run the same test for a server running MySQL 5.6.10, and no lock wait timeout happened.

Is there a way to avoid this timeout ?

On the AWS Aurora server, SHOW ENGINE INNODB STATUS showed:

---TRANSACTION 8530565676, ACTIVE 81 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 6 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 405, OS thread handle 0x2ae270b03700, query id 11045 10.50.101.56 app_migration
INSERT INTO contacts_contactaudit (action,
    contact_id,
    date_created,
    date_updated,
    external_contact_id,
    entity_name,
    first_name,
    last_name,
    middle_name,
    actor_created_id,
    actor_updated_id,
    email,
    phone_number_id,
    external_contact_guid,
    external_shared_contact_id,
    active_timezone, audit_date)
SELECT 'I' as action, new.id,
    new.date_created,
    new.date_updated,
    new.external_contact_id,
    new.entity_name,
    new.first_name,
    new.last_name,
    new.middle_name,
    new.actor_created_id,
    new.actor_updated_id,
    new.email,
    new.phone_number_id,
    new.external_contact_guid,
    new.external_shared_contact_id,
    new.active_timezone, now();

This is the trigger we created for INSERTs statement:

CREATE TRIGGER contacts_contact_insert_audit
AFTER INSERT ON contacts_contact
FOR EACH ROW
    INSERT INTO contacts_contactaudit (action,
    ...
    audit_date)
SELECT 'I' as action, new.id,
    ...
now();

And this is the audit table schema:

  CREATE TABLE `contacts_contactaudit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime(6) DEFAULT NULL,
  `date_updated` datetime(6) DEFAULT NULL,
  `action` varchar(1) NOT NULL,
  `audit_date` datetime(6) NOT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `external_contact_id` bigint(20) DEFAULT NULL,
  `entity_name` varchar(128) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `middle_name` varchar(128) DEFAULT NULL,
  `actor_created_id` int(11) DEFAULT NULL,
  `actor_updated_id` int(11) DEFAULT NULL,
  `email` varchar(256) DEFAULT NULL,
  `phone_number_id` int(11) DEFAULT NULL,
  `external_contact_guid` varchar(128) DEFAULT NULL,
  `external_shared_contact_id` bigint(20) DEFAULT NULL,
  `active_timezone` varchar(128),
  PRIMARY KEY (`id`),
  KEY `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` (`actor_created_id`),
  KEY `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` (`actor_updated_id`),
  KEY `contacts_contactaudit_contact_id_9b809fe7_uniq` (`contact_id`),
  CONSTRAINT `contacts_contactaud_actor_created_id_3f6f4269_fk_actors_actor_id` FOREIGN KEY (`actor_created_id`) REFERENCES `actors_actor` (`id`),
  CONSTRAINT `contacts_contactaud_actor_updated_id_2fafc937_fk_actors_actor_id` FOREIGN KEY (`actor_updated_id`) REFERENCES `actors_actor` (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=21577
DEFAULT CHARSET=utf8;


texts: Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middle

texts:
I'm stuck at trying to write the correct SQL code for retrieving and displaying all results from querying three tables. Simply put, I have three tables: A, B, and C. A and C are not connected to each other and must use B to get the referenced data that A needs from C. Below are simple examples of the tables:

Table Users
-----------
user_id     username
-------     ---------
1           bob
2           harry
3           jill

Table User_Emails
-----------------
email_id    user_id
--------    -------
1           1
2           1
3           2

Table Emails
------------
email_id    address
--------    --------------
1           bob-email1@test.com
2           bob-email2@test.com
3           harry-email1@test.com

In this example, the user_emails table holds an id that links an email address to a user account, letting a user have multiple email addresses. What I am trying to do is retrieve all the users and the email addresses for those users and if they do not have an email address just display a null value.

I cannot change the table as this is how it is set in stone. The database that is being used is Oracle 12c. Currently I have a cursor that grabs all the users and then loops through each user, checking it against the user_emails table. While this would work for a small result set, the reality is that there are thousands of records and I know this is inefficient, however I cannot figure out how to display the usernames and email addresses together along with null values for email addresses if the username doesn't have an id in the user_emails table. I asked the in-house DBA and didn't get anywhere, so I am asking for some SQL help from the gurus as this dummy (me) cannot figure it out.

Thank you for your time and help.


texts: Conditional where for postgres stored procedure

texts:
I want to create a conditional where clause using a PostgreSQL stored procedure.

This is my original stored procedure :

    select distinct
    t.serialnumber as sn,
    t.productid as tid,
    t.merchantid as mid
    from tbterminal as t
    ORDER BY idterminal ASC

I want to check if p_mid, p_tid and p_sn are null or empty; if not null and not empty, add an and where for every component.

This is what I've tried :

CREATE OR REPLACE FUNCTION get_list_terminal_datatable (
    p_mid varchar,
    p_tid varchar,
    p_sn varchar,
    p_limit int,
    p_offset int
)
 RETURNS TABLE (
 sn VARCHAR,
     tid varchar,
     mid varchar
)
AS $$
DECLARE
  data_where varchar := 'WHERE ';
BEGIN

 IF p_mid notnull and p_mid != '' THEN
   data_where := data_where || ' t.merchantid like '''%|| p_mid ||'%'' ';
 END IF;
 IF p_tid notnull and p_tid != '' THEN
   data_where := data_where || ' t.productid like '''%|| p_tid ||'%'' ';
 END IF;
 IF p_sn notnull and p_sn != '' THEN
   data_where := data_where || ' t.serialnumber like '''%|| p_sn ||'%'' ';
 END IF;
 RETURN QUERY
 select distinct
    t.serialnumber as sn,
    t.productid as tid,
    t.merchantid as mid
    from tbterminal as t
    ORDER BY idterminal ASC
    limit p_limit offset p_offset;
END; $$

LANGUAGE 'plpgsql';

I know it doesn't work properly, so I need help on how to make the conditional where clause stored procedure in PostgreSQL work.

How would I have to write the stored procedure correctly?


texts: REPMGR - change reconnect_attempts and reconnect_interval not working

texts:
I configured PostgreSQL automatic failover with REPMGR. But, in my simulation the sleeping time is 1 second for each reconnect attempt:

If I consult official documentation the default value to reconnect interval is 10 seconds.

#reconnect_interval=10            # Interval between attempts to reconnect to an unreachable
# primary (or other upstream node)

I tried to change values of reconnect attempts and interval in repmgr.conf and restart service of repmgr and use "repmgr standby register --force" but doesn't work.
My REPMGR.CONF
node_id = 2
node_name = 'PGCluster02'
data_directory='/var/lib/postgresql/12/main'
conninfo = 'host=PGCluster02 port=1010 user=my_repmgr dbname=dbrepmgr connection_timeout=2'
use_replication_slots=1
reconnect_attempts=30
reconnect_interval=10
failover=automatic
promote_command='/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

Environment
PostgreSQL Version: 12.5
REPMGR Version: 5.2
Debian version: 10

Other tools in my environment are Barman (another VM) and PGBouncer (current VM)
How can I change the reconnect interval to 10 or 15 seconds? Anyone can help me?
I want to put my failover time to 5 minutes.
Thank you!!


texts:
I have this query:

WITH
  I1 AS
  (
    SELECT
      *
    FROM
      XYZ A
    WHERE
      UPPER(A.DES) LIKE '%%' ESCAPE '\'  --'
      AND NVL(A.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= TO_DATE('2016-06-01', 'YYYY-MM-DD')
      AND NVL(A.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= TO_DATE('2016-06-08', 'YYYY-MM-DD')
      AND A.ID_OU IS NOT NULL
    ORDER BY
      IDFR
  ),
  I2 AS
  (
    SELECT
      I1.IDFR, I1.DES, I1.ID_OU,D1.VAL, T1.CODE
    FROM
      ABC D1, I1, DEF T1
    WHERE
      D1.IDFR = T1.IDFR
      AND D1.IDFR = I1.IDFR
  ),
  I3 AS
  (
    SELECT I2.* FROM I2
  ),
  I4 AS
  (
    SELECT
      *
    FROM
      I3
      PIVOT
      (
        MAX(VAL) AS VAL FOR CODE IN ('AB' AS ABC , 'XY' AS XY)
      )
    ORDER BY
      DES
  ),
  I5 AS
  (
    SELECT COUNT(*) TOTAL_NO_ROWS FROM I4
  ),
  I6 AS
  (
    SELECT I4.*, rownum row_number FROM I4
  )
SELECT
  I6.*,  I5.TOTAL_NO_ROWS
FROM
  I5, I6
WHERE
  row_number > 0 AND row_number < 3;

Sometimes it runs fine, but other times it throws this error:

  ORA-01428: argument '000000000000000000000000' is out of range

I implemented the same structure and data in another database (similar database configuration) and didn't face such error.

How to resolve this issue?


texts: Joining two processed tables without Primary Key

texts:
I recently started such an exiting activity as digging in databases.
Straight to the point.
There are two tables. First one shows the current stock of products. I simplify it by following query:

SELECT *
FROM dbo.INDIVID
WHERE PAWHLO ='h550'--Specific warehouse

The second one is showing past, current and future orders.It contains hell lot of info including multiple orders of same SKU:
 SELECT DISTINCT P1MTRL AS SKU, SUM(P1UPL1) AS PLANNED_quantity
FROM dbo.PORDHV
WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
GROUP BY SKU
ORDER BY PLANNED_quantity DESC

Both of the queries work on themselves, but combining them is something I cant manage,
How can I combine those two queries in one,preferably outer joint, so I dont lose the data neither from stock nor future orders?
I tried:

SELECT DISTINCT PORDHV.P1MTRL,INDIVID.PAANTX AS ON_STOCK, PORDHV.P1UPL1 AS PLANNED
FROM dbo.PORDHV
FULL JOIN
 dbo.INDIVID
ON
PORDHV.P1MTRL=INDIVID.PAANUM
WHERE PORDHV.P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND PORDHV.P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
AND INDIVID.PAWHLO ='h550'
AND INDIVID.PAANTX  0
ORDER BY PLANNED DESC

But it apparently returns noncense.
Thanks in advance.

P.S.

I was asked to clarify.
I will leave the UNION, with which I managed to get an something the resembles what I need:

SELECT
  INDIVID.PAANUM as P1MTRL,
 SUM(INDIVID.PAANTX) AS AVAILABLE_QUANTITY,
 '' as PLANNED_QUANTITY,
 convert(date,getdate()) as Date
  FROM
  INDIVID
    WHERE PAWHLO ='h550'
  GROUP BY INDIVID.PAANUM
  --ORDER BY QUANTITY DESC
  union
   SELECT DISTINCT DS1.P1MTRL,
  '' as AVAILABLE_QUANTITY,
  SUM(P1UPL1) AS PLANNED_QUANTITY,
  convert(date,convert(varchar(10),P1PTF)) as Date
FROM dbo.PORDHV as DS1

INNER JOIN (
  SELECT DISTINCT P2ANR as P1MTRL
  FROM [POPPROD].[dbo].[PORDRDART]
  WHERE PMWHSL = 'H550'
  ) as DS2 on (
  DS1.P1MTRL = DS2.P1MTRL
  )

WHERE P1STAT NOT IN ('E','T','P','R') --Status is not E, T, P or R (Executed, Terminated, Paused or Running).
AND P1PTF>= FORMAT(getdate(), 'yyyyMMdd') -- Date of planned closing is same or later than current date.
--AND P1MAS2 in ('ph640','ph625')
GROUP BY DS1.P1MTRL, P1PTF
--ORDER BY SUM(P1UPL1) DESC
order by P1MTRL

In this way it returns the union of tables with several rows related to same SKU. I also added date to be able to play with periods in the future. Date for the row of current stock is TODAY, when there are two or more orders of the same SKU on particular date - they are summed up.

400011873   0       0       2018-03-02
400015114   0       0       2018-03-02
400052975   24894   0       2018-03-02
400052977   540     0       2018-03-02
400052979   0       12000   2018-03-05
400052979   8311    0       2018-03-02

How to get those rows of SKU's distinct. What would You improve and aggregate before going to Report Manager?


texts: How do I distribute data from the *source database* to the *destination database* in `SQL`?

texts:
I have a different source and destination database server with the same name.

On a periodic interval, we push "new" data configuration to the source database and manually update the data in the destination database.

Instead of handling it manually, we are trying to do one time "new" data distribution from source db to the listed linked server destination db's.

I presume that if we use "Linked Server"s it can help to achieve the data distribution by validating missing (or) unavailable records in the destination database servers.

Scenario : New price data distribution from source database to the client databases. We might have new price for new products (or) new price for existing products. Based on the "new" data changes or "new" data in the source db, we will have to distribute the client db.

How do I distribute data from the source database to the destination database in SQL?


texts: What's the difference between a thread vs. a process?

texts:
Specifically regarding MySQL, what is the difference between a thread and a process? The documentation seems to refer to them interchangeably at times, and it even throws in the term "session" too.

At the most basic level, the command to show threads includes the term PROCESSLIST, as if a list of processes is a list of threads. (https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html -- The first sentence says this list "shows which threads are running.") But then, in the performance_schema.threads table, each thread there has its own unique thread_id that is not the ID returned from SHOW [FULL] PROCESSLIST. Instead, PROCESSLIST_ID is a foreign key to the PROCESSLIST (https://dev.mysql.com/doc/refman/5.6/en/threads-table.html). This leads me to believe that threads and processes are not the same thing.


texts: SSIS Merge Join Trouble: 3 tables into 1

texts:
So I am having trouble merging three tables into one. All sources are DB, and I am using the MERGE JOIN function. When I join my first two tables, using standard left inner join, it returns the exact same number of rows as in the left (and largest) table, which is what I expect.

Here's where my trouble begins. I then apply another sort on the newly created table because I have to use a different sort key, as none of the three tables have common columns. When I attempt the second merge, it will count the first ~50k rows from the sort, stop counting, and continue to insert more rows into the destination table. I end up with a great many more rows than from either of the original tables. The highest source table has 3.3 million, but letting it run for a few hours generated over 800 million rows! Here is what the data flow looks like:

I'm sure this is either a lack of personal understanding of what I'm doing, or I need to take a different approach in SSIS to eliminate this Cartesian product situation.


texts:
this is the query:

$sql = "select pn.id, pn.purchased_number, pn.plan, pn.ring_to_group,
        count(rtg.id) as totalGroups,
        concat_ws(',', group_concat(rtg.group_name), group_concat(rtg.id)) as groupList
           from tbl_phone_numbers as pn
           left join tbl_rings_to_groups as rtg on rtg.user_id=pn.user_id
           where pn.user_id = ? and pn.status = ?";
$query = $this->db->query($sql, array($this->userId, 'Y'));

that returns the result of groupList like default,demo,1,2 but i want to get result something like :

default:1,demo:2

in MYSQL any suggestion??


texts: import text file getting command is not allowed with this MySQL version

texts:
here is my table

CREATE TABLE cities (
    country       CHAR(2),
    city_ascii    VARCHAR(100),
    city          VARCHAR(255),
    region        CHAR(2),
    population    INT UNSIGNED,
    latitude      DECIMAL(10, 6),
    longitude     DECIMAL(10, 6),
    INDEX idx_lat_long (latitude, longitude),
    INDEX idx_country (country),
    INDEX idx_region (region)
);

I'm trying to import a text file to my database following tutorial here.

LOAD DATA LOCAL INFILE 'worldcitiespop.txt' INTO TABLE cities
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(country, city_ascii, city, region, population, latitude, longitude);

but I'm getting the following error message.

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Any ideas?

Thank you