texts: Table 1 and Table 2 have matching columns. How do I join the 9th column from table 2 to table 1 based on the matches?

texts:
My Issue:

I want to know if an account has an active error.

Background Information:

I have 2 tables:
    (1) Work - 9 Columns;
    (2) Errors - 9 Columns

The Work table contains Account Numbers and 8 variables which are associated to an error from the Errors table.

The Errors table contains each possible error. Each error has 8 variables and is either set as on (y) or off (n) with an Active Flag column.

    In table 1 (Work) I have 9 columns:
    (i) acct_number
    (ii) system
    (iii) source
    (iv) object
    (v) group
    (vi) code
    (vii) detail
    (viii) routing
    (ix) complexity

    In Table 2 (Errors) I have 9 Columns:
    (i) active_flag
    (ii) system
    (iii) source
    (iv) object
    (v) group
    (vi) code
    (vii) detail
    (viii) routing
    (ix) complexity

Therefore both tables have the same columns except that the Error table does not have the acct_number column and the Work table does not have the active_flag column.

I am attempting to match (join) the active_flag column from the Error table to the Work table based on a match from the other 8 columns.

The result I am looking would look like this: acct_number, active_flag (where each account number is showing whether or not the error associated is active or inactive).


texts:
I'm trying to add this as one of my columns:

REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '
')| CHAR(10)| '
')| '"'| 'in')+'Location: '+IF(dbo.new_truck.ditl_Inventory=True,'Vancouver','Edmonton')

But after executing the SELECT, SQL Server changes it to this:
REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '
')| CHAR(10)| '
')| '"'| 'in') + 'Location: ' + 'IF(dbo.new_truck.ditl_Inventory=True,[Edmonton],[Vancouver])'

It appears that I cannot concatenate a function with a string, with another function. If I take everything after "Location:" out, of course, it works. But without the location :)

Any other way to accomplish this? I could write this into the PHP code that handles the output of this query, but thought I'd try this first

Thanks for your help!


texts: Creating Database Mail with T-SQL returns "principal name is not valid"

texts:
Configuring Database Mail is so long.

So I want to create a script that automatically create it for me

DECLARE @YourEmail NVARCHAR(50) SET @YourEmail = 'MyEmail@mydomain.com' --Put here your E-mail
DECLARE @YourPassword NVARCHAR(50) SET @YourPassword = 'MyPassword' --Put here your E-mail
DECLARE @YourSMTPSserver NVARCHAR(50) SET @YourSMTPSserver = 'MySMTPserver' --Put here your SMTP Server
DECLARE @YourPort int SET @YourPort = '25' --Put here your SMTP port

-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL_Spring_Cleaning_Account',
    @description = 'Mail account for SQL_Spring_Cleaning.',
    @email_address = @YourEmail,
    @replyto_address = @YourEmail,
    @display_name = 'SQL_Spring_Cleaning Mailer',
    @mailserver_name = @YourSMTPSserver,
    @port = @YourPort,
    @use_default_credentials = 0,
    @username = @YourEmail,
    @password = @YourPassword;

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @description = 'Profile used for SQL_Spring_Cleaning mail.' ;

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @account_name = 'SQL_Spring_Cleaning_Account',
    @sequence_number =1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'SQL_Spring_Cleaning_Profile',
    @principal_name = 'ASPIRES3\franc',
    @is_default = 0 ;

But unfortunately SSMS returns me:

Msg 14607, Level 16, State 1, Procedure sysmail_verify_principal_sp, Line 49 [Batch Start Line 0]
principal name is not valid

The error is obviously on line

@principal_name = 'ASPIRES3\franc',

but I don't understand what is wrong with that


texts: Is it possible to custom parse word in MySQL 5.7.22

texts:
Now I am using MySQL 5.7.22 to fulltext search, this is my fulltext create command:
CREATE FULLTEXT INDEX ft_index_picker ON report_envelope_app_list(picker_user_info) WITH PARSER ngram;

and using this command to search:
select *
from report_envelope_app_list
where match(picker_user_info) against('猫游xsyi')
limit 10

Now I store my data like this:
62839,猫游yMiW,猫游xszO,62841,猫游VHFi,猫游UnVb,62846,猫游fyZx,猫游3Hrh,62850,猫游yMmU,猫游VP8N

Is it possible to customise the fulltext search words like: 62839 猫游yMiW so the db could match using 猫游yMiW as a word or strict match by word 猫游yMiW? I am trying to tweak the config:
ngram_token_size=3

It is better, but when the word is less than 3, it would not work perfect.


texts:
So, I have a single column that would have different string values, is it possible to sort it based on similarities in the string?

e.g. table.name would be '3d audio' , but then we would have table.name to be '3d audio and entertainment' and other possible scenarios, however '3d audio is just example' - any string is possible, so I cant filter by certain strings.

I know I've asked this, however I asked it as a secondary question to another one and it felt like it was overlooked

select pt.id, pt.name ,pt2.name from product_taxonomy pt
left join product_taxonomy pt2 on pt2.name = pt.name
where pt.name like pt2.name

This does not seem to be working at all, as I want to compare pt.name with the next instance of pt2.name, so lets say I have id = 1, 2, 3, 4 ,5
so I want to compare pt.name with id=1 to pt.name with id=2 and so on


texts: Importing LARGE quantity of data into a PostgreSQL database

texts:
I am importing a large amount of data into a PostgreSQL server (across multiple databases on the server).

When I last attempted to import the data, it effectively "crashed" my machine (Ubuntu 10.0.4), because /var/log/postgres contained over 360G of files (and I was only about 1 half way through the import).

I am not sure what the log files are for (I am guessing it must be for ACID compliancy etc). But I would like to know if there is a way to reduce the size of the log files generated during import of a large amount of data.


texts: is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra

texts: Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?


texts: trigger to first validate values in other table and then update

texts:
I have problem coding a trigger.

The table structure is this:

table 1: inventory

field 1: item_code(PK)
field 2: item_desc
field 3: unit_price

table 2: stock_master

field 1: item_code(fk)
field 2: quantity

table 3: warehouse_master

field 1: warehouse_code(PK)
field 2: location
field 3: description
field 4: address

table 4: stock_warehouse_details

field 1: stock_ware_id (PK)
field 2: item_code (FK)
field 3: warehouse_code (fk)
field 4: quantity

I need to create a trigger on stock_warehouse_details so that it will first validate item_code in stock_master table and warehouse_code in warehouse_master table and then with update trigger on stock_warehouse_details table, values should be added in stock_master if not present there and if values are present, update them with total quantity with each respective item_code.

I'm using Oracle


texts: How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2?

texts:
Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe and Microsoft won’t help. (We used an emergency ticket) So the “cashier”, doesn’t see the database, however it will see the server it’s self. When ever using ole ob connection the server is seen but not able to be connected to. We have opened every port on both computers to test to see if we could establish a connection, but to no avail... I have spent 16 hours on this with several people and can’t figure it out.
Here is the original question asked here https://www.facebook.com/groups/thisisanitsupportgroup/permalink/2698630683716128/
Covers a lot of what I have already done so


texts: SQL Server replication over internet error

texts:
I have 2 SQL Servers connected over internet, I can connect (with no problem) from each one to other using SQL Server Management Studio by using publicIP,1433\server as server address.

So, I created a publication on server1 called PUB1 but if I try to create a subscription (from server1) to server 2, I get this error message

  TITLE: Connect to Server

  Can not connect to MyPublicIP,1433 .

  ADDITIONAL INFORMATION :

  SQL Server replication requires the real name of the server to establish a connection. Connections via alias, IP address or other reference are not supported. Enter the real name of the server, " SERVEUR\SERVER ". ( Replication.Utilities )


texts: Restoring a postgres8.3 backup with postgres8.4

texts:
I'm trying to restore a backup dumped from a postgres 8.3 server in a server with postgres 8.4 with this command:

pg_restore --host host --port 5432 --username username --dbname my_db /route/to/backup/b.backup --ignore-version --format=c

But I got this error:

  pg_restore: [custom archiver] could not read from input file: end of
  file

I have done a restore with another Postgres 8.3 server and it worked just fine. My box is a Linux Mint Julia.

Anyone else with this same problem? Any idea?


texts: Help needed with several engines use case

