texts: How important is the build number on a SQL Cluster

texts:
I did not actually perform this install, nor am I responsible for fixing issues on this particular cluster. I just happen to be checking it and found some things that puzzle me. My point in this is trying to understand why it is working, at least to some respect.

OS: Window Server 2003 Enterprise (64-bit)
SQL: SQL Server 2005 Enterprise (64-bit)

Started with a 2 node active/active cluster: Server1 running Instance1, then Server2 running Instance2.

Instance 1 and Instance2 are at the service pack 3 build number for SQL Server 2005 (+ a few hotfixes). I believe it is 9.00.4053. Two new servers are built to replace Server1 and Server2. So the servers were built and added into the cluster, Server3 and Server4. Since I did not do the installation I am assuming to a point that the person followed the steps to add a new SQL Server node as described here in BOL:
http://msdn.microsoft.com/en-us/library/ms191545(v=SQL.90).aspx

I found that Instance1 and Instance2 are now both running on Server3. However the build number of the instances now show to be 9.00.1399, which is an installation unpatched. The instances are active and still running on this RTM build of SQL Server within the cluster. ?????

My thought process here is you cannot take a backup of master database and restore it to an instance that is at a lower build number than what the backup was taken from. So if you just go on that point, how can a cluster instance failover to a node that is not at the same build number? Why would SQL Server (or Microsoft) even allow it to do this?

Also the last step in the BOL article linked above has "All nodes of a failover cluster instance must be at the same version level." I cannot find anything that states what happens if you are not. The only thing I have found on Instance2 is the SQL Agent jobs don't seem to be working anymore and when the databases are being brought online there is a stack dump from something like the IO listener (I don't recall the exact message showing). I have also seen a few messages referencing IO write issues.

The databases themselves are online and appears the applications are functioning as desired. They have been running in this manner for the past week or so. Any thoughts?


texts: Problem with SELECT CASE WHEN THEN, 0 result

texts:
Need an advice in  SELECT CASE WHEN THEN. In main, removed all unimportant function, usual select i.fav_id FROM users; i see original table:

fav_id
1
2
6
7

but i need change some id's for result will be:

fav_id
1
3
6
8

i do:

SELECT i.fav_id =
        CASE
           WHEN 2 THEN 3
           WHEN 3 THEN 2
           WHEN 7 THEN 8
           WHEN 8 THEN 7
           ELSE i.fav_id
       END fav_id
FROM `favorites` AS i WHERE i.member = '123456'

i got:

fav_id
0
0
0
0

i'm new to select CASE selection but after searching alot manuals and examples, and did not fund how to solve my problem. thanx for your time !

used:
libmysql - mysqlnd 5.0.11-dev - 20120503
PHP: 5.6.28-1+deb.sury.org~xenial+1
phpMyAdmin  4.5.4.1deb2ubuntu2


texts: Restoring database without worring about partitions

texts:
On our our production database there is a partitioned table called FullLog. This is updated via triggers so we know who changed the database and when. Which is a problem for me when I restore the production database on our development servers as it doesn't have an F drive. Is there a easier way I can tell it to move to the specified location without knowing how many ndf files are there.

RESTORE DATABASE MartinUAT
  FROM DISK = 'C:\DBBaks\MARTIN_PreChange_2015_06_09_0800.bak'
  WITH MOVE 'CMTS_dat' TO 'e:\SQLRestore\MARTIN_UAT_.mdf',
  MOVE 'P1' TO 'e:\SQLRestore\MARTIN_UAT__1.ndf',
  MOVE 'P2' TO 'e:\SQLRestore\MARTIN_UAT__2.ndf',
  MOVE 'P3' TO 'e:\SQLRestore\MARTIN_UAT__3.ndf',
  MOVE 'P4' TO 'e:\SQLRestore\MARTIN_UAT__4.ndf',
  MOVE 'P5' TO 'e:\SQLRestore\MARTIN_UAT__5.ndf',
  MOVE 'P6' TO 'e:\SQLRestore\MARTIN_UAT__6.ndf',
  MOVE 'P7' TO 'e:\SQLRestore\MARTIN_UAT__7.ndf',
  MOVE 'P8' TO 'e:\SQLRestore\MARTIN_UAT__8.ndf',
  MOVE 'P9' TO 'e:\SQLRestore\MARTIN_UAT__9.ndf',
  MOVE 'P10' TO 'e:\SQLRestore\MARTIN_UAT__10.ndf',
  MOVE 'P11' TO 'e:\SQLRestore\MARTIN_UAT__11.ndf',
  MOVE 'P12' TO 'e:\SQLRestore\MARTIN_UAT__12.ndf',
  MOVE 'P13' TO 'e:\SQLRestore\MARTIN_UAT__13.ndf',
  MOVE 'P14' TO 'e:\SQLRestore\MARTIN_UAT__14.ndf',
  MOVE 'P15' TO 'e:\SQLRestore\MARTIN_UAT__15.ndf',
  MOVE 'P16' TO 'e:\SQLRestore\MARTIN_UAT__16.ndf',
  MOVE 'P17' TO 'e:\SQLRestore\MARTIN_UAT__17.ndf',
  MOVE 'P18' TO 'e:\SQLRestore\MARTIN_UAT__18.ndf',
  MOVE 'P19' TO 'e:\SQLRestore\MARTIN_UAT__19.ndf',
  MOVE 'P20' TO 'e:\SQLRestore\MARTIN_UAT__20.ndf',
  MOVE 'CMTS_log' TO 'e:\SQLRestore\MARTIN_UAT__21.ldf'
  ,REPLACE


texts: Oracle: How to create a not null column in a view

texts:
Given a table like:

CREATE TABLE "MyTable"
(
  "MyColumn" NUMBER NOT NULL
);

I want to create a view like:

CREATE VIEW "MyView" AS
SELECT
    CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn"
FROM "MyTable";

Only where the column "MyColumn" is "NOT NULL".

In SQL Server this is pretty straight forward:

CREATE VIEW [MyView] AS
SELECT
    ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn]
FROM [MyTable];

However the Oracle equivalent results in a "NULL" column:

CREATE VIEW "MyView" AS
SELECT
    NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn"
FROM "MyTable";

Is there anyway to force Oracle to mark the view's column as "NOT NULL"?


texts: What is DEALLOCATE pdo_stmt_00000001 in pg_stats_activity "query"?

texts:
In my return from pg_stat_activity, there are many processes in the "query" column that have the value "DEALLOCATE pdo_stmt_00000001".

What does it mean?


texts: How do you get the default compatibility level in Azure SQL Database from T-SQL?

texts:
In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get the default compatibility level new databases will be created with.

In Azure SQL Database, neither of these work; the server version stays at 12 even though the maximum compatibility level is 140, and there isn't a model database to check against.

How do you find out what the current default compatibility level is in Azure SQL Database, from T-SQL? In other words, I want a T-SQL query that would have returned 130 if run before 18th Jan 2018, returns 140 if run today, and will return 150 whenever Azure updates to a default compatibility level of 150.


texts: Add partition works but not the drop partition

texts:
We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any reason for this?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;


texts: Should I create an enumeration or individual flags?

texts:
I'm creating a new database table which will be used to store pictures of products. I'd like to identify the product "type" represented in the picture.  Say we have 3 general categories for all the products:

tables,
chairs,
and lamps.

Am I better off creating 3 boolean columns, is_table, is_chair, and is_lamp?

Or am I better off creating an enumeration, and then using a single column for that enum:

CREATE TYPE product_type AS ENUM ('table', 'chair', 'lamp' );

