texts: Should I stop transaction log backups during a full backup?

texts:
I have two SQL Agent jobs which are scheduled to run at different intervals. The first job runs a full backup once a day. The second job runs transaction log backups every fifteen minutes.

As the database has grown, the full backup is taking longer than originally planned (even with compression). And I've noticed from my logs that the transaction log backups are now running at the same time.

Should I change the schedule of the transaction log backup so that doesn't run when the full backup is running? Does it matter?


texts: Auto running SQL script with Azure database server

texts:
I am trying to automate a report that is generated from the database on Azure database server. I am using below script in the batch file but I get the error.
Script:
osql -U firstname.lastname@company.edu -S aazdb01.c224.database.windows.net  -P "Password" -s "|" -i "c:\Users\fl1\Desktop\script.sql" -o "c:\Users\fl1\Desktop\output.txt"
Error:
Cannot open server "company.edu" requested by the login.  The
login failed.

If I run the below script with 'Sqlcmd', I still get the error.
Script:
sqlcmd -S aazdb01.c224.database.windows.net -U firstname.lastname@company.edu -P "Password" -s "|" -i "c:\Users\fl1\Desktop\script.sql" -o "c:\Users\fl1\Desktop\output.txt"
Error:
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Cannot open server "company.edu" requested by the login.  The login failed..
Can anyone advise what is the issue here?


texts: Stubborn autovacuum process slow things dows

texts:
We are facing a strange problem since yesterday.

In a database of ours there is a partitioned table containing historical data.  That is, every week's data goes to a different partition and stays there till the end of time.  There are no further modifications to these - no UPDATE, no DELETE, no TRUNCATE, even no INSERT once the week has passed.

At least this is the theory.  We realized that some bug caused a heavy multiplication of a part of the data.  The order of magnitude is about 60k useful and some 15-20M unnecessary rows.  There are other rows as well, unaffected by the bug - the overall partition size varies between 6 and 15 GBs.

Once the bug was fixed, we decided to remove the unnecessary rows, keeping the latest one of the sometimes 9000 siblings.  Of course, this means that some serious vacuuming will be needed as around a quarter of the tables is gone now.  The nice autovacuum daemon jumps on the back of the partitions a few minutes after finishing the removal.  And, at least for some of the partitions, when it finishes, has a few minute's rest, and starts again.  And again.  And...

It seems that this has an adverse effect on other IO-heavy processes, so we tried to VACUUM ANALYZE manually the table(s) in question.  As expected, this terminates the autovacuum process, removes what should be removed and exists successfully.  And then the autovacuum process jumps again, and again, and...  Each run lasts more than two hours.

Have you ever experienced this?  If yes, what was the solution?  I have a feeling that a VACUUM FULL would solve it, but as the applications must be able to read the tables nearly continuously, it is not an option.  Actually, we are migrating away from that structure, and once it is done, the problem disappears, but this issue seems to slow the migration process down significantly.  So any idea would be more than welcome.


texts: Can't run digest() on PosrgreSQL 9.3 on RDS

texts:
Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS:

devdb=> SELECT digest('blah', 'sha1');
ERROR:  function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha1');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Even though pgcrypto exist:

devdb=> SHOW rds.extensions;

  btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp

How can I enable the digest() function?

Updates

I have co-asked on the AWS RDS forum, will posts mutually updated.

digest() does not seem to exist on the db:

devdb=> \df digest
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)


texts: How to convert numeric values to text in sql for select query

texts:
I have string values like this

'[123, 124]'

I could trim the value using below command

select trim('[123, 124]', '[]'); --returning '123, 124' as text

I want to pass the value above as

select *
from mytable
where numeric_column in (select trim('[123, 124]', '[]'));

I can understand, numeric_column is numeric. But the inner query is returning the data as text. I could not convert the inner query to numeric since it has a comma.  If I want to convert the result to '123', '124', I could run the following command and get the expected result:

select *
from mytable
where numeric_column::text in (
    select func_to_change(select trim('[123, 124]', '[]'))
);

How can I achieve this? What is the func_to_change logic I need to write?

Note: I am using Postgresql 9.1.


texts:
I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008?

FORMAT([PhoneField],'###-###-####')


texts: Unable to get output variable of Oracle procedure using Sql linked server

texts:
I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure.

  OLE DB provider "ORAOLEDB.Oracle" for linked server "TESTORACLE"
  returned message "ORA-06502: PL/SQL: numeric or value error: character
  string buffer too small ORA-06512: at "TESTUSER.PKG_TEST", line 1744".
  Msg 7215, Level 17, State 1, Procedure SQLSTOREDPROCEDURE, Line 75
  [Batch Start Line 0] Could not execute statement on remote server
  'TESTORACLE'.

I have gone through the code and found that the error occurs only when assigning the value for OUT parameter.

SQL SERVER PROCEDURE

CREATE PROCEDURE [dbo].[SQLSTOREDPROCEDURE]  AS
DECLARE @op_update_status Nvarchar(50)
DECLARE @op_error_message Nvarchar(500)
BEGIN
 SET NOCOUNT ON;
 SET XACT_ABORT ON;
 SET @p_process_name = 'PROCESSNAME'
 EXECUTE ('BEGIN  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?); END; ',@p_process_name,@op_update_status ,@op_error_message ) AT [TESTORACLE];

 RETURN 0;
END

ORACLE PROCEDURE

PROCEDURE  P_SET_VALUES  (   p_process_name      VARCHAR2,
                             p_processed_dt      DATE,
                             op_update_status OUT VARCHAR2,
                             op_error_message OUT VARCHAR2
                         ) AS

 v_update_status VARCHAR2(50);
 v_error_message VARCHAR2(500);

 BEGIN

    USER2.PKG_TEST2.P_SET_VALUE  ( p_process_name,'TEST STATION',p_processed_dt,v_update_status,v_error_message) ;
     op_update_status := v_update_status;
     op_error_message := v_error_message;

END;

Then I googled and alter the procedure by specifying the OUTPUT parameter in SQL Server.

EXECUTE ('CALL  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?)',@p_process_name,@op_update_status OUTPUT ,@op_error_message OUTPUT) AT [TESTORACLE];

After that I get below error,

  Msg 7215, Level 17, State 1, Procedure
  SQLSTOREDPROCEDURE, Line 75 [Batch Start Line 0] Could not execute
  statement on remote server 'TESTORACLE'.

Please help me to overcome this error.

Thanks.

UPDATE

Even though Sql server return a error message, oracle procedure was called. Checked with dummy insert in application log table.


texts: mysql workbench migration changed my database. Can I trust it?

texts:
I'm migrating a large production database(s) from mysql 5.5 to 5.6.
Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines.

The process worked well but there are major changes to the database:

For example:

Changed order of fields in primary key
PRIMARY KEY (ru_sid,ru_rule_id), =>   PRIMARY KEY (ru_rule_id,ru_sid)
Messed encoding while the data is expected to be utf8
I did a query of the same data in the original and new server and the output is different:
Pré-Home Flash Sales => PrÃ©-Home Flash Sales

Possibly there are other changes.

My question is - how can I tell if the migration is trustable. I cant check every table to see if changes in the index will not break something in my code.
Can the workbench migration be trusted ?
Is there a better way to do the migration? What would you do?

EDIT:
Create statement before:
 (I removed the fields that are the same in the before and after)

CREATE TABLErules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
ru_datatext COLLATE utf8_unicode_ci NOT NULL,
ru_namevarchar(64) COLLATE utf8_unicode_ci NOT NULL,
ru_tagsvarchar(64) COLLATE utf8_unicode_ci DEFAULT '',
ru_priorityvarchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
  PRIMARY KEY (ru_sid,ru_rule_id),
  KEYnum(ru_rule_id) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9998 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create statement after:

CREATE TABLErules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) CHARACTER SET utf8 NOT NULL DEFAULT '',
ru_datatext CHARACTER SET utf8 NOT NULL,
ru_namevarchar(64) CHARACTER SET utf8 NOT NULL,
ru_tagsvarchar(64) CHARACTER SET utf8 DEFAULT '',
ru_priorityvarchar(12) CHARACTER SET utf8 NOT NULL DEFAULT '1',
  PRIMARY KEY (ru_rule_id,ru_sid),
  KEYnum(ru_rule_id)
) ENGINE=MyISAM AUTO_INCREMENT=9954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: how much overhead an error in RDBMS has?

texts:
I have a class in my project. On the class I need to do two inner select, to know is there any duplication or not, but I think if I get duplication error and then manage that is better than to select.
Error overhead Vs. Two inner select, which one?


texts: Determine existence in recursive working table

texts:
I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is already found.

I tried to reference the working table in an EXISTS subquery in the JOIN to prevent the unnecessary checks, but this gave an error that the recursive query cannot be referenced in a subquery.

For a simplified example modified from the docs only meant to demonstrate my problem in code:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100 AND NOT EXISTS (SELECT 1 FROM t u WHERE u.n = t.n+1)
)
SELECT sum(n) FROM t;

This is of course logically ridiculous, but the NOT EXISTS is the problem and specifically what gives an error.

Is there a way to determine existence in the working table?


texts: Query or ETL process to insert data into fact tables in SSMS

texts:
I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle.

The thing that I'm having trouble loading data into my fact tables. Below is my schema design. I was able to load data into my dimension table:


texts:
Some databases search/check multiple locations for configuarion files, if it finds many, it merges and does a priority if same options defined.

Does mongodb search multiple locations for configuration files? (starting as a service, not using --config option)

From searching I know it checks /usr/local/etc/mongod.conf by default.

I am hoping to do is separate my global config and production/development config.


texts: Windows Server 2019 and SQL Server Backups issue?