texts:
We are developing an app, aprox 50k RPM read, 1k RPM write, it ask via key and get a JSON.
The search is always made by key.
I'm inclined to use one MySQL 8 table with a Id field and JSON field with innodb. Seems simple, cheap and fast accessing by index. Each index can have n rows (30 max), total size of table less than 100gb.
Response time is important, I think 2-10ms are achievable on MySQL.
The other, more expensive options that I have are DynamoDB and ElasticSearh (can't use another tool).
Can't find a comparison for this use case to help me know if I'm in the correct path. Do you see any cons of using MySql or I'm missing something?
Thanks!!


texts: How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

texts:
We are currently running into this issue for one of our availability group design:-
On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours
On Secondary which is configured as readable replica there will be SELECT queries running every now and then , as we do not have a window defined for secondary, REDO thread on secondary tends to get blocked by those SELECT queries which keep coming.
Currently to mitigate this issue, manually kill all select queries which keep blocking REDO thread ( this is in turn block other readers) until we see last commit time or redone time from SSMS AG dashboard sync up with the time DDL operation is completed.
Database in question is RCSI
Questions:-

Is this expected way to keep killing SELECT if you don't have a defined maint'ce window for secondary server

How are you guys dealing with this situation? I mean is there a way or script available online that it is automated to KILL such transactions on secondary in FLY when DDL is running on primary and wait until committed on secondary "?


texts:
declare @xml xml,
declare @num int
set @num = 1
set @xml = '
<row>
    <Id>1</Id>
    <name>reza</name>
</row>
<row>
    <Id>2</Id>
    <name>Masoud</name>
</row>
'

how to select ID = @num ??


texts: Attaching Database Incorrect name in sys.master_files

texts:
I have been moving some databases from SQL 2008 to SQL 2012 using detach and attach method.  I correctly map up all the files and the physical and logical names appear correctly in the properties of the database.

However if I run sys.master_files I can see that all my files have been given the same file name in the Name column of the DMV and this is making our alerting system go crazy.

Has anyone seen this before or know how I can fix it?

Thanks


texts: SELECT fields from table having some conditions

texts:
I have two tables
1. Named as t1
2. Named as t2

example of the tables are :

t1 :

 id  name
 1   one
 2   two
 3   three

t2 :

 id  name
 2   five

I need to select all the data from the t1 table as well as from table t2 but I don't want to write data from table t1 which is updated

I need a resulting table like this:

 id  name
 1   one
 2   five
 3   three

Please help me to solve this in postgres


texts: Msg 7391, Distributed Transactions (DTC) on SQL Server

texts:
This may be DBA related or developer related, I honestly don't know.

I have two Database servers:

A: SQL Server 2005 (Trigger that uses DBMail on Server B)
B: SQL Server 2008 R2 (DBMail configured)

I have a table in Server A where email notifications are queued and I created an "after insert" trigger to execute sp_send_dbmail using Server B like so...

USE SampleDB
ALTER TRIGGER [dbo].[mail_inserted]
   ON [dbo].[tblEmailQueue]
   AFTER INSERT
AS
BEGIN
    declare @MailTo nvarchar(max), @MailSubject nvarchar(max), @MailBody nvarchar(max)
    -- Initialize vars
    select @MailTo=MailTo from inserted
    select @MailSubject=MailSubject from inserted
    select @MailBody=MailBody from inserted

    -- Send email via DbMail on ServerB
    exec [ServerB].msdb.dbo.sp_send_dbmail --this runs fine by itself, but not via trigger
        @profile_name = 'Test Profile'
        ,@from_address = 'noreply@test.com'
        ,@recipients = @MailTo
        ,@subject = @MailSubject
        ,@body = @MailBody
END

Here's where the weirdness begins that I need help with, mind you this seems to be a very poorly documented (or severely lacking thereof) feature.

Now I can execute that last statement with some sample data and everything works just fine. But when I try to insert into the table like so...

insert dbo.tblEmailQueue (MailTo,MailFrom,MailSubject,MailBody) values
    ('to-me@test.com','from-somebody@test.com','Subject','Body')

I get the following ERROR...

OLE DB provider "SQLNCLI" for linked server "ServerB"
    returned message "The partner transaction manager has disabled its support for
    remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure mail_inserted, Line 15
The operation could not be performed because OLE DB provider "SQLNCLI" for
    linked server "ServerB" was unable to begin a distributed transaction.

Perhaps I'm not understanding how Server A is connecting to Server B under the context of the trigger? Do I need to setup a special Login or something? I'm stumped.


texts: Azure Managed Instance - Is it possible to send Sql Logs To Log Analytics?

texts:
Is it possible to stream a Managed Instances SQL logs into log analytics?

I need a single location for all Azure based monitoring so that multiple teams have visibility/access to log information for issue investigation.

The idea being that if there is ever a failover we don't have any gaps in our SQL Logs as well as having a single location to view this information.


texts: Why can not user set current schema to SYS?

texts:
See the example bellow. User is created with dba (but not sysdba) privs.
He's allows to access v$session (which is public synonym to view in schema SYS, having different name).

When executing alter session set current_schema=SYS;, the public synonym does not resolve anymore.

SQL> create user xy identified by xy;
User created.

SQL> grant connect, resource, dba to xy;
Grant succeeded.

SQL> connect xy/xy;
Connected.
SQL> select count(1) from v$session;

  COUNT(1)
----------
        96

SQL> alter session set current_schema=SYS;

Session altered.

SQL> select count(1) from v$session;
select count(1) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Update:
I understand, that this is not a problem for most of the applications.
In GUI application I work on a user can execute any SQL in a worksheet,
he also can switch into any schema (using combo box). But then when the connection is returned into connection pool this connection can not work anymore. When current schema is switched to SYS embedded application queries raise an error.

I just do not understand rationale for this behavior.


texts:
in my mariadb i have replication, i tried to change the location of the binlogs by changing the configuration file as so: log-bin = /new/path/mysql-bin
I also tried to change the index location as so: log_bin_index = /new/path/mysql-bin.index
but every time when I restart I see that the location of the binlog files is /var/lib/mysql...
i tried to check with mysqld --verbos --help | grep bin and I saw that the lob-bin parameter is mysql-bin and not what I wrote in the cnf...
how to I make the MySQL to take the value of the log-bin from the cnf file?
this is the cnf file of the MySQL for sure...


texts:
I have a mysql query, which I want to use in MS Access.

SELECT company_name, agent_id FROM
( SELECT company_name, agent_id, @rn := IF(@prev = agent_id, @rn + 1, 1)
  AS rn, @prev := agent_id FROM users
  JOIN (SELECT @prev := NULL, @rn := 0) AS vars
  ORDER BY agent_id DESC, company_name)
 AS T1
 WHERE T1.agent_id is not null and  rn <= 3;

I have been trying to run it in Access but its throwing the error: "Syntax error in from clause".


texts: Select count date from multiple where conditions for each day

texts:
I have lot of coupons. I would like to learn for each day how many coupons from each campaign have been received by users. But I cannot write something like assigned_date for each select row

SELECT count(id)                                                  as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')                     as date,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 1 AND assigned_date=THIS MUST BE SOMETHING) as campaign_1,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 2 AND assigned_date=THIS MUST BE SOMETHING) as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC

So the result will be something like. How can I achieve this result?

+-------------------+------------+-------------+-----------+
| number of coupons |    date    |  campaign_1 | campaign2 |
+-------------------+------------+-------------+-----------+
|               156 | 12-10-2019 |        6980 |       100 |
|               177 | 11-10-2019 |        6980 |       100 |
|                44 | 10-10-2019 |        6980 |       100 |
|                94 | 09-10-2019 |        6980 |       100 |
|                93 | 08-10-2019 |        6980 |       100 |
+-------------------+------------+-------------+-----------+


texts: SQL Server 2008 R2 - Upgrade Default DB Instance (Express) to Developer Edition

texts:
I have just installed SQL Server 2008 R2 Developer edition (created a new instance for it: DEVEDITION). I previously had and the default instance (MSSQLSERVER) which was running SQL Server 2008 R2 Express.

I have ~20 DB's that are running on the default instance: MSSQLSERVER.

If this is possible, I would like to convert/upgrade the MSSQLSERVER instance so that I can use all the bells and whistles of the Developer edition on it. i.e. Profiler, SQL Agent etc.

I have all DB's backed up and as it is a development environment I can uninstall all versions of SQL if needs be.

Thanks in advance.

Here's the previous question I asked regarding the installation of Developer Edition over Express.


texts: Delete word, its meanings, its meaning's example sentences from DB

texts:
I have three tables as below (simplified for demonstration):

words
=====
integer id
text    word

meanings
========
integer id
integer word_id
text    meaning

examples
========
integer id
integer meaning_id
text    sentence

where, word_id stores id of the word in words table and meaning_id stores id of the meaning in meanings table. I am trying to figure out a sql query, given a word's id, to delete the word with all its meanings and example sentences all at one time. Is such sql query possible to compose? If so, how?

Edit1: I am using SQLite3 as the database.

Edit2: I figured the following solution which requires 3 sql queries in order:

DELETE FROM examples WHERE meaning_id IN (SELECT id FROM meanings WHERE word_id=the_given_id);
DELETE FROM meanings WHERE word_id=the_given_id;
DELETE FROM words WHERE id=the_given_id;

I'm still looking for the answer to my question: is the whole process possible to be done in one query?


texts: Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases?

texts:
I want to migrate some data from Postgres database A to database B. I found Foreign Data Wrappers that allow you to connect to a table in B while in database A. I have a table in A that I want to move to B and clear out the records in A.
insert into b_table select * from a_table;
delete from a_table;

Will a postgres transaction provide me with all expected error recovery in the event of a crash or disconnect between the two SQL lines even though I'm working with 2 different databases?


texts: Postgres, RDW, WHERE condition not pushed to remote (JSONB attribute, using postgres_rdw)

texts:
I have a remote postgres table using RDW. It contains a JSONB column and I use values in that JSONB for the WHERE condition.

The remote table has a GIN index on the JSONB column and an index on the hostname-attribute that I use for filtering.

EXPLAIN ANALYZE VERBOSE
SELECT
*
FROM forein_table
WHERE details->>'hostname' = 'host-xyz'

shows that the "Remote SQL" does not contain the WHERE-clause and filtering is done local.
On the other hand

EXPLAIN ANALYZE VERBOSE
SELECT
*
FROM forein_table
WHERE details@> '{"hostname": "host-xyz"}'

sends down the 'WHERE' clause which is obviously much faster.

The documentation says:

  WHERE clauses are not sent to the remote server unless they use only built-in data types, operators, and functions. Operators and functions in the clauses must be IMMUTABLE as well.

Is there a difference between ->> and @>?

Is there a way to use WHERE details->>'hostname' = 'host-xyz' on a remote table and have it push down the WHERE clause?

(I found a way by creating a VIEW which extracts the hostname attribute as a column, and then creating the remote table on this view...but obviously this is not a very flexible/elegant approach)


texts: INSERT of 1 wrong value -> Error; INSERT of 2 wrong values -> Warning!

texts:
CREATE TABLE `dummy` (
    `a` TINYINT(4) NOT NULL
) ENGINE=MyISAM;

1 wrong value

INSERT INTO `dummy` (`a`) VALUES (NULL);
/* SQL Fehler (1048): Column 'a' cannot be null */
/* Nothing is stored! */

2 wrong values

INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
/* Affected rows: 2  Gefundene Zeilen: 0  Warnungen: 2  Dauer von Abfrage: ... sec. */
SHOW WARNINGS LIMIT 5; /* Not sure where this comes from! Maybe HeidiSQL? */
/* Two entries are stored! */

Why is MySQL raising an error with the single value query, but not at the second query with two values?

Is it related to SHOW WARNINGS LIMIT 5; But where does this line come from and why is it changing the error type from Error to Warning?

MySQL V5.6.31


texts:
I'm trying to troubleshoot my MYSQL in a LAMP and have an interesting situation.  At first I thought MYSQL itself was locking up, but it turns out to be ONLY innodb databases that are locking up.

These sites are WordPress.  I converted one to Myisam and now when the lockups happen that one is still good, so in my mind this rules out site specific issues.

Any suggestions?

This is my my.cnf

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
query_cache_size=128M
tmp_table_size=32M
thread_cache_size=4
innodb_buffer_pool_size=400M
key_buffer_size=60M
max_heap_table_size=32M
tmp_table_size=64M
join_buffer_size=1M


texts: "Mysql has gone away" heavy multiple insert

texts:
I am stuck because Mysql server will go away after executing this part of code this function will update the database with the new RSS feeds which are stored in ToUpdate but Mysql stops working. Every cell of the array contains a long string for description,for example at the size of 3000 words or more and may be that's the problem. the maximum size of $this->array_counter is less than 30 and is set before in another function. after running the function after 3 to 6 inserts mysql says " mysql has gone away". Is the problem related to connection limit or bandwidth limit or fast inserting?

function update_db()
{

    for($i = 0;$i < $this->array_counter ; $i++ ){

        $url=$this->toUpdate[$i]['url'];

         $t=addslashes( $this->toUpdate[$i]['title'] );
         $t=$this->convert_line_breaks($t,"
");

         $d=addslashes( $this->toUpdate[$i]['desc'] );
         $d= $this->convert_line_breaks($d,"
");

         $date= $this->toUpdate[$i]['date'];

    //echo "<fieldset>".$url."
".$t."
".$d."</fieldset>";

        $r=mysql_query("INSERT INTO feed_items(items_id,items_title,items_url,items_description,items_date,feed_id,like_hits) VALUES('','$t','$u','$d','$date','$this->Feed_id','0') ",$this->dblink)or die(mysql_error());

    }//end of foreach
}


texts: Communication link failure for some queries to linked server

texts:
I am seeing the following error in SSMS (server has Windows Server 2008 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 installed) when running some queries against the linked server, in particular long running queries.

Simple selects from tables across the linked server work fine. This is a new issue that was noticed when SPs that have worked for years started failing.

I have run a Wireshark capture on the server, capturing for packets to port 1433 on the linked server host. At the tail of the capture, I see many (10) TCP Keep-Alives being issued (after a message regarding bad checksum) and then an RST packet. The RST packet is correlated with the error below being returned to the client.

There are other database servers on our network, where the linked server is configured identically, that don't exhibit this issue.

I have found some articles such as this and this. We are using the implicated Broadcom NICs. The Chimney Offload State setting is enabled on the server.

We will try disabling. Other thoughts on troubleshooting would be much appreciated.

OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.


texts: Query shows sort cost,even when required index is available

texts:
This question is from SO,iflagged it move to DBA.SE..but i may not see that happening,due to upvotes..so posting here

below is test data  :

--Main Table

 CREATE TABLE [dbo].[LogTable]
    (
      [LogID] [int] NOT NULL
                    IDENTITY(1, 1) ,
      [DateSent] [datetime] NULL,
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [PK_LogTable] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent_LogID] ON [dbo].[LogTable] ([DateSent] DESC) INCLUDE ([LogID]) ON [PRIMARY]
GO

--Cross table

 CREATE TABLE [dbo].[LogTable_Cross]
    (
      [LogID] [int] NOT NULL ,
      [UserID] [int] NOT NULL
    )
 ON [PRIMARY]
GO
 ALTER TABLE [dbo].[LogTable_Cross] WITH NOCHECK ADD CONSTRAINT [FK_LogTable_Cross_LogTable] FOREIGN KEY ([LogID]) REFERENCES [dbo].[LogTable] ([LogID])
GO
 CREATE NONCLUSTERED INDEX [IX_LogTable_Cross_UserID_LogID]
 ON [dbo].[LogTable_Cross] ([UserID])
 INCLUDE ([LogID])
GO

-- Script to populate them
 INSERT INTO [LogTable]
        SELECT TOP 100000
                DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0)
        FROM    sys.sysobjects
                CROSS JOIN sys.all_columns

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                1
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                2
        FROM    [LogTable]
        ORDER BY NEWID()

 INSERT INTO [LogTable_Cross]
        SELECT  [LogID] ,
                3
        FROM    [LogTable]
        ORDER BY NEWID()

GO

When we use below simple query  ,

SELECT  DI.LogID
FROM    LogTable DI
        INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
        WHERE  DP.UserID = 1
ORDER BY DateSent DESC

query shows sort

As per my understanding,sort cost should be avoided,since we have the required index

 CREATE NONCLUSTERED INDEX [IX_LogTable_DateSent] ON [dbo].[LogTable] ([DateSent] DESC) ON [PRIMARY]

and also digging into plan shows the same index is used..

My questions are  :
1.Why sort cost is still present
2.What exactly does ordered property is true means .

Few observations/work i have done :
1.Ordered property is set to false

so i have rewrote query like below

   SELECT  DI.LogID
    FROM    LogTable DI
            INNER JOIN LogTable_Cross DP ON DP.LogID = DI.LogID
            WHERE  DP.UserID = 1  and di.datesent is not null
    ORDER BY DateSent DESC

rewriting like above,makes ordered property to true,but still sort is present

closest,i can find is this article by Paul White:A Tale of Two Index Hints ,in this article,below Point clarifies why this sort occurs

  For very large tables, the optimizer may calculate that an IAM-driven scan might save more time than would be consumed by the extra sort, and a plan featuring the unordered scan + sort would be chosen.  This is a heuristic optimization: the optimizer knows nothing about the actual fragmentation level of an inde

But this query requires sort and it shouldn't satisfy the conditions for an IAM scan

Let me know ,if you need any more details


texts: Is DBCA in Oracle for a single database or the entire system?

texts:
I ask this because I was going to create a simple phone list database for learning.  But when I used dbca, I was asked did I want to install the sample databases, HR, etc.  It also had the default locations for all archive_log files, passwords for sys, sysman...I thought dbca was just to create a single database.

What is dbca for?


texts: How to use values from table column as field names in SELECT query?

texts:
Scenario:

Field names of Table1 named as testtable

id,name,size,width,height

Field names of Table2 named as errortable

id,desc,field1,field2,operator

Values of errortable

+----+-------------------------------------+--------+--------+----------+
| id |                desc                 | field1 | field2 | operator |
+----+-------------------------------------+--------+--------+----------+
|  1 | size should not greater than width  | size   | width  | >        |
|  2 | size should not greater than height | size   | height | >        |
|  3 | with should be equal to height      | width  | height | <>       |
+----+-------------------------------------+--------+--------+----------+

Now I want to check from testtable:

count all those records where size > width.
count all those records where size > height.
count all those records where width <> height.

Required Output

+-------------------------------------+-------+
|              errorname              | count |
+-------------------------------------+-------+
| size should not greater than width  |     6 |
| size should not greater than height |     2 |
| with should be equal to height      |     3 |
+-------------------------------------+-------+

Is it possible to do like this?

Current query:

select desc,
  (select count(*) as "Total Errors"
   from testtable
   where errortable.field1 errortable.operator errortable.field2 )
from errortable group by id;


texts: Kerberos authentication not working with Linked Servers in SQL Server 2012

texts:
I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012.  We are moving from SQL Server 2005 on Windows Server 2008, where we already have this up-and-running correctly.

In SQL Server 2012, Kerberos authentication is not working.

Each server has it's own Active Directory account that has the "Write Service Principal Names" and "Read Service Principal Names" rights granted through Active Directory Users and Computers.  Whenever I connect to the SQL Server 2005 servers, and run:

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

I see:

net_transport    auth_scheme
TCP              KERBEROS

When I perform the same query against my new SQL Server 2012 instances, I see:

net_transport    auth_scheme
TCP              NTLM

If I use SetSPN -Q MSSQLSvc/* to query Active Domain for Service Principal Names, I see both the 2005 and 2012 servers listed, exactly the same way other than the name of the server.

For instance:

MSSQLSvc/SERVERa2005.domain.inet
MSSQLSvc/SERVERa2005domain.inet:1433
MSSQLSvc/SERVERb2005.domain.inet
MSSQLSvc/SERVERb2005domain.inet:1433
MSSQLSvc/SERVERa2012.domain.inet
MSSQLSvc/SERVERa2012domain.inet:1433
MSSQLSvc/SERVERb2012.domain.inet
MSSQLSvc/SERVERb2012domain.inet:1433

What else do I need to do to enable Kerberos Authentication to SQL Server 2012?  Books Online seems to have nothing else to say, except that the SPN's have to be setup.  Which clearly, they are.  SQL Server Error logs on both 2012 machines say:

2012-12-10 14:55:47.630 The SQL Server Network Interface library
                            successfully registered the Service Principal Name (SPN)
                            [ MSSQLSvc/SERVERa2012.domain.inet ] for the SQL Server
                            service.

2012-12-10 14:55:47.630 The SQL Server Network Interface library
                            successfully registered the Service Principal Name (SPN)
                            [ MSSQLSvc/SERVERa2012.domain.inet:1433 ] for the SQL
                            Server service.

2012-12-10 14:55:47.590 SQL Server is attempting to register a Service
                            Principal Name (SPN) for the SQL Server service.
                            Kerberos authentication will not be possible until a
                            SPN is registered for the SQL Server service. This is an
                            informational message. No user action is required.


texts: Why is it not possible to alter a view with a "CREATE OR REPLACE"?

texts:
The Postgresql documentation is clear on this point: you can not change the columns of an existing view.

  The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types) (...)

Reference: http://www.postgresql.org/docs/9.3/static/sql-createview.html

Why on earth do I have to either DROP the view or ALTER it for modifying it ?


texts: Mysql utf-8 behaves differently into different environments

texts:
I have MySQL server under docker in 2 different environments:

This is my create database:

CREATE DATABASE guests CHARACTER SET utf8mb4 COLLATE UTF8MB4_UNICODE_CI;

This is the create table:

USE guests;
CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL);

This is how I Insert data:

insert into MyGuests (firstName)  VALUES("בדיקה");
insert into MyGuests (firstName)  VALUES("ة التي تح");
insert into MyGuests (firstName)  VALUES("7");

In the one environment, I get question marks in the result.
In the other environment, I get the correct characters

The good environment is mysql in a docker under windows 10.
The bad one is mysql in a docker under Ubuntu.

This is the result for running:

This is the output of the command mysql> show variables like '%collation%'; in the bad env.

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

This is the output of the command mysql> show variables like '%collation%'; in the good env.

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_bin        |
| collation_server     | latin1_swedish_ci  |
+----------------------+-------------------=+

Thanks


texts: I am moving my forum from one domain to another and need to replace all instances of the previous domain with the new one. In looking at the mysql data, many of the matches are going to be in the middle of a serialized string, is there a quick way to replace all matches?


texts: How can I find variations in database schemas?

texts:
I'm working with a system where each customer has their own database.

This amounts to ~4000 databases (approximately 150 tables each database) spread over 8 instances of Microsoft SQL server 2012.

The database scheme for all these databases is supposed to be identical, but recent events has shown that there are minor deviations in data types, indexes, keys and nullable/default value settings in some of the databases.

My goal is to synchronize all database schemas so they all have identical tables, columns, indexes, keys and nullable/default value settings

I have a single database that I'm certain has the 100% correct database schema.

How could I compare this database scheme to others, in an attempt to find variations in keys, indexes, tables, columns and nullable/default value settings?

I've read this question which is similar to mine, but the suggestions (like right clicking the database object in SSMS to generate a script or generating and comparing a .dacpac file with the Visual Studio extension SSDT) seems to be oriented towards comparing only two databases, and is not practical for comparing 4000 database schemas.


texts: Batch Delete with checkpoint still causes log growth

texts:
I have a large table (600 million rows,  60GB data) that I need to migrate to another table. This is for a few reasons:

The current table is a heap and I want to convert it to having a clustered index
I have a column which is time(7) that only really needs to be time(4)
The table has previous deleted columns that are still reserving space from what I can see (they are just marked as is_dropped in the metadata.

The best way I could come up with doing all this in the fewest operations (i.e. not having to rebuild the table multiple times) was to create a new table with the column definitions I want and copy the data across into the new table while deleting the old data, followed by a name switch. I've split this into batches in a loop to prevent the log file growing continously, however this doesn't seem to be working and after about 70million rows, the log file has grown to around 110GB and there is no space left on the server...
The database is using simple recovery and I have a checkpoint after each batch, which I thought was meant to stop the log file growing! Am I missing something, or is there some other reason my log file continues to grow? This is testing on a dev server I'm doing, so there are no other operations currently running on the table.
My query is:
WHILE 1 = 1
BEGIN
    DELETE TOP(1000000) FROM RunningData with (tablock)
    OUTPUT
        deleted.[Data],
        deleted.[Date],
        deleted.Time,
        deleted.Category,
        deleted.SystemID
    INTO [dbo].[TmpRunningData] (
        Data,
        Date,
        [Time],
        Category,
        SystemID
    )

    IF @@ROWCOUNT = 0 BREAK
    CHECKPOINT
    RAISERROR ('Moving to next batch', 0, 1) WITH NOWAIT
    waitfor delay '00:00:01'
END

I really want to be able to sort this table out, but I'm struggling to find a way of processing this table due to the size! Any suggestions on how I can stop the log growing, or another way to do it would be very appreciated, thanks! In production the database can go down for a weekend, so there should be plenty of time to do it.


texts: Does SQL Server place shared locks on scanned records when using REPEATABLE READ

texts:
Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ.

In this session I start a transaction and execute an UPDATE statement with a WHERE clause on  a non indexed column. This statement should evaluate WHERE clause for each record in the table, but only one will match.

If I examine the locks placed under this transaction after running the UPDATE statement I can only see two IX locks on Table and Page, and a X lock on the row which was updated.

My question is: shouldn't the database engine place shared locks on all the rows it read to ensure REPEATABLE READ? What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

If I execute a SELECT *, then I can see it placing S locks on each row, which are not already locked with X.

Can anyone help me understand this situation?

I've tried with both SQL Server 2008 R2 and 2012, same behavior in both.


texts: Postgres, return 0 rows if any row matches condition, otherwise return all rows

texts:
I have a table with ten rows. One of the columns is a boolean, let's just say for argument's sake:

email text
is_activated boolean

I'd like to write a query that returns zero rows if is_activated is true for any of the rows. If none of the rows have is_activated set to true, I'd like to return all of the rows in the table.

How can I do that?


texts: MultiSubnet Failover with RegisterAllProvidersIP 1

texts:
I am using a 2 node SQL AG (2014 EE) (each on a different subnet) and the Listener is configured with 2 Static IPs. The client application uses SQL Native Client 11.4.
My understanding is that this driver is legacy and hence should not support failover with RegisterAllProvidersIP as 1 and default HostRecordTTL( 20 minutes).
Although I am unable to failover when RegsiterAllProvidersIP is 0 and TTL is 20 minutes (I am able to when TTL is lowered to say, 2 minutes), the application successfully fails over with RegisterAllProvidersIP set as 1.
According to the blog here, https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
It is recommended to set RegisterAllProvidersIP to 0 and HostRecordTTL to 2 minutes to achieve failover, but in my case I found that it is not needed as I am able to failover with RegisterAllProvidersIP set as 1.
Am I completely missing the point of recommended settings from MS blog or my multi subnet setup has any issues?


texts: SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

texts:
After doing some research I cannot seem to find an answer to this question.

Background
I am attempting to setup a backup plan that fits the following three requirements:

Reliability of backups, having nightly full backups
Transaction log backups that could be restored from
Low amount of disc space used
The backups must be accessible locally for an auditing tool

So to fit those needs I am thinking full backups weekly, differential daily, and transaction hourly. Then each night a copy_only backup would run that can be shipped offsite, this backup is done so that the log chain is not broken, and we have reliable nightly full backups offsite, without having to eat up so much local disc space.

Question
Is it possible to restore from the copy_only backup, and restore the transaction logs after.

Let me just give an example so you know what I am talking about.

Using the below list I am wondering if it is possible to restore FullbackupCOPY_ONLYC.bak followed by TransactionbackupG.trn, TransactionbackupH.trn, finally TransactionbackupI.trn

> ---List of Backups---
FullbackupA.bak 01/01/2013 00:00:00
>  DifferntialbackupA.bak 02/01/2013 00:00:00
FullbackupCOPY_ONLYA.bak 02/01/2013 00:00:00
>     TransactionbackupA.trn 02/01/2013 01:00:00
>     TransactionbackupB.trn 02/01/2013 02:00:00
>     TransactionbackupC.trn 02/01/2013 03:00:00
>  DifferntialbackupB.bak 03/01/2013 00:00:00
FullbackupCOPY_ONLYB.bak 03/01/2013 00:00:00
>     TransactionbackupD.trn 03/01/2013 01:00:00
>     TransactionbackupE.trn 03/01/2013 02:00:00
>     TransactionbackupF.trn 03/01/2013 03:00:00
>  DifferntialbackupC.bak 04/01/2013 00:00:00
FullbackupCOPY_ONLYC.bak 04/01/2013 00:00:00
>     TransactionbackupG.trn 04/01/2013 01:00:00
>     TransactionbackupH.trn 04/01/2013 02:00:00
>     TransactionbackupI.trn 04/01/2013 03:00:00

Maybe this whole setup is not reasonable I am fairly new to SQL Server and am trying to learn as I go. Any advice/help would be appreciated.


texts:
We have an Access 2000 format database running in Office Access 2003.

We have a problem with the size when I merge the database; the size is about 250 MB.  Two days later, the size has ballooned to about 750 MB.

How can I see where this increase in size comes from?  Can I look behind the scenes in Access?

The database is running for a tennis club and our employees are working all day long with the database.  Another programmer made the database and says it is common to see Access databases increase in size.

What can I do to alleviate this issue?


texts: Dynamic query in Stored Procedure over table values

texts:
CREATE TABLE `Slot` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `shift_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

I have a stored procedure where I have a Temporary Table like :

TempSlot

| starttime              | endtime             |
| 2018-10-25 15:00:00    | 2018-10-25 16:00:00 |
| 2018-10-25 13:00:00    | 2018-10-25 14:00:00 |
| 2018-10-26 12:00:00    | 2018-10-26 13:00:00 |

Now, I need to check if any entry from TempSlot is present in the Slot table such that inserting it will create a conflict.

Am not sure how to write this query in stored procedure :

I want to write something like this :

SELECT COUNT(*)  FROM Slot
WHERE
(((starttime >= '2018-10-25 15:00:00' and starttime < '2018-10-25 16:00:00')
or
(endtime > '2018-10-25 15:00:00' and endtime <= '2018-10-25 16:00:00'))
OR
((starttime >= '2018-10-25 13:00:00' and starttime < '2018-10-25 14:00:00')
or
(endtime > '2018-10-25 13:00:00' and endtime <= '2018-10-25 14:00:00'))
OR((starttime >= '2018-10-26 12:00:00' and starttime < '2018-10-26 13:00:00')
or
(endtime > '2018-10-26 12:00:00' and endtime <= '2018-10-26 13:00:00'))
)

But I want a generic query. What will be a good way to write this ?


texts: SSIS error Property BackupDirectory is not available for Settings

texts:
I am trying to edit the SSIS Back Up Database Task And it gives the folowing error

  Property BackupDirectory is not available for Settings 'Microsoft.SqlServer.Management.Smo.Settings'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)


texts: When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes

texts:
I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance reasons I bulk insert the data before creating any primary keys or indexes. This is my current order of operations:

create the tables
bulk COPY all of the data
create primary keys
create additional indexes

Creating the primary keys and the indexes are the slowest parts of this workflow. Is there a point in this workflow when recalculating statistics via ANALYZE/VACUUM/REINDEX would be helpful or even necessary?


texts: Turning off Append only and snapshot on Redis

texts: I want to turn off data persistence on my Redis master, by turning off appendonly and snapshots, I want to use the replica for persistence, will turning off these two have any affect on replication?


texts: SQL Server 2008 R2 Query Last Successful Database Backup For All DBs

texts: Is it possible to query the last successful backup date (and perhaps the type of backup) of each database in SQL Sever 2008 R2?


texts:
We're facing an Out Memory Problem with an Oracle database SE 12c Release 1 (deployed as a Docker container) ...

The only information that I have is this one:

Caused by: java.sql.SQLRecoverableException: ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory Additional information: 2673
Additional information: 360449 Additional information: 1627389952

I try to google the ORA-27102 but the forum discussions are to broad (to implement any solution will be like shotting in the dark) ... I don't know how to narrow my problem? Does anyone know what those additional information codes  are (I tried to google them without success) ??

Any suggestion will be appreciated!


texts:
EDIT - drachenstern: see this question for more information on this problem and on the source of his query below:

  Group data by non-unique keys by distinct time range

Can someone please advise some tuning options for the following query? When running for one project it is fast but for all the records it takes hours to finish running. NUM_ROWS in PA table: 2,101,528

I want to group data by non-unique keys by a distinct time range

SELECT project_nbr, status, MIN(aud_timestamp) start_dt, end_dt
FROM (
    SELECT a.project_nbr
         , status
         , aud_timestamp
         , ( SELECT MAX(p.aud_timestamp)
             FROM pa p
             WHERE p.project_nbr = a.project_nbr
               AND p.status = a.status
               AND p.aud_timestamp >= a.aud_timestamp
               AND NOT EXISTS (
                            SELECT NULL
                            FROM pa q
                            WHERE q.project_nbr = p.project_nbr
                              AND q.status <> p.status
                              AND q.aud_timestamp < p.aud_timestamp
                              AND q.aud_timestamp > a.aud_timestamp
                               )
            ) end_dt
     FROM pa a
     )
GROUP BY project_nbr, status, end_dt


texts:
I'm attempting to add databases to an existing availability group using automated seeding but I randomly receive this error: "Seeding Check Message Timeout".  I'm pulling this error message from sys.dm_hadr_automatic_seeding.

I've attempted to add databases one at a time or several at once.  Either way results in the above error.  I've also attempted using script and SSMS gui.


texts:
I have recently set up a new RDS instance to test out working with a AWS postgres instance vs a heroku postgres instance.  What I've found is that when I load an instance of 1000 entries in RDS its taking 100ms to read where when I do the same thing in heroku its taking 20ms.

I imagine its something I did wrong during set up.  I followed https://reinteractive.net/posts/128-heroku-app-backed-by-an-aws-rds-postgres-database for the set up.

Is there something I missing that I should have done after setup?

RDS Build:
Instance: db.m3.xlarge
Ram: 15GB
Storage: Purpose (SSD)
IOPS: disabled
StorageAmount: 100 GB
Multi-AZ: NO

Heroku Build:
1 GB RAM
64 GB storage


texts:
I have 2 tables, one for links (Links) and one for comments (Comments) where each comment contains a reference to a link (it can also point to NULL if it has no associated link, such comments we disregard for a moment). I want to order links such that links with newest comments are on top.

Currently, I do this:

SELECT DISTINCT linkId FROM Comments WHERE
  linkId IS NOT NULL AND
  dateAdded <= ?
ORDER BY id DESC
LIMIT 10

This is working very well (around 1ms with current 60k rows, I've tested it also with 800k rows, it still performs just as well)

However, now I'd like to classify the links into different categories and therefore I created a new column category in Links table and I want to order the links within their category. I tried running following query:

SELECT DISTINCT c.linkId FROM Comments c LEFT JOIN Links l ON c.linkId = l.id WHERE
  c.linkId IS NOT NULL AND
  c.dateAdded <= ? AND
  l.category = ?
ORDER BY c.id DESC
LIMIT 10

This makes the query execution time become ~1.5s with the table with 800k rows so it is obviously not scaling well.

The tables are defined as follows:

CREATE TABLE `Links` (
  `id` int(10) UNSIGNED NOT NULL,
  `category` int(10) UNSIGNED NOT NULL,
  `url` varchar(2083) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `Links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category` (`category`);

CREATE TABLE `Comments` (
  `id` int(10) UNSIGNED NOT NULL,
  `linkId` int(10) UNSIGNED DEFAULT NULL,
  `content` varchar(2000) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `Comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx2` (`linkId`,`dateAdded`);
ALTER TABLE `Comments`
  ADD CONSTRAINT `fk_link_id` FOREIGN KEY (`linkId`) REFERENCES `Links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Is there any way how this query can be optimized?

I guess creating category column in Comments table would help but that could lead to inconsistencies (i.e. link pointing to one category but its links pointing to another category).


texts: Mysql no longer working and cannot reinstall

texts:
I am on ubuntu 16.04 and my mysql connection to local database suddenly stopped working one day, I then tried various things to fix it, but then came to trying to completely remove and reinstall it but when I do it errors out

Systemctl status mysql.service

mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Mon 2019-01-07 10:26:11 GMT; 15s ago
  Process: 14346 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 14339 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 14346 (code=exited, status=1/FAILURE);         : 14347 (mysql-systemd-s)
   CGroup: /system.slice/mysql.service
           └─control
             ├─14347 /bin/bash /usr/share/mysql/mysql-systemd-start post
             └─14403 sleep 1

Journalctl -xe

Jan 07 10:26:11 np161-Vostro-3268 audit[14346]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/14346/status" pid=14346 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=
Jan 07 10:26:11 np161-Vostro-3268 audit[14346]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=14346 comm="mysqld" requested_mask="r" denied_mask="r"
Jan 07 10:26:11 np161-Vostro-3268 audit[14346]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/14346/status" pid=14346 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=
Jan 07 10:26:11 np161-Vostro-3268 kernel: audit: type=1400 audit(1546856771.110:25227): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/14346/status" pid=14346 comm="mysqld" reque
Jan 07 10:26:11 np161-Vostro-3268 kernel: audit: type=1400 audit(1546856771.110:25228): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=14346 comm="mysqld
Jan 07 10:26:11 np161-Vostro-3268 kernel: audit: type=1400 audit(1546856771.110:25229): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/14346/status" pid=14346 comm="mysqld" reque
Jan 07 10:26:13 np161-Vostro-3268 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Jan 07 10:26:14 np161-Vostro-3268 sudo[13239]: pam_unix(sudo:session): session closed for user root
Jan 07 10:26:42 np161-Vostro-3268 systemd[1]: Failed to start MySQL Community Server.


texts: Index on foreign key makes query extremely slow

texts:
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.

The queried table (table3) has an indexed PK, three FK with indexes and a compound unique constraint on the three FKs. The offensive query looks like this:

SELECT ...
  FROM table1 t1, table2 t2, table3 t3
  WHERE t1.abs_id = ?
    AND t3.vgs_id = t1.vgs_id
    AND t3.ai_id > ?
    AND t2.id = t1.t2_id
    AND t2.status = 2
    AND t2.felddimension = 0
    ...

Only instance restart solved the issue. Even killing connections did no help.

After futher investigation on the FKs and the indexes, it turned out that the index on the t3.ai_id column causes the severe drop in performance. After disabling this one the unique constaint served the query extremely fast.

The problematic part is AND t3.ai_id > ? (range scan). Unique scan does not cause any trouble.

Now the question is, how can an index cause such a slowdown and moreover, how can I investigate the cause? It simply doesn't add up for me.

Competitive times: normal 10 s, slowdown > 2 min or never returning.

EDIT 1 (2013-06-05): Upon Jack Douglas' and Chris Saxon's advises, I have ran stats and then performed explain plan, I have made a giant leap forward.

I have calcucated schema stats with and without the index. Regardless whether the index is available or not, the optimizer uses the 3-field-composite-unique-index making the query extremely fast.

Here's the explain plan from SQL Developer:

So far so good, now I have added a hint into the query to use the bad index, and the explain plan is:

Now, this is extremely slow. But why?


texts: Cannot find query in plan cache and query execution times different for different paramters

texts:
I am investigating the reason why a query is performing bad in my SQL server 2016.

The query executes in 1 sec when i give the date range filter as 2017-06-21 and 2018-06-20.
When i give the date range filter as 2016-06-21 and 2017-06-20 ,the query becomes slow and takes 8 sec to executes.

The query is getting generated from .net application(linq-sql and EFF) and i capture the query with SQL profiler and execute in SSMS for testing.Query is same with same parameters but with different parameter values(date range).

I checked the actual execution plan from SSMS and it looks slightly different.I tried to retrieve the query execution plan for the query using the below sql query

    SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

and adding where clause to filter my query.But to my surprise,i couldn't find any query plan for the query i execute.

I know there will be suggestions that it is due to parameter sniffing.But sql server is not even caching my query,as far as i tested.It is very weird for me.

The query when i capture from profiler starts with exec sp_executesql and i dunno how sql handles ad-hoc queries like this.
Any suggestions of explanation?

Additional Info:
I took out the portion of the query where filtering takes place and ran with two date ranges.In the execution plan of faster query at the beginning itself,left most node(left most part of the execution plan),the filtering of date is taking place.
But for the slower query,it is not happening,the filtering of date occurs somewhere in the middle part.

select * from Searchview as tb1 where
    (tb1.ReportDateTime >= '2016-06-21 00:00:00') and (tb1.ReportDateTime <= '2017-06-21 23:59:00') and (
    (tb1.AgentID='1330056') )

select * from Searchview as tb1 where
(tb1.ReportDateTime >= '2017-06-21 00:00:00') and (tb1.ReportDateTime <= '2018-06-21 23:59:00') and (
(tb1.AgentID='1330056') )

The above queries are  reduced version of the actual query and here the parameters are same only difference is the dates.
The execution plans are different though.I don't understand why the difference,as i have leading index on ReportDateTime and AgentID and statistics are uptodate.


texts: Generating 50k rows of unix time entries auto-incremented 5 mintes each

texts:
I am relatively new at using mysql, or any DB for that matter, so let me know if I'm doing this in a round-about way.

I have 4 similar tables with two rows columns each - unixtime + count. They all look something like this:

mysql> select * from wifiexporttotal limit 5;
+------------+-------+
| epoch      | count |
+------------+-------+
| 1446292800 |   298 |
| 1446293100 |   332 |
| 1446293400 |   375 |
| 1446293700 |   439 |
| 1446294000 |   412 |
+------------+-------+

etc...

These are log entries rounded to the nearest 5 minute interval for approx 6 months with around 50k entries each.

I want to join these in a single table with unixtime + 4 columns of values, and have done so successfully. However, there are some timestamps without a log entry in any of the four tables, and I would like for these to show zero instead of missing skipping rows.

So my question is - How can I generate a table with 50k rows and a single column that starts with a bigint (1446332400 for nov 1st in my case) and auto increments with 300 (5 minutes) per row? I assume I could LEFT JOIN the other 4 tables with this table on the timestamp and get an unbroken string of 5 minute intervals for the 6 months this way.


texts: setting up a local database to be updated from a master database

texts:
I'm looking for an efficient way to keep multiple databases (on different servers) sync with a master database (also on a different server). Mind you I only need a one way sync (from master to all others).

As I was searching I came across multiple solutions.

replication : Which (from what I read) is unreliable, because if the slave servers or down temporarily or anything like that, they end up missing information. (Please correct me if I'm wrong)
Injection   : Which I also think is unreliable, not to mention a security problem because you're directly communicating with the slave databases.

Since I didn't like what I saw, I decided to make my own system (ultimately not liking it either) My system was to make a new table called ORDERS , and would basically hold values that could be translated as "Delete item with id 32 in table someTable" and on the slave side they would follow each of these instructions.

What do you think is the best way? The database is really small (with at most 3000 entries), so I want a really simple solution. Thanks


texts: My master database has multiple personnality issues

texts:
I have a tricky case for you, and I didn't find any similar issue anywhere (I must be bad at searching), so here it is...

I took a backup of the master database from a production server (SQL Server 2014 Std), let's call it SQLPRD, and I restored it, like any non-system database (so I thought), to a dev server (SQL Server 2014 Developer), let's call it SQLDEV.

In the restore statement, I changed the name of the destination database to be master_SQLPRD, and I changed the mdf and log file names to match the new name of the database, so they wouldn't overwrite the DEV master.

I extracted what I wanted from master_SQLPRD, so I dropped it. Then SSMS started throwing me errors as it couldn't find that master_SQLPRD database: now I can't list the databases on SQLDEV, I can't expand the SQL Agent node, etc. I restarted the database engine service, expecting it not to come back online... but it did.

Before I rebuild master (which I didn't backup - it's almost a throw-away instance), is there anything I could try to make master identify itself as master, not master_SQLPRD?

Thank you.

Edit: The error showing in the error log is-

Error: 18456, Severity: 14, State: 73.
Login failed for user 'mydomain\my.login'. Reason: Failed to open the database 'master_SQLPRD' configured in the session recovery object while recovering the connection. [CLIENT: ]


texts: Is it better to have a CASE expression or a JOIN with a dimensional table?

texts:
I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:

SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(timestamp,'week') = 1 then 'Tuestday'...
FROM fact_table

Or I could create a dimensional table like the following:

day_number day_name
0          Monday
1          Tuesday

and then make a JOIN with the fact table:

SELECT day_name
FROM fact_table
LEFT JOIN dm_days
on day_number = date_part(timestamp, 'week')

Which one is better?

What if instead of a date is some indicator (for instance where the traffic comes from: newsletter, SEO, affiliates...) that does not require any function (like date_part) to be obtained? For example, would this be better:

SELECT CASE id_channel WHEN 0 then 'SEO'
WHEN 1 then 'Newsletter'...
FROM fact_table

Than this?
    SELECT channel_name
    FROM fact_table
    LEFT JOIN dm_channels
    on channel_id = id_channel


texts:
I have a MySQL table:

o_id | Salesperson |   fax   | Pincode
1      Sandy         2110271   12011
2      Sandy         2452356   24523
3      Ricky         1023564   45510

I need to get the following result:

Salesperson |   fax   | Pincode
Sandy         2452356   24523
Ricky         1023564   45510

(Note: I don't need o_id column to be displayed)

I have tried:

select distinct salesperson,faxno,pin
from OPPORTUNITY

and:

select salesperson,faxno,pincode
from OPPORTUNITY
Group By (salesperson)

...but still I am getting:

Salesperson |   fax   | Pincode
Sandy         2110271   12011
Ricky         1023564   45510

I have tried a lot of scenarios present on the web but still not able to achieve the expected result.

Is there any property or function that will give "Sandy" -  whose o_id is max.

Example:

Select distinct sales, faxno, pincode
from opportunity where o_id= max(o_id)

Although above code is not working, but anything like that can help?


texts: PostgreSQL full text search on many columns

texts:
I need an advice with searching of record based on specified string.

Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the correct order and also the values of some columns in this string may be missing.

Example of search string:

22 Karntner Wien

And I get for example result with top 5 similar records.

I think I should use full text search, but I have no experiences with it. Can you tell me how to proceed?


texts: DB Locks - How would one handle long transactions and maintain high concurrency?

texts:
We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle our transaction. We're considering of enabling read-committed snapshot on our DB, but according to this answer, it seems that won't really solve anything and is prone to introduce more issues later on.

The whole process of ordering products to shipping is very intricated (The application has been in use for 19 years and has been consistently maintained by numerous developers). Here's a pseudocode of how a batch ordering process uploaded from an excel file is locking up our DB:

Load all items from excel files
Begin Transaction
Loop per item
    Perform all validation checks, such as checking current stocks, authorization to order etc
    (There's a lot of complex checks going on here)
    Insert new item to order table
    If an error occurs during insert, rollback and return
End Loop
Commit Transaction

Since our order table is within a transaction, this means other users must wait until the whole job is done to perform any operation on it. This particularly is causing a lot of issues for warehouse employees, where they must wait for a long time until they can mark the items as shipped on our order table before actually shipping them.

One might say "The transactions should be kept as humanly short as possible", but the thing is, if any error occurs during this order process, the whole job should be rollbacked to initial state. This is part of the program requirement.

I'm not a DB expert, so I maybe missing something critical here but given our requirements, it seems like the only option is to enable row versioning on our transactions.

I'd really like to know how this type of issue could be resolved.


texts:
I am trying to create a stored procedure (SP) in order to archive a table within my production database which is about 1TB.

The production DB runs on Linux SUSE OS and the destination location for the archive is Windows Server 2012.

The SP will recurrently archive 42 days of data from the production table to the  archiving destination table and clean up the production table from the archived data to free up space.

I really don't have the skills to write this stored procedure and also put in place a cron job or a script that will perform this task automatically.

Please anyone to help me out with the above?


texts: Postgres 9.4 - pg_stat_database and others showing no stats

texts:
A database server running Postgres 9.4 shows no statistics when running select * from pg_stat_database - well only the numbackends is non-zero. The stats_reset is null.

I haven't been able to find much as to what should be checked in this case, to find out why statistics aren't reported.

track_counts and track_activities are both set to on.

Per the manual:

  PostgreSQL's statistics collector is a subsystem that supports
  collection and reporting of information about server activity.

I get that, but I can't find how to see if it's working properly.

Even more, the pg_stat_user_tables returns zeros in all columns and null for last_vacuum - even if I run a manual vacuum on the table.


texts:
Reason for lot of small relay log file formation.
I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.


texts: Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?

texts:
We have a HAProxy which balances reads and writes to only one (of three) Galera nodes.

If this balancing works fine, the parameters wsrep_log_conflicts and cert.log_conflicts should never log any conflicts and errors? It won't fill up /var/log filesystem with messages?

What is the drawback of enabling wsrep_log_conflicts and cert.log_conflicts? Why it's not enabled by default?

Please give more info, the only thing I found was DEALING WITH MULTI-MASTER CONFLICTS

  Lastly, you can enable conflict logging features through
  wsrep_log_conflicts and cert.log_conflicts.

# Enable Conflict Logging
wsrep_log_conflicts=ON
wsrep_provider_options="cert.log_conflicts=YES"

  These parameters enable different forms of conflict logging on the
  database server. When turned on, the node logs additional information
  about the conflicts it encounters, such as the name of the table and
  schema where the conflict occurred and the actual values for the keys
  that produced the conflict.

7:51:13 [Note] WSREP: trx conflict for key (1,FLAT8)056eac38 0989cb96:
source: cdeae866-d4a8-11e3-bd84-479ea1a1e941 version: 3 local: 1 state:
MUST_ABORT flags: 1 conn_id: 160285 trx_id: 29755710 seqnos (l: 643424,
g: 8749173, s: 8749171, d: 8749171, ts: 12637975935482109) <--X-->
source: 5af493da-d4ab-11e3-bfe0-16ba14bdca37 version: 3 local: 0 state:
APPLYING flags: 1 conn_id: 157852 trx_id: 26224969 seqnos (l: 643423,
g: 8749172, s: 8749171, d: 8749170, ts: 12637839897662340)


texts: Method to Obtain Lossless Join Boyce-Codd Normal Form (BCNF) Decomposition

texts:
I have been told a way to obtain lossless join BCNF but I don't know how to calculate candidate keys (also called super key[s] in some cases) and trivial dependencies.

I have been given the following relation:

R = (A, B, C, D, E, F)

...where R is relation name, and A, B, C, D, E, F are its attributes.

The given functional dependencies (FDs) are:

A B → C D E F
C → D
E → F

The algorithm says that the first FD is not non-trivial, but here A B is a super key so we don't break original relation based on this.

Moving to the second FD, it's neither non-trivial nor C is candidate key. So, we break the relation:

R1 = (A, B, C, E, F) and R2 = (C, D)

Moving to the third FD it's nor non-trivial neither E is candidate key. So, we break the relation again:

R11 = (A, B, C, E), R12 = (E, F) and R2 = (C, D)

Hence, we obtained Loss Less BCNF.

But, I always get confused on how to calculate candidate key(s) and see if a FD is non-trivial, although I am quite aware of the definitions1. I also googled and read some documents but still didn't understood this properly. Please help me to figure out candidate key(s), super key(s), non-trivial FD(s) in the easiest way possible so that I never struck with the concept again.

Why do we only see left one's (A B, C, E) for candidate key and not the right one's ( F, D, C D E F) in the three FDs written above?

1 My definitions:

Super key: A set of one or more attributes which taken collectively, allow us to uniquely identify an entity in an entity set.

Candidate Key: A super key for which no proper subset is also a super key.


texts: Transform a Lease relation into Third Normal Form (3NF)

texts:
Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:

RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key = {PersonID}

I try:

LEASE ( PersonID, BuildingID )
BUILDING ( BulidingID, RentID)
RENT ( RentID, Rent)

Is it right?


texts: ORACLE timed Datapump on Linux? (No crontab)

texts:
what is the best way to create a timed Datapump Dump of a PDB on a Linux System? No Crontab.

And how do I implement it?


texts: PostgreSQL - Variables on transaction-level

texts:
Do PostgreSQL have variables at transaction level? If not, is it possible to implement something like that relatively easily? Unfortunately, I am still relatively inexperienced with PostgreSQL and have only found out that you can define settings at session level.

I am currently trying to implement a historization like Hibernate has directly in PostgreSQL. A short info for which they do not know Hibernate: Hibernate has a table for revisions.  It contains an ID, the time and other information. For each table there is a "_AUD" table. Every change within the same transaction gets the same revision ID.

To implement this directly in PostgreSQL, I would need a variable that I can use within a transaction and whose value can only be used within the same transaction. My idea is that every time a transaction is historized, a function queries if an ID exists for the transaction, if not, it creates one, otherwise it returns the already created one.

I use the latest PostgreSQL version.


texts: Restoring a backup of a database from SQL Server 2014 into SQL Server 2012

texts:
Is it possible to load a backup of SQL server 2014db into SQL server 2012?

Trying to import/restore is throwing backward compatibility issues.

Thanks


texts: SQL Server 2008 Version of rows between... preceding and current row

texts:
Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008

As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + Jan to July 2017 = 4625

Similarly, the SUM column for August 2017 is equal to the following: Sep to Dec 2016 + Jan to August 2017 = 4625

How can I automate this from month to month?

I understand that this can be done using window functions in SQL Server 2012 onwards but I have to modify this query to work in SQL Server 2008

select year(date), month(date),
   sum(sum(numbers)) over (order by year(date), month(date) rows between 11 preceding and current row) as prev_12_sum
 from t
 group by year(date), month(date)
 order by min(date);

I appreciate any help I can get.


texts: How to get back value passed to HASHBYTES()?

texts:
I want to use the SQL Server HASHBYTES() function.

But I don't know how to get back the value passed to HASHBYTES...


texts:
I have a hierarchy that looks like this:

As JSON in TSQL it is this:

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

This is just an example. The actual data could be any tree of indeterminate depth or breadth.

The documentation and all the examples I've found show top-down traversal. Every JSON path starts at the root node and navigates, by known node names, toward the desired node. I've found nothing about starting at an indeterminate depth within the hierarchy and working upward. I feel like I need something like transitive closure.

Given a name I would like to get the ancestry for that name. For example, given "Emma" the result will be "Emma / Chris / Amy". Given "Bill" the answer will be "Bill / Amy". The output format is unimportant; it can be JSON, a string or a resultset. The names are unique.

This is a learning exercise for myself. It's OK to change the original JSON representation to anything equivalent as long as it's still JSONy. A JSON_QUERY over an adjacency list representation of the hierarchy wouldn't achieve my goal.


texts: Read-only replica of RDS Postrgres instance with modified schema

texts:
We have a Postgres instance hosted in Amazon RDS. The idea is to create some kind of a read-only replica for a separate application. This application requires some modifications of the DB schema - additional indexes, denormalization, etc.

What is the common practice to achieve such goals?


texts: How do I insert record only if the record doesn't exist?

texts:
I have a PaymentInformation table

ID   NAME       Start       End
1    Tina       1/7/2014    1/17/2014
2    Alice      3/7/2014    3/17/2014
3    John       5/7/2014    5/17/2014
4    Michelle   7/7/2014    7/17/2014

I'm building my SQL query dynamically, like this:

SQLiteDataBaseQueryHolder3 = "INSERT INTO " + SQLiteHelper.TABLE2_NAME + "(" +
                            "name, Start, End" +
                            ")VALUES('" +
                            tempName + "'" +
                            ",'" +start + "'" +
                            ",'" + end + "" +
                            "')" +
                            " WHERE NOT EXISTS ( SELECT * FROM " +SQLiteHelper.TABLE2_NAME +" WHERE name = '"+
                            tempName+"' AND Start = '"+Start+"')"

Which gives the following result (formatted for readability):

INSERT INTO PaymentInformation(NAME, Start, End)
VALUES('Tina','01/10/2017','2/10/2017')
WHERE NOT EXISTS (
    SELECT *
    FROM PaymentInformation
    WHERE name = 'Tina' AND duration_start = '01/10/2017'
)

Is this how it should be done?
It shouldn't insert if Tina is already in the table with a Start day of 01/10/2017.


texts: How to determine if a column already exists in a MySQL table before altering the table to add it?

texts:
I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use.

SELECT COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

Unfortunately the DATABASE() function returns information_schema instead of current connection DB at least in PHPMyAdmin. It may be different through the PHP mysql driver. Therefore, it returns zero for the column count.

SELECT DATABASE(), COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

This returns: "Information_schema", "0" despite the fact that the original connection was to a different database.

I have come up with a workaround in PHP but would still like to know if this is possible using only SQL.


texts:
How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way?

There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be done on a site. Some Tasks are to fix a hazard, no task can deal with multiple hazards. Some Hazards have a task to fix them. No hazard can have two tasks associated with them.

The below is the best I could think of:

CREATE TABLE [dbo].[Hazard](
  [HazardId] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Hazard] PRIMARY KEY CLUSTERED
(
  [HazardId] ASC
))

GO

ALTER TABLE [dbo].[Hazard]  WITH CHECK ADD  CONSTRAINT [FK_Hazard_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([TaskId])
GO

CREATE TABLE [dbo].[Task](
  [TaskId] [int] IDENTITY(1,1) NOT NULL,
  [HazardId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED
(
  [TaskId] ASC
))

GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Hazard] FOREIGN KEY([HazardId])
REFERENCES [dbo].[Hazard] ([HazardId])
GO

Would you do that differently? The reason I'm not happy with this set up is that there need to be application logic to make sure that tasks and hazards point to each other and not to other task and hazards and that no task/hazard points to the same hazard/task another task/hazard points to.

Is there a better way?


texts: SQL Server 2012 - Dropping Snapshots takes hours

texts:
We recently did a SQL Server upgrade from 2005 to 2012. We have a nightly job that drops a snapshot of an active database. The snapshots gets created again later in the day. Before upgrading, in SQL 2005 the Job that drops the snapshots used to finish in few seconds. But once the server has been upgraded to 2012, the same job takes close to an hour to complete.

The log files for the job contains a message

  "Nonqualified transactions are being rolled back. Estimated rollback
  completion: 0%. [SQLSTATE 01000] (Message 5060) Nonqualified
  transactions are being rolled back. Estimated rollback completion:
  100%."

This message is repeated several times. While monitoring the underlying database, we have found some blocking on the database during that time. But if I am correct, a blocking on the underlying database should not delay a statement to drop its snapshot.

May I know if I am correct? If so, what could be the reasons for this delay after upgrading to 2012? Any missing settings at the server or database level? Thanks.


texts:
I am trying to perform a point in time on a specific database using a snapshot and mysql's transaction logs. I am using the following to pull statements from the time of the snapshot to the time of recovery:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names

The resulting statements that mysqlbinlog produces include INSERT/UPDATE/DELETE statements for another database that has the form:

INSERT INTO **database**_reporting.tablename VALUES (data);

So functionally I'm getting statements from 2 different databases, one is our production database, the other is our reporting database, differentiated by nomenclature by the '_reporting'. It would appear to me that our application is inserting to the secondary database while still using the primary one and the binlogs associate both statements with the primary database.

Am I correct that mysqlbinlog is going to read out statements for both databases as long as they are executed after a USE primary_database;? Is there a way to separate out the ones for the reporting database using mysqlbinlog? I have searched quite a bit- I may be barking up the wrong tree here. Let me know if I can clarify the question.


texts: Update and Insert Statement taking forever

texts:
I have a database with two columns that is called using the following query:

 SELECT TimesUsed, Word FROM Predictions WHERE (Word LIKE @partialWord) ORDER BY TimesUsed DESC

In my application it is taking a ridiculous amount of time to call this statement

PredTable = PredTA.GetDataByPartialWord(combo)
            If PredTable.Count = 1 Then PredTA.Update(PredTable(0).TimesUsed + 1, combo)
            If PredTable.Count = 0 Then PredTA.Insert(combo, 1)

Indexing does not help because it is only a two column database and is constantly being updated

Is there any other way that I can improve the speed of this statement?


texts: Why I cannot execute this auto generated script with mysql workbench

texts:
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `motivian` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `motivian` ;

-- -----------------------------------------------------
-- Table `motivian`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`user` ;

CREATE TABLE IF NOT EXISTS `motivian`.`user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NULL,
  `password` VARCHAR(100) NOT NULL,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  PRIMARY KEY (`user_id`));

-- -----------------------------------------------------
-- Table `motivian`.`calculation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`calculation` ;

CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
  `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `calc` VARCHAR(100) NOT NULL,
  `user_id` INT NULL,
  `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

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

This is auto generated with mysql workbench but the constraint could not be applied. I tried like this:

CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCESmotivian.user(user_id) ON DELETE NO ACTION ON UPDATE NO ACTION);

but also don't work.....The problem is with the constraint in the calculation table. How it should be written.
This is the error message:

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO A' at line 8
SQL Code:
        CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
          `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `calc` VARCHAR(100) NOT NULL,
          `user_id` INT NULL,
          `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`category_id`),
          CONSTRAINT `user_id`
            FOREIGN KEY ()
            REFERENCES `motivian`.`user` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

the problem is with the constraint. And now I am thinking is it needed at all. I just want to have a relation with the users table but maybe the constraint is not needed when I create the table??


texts: Select only the last record for multiple values

texts:
I have a table called "sessions" with say 4 columns like below
id  name s_time f_time
01  abc  10.15  10.45
02  abc  11.05  11.55
03  abc  12.18  13.46
04  abc  15.12  16.53
05  def  10.01  12.58
06  def  14.06  16.51
07  def  17.43  18.54
08  xyz  09.45  12.36
09  xyz  14.51  15.57
10  xyz  16.23  18.01

How can I get the last f_time for each name?
What I need is:
name f_time
abc  16.53
def  18.54
xyz  18.01

What am trying is this:
select name,f_time
from sessions
where name in ('abc','def','xyz')
order by id DESC LIMIT 1;

but am only getting the finish time for the first name.
MariaDB 10.1.37


texts:
I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c.

Is it better to create another table for doctors,one for nurses,etc or should I connect the appointment table, nurse_schedule, test_table, all directly to the staff table ??


texts: How to correctly define foreign keys to heterogenous dimensions in fact table?

texts:
Question

What is the best practice for defining foreign keys in a fact table to multiple different and mutually-exclusive dimension tables?

Example Case

Business process

I am creating a dimensional model for the business process of inventory movements. These inventory movements can be triggered by three different actions:

orders
returns
internal transfers

Fact / dimensions

I'd like to foreign key to the three 'actions' above that already have corresponding dimensions within our warehouse:

dim_orders
dim_returns
dim_internal_transfers

My current plan is to create a foreign key column for each of these, understanding that only 1/3 of them will join "properly", like so.

# fact_inventory_movements

_key products                abc
quantity                     10
occurred_at                  2020-01-01

_key_orders                  null/placeholder
_key_returns                 null/placeholder
_key_internal_transfers      12345

I'm concerned that this prevalence of mostly-null or placeholder foreign keys has a 'smell' to it that may be improved by a better design.

I've consulted both the Data Warehouse Toolkit and Star Schema: The Complete Reference, but am having difficulty finding an equivalent example. I'd love to be pointed to a specific chapter that explicitly spells this out!


texts: What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

texts:
I am very new to Database Administration.

I face a lot of problems while setting up mysql master-slave replication.

I also face regular mysql replication troubleshooting issues.

Can anybody helps to understand how should i deal with all these?


texts: SSAS : An error occurred when instantiating a metadata object

texts:
Aside from installing SSAS, I have no experience using it.

Our developers are saying that it is no longer possible to create a new aggregation, whatever the cube.

The error occurs when hitting Start.

Here is what I found in the msmdsrv file

Type: 3, Category: 289, Event ID: 0xC1210012

(7/30/2014 10:55:10 AM) Message: Errors in the metadata manager.
An error occurred when instantiating a metadata object from the file,
'\\?\E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml'.
(Source: \\?\E:\MSAS11.DWH_AS\OLAP\Log\msmdsrv.log,
Type: 3, Category: 289, Event ID: 0xC1210012)

The file E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml has 0kb, so should this file be removed?

Any help on resolving this issue would be appreciated.


texts: InnoDB Table has backed up INSERT and UPDATE queries, high CPU

texts:
I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST;

Basically, I keep a table of every logged in customer and update their location periodically. During busy times of the day, I will see this backup where the time to execute is well over 5 minutes. Eventually, they will all process through and the DB CPU and Connections will normalize (CPU will hover around high 90%s).

The queries I run on this table are INSERT and UPDATE only, and those are the ones that will randomly get backed up during a high traffic time of day. I can't seem to figure out why this happens, although it always happens between the 3 hour block of our busiest time.

Here is my database create statement:

CREATE TABLE `active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`public_post` tinyint(1) NOT NULL,
`customer_id` int(11) NOT NULL,
`order_type` varchar(75) NOT NULL,
`clocked_in` tinyint(1) DEFAULT NULL,
`auto_clock_out` datetime NOT NULL,
`clocked_out` datetime NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lat` varchar(60) NOT NULL,
`lng` varchar(60) NOT NULL,
`region_id` int(11) DEFAULT NULL,
`description` varchar(140) DEFAULT NULL,
`date` varchar(15) NOT NULL,
`time` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `clocked_in` (`clocked_in`),
KEY `lat_lng` (`lat`,`lng`),
KEY `customer_id` (`customer_id`),
KEY `region_id` (`region_id`),
KEY `public_post` (`public_post`),
KEY `auto_clock_out` (`auto_clock_out`),
KEY `order_type` (`order_type`),
KEY `clocked` (`clocked_out`,`clocked_in`)
) ENGINE=InnoDB AUTO_INCREMENT=222952 DEFAULT CHARSET=latin1;

Here are the results of SHOW PROCESSLIST;

104224  proddb  172.31.52.67:55238  api Query   848 update  INSERT INTO active (customer_id, clocked_in) VALUES('188878', 0)
105154  proddb  172.31.52.68:53930  api Query   829 update  INSERT INTO active (customer_id, clocked_in) VALUES('188879', 0)
107401  proddb  172.31.25.4:46032   api Query   784 update  INSERT INTO active (customer_id, clocked_in) VALUES('188880', 0)
108101  proddb  172.31.18.216:54461 api Query   771 update  INSERT INTO active (customer_id, clocked_in) VALUES('188881', 0)
108567  proddb  172.31.52.67:56045  api Query   763 update  INSERT INTO active (customer_id, clocked_in) VALUES('188882', 0)
109002  proddb  172.31.32.221:41667 api Query   755 update  INSERT INTO active (customer_id, clocked_in) VALUES('188883', 0)
114304  proddb  172.31.52.67:57048  api Query   659 update  INSERT INTO active (customer_id, clocked_in) VALUES('188884', 0)
114779  proddb  172.31.52.68:55762  api Query   651 update  INSERT INTO active (customer_id, clocked_in) VALUES('188885', 0)
118410  proddb  172.31.18.216:56459 api Query   588 update  INSERT INTO active (customer_id, clocked_in) VALUES('188886', 0)
119345  proddb  172.31.52.68:56702  api Query   567 update  INSERT INTO active (customer_id, clocked_in) VALUES('188887', 0)

Here are some statistics on the number of temporary tables and threads being created during a high traffic time I found while trying to diagnose.

| Created_tmp_disk_tables | 168686 |
| Created_tmp_tables      | 599149

| Threads_connected | 232 |
| Threads_created   | 21566 |
| Threads_running   | 107 |


texts: Differentiate between a row modification and add?

texts:
Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted?

I know minus can extract any rows that were modified in any way (add/delete/edit), but right now I feel the only way to differentiate an "edit" among these 3 is to compare "hard values" shared between corresponding records and detect the column change...

Take below for example:

Table GarageBefore
__________________________
|Make |Color |VIN |Driver |
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |808 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|

Table GarageAfter
__________________________
|Make |Color |VIN |Driver |
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |303 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|BUIC |BLAC  |400 |JAMES  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|

Desired Result

(Modified Existing Rows?)
|BMW  |SILV  |303 |JOHNS  |

(Added Rows?)
|BUIC |BLAC  |400 |JAMES  |


texts: Many-to-many relationship with a one column ‘junction’ table?

texts:
Basically trying to get my head around how to enforce primary / foreign key constraints on what is essentially a many-to-many (M:N) relationship. Or if I should ignore them in this instance...

The schema design below is based on the following.

Shops sell fruit (item)
The fruit for each shop can be retrieved from certain warehouses that have stockcodes
Multiple shops have access to multiple warehouse
Multiple warehouses supply multiple shops

--Fruit Stock levels (PK surrogate key, item and stockcode unique constraint)
DECLARE @stock TABLE(item VARCHAR(50),stockAmount INT,stockCode VARCHAR(5))
INSERT INTO @stock( item, stockAmount, stockCode )
VALUES   ( 'apples',  10, 'SC01' )
        ,( 'bananas', 15, 'SC01' )
        ,( 'apples',  10, 'SC02' )
        ,( 'oranges', 50, NULL ) --no stock code as 50 avaibale from any warehouse

--Stock allocated into stockpiles/warehouses. Each warehouse has a code e.g SC01 Shops have access to certain warehouses. Table keys currently (Composite key on code & shop, can change this?)

DECLARE @stockForShop TABLE(code VARCHAR(5),shop VARCHAR(50))
INSERT INTO @stockForShop( code, shop )
VALUES   ( 'SC01', 'shop1')
        ,( 'SC01', 'shop2')
        ,( 'SC01', 'shop3')
        ,( 'SC02', 'shop9')
        ,( 'SC03', 'shop1')

--1 column ‘junction’ table to enforce primary and foreign key constraints
DECLARE @stock_stockForShop TABLE (code VARCHAR(5))
INSERT INTO @stock_stockForShop(code)
VALUES ('SC01'), ('SC02'), ('SC03')

--SELECT  * FROM @stock
--SELECT  * FROM @stockForShop

--Query to get shops and what stockpile the products can sell to via  from
--using ‘junction’ table (enforce primary key on actual tables)
SELECT * FROM @stock s
LEFT JOIN @stock_stockForShop ssfs ON s.stockCode = ssfs.code
LEFT JOIN @stockForShop sfs ON ssfs.code = sfs.code

--no ‘junction’ table
SELECT * FROM @stock s
LEFT JOIN @stockForShop sfs ON s.stockCode = sfs.code