(Note that with the engineering product pictures we're storing, it is guaranteed a product/picture will never qualify as 2 types.  It must always be 1 very specific type.)


texts: Best practices with large amount of data

texts:
I'm building a solution and I have a question.

All my data is currently stored in a unique table. Each row of data is associated with a type.

My question is, what's the best practice for defining a table structure: all data in an unique table with associated type or data stored in dedicated type table?

eg: how to store people and associated colors.

all data in an unique table like id, people_id, color_id
data stored in table dedicated to color:

table blue: id, people_id
table red : id, people_id

In my opinion, it's better to have everything in a single table to avoid complex queries, but I'm scared about query time with billions of rows.


texts: Mongo zoned sharding with a single value

texts:
We have a database with around 50 collections, and 70 million records. All records belong to specific customers and have a customerId property. Currently all clients are in the USA. However, we're in the process of adding EU clients and would like to host their data in an EU datacenter.

Since all records have a customerId (which is a string) and virtually all of our queries also specify this customerId this makes it a good choice for a shard key for us - we're only going to have one shard per zone, and we'd like all of a customers data to be in that one shard.

My question is, given that the customerId's are strings, how do we specify a minimum and maximum for the sh.updateZoneKeyRange() function? Obiously minimum will be the customerId (e.g., "customer-somename") but how do we specify a maximum of "customer-somename" + 1? The issue here is that maximum is exclusive, so it can't be the same as minimum


texts: SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?

texts:
Is there a difference between a plain SQL function doing a SQL query:

create function get_sports() returns setof sport as
$body$
    select * from sport;
$body$
language sql stable;

and PLPGSQL function returning that same SQL query:

create function get_sports() returns setof sport as
$body$
begin
    return query select * from sport;
end
$body$
language plpgsql stable;

with regards to performance? In which situation should which version be used?

And what if we pass arguments, does that change anything? Such as:

create function get_sports(status int) returns setof sport as
$body$
    select * from sport where status = $1;
$body$
language sql stable;

create function get_sports(status int) returns setof sport as
$body$
begin
    return query select * from sport where status = $1;
end
$body$
language plpgsql stable;


texts: Schannell: Certificate received from the remote server was issued by an untrusted certificate authority

texts:
i'm not sure whether to ask this on ServerFault or here.

Periodically following error is logged in Windows Server 2008 Administrative events:

  The certificate received from the remote server was issued by an
  untrusted certificate authority. Because of this, none of the data
  contained in the certificate can be validated. The SSL connection
  request has failed. The attached data contains the server certificate.

Log Name: System
Source: Schannel

Followed by:

  The following fatal alert was generated: 48. The internal error state
  is 552.

Log Name: System
Source: Schannel

If i change the Force Encryption property in SQL-Server Configuration from "No" to "Yes"(see image below) the error would not be logged anymore.

But i don't really need SSL-Encryption since all connections are trusted(from intranet) and ports 1333 and 1334 are firewalled from internet.

Would it be a performance impact if i would force encryption and generate a server certificate, is it recommended at all in my situation? I don't want to enforce encryption only to prevent from some event logs if it not even causes serious connection problems(where can i see which connection has caused it?).

Q: Can somebody please explain why these errors are raised and wherefrom?


texts: Make a Copy of a Microsoft SQL Server 2005 database from a Mirror Copy

texts:
I have a SQL mirror database in Microsoft SQL Server 2005. I would like to get a copy of it as a new database so I have the ability to read, write, and backup the database.

I cannot break the mirror and ALTER it to become a live database. This is a business requirement that we always have a copy on the other server.

I need a method to pull the data and all other parts of the database (triggers, stored procedures, etc) out of it into another database without breaking the mirror. I have tried SQL Server backups, which do not work on mirrors or snapshots of mirrors, and running through the copy database wizard which also did not work.

Is there any way to get a copy from the mirror database?


texts: Not able to search on zipcode column a table in postgresql

texts:
Hello I have a postgresql question about searching on zip codes.  Basicly, if I do a:

select * from my_table where zip_ = '90210';

I am returned 0 rows.  I know for a fact that there is thousands of rows with that zip code.  The table itself is over a million rows.

    Column        |       Type        |                      Modifiers
---------------------+-------------------+-----------------------------------------------------
...
 zip_             | character varying |

Here is my table and I am not able to search effectively on zip_ column.
  The table does return a search if I use the like keyword along with % char, but that is not what I need.

my questions are:

why when i search on zip_ lets say '90210' it comes back with 0 rows when I know there is thousands of records with the zip code of '90210'?
if i change the type to int would that make a difference?
do i need an index on the zip_ column?


texts: Transactional Replication to DB on Same Server instance

texts:
We have a SQL Server 2012 instance that contains a database with data for many product lines.

We are looking for a way to take subsets of this data into another database on the same instance.

This doesn't have to be continuous as we are looking for ways to make it easy to export backups of the subsets that we can send out to vendors (we have a company that loads this data into their own sql server to produce a product we sell, naturally its huge and we want to trim it down to just the product lines they work with on a regular enough basis) or use to load SQLite DB's for use with offline applications.

ProductDB - the superset of all product line data.

ProductLine1DB - A subset identical in structure to ProductDB, but with data only relevant to product line 1.

ProductLine2DB - A subset identical in structure to ProductDB, but with data only relevant to product line 2.

This can be done nightly, weekly, as often as we need to but not a continuous replication.

We don't want to add in distributor servers or even extra subscriber servers. We would like the replication to happen on the same server with the subscriber databases being on the same server.

Is this a good candidate for transactional replication?

Why or why not?

We obviously have other options to load this data, but it seems like this might be a good case to use something like transactional replication. I've heard its powerful but prone to issues when not setup optimally.

Note: I am not a DBA, simply a developer, but we are currently looking for a new DBA and some of us are taking over duties like this temporarily.


texts:
I'm new to MySql partitioning and have a question regarding indexes.
let say i have the following table:

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `member_rating` int(11) DEFAULT '0',
  `persona_id` int(11) NOT NULL,
  `high_value_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `user_id` (`user_id`,`persona_id`),
  KEY `member_rating_index` (`member_rating`),
  KEY `persona_index` (`persona_id`),
  KEY `high_value_members_index` (`user_id`,`high_value_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

to my understanding, everything will work fine as long as i query the table while providing user_id, my question is: how will the table perform when i'll make a query with out user_id, say something like:

SELECT * FROM members where persona_id=3

any ideas?

p.s. I'm running on MySql 5.1 / innodb


texts: Replace value using several criteria in another table

texts:
Brief illustration :
If I want to replace a single string using multiple criteria from another table, I can use this statement :

-- assume table_2 has column 'value' containing records such as 'd', 'f','h'.
declare @x varchar(10)
set @x = 'abcdefghij'

Select @x = replace(@x,value,'') from table_2
Select @x
-- the result will be : abcegij.

Question :
How do I implement this if I have many initial strings i.e the 'abcdefghij' one from above example, stored in a table (table_1) and would like to be replaced for any occurrence of strings contained in another table (Table_2)

Edit :
DBMS being used in this question is sql server 2008 r2.
My current solution is somewhat similar to what JoseTeixeira has proposed i.e using cursor, manually iterating each record in table_1, process it using above snippet and then store each result into a temporary table. In other words, this temporary table will hold all records from table_1 which have been cleansed (replaced for any string occurrence) using "dictionary" in table_2.

And perhaps this is the most important thing I have missed when asking this question.. I'm looking for a solution without using cursor iteration.


texts: A Tool to Manage Tracking Query Performance Improvements

texts:
I'm building a BI system on SQL Server 2012. I have a test set of data with some tens of millions of rows.

Currently many functions and stored procedures are unacceptably slow. I'm looking for a tool I can use to automatically compare query times from previous executions as I run and re-run these queries and make performance improvements.

I want to tinker, measure, compare execution times, repeat.

For example, I want to be able to see a report showing what query times were when run on 7/31/2013. Then after I make some changes, I want to kick off the tool again on say, 8/1/2013. I'd like to see side-by-side the difference in query times between all of the historical executions.

I know I can track query times manually with SET STATISTICS TIME ON. I've also seen stored procedures others have written to manually track the performance of one or two queries. But the process is very manual.

I'm looking for a tool that I can enter the 30 functions and stored procedures I want to test into (or give it a trace), and then it will do the work of kicking of the queries 5 times (or replaying the trace), record the average time each query took to execute, and compare those times to previous executions.

Regarding Similar Questions

I've looked at:

Testing stored procedure scalability - My question is not about stress testing. (Although the RML tools mentioned are VERY close to what I'm looking for. If all else fails, I will use the RML tools.)
How to profile stored procedures - I know how to profile a stored procedure and use tools like SQL Profiler. Profiler won't give me comparisons to previous executions.

The RML tools are very close to what I want, as one can load traces into multiple DBs and view the results side by side.

But is there any tool that automates: replaying sql statements and viewing the performance diff of different iterations side-by-side? (The major limitation with the RML tools is that the trace from each iteration has to be loaded into a different database and the side-by-side comparison is only possible by opening two instances of Reporter and switching back and forth between the two of them. Ten different databases and ten different windows for ten different executions seems unwieldy...)


texts:
When creating a foreign table in PostgreSQL 9.6, comments (COMMENT ON smth IS "Text.") are not copied from the source table/view to the destination foreign table.

How do I import the comments, without processing every table by hand?


texts:
I have a need to model the following requirement for a web application (PHP/MySQL5):

A user can compete in a distance-based challenge which consists of entering progress along a defined round-the-world route

A JS map displays an individual user’s progress in distance based on the
distance they have covered in the challenge relative to the total
target distance (a user updates distance covered periodically)
A Challenge can have many lat/long points through which the
challenge route travels
More than one user can participate in the same challenge

I have roughly sketched how I would do this but would appreciate advice on the DB design. Initially there will actually only be one pre-defined challenge as this is for a prototype but I would like to model the data in such a way that it can cope with future development e.g. Users challenging other users to a 'race;' i.e participating in the same challenge and displaying both their paths on the same map.

Tables:

challenges

id
title
total distance

challenge_coordinates

id
lat
long
challenge_id

challenges_users

user_id
challenge_id
distance_covered

I already have a user-management system in place with a users table.


texts:
I am having Java Application with Two nods in cluster however I have Postgresql 1 Master Database server and 3 nodes in Hot-Standby Mode.
Problem: Since DB is a single point of failure, every time patching the servers I need to bring down the entire Application which increases my Application downtime.
Looking for a Solution to implement Postgres DB Autofailover to Stand By node.
My Environment: Ubuntu 18 OS with Postgres v10
Questions:

Postgres 10 Supports Auto Failover?
is it possible to implement AutoFailover of DB using Postgres native tools?
I am planning to configure my application to point to single LB IP which is configured to connect to an active DB server out of 4 Postgres DB serves( Out of 4 one is master). Any recommendation on this is welcome.
How to configure Auto failover for Postgresql DB with minimal failover time?


texts:
I´m trying to calculate the Effective Lock %, like MMS does. I think I could get it via MMS API, but most of our mongo instances currently are not monitored in MMS (and probably never will).

All I have is a very rudimentary globalLock calculation using the GlobalLock totalTime and lockTime, but these kind of metrics does not exist for the individual locks and Im not really sure how to interpret a calculation like that with the timeLockedMicros and timeAquiringMicros. Could you guys point me towards the light?


texts:
When executing a simple select query on a SQL Server 2012 system, the execution time is very different if I use OLE or ODBC 32bit. Unfortunately I am forced to use ODBC 32bit.

The Query:

SELECT
  ID_Subjekt,
  Adresse,
  Vorname,
  Name,
  Strasse,
  HausNr,
  HausNrZusatz,
  WohnungsNr,
  PLZ,
  Ort
FROM kunde..VR_ISE_d3_Adressen
WHERE ID_Subjekt = '8751'
AND Adresse = 'Fritz, Furrenstrasse 8B, 8840 Einsiedeln'
AND Name = 'Züger'
AND Vorname = 'Fritz'
AND Strasse = 'Furrenstrasse'
AND HausNr = '8'
AND HausNrZusatz = 'B'
AND WohnungsNr = ''
AND PLZ = '8840'
AND Ort = 'Einsiedeln'

Using OLE it takes some ms (92 Reads). The same query with the same user needs over a million reads (12 seconds) when I use ODBC.

Can someone please point me in the right direction for solving this performance issue?

What I also do not understand is why the two connections lead to different execution plans in the db? I have uploaded the two execution plans to my webserver: http://goo.gl/8LBJpk


texts: Migration error: "Invalid date literal detected" when reading decimal


texts: We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for us.


texts: Why SQL Server Standard 2017 is using more memory than limit described in documentation?

texts:
Documentation says:

  For example: A Standard Edition of SQL Server has buffer pool memory
  limited to 128GB, so the data and index pages cached in buffer pool is
  limited by 128GB. Starting with SQL Server 2016 SP1, you can have an
  additional 32GB of memory for Columnstore segment cache per instance
  and an additional 32GB of memory quota for In-Memory OLTP per
  database. In addition, there can be memory consumed by other memory
  consumers in SQL Server which will be limited by "max server memory"
  or total memory on the server if max server memory is uncapped.

We are using columnstore so I expect that SQL Server will be limited by using 128+32 Gb of memory. Memory optimized tables are not used.

But in reality more than 215 Gb is being used for buffer pool:

Memory Clerks

Overall sql process takes ~300Gb.

  select * from sys.dm_os_process_memory

Max memory set to 360Gb. Server has 380Gb.

What am I missing?

Version:

  Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
  Mar 12 2019 19:29:19   Copyright (C) 2017 Microsoft Corporation
  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0
  (Build 14393: ) (Hypervisor)

UPDATE (2019-04-26)
Here is interesting thing - MEMORYCLERK_SQLBUFFERPOOL is presented by two records. Row #3 is exactly what I'd expect to see for Standard Edition. But what is the first row?
Server has 2 processors with 48 cores total.


texts:
In the recent time we have found that the bin log is getting corrupted, 20% binlog files were having errors, which couldn't even pass the sanity check by mysqlbinlog -u user -p mysql-bin.000021 | head -5.

Consider me new to MySQL, Is there a way which can reduce binlog corruption or some parameter which is safe for binlog/replication. What is the best way for replication, Master-Master or Master-Slave? Is there a way to get auto backup weekly or monthly.

Edited as per request

[root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000089 | tail -n 5 | head -n 1
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 541414465, event_type: 116
ERROR: Could not read entry at offset 184469: Error in log format or read error.
DELIMITER ;

[root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000086 | tail -n 5 | head -n 1
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: -736930139, event_type: 71
ERROR: Could not read entry at offset 668651590: Error in log format or read error.
DELIMITER ;


texts: Is it dangerous to use Linked Server? (SQL Server 2008)

texts: I wonder what are the security risks in using Linked Server.


texts: I got database oracle 11g on my localhost on laptop. I would like to copy it to run on other PC (windows) for some tests. How can I do that?


texts:
How does one reference the currently-grouped by field in a subquery?

Consider a table with the following data:

Client    Status    Size
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

I am trying to get the following data in a single query:

Client     A      B
   1      500   1500
   2     2000    800

I've been playing with variations on this, with absolutely no success:

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

The status for each Client is not repeated, that is, each client should have only one row with a given status. For purpose of this question, we can assume that there is exactly one entry for each status.

Note that there are about one hundred thousand rows: about 20 thousand Clients and five status per client. There exist other fields as well (such as date of status update and the user who performed the update) so changing the table schema to simply Client, A, B is not feasible.

How can I reference the grouped field in the subqueries?


texts:
For backing up our postgres database, we're considering 2 separate options:

1- Logical backup with pg_dump
2- pg_basebackup with consistent WAL Archiving.

So I have two questions:

1- How often should we make a base backup?
Can we just create it once and then just archive the WAL files afterwards?

2- Do we need both approaches or one would suffice?


texts:
We have three dedicated SQL 2008 64 bit patched to SP4 (10.0.6241.0) in production. They run Windows 2008 R2 (SP1 and patched) under ESXI 6, and have 32GB ram (24gb max for sqlserver) and 8 vcpu. Each of these servers hosts 800-1200 small (less than 2gb) databases, though there are a few outliers of 3-100GB.

Periodically (7 dumps on one server since late June) we get a stack dump in the error log.

Error: 30087, Severity: 17, State: 5.
Data coming back to the SQL Server process from the filter daemon host is corrupted.
This may be caused by a bad filter.
The batch for the indexing operation will automatically be retried using a smaller batch size.

While the stack dump is being created, I'll get about 20 errors from our web servers that connect to the SQL dumping host

Database connection errors: Array
(
    [0] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => 258
            [code] => 258
            [2] => [Microsoft][ODBC Driver 11 for SQL Server]TCP Provider: Timeout error [258].
            [message] => [Microsoft][ODBC Driver 11 for SQL Server]TCP Provider: Timeout error [258].
        )

    [1] => Array
        (
            [0] => HYT00
            [SQLSTATE] => HYT00
            [1] => 0
            [code] => 0
            [2] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
            [message] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
        )

    [2] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => 258
            [code] => 258
            [2] => [Microsoft][ODBC Driver 11 for SQL Server]Unable to complete login process due to delay in prelogin response
            [message] => [Microsoft][ODBC Driver 11 for SQL Server]Unable to complete login process due to delay in prelogin response
        )

)

I can't find much on this error 30087 on the web, except for some problems that were resolved in SP1.

This blog post was the closest thing to a suspect, but I can't find that same memory pressure error that the author located in his dump file. In my SQLDump0071.txt file, the only text I can decipher confirms what processor I use, thanks dump file.

[I.n.t.e.l.(.R.).]
[ .X.e.o.n.(.R.).]
[ .C.P.U. .E.7.-.]
[ .4.8.5.0. . .@.]
[ .2...0.0.G.H.z.]

I'm pretty sure full text search is to blame, but not sure how to diagnose further. We use full text search on 5 columns in an indexed view, and 1 column in an Activity table for each database. We set them to AUTO repopulate.

We daily drop the indexed view on a few dozen database, update base
tables, and re-create the view. This prompts FTS to re-index 3k-25k
rows per database. If that volume seems alarming to others, we could
investigate a more targeted way to update data in the base tables.
The Activity table is filled by specific user actions, that are
ongoing

I don't have anything written in a SQLFT0096100NNN.LOG file at the time of failure.

Next steps?


texts: Query execution plan is horrible until Statistics are Updated

texts:
I hope you guys can help me here.
Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They will have no activity for 23hours a day and then load thousands of messages all at once (3000+). In other cases, this volume is nothing and we can easily deal with it, except in this case, the SQL query below takes approximately 30 seconds to run and gets worse as the queue backs up as it does an update, require an exclusive lock and therefore blocking all the other queries and so the issues causes all kinds of havoc.
This is all due to a bad query plan.

We have daily reindexing that runs every morning at 5am (reorganise < 30%, rebuild > 30%, ignore <5%) as well as updates statistics. These are both from the Ola Hallengren maintenance solution.
We are also on SQL Server 2016 and are fully up to date (13.0.5492.2)

I don't have the 2 plans handy, but basically the bad plan goes and does a full table scan of the MessagesSent table (3.5m rows).

My theory is that because the query returns nothing all day, certain parts are not executed and therefore the bad query is the most efficient query for SQL.

This will continue after flushing the plan for the query as it just generates the same plan, however when I UPDATE STATISTICS on the MessagesSent table, the good plan is created and all is healthy, with the query executing in about 10-30ms.

Does anyone know how I can fine-tune this to always use the better plan even if no data exsists for the query to return?
As a hotfix, we have added option recompile to the application but I don't feel that's the ideal solution for a query that is executing ever 3 seconds.

Here is the query :

WITH TopMessage
    AS
    (
        SELECT TOP 1 ID, BatchID FROM MessagesSent
        JOIN Units ON Unit = idUnit
        WHERE   MessageDate <= GETDATE()
          AND         Active = 'True'
          AND         Status = 'Queued'
          AND NOT(DialString = 'null')
          AND           Unit = ('29')
          AND System in ('SystemName', 'Q1', '')
        ORDER BY
            CASE
                WHEN QPriority IS NULL
                    THEN
                        CASE
                            WHEN DefaultPriority IS NULL
                                THEN 999999
                                ELSE DefaultPriority
                        END
                 ELSE QPriority
            END ASC,
            Retries ASC,
            MessageDate ASC,
            ID ASC
    ),
    BatchCalls
    AS
    (
        SELECT * FROM MessagesSent
        WHERE (
                 (LEN(BatchID) > 0
                  AND BatchID = (SELECT TOP 1 BatchID FROM TopMessage)
                 )
        OR ID = (SELECT TOP 1 ID FROM TopMessage)
        )
        AND Status = 'Queued' AND Active = 'True'
    )

    UPDATE BatchCalls
    SET LastUpdated = @dtNow
    OUTPUT INSERTED.*
    WHERE Status = 'Queued'

Thank you very much for your time and for looking.


texts:
Database set up here (was too large for sqlfiddle):
https://pastebin.com/Hd0DbQ5z

This is an MS Access DB.

I am looking to get the top score as well as the second top score.  I have the following query for getting the top score which is the exact results I am looking for:

SELECT e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN
(SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

I would like to tweak this query to show me the 2nd place scores, here is the closest I got (but with some unexpected results):

SELECT e.GAME_VER, e.CLAN, e.SCORE, e.S_REGION
FROM dev AS e
INNER JOIN (SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' AND ID NOT IN ( SELECT ID FROM dev as f INNER JOIN  (SELECT GAME_VER, MAX(SCORE) AS MAXVAL2 FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER) AS MAXSCORES2 ON  (f.SCORE = MAXSCORES2.MAXVAL2) AND (f.GAME_VER = MAXSCORES2.GAME_VER)) GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

(Sorry for the formatting, the query is kind of messy)

This query gives me the results that I am looking for, but it is returning rows from different S_REGION values even though I have them constrained to a single region in the query.

+----------+---------+----------+----------+
| GAME_VER |  CLAN   |  SCORE   | S_REGION |
+----------+---------+----------+----------+
|     3.25 | Melons  | 93.97925 | USA      |
|    3.375 | Melons  | 94.96925 | USA      |
|      3.5 | Bananas |   95.883 | USA      |
|    3.625 | Grapes  |    96.74 | USA      |
|     3.75 | Bananas |   97.503 | USA      |
|    3.875 | Grapes  |   98.354 | USA      |
|     3.99 | Lemons  |   96.777 | USA      |
|        4 | Grapes  |   99.092 | USA      |
|    4.125 | Grapes  |   99.698 | USA      |
|     4.25 | Grapes  |   100.39 | USA      |
|    4.375 | Grapes  |  100.994 | USA      |
|    4.375 | Apples  |  100.994 | PANAMA   |
|      4.5 | Bananas |  101.618 | USA      |
|    4.625 | Bananas |   102.22 | USA      |
|     4.75 | Bananas |  102.804 | USA      |
|    4.875 | Bananas |  103.342 | USA      |
|     4.99 | Lemons  |  101.619 | USA      |
|        5 | Bananas |  103.823 | USA      |
|    5.125 | Bananas |  104.428 | USA      |
|     5.25 | Bananas |  105.014 | USA      |
|    5.375 | Bananas |   105.49 | USA      |
|      5.5 | Bananas |  105.944 | USA      |
|    5.625 | Grapes  |  106.379 | USA      |
|     5.75 | Grapes  |    106.5 | USA      |
|     5.75 | Grapes  |    106.5 | CANADA   |
+----------+---------+----------+----------+

What is going wrong? Or is there a better way of writing the query to get the results I'm looking for. (I'm a noob).


texts: MySQL DB structure with Main category and several nested sub categories

texts:
I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, parts & accessories. So for example I would like to have three very broad categories:

Motorcycles
Powersports
Parts & Accessories

If a user selects Motorcycles it will then ask Brand:

Yamaha
Honda
Kawasaki
Harley-Davidson

Upon selecting a Brand it will then ask for model for that specific brand, so for Yamaha:

YZ
YZF
YFZ
WR
PW

So

Motorcycles -> Brand -> Model

Now if I wanted to add something to parts and accessories it would be something like

Parts & Accessories -> Brand -> Model -> Type of part -> Sub type of part

Same for Powersports:

Powersports -> ATV -> Brand -> Model

I have tried a structure like this

Main Categories(categories):

+-------+---------------------+-----------------------+
| CatID | Category            | CatDBTable            |
+-------+---------------------+-----------------------+
|     1 | Motorcycles         | motorcycle_brands     |
|     3 | Parts & Accessories | part_acc_categories   |
|     2 | Powersports         | powersport_categories |
+-------+---------------------+-----------------------+

Motorcycles(motorycle_brands):

+-------+-------+-------------------------+
| CatID | SubID | SubName                 |
+-------+-------+-------------------------+
|     1 |     1 | American Classic Motors |
|     1 |     2 | American Ironhorse      |
|     1 |     3 | Aprilia                 |
|     1 |     4 | Benelli                 |
|     1 |     5 | Big Dog                 |
+-------+-------+-------------------------+

Motorcycles Model(motorcycle_models):

+---------+--------------+------------+
| ModelID | BrandID      | BrandModel |
+---------+--------------+------------+
|       1 | Custom Built | Bobber     |
|       2 | Custom Built | Chopper    |
|       3 | Custom Built | Pro Street |
|       4 | Custom Built | Other      |
|       5 | BMW          | F-Series   |
+---------+--------------+------------+

Now, this is really simple to maintain keeping everything in separate tables but a nightmare for me to figure out how to query it properly. I have posted this question on another site and a user suggested using one large table which I think is a terrible suggestion but then again I am not a DB admin. I am hoping some of the experts here that do this on a daily basis can help me come up with a structure that is easy to query and maintain at the same time. I hope all of this makes sense. I have search around but can not find anything on doing several nested sub categories only a main category and a sub category.


texts: "Network connection was interrupted" error with shared Access database

texts:
We have an Access database with a split front/back end. The back end resides on a Samba 4.1.6 share with around 15-20 users. Frequently the database will popup with the error "Network access has been interrupted". Obviously this is not ideal.

When we move the back-end to a Windows home server share the issues do not persist, so as far as I'm aware this rules out any hardware issues within the network itself.

Has anyone else encountered the same issue? Or anybody have any ideas?


texts: determine maximum advisory locks supported by Postgres

texts:
According to the Postgres documentation, the maximum number of regular and advisory locks is limited by a shared memory pool:

Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.

How can I determine the size of this pool? Is this the same thing as shared buffers, which I can see with show shared_buffers; or is it something different? I am trying to determine roughly how many advisory locks my installation would be able to support because I am doing a ton of locking. My shared_buffers size is 5012MB.
I also have a couple more detailed questions:

If the server was unable to grant an advisory lock when I called pg_advisory_xact_lock(), would it hang, error out, or fail silently? As long as it doesn't fail silently I'm good, although ideally it would hang and then continue once memory frees up.
I locking not only with advisory locks but also with SELECT ... FOR UPDATE. If I know the size of the pool, how can I calculate roughly how much space in the pool each advisory lock takes, and each SELECT ... FOR UPDATE takes? I know roughly how many rows will be impacted by each SELECT ... FOR UPDATE.

The documentation is a little confusing because if you look at the documentation for max_locks_per_transaction it says:

The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited.

This seems to track with the idea that the memory pool is equal to max_locks_per_transaction * max_connections described earlier, but here it is saying that the max has more to do with the number of tables and not the number of rows. I'm not really sure how to square this with the first quote, or how this relates to the space taken by advisory locks.
Any tips on calculating would be greatly appreciated!


texts: How to implement Sql Server 2016 mobile publisher on existing Report Server

texts:
Currently working on sql server 2014 (SSRS 2014) on window server 2012 R2.

Requirement is to publish Sql Server mobile publisher report on report manager on SSRS 2014.

I installed Sql Server 2016 mobile publisher and developed some report but how to publish it on ssrs 2014 (Is it possible ?).

My problem is how to implement and use Sql Server 2016 mobile report on my current scenario.
Is this possible ? or upgrade to sql server 2016.


texts: One record, multiple updates in one query (MySQL)

texts:
I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can I do it in a single query? This query below works, just that it only works for the first match(?!).

UPDATE test AS t1
LEFT JOIN test AS t2 ON t1.target = t2.obj_id
SET t2.amount = t2.amount + t1.amount, t1.amount = 0
WHERE t1.amount > 0 AND strcmp(t1.target, "null") <> 0;


texts: Loop through key/value pairs of a jsonb object in postgresql function

texts:
I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object.

create or replace function myFunction
(input jsonb)
returns jsonb as $$
BEGIN

    // foreach(key in input)
    //       do some math operation on its corresponding value

    returns input;

END; $$

The argument input is expected to be a jsonb object, such as {"a":1, "b":2, "c":3}.

I want to loop through each key/value pair in the object. As you can tell from my comment, I used to write backend code with more general-purpose programming languages like c, java, etc. So I am not good at SQL. I have tried to search online, but they talk about how to loop through jsonb array instead of object. So really stuck here.


texts:
I have a table that keeps tags and their reference. For example:

| tag  | tableName | rowId |
| ---- | --------- | ----- |
| tag1 | table1    | 1     |
| tag2 | table1    | 1     |
| tag1 | table2    | 1     |

I would like to form a query that says something like "get distinct table name and rowId WHERE it has all of these tags: 'tag1', 'tag2'"

The objective of this table is to make it easy to look up rows in tables by tags. Maybe I'm going the complete wrong direction with how to solve this.

How can I do this and keep performance high?

Thanks for the help.


texts: How to Traverse All Foreign Keys to Generate All Possible Paths

texts:
I'm attempting to traverse all of the foreign keys in a SQL Server database to generate all of the possible paths between tables.  The query below seems to mostly work for what I am needing except that it seems to run indefinitely.  My goal is to be able to find the most efficient foreign key path between any two tables in the database by using the path with the least number of "hops" to my destination table.  Is there a way to limit the query below so that it doesn't go beyond 20 hops?

WITH cte
AS
(
    SELECT
        fk.create_date
        , fk.modify_date
        , fkc.constraint_object_id AS ConstraintId
        , OBJECT_NAME(fkc.constraint_object_id) AS ConstraintName
        --, fkc.referenced_object_id AS PrimaryKeyTableId
        , OBJECT_NAME(fkc.referenced_object_id) AS PrimaryKeyTableName
        --, fkc.referenced_column_id AS PrimaryKeyColumnId
        , rc.name AS PrimaryKeyColumnName
        --, fk.parent_object_id AS ForeignKeyTableId
        , OBJECT_NAME(fk.parent_object_id) AS ForeignKeyTableName
        --, fkc.parent_column_id AS ForeignKeyColumnId
        , lc.name AS ForeignKeyColumnName
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns rc
        ON  rc.OBJECT_ID = fkc.referenced_object_id
        AND fkc.referenced_column_id = rc.column_id
    INNER JOIN sys.foreign_keys fk
        ON  fk.OBJECT_ID = fkc.constraint_object_id
    INNER JOIN sys.columns lc
        ON  lc.OBJECT_ID = fk.parent_object_id
        AND fkc.parent_column_id = lc.column_id
)
, cte2(create_date, modify_date, ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path ) AS
    (
        SELECT
            create_date, modify_date, ConstraintName
            , PrimaryKeyTableName, PrimaryKeyColumnName
            , ForeignKeyTableName, ForeignKeyColumnName
            , 1 , CAST(QUOTENAME(PrimaryKeyTableName + '.' + PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte
    UNION ALL
        SELECT
            cte.create_date, cte.modify_date, cte.ConstraintName
            , cte.PrimaryKeyTableName, cte.PrimaryKeyColumnName
            , cte.ForeignKeyTableName, cte.ForeignKeyColumnName
            , cte2.Hops +1, CAST(cte2.path + '-> ' +QUOTENAME(cte.PrimaryKeyTableName+ '.' + cte.PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte2 INNER JOIN cte ON cte2.ForeignKeyTableName = cte.PrimaryKeyTableName
        AND cte2.ForeignKeyColumnName != cte.PrimaryKeyColumnName
    )
SELECT
ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path + '-> ' + QUOTENAME(ForeignKeyTableName + '.' + ForeignKeyColumnName) AS Path
FROM cte2


texts: How does the MongoDB query optimizer evaluate candidate plans?

texts:
The MongoDB query optimizer processes queries and chooses the most
  efficient query plan for a query given the available indexes

This is an excerpt from MongoDB documentation.
I am looking for more information about how the query optimizer evaluates candidate plans.

Does the query optimizer use collections statistics ? Indeed, we can
think that a COLSCAN has not the same cost for a collection with
1000 docs or a collection with 100 millions docs.
When we run explain("allPlansExecution"), we get a lot of information about each evaluated plans. But, among following, what are the selection criteria in order of importance to determine the winning plan ? totalKeysExamined,   totalDocsExamined,   executionTimeMillis,   nReturned,   memUsage,  other... ?


texts: Reclaim disk space from dropped column without downtime

texts:
I have a heavily used table (with around 5 million rows) in a PostgreSQL database in which I want to drop a column and reclaim the space the column used.

The docs suggest doing a table rewriting ALTER TABLE to force the space back, but that is not safe to run while the table is used and would cause downtime. Are there any practical options that don't require any downtime? I tried to run the pgcompact tool, but that did not change anything.


texts: Importing SQL Server database from a .sql file

texts:
I'd like to import a database without knowing its name from a .sql file.

In MySql server it is easy because there is tools like heidSQL in which I can import easily my database from a sql file but in SQL Server I can't find the method to do it.

I tried to read the file in notepad but the encoding makes the content unreadable.

When i execute this query RESTORE HEADERONLY FROM DISK = N'C:\Planning\bdd.sql' i have this

How can I do it with SQL server?
Can SQL Server Management Studio do this task or do I have to install a new tool?


texts: Stored Procedure Asking for Input While Processing

texts:
So I am asking this to see if this is something that is even possible within SQL.

I am currently writing up some stored procedures for a VB.Net application I am working on to use to interact with the data within my database.

I am creating a procedure to delete a set of records, but want to know if it's possible for the Procedure itself to sort of ask for confirmation that the item is to be deleted.  Something like a message prompt or something where the procedure pauses to wait for further input.

If this isn't doable, I know how I can manage it on the VB.Net side, I am just trying to keep as much of the SQL and database stuff in the database itself as possible.


texts: Minimizing/avoiding data repetition and choosing between compound keys and unique indexes

texts:
I am absolutely new to database development and i'm trying to build a question bank basically.

The questions have to be categorized based on Class, Subject and Topic.
Since each Class can have many Subjects and each Subject can be in many Classes I made a junction table called tb_SubjectClass with Subject and Class being primary keys.

To uniquely identify any Chapter I would need a combination of Subject and Class so I created tb_Chapters with Chapter, Subject and Class as primary keys with Subject and Class coming from tb_SubjectClass. I then repeated the same thing with tb_Topics as each topic can only be identified with a combination of Subject, Class and Chapter.

I have placed the relationship diagram below. It may also be relevant to know that I am expecting around 50000 questions (with equations and diagrams) to be stored in this database.

Main question: If I decide to stick to compound/composite keys, is my design proper or can it be improved further ?

Side question: what would be the best approach in my case, with the following considerations in mind:

expected records around 50000
questions table (tb_QnA) is going to be the most queried table (for selecting and for adding/deleting data)
the tables tb_Class, tb_Subjects and tb_SubjectClass are not going to change at all
ease of writing queries when connecting the database to a c# winform application (for data entry and generating reports)
the project is going to be desktop based

I have mainly been following the Database design tutorial on tekstenuitleg.net and video tutorials by WiseOwlTutorials. I have also recently started reading the book Relational Database Design Clearly Explained by Jan L. Harrington.


texts: Permission error while browsing the Dimension when Dimension Security is defined on one of the attribute

texts:
I am using SSAS SQL Server for dimension modelling, and the dimension is defined as below:

Dimension Name: Department

Dimension Attributes:

 Location
 DepartmentCode
 DepartmentType

I have dimension security defined on one of the attribute, i.e., on Location (not on the main Department dimension) using the Factless Fact Dimension Security.

When I try to execute the following MDX query (to retrieve all the Level 0 members of the Department Dimension along with the DepartmentCode Attribute), I get the error as "Errors in the OLAP storage engine: Either you do not have permission to access the specified member, or the specified member does not exist.":

SELECT {} ON 0, { [Department].Levels(0) }
DIMENSION PROPERTIES [Department].[DepartmentCode] ON 1
FROM SampleCube

I am able to successfully browse Location Attribute using the following MDX query provided the user has access to Location:

SELECT {} ON 0, { [Location].[All].Children } ON 1 FROM SampleCube

Any Idea on what might be going wrong when I browse the main dimension with DepartmentCode attribute? Browsing without DepartmentCode attribute works fine. Please do let me know if you need any information..


texts: Extract all unique chars from all texts across the table

texts:
I have a huge mysql table (20 columns by 320.000 rows) that contains a lot of text from a lot of languages and the problem is that I need to identify all the unique characters in that table. For example, if character "," appears one time or more than one time in that table I need this query to show it to me once.

The query should return a list with all the unique characters from this database. I don’t need the query to count them.

Can you help me finding this solution?
Thanks in advance, Catalin


texts: How to avoid the error "read ECONNRESET"?

texts:
I'm building an API that tracks binlog files in order to notify users of some specific changes on database.
My max_binlog_size is set to 1048895 (1M) and after some time running the follow error is thrown on terminal:
Error: read ECONNRESET
at TCP.onStreamRead (internal/stream_base_commons.js:205:27)
--------------------
at Protocol._enqueue (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Connection.query (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\Connection.js:198:25)
at ZongJi._fetchTableInfo (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\@rodrigogs\zongji\index.js:203:23)
at Binlog._callback (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\@rodrigogs\zongji\index.js:251:18)
at Binlog.BinlogHeader (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\@rodrigogs\zongji\lib\sequence\binlog.js:43:22)
at Protocol._parsePacket (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket.<anonymous> (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\Connection.js:88:28)
at Socket.<anonymous> (C:\Users\edils\Documents\DEV\Updata\mk-auth-support-app-backend\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (_stream_readable.js:297:12)
at readableAddChunk (_stream_readable.js:273:9)
at Socket.Readable.push (_stream_readable.js:214:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {

errno: -4077,
  code: 'ECONNRESET',
  syscall: 'read',
  fatal: true

I realized that everything works fine until fifth binlog file is created. That's when the sum of all existing binlog files is greater than 4 mb which theoretically exceeds max_allowed_packet value.
So, why does it happen? How can I overcome that problem?
My Server version: 10.0.20-MariaDB-1~jessie-log mariadb.org binary distribution


texts:
I'm a new DBA working with a MySQL(5.6.19) instance I have running on a Mac Pro, I'd like to back it up with the Percona tools, but there isn't a distro for Mac, so my plan is to create a copy of the database on a linux box, make it a slave to the one on the Mac and then use the Percona tool to back it up from there (this will be on a local network). This Linux box will also be running the full system backup software (Bacula) we're setting up. I've looked into how to set this up and I think I get most of it, but I'd love advice on the following issues:

1) Bacula uses MySQL for part of its back end, if I set up the slave server on that machine will Bacula still be able to use MySQL as its back end? (More generally, can there be other schema on a MySQL instance that is running in slave mode?). Or do I need to configure a separate server instance as here: http://dev.mysql.com/doc/refman/5.6/en/multiple-servers.html  ? (or are there better ways that I'm missing?)

2) I can do a mysqldump of the current database, but it will be rather slow as I have a few very large files, will there be a problem transferring the files directly as http://dev.mysql.com/doc/refman/5.6/en/replication-howto-rawdata.html from Mac to Linux?

Thanks everyone! Any responses to either would be much appreciated!

David


texts:
In my phpmyadmin , when am adding a new column to my table , mysql throwing me error table xxx is full
i also search on google , but did not get help.

am also gone through dba.stackexchange.com answer realeted to this question , but no help. https://dba.stackexchange.com/questions/43503/error-1114-hy000-the-table-is-full-with-innodb-file-per-table-set-to-aut


texts: How can I use mongoDB efficiently in this scenario?

texts:
I'm trying to build a system of documents that have hundreds of tags assigned to them, but each document-tag association has a certain condition it has to also meet so the document would be returned:

{
title: "new document",
tags: [
    {
    label: "label1",
    intervals: [
       {start_date: "2012-01-05", end_date: "2013-01-05"},
       {start_date: "2012-01-06", end_date: "2013-01-25"},
    ],
    applicable_for: ["birthdays","anniversaries"]
    },
    {
    label: "label2",
    start_date: "2012-04-05",
    end_date: "2014-02-05",
    applicable_for: ["anniversaries"]
    }
]
};

A typical search would be something like:

Show me documents tagged with "label1", but only if this tag is applicable for "birthdays" and a certain date interval D0-D1 overlaps with one of the tag's intervals.

I have two questions:

can this type of query be performed in MongoDB?
is MongoDB the correct choice for this scenario? I've never used it until now, but it sems a better fit than MySQL or SOLR for tens of thousands of documents, each with hundreds of tags.

Thanks in advance!


texts:
SELECT
`members`.`id` AS `aid` ,
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `amount` ELSE 0 END) AS `deduction_amount` ,
SUM(`portion`) AS `total_portion`
FROM `members`
LEFT JOIN `agents_data` ON `members`.`id` = `agents_data`.`agent_id`
LEFT JOIN `agents_deductions` ON `members`.`id` = `agents_deductions`.`agent_id`
LEFT JOIN `agents_commission` ON `members`.`id` = `agents_commission`.`agent_id`
GROUP BY `members`.`id` ORDER BY `members`.`id`

The total_portion results is not correct

how I use multiple SUM with multiple LEFT JOINS

UPDATE

I have 3 tables members, agents_data,  agents_deductions, agents_commission

I want to view all members and calculate agents_deductions.amount and agents_commission.portion

members
agents_deductions
agents_commission
I want to calculate agents_deductions.amount it should be 12000
also calculate agents_commission.portion it should be 166666.7
 for the members.id = 1

UPDATE

I tried

SELECT
(
SELECT *,
`frist_step`.`id` AS `aid`,
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `agents_deductions`.`amount` ELSE 0 END) AS `deduction_amount`
FROM `members` AS `frist_step`
LEFT JOIN `agents_deductions` ON `frist_step`.`id` = `agents_deductions`.`agent_id`
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `frist_step`.`id` ORDER BY `frist_step`.`id`
),
(
SELECT *,
`step_two`.`id` AS `aid2` ,
SUM(`agents_commission`.`portion`) AS `total_portion`
FROM `members` AS `step_two`
LEFT JOIN `agents_commission` ON `step_two`.`id` = `agents_commission`.`agent_id`
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `step_two`.`id` ORDER BY `step_two`.`id`
),
(
SELECT * FROM `frist_step`
LEFT JOIN `step_two` ON `frist_step`.`aid` = `step_two`.`aid2`
GROUP BY `frist_step`.`aid` ORDER BY `frist_step`.`id`
)

the results Error Code: 1146. Table 'frist_step' doesn't exist


texts: How can I enable/disable MySQL slow query logging on Amazon RDS without restarting

texts:
I would like to enable/disable slow query logging on an Amazon RDS instance without having to restart. I'm aware that I can do this by changing the slow_query_log parameter in the instance's parameter group, but this requires me to restart.

On the other hand, I've seen that this is possible with SET GLOBAL slow_query_log = 'ON'; in MySQL, but this requires super privileges, which are restricted to the rdsadmin user on Amazon RDS.

I've also found this resource: How do I enable functions, procedures, and triggers for my Amazon RDS MySQL instance? but the description says "when binary logging is enabled", which is not what I need.


texts:
Can I monitor all databases on different hosts with single cloud control?
Do I need to install agent on each server?
Do I need any license ?


texts: In what cases are BLOB and TEXT stored in-line on InnoDB?

texts:
I've googled this and can't find it, you can see Rick James mention it here.

... BLOB and TEXT are not always stored separately

The answer on What is the difference between MySQL VARCHAR and TEXT data types? says,

InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

That's not the only massively upvote answer with this stance. From StackOverflow you can find an answer with 754 upvotes that says it quite clearly,

TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage.
VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a realworld scenario with your data.

The comment here links to a post on a mailing list that says

MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' -- costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.
InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.

I've seen other posts that talk about storing text and blobs in-line if they're under some limit? What is the limit? When, if ever, does InnoDB store blobs and texts in line?


texts: Trigger to update only if value is in lookup table

texts:
I only want to update the comment if a description for the event is found in the TagDescLookup Table.  If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column.  The current trigger will update the comment column with the correct value unless the value isn't found in the lookup table, it sets comment to Null.  I'm using SQL server 2017.

USE [AwxLogger]
GO

/****** Object:  Trigger [dbo].[TagDescTrigger]    Script Date: 14/03/2019 16:47:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TagDescTrigger]
    ON [dbo].[EventLog]
    AFTER INSERT
    AS
       BEGIN
           SET NOCOUNT ON
              UPDATE [dbo].[EventLog]
        SET Comment =
                  (
            SELECT [Description] FROM TagDescLookup
            WHERE Tag = I.NameOfParameter
            )
        FROM [dbo].[EventLog] AS E
        JOIN INSERTED AS I
              ON E.NameOfParameter = I.NameOfParameter
       END

TagDescLookup Table

ID int NOT NULL PRIMARY KEY IDENTITY (1,1)
Tag nvarchar(MAX) NOT NULL
Description nvarchar(MAX)


texts: Can't connect to MySQL database over SSH with Workbench

texts:
I'm trying to connect to my database via SSH tunneling from one of our web app servers with MySQL Workbench. Here's the basic config; note that I changed some values in the screenshot for security reasons.

The problem is every time I try to connect over an SSH tunnel from one of our app servers, I get the following error:

  Failed to connect to us-east-1.amazonaws.com through SSH tunnel at computer.amazonaws.com with user social_shop_prod. Can't connect to MySQL server on 127.0.0.1.

However, if I use the same credentials over SSH via the following command line:

mysql -u social_shop_prod -h us-east-1.amazonaws.com -p

I can connect successfully and get the MySQL interactive command prompt.

Been talking with the rest of my development team here and none of us can figure out why I can't tunnel over SSH from our app servers with Workbench; but when I SSH to one of our app servers and connect to MySQL via the command line; I can connect successfully.

And why is it trying to connect over 127.0.0.1? I didn't specify that in the configuration; nor is my hosts file redirecting the domains shown below to that IP.

Any constructive input is greatly appreciated.


texts: MySql counting results with left joins doubles the results

texts:
I have this query generated by Django where a user has orders and jobs as related tables. jobs are of two types: clean and wash. I want to count for each user shown the number of orders, clean jobs and wash jobs.

One of the users has 3 clean jobs in the database but this query returns the value of 6 for column clean_ct. If I remove the join to the order table and the orders count the counts are as expected so the second LEFT OUTER JOIN is causing this doubling to occur. I'm suspecting a wrong GROUP BY but I haven't found a solution. Any ideas?

SELECT DISTINCT `auth_user`.`id`,
                `auth_user`.`username`,
                Count(DISTINCT `order`.`id`) AS `orders_ct`,
                Count(CASE
                        WHEN `job`.`service` = 'clean' THEN 1
                        ELSE NULL
                      end)                   AS `clean_ct`,
                Count(CASE
                        WHEN `job`.`service` = 'wash' THEN 1
                        ELSE NULL
                      end)                   AS `wash_ct`
FROM   `auth_user`
       LEFT OUTER JOIN `order`
                    ON ( `auth_user`.`id` = `order`.`user_id` )
       LEFT OUTER JOIN `job`
                    ON ( `auth_user`.`id` = `job`.`user_id` )
GROUP  BY `auth_user`.`id`
ORDER  BY `auth_user`.`id` DESC
LIMIT  100

clean_ct should be 3 but returns 6


texts: ALTER TABLE ... ROW_FORMAT=Compressed times out

texts:
I'm trying to change a table with the command:

ALTER TABLE ... ROW_FORMAT=Compressed

This fails after 15 minutes, the log says:

InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

and I'm left with a large temporary table:

-rw-rw---- 1 mysql mysql 37G Jun  1 10:53 #sql-ib788-3264739711.ibd
-rw-rw---- 1 mysql mysql 15K Jun  1 10:37 #sql-13e5_95.frm

Whereas the table is only 100MB. I'm using MariaDB 10.0.16 on SLES 12.

I've actually backed up this table and restored it on a test VM running openSUSE 13.2/MariaDB 10.0.13 and the ALTER TABLE takes 60 seconds. Unfortunately I need to perform this process on the actual server.

The table has approximately 100,000 rows. If I cut it down to 1,000ish rows the statement works (the exact number of rows depends on whether I remove from the start or end of the table).

The log reports the InnoDB status and I noticed this:

---TRANSACTION 8665481870, not started
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
---TRANSACTION 8665481869, ACTIVE 253 sec
mysql tables in use 1, locked 1
MySQL thread id 4, OS thread handle 0x7f8bf01b3700, query id 709 localhost user1 altering table
ALTER TABLE mdl_user ROW_FORMAT=Compressed
Trx read view will not see trx with id >= 8665481871, sees < 8665481870

Which looks to me like it's trying to use two threads which are deadlocked although I may be misunderstanding what this is telling me.

What's going wrong?


texts:
I don't understand why I'm getting these results with my full-text search query in SQL Server 2014.

The following query returns results with "Supervisor" in the Title field and "Tokyo" in the HTML_Description field.

SELECT
    *
FROM post
    JOIN CONTAINSTABLE([post], (Title, HTML_Description), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

If I remove the HTML_Description field in the CONTAINSTABLE like this:

SELECT
    *
FROM post
    JOIN CONTAINSTABLE(post, (Title), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

The result is the same.

If I use only the HTML_Description field in the CONTAINSTABLE like this:

SELECT
    *
FROM post
    JOIN CONTAINSTABLE(post, (HTML_Description), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

There aren't any results.

How can I apply the NOT operator to all fields?


texts: Ensure correct username when using pg_restore

texts:
I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this:

sudo -u postgres psql -c "create user app_user with password 'pass';"
sudo -u postgres psql -c "create database app_production owner app_user;"

Now, when I try to restore the the dump I use the following command:

pg_restore --verbose --schema=public --no-acl --no-owner --jobs=8 --exit-on-error --username=app_user --dbname=app_production /tmp/app_production.dump

Now in psql with \l to see ownerships I get the following:

                                         List of databases
          Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------------+-----------+----------+-------------+-------------+-----------------------
 app_production   | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres         | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0        | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |           |          |             |             | postgres=CTc/postgres
 template1        | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |           |          |             |             | postgres=CTc/postgres

As you can see, the ownership of app_production database has now turned from app_user to postgres. I would have expected the owner of the app_production database to still be app_user, so what am I doing wrong?

BTW, The --schema=public was added, because I was getting a weird error:

  "Could not execute query: ERROR:  must be owner of extension plpgsql"

Another thing is, that the owner of the dump is the user that the database was having on heroku, which would be something like 'jebf473b73bv73v749b7'


texts: Is it recommended to run DBCC CheckDB on tempdb? If yes, can someone please list out the reasons to do so? This could be on any SQL Server version. The one I have is SQL Server 2017.


texts:
I'd like to know what is advantages of Streams over Advanced Replication. I wanted to use updatable materialized views on my project, but on one forum I read comment that if we don't use very old version of Oracle, it's better to use Streams.

Can you tell me what benefits I'll get if I use Streams, like simpler configuration, better conflicts resolving configuration, more stable work of replication or something else? Client database should be able to work offline.


texts: How to calculate total distance by datetime

texts:
This is my database table ↓↓

ID   GPSLat     GPSLon       GPSTime
(1 , 3.214502 , 101.638419, '2017-06-23 12:21:51'),
(2 , 3.214478 , 101.638101, '2017-06-23 12:20:42'),
(3 , 3.214668 , 101.638049, '2017-06-23 13:25:53'),
(4 , 3.215089 , 101.638022, '2017-06-23 13:19:12'),
(5 , 3.215196 , 101.638626, '2017-06-23 14:10:56'),
(6 , 3.215255 , 101.639087, '2017-06-24 15:10:48'),
(7 , 3.215011 , 101.639448, '2017-06-24 15:10:36'),
(8 , 3.215041 , 101.640151, '2017-06-24 16:10:26'),
(9 , 3.218021 , 101.620416, '2017-06-24 17:31:59'),
(10, 3.123456 , 101.555555, '2017-06-25 18:45:41'),
(11, 3.345214 , 101.632157, '2017-06-25 18:02:51');

the distances result in create view image  ↓↓

I'm using the following query to sum up the distances by prev_ to curr_, but how do I calculate the distances by date or time instead of using prev_ and curr_?

For example, I want to calculate from date "2017-06-23 12:00:00" to "2017-06-23 14:00:00", which is output came from id1 to id4, how do I calculate and using the following similar query?

PS:I tried LIKE operator but it's looks tricky

select
 sum(distance_km) total_km
from
  distances
where prev_ >=2 and curr_ <=11;

this is my formula to calculate between each id distance  ↓↓ (I'm not sure the code it's whether affect the datetime , so I post it out too)

create view distances as
select
  prev.id prev_
, curr.id curr_
, prev.gpslat p_lat_
, prev.gpslon p_lon_
, curr.gpslat c_lat_
, curr.gpslon c_lon_
, prev.gpstime p_time_
, curr.gpstime c_time_
, ROUND( 6353 * 2 *
    ASIN(SQRT( POWER(SIN((curr.GPSLat - abs(prev.GPSLat)) * pi()/180 / 2),2)
  + COS(curr.GPSLat * pi()/180 ) * COS( abs(prev.GPSLat) *  pi()/180)
  * POWER(SIN((curr.GPSLon - prev.GPSLon) * pi()/180 / 2), 2) )), 2) as distance_km
from gpsdata prev
  join gpsdata curr
    on prev.id = curr.id - 1
where curr.id >= 1;

Edited: Expected Result

  select datetime from 2017-06-23 12:00:00 to 2017-06-23 14:00:00

prev_ | curr_ | p_time_             | c_time_             | distance_km
1     | 2     | 2017-06-23 12:21:51 | 2017-06-23 12:20:42 | 0.04
2     | 3     | 2017-06-23 12:20:42 | 2017-06-23 13:25:53 | 0.02
3     | 4     | 2017-06-23 13:25:53 | 2017-06-23 13:19:12 | 0.05

total_km
0.11


texts: Very Sporadic Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server

texts:
OLE DB provider "SQLNCLI11" for linked server "001" returned message "Unable to complete login process due to delay in opening server connection".
  Msg 7303, Level 16, State 1, Line 19
  Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "001".

I realize there are a lot of potentially applicable SO posts and google hits on this (many of them I've read quite a few times), so let me go through what we're seeing and what we've already tried. I'm at the point where I'm honestly not sure what else to look at.

We have Linked Server 001 (Dest) going to 002 (Source) as a SQL Login which is called from C# / .Net application code as a proc via a data access method and an SSPI connection string via service account with proper rights. I believe the .Net version was 4.3 and is now 4.7, but the issue was persistent in both. It's been migrated from an older 2008R2 environment on a different network where this was never an issue on physical boxes and both of the new, current are 64-bit 2016 virtuals.

It is consistently reproducible during the issues, but not otherwise and seems to always be the same call. Nothing shows up in sp_what for blocking/deadlocks/weird locked pages or objects. And the issue isn't reproducible when it's not being reported. Sometimes it only happens once or twice, sometimes for 10-15 and then it'll work fine for weeks. Not sure it's related, but the application also shows thread aborts occasionally when we see this, but I think it's more of a symptom.

The linked server and account on both sides have been recreated manually and via script as well as GUI and from known working linked servers. I've verified that they're not getting locked out and have the correct permissions multiple times.
I've increased the max query timeout on both sides.
The SQLNCLI11.DLL drivers are on both servers in the same versioning as the SQL Server install.
Nothing obvious is showing up in the SQL Management or Error logs in terms of login failures, corruption, or weird behavior.
We don't see anything in the server event logs on either side.
Our networking and security teams have taken a look and aren't finding any blips in connectivity and traffic the boxes reside on the same switch and don't pass through a firewall.
Ports are open.
When these issues are occurring Test Connection from the linked server works fine and I can typically get to the underlying issue table via ad-hoc, but not the stored proc's code, even run in-place.

What am I missing here?


texts: Can SSRS used to Update or Modify data tables?

texts:
We all know SSRS (SQL Server Reporting Services) is to read data.

However, can SSRS be utilized to update rows or insert rows, and should it?

I am seeing resources here, where you can take user input parameters and update rows.
Just inquiring if it is advisable/allowed to use that tool in such a way. Does Microsoft have anything against doing that?

Resources stating this:

madeiradata
sqlservergeeks
experts-exchange


texts: Plan Cache: Why are multiple plans cached for this stored procedure in SQL Server

texts:
I am trying to gain a better understanding as to how SQL Server caches query plans.

For the following table(Populated with the values 1-500,000):

CREATE TABLE [dbo].[Test] ([ID] INT PRIMARY KEY);

And the following stored procedure:

CREATE PROCEDURE [dbo].[TestGet]
    @ID INT
AS
BEGIN;
    IF(@ID IS NOT NULL) BEGIN;
        SELECT [ID]
        FROM [dbo].[Test]
        WHERE [ID] = @ID;
    END;
    ELSE BEGIN;
        SELECT [ID]
        FROM [dbo].[Test];
    END;
END;

Making the following two calls:

EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;

Results in two different plans in the query plan cache:

If the stored procedure were re-written as:

CREATE PROCEDURE [dbo].[TestGet]
    @ID INT
AS
BEGIN;
    SELECT [ID]
    FROM [dbo].[Test]
    WHERE [ID] = @ID OR @ID IS NULL;
END;

And the same two calls were made:

EXEC [dbo].[TestGet] @ID = 1000;
EXEC [dbo].[TestGet] @ID = NULL;

Only a single query_hash, and query_plan_hash are cached:

The calls to the stored procedure have the same shape in both cases.  Why does one the first stored procedure and call generate two cached plans, with a different query_hash, and a different query_plan_hash?  What criteria is SQL Server using to generate these hashes?


texts: Question about encrypting multiple MariaDB schemas on the same server

texts:
I have 1 server running 1 installation of MariaDB

In that installation of MariaDB, I have a CustomerService database and WorkFlow database.

Can I encrypt CustomerService with one key and WorkFlow with another key so that users accessing WorkFlow cannot see CustomerService or vice/versa?

Or do I need another installation of MariaDB?


texts: Joining INTO many-to-many table (From Primary Key Table)

texts:
This is how far i have gotten, though i don't think it can be done with 1 SQL-statement, i just want to confirm whether or not it is possible to do this with ONLY 1 statement:

SELECT * FROM users
INNER JOIN users_mentors ON users_mentors.id=users.mentoruser_id
INNER JOIN mentor_types ON (mentor_types.id=users_mentors.mentor_type OR users_mentors.mentor_type IS NULL)
INNER JOIN mentor_geographies ON mentor_geographies.mentor_id=users_mentors.id
INNER JOIN communes ON communes.id=mentor_geographies.commune_id
LIMIT 0,10

users table with foreignkey to users_mentors:

+------+---------+---------------+
| id   | user_id | mentoruser_id |
+------+---------+---------------+
| 1886 |    NULL |             4 |
| 1885 |    NULL |          NULL |
| 1884 |    NULL |          NULL |
| 1883 |    NULL |          NULL |
| 1882 |    NULL |          NULL |
+------+---------+---------------+

users_mentors table (in a many-to-many relationship with communes):

+----+-------------+
| id | mentor_type |
+----+-------------+
|  4 |        NULL |
|  1 |           1 |
|  2 |           2 |
|  3 |           3 |
+----+-------------+

communes table (in a many-to-many relationship with users_mentors):

+----+--------------+-------+----------+
| id | name         | short | contract |
+----+--------------+-------+----------+
|  1 | KÃ¸benhavn   | NULL  |        0 |
|  2 | Aarhus       | NULL  |        0 |
|  3 | Aalborg      | NULL  |        0 |
|  4 | Odense       | NULL  |        0 |
|  5 | Esbjerg      | NULL  |        0 |
+----+--------------+-------+----------+

mentor_geographies table (the m2m table that has FK to communes & users_mentors):

+----+-----------+------------+
| id | mentor_id | commune_id |
+----+-----------+------------+
|  1 |         4 |          1 |
|  2 |         4 |          2 |
+----+-----------+------------+

So my question is:

Is it possible to get all rows from users_mentors and a list of all their commune.type's, IF THEY EXIST (if mentor_geographies is empty, i want empty list of commune.type). In all cases i want the user.


texts:
Recently i get backup of my database from host in that backup i get table in .ibd,.frm,.myd format .

I placed this files in

  xampp->mysql->data->test

. Now i can open the tables in php myadmin. Now i can open some tables .But some files i can not open . When clicked it showing 932 - Table 'table.name' doesn't exist in engine . And also i noticed in the collection column it is showing in use

Why this happen . Please help to solve


texts: Is it possible to set Buffer pool extension on table having clustered Column Store index in SQL Server 2014?

texts: I created Clustered Column Store index on a table. I want to improve memory performance using buffer pool. Is it possible to set buffer pool on sql server, a table having clustered column Store index in SQL Server 2014? What will be the performance gain after setting buffer on it.


texts: Copying Tables (partly replicating) databases remotely

texts:
I am having hard time figuring out a possible solution copying data from a tables to another.

I have 2 databases
(DB1 and DB2).

DB1 and DB2 has the following tables (eg):

Product
Contacts
Sales

Copying Part,

When a data is inserted, updated to DB1 product tables, it has to be copied to DB2 also.

However,
if DB2  product table is inserted / updated, it should not reflect to DB1 (i assume this might not be a issue).

Further Complication:

If in DB2 Sales table, if new data is inserted/updated, it has to be copied to DB1 and DB1 can make changes and update the table which should reflect to DB2.

I have very less experience with database, although i am using it for quite frequently but have not attempted to do this sort of work, i am finding this a bit complicated. I attempted to look few of the Tutorials, videos. There are techniques using replications, hot standby, which is good but not suitable for my case.

Is it possible to do this thing, i am sure there are tools but i am not sure which tools is very useful for my case? Any suggestions or help is much appreciated.

AFAIK, if we can sync between 2 different database we need to use GUID / UUID (i have used this for one way replication). For this case i use a python server calculate and do the transaction. to retrieve about 10000 records it takes around 5-10 minutes which is tediously slow and useless for my new case. My new case might need to retrieve more than 100 thousan records at a time.

Any suggestions i much appreciated. I am just looking forward for some sign of hope as i could not google any solution.


texts: postgresql - declaring variables to be used for start_time - end_time range

texts:
I'm relatively new to postgresql, so forgive me if this is a simple problem.
I'm trying to implement a query with start_time and end_time arguments, which the user can input their own dates into for customized results.
From taking bits and pieces from other examples online, I've come up with something that more or less runs, however I'm getting an error "query has no destination for result data".
I'm simply trying to return the data from the select statement.
Is there something trivial I'm missing here?
do $$
DECLARE
    start_date timestamp := '2020-10-1';
    end_date timestamp := '2020-10-5';
begin

select distinct on (account.id, menu.name, kitchen_item.name)
...
query stuff
....
where orders.placed BETWEEN start_date AND end_date
order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;

end$$

EDIT -
This is the final result I've worked out which appears to work for this purpose utilizing temp tables.
DO $$
DECLARE

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';

BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name",
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes",
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time",
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday",
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $$;

SELECT * FROM temp_output;


texts:
I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count).
The data looks like this:

Transactions table:

product_id
seller_id
seller_country
buyer_id

buyers table

buyer_id
buyer_country

There could be multiple rows for the same product, and the same buyer can buy from multiple sellers multiple times.

Buyer table has unique rows for each buyer, so while the same buyer can appear on multiple rows in the transactions table, he'll appear just on one row in the buyers table.

What I want to create is a matrix where the rows are for sellers' countries, columns for buyers' countries, and the values in the table count the number of unique buyers from each country who bought from each country. Count the distinct buyers each cell.

I tried several things but ultimately couldn't get past a row group by. Searched online for solutions but nothing was relevant for this very problem.
This is what I managed to do with MS access:

TRANSFORM Count(Dummy_users.buyer_id) AS CountOfbuyer_id
SELECT Dummy_transactions.seller_country
FROM Dummy_users LEFT OUTER JOIN Dummy_transactions ON Dummy_users.buyer_id = Dummy_transactions.buyer_id
GROUP BY Dummy_transactions.seller_country
PIVOT Dummy_users.buyer_country;

but I don't see PIVOT is acceptable anywhere in mysql (no search results which offer that solution), and also for some reason it broke when I tried to add the distinct element to the count.

Can you help me with the syntax please?

Here is some dummy data if needed:
transactions: https://docs.google.com/spreadsheets/d/1vHzjmSkaQ5G5lTdV_M6h7gp8zR4Kfqz70gL4TeID2jQ/
buyers: https://docs.google.com/spreadsheets/d/18HXZ_GotqEMDhxytYKxNeD4W6-PfqUvkcWh3DBuCYPA/

Many thanks!


texts: Performance recommendations stuck on 'Validating'

texts:
I have been tasked with migrating a 'database from hell' to Azure SQL. It has some complicated and long-running queries (20 seconds on the S6 tier, >600 seconds on S4 tier) and I'd like to optimise performance so we can drop to a lower tier, without making any structural changes to the database.

I haven't explicitly created any indexes myself. I don't really have the skillset to do this properly as I'm not a DB admin.

I was hoping the the Azure performance recommendations would help by creating those indexes that are beneficial and avoiding any that would hurt performance. But so far results have been very disappointing. It has created one index resulting in just a 0.13% DTU improvement. It has rejected another index, and is currently validating two more although it looks like it will reject both (overall DTU regression).

But it has now been stuck on validating for nearly 24 hours, even though the database has been serving a normal workload all that time. (There are four different problem queries, invoked every ten minutes.)

Is this normal? Can coerce Azure into generating and validating performance recommendations a bit more quickly?


texts: SELECT DISTINCT on one column, while returning the other columns?

texts:
I have a query which uses three lookup tables to get all the information I need.  I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.

My SQL code:

SELECT acss_lookup.ID AS acss_lookupID,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
ORDER BY product_displayheading ASC;

I want to get all the products from this query but I only want to get them once because I'm populating a drop down menu for a search application.  I want the user to be able to select from the products that are in that table (that's why I only need them once).

Is this too complicated?  Should I use a more simplified approach?


texts: Disable dbo schema fallback when table doesn't exist in default schema

texts:
I've properly performed these SQL commands in SQL server 2017:

CREATE DATABASE DataTeamDatabase

USE DataTeamDatabase

CREATE LOGIN DataTeamUser
    WITH PASSWORD = 'YourStrong!Passw0rd',
    DEFAULT_DATABASE = DataTeamDatabase;

CREATE SCHEMA DataTeamSchema

CREATE USER DataTeamUser FOR LOGIN DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema

ALTER ROLE db_datareader add member DataTeamUser
ALTER ROLE db_datawriter add member DataTeamUser
ALTER ROLE db_ddladmin add member DataTeamUser
GRANT CONNECT TO datateamuser

CREATE TABLE dbo.TestTable (column_1 int)

ALTER USER DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema;

Now, let's say that I want to query the TestTable (which I know is not existing in DataTeamSchema):

SELECT * FROM TestTable

Since the table TestTable is not existing in the DataTeamSchema, SQL server is looking it up in the dbo schema and uses it.

Is there a way to disable this behavior? I know that prepending the schema name to the table name will work, but I would like to avoid that because of different reasons.

I searched through the SQL server documentation but didn't find anything so far.

Thanks,

Mattia


texts:
I have an existing Point of Sale app that is used for golf courses. I have a new feature request from one of my courses that would allow users of my POS to sell golf lessons to customers. Here are some high level entities:

Instructors - identified by email
InstructorAvailabilitys - contains title and reference to Instructor, possibly a start and end date?
InstructorAvailabilityDays - each day of the week the instructor is available, also need a way to have 1 to many start and end times in each day
Lessons - lessonTypeID (private, clinic, etc.), MaxStudents
LessonTypes
GolfCourseCustomers - existing table used to identify customers but will double as "students" in this case
LessonCustomers - connects lessons with golf course customers
GolfCourseProducts - I'm guessing I'll just add a LessonID to this table?
OrderItems - has reference to GolfCourseProductID, this is what gets added to the cart when ringing up a transaction and holds the final price charged for the item

So my biggest point of confusion is as follows.
Scenarios

My name is John and I'm a golf pro. I'm available Mon - Thu from 8am to 3pm, I charge $100 for private lessons, I charge $80 for on course lessons.

My name is Allison and I'm a golf pro. I'm available Mon - Sat from 8am to 11am and then from 2pm - 5pm, I charge $80 for private lessons.

My name is Tom and I work in a pro shop. I sell lessons for Allison and/or John and I need a way to ring these up as products including choosing a lesson type and an instructor.

Problems

days off.  How do I structure exceptions to instructor availability?

multiple availabilites per instructor, how do I render these in the calendar?  Should I limit availability to 1 at any given date?  In other words should InstructoryAvailabilys have a start and end date and should I force users to not overlap them on the client side?

Lessons can be any length of time, different lessons take different amounts of time (on course is longer than private).  I think we give the instructor the option of only allowing "top of the hour" bookings.  Any lesson type can take place in for any time range.
I'm open to any and all advice on database design here that won't restrict my options. This is my diagram so far:


texts: set serializable isolation for update query postgresql --9.4

texts:
CREATE TABLE xtable(xtableid integer, xcolumn integer)
CREATE TABLE ytable(ytableid integer, ycolumn integer)

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

I want to set SERIALIZABLE isolation for the UPDATE and SELECT query inside but I get an error saying

ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"

How can I use this isolation level in PostgreSQL?  I don't have a live issue, just want to understand how it works.


texts: Backup a remote database, from my machine, to another server (SQL Server 2008)

texts:
I'm trying to backup a database, of a remote server, from my machine, to another server.

 SERVER1   >>>  BACKUPSERVER
      \             /
       \           /
        \         /
         My Machine

I have SERVER1 with some databases. I'm connected on it from my machine, and I'm using:

BACKUP DATABASE X
TO DISK ='BACKUPSERVER\Database.bak'

And I'm getting this error:

  Msg 3201, Level 16, State 1, Line 1
  Cannot open backup device 'BACKUPSERVER\BACKUP LOCAL'.
  Operating system error 1265(The system detected a possible attempt to
  compromise security. Please ensure that you can contact the server
  that authenticated you.).

OBS: I will not change anything, because I contact the guys from infrastructure, and told them to change the Service Login (just to test), and they told me that they will migrate this server to another one and I should not be worried about it (I'm DBA jr, so I'm learning the server maping from here).

But I'm curious about that error. And if a face this again, how could I solve it?

Thanks guys.