texts:
I have an issue with SQL Server that is really driving me nuts so I wanted to ask if anyone has any suggestions.
I have a program that does a daily backup of the SQL Server database. This has worked for two years.
Now, today, we had to upgrade our server and along with this update (now Windows Server 2019 Essentials), we upgraded to SQL Server 2019 Express.
Now, when I try to perform the backup, I get the error:

Cannot open backup device '\\SERVER\DIRECTORY\BACKUPDIRECTORY\Backup_Wed.BAK'. Operating system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.

I know that this error generally means that SQL Server doesn't have read / write access, but the issue I have is that it DOES.

This IS the SQL Server Instance, as you can see here:

AND, if I run a backup from within SQL Server Management Studio, this does succeed...
I also checked to make sure that the file and it's contents weren't set to "Read Only" and they're not.
Those are the only two causes I'm aware of and all I was able to find searching online over the past several hours.
There's plenty of room on the drive as well (As I said, it's brand new and there's little on it).
Has anyone else seen anything like this or know what else might cause this issue?  The old SQL Server version was the prior version, so I wasn't upgrading from some ancient release either.
And here's my SQL that has worked for several years.  I can't find anywhere where SQL Server 2019 changed the requirements here either:
BACKUP DATABASE THEDATABASE
TO DISK = '\\\\SERVER\\DIRECTORY\\BACKUPDIRECTORY\\Backup_Wed.BAK'
WITH INIT

I'm at a loss...  If SQL Server has access permissions on the directly, how can it NOT have permissions on the directory?
Also, just to clarify, this is a local directory. SQL Server is on the server and is attempting to backup to a directory ON the server.
Is this a bug in Windows Server 2019?
I really appreciate everyone's help and sincerely hope that someone knows a solution here. Thank you again.


texts: I am not able to create/reindex a unique index in a table

texts:
I have a table that contains user locations (called public.user_location) and is the following:

   Column        |           Type           |                            Modifiers
     id          | bigint                   | not null default nextval('user_location_id_seq'::regclass)
     user_id     | integer                  | not null
     location    | geography(Point,4326)    |
     accuracy    | smallint                 |
     datetime    | timestamp with time zone | default now()
Indexes:
   "users_locations_pkey" PRIMARY KEY, btree (id)
   "user_location_datetime_desc_idx" btree (datetime DESC)
   "user_location_user_id_idx" btree (user_id)
   "users_locations_gix" gist (location)

Foreign-key constraints:
   "users_locations_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE
CASCADE
Referenced by: ...

I had a unique constraint composed by the fields (location, datetime, user_id), but then I found out that it was corrupted and so there were duplicates.

After the deletion of the duplicates I tried to reindex but I have got the following message:

The connection to the server was lost. Attempting reset: Failed.
!>

So I am not able anymore to create the constraint or even just the unique index and I always got an INVALID index.

What should I do?
Let me know if you need more info.

Cheers


texts: Database design for medicine/drug search engine

texts:
I am working on developing a medicine search engine which will show following details about a particular medicine/drug.

Trade name of drug
Manufacturer
Salt composition
Dosage i.e, 50mg, 100mg
Uses
Side Effects
Working
Related drugs from other companies based on salts / dosage
Formulation - tablet, capsule etc
Packing i.e, 10 tablets in 1 strip
Pricing
Expiry
Batch

There can be a drug with multiple variants based on dosage / formulation.

I have made following tables to store the data

medicines Table

id
name
manufacturer_id

**manufacturers Table

id
name
...

medicine_variants Table

id
medicine_id
formulation (tablet/capsule etc)
dosage
unit (tablet / strip / strip / vile / box)
count (no of units in packing)
packing (strip / box etc)
...

salts table

id
name
usage
working
side_effects
...

medicine_variant_salts Table

id
medicine_variant_id
salt_id
quantity
...

medicine_variant_batches Table

id
medicine_variant_id
batch_no
manufacturing_date
expiry_date
price
...

Suggest any improvements in the database design. Related drugs/medicines for a particular medicine can be derived by selecting all those medicines where salts are same.


texts: Issue Restoring Certificate to SQL Server When Different Service Account Used

texts:
I am attempting to configure backup encryption in SQL 2017 and have run into some problems.

I have no issues in creating the master key and certificate on one machine. Nor do I have a problem getting the cert installed on another machine and reading the backup, however, I have an issue where this does not work should the other machine be running a different service account than the one where cert is created.

Here are the steps I am taking (I also tried restoring the master key but that also throws an error):

/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO

/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
   WITH SUBJECT = 'Backup Encryption Certificate';
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'
    ENCRYPTION BY PASSWORD = 'SomeRandomPwd';

BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',
     ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');

GO

/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',
     DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');

--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'
    DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
    ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';

--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.

References I have looked at to try and figure out what is going on here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017

I have also ensured that the SMK is the same between all machines, but still no luck in getting the cert restored.

I feel sure that I'm doing something wrong here, but I have not been able to figure out what.

Any ideas would be appreciated. Thanks.


texts:
SQL Server 2012.
AlwaysOn
Cluster with two nodes, each has 4 cores

We are consolidating SQL server instances and moving bunch of databases from various locations to a single cluster which has two instances, 4 cores each.
There are "more" and "less" important databases which are going to be mixed at the same location. Is there a way to make queries from a database to use only certain core on the host?

Example:

We have 10 databases named A1-A10.
Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10  to use rest of the cores (in this case 1-3).

Thanks.


texts:
Trying to adjust Oracle DB 12.2 sharding configuration. Have one DB as the shard catalog and other two DBs as shards. Just want to test this functionality so run the tree DBs on the same machine in the same ORACLE_HOME. Using composite sharding method.

All the things done according to the official documentation. GDSCTL> config shows  nothing wrong as I can see. but:

GDSCTL>config shard
Name                Shard Group         Status    State       Region     Availability
----                -----------         ------    -----       ---------- --------
ape002              shrdgrp_oper        Ok        Replicated  reg_oper   PENDING
ape003              shrdgrp_arch        Ok        Replicated  reg_arch   PENDING

In the example in the documentation the states are "Deployed" and the Availabilities are "ONLINE" and "READ_ONLY". Every time I try to redeploy I get:

GDSCTL>deploy
deploy: examining configuration...
deploy: requesting chunk deployment on shards in shardgroup 'shrdgrp_oper'  via GSM ...
deploy: requesting chunk deployment on shards in shardgroup 'shrdgrp_arch' via GSM ...
deploy: shards configured successfully
GSM Warnings:
shrdgrp_oper ape002:ORA-45598: Shard  is in converted state , it should be either "PRIMARY" or "PHYSICAL STANDBY".
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3865
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3827
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3861
ORA-06512: at line 1 (ngsmoci_execute)
shrdgrp_arch ape003:ORA-45598: Shard  is in converted state , it should be either "PRIMARY" or "PHYSICAL STANDBY".
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3865
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3827
ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN", line 3861
ORA-06512: at line 1 (ngsmoci_execute)

And in shard catalog I get:

SQL> alter session enable shard ddl;
ERROR:
ORA-02521: attempted to enable shard DDL in a non-shard database

Please tell me how to get it to work.


texts: What could be preventing updates and inserts to a table without anything visibly blocking the process?

texts:
The following has happened on more than one occasion.

Updates or inserts to a certain table (I'm not 100% sure but there seem to be a couple of tables it always happens to) fail due to a timeout. If I run the same query from Management Studio, it just keeps running without ever ending or failing (I've let it try up to an hour, on one occasion).

SSMS's Activity Monitor, as well as sp_who2 don't show the process as being blocked by anything. It seems to be running with no issues, except that it isn't doing anything or ending.

This is the same if it's only one row being updated, selected by its primary key; it's not an issue of the query affecting many rows or the row being difficult to be "found", as far as I can tell.

I've tried to look for other questions here and using Google, but all of them seem to point to the process being blocked by something else that's visible by sp_who2, but this isn't the case here.

EDIT 2015-12-15: The problem has resurfaced, and this time I was able to run sp_whoisactive as well. It shows my process, but nothing in the wait_info or blocking_session_id columns. The data it returns is the following (sensitive information removed):

dd hh:mm:ss.mss: 00 00:00:29.016
session_id: 85
sql_text: <?query --
insert into MSmerge_cpmv_66969A68BA3C4DFE9CFC3A88E87970FD (publication_number, tablenick, rowguid, partition_id)
            select distinct 1, @tablenick, bt.rowguid, bt.partition_id
            from (  select [BlockedTable].[blocked_table_id], [BlockedTable].[for_replication], [BlockedTable].[rowguid], msp.partition_id from inserted [BlockedTable] , dbo.MSmerge_partition_groups msp where  (msp.publication_number = 1 and ( ((blocked_table_id in (select blocked_table_id from dbo.GetFilteredIDsForUser(msp.[SUSER_NAME_FN] )) and for_replication = 1)) )) ) as bt

--?>
login_name: MyUserName
wait_info: NULL
CPU: 28,813
tempdb_allocations: 0
tempdb_current: 0
blocking_session_id: NULL
reads: 25,507,251
writes: 9
physical_reads: 0
used_memory: 3
status: runnable
open_tran_count: 2
percent_complete: NULL
host_name: MyHostName
database_name: MyDatabaseName
program_name: .Net SqlClient Data Provider
start_time: 2015-12-15 10:44:49.200
login_time: 2015-12-15 10:44:49.183
request_id: 0
collection_time: 2015-12-15 10:45:18.237

It's possible that I haven't understood how to read the output of sp_whoisactive, but I've not found any information on that. The existing answer here (as of 2015-12-15) explains how to gather data and how to fix the blocking, but not how to read the output.

The query shown in the output seems to mean it has trouble inserting the new data in partitions for our merge publication. The publication is set to precompute partitions. Could it be that this is what's blocking it?

What else may be blocking the query in such a way?


texts: How to store several lists and their items

texts:
The app I'm working on revolves around projects. Each project can contain several lists, and each list is made up of several items.  Each list relates to a single project.

