texts: sp_executesql changes non standard characters to?

texts:
Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran.

sql server 2008R2 & 2012

Example Code

DECLARE @text nvarchar(30) = N'select ''J こんにちは C 你好'''
SELECT @text
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select ''J こんにちは C 你好'''

J= Japanese 'Hi' C = Chinese 'Hi' as processed by google translate for testing purposes

Output

------------------------------
select 'J こんにちは C 你好'

------------
J こんにちは C 你好

------------
J ????? C ??

------------
J ????? C ??

As you can see when ran naturally or the code is selected the output is returned as expected, however once it is passed through sp_executesql it returns as ? characters. Its worth noting if you attempt to send a varchar instead of an nvarchar it errors out so it defiantly uses unicode.

Have tried a few different collations for the input but that doesn't seem to change anything


texts: Ways to Reduce Database Size and possibly improve performace. Untouched enviroment

texts:
I am new to an environment. The databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance.

Unfortunately, I only have Web edition SQL Server 2008 so compression is unavailable. What are some things that I can do, before I ask for SQL and HW upgrades?

I'm hosting multiple sites, they all have the same schema structure, but may vary from customer to customer based on use. I want to reduce the size of the MDF files and the overall usage of the data drive they are on.


texts: Sql agent job step to Rename a file in SQL Server 2000 fails with error " file being used by another process"

texts:
We have a SQL agent job configured on Sql Server 2000, which has an initial step of renaming a backupfile coming from a another server (SQl Server 2000) Say coming from \A\B\X.dat to X_back.dat. till month of Feb process was running fine. However it came to our notice that step started failing with below error

Executed as user: AUTOMATEDLOGIC\mssql. The process cannot access the file because it is being used by another process.  Process Exit Code 1.  The step failed.

The step runs at say 8 AM and the file is delivered to above location by 7 AM. But still we get the above error.

Any suggestions what/where to look for?

Thanks!


texts: How to convert binary string value from error log?

texts:
I'm trying to investigate an issue with a query I'm currently debugging.

The error I get from my client code is the following:

Duplicate entry '\x9F\x98\x9Ern\x06\x8BN\x97\x8D\xB5A\xB5e\x12\xC9-\x9C\xE1\x1Ai\' for key 'PRIMARY'.

How do I convert this binary string value to its original value so I can find out which part of my SQL query it belongs to? My goal would be to take this string and be able to convert it back to its binary(16) equivalent.

I found this MySQL documentation that explains briefly how the conversion works, but I'm not sure how to proceed in SQL.


texts: I have a main table with an autonumber key.  I need to introduce a new table, related to the main table in a one-to-many relationship (i.e. a record in the main table could conceivably link to several records in the new table).  If the new table links to the main table via that autonumber key, then I (or the user) has to supply the corresponding number for each new record in the new table.  Is there a way to automate any aspect of this, so the user doesn't have to keep cross-checking this number when he makes a new entry?


texts:
I want to match 2 pair of fields in where clause

select * from A where (x,y) in (select u,v from B)

Can some one guide me


texts:
We want to grant a user the ability to update their own mail connection profile used by db mail in msdb. For example, when the IT company maintaining the mail server wants to change the password.

We don't want to give them any other rights. The stored proc we have contains the line:

EXECUTE msdb.dbo.sysmail_update_account_sp

...which fails because sysmail_update_account_sp requires sysadmin rights. We are trying to give the permission via signing the procedure. The following code was derived from

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

USE [master];
go

-- DROP CERTIFICATE [MailSetupCert];

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'MailSetupCert')
    BEGIN
        PRINT 'trying to create certificate MailSetupCert'
        CREATE CERTIFICATE [MailSetupCert]
            ENCRYPTION BY PASSWORD = N'OurSp3cialPassW0rd!!'
            WITH SUBJECT = N'Certificate for Managing Special Permissions';
    END

CREATE LOGIN [dbMailAdmin] FROM CERTIFICATE [MailSetupCert];
GRANT CONTROL SERVER TO [dbMailAdmin];#
-- also tried EXEC sp_addsrvrolemember @loginame = N'dbMailAdmin', @rolename = N'sysadmin'

BACKUP CERTIFICATE [MailSetupCert]
    TO FILE = 'C:\TEMP\EmailSetup.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\EmailSetup.PVK',
        DECRYPTION BY PASSWORD = 'OurSp3cialPassW0rd!!',
        ENCRYPTION BY PASSWORD = 'AnotherSp3cialPassW0rd!!'
    );
                                                            -
USE [UserDatabase];
go

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'MailSetupCert')
    BEGIN
        PRINT 'trying to create certificate MailSetupCert from file'
        CREATE CERTIFICATE [MailSetupCert]
            FROM FILE = 'C:\temp\EmailSetup.CER'
            WITH PRIVATE KEY (
                FILE = 'C:\temp\EmailSetup.PVK',
                DECRYPTION BY PASSWORD = 'AnotherSp3cialPassW0rd!!',
                ENCRYPTION BY PASSWORD = 'OurSp3cialPassW0rd!!'
            );
    END

We follow this by:

ADD SIGNATURE TO dbo.ai_UpdateWebSetupEmail
    BY CERTIFICATE [MailSetupCert] WITH PASSWORD = 'OurSp3cialPassW0rd!';

GRANT EXEC ON ai_UpdateWebSetupEmail TO DatabaseUser

When we execute as DatabaseUser:

declare @p10 int
set @p10=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ai_UpdateWebSetupEmail] @ParamWebAppSmtpServer = @p0, @ParamWebAppSMTPPort = @p1, @ParamWebAppSMTPUser = @p2, @ParamWebAppSMTPPwd = @p3, @ParamWebAppEmailMessageDisclaimer = @p4, @ParamSMTPFrom = @p5, @ParamSMTPSSL = @p6',N'@p0 varchar(8000),@p1 int,@p2 varchar(8000),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),@p6 int,@RETURN_VALUE int output',@p0='smtp.gmail.com',@p1=587,@p2='testmail@nosuchdomain.com',@p3='emailpassword',@p4='The Email Disclaimer: IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas. Unless the word absquatulation has been used in its correct context somewhere other than in this warning, it does not have any legal or  grammatical use and may be ignored. No animals were harmed in the transmission of this email, although the kelpie next door is living on borrowed time, let me tell you. Those of you with an overwhelming fear of the unknown will be gratified to learn that there is no hidden message revealed by reading this warning backwards, so just ignore that Alert Notice from Microsoft.',@p5='testmail@nosuchdomain.com',@p6=2,@RETURN_VALUE=@p10 output
select @p10

We get:

  User does not have permission to perform this action.

So how stupid are we being and more importantly where?


texts: Migrating SQL Server 2008 databases to new 2-node WSFC SQL2014 Alwayson AG

texts:
I am currently working on migrating our single FCI Prod SQL Server 2008 Enterprise instance (on Win2008) multiple databases (>2TB total) to a new 2-node WSFC cluster (Win2012 R2 & SQL2014 enterprise) AlwaysOn AG.

The current single FCI prod server will be decommissioned after migration.

I could use LogShipping to migrate from our current single FCI Prod server to the new WSFC cluster Node 1 (Primary).
Then, backup the Node 1 (Primary) db's and Restore on Node 2(Secondary) to prepare Node 2 (Secondary) and then Create/JOIN the AG group.

I would like to reduce the preparation time for the Node 2 (Secondary) i.e. downtime during migration i.e. to avoid separately backing up the Node 1 db's and Restoring on Node 2(Secondary).

If we Logship our current Prod db's to both Node 1 and Node 2 simultaneously, then is it possible to create an AG and JOIN db's on both these Nodes 1 and 2 successfully at the same time?


texts:
I am in the process of trying to move data around via MQTT with MongoDB as my storage engine.

I am using Node-Red to get the MQTT data and store it as JSON objects in Mongo using the mongo node available via the node red library.

Whilst I can successfully create new documents in Mongo with each MQTT message received, I can't workout how to use the UPDATE function in order to append to existing documents. This is the usage guideline:

"Update will modify an existing object or objects. The query to select objects to update uses msg.query and the update to the element uses msg.payload."

So two questions i suppose...

How can I split the MQTT message into a msg.query and msg.payload?

Has anyone here got experience with Mongo and Node-Red that can share some examples?

Tom


texts: How to delete large amount of data in sql server without data loss?

texts:
I've been dealing with million of data deletion in day to day process.
Basically I have 4 tables.

Table_A
Table_B
Table_C
Table_D

I'm attempting to delete data older than 10 days in all tables.

Possibly I will be deleting around a million in each table.
I have created a stored procedure to do these operations.

The steps I have followed to delete the data is

Step 1: Move the recent days (data which I have to preserve) to a temp table

select * into Table_A_Temp
from Table_A
where <<where clause last 10 days to till date>>

Step 2: Rename main table to old table (table with all days data)

exec sp_rename 'Table_A', 'Table_A_Old'

Step 3: Rename temp table to main table (table with data between last days to till date)

exec sp_rename 'Table_A_temp', 'Table_A'

Step 4: Query the temp table with time frame if any new data is inserted during the copy process

Insert into Table_A
select * from Table_A_old

Step 5: Drop old tables

DROP TABLE Table_A_old

Step 6: Create keys and constraints in main table (means renamed table)

code to create primary keys and constraints

Problem:
If I continuously inserting data into table while the store procedure is running I'm losing data for few seconds. (all 4 tables)

Case 1: While renaming table

when I rename the main to old and temp table to main

I'm getting invalid object error (that table is exist error)

Case 2: Two of my tables have foreign key relation
If I insert data before creating constraints and key I'm getting related errors.

How to handle and delete the data properly without losing data.
Please advice the best practices.


texts: Does SQL Server's is_nullable mean anything at all?

texts:
SQL Server has a type flag called is_nullable you can see it on sys.types. Currently, (SQL Server 2019) it's only set to FALSE two types, sys.timestamp and sys.sysname. On sys.timestamp this type seems to accept null. On sys.sysname it does not. Is there any explanation of this behavior?
You can see these types with
SELECT * FROM sys.types WHERE is_nullable = 0;
SELECT TYPEPROPERTY('timestamp', 'AllowsNull');  -- returns 0

And you can use it like this,
CREATE TABLE foo ( a sys.timestamp );
INSERT INTO foo (a) VALUES ( null );

Note this won't work if you use sys.sysname

Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'a', table 'dbo.foo'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

It also will not work if you create a user-defined data type which is NOT NULL,
CREATE TYPE bar FROM int NOT NULL;

Is this just a hint for the user?


texts: Create a secondary read-only copy of DB in a different server

texts:
We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on the prod DB is less. Many of the tables in the Prod DB do not have primary keys and is a SQL Server 2005. Our target DB is SQL Server 2012 Standard (though we can down-grade the destination server if we have to).

How could we accomplish that? We've tried replication and failed because of the lack of primary keys. We also tried log-shipping but the second DB cannot be brought online because of the difference in SQL versions.

Thanks,
Jim


texts:
What are audit tables?

How are they useful?

I came across them reading this article.


texts: Beginner SQL: Unexpected results from GROUP BY

texts:
I have a sidewalk_deficiency table:

create table sidewalk_deficiency (
    def_id number(10),
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

    DEF_ID    INSP_ID  DEF_LENGTH
---------- ----------  ----------
        10          1          .5
        11          1           1
        12          1         1.5
        13          2           2
        14          2         2.5

I can create a query that summarizes the sidewalk inspections by tallying up the deficiency lengths, per inspection:

select
    insp_id,
    sum(def_length) as def_length
from
    sidewalk_deficiency
group by
    insp_id

   INSP_ID DEF_LENGTH
---------- ----------
         1          3
         2        4.5

This works as I had expected. Two rows are output; one for each insp_id.

I understand that the above query does, in fact, provide the desired output. However, I don't really understand why it works the way it does.

Admittedly, when I write queries like this, it makes more sense to me to include  fields such as def_length in both the aggregate function and the group by clause. To me, I know I want to collapse the def_length into a sum, so it seems to me that I should group by it.

select
    insp_id,
    sum(def_length) as def_length
from
    sidewalk_deficiency
group by
    insp_id,
    def_length       --<<--Problem

   INSP_ID DEF_LENGTH
---------- ----------
         2        2.5
         1         .5
         1          1
         2          2
         1        1.5

Of course, as we can see, my thought process is flawed.  When I include the def_length field in the group by, it produces more records than I had expected.

Forgive my ignorance, but why does the second query produce more results than the first? I would have thought that including the field in both the aggregate function and the group by would have grouped the rows as I had planned.


texts: Referential Integrity in schema, cascading the renaming

texts:
In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations.

There should be facilitated integrity of schema, just like referential integrity i.e. cascading on delete and update column/table name.

User should be allowed to rename a column/table only if user has also permissions to the related tables/columns. Then, cascade this change throughout schema i.e. (foreign keys,triggers etc.)

I have tried to post above request on mysql here Link but get error This form is invalid or has expired, please try again shortly

Please guide me Is this a good request . No? then why? Yes? then where should I and how can i submit it?


texts: Most efficient way to filter on two columns in WHERE clause?

texts:
I need to check if a list of people's names are in our MySQL database.

SELECT * FROM tbltrainerinfo WHERE lastname = "Arpay"       AND firstname = "Belly Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Bebelyn"     AND firstname = "Abulog"
SELECT * FROM tbltrainerinfo WHERE lastname = "Cabrera"     AND firstname = "Virgie"
SELECT * FROM tbltrainerinfo WHERE lastname = "Christopher" AND firstname = "Sajonia"
SELECT * FROM tbltrainerinfo WHERE lastname = "Edilberto"   AND firstname = "Galaura"
SELECT * FROM tbltrainerinfo WHERE lastname = "Embanecido"  AND firstname = "Efren"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gertrulyn"   AND firstname = "Natonton"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gina"        AND firstname = "Gindap"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gindap"      AND firstname = "Gina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jeffrey"     AND firstname = "Ladiero"
SELECT * FROM tbltrainerinfo WHERE lastname = "JIMENEZ"     AND firstname = "JUNPORT"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jocelyn"     AND firstname = "Paloma"
SELECT * FROM tbltrainerinfo WHERE lastname = "Ladera"      AND firstname = "Chared"
SELECT * FROM tbltrainerinfo WHERE lastname = "Lago"        AND firstname = "Michelle Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Maratima"    AND firstname = "Untal"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marciano"    AND firstname = "Iloreta"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marianita"   AND firstname = "Gemina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Pala"        AND firstname = "Sittie Norjanna"
SELECT * FROM tbltrainerinfo WHERE lastname = "Untal"       AND firstname = "Maratima"

How can I do one SELECT statement and get people's names if they are in the list?


texts: SQL AlwaysOn Availability Groups network performance

texts:
Our customers are asking to implement our products while configuring AlwaysOn Availability Groups (  between two FCI with Asynchronic  commit ). The DR server is geographically far from the Production server ( e.g . > 300 km ) .
Each customer may use different parts of the product running with different functionality and different load (e.g. different number of concurrent users).
Network latency is obviously one of the factors that will affect RTO and sync functionality with AlwaysOn Availability Groups.
My question is what will be considered a good / bad network response ? Are there any general network guidelines or counters thresholds relevant for Availability Groups?
Will 50 ms round trip be a reasonable threshold as a general guideline?
What else should I take into account regarding network performance when using AlwaysOn Availability Groups?


texts: What happens if the database node's network fails just after commit and before responding to the client

texts:
I know the transaction will be rolled back if connection breaks before commiting.
But what if the commit operation reached the sql server and before it could respond to the client, the network breaks?

The transaction is committed on the sql server, how can the client then know if the transaction was committed or not?


texts: Take database backup of a SQL server 2012 from local SQL Server 2008 R2

texts:
I have a SQL Server 2012 installation that has some issues with SQL Server Agent because of which I cannot schedule a job or a maintenance plan to take backup.

What I am trying to do is to create a SQL Server job on SQL Server 2008 R2 and take backup of the SQL Server 2012 databases. Can someone suggest how to achieve this?


texts: How to aggregate transactions to form a stateful view which can be updated efficiently?


texts: Revoke and Grant Privileges on SQL Server Database

texts:
I have a user that I want to grant all privileges on all database tables except for DROP, TRUNCATE, DELETE, INSERT and UPDATE.
The Database has over 300 tables and I can't do this one by one. I need something like:
REVOKE DROP, TRUNCATE, DELETE, UPDATE, INSERT ON DATABASENAME.* FROM USER1;

But its throwing an error when I put the DATABASE.* with * representing all tables.


texts: Language difference in full text search for numeric strings

texts:
If I create this table on SQL Server 10.50.2550 (SQL Server 2008 R2):

CREATE TABLE test(
  id BIGINT IDENTITY NOT NULL,
  number VARCHAR(10),
  CONSTRAINT test_pk PRIMARY KEY (id)
);

CREATE FULLTEXT CATALOG test_catalog;

CREATE FULLTEXT INDEX ON test
  (
    number Language 0
  )
  KEY INDEX test_pk
  ON test_catalog;
  ALTER FULLTEXT INDEX ON test START UPDATE POPULATION;

INSERT INTO test (number) VALUES ('1111111111');
INSERT INTO test (number) VALUES ('1111111112');

And when the index is populated perform the query:

SELECT * FROM test WHERE CONTAINS(*, '1111111111');

I get this:

id                   number
-------------------- ----------
1                    1111111111
2                    1111111112

(2 row(s) affected)

If I change the language to English (1033), if I only use 9-digit numbers or if I use SQL Server 11.0.3128 (SQL Server 2012) I only get the expected one row.

Do I do anything wrong or is this a bug?


texts:
This is my my.cnf file:

[mysqld]
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1
bind-address = 0.0.0.0

I have clearly mentioned the server-d in my.cnf but also I am getting this error while stopping the slave.

 stop slave;

  ERROR 1794 (HY000): Slave is not configured or failed to initialize properly.
  You must at least set --server-id to enable either a master or a slave.
  Additional error messages can be found in the MySQL error log.

Anyone can please help me?


texts: Grant select to dbo view without select on non dbo tables

texts:
SQL Server 2008 R2

We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database.

We then granted it SELECT on a dbo view - the dbo view however selects data from underlying tables that belong to a non-dbo schema - so we get an error selecting from the view saying access was denied to underlying tables.

How does one configure the user to have select access to the dbo view but not the underlying non-dbo tables?

Is this even possible?

Jordon


texts:
I got the following error in my MariaDB 10.x hostname.err log:

InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

Can someone please explain (as simply as possible ;-D) what a "semaphore wait" is within the context of MySQL/MariaDB?

All I could gather from Mr. Google is that they are "related to internal synchronization between threads in mysqld"


texts: How to "normalize" external data before insertion into "Data Warehouse" like database?

texts:
I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.
There are MILLIONS of email records from an email platform that wasn't too well though out technically. It's data is not normalized.
So for instance one import might be 7 million records where one important field is a subject line (for testing purposes) and that subject line is "Happy Mother's Day One and All, we love Long Texts!" for all 7 million records.
Now add that "7 million of the exact same long text" to about maybe 50 different subject lines. The benefits of normalization couldn't be clearer with this example. Convert the highly redundant long texts to integers, and then store a small dimensional table translating them back to text. Right?
I'm just wondering the exact implementation of this.
I'm going to use an ETL tool (one or another) but I suppose there are multiple ways of doing it. I guess I would attempt to translate the "subject" into an int based on the small dimensional table, and if no match is found, I would take the distinct new records and append them with an auto-incrementing table? Just wondering if this is a common task.
Normalizing smelly data before insertion into a data warehouse. I don't mean cleaning, auditing, profiling --- I mean literal database normalization to shrink the data volume.


texts: Process staging table by getting unique rows only (incremential)

texts:
I had simple problem with etl on staging table that was answered here:

Process staging table by getting unique rows only

Now after apllying solution I spotted that will be unusable in production enviroment due to amount of data (around 1 mln rows daily) and that will be longering execution of etl statement every day by 1 minute at least on my dba infrastructure, then it will be unusable after few months.

Also staging table can't be locked in any time for INSERT operations.

How to do that etl in incremential fashion? like:

open transaction
take rows from staging
process that new rows (eliminate rows that exists in final table, not agaist staging table, like in non-incremential solution answered in thread linked above)
insert new rows into final table
delete rows from staging table taken at point 2.
commit transaction

Problematic is only 3 point, can anyone help?


texts: In MDX what is the ParallelPeriod of UnknownMember?

texts:
Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year]

WITH MEMBER [Measures].[sales last year] AS (ParallelPeriod([Time].[Time].[Year],1,[Time].[Time].CurrentMember), [Measures].[sales])
SELECT {[Measures].[sales], [Measures].[sales last year]} ON 0
, [Time].[Time].[Month].Members ON 1
FROM [Sales]

Now the time dimension has a visible UnknownMember and it shows as [sales last year] the same value I see for january of my last year in the hierarchy.

Why is that so?
How can I avoid it? I mean the sum of all [sales last year] should be the same as the sum of all [sales] over all years except the first.


texts:
Why would you ever use mysqldump to repopulate a table over load data infile?

Here's the back story to my question.  We had a table that was incorrectly partitioned.  It was supposed to be partitioned in 10M row chunks by PK.  After 90M => 100M it started getting partitioned in 100M row chunks (100M=>200M etc).

The most recent partitions had 135M rows between them and we decided it was time to bite some downtime and fix this mess.

The game plan was basically:

1) mysqldump >= 100M

2) drop partitions >= 100M

3) Create desired partioned ranges

4) readd via the mysqldump files

These dumps were done in 10M chunks so we could reload into the downed master and slaves simultaneously and do some sanity checking in between so we didn't get too far in before realizing we were screwed.  This was an append only table that didn't mutate so we were able to have the historical dumps done before hand and scp'd to local copies before the real downtime had to start.  Because of this we added --skip-disable-keys since our version of mysql doesn't allow you to do this per partition and didn't want to continuous rebuild after each chunk so that could have something to do with the performance diff I'm about to lay out.

Some benchmark tests before hand left us with an estimated 90 minutes of downtime.  We were wrong; the mysqldump reloads were taking about 3-4 times longer than expected.

We had some time to just twiddle our thumbs.  Part of the contingency plan was to not drop one of the slaves partitions until the master and everyone else was rebuilt "just in case".  While the rebuild was going we decided to do a test, select * into outfile from the untouched slave for some segments we'd yet to get to, then reload those w/ load data infile.

We made our dump, gzip'd it and copied it over to the machines in process of rebuilding.  To save some overhead of having to uncompress it then read it again we gzip -dc'd it into a named pipe and then loaded from them.

The load data approach was finishing in about 4 minutes/chunk rather than the 12-15 minutes the mysqldump reload was taking.

I know the manual says this can be faster for larger loads but this left me struggling for a reason why we should use mysqldump if our schema is already in place?

P.S. I'm aware of reorganize partition but have found this dump/reload into alteeed schema to be more performant in the past.


texts: How to log SSAS $system.discover_sessions to a SQL Server Database

texts:
We are running SQL Server 2014 and SSAS on the same box.  Every now and then our SSAS service appears to freeze.  When it does I check the msmdsrv.log file and it tells me that spid x is blocking transaction on spid y.

Is it possible to log the results from SSAS $System.discover_sessions to a database on the SQL Server on a regular interval?  I'd like to capture the current session so that when these issues come up I can see the commands that are running and the SSAS instance refuses to allow me a connection when this occurs.


texts: Performance of table locks vs exclude constraints

texts:
Suppose I have a system for reserving vacation properties. One of the tables is reservations. Each reservation has a property_id, a start_date, and an end_date.

I want to prevent conflicting reservations. Eg, if a property is reserved from June 15-20, nobody can reserve it on June 17, or from June 19-22, or any other overlapping period.

I can see two ways to guarantee this:

Option 1: Lock the table for writes, run a SELECT and check the existing reservations in application code, and if it will not create a conflict, INSERT a reservation or UPDATE the dates of an existing one.

Option 2: Use an EXCLUDE constraint.

ALTER TABLE reservations ADD CONSTRAINT no_overlapping_rentals
EXCLUDE USING gist (property_id WITH =,
daterange("start_date", "end_date", '[]') WITH &&);

I'm fairly sure that the second option is more performant overall, because PostgreSQL doesn't have to wait while the application code examines the existing reservations.

But at the database level, what's the performance difference between these two options? And are there any other factors I should consider?


texts: I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer

texts:
When trying to use the SQL Tuning Advisor from SQL Developer I get this error.

Tuning advisor was working some days before.


texts: What database supports over a thousand fields and tables

texts:
I have a requirement to store several csv files with several fields in an database. This data does not need to be searchable but i need to query the data by Id. I will also need SQL to query the data so this may rule out many nosql options. Considering, each csv file is huge I will need to put each csv file in a separate table. i do not need to perform joins, subqueries or anything complicated - I just need to query by Id. Since the database will be getting hit hard with thousands of requests per hour horizontal scaling would be nice. Is there a database that can handle these requirements?

I have checked out MongoDB but it does not support sql. MongoDB would have been perfect if it supported basic sql.


texts: SQL Server Change Tracking and Replication

texts:
My colleagues and I are working on a solution revolving around change tracking…

Our goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system.

Our database is a subscriber (transactional replication). Numerous publishers are pushing transactions into numerous tables.

During our testing, we observed that the entries recorded by change tracking has records grouped into ‘chunks’. I.E. There are many records with the same value of SYS_CHANGE_VERSION.

This is based on the query of select * from changetable(CHANGES my_table, 0)

Observation: When we execute the above query, some of the rows are ‘interleaved’.

For example, we may have 50 row with SYS_CHANGE_VERSION = 100, 1 row with SYS_CHANGE_VERSION = 101 and then more rows with SYS_CHANGE_VERSION = 100.

Our question:

When rows are inserted into the change tracking table/mechanism, can rows be inserted with a lower value of SYS_CHANGE_VERSION than the current maximum value?

We are trying to determine if all we need to do is have a sort order on SYS_CHANGE_VERSION and simple ignore records for the current max value of SYS_CHANGE_VERSION.

Furthermore, once data is read from the change tracking table, can subsequent reads contain entries which have the same ‘maximum’ SYS_CHANGE_VERSION which was read previously.

We are worried about whether or not we can continue/resume the process of pushing transactions to the non-sql system.

As an alternative, we have considered using the replication stored procedures on my subscriber to push changes to my non-SQL system.

Any answers greatly appreciated.


texts: Is it possible to specify a different collation for the data vs the entities within a single database?

texts:
I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used.
The entities and their column names within that same database don't need to be case sensitive and we'd prefer that they weren't for querying purposes.
Is it possible to keep the data itself as case sensitive but allow the entities to be case insensitive within the same database?


texts: Mysql InnoDB relations vanish when exporting/importing to online database

texts:
My issue is the following : I have a local database running on EasyPhp server :

WEB server : Apache/2.4.7 (Win32) PHP/5.5.8
DB server : 5.6.15-log - MySQL Community Server (GPL)
PHPMYADMIN : version 4.1.4

And a remote rented server :

WEB server : Apache/2.4.18
DB server : 5.5.49-0+deb8u1-log (Debian)
PHPMYADMIN : version 4.6.3

When I export my database in a .sql.zip file from the local phpmyadmin it correctly creates a file containing the references creating lines :

Example :

ALTER TABLE `t_transaction`
  ADD CONSTRAINT `t_transaction_ibfk_2` FOREIGN KEY (`fkEvent`) REFERENCES `t_event` (`idEvent`),
  ADD CONSTRAINT `t_transaction_ibfk_1` FOREIGN KEY (`fkSociete`) REFERENCES `t_societe` (`idSociete`);

As well, in the local phpmyadmin "conceptor" tab, I can see the relations displayed with colored lines.

But when I import this file into the remote server (using phpmyadmin) it ignores any relation : they simply vanish from the conceptor window and the "table structure" tab.

All tables (local ones as well as remote ones) work with InnoDB engine.

I tried to recreate the relations with the conceptor It simply doesn't work, no error messages or anything.

I tried to create the relations with SQL query it doesn't work either. I get no error message (Your request has been executed with success) on first attempt and on second attempt it throws the message "Error creating the foreign Key, check data type" as if the relation existed somewhere.

Both columns are INT(8).

The foreign key is an index and the reference is primary.

I made tons of research but this seems to be quite a unique case...


texts: Conversion failed when converting from a character string to uniqueidentifier

texts:
I am creating a Dynamic CRM 2011 Report in SSRS.
I use several joins, And use GUID in joins.
For ex :

Quote.OpportunityId=Opprtunity.OpportunityId

The problem comes from parameters.
I have a parameter which its label is a name and the value is GUID of a record.
I retrieve them from a Dataset.
The query of the this Dataset(named siteDataset) is this :

select site.SiteId, site.Name from Site;

the siteId goes to the value of the parameter and site.name goes to the label of the parameter, the Parameter is named siteId itself.

But when I try to do this in another DataSet :

siteGUIDInAnotherEntity='@siteId'

Give me the subject Error.
I even tried this :

CONVERT(VARCHAR(50), site.SiteId)

in siteId DataSet; But no success.

Any help will appreciated.


texts: SELECT that is null if there is one more register in this date that is not null

texts:
I'm trying to make a query that I can't produce...

This is my table capturainfo

captureid   int(11)
userid      int(11)
date        date
dateinit    datetime
datefin     datetime

I need to select all rows that have datefin=null and is not the last row of the day ( ie there is one more row with date is the same day but dateinit1<dateinit2.

I just can't do it.

Here are some attempts I've made:

The basic one:

SELECT * FROM capturainfo where date < "2015-02-27"
AND datefin is null

it gets me all datefin is null, but I can't know if there is one more row in the same day.

SELECT * FROM capturainfo a where date < "2015-02-27"
AND datefin is null
AND EXISTS ( SELECT * FROM capturainfo b WHERE a.datefin<= b.datefin) AND a.userid = b.userid )

... Well this last one doesn't work.

here is the data sample

+-----------+--------+------------+---------------------+---------------------+
|     id    | userid |    date    |      dateinit       |     datefin         |
+-----------+--------+------------+---------------------+---------------------+
|    0001   | 2034   | 2015-02-20 | 2015-02-20 11:41:31 | 2015-02-20 20:16:07 |
|    0002   | 1789   | 2015-02-20 | 2015-02-20 12:41:31 | 2015-02-20 13:43:34 |
|    0003   | 1432   | 2015-02-20 | 2015-02-20 12:55:33 |         NULL        |
|    0004   | 1432   | 2015-02-20 | 2015-02-20 13:44:21 | 2015-02-20 13:45:31 |
|    0005   | 1444   | 2015-02-20 | 2015-02-20 14:41:31 |         NULL        |
+-----------+--------+------------+---------------------+---------------------+

In this case, query should return id 0003 because user 1432 have made a posterior register in the same day

Anybody knows how to do it?


texts: Architecture for low-latency RESTful server on Amazon EC2 with Node.js and a database

texts:
This is a question for DBAs with a lot of practical experience with large scale deployments.

We are trying to build an app with very low latency. (We really want to create the best user experience, and latency matters in this case.)
The app is fundamentally a RESTful Node.js server that queries a database (OrientDB). Static content/files are hosted elsewhere. Objects are translate into UI largely on the client end.
We are planning on using Amazon EC2 to host the web server and database.
High availability is important as well.
Cost is not a major deciding factor at this time.

What architecture would you recommend?

Here are 2 options, maybe there's more:

Put the webserver (Node.js in this case) on the same EC2 instance as the database. We'd have multiple instances like this for availability and load balancing. In the past, I've used this to reduce latency in high volumes but those system didn't have to scale as dramatically. It was also before placement groups.
Or, have databases on different instances than the REST web server.

Background

The database size is about 50 GB and we need a better solution. Projections look like traffic will be 10x that amount (a 500 GB DB) within 8 months.

It is about 20% writes and 80% reads. There's about 1000 queries per second right now and the rate is increasing.


texts: Writing SQL to check for a role if exist and add member

texts:
I am not to good at writing SQL so please help with below as i am doing something wrong here

I need to alter a role say role_a to add member Domain/SQLAgent

The problem is how can i check or write a query to do this on SQL servers where there is one more role role_b only existing if role a is not there

Basically, how can i write a script to check on server if role a exist alter that role to add member and if role b then do same.

modifying as Q is not duplicate

I want to check if role_a exist then

ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]

else

ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]

Note@ Both the roles do no exist together


texts: UPDATE/DELETE performance on partitioned table

texts:
Hi I'm using table partitioning in my Postgres 9.3.

I've notice that the DELETE and UPDATE queries are much slower when I'm doing them via the parent table.

I see that the constraint_exclusion is working but it's around 10 times slower than deleting/updating directly the table.

Any reason why? Is the partition lookup so expensive??

I have over 300 partitions. Tables are partitioned by client_id.

directly

 (user@localhost:5432) [db01]  [*] # explain analyze delete from events_128 where datastream = 'API';
  Delete on events_128  (cost=0.42..4667.35 rows=13749 width=6) (actual time=55.394..55.394 rows=0 loops=1)
    ->  Index Scan using events_128_datastream on events_128  (cost=0.42..4667.35 rows=13749 width=6) (actual time=0.086..5.396 rows=13821 loops=1)
          Index Cond: ((datastream)::text = 'API'::text)
  Total runtime: 55.432 ms
 (4 rows)

 Time: 55.858 ms

 (user@localhost:5432) [db01]  [*] # delete from events_128 where datastream = 'API';
 DELETE 13821
 Time: 28.673 ms

via parent table

 (user@localhost:5432) [db01]  [*] # explain analyze delete from events where client_id = 128 and datastream = 'API';
  Delete on events  (cost=0.00..4701.72 rows=13750 width=6) (actual time=12.577..12.577 rows=0 loops=1)
    ->  Seq Scan on events  (cost=0.00..0.00 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=1)
          Filter: ((client_id = 128) AND ((datastream)::text = 'API'::text))
    ->  Index Scan using events_128_datastream on events_128  (cost=0.42..4701.72 rows=13749 width=6) (actual time=0.132..6.275 rows=13821 loops=1)
          Index Cond: ((datastream)::text = 'API'::text)
          Filter: (client_id = 128)
  Total runtime: 12.645 ms
 (7 rows)

 Time: 315.475 ms

 (user@localhost:5432) [db01]  [*] # delete from events where client_id = 128 and datastream = 'API';
 DELETE 13821
 Time: 264.968 ms

Table definitions:

                     Table "public.events"
(user@localhost:5432) [db01]  [] > \d events

        Column             |           Type           | Modifiers
-------------------------------+--------------------------+-----------
 id                            | bigint                   | not null
 actions                       | bigint                   |
 app_installs                  | bigint                   |
 dimensions                    | hstore                   |
 ... 20 more varchar or bigint or date
 datastream                    | character varying(255)   |
 datastream_type               | character varying(40)    |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_client_id" FOREIGN KEY (client_id) REFERENCES client(id)
Triggers:
    insert_events_trigger BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE events_insert_trigger()
Number of child tables: 254 (Use \d+ to list them.)

(user@localhost:5432) [db01]  [] > \d events_128
                       Table "public.events_128"
        Column             |           Type           | Modifiers
-------------------------------+--------------------------+-----------
 id                            | bigint                   | not null
 actions                       | bigint                   |
 app_installs                  | bigint                   |
 dimensions                    | hstore                   |
 ... 20 more varchar or bigint or date
 datastream                    | character varying(255)   |
 datastream_type               | character varying(40)    |
Indexes:
    "events_128_unq_id" UNIQUE, btree (id)
    "events_128_channel" btree (channel)
    "events_128_client_id" btree (client_id)
    "events_128_datasource" btree (datasource)
    "events_128_datasource_class" btree (datasource_class)
    "events_128_datastream" btree (datastream)
    "events_128_datastream_type" btree (datastream_type)
    "events_128_rangeend" btree (rangeend)
    "events_128_rangestart" btree (rangestart)
Check constraints:
    "events_128_client_id_check" CHECK (client_id = 128)
Inherits: events


texts: How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task

texts:
Let's assume you have a task FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10 FOO tasks are queued. When the queue is resumed, the 10 FOO tasks will be executed almost concurrently (because there are more workers than tasks).

In this case, I need to ensure that no more than 1 FOO task per minute (time can vary) is performed.

One solution, using Redis, is to take advantage of Redis atomic and the TTL of a key. When a FOO task starts, it checks if the key worker:FOO exists. If does, then it exists, if it does not it sets the value and a TTL to the maximum frequency. This is easy to achieve using SETNX worker:FOO whatever and then using TTL worker:FOO if the previous command returned 1.

Because SETNX is atomic, I won't fall into the case where two FOO tasks are executed because of the race condition between the GET and the SET.

Now the question is: what is the correct way to achieve the same result using PostgreSQL? I can have a table with a key and a executed_on timestamp value, but how can I ensure that there is no case where two FOO tasks are both executed because of the delay between FOO 1 checks the record and writes a lock?


texts: trigger - Sql2008 - Msg 7391 - "no transaction is active/unable to begin trans"

texts:
I have a trigger on my SqlServer, located in server7.

It inserts a data on another server ( server5 ), after insert, as you see the code below:

CREATETRIGGER [dbo].[trgSPTInsereDepartamento]
   ON  [dbo].[tblDepartamento]
   AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [Server5].alfabase.dbo.tblDepartamento (CodDepto,DescDepto,IncluiOutroDepto)
Select ins.codDepartamento, ins.DescDepartamento, 0
From inserted ins where not exists(select 1 from [Server5].alfabase.dbo.tblDepartamento sptsta where sptsta.CodDepto = ins.CodDepartamento)
END

When i make an insert, the SQL shows me this:

  OLE DB provider "SQLNCLI10" for linked server "server5"
  returned message   "No transaction is active.".
  Msg 7391, Level 16,
  State 2, Procedure trgSPTInsereDepartamento, Line 7 The operation
  could not be performed because OLE DB provider "SQLNCLI10" for linked
  server "server5" was unable to begin a distributed
  transaction.

Local DTC properties is all checked ( it allows remote users ).

I've restarded the server and the DTC service. No Luck for me.

I read a lot of posts but none of them helped me.

Thanks for the help.

  update1:

Checked firewall. disabled it but still doesn't work.

DTC is ok ( only server5 is on a cluster. )

I'm really out of ideas guys.

Thank you very much.

  Update 2:

From server7 to server5, I have the problem above. To test, I made a trigger on a database from server4 to server5. and it worked. So, the problem is on server7.

Server7 is not on the Cluster ( server3,4,5 are clustered ).

Thanks.


texts: Poor SSRS Rendering performance in SharePoint 2010

texts:
Compared to running reports locally (in Visual Studio) or in a Native installation of Reporting Services (SQL Server 2012 SP3) we are experiencing very frustrating performance issues when rendering reports in a SharePoint 2010 web-part.

In SharePoint we are using the SQL Server 2012 SP3 Reporting Services Add-In. We have two load balanced web servers, and two load balanced App servers in the web farm, and one database server which holds both the SharePoint databases and our 'Reporting' database.

Fro mthe execution logs I can see that the Data Retrieval and Data Processing times match when the reports are executed both locally and in Native reporting services, however the rendering times can be wildly different. 1 second locally or native, to 30-40 seconds in SharePoint.

I'm aware that in the past there has been documented problems with Report rendering when combining SharePoint 2010 and SQL Server 2008R2 - however, there's nothing to suggest this is still a widely known issue.

Our servers are given a lot of resource, more than enough, 32GB each - so I don't think it's a resource issue. I also don't think we can accept the extra overhead, in terms of added HTTP calls, is the issue. Surely it cannot make the issue that bad?

Are there any gotchas to be aware of when running this configuration? Why is running Report Services in integrated mode so bad for performance?


texts: Proper technique for storing users event data

texts:
I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry standard' method.

Most databases I design have a user table, and then a persons activty is tracked in another table. I understand that the beauty of the database is to have these sorts of efficiencies, but the activity table will gather many many events fairly quickly just from every user using it regularly, thus becoming a huge table fairly quickly with moderate user usage. Is this best practice to just let it grow in this way? Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?

+--------------------+                   +------------------------+
|   UserData         |                   |   Activity             |
+-=------------------+                   +------------------------+
| ID     (auto uint) | <--1-to-many-+    | ID  (auto uint)        |
| UserName (text)    |              +--> | UserID (uint)          |
| Email    (text)    |                   | Timestamp (time)       |
| additional info... |                   | Type (ID to elsewhere) |
+--------------------+                   | additional info...     |
                                         +------------------------+

I just would like to know of where I can improve anything, as to help me learn.


texts:
From postgresql document, I know  vacuum can skip pages that are already marked in  visibility map, so vacuum progress only vacuum pages which  requires vacuum,and we call this partival vacuum; Sometimes  all pages of a table happen to require vacuuming to remove dead row version, we call this whole table vacuum ( not vacuum full ),  My question is: How can we see if a vacuum is a partial vacuum or whole table vacuum, the following is the output of vacuum verbose commond, can we judge from this ?

--vacuum verbose
mydb=> vacuum verbose test_vacuum2;
INFO:  vacuuming "mydb.test_vacuum2"
INFO:  "test_vacuum2": found 0 removable, 774 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 226 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM


texts: decrement all columns entry when row is deleted

texts: I have a column in a table containing the row_number, some kind of id number to identify the row. When I delete one row, I need the other rows to have their row_number decremented, to keep them sorted. What's the best way to do it? I've seen that auto_increment is not good, so what do can I use? Programatically updating all tables entries sounds bad for me, isn't it?


texts:
I have a query that pulls the 3 most recently commented posts on our forum. The issue is is that COUNT(forum_posts.id) as posts, is indexing the entire database of 600,000 posts and the query takes forever to complete. I'm left joining the table like this LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id. Apparently I will have to use another approach to this. Any suggestions?

SELECT    forum_topics.id,
         forum_topics.title,
         forum_topics.date_created,
         forum_topics.updated,
         forum_topics.last_activity,
         forum_topics.category_id,
         SUBSTR(forum_topics.content, 1, 70) as content,
         forum_topics.author_id,
         users.username,
         users.avatar,
         COUNT(forum_posts.id) as posts,
         CASE WHEN forum_topics_seen.user_id = 49 then 1 else 0 end as seen,
         forum_categories.category_order,
         forum_categories.name as cat_name
FROM forum_topics
INNER JOIN users ON users.id = forum_topics.author_id
LEFT JOIN forum_topics_seen ON forum_topics_seen.topic_id = forum_topics.id AND forum_topics_seen.user_id = 49
LEFT JOIN forum_posts ON forum_posts.topic_id = forum_topics.id
LEFT JOIN forum_categories ON forum_categories.id = forum_topics.category_id
GROUP BY forum_topics.id
ORDER BY forum_topics.last_activity DESC LIMIT 3

Result from SHOW CREATE TABLE forum_posts

CREATE TABLE `forum_posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8mb4 NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `editor_id` int(10) unsigned DEFAULT NULL,
  `topic_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102183 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics_seen` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4

CREATE TABLE `forum_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `game` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `icon` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_order` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `forum_topics` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL,
  `content` longtext CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `author_id` int(11) unsigned DEFAULT NULL,
  `editor_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned NOT NULL,
  `date_created` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_activity` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `views` int(11) NOT NULL DEFAULT 0,
  `is_locked` int(11) NOT NULL DEFAULT 0,
  `is_sticky` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_forum_topics_forum_categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7040 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE users
(
  id INTEGER,
  username VARCHAR (30),
  avatar   VARCHAR (30)
);

Mysql version: 10.4.8-MariaDB


texts: NULL value returned when concatenating a string in SQL while loop!

texts:
I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing?

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s += 'hello' + cast(@i as nchar(4));
    set @i += 1;
  end

select @s;


texts: MongoDB: what is the better way to implement shared record with ability to remove it by each user

texts:
I have a MongoDB record, for example, called Letter.

This letter can be sent to some group of users, so many users have an access to it.

A Letter can be sent in a context of Topic that is shared between group of users.

So, the Topic model has the following structure:

_id
name (String)
users_ids (Array of users ids, that can send letters withing this topic)

Also, each user can remove a letter for himself.

I have some concerns regarding how to implement/store this model with MongoDB.

Variant 1:

MongoDB record, fields:

_id (ObjectId)
topic_id (ObjectId)
text (String)
deleted_for_users_ids (Array)

When a user deletes a record then we add his ID to the deleted_for_users_ids array.

To retrieve records I use the following request:

db.letters.find({topic_id: 'X', deleted_for_users_ids: {$nin: 'user_id'}})

It's not good because we can't add an Index for 'nin' filter in MongoDB.

Variant 2:

MongoDB record, fields:

_id (ObjectId)
text (String)
topic_id (ObjectId)
accesible_for_users_ids (Array)

When a user creates a record then the accesible_for_users_ids array will be equal to the topic.user_ids array.

When a user deletes a record then we remove his ID to the deleted_for_users_ids array.

To retrieve records I use the following request:

db.letters.find({topic_id: 'X', accesible_for_users_ids: {$in: 'user_id'}})

It's better, now we can use Index because a filter 'in' can be covered by index.

But I see that indexes for array use a lot of RAM, which is not good.

====

So 2 concerns: RAM and speed.

Do you have any other ideas how to implement logic like that on MongoDB


texts:
I need to create a clone of a database owner.

I I have the following script:

create role oclone with password 'xxx' LOGIN INHERIT CREATEDB CREATEROLE;
grant dbowner to oclone;
alter role oclone set role dbowner;

I'm granting oclone ALL dbowner privileges and as an added check I'm setting oclone to act as dbowner role so it behaves EXACTLY THE SAME, in terms of object ownership and all other rights.

Are there any drawbacks to the above, or is this enough?:

create role oclone with password 'xxx' LOGIN INHERIT CREATEDB CREATEROLE;
grant dbowner to oclone;

Thanks


texts: how to make levenshtein distance faster for mysql

texts:
I am trying to make a similar query faster:

SELECT levenshtein(substring(keywords, 1,5),'texas') as distance
FROM stuff ORDER BY distance ASC

Unfortunately and as expected, the query takes an eternity. How can I accomplish the same thing, but make it fast? The table is 5 million rows.


texts:
I need to run an alter with Percona tool but it gives error:

  The table gts.xml_bookings has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers

So I checked the triggers like this:

  show triggers like 'xml_bookings'\G;

output:

Trigger: booking_status_update2
           Event: UPDATE
           Table: xml_bookings
       Statement: BEGIN
IF (OLD.backofficestatus <> NEW.backofficestatus
       AND left(OLD.booktime,10) <> curdate()) THEN
   insert into booking_status_changes
       set processid=new.processid,
           oldstatus=OLD.backofficestatus,
           newstatus=NEW.backofficestatus,
           website=NEW.website;
END IF;
END

         Timing: BEFORE
         Created: NULL
         sql_mode:
         Definer: root@localhost
         character_set_client: utf8
         collation_connection: utf8_general_ci
         Database Collation: latin1_swedish_ci
         1 row in set (0.00 sec)

  ERROR:
  No query specified

What are the options I have? Eventually I need to use pt-online-schema-change to altering.


texts:
PS C:\Users\administrator.INTERNAL\Desktop> sqlcmd -S SQLMAIN\MSSQLSERVER -i rdmdb.sql
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: Connection string is not valid [
87]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while
 establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I opened the port 1433 to allow remote connections in the firewall. I also disabled the firewall with no success.

I double checked server name and instance and they all check out.


texts: Why does this simple select by primary key show such intensive CPU usage?

texts:
I have advanced performance metrics enabled for my m6g.xlarge MySQL instance in RDS.  I'm noticing that the during CPU spikes (which are expected given our app), that it seems a simple query is occupying the most CPU.
The blue query is:
SELECT * FROM `job_batches` WHERE `id` = "91b0f7a8-0693-4cb4-8647-e7509318fbbc" LIMIT 1 FOR UPDATE

I did an explain thinking that might shed some light on it, but the explain looks fine/efficient to me.

Am I correct in my understanding that this simple query is what's driving a good amount of our CPU usage?.  From a query volume perspective, I would expect to see the same number of SELECT * FROM job_batches... queries as I would say the orange SELECT * FROM channel_subscription.  This makes the blue stand out to me as an area of potential optimization.
The table's architecture is:
CREATE TABLE `job_batches` (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


texts:
I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup.  This is what the book says:

flush tables with read lock
take a snapshot
unlock tables;

THAT's SO NULL!....

Can someone give me MORE information about the snapshots in MySQL?


texts: How to run mysqldump from windows command prompt

texts:
I'm using non-install version of mysql5.1 on Windows.
I have placed "mysql" folder under c:\ and it seems to work fine.

But when I try C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\>mydatabase.sql from command prompt it doesn't work and gives back only Access Denied.
(It doesn't even ask to enter the password.)

How can I use mysqldump from windows command prompt?


texts: Should I add page level compression before adding the primary key, or after?

texts:
Situation

Data warehouse on Sql Server 2008 Enterprise
36+ million row heap (don't ask), with 60+ columns
750k added monthly
No primary key defined (I have now identified one)
No compression

What I'm thinking of doing (in this order)

Add page level compression
Add the PK
Add a number of non-clustered indexes
Do this as quickly as possible

Question

Ultimately: Do I add the PK or the page compression first? (Does it matter?)
If I add the compression first to the table, will any indexes then inherit the table level compression settings? The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

What I'm leaning towards at the moment
-- Add page level compression
alter table     dbo.TableName
rebuild with    (data_compression = page)
;
go

-- Add primary key
alter table             dbo.TableName
add constraint          PK_TableName
primary key clustered   (<Columns>)
;
go

-- Add NC_IXs here
...
...

Ive looked here (PK creation documentation) and here (ALTER TABLE documentation), but can't see anything definitive about whether or not any indexes inherit table compression settings. The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange


texts: Conversion failed when converting the nvarchar value 'value' to int

texts:
I have the following code to pull a number of values from several rows into a single column and pipe-delimit the values.

SELECT DISTINCT P2.PatientID,
SUBSTRING(
    (
        SELECT '| '+ CAST(P1.ProcedureCode AS NVARCHAR(MAX))  AS [text()]
        FROM #Proc50 P1
        WHERE P1.PatientID = P2.PatientID
        ORDER BY P1.PatientID
        FOR XML PATH ('')
    ), 2, 1000) [ProcedureCodes]
INTO #Proc_Concat
FROM #Proc50 P2

I then have a dataset that looks like this:

PatientId   ProcedureCodes

1111111      11100| 17271| 1CHKS| 1CHN1| 1CHNS| 36415| 51798| 69210| 70450| 70544

When I try to insert the values into the final table I receive the conversion error.  I tried casting the value but that hasn't helped.  I also created a table for insertion where the field was nvarchar.

I am using SQL Server 2018.  Any help would be appreciated.

EDIT to answer questions:
Here is the definition of #Proc50

Both P1.PatientId and P2.PatientId are int

For the code that I am using to insert the results of the pipe-delimited procedure, I am creating a table for the insertion.

    CREATE TABLE dbo.tmpWellMedTest (
                    PatientId int not null
                    ,Lastname  varchar(30) not null
                    ,Firstname  varchar(30) not null
                    ,ProcedureCodes nvarchar(MAX) null
                    ,Diagnosis1 varchar(10) null
                    ,Diagnosis2 varchar(10) null
                    ,Diagnosis3 varchar(10) null)

Then the insertion script:

SELECT
 ME.PatientId
,ME.LastName
,ME.FirstName
,P.ProcedureCodes
,ISNULL(ME.Diagnosis1,'')                AS Diagnosis1
,ISNULL(ME.Diagnosis2,'')                AS Diagnosis2
,ISNULL(ME.Diagnosis3,'')               AS Diagnosis3
FROM vwPatientDemographic ME
LEFT OUTER JOIN #Proc_Concat        P
    ON P.LbPatientId = P.ProcedureCodes


texts: Diagnosing high write throughput on RDS Postgres

texts:
I'm using Postgres 9.6.1 on RDS, and seeing orders-of-magnitude higher write throughput than I expect to see.  I'm seeing a WriteThroughput around 40,000,000 Bytes/Second.  I've looked at the following to try to track down where it is coming from:

I installed pg_stat_statements and have been periodically running:

SELECT sum(shared_blks_dirtied) from pg_stat_statements

And it looks like my queries are only dirtying at most about 30 blocks per second.  A block is 8kB, right?  So that's only 240 kB per second.  (I also checked temp blocks and local blocks and I'm barely dirtying them or writing them at all.  I also confirmed that I'm not hitting the limit of unique statements that pg_stat_statements will track).

I'm also looking at my logs, and I see checkpoints running every 5 minutes about this size:

LOG:  checkpoint complete: wrote 8538 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=269.825 s, sync=0.021 s, total=269.921 s; sync files=2349, longest=0.010 s, average=0.000 s; distance=39599 kB, estimate=39599 kB

So that's approximately 40 MB / 5 minutes = 133 kB per second, which is the same order of magnitude I'm seeing in pg_stat_statements.

So I'm a little confused... am I doing the math wrong, or is there somewhere else I should be looking to see what's generating all that write throughput?

One other thing to mention: I have a lot of tables and schemas: approximately 25,000 schemas and 2 million total relations. (And I create / drop new schemas a couple times a minute generally). I've found that autovacuum can't keep up with this, even with very aggressive configuration, so I am manually vacuuming and analyzing tables based on my write patterns, and I've adjusted the autovacuum thresholds upwards so that in practice my manual vacuuming almost always gets there before autovacuum runs on it.  I've confirmed that my manual vacuums do show up in pg_stat-statements, so I'd assume that any writes they generate are already accounted for in my above analysis, but maybe not?


texts: MySql - is possible that performance issues are caused by dynamic SQL?

texts:
We have this MySQL SP, which uses dynamic SQL.
It seems it does NOT perform well under load.

It is possible that this SP becomes slow under load because it uses dynamic SQL?
Can dynamic SQL cause performance issues in MySql (eg because it is not cached by the engine)?

Please note that this SP is called from several other SPs. It passes results to parent SPs using a temp table.

UPDATE - problem solved (see my answer below)

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    end if;

END


texts:
Yesterday I discovered a peculiar thing, it looks like a bug but I'll post it here in case someone seen it before. Testscript:

#!/usr/bin/python

import sys
import getopt
import re
import os
import traceback

import ibm_db

"""
Test of ibm_db in a HADR env
"""

def f1(dbname, username, pwd):
    print "start"
    cfg = (dbname, username, pwd)
    conn_str = "DATABASE=%s;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=%s;PWD=%s" % cfg
    conn1 = ibm_db.connect(conn_str, "", "")
    print "connect done"
    ibm_db.close(conn1)
    conn1 = None
    print "end"

def main():
    dbname = None
    username = None
    pwd = None
    try:
        opts, args = getopt.getopt(sys.argv[1:], "d:u:p:")
    except getopt.GetoptError:
        usage()
    for o, a in opts:
            if (o == "-d"):
                dbname = a
            if (o == "-u"):
                username = a
            if (o == "-p"):
                pwd = a

    f1(dbname, username, pwd)
    f1(dbname, username, pwd)

if __name__ == "__main__":
    try:
        main()
        print('')
    except Exception, e:
        print e
        sys.exit(1)
    sys.exit(0)

Running the script on a standalone database works as expected:

./test_hadr.py -d db1 -u user -p pwd
start
connect done
end
start
connect done
end

Running the script against a HADR PRIMARY hangs on second connection

./test_hadr.py -d db1 -u user -p pwd
start
connect done
end
start
# waits for a couple of minutes

I straced it and it appears to connect to failover on the second attempt, and after a couple of minutes:

write(1, "[IBM][CLI Driver] SQL1776N  The "..., 111[IBM][CLI Driver] SQL1776N  The command cannot be issued on an HADR database. Reason code = "1". SQLCODE=-1776
) = 111

Any clues why this happens, and how it can be avoided? Using the same connection several times appears to be working, but using two different connection hangs.


texts: Merge replication Identity columns for already existing databases

texts:
I'm attempting to setup merge replication with Pull subscriptions (SQL Server 2014 for Subscribers and 2016 for the Publisher). I have a number of databases at remote sites which operate independently, and have been operating independently for years, and we have never had a central server (Publisher) where all remote site data rolls up.

The database schema is essentially the same at all sites (it's the same application after all). What this also means is that, since all DBs have been operating independently, there is definitely going to be duplication of values for Identity columns between Subscriptions (though the actual row data itself will be site specific). Say, Subscriber A Table X has ID column 25 with row data (a,b,c,d) and Subscriber B Table X has ID column 25 with row data (e,f,g,h).

I've been reading up on automatic identity range management, etc., which is all fine if I'm setting up brand new subscriptions. However, here I am inheriting a bunch of Subscriber databases and I have to setup replication so that the data is aggregated at a central location. Which means I am inheriting duplicate identity column values across subscribers. Auto range management is fine for new rows, but I'm not sure what is the best way to manage the identity conflicts that come with the existing databases.

Any help will be greatly appreciated!

Regards,
Vikram


texts: Generate table of all databases and schema with a single psql command

texts:
In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

To be concrete, let me provide an example.

I have two databases in a PotgreSQL database cluster: accounts and people. In the accounts database there are these schema  (beyond the default): hr and finance. In the people database there are these schema (beyond the default): hr, admin, backup.

Is there a single psql command I can run that generates results something like this:

database | schema
-----------------
accounts |  hr
accounts |  finance
people   |  hr
people   |  admin
people   |  backup

Or is the only way to generate this information to connect multiple times and run separate queries?


texts: How to find object name given in a problematic query

texts:
I want to know the name of database object which is running the part of my query which is causing problem

From the awr report, I found one query which is making some performance issue, but I am not able to find that query anywhere in the database. Is there any way to find where that is being used in the DB in Oracle 10g? Like in which view or package or procedure.


texts: Sqlite: strange behavor on index , about group by operation

texts:
CREATE TABLE [newtable](
  [id],
  [HLevel],
  [LeftBower],
  [RightBower],
  [NodeNumber],
  [NodeCount],
  [SortPath],
  [ID:1] INT,
  [parent] INT,
  [name] TEXT,
  [folder] NUM,
  [size] INT);

CREATE TABLE [tally]([N]);

This is the index I used:

explain query plan select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)
as base join newtable on newtable.id> base.lft and newtable.id<base.rgt

output:
id  parent  notused detail
5   0   0   SEARCH TABLE newtable USING INDEX vdsew (folder=?)
12  0   0   SEARCH TABLE newtable USING INDEX dd (id>? AND id<?)

but with group by:
explain query plan select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)
as base join newtable on newtable.id> base.lft and newtable.id<base.rgt group by pid;

output:
id  parent  notused detail
8   0   0   SCAN TABLE newtable
10  0   0   SEARCH TABLE newtable USING INDEX vdsew (folder=?)
23  0   0   USE TEMP B-TREE FOR GROUP BY

As rediculous consequence, If I want to get the group by result, create a temp table to save the result, then select from temp group by. This 2-step takes only about 200ms and the combination takes much much longer, depending on what index I am using. For instance, in this case(the indices I posted here).
This query:
create table temp as select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)

as base join newtable on newtable.id> base.lft and newtable.id<base.rgt ;
select pid, sum(size) from temp group by pid;

takes only 250ms to get the result, while
select pid ,sum(size) from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)

as base join newtable on newtable.id> base.lft and newtable.id<base.rgt
group by pid

takes very very long time, depends on which Index I used, and got the same out. In this case, it freezes forever.(It is because it use folder index. After I delete folder index, the query will use parent index and the time is about 14seconds. Anyway, this is another question. Let's focus why the behavoir of using indecies by  group by operation is so strange first.


texts: Most efficient way to bulk insert or upsert data into multiple related tables

texts:
Given a PostgreSQL 9.4 schema with 3 related tables:

CREATE TABLE a (
  id BIGSERIAL PRIMARY KEY,
  a_name VARCHAR(80),
  a_field INTEGER
);
CREATE TABLE b (
  id BIGSERIAL PRIMARY KEY,
  b_name VARCHAR(80),
  b_field INTEGER,
  a_id BIGINT REFERENCES a ON DELETE CASCADE
);
CREATE TABLE c (
  id BIGSERIAL PRIMARY KEY,
  c_name VARCHAR(80),
  c_field INTEGER,
  b_id BIGINT REFERENCES b ON DELETE CASCADE
);

And a structure of application data of the form:

{
  "an10,10":{
    "bn10,10",
    "bn11,11":{
      "cn10,11",
      "cn11,11",
    },
    "bn12,12",
    "bn13,13":{
      "cn12,12"
    },
  "an15,15",
  "an20,20":{
    "bn20,20":{
      "cn20,21",
      "cn21,21",
    },
    "bn21,21":{
      "cn23,23"
    }
}

The id for each table references the parent in the structure above.

We could insert one record-set using a data-modifying CTE like this:

WITH gp AS (
  INSERT into a (a_name, a_field)
  SELECT 'an10',10
  RETURNING id),
p AS (
  INSERT into b (b_name, b_field, a_id)
  SELECT 'bn11',11, gp.id from gp
  RETURNING id)
INSERT into C (c_name, c_field, b_id)
  SELECT 'cn10',10, p.id from p;

SQL Fiddle

But extending that to the full data structure is tripping me up.  What is the most efficient way to insert all of the data?

And really, an upsert is what would be desired (a.a_name as the key, everything else would be upserted).  But I thought that might be too much.  I was just going to bulk-insert and then cascade delete the old record, using a TOP query to prevent the duplicates from showing up until they could be removed.

It's really structured data all bound to table a records.  Would a JSON type be a better idea?  The data will always be bulk-upserted through table a, but be queried read-only against tables b and c directly, independent of table a (except for the TOP query if we insert instead of upsert).  So I thought providing a normalized form was worth a shot.


texts: Can IS DISTINCT FROM be combined with ANY or ALL somehow?

texts:
Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result?

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo <> any(array[null, 'A']);

 count
-------
     1
(1 row)

select count(*)
from (select 'A' foo union all select 'Z' union all select null) z
where foo is distinct from any(array[null, 'A']);

ERROR:  syntax error at or near "any"
LINE 3: where foo is distinct from any(array[null, 'A']);
                                   ^


texts:
I'm trying to find out how to detect if a backup device exists using T-SQL.

This is how I create my backup device:

EXEC master.dbo.sp_addumpdevice
    @devtype = N'disk',
    @logicalname = N'TestTest',
    @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

but I can't find how to detect if it already exists first.


texts: MySQL ORDER BY id, but keep identical names next to each other

texts:
Suppose I have the following table:

id  name
---------
1   lion
2   tiger
3   lion
4   bear
5   tiger
6   bear

I would like to be able to return results with the most recent id column first, but keep all of the names together, like this:

id  name
---------
6   bear
4   bear
5   tiger
2   tiger
3   lion
1   lion

Is there a simple ORDER BY clause that can do this? Or do I have to do a UNION of some type?


texts: Multiple self Join on Mysql 5.7 - Poor Perfomance

texts:
I have a table containing a lot of geonames and I am querying it with self join in order to get the cross referenced information.

The only issue is that it takes 7/10 seconds for the query to be executed (and locks the table) representing a bottle neck of the app.

I checked other answers but I could not find out a solution for me. I guess I created all the Index that could help me. I am not an expert in MySql so please if you need more info for a better understand feel free to ask.

This is the query:

select  gp.*,
                        gp1.description as desc_adm1,
                        gp2.description as desc_adm2,
                        gp3.description as desc_adm3,
                        gp4.description as desc_adm4
                 from
                 geonames_table gp left join
                 geonames_table gp1 on gp1.country = gp.country
                                        and gp1.geonames_code = 'ADM1'
                                        and gp1.adm1 = gp.adm1
                                        left join
                 geonames_table gp2 on gp2.country = gp.country
                                        and gp2.geonames_code = 'ADM2'
                                        and gp2.adm2 = gp.adm2
                                        left join
                 geonames_table gp3 on gp3.country = gp.country
                                        and gp3.geonames_code = 'ADM3'
                                        and gp3.adm3 = gp.adm3
                                        left join
                 geonames_table gp4 on gp4.country = gp.country
                                        and gp4.geonames_code = 'ADM4'
                                        and gp4.adm4 = gp.adm4
                 where 1  and LOWER(gp.description) like 'lo%'  limit 10;

And it is launched against this table structure:

id  bigint(20)  NO  PRI
description varchar(200)    NO  MUL
alternative_names   varchar(5001)   NO
country varchar(2)  NO  MUL
adm1    varchar(20) NO  MUL
adm2    varchar(80) NO  MUL
adm3    varchar(20) YES MUL
adm4    varchar(20) YES MUL
latitudine  decimal(65,7)   NO
longitudine decimal(65,7)   NO
geonames_class  varchar(1)  NO
geonames_code   varchar(10) NO  MUL

Thank you for your help in advance.

Here the Show Create Table output:

CREATE TABLE `31_geonames_places` (
   `id` bigint(20) NOT NULL,
   `description` varchar(200) NOT NULL,
   `alternative_names` varchar(5001) NOT NULL,
   `country` varchar(2) NOT NULL,
   `adm1` varchar(20) NOT NULL,
   `adm2` varchar(80) NOT NULL,
   `adm3` varchar(20) DEFAULT NULL,
   `adm4` varchar(20) DEFAULT NULL,
   `latitudine` decimal(65,7) NOT NULL,
   `longitudine` decimal(65,7) NOT NULL,
   `geonames_class` varchar(1) NOT NULL,
   `geonames_code` varchar(10) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_nome_place` (`description`),
   KEY `Sigla_paese_place` (`country`),
   KEY `idx_geonames_code` (`geonames_code`),
   KEY `idx_adm1` (`adm1`),
   KEY `idx_adm2` (`adm2`),
   KEY `idx_adm3` (`adm3`),
   KEY `idx_adm4` (`adm4`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: SSMS 2008 / 2014 showing different values for TempDB file sizes

texts:
Here's a fun one.  Last week I reconfigured the initial file sizes in our tempdb database to 1GB each.  We had an event a few weeks back that locked up our server due to a table with no indexes getting absolutely hammered.  This caused our 3 of our tempdb files to grow.  I re-sized them using SSMS 2014 and they should have reset to their configured size this past Sunday when we rebooted the server.  They didn't and now I'm seeing a difference in the information SSMS is returning for those files.

Our server is 2008 R2, here are the different SSMS views:

2008:

2014:

Here are the files as they currently sit on the server itself:

It seems that SSMS 2014 is returning the correct information, but SSMS 2008 is not.  Is this a known issue that anyone has seen before?  If so, what's causing it?  Does 2014 attempt to store this information differently than 2008 R2 and negate the 2008 configured values?

Now, there were no auto-grow events that happened once the server was restarted (checked the default  trace and there were no events)  Given this information, what do you think my best course of action is to get these files re-sized appropriately.  I'm totally fine with growing all the files to 2GB but those tempdb files are hardly being used (<5% usage on all of them).

The servers are not on fire or anything and production is running fine but we definitely want all those tempdb files configured to the same size.

EDIT: Result of requested query


texts: Database replication without locking tables

texts:
I've been asked to setup replication between master and slave database.
The aim is not to lock any tables while dumping database.

I've never setup replication but I have found this guide:

https://plusbryan.com/mysql-replication-without-downtime
He is using these settings.

On master:

server-id=1
binlog-format   = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

On slave:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

Could you please guys advise me if what following settings mean and what impact they will have on whole settings (database is around 35GB)

One more thing. This replication needs to be 'row based' so instead mixed I shall use row option in master settings

innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-slave-updates = 1
read-only               = 1

In don't want to suffer any performance issue or high I/O because of constant disk writes.
As I mentioned I have no experience on settings this up so I'm kindly asking you for help. Many Thanks


texts:
I have a column type of jsonb named changes

THis is the structure of my column.

[["change","is_enabled",[false,true]]]

I want to query within the column.

I want to find all the rows that "is_enabled" = true

I have tried everything. Can you please help me find a solution


texts:
Assume a cash register system where some users have access to a cash drawer, while others do not (they can only close by some kind of card payment).  Keep in mind that a user who closes a check a one time may have access to the cash drawer, but later they may not.

There is a table called check with a column of closedAtDeviceId.

While a check is open, closedAtDeviceId is null.

If a check is closed by a user that has access to the cash drawer, the column closedAtDeviceId is populated with the id of the cash drawer.  This would include if a check is closed by debit, credit, or gift card.

If a check is closed by a user that does not have access to the cash drawer the column in question is assigned the id of the scanner.

Is this a horrible hack?  Yes.
Could this be solved with reasonable DB design?  Yes.
Is this a real world example?  Its to wacky to be conceived in theory.

Is this a violation of some level of normal form?


texts: What would be the most efficient way to check for null values in all columns for multiple tables?

texts:
Say I have three tables or four tables that I want to check for values present and I don't want any of them to be null. IF they are null, I will send an email notification per row. What would be the best way to go about this? Should I make a stored procedure with IF statements separately?

I say this because all these tables are not having any constraints, they just pull data out of an excel sheet. So making a table type variable and unioning or inner joining returns nothing as even the column names are different, but the information they store is the same kind of information. So for example if in one table, a column exists called 'DueDate', in the other table, this same sort of information is called 'PaymentDate'. How would I perform such a check on all these tables?

Update: Here's what I've thought of as of now: Say the two tables are A and B, then

DECLARE @messageBody NVARCHAR(MAX)
DECLARE @iterator int
DECLARE @Rowcount int
DECLARE @varTableA TABLE(tablecounter int IDENTITY(1,1), DueDate date, Account NVARCHAR(20), Retailer NVARCHAR(20), Interest float)

INSERT INTO @varTableA (DueDate, Account, Retailer, Interest)
SELECT DueDate, Account, Retailer, Interest
FROM TableA
WHERE DueDate IS NULL OR Account IS NULL OR Retailer IS NULL OR Interest IS NULL

SET @Rowcount = @@ROWCOUNT
SET @iterator = 1

WHILE(@iterator <= @Rowcount)
BEGIN

SET @messageBody = (SELECT 'No values in following columns' + 'Duedate: ' + ISNULL(Duedate, 'No value') + CHAR(10) + CHAR(13)
+ 'Account: ' + ISNULL(Account, 'No value') + CHAR(10) + CHAR(13)
+ 'Retailer: ' + ISNULL(Retailer, 'No Value') + CHAR(10) + CHAR(13)
+ 'Interest: ' + ISNULL(Interest, 'No Value') + CHAR(10) + CHAR(13)

                    FROM @varTableA
                    WHERE @tablecounter = @iterator)

EXEC msdb.dbo.sp_send_dbmail @profilename = [name], @recipients = [reclist], @subject = [sub], @body = @messageBody
SET @iterator = @iterator + 1
END

And to repeat this in either the same or a separate stored Procedure for TableB and Table C. Is there a better way of doing this or do you think I am going about this the right way?


texts: Stored procedure to compare two columns from different tables and make the insert

texts:
I have two tables:

Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer)
Timesheet with columns fld_id (integer), fld_allocated_time (integer), fld_project_id (integer).

And I have this procedure:

CREATE PROCEDURE sp_insert_allocated_time(p_project_id integer, p_allocated_time integer)
  RETURNS void
  AS
BEGIN
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
from
(
  SELECT p.fld_id, p.fld_allocated_hours t.fld_allocated_time
  FROM dbo.Project p
  INNER JOIN dbo.Timesheet t
  ON p.fld_id=t.fld_id
  where t.fld_project_id = p_project_id
)AS Alias
GROUP BY fld_id, fld_allocated_days, fld_allocated_time
having SUM(fld_allocated_time) < fld_allocated_hours;
END;

All I want to make this procedure to insert the allocated_time value into the table Timesheet using the parameter fld_project_id  if  SUM(fld_allocated_time) from the table Timesheet is smaller or equal to fld_allocated_hours from the table Project, otherwise show an error message and exit.

How can I do this? I think my procedure code is not even close to what I want to do.

Thanks.


texts: Locked out of my own database through invariant() failure

texts:
I am trying to set up mongodb on my own server and connect to it remotely through my laptop.

I had successfully installed the database and created my own user with admin privileges. However, when I shutdown the server and restarted it, I try to run it through

mongod

and get

***aborting after invariant() failure

This error seems to be because I'm not passing in the correct credentials, but I cannot find any documentation on how to log in and start the instance using the new user I created. I've only found very old solutions through searching on my own that no longer seem to apply.

How do I connect to it, and then demand authorization from remote connections?


texts: Report Builder - databases missing in connection properties drop down

texts:
I'm putting together a new paginated report using Report Builder, but when it comes to adding the datasource, there are a number of databases that aren't showing in the drop down after entering the server name.
The account I'm running as in an administrator with full access to the server/SQL databases and all databases are available through SSMS.
Has anyone seen this before?


texts:
Good day,
Im trying to find a good way on my select statement

Table1
AccountID      AccountName    Rate  OldRate
01             Smith          X     X2
02             Providence     X     X2
03             Jones          X     X3
04             Charity        X2    X
05             Smith          X3    X
06             Providence     X     X2
07             SMith          X     X3
08             Charity        X     X3

Table 2
Rate          Amount
X             500.00
X2            200.00
X3            30.00

Result

AccountID      AccountName    Rate  Amount  OldRate Amount
01             Smith          X     500.00  X2      200.00
02             Providence     X     500.00  X2      200.00
03             Jones          X     500.00  X3       30.00


texts: How to validate column while creating Table?

texts:
Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns,

Example :

create table tab_1 (
a integer,
b integer,
Audit1 integer,
audit2 integer)

Should not create the tables without the audit columns , Is it possible by trigger or any other concept.


texts: Using DISTINCT in window function with OVER

texts:
I'm trying to migrate a query from Oracle to SQL Server 2014.

Here is my query which works great in Oracle:

select
count(distinct A) over (partition by B) / count(*) over() as A_B
from MyTable

Here is the error i got after tried to run this query in SQL Server 2014.

Use of DISTINCT is not allowed with the OVER clause

Anyone know what is the problem? Is such as kind of query possible in SQL Server?
Please advise.


texts: select query on unique column AND alternate column of "aliases"

texts:
I have a MySQL table (~10,000 rows) with a pk id (int) column and a name (varchar) column (as well as a few other unimportant columns for this question).

The name column contains unique values that are "official" names however some of the items have a commonly known alias, abbreviation or alternate spelling. I'm guessing maybe 10-15%.

I have an existing query to the effect of:

SELECT id, name
FROM items
WHERE name
LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;

to allow users to search for items with a quick type-ahead widget.

Since some items will have aliases I'd like users to be able to search and get matches based on the alternate names.  My current plan was to add another column to the table called alternates containing delimited alternatives.

e.g. (the delimiter : could be whatever)

  id  |    name               |    alternates
------+-----------------------+------------------------
  1   | Computer              | PC : Mac
  2   | Kraft Dinner          | KD
  3   | Chesterfield          | Couch : Sofa : Sete
  4   | BMW                   | Beamer
  5   | Microsoft SQL Server  | MSSQL Server : SQLServer

and then alter my query to:

SELECT id, name
FROM items
WHERE name LIKE '%keyword%'
OR alternates LIKE '%keyword%'
ORDER BY name ASC
LIMIT 15;

Is this an "accepted" practice or is there a preferred or better performing way to do this?

I apologize if this seems simple to the SQL Pros but Googling for "SQL" and "alias" shows hundreds of non-applicable results (surprise, surprise ;-)


texts:
Our company is moving from SSRS 2012 to SSRS 2017. I would like to know if anyone has done a similar migration. If yes, could you please guide me through the steps?

From what I read about the migration, it looks like its a very tiring process since I will have to download each report from 2012 server and upload it into 2017 server. or use PowerShell scripting to automate the same.

If anyone knows a better way to do it, I am all ears.

Thanks


texts:
I have a table called FunctionWeights which contains data as follows:

My query is as follows:

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchCity'
Insert into #Temp2 select RowNumber,PercentMatch,@constVal, PercentMatch * @constVal from dbo.MatchCity(@City)
select * from #Temp2

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchSSN'
Insert into #Temp2 select RowNumber,PercentMatch, @constVal, PercentMatch * @constVal from dbo.MatchSSN(@SSN)
select * from #Temp2

and so on................ for all function names.

Data-types for columns of #Temp2 are as follows:

Rownumber int not null,
ValFromFunc float(3)null,
FuncWeight float(3) null,
Percentage float(3) not null

However, The #Temp2 table after every insert has only 1 in FunctionWeight column for all the functionNames matched as shown above in queries.

The snapshot of #Temp2 is as follows after insert for any function Name i.e FunctionWeight does not change as it should.


texts:
There is a database owner, I need to add another user with the same privileges. With rights to update/delete/alter, etc.
How can I do it?


texts: Should I declare one or two tables to represent Items and Sub Items?

texts:
I'm facing a dilemma in my database design for an upholstery store.

This store cleans carpets, pillows, car seats, car trunks, couches.
There are chairs and sofas with fixed and removable seat cushions. Car seats and trunks with different sizes. The same occurs with pillows.

From my point of view all of these things are items. My idea is to create a table like this:

CREATE TABLE Item (
    ItemID …
    Name …
    Description …
    Material … -- (like 'cotton', 'wool', 'rayon', 'silk')
    Length …
    Height …
    Width …
);

The items mentioned earlier have sub items. A car has seats and trunk. A chair can have a fixed or a removable seat, a couch can have pillows and so on.

I've read these posts:

Sub and sub sub items organization
What is Parent/Child relationship
Parent/Child relationship best practice
Items with sub items

After reading them, I have some questions:

Is it a good idea to include another column called subItem which references a parent item from the same table? Doing that it hurts performance?
Is this a case of a parent/child table?

I'm wondering what approach fits simpler for maintenance and performance.