Is it better to have one listitem table structured as such: id, list_id, event_id, content or 2 tables; one containing the relationship between project and list, and the other containing only list items and their relationship to a particular list?

Is it better to have just one table of which each row would be a list item, including that item's relationship to a project and the name of the list it belongs to:

list_item(id, project_id, list_name, content, <<other attributes>>)

or, have one list item table for all of the list items in the app and and a separate list table:

list(id, project_id, name, description, <<other attributes>>)
list_item(id, list_id, content, <<other attributes>>)


texts: temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

texts:
I executed some alter statements on my master server, which have been successfully executed on master (now there is no temp table on master) and all alter also have been replicated on slave server successfully. But still I am getting temp table on slave (#sql-7a87_230c32.ibd and #sql-7a87_230c32.frm).
Now I need help to know below points:

When alter has been successfully done then why this temp table still exist and not deleted.

Now how can I delete this temp table and what will be its impact.

May I read this temp file or know by any command that for which table this temp table was created.

Note: If I delete it from its directory path then mysql will throw error message related with it and also I am not aware what will be its impact.
I will be very thankful for any quick help.

2nd Update

1.. When alter has been successfully done then why this temp table still exist and not deleted.

After reading #sql-7a87_230c32.frm whith the help of your post, now it is clear that it was my first alter table (I altered 6 tables one by one).
Below are some facts which will help to know exact reason-

sql-7a87_230c32.frm file creation time is "2014-06-09 01:49:00" and its .ibd file #sql-7a87_230c32.frm time is "2014-06-09 03:34:00" and its size is 3.4 GB while its main file size is 6.8 GB

After checking server logs it came to know that slave was being shutdown at "2014-06-09 03:33:05" (I have to check if it was done by system admin team even they were aware about this db activity or itself) and restarted when alter was executing on slave.

This table creation time on slave is showing as "2014-06-09 04:43:32".

After analyzing above facts it seems first time alter statement on this table was executing near about "2014-06-09 01:49:00" but without copying completely (as temp .ibd table size is less than its original size) it was rolled back when server was gone shutdown near about at "2014-06-09 03:33:05" but system did not remove these temp tables. Now again when slave gone up it was started replication again same point and now altered table successfully, which was completed by "2014-06-09 04:43:32".
But as per my understanding in this case temp tables should be remove along with rolled back but in this case it was not done. Please help me to clear it.


texts: PostgreSQL 8.4 hangs on create table, reindex

texts:
I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem:
When I create a table that references other table like:

    CREATE TABLE "limit" (
        "id" serial NOT NULL PRIMARY KEY,
        "currency_id" integer NOT NULL UNIQUE REFERENCES "currency" ("id")
DEFERRABLE INITIALLY DEFERRED,
        "min_amount" numeric(10, 2) NOT NULL,
        "max_amount" numeric(10, 2) NOT NULL
    )

The thread hangs and blocks all other threads eventually,(but I can still terminate it with kill -INT) though the other table (currency) is rather small like 24 entries. Sources out there say that I should reindex my table but then again, running

REINDEX table currency

hangs forever.
BTW creating the table without index, works like a charm.
What can be the possible explanation and solution to this?


texts: Are same tag values stored multiple times if tag values are not chaging in points, only field values are changing?

texts:
I have a doubt in InfluxDB. When storing multiple points with same tag values and different field values, are tags stored multiple times or just once for each unique combination?

My understanding is tags are stored once and if we get new tag values then they are stored and indexed.


texts: Oracle - Scan listener and multiple databases

texts:
I have a two node RAC database server hosting 8 databases. I have 3 scan listeners running. 2 in node 1 and one in the 2nd node. All the 8 databases are running for different applications.

Are these 3 listeners sufficient to manage the work load for different 8 databases? So only the service names will be different for the databases for the respective applications to talk to right?

Are there any best practices to have more scan listeners run on different ports or or is it recommended that way?


texts: I have to delete over 90 million records, do I remove or maintain the transaction's SQLWARNING?

texts:
SQL Server: mysql  Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1

OS: Slackware 14.1

That's 90 million old radius bank records I want to remove, leaving only those this month. The script would be this:

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL
DELIMITER $$
USE ${DB} $$
DROP PROCEDURE IF EXISTS sp_monthly_table_backup $$
# tm: time column of table, tb: table
CREATE PROCEDURE sp_monthly_table_cleanup(IN tb varchar(30), IN tm varchar(30), OUT msg varchar(70))
        BEGIN

                ######## rollback block in case of errors ##########
                DECLARE EXIT HANDLER FOR 1062
                BEGIN
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | Duplicate keys error encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLEXCEPTION
                BEGIN
                        # GET DIAGNOSTICS NOT SUPPORT IN MYSQL 5.5, ONLY >= MYSQL 5.6
                        # GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
                        # SET msg=CONCAT('FAILED: Routine failed for table ',tb,' | SQL State: ',@sqlstate,' | Errno: ',@errno,' | Msg: ',@text);
                        SET msg = CONCAT('FAILED: Routine failed for table ',tb,' | SQLException encountered ');
                        ROLLBACK;
                END;

                DECLARE EXIT HANDLER FOR SQLWARNING
                        BEGIN
                            SET msg=CONCAT('FAILED: Routine failed for table ',tb,'. SQLWarning encountered');
                            ROLLBACK;
                        END;
                  START TRANSACTION;
                      SET @str4=CONCAT('DELETE FROM ',tb,' WHERE ',tm,' < date_sub(NOW(), INTERVAL 1 MONTH)');
                      PREPARE stmt4 FROM @str4;
                      EXECUTE stmt4;
                      DEALLOCATE PREPARE stmt4;
                      SET msg=CONCAT('SUCCESS: routine successfully executed for table ',tb,' on ',NOW());
                      COMMIT;

       END $$
DELIMITER ;
EOFMYSQL

for t in "${!TABLES[@]}";
do
        echo ${t};
        echo ${TABLES[${t}]};
        mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} <<EOFMYSQL2 >> ${CLEARLOG}
        CALL sp_monthly_table_backup('${t}','${TABLES[${t}]}',@Msg);
        Select @Msg;
EOFMYSQL2

done

END=$(date +%s);
RUNTIME=$((END-START));

echo -e "Script finished on $(date -d @${END}) \n Runtime: ${RUNTIME} seconds" >> ${CLEARLOG};

STARTOPTRADACCT=$(date +%s);

echo -e "Starting optimization of table radacct on $(date -d @${STARTOPTRADACCT})" >> ${CLEARLOG};

mysql --defaults-extra-file=/etc/my.cnf.d/.mylogin.cnf ${DB} --execute "OPTIMIZE TABLE radacct";
ENDOPTRADACCT=$(date +%s);
RUNTIMEOPTRADACCT=$((END-START));

echo -e "Optimization of table radacct finished on $(date -d @${ENDOPTRADACCT}) \n Runtime: ${RUNTIMEOPTRADACCT} seconds" >> ${CLEARLOG};

While checking the result file, I noticed that delete was terminated because of a SQLWarning:

Starting script on Wed Jan  8 15:06:17 -03 2020
 Server timestamp: 1578506777
@Msg
FAILED: Routine failed for table radacct. SQLWarning encountered
Script finished on Wed Jan  8 22:50:03 -03 2020
 Runtime: 27826 seconds
Starting optimization of table radacct on Wed Jan  8 22:50:03 -03 2020
Optimization of table radacct finished on Thu Jan  9 01:22:13 -03 2020
 Runtime: 27826 seconds

Is it important that I keep this rollback to SQLWarning, or can I pull it out and still maintain data integrity?

UPDATE:  I just saw a warning in the database log, shown just as the script started. But did mysql go to all the trouble of deleting it first and then seeing this warning shown at the beginning of it and rolling it back?

200108 15:06:17 [ERROR] mysqld: Table './mysql/proc' is marked as crashed and should be repaired
200108 15:06:17 [Warning] Checking table:   './mysql/proc'


texts: Best way to get current year minus 5 years

texts:
I'm actually have the next query that I use to obtain the current year minus 5 years in january in this format 'YYYYMM'.

select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12*5*-1),'YYYYMM') from dual;

I want to know if this way is the best way to do this.


texts: Computing Matching Data Points from Fuzzy Timestamps in Postgresql

texts:
I have a table containing multiple time series of different types. The timestamps of cohesive points from different series do not match exactly (i. e. the difference can be up to an hour).

Schema

Below is the schema with two example series:

CREATE TABLE series (id integer, series_type integer, charttime timestamp,
                     value integer, PRIMARY KEY (id));
INSERT INTO series VALUES (1, 1, '2018-03-01 12:10:00', 40),
    (2, 1, '2018-03-01 13:25:00', 30), (3, 1, '2018-03-01 14:10:00', 50);
INSERT INTO series VALUES (4, 2, '2018-03-01 11:20:00', 2), (5, 2, '2018-03-01 12:15:00', 6),
    (6, 2, '2018-03-01 13:00:00', 7), (7, 2, '2018-03-01 13:45:00', 1);

id |series_type |charttime           |value |
---|------------|--------------------|------|
1  |1           |2018-03-01 12:10:00 |40    |
2  |1           |2018-03-01 13:25:00 |30    |
3  |1           |2018-03-01 14:10:00 |50    |
4  |2           |2018-03-01 11:20:00 |2     |
5  |2           |2018-03-01 12:15:00 |6     |
7  |2           |2018-03-01 13:45:00 |1     |
6  |2           |2018-03-01 13:00:00 |7     |

Goal

The goal is to select one series together with the closest datapoint from another series. For the example dataset the result should be:

charttime           |s1 |s2 |
--------------------|---|---|
2018-03-01 12:10:00 |40 |6  |
2018-03-01 13:25:00 |30 |1  |
2018-03-01 14:10:00 |50 |1  |

First working approach

My current approach is to select the best matching data point from the other series by a subquery:

SELECT l.charttime, l.value AS s1,
    ( SELECT r.value
      FROM series r
      WHERE ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime ) / 3600 ) < 1
            AND r.series_type = 2
      ORDER BY ABS( EXTRACT( EPOCH FROM l.charttime - r.charttime )) ASC LIMIT 1
    ) AS s2
FROM series l
WHERE l.series_type = 1
ORDER BY l.charttime ASC

This does not seem to be the best approach as the dataset is quite huge and thus performing many subqueries slows down the query.

Second approach

A different idea is to self-join the table and filter for close data timestamps:

SELECT l.charttime, l.value AS s1, r.charttime, r.value AS s2
FROM series l, series r
WHERE abs(EXTRACT(EPOCH FROM l.charttime - r.charttime) / 3600) < 1
      AND l.series_type = 1 AND r.series_type = 2

charttime           |s1 |charttime           |s2 |
--------------------|---|--------------------|---|
2018-03-01 12:10:00 |40 |2018-03-01 11:20:00 |2  |
2018-03-01 12:10:00 |40 |2018-03-01 12:15:00 |6  |
2018-03-01 12:10:00 |40 |2018-03-01 13:00:00 |7  |
2018-03-01 13:25:00 |30 |2018-03-01 13:45:00 |1  |
2018-03-01 13:25:00 |30 |2018-03-01 13:00:00 |7  |
2018-03-01 14:10:00 |50 |2018-03-01 13:45:00 |1  |

The problem then are the duplicate data points. Grouping in the first column does not work as the best matching s2 cannot be selected.

Is there a better approach?


texts: Unable to fully drop a database snapshot

texts:
I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists.

The database however does not have an entry in master.sys.databases, and is not visible in SSMS.

The [database].ss file does exist, and I can even run queries on tables in the database.

I'm not sure how to drop the database fully however, if I try to drop, I get the folowing:

drop database [database_snapshot]

Msg 3701, Level 11, State 11, Line 1
Cannot drop the database '[database_snapshot]', because it does not exist or you do not have permission.

(I know it's not a permissions issue)

Similarly I am unable to create the database:

CREATE DATABASE [database_snapshot] ON
(Name = N'[database]', FILENAME = N'[path to .ss]')
AS SNAPSHOT OF [database]

Msg 1801, Level 16, State 3, Line 2
Database '[database_snapshot]' already exists. Choose a different database name.

If anyone has ideas on how to proceed here, it would be most appreciated. :)


texts: How do I conditionally select elements of an array?

texts:
I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text).

count     array
6         "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292"

The array represents six sets of values i.e., Lower_limit, height and Upper_limit.How? every three elements of array make the set as 1 thus 18 elements / 3 = 6 sets (count). Now, I need to conditionally modify my array i.e., when upper limit and lower limits are coinciding AND if the difference among values is less than or equal to 1 then merge those sets and select the first upper limit and last lower limit and return the most common value (which is 19.3) among the limits. My desired output would be like:

count    array
1        112, 19.3, 292

Could anyone help me to have some pointers towards my desired output?

UPDATE1:

The array is formed from a series of three members/elements i.e., lower limit, value and upper limit. Whenever, these three values appear in array elements it means we have 1 set (count = 1). In the question, there are 18 elements in array >> 18 / 3 = 6 sets (or count = 6). It can be considered as counting the number of elements in array (3 elements make one set i.e., foo, value, bar >> 1 set). I hope things are clear and I may replace "count" by "set" in question if it creates confusion.

Now my requirement was:

Starting from the first set, check if the upper limit of first set coincides with the next AND
if the difference among values (19.3 and 19.3) is less than or equal to 1
If both or any of above condition fulfills, then merge them and get limits of merged ones
Do the same for all sets and get the lower and upper limit of finally merged set. Count the elements  of merged set and update "counter/set"

In the given array, the difference among values is less than or equal to 1 and also starting from 1st set (112, 19.3, 142)to second set (142,19.3, 172), the upper limit of first set coincides with the lower limit of second set means there are next to each other and also the difference between their values is 0 thus they should be merged. In this manner, I needed to have my desired output. Hope things are clear and make sense now.


texts:
I have the following delete query running and it's killing the memory
:

delete from dbo.programselected where id=@p0

This is the execution plan

How can I fix this problem?


texts: Returning a set datatype from MariaDB function

texts:
Given below is the syntax for the MariaDB's CREATE FUNCTION:

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement

The type parameter accepts any valid MariaDB data type. I tried to return a set:

delimiter //
create function get_a_set ()
  returns set
  begin
    RETURN SET('a' , 'b');
  end //

delimiter ;

But I am getting a syntax error in the following line: RETURN SET('a', 'b');

The exact error is given below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'begin
    RETURN SET('a' , 'b');
  end' at line 3

Any help would be greatly appreciated. Thanks!


texts:
I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to notice is that the partitions should be placed in different file groups in different drives. But my server has a single ssd that is partitioned. Is there any advantage in placing the files in individual partitions of the storage device even though physically it is just one. Does partitioning still help if all the file groups are in the same physical drive?

Thanks in advance.


texts:
In Oracle I can do:

SELECT *
 FROM ALL_TAB_STATS_HISTORY;

and figure out last time stats was run for various tables.
Similarly, I can get the stats information columns by doing:

SELECT * FROM ALL_TAB_COL_STATISTICS WHERE OWNER = 'CURAM' ORDER BY TABLE_NAME

What about for index tables?  Does Oracle also store stats for index tables? And how do I check the last time they were run?


texts:
I found this post here back from 2011, and wanted to verify that it's still valid and good advice to take a hot snapshot of an ebs volume (PostgreSQL still running) so long as you're snapping all of the data.

The PostgreSQL documentation indicates that a low level snapshot is fine so long as the whole data directory (WAL and all tables, different tablespaces could cause an issue) are taken in the same snapshot.

I'm currently using PostgreSQL v9.2.


texts: Is data encrypted by ENCRYPTBYPASSPHRASE() protected by the service master key?

texts: In a recent question, What does the Service Master Key protect?, it was suggested that the service master key protects, among other things, data encrypted by ENCRYPTPASSPHRASE(). Is this true?


texts: MySQL Innodb_row_lock_current_waits is 2^64 - 1

texts:
I have a MySQL 5.6 server which we monitor using Nagios.

Over the weekend, it's started complaining about the Innodb_row_lock_current_waits, which currently has a value of 18446744073709551615, yes, that's more than 10^19! As I understand it, this value is the number of current row locks. Basically, I don't believe it, that number is far too large?

As Phil pointed out, the number is 2^64 - 1, so now my question is: Why is MySQL returning this number, it must be an overflow/error code of some sort.


texts: Delete Trigger - Linking to a table with a PK made up of three columns

texts:
I'm currently writing a number of "AFTER INSERT, DELETE" triggers that will be used to sync data between two identical tables, in different instances. The issue that I'm having right now, is getting the DELETE part working, due to how the table has been designed.

The table is called "MobileUsageReportPackageLog" and the PK is made up of the following columns, UserId, DeviceId, PackageNumber.

I'm now unsure how to link this table to the "deleted" table, to ensure that if a record is deleted, then the trigger also deletes the same record in the other table.

I have a feeling that I'm missing something simple, but if anyone has any suggestions or recommendations for a situation like this, I'd love to hear them.

Thanks in advance!

EDIT: I should mention that as a combination, all three column will be unique. As individual columns however, they will contain duplicate data.

Code as of now:

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        DELETE murpl
            FROM [Audits].[dbo].[MobileUsageReportPackageLog] murpl
                JOIN deleted del ON murpl.[UserId] = del.[UserId]
                JOIN deleted del2 ON murpl.DeviceId = del2.[DeviceId]
                JOIN deleted del3 ON murpl.[PackageNumber] = del3.[PackageNumber]


texts: What are the main causes of deadlocks and can they be prevented?

texts:
Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor.

This is the first time I've seen this error and didn't know how to track and fix it effectively.  I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column.

I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.

I felt happy and came back to my project, and did some research to found out the reason for that deadlock...

Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.

I know it isn't a well defined conclusion, that is why I'm posting here...

Is the missing primary key the problem?
Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
How do I prevent and track deadlocks?


texts:
I want to implement database for following application:-

User creates task and assigns to other user who can furthur split it into subtasks and assign it to other users.creaking a tree like structure.

Should i use graph database or what?


texts: Is there anyway to UPDATE a series of columns on a row with a composite type record/row?

texts:
Take a table foo,

CREATE TABLE foo(a,b,c,d)
AS VALUES
  (1,2,3,4);

I can insert into foo with a row type..

INSERT INTO foo
  SELECT (foo).*
  FROM foo;

INSERT INTO foo
  SELECT (v).*
  FROM ( VALUES (42,42,42,42) )
    AS v;

But I can't update foo with a row type.

-- fails
UPDATE foo SET (a,b,c,d) = (
  SELECT ROW(1,2,3,4)
).*;

-- also fails.
UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));
ERROR:  number of columns does not match number of values
LINE 1: UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));

Is there any syntax to update an entire row with a composite type. The really weird thing is that this works in a trigger with NEW, and I'm trying to write the same code without a trigger.

CREATE OR REPLACE FUNCTION foo()
RETURNS trigger
AS $$
  BEGIN
    NEW=(42,42,42,42);
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql
IMMUTABLE;

CREATE TRIGGER ok
  BEFORE UPDATE ON foo
  FOR EACH ROW
  EXECUTE PROCEDURE foo();

-- The NEW row in trigger gets updated with (42,42,42,42)
-- the UPDATE succeeds; all rows (42,42,42,42)
UPDATE foo
  SET (a,b,c,d) = (1,2,3,4);

You can see here that we can update the row object inside the procedure, and change the update. However, we can't do that in the UPDATE ddl? I would expect the RECORD produced by the same name of the table to work the same way.

-- Such as this,
UPDATE foo
  SET foo = (1,2,3,4);


texts: Backup up database to compressed local folder - what will be the performance implication?

texts:
I have sql server 2016 web edition on Windows 2016 server VM.

I want to backup the database, to a folder on a local drive (SSD) - please don't ask why, long story.

Due to the size of the DBs, and comparatively small size of disk, I want to back up to a compressed folder.

What would be the performance implications? Will they be noticeable? (VM with 16 cores, 128GB ram, typically cpu utalization ~ 20%)

Looking for some suggestions based on experience


texts: How can it be possible that sum operation is so much faster than count?

texts:
When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below:

select sum(t.amount * t.exchange) from table t;

Finalize Aggregate  (cost=658027.39..658027.40 rows=1 width=8) (actual time=2391.248..2391.248 rows=1 loops=1)
  Buffers: shared hit=25366 read=550786 dirtied=18 written=18
  I/O Timings: read=1978.454 write=0.205
  ->  Gather  (cost=658027.17..658027.38 rows=2 width=8) (actual time=2391.100..2391.229 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=25366 read=550786 dirtied=18 written=18
        I/O Timings: read=1978.454 write=0.205
        ->  Partial Aggregate  (cost=657027.17..657027.18 rows=1 width=8) (actual time=2377.613..2377.613 rows=1 loops=3)
              Buffers: shared hit=24961 read=550759 dirtied=18 written=18
              I/O Timings: read=1977.930 write=0.205
              ->  Parallel Seq Scan on odeme_kaydi ok  (cost=0.00..622181.24 rows=4646124 width=16) (actual time=0.084..1972.061 rows=3688816 loops=3)
                    Buffers: shared hit=24961 read=550759 dirtied=18 written=18
                    I/O Timings: read=1977.930 write=0.205
Planning time: 0.279 ms
Execution time: 2408.745 ms

select count(t.id) from table t;

Aggregate  (cost=489270.14..489270.15 rows=1 width=8) (actual time=12256.560..12256.560 rows=1 loops=1)
  Buffers: shared hit=6902688 read=372054 dirtied=32
  I/O Timings: read=3067.841
  ->  Index Only Scan using pk_odeme_kaydi_id on odeme_kaydi ok  (cost=0.43..461393.25 rows=11150756 width=8) (actual time=0.169..11583.174 rows=11066478 loops=1)
        Heap Fetches: 4085161
        Buffers: shared hit=6902688 read=372054 dirtied=32
        I/O Timings: read=3067.841
Planning time: 0.110 ms
Execution time: 12256.609 ms

Note: amount field is always greater than 0 in the table.
Postgresql version is 9.5.

Is it normal or is there anything tricky?


texts: MySQL, "columns partitioning" when multiple columns are individiually important

texts:
MySQL 5.5 introduces "columns partitioning".
http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html

I'm trying to better understand how it works when two columns are important individually.

Let's say for a table which holds the messaging between two system users. We would potentially have "sender_id" and "receiver_id" columns, and we might want to query against these columns individually.

If we have separate indexes on both of these columns, we can query them individually when necessary. Results are fast.

But what if our table is 100M rows large, and we consider partitioning. My understanding is multiple column partitioning focuses on the first column in the columns definition, then the second one. Here is a sample table structure:

CREATE TABLE messages (
         message_id INT,
         sender_id INT,
         receiver_id INT
     )
     PARTITION BY RANGE COLUMNS(sender_id,receiver_id) (
         PARTITION p0 VALUES LESS THAN (10,10),
         PARTITION p1 VALUES LESS THAN (20,20),
         PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)

If we query for "WHERE receiver_id=5", partition pruning will not kick in, right? It will need to search all partitions. But if we were to search for "WHERE sender_id=5", then we would immediately know the result is in p0.

So for a table where two columns could potentially be individually important, partitioning might not be the best solution since now we lost the benefit of a full-table index for the secondary column(receiver_id, for this case) in the columns parameter. Is that right?


texts: Tablespace symlink error after changing data_directory location in PSQL 9.4

texts:
Problem: After changing the 'data_directory' location in PSQL 9.4 (Windows Server 2012 R2) an error is thrown to the user when: (a) trying to connect to the PSQL server using PgAdmin or (b) when trying to list the tablespaces \db using the SQL Shell. In both cases the error is the following:

  ERROR: could not read symbolic link "pg_tblspc/53605151": Invalid Argument.

Although the error message persists even after restarting the PSQL server it does not seem to affect the users' ability to ETL using the database.

Background: I followed the instructions suggested at the following link https://stackoverflow.com/questions/22596301/how-to-change-postgresql-data-directory to change the 'data_directory'.

Actions taken: I tried to remove the symbolic link using the following command using my system administrator account.:

rmdir ...\pg_tblspc\53605151

After the symlink is removed the error thrown changes slightly to the following:

  ERROR: could not read symbolic link "pg_tblspc/53605151": No such file or directory

Any insights would be more than welcome on how to fix this.


texts: how to resolve intermittent SQL backup issue?

texts:
We have this issue on aprox 100-300 SQL machines each month and it's never re-occuring. If you re-run the backup it will complete. The problem exists in both win2k8 and win2k12 machines.

Backup failes with the following error in the nsrsqlsv.raw.

  A nonrecoverable I/O error occurred on file
  "Legato#1978fba8-cf4b-4d3b-8cec-bf2316364b27:" 995(failed to retrieve
  text for this error. Reason: 15105)..

I found that the 995 is a ERROR_OPERATION_ABORTED signal sent to Networker/VDI writer but can't figure out what's sending the signal.

When looking into the SQL logs i find entry's for the same time and db.

  08/25/2014
  14:19:05,spid54,Unknown,BackupVirtualDeviceFile::RequestDurableMedia:
  Flush failure on backup device
  'Legato#097d3227-43a8-4b2e-ad0a-5cf35a661b63'. Operating system error
  995(failed to retrieve text for this error. Reason: 15105).

  08/25/2014 14:19:05,spid54,Unknown,Error: 18210 Severity: 16
  State: 1.

  08/25/2014 14:19:05,Backup,Unknown,BackupIoRequest::ReportIoError:
  write failure on backup device
  'Legato#097d3227-43a8-4b2e-ad0a-5cf35a661b63'. Operating system error
  995(failed to retrieve text for this error. Reason: 15105).

  08/25/2014 14:19:05,Backup,Unknown,Error: 18210 Severity: 16
  State: 1.

  08/25/2014 14:19:05,Backup,Unknown,BACKUP failed to complete the
  command BACKUP LOG RP_DBadminOp. Check the backup application log for
  detailed messages.

  08/25/2014 14:19:05,Backup,Unknown,Error: 3041 Severity: 16
  State: 1.

So i can see that there is an issue with the RequestDurableMedia operations and that it fails on doing a flush. But i have no idea on what it mean and what the Flush operation does.
In the file xbsa.messages

  _nwbsa_is_retryable_error: received a retryable network error (Severity 0 Number -13): busy

Is this due to packetloss, latency or other Network related issues?

And please don't ask me for FQDN checks, license or other default troubble shooting. Backup works 5minutes after it fails so there is no DNS failure. We're running a capacity license so can have as many clients i wish to have.

Servers are mostly virtual and having their data stored on EMC VMAX 10K disk over SAN. All physical have the same disk setup.


texts:
I have mysql 8 running on a ubuntu 20.4 server. I want to apply some setting to optimize the database so that it uses less ram for a web application. typically switching of the performance_schema.
Which cnf file I should be adding my settings? When I run the following command I have get the following information:
$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

/etc/my.cnf file doesn't exist
/etc/mysql/my.cnf contains the following:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

~/.my.cnf doesn't exist.
Then I have the following file /etc/mysql/mysql.conf.d/mysqld.cnf
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql
...

I've added the setting to /etc/mysql/mysql.conf.d/mysqld.cnf file but would be helpful if someone can advise which would be the correct file to add such settings?


texts: Is the update in this MERGE Statement redundant?

texts:
I'm trying improve the performance of a complicated stored procedure. Inside the stored procedure is the following MERGE statement. I'm not very familiar with the MERGE syntax.

The [Contact].[PhoneNumber] table looks like this:

CREATE TABLE [Contact].[PhoneNumber]
(
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [PhoneNumber] [nvarchar](255) NOT NULL
    [InsertedAtDateTimeUTC] [datetime2](7) NOT NULL DEFAULT(sysutcdatetime())
)

The merge looks like this:

;WITH _ContactPhoneNumbers ([PhoneNumber]) AS
(
    SELECT DISTINCT ([#ChannelData].[CallerAni])
    FROM [#ChannelData]
)
MERGE [Contact].[PhoneNumber] WITH (HOLDLOCK) _target
USING [_ContactPhoneNumbers] _source ON [_target].[PhoneNumber] = [_source].[PhoneNumber]

WHEN NOT MATCHED THEN
    INSERT ([PhoneNumber])
    VALUES ([PhoneNumber])

WHEN MATCHED THEN
    UPDATE
        SET [_target].[PhoneNumber] = [_source].[PhoneNumber]
        OUTPUT INSERTED.[Id], [_source].[PhoneNumber] INTO #Contact_PhoneNumber_Output ([Id], [PhoneNumber]);

I read this as:

If the PhoneNumber does not exist in the [Contact].[PhoneNumber] table the merge inserts a new row into the [Contact].[PhoneNumber] and returns the new Id. If PhoneNumber does exist then the merge updates the PhoneNumber to be the same value, and returns the Id for the existing row.

Is the UPDATE redundant?

If it's redundant I'll take it out and see if it increases the performance. There are several MERGE statements in the stored procedure which follows the same pattern (with the UPDATE) so it could be a useful gain.

The Id is returned even if it exists, as it's used as an FK in a further insert.


texts: Can Set-DbaAgReplica filter any SqlInstances out

texts:
I have been working with an Availability Group with an extra asyncronous secondary, that is always set to manual failover.

I am worried that if I ran:

Get-DbaAgReplica -SqlInstance x -AvailablityGroup y | Set-DbaAgReplica -FailOver Automatic

it would then set all the sqlinstances to auto failover, I was wondering if you can ignore an instance?


texts: UPSERT or INCREMENTAL update of table but with DELETE

texts:
I have a following problem.

We have two systems, an OLTP system and our Data Warehouse, both on PostgreSQL. I need to sync one table from OLTP system with DWH, so they're identical. I am using Foreign Data Wrapper for comunication between databases, so then sync could be straightforward and look like that:

TRUNCATE TABLE target_table;
INSERT INTO target_table SELECT * FROM source_table;

Unfortunately, the table is huge for the hardware we use (hundreds of millions of rows, and about 50GB of data), so this is massively time consuming.

I have then figured out a way to do it faster by doing incremental upsert, based on following approach.

Identify new rows in source table by taking rows with insert_time > SELECT max(insert_time) FROM target_table;
Identify rows, that need to be updated, by joining both tables on id and source_table.update_time > target_table.update_time.

That worked absolutely fine, however, a new problem arised - rows from source table can be removed, therefore I need to remove corresponding rows from target table. All approaches I tried are way to slow, like this one for example:

CREATE VIEW deleted_rows AS SELECT
target.row_id
FROM target
LEFT JOIN source USING(id)
WHERE source.id IS NULL;

Any idea how to solve this problem smartly and quickly? Thanks in advance for your responses.


texts: Where can I download an old version of PostgreSQL for Windows?

texts:
I need version 8.4 for a legacy system, but EnterpriseDB (the official link) only shows 9.x

On the PostgreSQL FTP Mirror I see the 8.4.22 binary, but only have binary for solaris:


texts: Best practices for service name / aliases in tnsnames

texts:
I have this idea that service names / aliases given to connections descriptors in a tnsnames.ora (not the one in the server that dblinks use, but the one for the public) file should in no way be related to the SID and much less the name of the server.

Suposse the name of the sever is "myserver" and the SID of the instance is "myinstance".

I think giving the alias "myinstance-at-myserver" to the connection string is not a good idea because you are somehow coupling something logical to something physical.

Am I right or wrong and why ?

Is this the best practice?:

# one server-instance-named descriptor
MYINSTANCE-AT-MYSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

Or is this ?

# several business-named descriptors pointing to the same listener
RRHH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

FINANCE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )

SALES =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myinstance)
    )
  )


texts:
I am using WordPress to run my website, along with the traditional MySQL Databases. Whilst seeking to speed up my website, I decided to clear some of the old and unused tables with WP-Optimizer. Sadly, it seems that I have deleted something I shouldn't by mistake, since it seems I have partially broken Elementor Plugin.
I luckily have a database backup, but it is a few days old and, given there are a lot of modifications done on a single day, I do not wish to simply import that backup; what I'd like, is to compare my website's database with my backup and restore the missing tables only, after thorough examination of their content.
I am no SQL expert at all and looking for guidance.


texts: Why does Oracle indicate my table is using so little space?

texts:
I'm running Oracle 11g.  I have a table with two BLOB columns.  I inserted 10,000 rows into the table, and each column contained a byte array of size 10289.

As you can see, there are 10,000 rows in the table and each column has 10,289 bytes in it:

SQL> select count(*) from MYUSER.MYTABLE;

  COUNT(*)
----------
     10000

SQL> select min(length(COL1)), min(length(COL2)) from MYUSER.MYTABLE;

MIN(LENGTH(COL1))      MIN(LENGTH(COL2))
-----------------      -----------------
            10289                  10289

Therefore the table should be using up at least 205MB of space (10289 bytes * 2 * 10000).

But I used the query given here to find out how much space the table is using, and I got the following:

SQL> SELECT SUM(bytes), SUM(bytes)/1024/1024 MB FROM dba_extents WHERE owner = 'MYUSER' AND segment_name = 'MYTABLE';

SUM(BYTES)         MB
---------- ----------
   2097152          2

Why does it say the table is using up just 2 MB when it should be using up 250 MB?

The byte arrays I'm inserting are compressed data, so Oracle couldn't possibly be compressing that data any further.


texts: Job is Storing History But 'Include Step Output in History' is not Checked

texts:
I have a SQL Server Agent Job. The 'Include step output in history' checkbox is unchecked. However, when I right-click oon the job and select 'View History', I can see that it is tracking step output.

There is just the one step on the job.

Why is this? Am I missing some setting?


texts: Calculating a running balance to store with transactions

texts:
I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements.

Balance
| id | member_id | product_id | balance |
|  1 |    100001 |         33 |  975.00 |
|  1 |    100002 |         15 |   10.43 |
|  1 |    100001 |         15 |    0.00 |

Transactions
| id | member_id | product_id |       date |    value | interest |
|  1 |    100001 |         33 | 2014-09-01 |  -100.00 |     0.00 |
|  2 |    100001 |         33 | 2014-08-20 |   600.00 |     0.00 |
|  1 |    100002 |         15 | 2014-06-01 |   -50.00 |     0.00 |
|  2 |    100001 |         15 | 2014-08-20 |  -600.00 |     0.00 |

This data is read-only, and due to external issues with the legacy system the data is coming from the tables are emptied and everything re-inserted every time the data is updated. Not ideal, and the legacy system should be replaced within 18 months.

The transaction data I import doesn't have a running balance column. I need this, and have come up with two options:

Do not store a calculated balance against each transaction, but calculate on-the-fly. This makes paginating for account statements a pain, as no matter how far in the past we have to start with the latest balance and work back through all the transaction history. And I know no fast way to do this recursive query.
Calculate a running balance when transactions are imported, and store this against each row.

The second is my preferred option (for ease when extracting "March 2009's statement"). I am stuck trying to devise an efficient way to update the running balance: the best I have is code selecting all transactions for a particular user's account, then looping over them updating the figures. This is not performant.

How would you deal with this? Mine has to work on MySQL but I am also interested in seeing more elegant solutions than it supports.


texts:
SQL server Read scaling on Amazon AWS

Let me know if this is a good idea.

We create a small server that essentially is running all the time.  This is the one to which we would be pushing data. (Master)
Create an AMI that acts as a mirror or Replica from the master.  (secondary)
Create an Elastic Load balancer  with the primary server running behind it all the time.
Create an auto scale group which launches secondary servers when cpu load increases to 50% and kills when cpu drops to 10%
On-demand secondary servers are launched, syncs to primary, gets diff and is ready for answering queries.

Questions :

a) Can ELB be used to load balance queries for SQL server ?
b) What kind of replication should I use  transactional / merge ?
c) I am looking into doing this essentially for read scaling ?

Thanks


texts: Is there any way to trace optimizer's work in MySQL?

texts: Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information like, which plans the optimizer considers, the cost of each plan?


texts: Passing info on who deleted record onto a Delete trigger

texts:
In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic.

I can track Inserts/Updates by including in the Insert/Update the field "UpdatedBy". This allows the INSERT/UPDATE trigger to have access to the field "UpdatedBy" via inserted.UpdatedBy. However, with the Delete trigger no data is inserted/updated. Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Here is an Insert/Update trigger

ALTER TRIGGER [dbo].[trg_MyTable_InsertUpdate]
ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS

INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges)
VALUES (inserted.ID, inserted.LastUpdatedBy)
FROM inserted

Using SQL Server 2012


texts: Buffer pool above 90% utilized causes MySQL to crash

texts:
Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner?

EDIT

I'm using Rolando's script to identify how full the innodb_buffer_pool is.

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Thanks


texts: Get ID which has two rows with specific values for one column

texts:
I have this Table

and would like to select only those IDs that I have as type A B only
The output table should be this table:

Might you be able to share an idea on how to approach this?


texts: SQL Server self recursive horizontal join

texts:
I need to generate a query which provides hierarchical data in a horizontal way. The levels of depth are variable, it is ok if there are many empty fields. The only rows that should exist should be just children. Here's an example of what I need:

ID   |Value  |ID_Parent
-----|-------|---------
1    |NULL   |NULL
2    |NULL   |1
3    |30     |1
4    |NULL   |2
5    |20     |4
6    |10     |NULL

So the output to this would be something like:

ID   |Value  |Ancestor_1 |Ancestor_2 |Ancestor_3
-----|-------|-----------|-----------|----------
3    |30     |1          |NULL       |NULL
5    |20     |1          |2          |4
6    |10     |NULL       |NULL       |NULL

It is needed that the ancestors are labeled 1-to-n as farest-to-nearest.
A tree view of the data would be this one:

1->/2->4->5
   \3
6

I've found a solution for fixed levels but I was wondering if this can be done in SQL Server.


texts: Master-Slave Database. How to activate a secondary database whenever the primary database is down?

texts:
I need some idea on how to accomplish a master-slave database in MySQL. It's like, there's a primary server which the system connects then when it is down, it will use its local server. Here's the scenario:

I'm doing my thesis as a requirement for my course, I'm currently developing a web-based POS which will be installed for two branches, the system will be connected to the main office as the server is located there. What my client wants is to have a secondary database which will be used whenever the internet connection is down, that secondary database is installed in the computer in the branch. Well ofcourse, the primary database is in the main office, and it needs at internet connection for the POS and the primary database to work together.

Then when the internet connection works again, the primary database will be updated from the latest records in secondary database.


texts: Postgres query plan of a function invocation written in plpgsql

texts:
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed inside a user defined function (UDF) using EXPLAIN. So how do I get hold of the query plan for a particular invocation of a UDF? I see the UDF abstracted away into a single operation F() in pgadmin.

I have looked at documentation but I couldn't find anything.

Currently I'm pulling out the statements and running them manually. But this isn't going to cut it for large queries.

For example, consider the UDF below. This UDF, even though it has the ability to print out its query string, will not work with a copy-paste as it has a local created temporary table, which does not exist when you paste and execute it.

CREATE OR REPLACE FUNCTION get_paginated_search_results(
    forum_id_ INTEGER,
    query_    CHARACTER VARYING,
    from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
    join_string CHARACTER VARYING := ' ';
    from_where_date CHARACTER VARYING := ' ';
    to_where_date CHARACTER VARYING := ' ';
    query_string_ CHARACTER VARYING := ' ';
BEGIN
    IF NOT from_date_ IS NULL THEN
        from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
    END IF;

    IF NOT to_date_ IS NULL THEN
        to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
    END IF;

    CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;

    if in_categories_ != '{}' THEN
        join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
        ' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
    END IF;

    query_string_ := '
    SELECT index,posted_at,post_text,name,join_date,quotes
    FROM forum_posts fp
    INNER JOIN forum_user fu ON
    fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
        join_string
    ||
    'WHERE fu.forum_id = ' || forum_id_ || ' AND
    to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' ||
        from_where_date ||
        to_where_date
    ||';';

    RAISE NOTICE '%', query_string_ ;

    RETURN QUERY
    EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;


texts: reduce number of innodb pages read by a query

texts:
In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of these queries. For that I compared the status variable innodb_pages_read before and after the execution of a query. In average 230 pages are read to fulfill a query that will return 200 rows.

The question is: is it normal that each query must read so much pages ? I have another similar table (but smaller with only 300 millions rows) and it takes on average only 30 pages read to fulfill a query that returns 200 rows. How could I reduce the number of pages read (and thus I/O) ?


texts: Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

texts:
I have a table A witht the following characteristics:

multiple indexes
constraints (null, unique, check, foreign key)
2 triggers (before update)
primary key

If I were to redefine the table with dbms_redefintion in order to change storage, do I have to recreate the triggers, constraints and trigger pointing to the interim table or does this go automatically with only having to rename them once is it is redefined?


texts: Is there anyway to search PL/SQL reserved words with contains function?

texts:
Is there anyway to search PL/SQL reserved words with the contains function in the Oracle?

We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions from the user table in Oracle but the results only show for "test" and ignore those reserved words such as "do" or "for" automatically.

We assume we have a table called content in the users schema.

The queries are listed below:

create table users.content (content_id int not null, content varchar(20) not null);

insert into users.content values (1, 'do something');

insert into users.content values (2, 'test it');

insert into users.content values (3, 'it has been tested');

insert into users.content values (4, 'will test it');

create index users.idx_content on users.content (content) indextype is ctxsys.context;

--drop index users.idx_content;

--drop table users.content;

select content_id, content from users.content where contains(content, 'do or test') > 0;


texts: Revert to using local account for SQL Server services

texts:
We have changed the login/startup account of our SQL Server service, to use a domain service account. However, we are experiencing issues with this account, and need to revert back to using the local NT Service\MSSQL$[namedinstance] account for now.

Is there a way of doing this? When prompted for the password in SQL Server Configuration Manager, what does one enter? We never set a password for this previously, but it appears to require one.

  The account name is invalid or does not exist, or the password is invalid for the account name specified. [0x80070421]


texts: SQL Server Active Directory login groups with limitation

texts:
I'm DBA in my organization and use SQL Server 2012 and 2014. I requestd to define multiple group in active directory such as Developer, DBA, supporter and ets... and add my users to this groups. after this a create login from this groups and grant access to DBA, Developer and supporter groups. Change in my organization chart is above, and each time one personnel changed his position, I request to change his group in Active Directory.

My problem: The Admin of Active Directory can add his user or any user to each of this groups and get access to my database without that I get it.

I want to use this groups for grant permission and looked up for a way for solve my problem.

Thanks in advance.


texts:
Currently, I synchronize the standby database from primary database by copying archive logs from primary server to the secondary server. I do this activity daily. I have heard that there are automated process for this are available.

But the problem is that, the primary data gets corrupted, then the same will be replicated to the secondary standby database. This will make the standby database of no use.

Is there any way of automated process so that only the data to be synchronized to the standby database which is not corrupted in the primary database ?

Thanks


texts: Are "column names" optimized in PostgreSQL JSONB jsonb_agg(*)?

texts:
Using this SELECT:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t;

If some_table had 1000 rows, are the "column names", which are repeated for each row in "content", sent over the network 1000 times? Or is that somehow optimized away? If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.


texts: Only allowing selected values of different linked tables be selected. MySQL Trigger

texts:
I have 3 tables.
I have an instructors table  and facilities table.

They are linked by facility.facility_id=instructors.facility_id (i.e. A facility can have many instructors but an instructor can only teach in one facility.)

The third table is bookings. They are linked by instructor.instrcutor_id= bookings.instructor_id ( An instructor can have many bookings but a booking can only have one instructor) AND facility.facility_id=bookings.facility_id ( A facility can have many bookings but a booking can only be for one facility)

I want a trigger that when when a facility_id is selected in bookings that only the linked instructors will be able to be selected. (throw up error message if needed)

This is my attempt so far----

DELIMITER
 //

    CREATE TRIGGER linked_instructor_to_facility
        BEFORE INSERT ON bookings
        FOR EACH ROW
            BEGIN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can only book an instructor that is linked to that facility';
            WHERE facility.facility_id <> instructors.facility_id;
    END

//
DELIMITER ;


texts: Performance issues after moving a database to SQL Server 2014

texts:
I've received a database that was previously on SQL Server 2008R2 but was just put on a SQL Server 2014 instance. There were no maintenance tasks run of any kind run on the database since 2014 (e.g. Rebuilding of indexes, updating statistics, etc.).

Once we ran update statistics as part of our regularly scheduled maintenance that we do on a set schedule, the performance of some queries has taken a massive hit to the point where some select statements will seem to never finish.

The queries have some CASE...WHEN statements in them, but I wouldn't expect there to be such a performance hit. Does anybody have any thoughts on what might cause such issues?

I've tried updating the compatibility level to 120 since it was on 100 when the database first came in but, that didn't make any difference on the performance.

Inculding links to executuion plans:

Slow: https://www.brentozar.com/pastetheplan/?id=Byf1Manvg

Fast: https://www.brentozar.com/pastetheplan/?id=SkJB762De


texts: Select Statement with RegEx Replace - DB2 9.7

texts:
In a db2 9.7 select statement's field, I need to perform a global regular expression that replaces multiple white spaces (between words) with exactly one space. The regular expression would be base on something like this:

\s{2,}

The query (without thinning out redundant white space) looks like this:

select
  random_degrees_of_redundant_white-space_between_words as fieldName
from tableName

The contents of a result looks like this:

I like  to   put    varying      numbers      of    whitespaces

     between

   words.

I've seen examples of people claiming to achieve this in DB2 9.7 via xmlquery and fn:replace, but I can't seem to accomplish this myself by looking at their examples.

Would someone be kind enough to convert my abstract example into something I can use as a model for achieving this in db2 9.7?

Please do not provide answers that achieve this via custom functions or stored procedures. I want to achieve this using an independent select statement on a default installation of DB2 9.7 (without installing any addons to db2).


texts: RDBMS: Proper way to store data - comma separated variables or in different fields or tables?

texts:
Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number.

Right now the lineItem column is just a long string of data separated by commas (original file appears to have been just an Excel file). This information is parsed in a PHP script for viewing on the front end.

The table looks like this:

|----------|----------|----------|
|lineItem  |receiptID |customerID|
|----------|----------|----------|
|CD, DVD,  |001       |User01    |
|----------|----------|----------|
|CD, CD,   |002       |User02    |
|DVD, usb, |          |          |
|----------|----------|----------|

Ultimately, is this bad practice? Should the lineItem values be linked to related values in another table instead maybe?


texts:
I am using mysql with php Yii framework.When doing data modelling I came accross some doubt.I have a table for user details like this.

CREATE TABLE IF NOT EXISTS `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(80) NOT NULL DEFAULT '',
  `lastname` varchar(80) NOT NULL DEFAULT '',
  `gender` varchar(6) NOT NULL DEFAULT '',
  `email` varchar(45) NOT NULL DEFAULT '',
  `company_name` varchar(80) NOT NULL DEFAULT '',
  `contact_no` varchar(45) NOT NULL DEFAULT '',
  `address` varchar(120) NOT NULL DEFAULT '',
  `state` varchar(45) NOT NULL DEFAULT '',
  `country` varchar(45) NOT NULL DEFAULT '',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=98 ;

Here my problem is I want the state,country should be like this so that I can use the fields like state/city

 `state/city` varchar(45) NOT NULL DEFAULT '',
  `state/province` varchar(45) NOT NULL DEFAULT '',

So is it safe to use like this or I have to use only one option there?Any help and suggestions will be highly appriciable.


texts: T-SQL or PowerShell to move bak files older than 2 days to another location

texts:
I'm using SQL Server 2005. One of my backup drives is filling up, so I want to move backups older than 2 days.

I was thinking of creating a job to run everyday, but got stuck on the scripts moving the .bak files.

Do you know a script to move files from drive A and move it to a shared location? My .bak files doesn't have a timestamp.


texts: Upgrade SQL Server 2008 R2 to SQL Server 2012 and then set up data replication to SQL Server 2016

texts:
Currently I have a Windows 2008 server with SQL Server 2008 R2 and I want set up a data replication to a Windows 2016 Server with SQL Server 2016.

I am aware that the aforementioned is not possible because replication is only supported if each SQL Server endpoint is within two major versions of the current version of SQL Server.

If I upgrade SQL Server 2008 R2 to SQL Server 2012 on the Windows 2008 server then will I be able to set up replication to the Windows 2016 Server with SQL Server 2016?

Please help me on this.


texts:
Is there a way to view the body of a MySQL event? The SQL part after the DO section? I can easily rename, enable, disable, even alter the schedule and the body separately, but exactly viewing what my event is doing, seems arduous to achieve.

The closest thing I've found was using the recreate command SHOW CREATE EVENT event_name, like explained here on the official MySQL site.

Is there another command or trick to retrieve the body of a MySQL event?


texts: Convert a list into one row with dynamic column in MySQL

texts:
I have a list of row to be converted into a dynamic column that depends on the record in one particular column.

From this statement

FID  |Steps | Action
---- |------|--------
1416 | 1    | Sit
1416 | 2    | Run
1416 | 3    | Jump
1416 | 4    | Sit
1416 | 5    | Run
1416 | 6    | Skip
1416 | 7    | Hit
1416 | 8    | Run
1432 | 1    | Hit
1432 | 2    | Guard

Into :

FID | 1   | 2     | 3    | 4    | 5   | 6    | 7   | 8    |
----|-----|-------|------|------|-----|------|-----|------|
1416| Sit | Run   | Jump | Sit  | Run | Skip | Hit | Run  |
1432| Hit | Guard | null | null | null| null | null| null |

How do I do this?
Please help. Thanks!


texts: How to preserve the uniqueness of a huge data type in MySQL?

texts:
I am in a project where I am storing phishing emails and I extract information of them such as the domains, the links, the IP addresses, the email addresses, etc.
I want to link all the emails that have the same links, or the same email addresses, etc.
So I have a double problem, the first is how to preserve the uniqueness of the links or even, the emails?
I thought to make a hash, like an md5 (Or whatever, I don't care) to have a smaller index, maybe an index in a char (40), that allows me to find an element very fast, but is it the best solution?
My second problem, but maybe it is better in another question, is how to find all the emails that have an element, like an email address. Of course, I will have foreign keys for each element type with the email. For example emailId-emailAddressId and PK(emailId,emailAddressId) and all the Id's will be bigint.
What do you think about this approach?


texts: Getting data from 2 tables by JOIN, when @Foreign Keys exist to the same Table/Object

texts:
I have 2 Tables:

User

id | name | email  | staff
1    name1  email1   true
2    name2  email2   no
3    name3  email3   true
4    name4  email4   no

Note

id | text | sender_id | receiver_id
1    text1    1            2
2    text2    1            3
3    text3    1            2
4    text4    3            2

Note has 2 Foreign Keys to the User, sender_id and receiver_id.

I want to get in a Select(one if possible) the data (name, email, staff) of both the sender and the receiver/.

I tried :

  SELECT N.text, U.name, U.email from Note as N
    LEFT JOIN User as U on Note.sender_id = U.id where U.staff is True ...

but this way I'm getting the sender only.


texts: Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() - Two part name needed?

texts:
In Visual Studio, when trying to create a DAC which has a schemabound view which contains hierarchyid::GetRoot() this error is generated and the project does not build.  However, the t-sql is ok as if I create the view directly in the database it works ok.

Is there a two part name for hierarchyid to solve this?

In a DAC project is VS:

CREATE VIEW [dbo].[View1]
with schemabinding
    AS SELECT hierarchyid::GetRoot() as t

Gives:

Error   93  SQL70561: Cannot schema bind view '[dbo].[View1]' because name 'hierarchyid' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


texts: Scripting drop/create of dependencies when altering view with schemabinding

texts:
In SQL Server 2017 I have a view, "ParentView" which is referenced by other views "ChildView1", "ChildView2", etc.  Those views are referenced by other views "GrandChildView1", "GrandChildView2", etc. and so on and so forth.  I make sure to setup all of my views to have "WITH SCHEMABINDING".  When I want to make a change to "ParentView" I'm required to drop and create all of the dependent views.  This, of course, makes perfect sense because I have "WITH SCHEMABINDING" on and I could be making a change that could break one of the dependent views.  However, doing all of this is tedious through the user interface so I was wondering...

How can I get a script that takes a view or any object as input and creates a drop/create statement based on dependencies?

For example:

DROP VIEW [dbo].[GrandChild]
GO
DROP VIEW [dbo].[Child]
GO
ALTER VIEW [dbo].[Parent]
...
GO
CREATE VIEW [dbo].[Child]
...
GO
CREATE VIEW [dbo].[GrandChild]
...
GO

Notes

This doesn't have to be specific to views.  I also have dependent functions and stored procedures as well.
None of my views are indexed views.


texts:
I am trying to figure out a way to set slave_exec_mode to idempotent on Amazon RDS.

I've checked the parameters associated with created DB instance on Amazon Console, but this parameter is not exit with other ones.

Is there a way to set it through command-line?


texts: PostgreSQL duplicate key violates unique constraint error

texts:
CentOS 6.2 64bit Server
PostgreSQL 8.4.9 installed via yum

I am seeing errors from time to time in PostgreSQL's logs about a table called events.  The events table is used to stores information generated by triggers in Zabbix everytime a specific event occurs on a server that is being monitored.

Zabbix is the only application using the database server.

Here is the error:

INSTRUCTION :  insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (6393428,2,3,38,1353354000,0,1,0)
ERROR:  duplicate key violates unique constraint « events_pkey »

There is no sequence associated with the table events

Here is the table structure for events

                               Table « public.events »
    Colonne    |  Type   |          Modificateurs           | Stockage | Description
---------------+---------+----------------------------------+----------+-------------
 eventid       | bigint  | non NULL                         | plain    |
 source        | integer | non NULL Par défaut, 0           | plain    |
 object        | integer | non NULL Par défaut, 0           | plain    |
 objectid      | bigint  | non NULL Par défaut, (0)::bigint | plain    |
 clock         | integer | non NULL Par défaut, 0           | plain    |
 value         | integer | non NULL Par défaut, 0           | plain    |
 acknowledged  | integer | non NULL Par défaut, 0           | plain    |
 ns            | integer | non NULL Par défaut, 0           | plain    |
 value_changed | integer | non NULL Par défaut, 0           | plain    |
Index :
    "events_pkey" PRIMARY KEY, btree (eventid)
    "events_1" btree (object, objectid, eventid)
    "events_2" btree (clock)
Référencé par :
    TABLE "acknowledges" CONSTRAINT "c_acknowledges_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
    TABLE "alerts" CONSTRAINT "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE

Question

What steps can I take to resolve this error?

Thank you

Updated 11/26

I tried to run the ALTER TABLE ALTER COLUMN statement that dezso sent me, but I get an error about the bigserial type.

zabbix=# ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;
ERREUR:  le type « bigserial » n'existe pas

I can however create a new table with that data type

zabbix=# \connect postgres
psql (8.4.13)
Vous êtes maintenant connecté à la base de données « postgres ».
postgres=# CREATE TABLE test(x bigserial);
NOTICE:  CREATE TABLE créera des séquences implicites « test_x_seq » pour la colonne serial « test.x »
CREATE TABLE
postgres=# \ds
              Liste des relations
 Schéma |    Nom     |   Type   | Propriétaire
--------+------------+----------+--------------
 public | test_x_seq | séquence | postgres
(1 ligne)

How should I go about changing eventid to bigserial?

Update 11/27

I have tried another method to add the sequence to the column eventid

create sequence eventid_seq;
alter table events alter column eventid set default nextval('eventid_seq');
select (max(eventid) +1) as nexteventid from events;
SELECT setval('eventid_seq', 6645754);

That worked, but when I turned Zabbix back on, the eventid value continued to increase but the sequence didn't.  What am I doing incorrectly?


texts:
I have Login Audits for both failed and successful. How do i view the actual logs? Where are they located?

https://docs.microsoft.com/en-us/sql/ssms/configure-login-auditing-sql-server-management-studio

Is there a way to ignore service accounts?


texts:
MySQL Server crashed every hours and this logs submited.

MySQL Version : 5.6 + cPanel

my.cnf :

Ram of SERVER : 128GB
Cpu : 2x 8core with thareds : 32Core
Hard Disk : SSD Pro

Logs :

http://paste.ubuntu.com/12646771/

How to solve the problem now?!

My.cnf :

http://paste.ubuntu.com/12646776/

I set a cron every hours with MySQL -e 'FLUSH QUERY CACHE;' in the crontab server. Because the size query cache full and all websites slowed, after flush all website working fast and fine.


texts: import .db files/tables into sqlite database

texts:
I have a zip file containing several .db files (~ 30 files). Each of these .db files correspond to a table, e.g., the "fuel_transaction.db" file contains a "fuel_transaction" table, "payment_transaction.db" contains "payment_transaction" table and so on.

I am very new to SQLite and whatever I've found is about importing CSV or sql files.. I was wondering if there is any way to import all these tables, i.e., all .db files (all at once) into a database in SQLite? Should I first create an empty database in sqlite and then import each file separately into this new database?

Thanks,


texts:
There is a table users with autoincrement primary key id.

There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not autoincrement.

employees.userid should be a foreign key for users.id.

Which kind of relationship in Workbench it should be?

As far as I understand:

It cannot be 1:1 relationships because for a given user the corresponding employee may not exist.
It is not advisable to be 1:n relationship because n can be only 0 or 1.
It should be an identifying relation because a user is determined by an employee.

Please advise me what is the best relationship in this case.


texts:
I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column.

Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not set when inserting new rows to the table. It is calculated afterward.

I calculate the "duration" of a row R as the interval (in seconds) between R.ReqTime and N.ReqTime where N is the next row (first row, inserted later the current row).

Thus for each row R (except of the last inserted row, as for it N is undefined) we have Duration value.

See pseudocode for updating the table with the correct Duration value (where R is the current row and N is the next (inserted later) row):

UPDATE Visits SET R.Duration=TIMEDIFF(N.ReqTime, R.ReqTime) WHERE R.Duration IS NULL

Should I use cursors to solve this problem? Or are MIN/MAX/ORDER BY fine?

I am not yet comfortable with cursors.

MySQL 5.