Primary Key Int vs String I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes.

In terms of the table design, the way I see it, there are at least two realistic options.

Have an autoincrement int PK, with an additional unique-constraint placed on the column that holds the name data.
Drop the integer autoincrementing PK, and just use the name as the primary key, it will by definition then have a unique constraint.

This attributes table will be joined all round the place, like say many thousands of products each having one or more attributes associated with them.

I am wondering if there is any disadvantage to #2 over #1?, like the indexing costs of adding new attributes down the path, or, are joins slower. What is the best practice?

I suspect #1 is the best option from performance, but #2 has advantages for readability of the data (ie pk:product_weight is more intuitive than pk:2)

Database is MySql, and, I am writing code using a SqlAlchemy/Python ORM abstraction layer, with migrations etc...

There is no problem in using the name as a primary key, provided that you never update that column.

Avoiding unnecessary constraints is a performance gain.


PROXYSQL configuration over mysql galera replication can I have same databases connected to two different proxysql configuration?

Yes, I would think so. In the ProxySQL blog post here there is a reference infrastructure which has multiple ProxySQL instances connected to the same set of database nodes. It also says:

  We usually recommend to setup ProxySQL instances on the application boxes for availability reasons

That said, for a Galera cluster, I'm unsure whether the ProxySQL instances agree between themselves which Galera node is the "master" and which are the "slaves" - this is especially relevant if you're using read-write splitting, which is something you may want to do with a Galera cluster in order to avoid certain deadlocks not seen with ordinary MySQL configurations. (See also: Galera’s big gotcha for MySQL users.)


MySQL: Select two values of two columns and compared their values exchanging them each other I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

UPDATE

I noticed a long execute time for this query (about 40 secs)!

This is the output for my table relations:

| relations | CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35207 DEFAULT CHARSET=latin1 |

I guess, from the description, that you first need all rows with rows (x,y) where no other row (y,x) exists. Then select a random row from them:

SELECT r.leader, r.subscriber
FROM relations AS r
WHERE NOT EXISTS
      ( SELECT *
        FROM relations AS r2
        WHERE r.subscriber = r2.leader
          AND r.leader = r2.subscriber     -- or perhaps without this
      )
ORDER BY RAND()
LIMIT 1 ;

It would be useful to have two indexes, on (leader, subscriber) and on (subscriber, leader). If the table is very big and performance is still not good, you may have to change how the random row is selected. The ORDER BY RAND LIMIT 1 solution works ok in small tables but it's quite slow in big tables as it has to sort all the matching rows before it chooses one.


Invalid Column Name on CONCAT_WS Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.
I have two tables cases and legal_entity the data on the two tables looks like this
legal_entity

first_name
last_name
address_1_zip

Maria
Munoz
33122

John
Glenn
10003

Melissa
Johnson
90210

cases

style
case_number
create_date

Munoz, Maria
SL12345
2021-03-01 11:45:26.657

Glenn, John
SL45678
2021-03-02 12:10:14.552

Johnson, Melissa (DNY)
SG42561
2021-04-10 01:06:10.214

What I am trying to do is combine the last_name and first_name fields from the legal_entity table with a comma between them, and match (contains) it against the style field in the cases table. For everything in the last month basically. This is the query I'm using
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON LE.CombinedName=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

The issue I run into is that it seems to not like the LE.CombinedName so I'm not sure how to reference the concatenated string against the style column in the cases table.
Any help would be greatly appreciated.

You can use only columns from the joined tables or in your case concatenated  version of it
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;

INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;

INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON CONCAT_WS(', ', LE.last_name,LE.first_name)=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :-----------
Maria      | Munoz     |         33122 | Munoz,Maria
John       | Glenn     |         10003 | Glenn,John

db<>fiddle here

CREATE TABLE legal_entity
    ([first_name] varchar(7), [last_name] varchar(7), [address_1_zip] int)
;

INSERT INTO legal_entity
    ([first_name], [last_name], [address_1_zip])
VALUES
    ('Maria', 'Munoz', 33122),
    ('John', 'Glenn', 10003),
    ('Melissa', 'Johnson', 90210)
;
GO

CREATE TABLE cases
    ([style] varchar(22), [case_number] varchar(7), [create_date] varchar(23))
;

INSERT INTO cases
    ([style], [case_number], [create_date])
VALUES
    ('Munoz, Maria', 'SL12345', '2021-03-01 11:45:26.657'),
    ('Glenn, John', 'SL45678', '2021-03-02 12:10:14.552'),
    ('Johnson, Melissa (DNY)', 'SG42561', '2021-04-10 01:06:10.214')
;
GO

SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    legal_entity AS LE
INNER JOIN cases as C ON PATINDEX( CONCAT( LE.last_name,', ',LE.first_name,'%'),C.style) > 0
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date
GO

first_name | last_name | address_1_zip | CombinedName
:--------- | :-------- | ------------: | :--------------
Maria      | Munoz     |         33122 | Munoz,Maria
John       | Glenn     |         10003 | Glenn,John
Melissa    | Johnson   |         90210 | Johnson,Melissa

db<>fiddle here


Security Risks with having Test Stored Procedures that have SQL Injection Vulnerability I noticed that some of the stored procedures used in our integration tests for data generation have SQL injection vulnerabilities. These procedures are taking into string parameters to build a SQL string and executing it. However, these procedures are only ever used for testing.

At first, I thought it was a big issue. However, if these procedures aren't accessible to the public, maybe it's not a problem. But I could be missing something. Are there any potential issues with this setup and my assumptions?

Some possible issues I could think of:

1) Test database somehow has access to a non-test database

2) Accidental procedure copying to a non-test database

3) People might see the procedure and think it's good practice

Though 1 & 2 still wouldn't be accessible.

One of the biggest IT security misconceptions people have is that there is little or no risk if the system is not public-facing.  There is certainly less risk, but there is still a huge amount of risk because many breaches are coming from within the attacked network.

Assume that your network is infiltrated by a hacker by gaining remote access to a workstation.  From this workstation, they can then launch attacks on all of your systems, not just the public-facing ones.  All of the millions of phishing attempts that are made each day would not be occurring unless some of them were successful, so you have to acknowledge that this type of infiltration does happen, and could happen on your network.

If a hacker were to discover this stored procedure, they could then add objects to the test server, which could possibly end up being promoted to the production server.  You have to assume that once you have bad code somewhere that it can spread.  The fact that there is a SQL injection vulnerability seems to indicate that your developers are not as concerned about security as maybe they should be.

How much damage could be done and how easily would depend on how many other security vulnerabilities exist.  It's certainly unlikely that a hacker would be able to find this stored procedure, but it's certainly possible if they are sniffing network traffic and your client connections are not encrypted.  And how secure are the servers that are calling this stored procedure?  You have to assume that the test code could be decompiled, which would allow someone to discover it and how it's used.

Another misconception is, "there's nothing important on this server."  That may be true, but it could be used as a bridge to servers that do have something important.  The current line of thought is that your network will be breached and there will be damage, so every system has to be locked down as much as possible to minimize the damage.

And as you mentioned, a less-seasoned developer might see this and think it's an acceptable practice, and then some emergency code that is not properly reviewed ends up in production.  So it certainly can be seen as setting a bad precedent.


Oracle: Connects with sqlplus but not from perl I need to connect to an Oracle database from a perl script, it works in my tests and when using sqlplus, but not in production. This is a summary of the situation:
Oracle 11g XE (11.2.0.2.0) : (Test environment)
   sqlplus : OK
   perl    : OK

Oracle 11g    (11.2.0.4.0) : (Production environment)
   sqlplus : OK
   perl    : ERROR

The error reported is: Connect failed because target host or object does not exist
I have triple checked the connection settings and are not mistaken.
Specifics:

Test Server: Windows Server 2012
Production Server: Windows Server (Unknown version)
Client: Ubuntu 18.04 x64
Perl: 5.26.1
SQL*Plus: 12.2.0.1.0

SQL*Plus command:
sqlplus64 user/password@192.168.0.1/db_sid

Perl script:
#!/usr/bin/perl
use DBI;
$driver = "Oracle";
$sid    = "db_sid";
$host   = "192.168.0.1";
$dsn    = "DBI:$driver:sid=$sid;host=$host";
$dbh    = DBI->connect($dsn, "user", "password");

Note: I won't be able to test or execute anything in the production environment until next month.
What could be the issue?

The connection string format is wrong. Use

$dsn = "DBI:$driver:$host/$sid";


pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace I am going to move a database from the old server to a new one.

the old one runs PostgreSQL 9.1, the new one has 9.3.
I want to change the database name.
I want the new database to be in a dedicated tablespace (the old one is in default) - including indices, etc.

Unfortunately, I have no chance to experiment (and HDDs of the old server start to fail) so I ask for the options for pg_dump/pg_restore that would work in my case.

I have completed the migration with no problems.

Creating the dump is easy:

sudo -u postgres pg_dump --verbose --no-tablespaces --format=directory --file=/backup/path old_database_name

Restoring on a new instance: first, create a new tablespace, and a target database in that tablespace. Then import your dump like this:

sudo -u postgres pg_restore --verbose --dbname=new_database_name --jobs=8 /backup/path/

You can adjust number of concurrent jobs dependent on Your hardware - processor cores, I/O subsystem performance, etc.

Now we have all the data in another database, with different name, in a dedicated tablespace, on a new server running newer PostgreSQL.


What are the criteria for a sql staying in the V$SQLAREA? I needed to pick up details from a SQL result but 5 minutes after running the SQL it was no longer under v$sql and V$sqlarea. What are the criteria for Oracle to keep the query in those views?

v$sql and v$sqlarea are views into the SQL area in the library cache (which is part of the shared pool, in the SGA).

What you see in those views are parsed/"compiled" statements that are in the cache. When a new statement comes in, Oracle checks to see if it already has it in that cache. If it does, it re-uses that (called a soft parse). (See SQL Sharing Criteria.)
If not, it has to do a full parse and find room in the cache to store it (hard parse). If there's no room left in the cache, older statements are aged out.

How long a statement stays in cache depends on the size of the cache and how your database is used. If you have "few" distinct queries and use bind variables everywhere, and a sufficiently large library cache, it should be mostly static after "warmup".
If you're not using binds at all and have many distinct queries, or simply have a very small library cache, it'll recycle very fast.

For more details, see Configuring and Using the Shared Pool and Large Pool in the Memory Configuration and Use section of the tuning guide, especially the Library Cache Concepts and following sections.


Does SQL Server Query Store capture parameter values? The new Query Store introduced in SQL Server 2016 is great. It's a nice replacement for much of what I used to do with the older Profiler tool. However, I haven't found a way to capture parameter values associated with individual calls to the high resource-consuming queries it sniffs out. Is this possible?

I understand that Query Store deals more with aggregated data vs. individual calls, so I suspect I might be out of luck here. When I find a slow query, I find it handy for troubleshooting to also have the parameters associated with one of its slowest calls. I'd like to know how to do this using the latest and greatest tools. (I do not miss using Profiler!)

Security-wise is Query Store any less locked down than Profiler? I would think it need to capture data from individual calls at some level in order to calculate aggregates. Just not sure if it stores any of it.

Depending on the plan, you should be able to see the ParameterCompiledValue for each parameter by inspecting the XML plans saved in sys.query_store_plan.

The list is typically contained towards the end of the xml plan, and can be found in the ParameterList node.

This doesn't provide the value for every execution, but does give you an idea of what the plan was compiled with.

If you truly need to get the value for each execution, you could create an Extended Events session to gather the details.


Master slave replication with different mysql version is on both slave and master Currently, we have MySQL master-slave replication. Both master and slave servers have Mysql 5.5 Currently I need to add one more Master server having version 5.7.

Is it possible to add Multiple masters to a single slave server?

  One master and slave is having Mysql 5.5 and new Mysql server is 5.7

Is it possible to configure Master-slave replication with different MySQL version?

When replicating, the master version must be the same or less than the version on the slave. If this isn't the case, the master may inject binary log messages the slave doesn't understand.

The master version / slave version should stay between on major version. So a 5.6 master is ok to a 5.5 slave but a 5.7 slave is too much, there may be deprecated messages in 5.6 that a 5.7 slave will not understand.

Multiple masters are possible with 5.7 multi source replication..


What ports does SQL Server need to communicate with a web server? If I have a web server trying to communicate to a SQL Server through a firewall, and the firewall is completely closed both ways (inbound AND outbound), I know I have to open port 1433 from the web server to the SQL Server in that direction.

However, do I need to open any ports from the SQL Server to the web server for the SQL activities to work? Does SQL Server respond back with any other ports?

I would suggest following the guildelines provided by Microsoft here:
TCP/IP port numbers required to communicate to SQL overall a firewall

  By default, when an application requests a socket from the system for an outbound call, a port number between the values of 1024 and 5000 is supplied.

You brought up the above statement in your comments to the other answers. This is something you have to handle on your firewall. The client chooses the port it wants to be communicated back on. As stated later down in the article above your firewall rules have to allow the dynamic allocation to occur.

I don't work with the application side but there might be something you do to force the port it wants to talk on.


Setting stack-size ulimit for mongodb to reduce connection overhead I am using MongDB 2.4.9 and working on performance tunung. With reference to the ulimit settings for mongodb, in my linux environment (ubuntu 12.04) the default setting for ulimit -s is 8192. MongoDB does not specify any default value for ulimit -s (stack-size). However this case study suggests to set stack-size to 1024. How does the default stack-size 8291 impacts mongodb performance. Does changing this value to 1024 help to reduce connection per overhead in order to improve performance?

The default stack size for MongoDB is already 1024, not 8192 (it is set in the code, not as a system setting) and has been since version 1.8.3 (see SERVER-2707), so you are already seeing the benefits of a lower stack size.


dropping a table does not delete the directories on the disk I am just testing the drop table command in Cassandra 3.11. The sequence is as follows:

Load data worth 10gb
drop the table
on the node,

nodetool flush
nodetool clearsnapshot
nodetool cleanup

Still I see those SSTables directory under the data folder (using default auto_snapshot = true).

select count(*) .. shows ZERO, which is correct.

Why the folders are not getting deleted?

Did you clear the snapshot of all clusters?

Clear it with

nodetool -h <ip address> -p <port> clearsnapshot


Is Mysql.pdb file useful? I've search on google and stackoverflow and dba.stackoverflow and couldn't find any content related to this question. The basic question is Mysql.pdb useful somehow?

Reason to remove: The file is big[160mb]. I'm about to deploy a desktop application that download and installs mysql on the local machine. I store the zip in my web filestorage[traditional host and amazon s3] so I can rely on myself and prevent from Oracle removing it, etc.

The mysql version is: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-win32.zip

The installer basically:

Download and Unzips the file
Execute the command "mysqld --initialize --init-file={app}\create_db.sql"
Configure the mysql server aas a Window Service
{Continue installation process}

Can I remove it without having unwanted behavior? Is there any other "useless" file on mysql?

The .pdb files are detailed information for use in debugging or reporting errors in the executables in MySQL. If you decide you don't need this functionality, you can delete the files


Mysql Count(*) incorrect value Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the count returns 3179 records. I'm sure I'm messing something up with it. Here's the two procedures:

Geo Search

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    campaign_inventory.*, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi, cpl desc
LIMIT `start`,`limit`;

END

Search Count

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    count(*) as search_results, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi desc;

END

I'm sure there's something funky. This is the first procedure I've put together and I'm not very familiar with them. But I think the issue may be in the query itself. Any input and suggestions are greatly appreciated. Let me know if there any additional info that would help.

Thanks a lot.

Alrighty well I sorted it out. It was indeed the having clause messing things up. Took advice from a similar issue and wrapped the count query in a query to get the count of the results. Should have dug up the solution earlier but searching for "get count in query with having clause" returns thousands of results for Having Count(*) type of queries. Thanks for the advice though guys. I appreciate it.

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);
SELECT COUNT(*) as search_results FROM (
SELECT
    id, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
) search_counts;

END


Possible to restore a damaged database's PRIMARY filegroup only? I have a database (MyDatabase) with 2 filegroups, [PRIMARY] and [SECONDARY].

The [SECONDARY] filegroup has 2 datafiles, [DataFileA] and [DataFileB].

[DataFileA] is damaged (even after DBCC CHECKDB process).

I tried to solve it with:

DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS) but it didn't help.
Deleted the damaged datafile [DataFileA], with no success.

The [PRIMARY] filegroup is ok though.

Can I backup just the [PRIMARY] filegroup and restore it (under a new name) and create a new database from this single filegroup?

Since the database is ONLINE, you have two main options:

Create a new database with the same schema for [PRIMARY] filegroup objects as the old database, and transfer data using any of the tools available (e.g. SSIS, bcp, linked server). Depending on the complexity of the existing schema, constraints, and relationships (e.g. foreign keys) this may be quite time-consuming.
Take a partial backup of the [PRIMARY] filegroup only. Restore this to a new database and files using piecemeal restore. Finally, mark the filegroup as defunct. Be sure DBCC CHECKFILEGROUP for [PRIMARY] runs cleanly first. You should export the schema and data to a safe place as in option 1 as well. As you know, having tested backups of your valuable data is priority #1 :)


How do I allow remote connection in SQL Server 2008 Express I've just installed SQL Server 2008 Express. But when I try to connect to database engine via SQL Server Management Studio I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server" error. I want to allow remote connections to the server but Surface area configuration is missing in 2008. If I cannot connect to the server instance then how am I supposed to configure this feature? Thanks ahead.

Go into the SQL Server Configuration Manager (SSCM) and under Service Protocols enable TCP/IP for your SQL Server Express instance (most likely called SQLEXPRESS).


(SQL) User tables are separated into two sections Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. The number of tables in each partition varies each time the tables are dropped then recreated. Each table is unique/there are no repeating tables.

I am having difficulties interfacing with the DB via php/OCI (I can access the column headings, but no records are found) and was wondering if this could be the reason. This is after multiple INSERTs, and SQL PLUS queries confirm the existence of records.

Overall, what does this grouping mean, is it problematic and how can I resolve the tables into one grouping?

That is just the paging in SQL*Plus. By default the paging value is 14, that includes a blank line at the start of each page, the line with column headers, the line with heading separators (hyphens) and the rest (11 rows) is for user data.

If you increase the pagesize, the result of your query will fit in to 1 page:

set pagesize 20

It has nothing to do with php/OCI.

SET PAGES[IZE] {14 | n}


relation "table" does not exist i created the following schema , inside that schema i created a table and inserted a some values.

After that i created a function that get a value in the table based on an ID.

Here is all the declarations :

CREATE SCHEMA pckg_50d_facture;
CREATE TABLE pckg_50d_facture.valeur(val_id text PRIMARY KEY, val text);

CREATE OR REPLACE FUNCTION pckg_50d_facture.const(id text) RETURNS text AS $body$
DECLARE
result text;
BEGIN
  --EXECUTE 'SELECT val FROM pckg_50d_facture.valeur WHERE val_id = id' INTO result USING checked_user, checked_date;

    SELECT val INTO result FROM pckg_50d_facture.valeur WHERE val_id = id;

 return result;
END;
$body$
LANGUAGE PLPGSQL
;

But when i call the function const i get the following error :

ERROR:  relation "valeur" does not exist

I tried to Grant some rights as follow :

GRANT USAGE ON SCHEMA pckg_50d_facture TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA pckg_50D_facture TO PUBLIC;

but still not working.

The error message is unrelated to privileges. The table valeur simply cannot be found. There must be some kind of confusion. My educated guess:

You call the function without schema qualification and accidentally execute a different function of the same name, because pckg_50d_facture is not in the search_path (or is there but in a later position). See:

How does the search_path influence identifier resolution and the “current schema”

Or you connected to the wrong database / database cluster by mistake.


Trying to allocate 3G for buffer pool in InnoDB fails I'm trying to boost my InnoDB driver on my Windows 7 PC since each page request takes just about 1.7 seconds to load versus 0.002 seconds on my Ubuntu 12.10 workstation.

The setting I'm playing around with is the well-known innodb_buffer_pool_size line, which should increase the performance a lot, and it did on my Ubuntu workstation - but my Windows 7 workstation can only seem to allocate 1G of RAM, my system currently have 16GB.

If I try to exceed 4GB I get [ERROR] innobase_buffer_pool_size can't be over 4GB on 32-bit systems, so I try to allocate 3G but now my server wont start, same goes for 2G. 1GB works but did only contribute to about 0.1s worth of speed increase - which isn't much.

Is there something more I have to account for on Windows to get better performance, and why can I not allocate more than 1G of RAM? (I get no errors when I try to allocate more than 1GB, the server just won't start)

Since

show variables like 'version_compile%'; gives you

version_compile_machine : x86
version_compile_os : Win32

Your OS is Windows 7 x64 OS

it looks like you simply need to upgrade the version of MySQL to the 64-bit Windows version.


Replication using Datagaurd to FAR DR We have already setup Oracle replication using Dataguard from a primary active site to a near DR site. The near DR is a passive site.

Is it possible to replicate from the Near DR (Passive) site to Far DR site using dataguard?

Yes it is possible.

Cascaded Redo Transport Destinations

  A cascaded redo transport destination (also known as a terminal
  destination) receives primary database redo indirectly from a standby
  database rather than directly from a primary database.

  A physical standby database that cascades primary database redo to one
  or more terminal destinations at the same time it is applying changes
  to its local database files is known as a cascading standby database.

More details at the above URL.


InnoDB primary key efficiency I've read a number of articles regarding efficiency of primary keys depending on the storage engine, and I am left confused.

Given a simple many-to-many table with two fields, storeId and zoneId, which one of the following designs is the most efficient with InnoDB and why?

using the two fields as a composite primary key:

CREATE TABLE store_zone(
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(storeId, zoneId)
);

Using a specific auto increment primary key:

CREATE TABLE store_zone(
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    storeId INT(10) UNSIGNED NOT NULL,
    zoneId  INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY(storeId, zoneId)
);

Notes:

I need a unique key on the (storeId, zoneId) pair anyway
I have foreign keys to the store and zone tables, not shown here for readability, so in both cases there's an extra required index on zoneId as well

Analysis
Since we are talking about InnoDB, let's zero in on the gen_clust_index. It's a special index that keeps the PRIMARY KEY and associated row data accessible from the same InnoDB pages.
According to MySQL Documentation on the gen_clust_index

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

This being the case, you would really feel efficiency or deficiency in the following areas:
DiskSpace
Having the single auto_increment column as the PRIMARY KEY keeps the overall size of the PRIMARY KEY smaller that that of having two columns. Why? BTREE pages would be twice as big if the PRIMARY KEY was two INTs instead of one. This becomes even more painful if you use foreign key constraints and secondary indexes as they must subsequently blow up in size as well.
In this instance, you would choose the second schema for better index usage.
Insert Performance
Inserting row data into an InnoDB table that has two UNIQUE indexes calls for twice as much BTREE management and unique checks.

One for the gen_clust_index (dictated by auto_increment)
One for the other UNIQUE index (dictated by (storeId, zoneId))

Additional microseconds for each additional UNIQUE index would add up in CPU time when inserting millions of rows.
In this instance, you would choose the first schema for faster INSERTs.
Conclusion
The diskspace and insert performance issues pretty much force you to choose. Obviously, you choose which one to live with. If you use foreign key constraints that represent (storeId, zoneId), then you need the second schema using the auto_increment as the reference in outside tables.


Postgres memory usage too low I have a 400GB Postgres 9.3.4 DB running on Fedora20, and a few indices per table. I have upped shared buffers to 4GB (the system has 16GB available), set shm in the kernel and set the effective_cache_size to 12GB. work_mem is 60MB

Then i'm running hundreds of queries per second against the DB for prolonged period. My entire system (with OS) is only using 3GB of RAM and no swap.

Postgres isn't really using the RAM it could be using and I'd rather max out my RAM and gain query/index performance if possible.

Am I missing something config wise?

Postgres isn't really using the RAM it could be using

Actually, it probably is.

PostgreSQL relies on the Linux kernel to cache disk blocks. That's what effective_cache_size is about - it's telling PostgreSQL what kind of cache size it can expect the kernel to maintain.

Run:

free -m

You'll probably see almost all RAM in use as buffers/cache. Which is exactly what you want.


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

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

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

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

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

rmdir ...\pg_tblspc\53605151

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

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

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

Postgres tablespaces are a symbolic link in the pg_tblspc directory. That link must appoint to a valid place for the tablespace files.

Please, make a link again, pointing to the write place.

You can see the tablespaces locations on psql client with the \db command or running the query bellow:

SELECT
  spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

See the documentation for more details:

https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html


Cannot connect Power BI Desktop to Azure SQL! I'm trying to connect Power BI Desktop to Azure SQL, but I get "We couldn't authenticate with the credentials provided. Please try again."

it works fine if I try from Microsoft SQL Server Management Studio I can connect with no problems with the same username/password

what I'm missing?

To get around this problem, notice that "Windows" is highlighted at the left of the dialog box. I clicked on Database, entered the database credentials, and then it worked.


Do i need streaming replication slots as many of replicas in postgres? Correct me if I am wrong but I’m thinking that if I have 4 different read only replica servers I need 4 streaming replication slots, one for each of them?
Or all of them can you use one?

Each replication slot requires a unique primary_slot_name in the recovery.conf, so you will need to have 4 connections for a streaming replication slot.


Query to find the subtract of each column from the following column Hi everyone hope you are doing well
   I have a table with this structure
   Table 1:

 [dbo].[ValueTest] (Date , PorojectCode , Value)

It has this data

('1397/04/01' , 'A' , 100) , ('1397/04/03' , 'A' , 120) , ('1397/04/05' ,'A',140)

I need to have the result below :

 ProjectCode    Value
-------------   -------
    A              20
    A              20

I Mean I need to have the subtract of the value of each column from the following column
I have tried this query but I did not get the result

Thanks for helping me

Select ProjectCode ,
       (Select v.Value - v2.Value
        From [dbo].[ValueTest] V2
        Where v2.DateKey >= V.DateKey)
from [dbo].[ValueTest] V

You could use LAG() to subtract the value from the previous value, ordered by DateKey.

SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V;

But this will give you an extra NULL for the first value since we are doing 100 - NULL (Non existing row since the row with val 100 is the first one).

One way around this is by removing the NULL with a CTE

;WITH CTE
AS
(
SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V
)

SELECT * FROM CTE
where val IS NOT NULL;

Result

ProjectCode val
A   20
A   20


MySQL ignoring some params from my.cnf I have a virtual box with 2 installed instances of MySQL.

First instance:

- /etc/mysql/my.cnf
- /var/lib/mysql
- port = 3306

Second instance:

- /etc/mysql2/my.cnf
- /var/lib/mysql2
- port = 3307

I can connect to both. The first instance is fine. The second is not.

MySQL doesn't ignoring some params from my.cnf file (socket, port, pid-file, data-dir...). But if I'm trying to change something else: cache, buffers, log files it completely ignores changes after restarting.

I need to make MySQL read all params from my.cnf.

I tried:
- to move my.cnf from /etc/mysql to /var/lib/mysql;
- commenting out !includeir /etc/mysql/conf.d.

Could someone guide me?

I had a similar problem with a box I was diagnosing for performance issues, it turned out that there was an incorrect / misplaced heading entry within the my.cnf for that server, so MySQL was ignoring all the settings within that section...

For example, if you had the following;

[mysqld]
######################################################
# First, the generic server configuration items
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock
datadir                 = /mysql/data

[client]
#password               = your_password
port                    = 3306
socket                  = /var/lib/mysql/mysql.sock

key_buffer_size         = 256M
table_open_cache        = 1024
query_cache_size        = 32M

Then the buffer tuning section is ignored as it is within the client section and not the mysqld section... Re-arrange the sections, and all works as intended.

Hopefully, it is as simple as this for you!


Database design for changing number of fields Google Contacts lets you to enter more than one phone number, or email address for a contact.  You can add as many as you want. I don't know if there is a limit, other than practical list size. To build this type of structure I normally create a table with userid, key, and value fields, so a user can keep an unlimited number of records.

I've read that key-value table design performance is low and they do not suggest it.  I have not used this type of structure for more than 50-100 users. At the moment, I am working on a big project and I need to keep information about accounts.

Without Key-Value pairs, I will define lots of fields to keep information of each account. I need a new kind of field later, I will add it for all account records.

I need ideas / sources / documents about how big businesses are building this type of structure.

For now, I expect to reach 2000-3000 customers in next 3 years. I normally use MySQL and PHP.

P.S. Each account will have at least 5 different kinds of information to start. In time, this will increase according to requirements.

KEY-VALUE NO!!!

A table for phone numbers -- sure.  It would have userid, phone_num, and (if you like) a phone type, such as ENUM('fax', 'home', ...).  Then JOIN to the main table.

To keep unlimited, unsearchable data, have a column with a bunch of key-value stuff.  I like to do it in JSON, then compress it (in the app), and store it into a BLOB or MEDIUMBLOB.  That makes it easily accessible by the app, reasonable compact, and quite open-ended.

In the table, have only columns that you need to search on; put the rest into the extra JSON column.

More discussion:
http://forums.mysql.com/read.php?125,428546,428769#msg-428769
http://forums.mysql.com/read.php?125,402095,402218#msg-402218

Another approach is MariaDB's "dynamic columns".  This even lets you index randomly added 'columns'.

2000-3000 customers -- Yawn.


Optimise and merge SELECT query results from 2 tables I have a schema like this:

And a query like this
SELECT
  `users`.user_id,
  `users`.user_name,
  `users`.user_email,
  `users`.user_password_hash,
  meta_a.first_name,
  meta_b.last_name

FROM (SELECT m.meta_value AS first_name
   FROM `users` AS u
     LEFT JOIN `usermeta` m ON u.user_id = m.user_id
   WHERE u.user_name = 'demo' AND meta_key = 'first_name'
   LIMIT 1
  ) AS meta_a,
  (SELECT m.meta_value AS last_name
   FROM `users` AS u
     LEFT JOIN `usermeta` m ON u.user_id = m.user_id
   WHERE u.user_name = 'demo' AND meta_key = 'last_name'
   LIMIT 1
  ) AS meta_b,

  users

  LEFT JOIN `usermeta` USING (user_id)
WHERE `users`.user_name = 'demo'
LIMIT 1

Is it possible to optimize/shorten this query to get the same results instead of using a lot of these multiple SELECTs?
So at the end I get all the meta_key and meta_values that are associated to that user_id.

Try this:

SELECT u.user_id,
       u.user_name,
       u.user_email,
       u.user_password_hash,
       fn.meta_value,
       ln.meta_value
FROM   `users` AS u LEFT JOIN `usermeta` AS fn ON u.user_id = fn.user_id
                    LEFT JOIN `usermeta` AS ln ON u.user_id = ln.user_id
WHERE  u.user_name = 'demo'
AND    fn.meta_key = 'first_name'
AND    ln.meta_key = 'last_name'
LIMIT  1


best db and file system for high volume, high qps I'm looking at setting up a DB for a very high volume non-profit situation that also would benefit from high speed updates and searches.

Each transaction record will be relatively small (basic customer information plus 10-12 extra fields) but must be updated/modified literally thousands of times via different updates, reports and screens.  (But I don't need records of each update except in recovery.)

Is MYSQL best for this?  What file system would be most efficient?

Thanks.

G

Virtually any database server can meet your needs, whether it is MySQL, Microsoft SQL Server, Oracle, etc. Performance with all the rdbms' vary a lot based on the following criteria

I/O Performance of the disk (get good, fast, RAID'd disks over high throughput connections)
Performance and Amount of RAM (good and fast RAM, and lots of it)
File System Overhead (use a modern fs designed for databases. Ext4 works fine, but there's OCFS2 that is Oracle's file system, built for databases, or even Oracle's RAW storage where there isn't even a file system in the OS-sense... the database manages the disk entirely.)
Properly Indexed table structure (quick access to data depends upon your data being indexed correctly, where "correct" means most efficient for all your needs.)
Properly Optimized Queries... usually goes hand-in-hand with the correct indexes; the database can try to figure out the best optimization for your query, but it can only do so with correct indexes and statistics. Better yet, fine tune your queries so that the database doesn't have to do a lot of optimization on its own, and you'll be faster and happier for it.
I/O Performance of the network - if you are transferring a lot of data in or out, network performance will matter (otherwise the database has to wait when sending data across the wire).

A few things that really don't matter very much (as far as performance):

Your operating system. Windows, Mac OSX, Linux, and anything else that's modern will do just fine as the operating system layer. Most of your work is done in the database anyway, with occasional tasks needing done in the OS. Pick an OS that will simplify your life (and not complicate it), but modern databases are performant on most OSes.
Your database engine. Yes, there are critical differences between MySQL and Oracle and MS SQL, etc. Does it really matter? If there is a particular feature you must have, then by all means, go for it, but ultimately, all will be sufficient with the correctly configured hardware and tables.

Really, pick a database, an OS, a file system, and build your app. With good hardware and good table design (with indexes and optimization), things should go swimmingly.

All that said, here's what I'd pick (only because I'm most familiar with this type of configuration):

RHEL / OEL / Ubuntu (OS)
Oracle Standard One (or Standard, or EE, depending on your needs) (DB)
Ext4 Journaled File System (FS)
2 2xCore Processors (>2.5GHz)
8 GiB Memory (something around 1066 or faster MHz)
1 TiB HDD (SATA, with >=7200rpms)
At least gigabit ethernet

Swap out the OS, File system, and DB as desired. Your machine should scream and be just fine for a good number of databases, and is probably a bit overkill. Best thing, though, is that you can always add memory and disk space and upgrade your processors if you need more. If necessary, move to a new box, or add boxes (if your database supports it). (Even better: run your environment as a Virtual Machine/Cluster. Then moving to another physical machine is child's play.)


SQL Azure: Drop Constraint and Index This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.

Here is my question:
When dropping Constraints, are their indexes dropped as well?

Basically, I'm asking because I want to modify an existing constraint. I want to change the foreign key to another column.

Here was the original query to create them:

-- Creating foreign key on [Industry_Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([Industry_Id])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([Industry_Id]);
GO

Here is what I'm thinking of doing:

IF OBJECT_ID(N'[dbo].[FK_CourseIndustry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_CourseIndustry];
GO

-- Creating foreign key on [IndustryId] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [FK_CourseIndustry]
    FOREIGN KEY ([IndustryId])
    REFERENCES [dbo].[Industries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseIndustry'
CREATE INDEX [IX_FK_CourseIndustry]
ON [dbo].[Courses]
    ([IndustryId]);
GO

Is that enough and will it work? Honestly, I don't mind if it's not an elegant solution just so long as it works.

Any piece of advise or information would be highly appreciated. Thanks!

When dropping Constraints, are their indexes dropped as well

Yes. And No. Constraints that are implemented by an index (eg. Primary key constraint and Unique constraint) will drop the index that implements them. In fact, the only way to drop the index that implements a constraint is to drop the constraint. Other constraints do not have an index to speak of, so there is nothing to drop.

You give an example of a foreign key constraint and ask if dropping the constraint will drop the index. a foreign key constraint is not implemented by an index therefore it has no index to drop. What you're tangentially hinting at is that to efficiently check a foreign key on update/on delete options, an index is required on the referencing key. It is up to you to create, drop and alter this index as appropriate to keep it aligned with the foreign key. The system will not update the index definition for you, since is unaware that the index is in any way related to your foreign key.

  Is that enough and will it work?

We really can't answer it. Will creating an index on IndustryId help the FK_CourseIndustry foreign key constraint? Yes. Will your upgrade/migration script always work, be idempotent and handle all cases? Unlikely. it depends on your current schema, on how you run your migration, etc. For instance, is not at all clear if you drop the Industry_Id column or not by the time you run the upgrade in the example.


Another "log_min_duration_statement is ignored" question Following the recommendations in log_min_duration_statement setting is ignored I set these two values and then reloaded.

log_min_duration_statement = 2000
log_statement = 'none'

However, I'm still getting lots of sub-second BINDLOG, BEGINLOG and PARSELOG entries in the log file.  What am I doing wrong?

Check individual users and / or dbs for settings that override server settings for logging:

smarlowe=# alter user smarlowe set log_statement='all';
ALTER ROLE
smarlowe=# alter database smarlowe set log_statement='all';
ALTER DATABASE
smarlowe=# \drds
                   List of settings
   Role   | Database |            Settings
----------+----------+---------------------------------
 smarlowe |          | log_statement=all
          | smarlowe | auto_explain.log_min_duration=1+
          |          | log_statement=all


How to query the top TWO and the bottom TWO values at the same time in MySql I am able to query the MAX and MIN at the same time but in my case I need the first TWO max and the first TWO min. I am able to query the top two and the bottom two but separately. Like this

SELECT playerName,teamName, scoreChange,category
FROM players order by scoreChange desc limit 0,2

and

SELECT playerName,teamName, scoreChange,category
FROM players order by scoreChange limit 0,2

the following query is working fine but it results only in max and min values .

SELECT playerName,teamName, scoreChange,category
FROM players
WHERE scoreChange = (SELECT MAX(scoreChange) FROM players)
UNION
SELECT playerName,teamName, scoreChange,category
FROM players
WHERE scoreChange = (SELECT MIN(scoreChange) FROM players)

You can achieve this by simply following this query as well.

    SELECT * FROM (
            SELECT playerName,teamName, scoreChange,category
            FROM players ORDER BY scoreChange LIMIT 2
        ) AS t1
    UNION
    SELECT * FROM (
            SELECT playerName,teamName, scoreChange,category
            FROM players ORDER BY scoreChange DESC LIMIT 2
        ) AS t2


Flat similar rows on few columns - Oracle I wonder is there any way to flat data like this using sql query
maybe you can help me

The data looks like this:

id  name    customer_code column1 column2
-----------------------------------------
1   Tom      Dilbert        null    text
1   Tom      Dilbert        text2   null
2   Bob      Wally          text3   null
2   Bob      Wally          null    text4

My results need to look like this:

id  name    customer_code column1 column2
-----------------------------------------
1   Tom      Dilbert        text2   text
2   Bob      Wally          text3   text4

select id, name, customer_code, min(column1), min(column2)
from the_Table
group by id, name, customer_code
order by id;

Note that if there are more rows with non-null columns this would still only return one per combination of id, name and customer_code (but it does return the result in your question)

Edit (after the question was changed)

To get all values for that combination use listagg()

select id,
       name,
       customer_code,
       listagg(column1, ',') within group (order by column1) as col1,
       listagg(column2, ',') within group (order by column2) as col2
from the_Table
group by id, name, customer_code
order by id;


Full Outer Join With Where Clause I am in need of a way to return all records from both tables BUT have a where condition on ZT1 I have tried both queries below, however Query 1 ignores any row from @TTBL that does not exist in ZT1, while Query 2 ignores the WHERE condition I want to put on the table ZT1 (bc it was changed to an or)

How should my query be written in order to accomplish my desired result of all data from @TTBL AND only data from ZT1 that fall between the date criteria

    Query 1
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

--Query 2
SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
OR ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625'
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name

Give this query a shot. I suspect that the "WHERE" clause is effectively turning your full outer join into a right join. When a record doesn't exist in ZT1, your where clauses are obviously going to evaluate false. By enclosing the AND clause in parenthesis for sanity's sake, and adding some sort of OR clause (I assumed that you should have a field named SaleDate in @TTBL, and that you only want to fetch records where the SaleDate is in that range one way or another), you should hopefully see this problem go away. Let me know how it goes.

SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE (ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625')
OR (Z.[SaleDate] >= '20170601'
AND Z.[SaleDate] <= '20170625')
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name


Why is column value going negative even with checks I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time, such that balance - locked must always be at least x. The query for the same is as below:

UPDATE tablename
SET locked = (locked + x)
WHERE id = 1
    AND balance - locked >= x;

After the locked count is increased, the process does some job, and then comes back and reduces the balance and the locked counts using:

UPDATE tablename
SET balance = balance - x
    , locked = locked - x
WHERE id = 1;

The idea is to get lock for x items, then do some processing, and then reduce the balance by x and release the locks.

The issue I have is that after running the multiple processes for some time, at one point the balance column goes negative. What could be causing this issue?

I am aware of the race condition, and that is why I have the check in the where clause. The value still goes negative, even when balance is 2, and there are only 2 threads trying to lock 1 balance each.

I do check that the first update actually updates a row. Only if the first query affected rows is 1, then I go an do the processing work, and then reduce the locked counts. I ensure locked is never negative by having update query as

update tablename set locked = IF(locked - x > 0, locked - x, 0) where id = 1;

The error cause is a race condition that is happening, because your query was not immune to it.
There are ways to code your queries to avoid it (atomic update, transactions, etc.).
Because you did not use table locking or transactions, you ended up with an error when your query read an updated value from another thread. This produced a negative value.

Proposed solution:

Issue both SELECT and UPDATE in a single statement to avoid race condition, as you have tried, however, select from underlying table into a session variable.
That way, you can have control of the time when you select a value, and not get value from another thread.

I have rewritten your code to use user-defined, session variables.

Update 1 rewritten:

-- @blnc and @lckd are user-defined variables
UPDATE tablename
SET locked = (@lckd + x)
WHERE id = 1
    AND ((@blnc := balance) - (@lckd := locked)) >= x;

Update 2 rewritten:

-- @blnc and @lckd are user-defined variables
UPDATE tablename
SET balance = (@blnc := balance) - x
    , locked = (@lckd := locked) - x
WHERE id = 1;


How to design this database to avoid cyclic dependency? There are two tables:

User
Address

User contains a reference to Address.

Address contains the columns CreatedBy and ModifiedBy, which is reference to User.

How do I design this database to avoid a cyclic dependency?

Instead of searching for tips and tricks (deferred constraints included) I would suggest that you simply design your way out of this "reference lock" -- so try something like this:

Facts

User(UserID) exists.
Address(AddressID) was created by User(UserID).
Address(AddressID) was created on Date(DateCreated).
Address(AddressID) was last modified by User(UserID) on Date(ModifiedOn).
User(UserID) resides at Address(AddressID) since Date(ValidFrom).

Constraints

Each Address was created by exactly one User. It is possible that more than one Address was created by the same User.
Each Address was created on exactly one Date. It is possible that more than one Address was created on the same Date.
For each Address and Date, that Address was modified by at most one User on that Date.
For each User and Date, that User resides at most one Address since that Date.

Logical

As far as mandatory address is concerned, verify that on  the application layer and wrap the loading statements into a transaction -- that way you'll get all or nothing.


For loop in query - how can i use Union? hello friends I ´m working on a module that graphs data from a database, the thing is that I must graph a series of data that went through a mathematical formula first. So far no problem, but I must take data from the months of a year, therefore I must repeat this process twelve times. My truth question is this:

  How can i get just one row an column per response?

If I try something like this ...

DECLARE @cnt INT = 0;
WHILE @cnt < 12
BEGIN
select idestatus from order
SET @cnt = @cnt + 1;
END;

I'll get 12 queries executed & 12 tables And i just need one.
I will eventually transform this into a json, so I would like to have everything in a single response table
.This my query:

declare
@a decimal(5,2) = 0,
@d decimal(5,2) = 0,
@f1 decimal(5,2) = 0,
@f2 decimal(5,2) = 0

set
@a = (
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set
@d = (
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)
set @f1 =
(
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set @f2 =
(
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)

select
 (((@a + @d)/2) *100)/((@f1 + @f2)/ 2) rf

If also some can tell me how to order this by a UNION , that could be really usefull to.

You can simplify your query above with this example, but it suffers from some limitations caused by our lack of knowledge of the schema of your table as well as what your final objective really is.

This should do in one step what your query does, but it has hard coded idEstatus values (which may be ok) and is limited to one month.

It's not clear form your example query if you really need the second half since it appears to be the same date (if you wanted year over year then the correct answer is different).

 ;WITH CTE_Details AS
    (
    SELECT YEAR(dat) AS [year]
        , MONTH(dat) AS [month]
        , idEstatus_Count_2 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 2 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
        , idEstatus_Count_3 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 3 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
    FROM t1
    WHERE idEstats IN (2,3)
        AND dat BETWEEN CAST('1/1/2017' AS DATETIME) AND CAST('1/31/2018 11:59:59 PM' AS DATETIME)
    GROUP BY YEAR(dat)
        , MONTH(dat)
    )
SELECT [year]
    , [month]
    , idEstatus_Count_2
    , idEstatus_Count_3
    , rf = (((idEstatus_Count_2 + idEstatus_Count_3) / 2.0) * 100) / ((idEstatus_Count_2 + idEstatus_Count_3) / 2.0)
FROM CTE_Details


How to make MySQL replication reliable? Master version: 5.5.13-1
Slave version: 5.5.14-1
Binary log format: MIXED

My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. Google gives me a very helpful link.

I'm going to re-sync the database follow this instruction to minimal downtime on the Master. But before doing this, I just want to make sure that this situation is limited in the future. I will scan through the parts in the above to show you what I've done:

Slave database was configured with read-only option
There is some unsafe queries. Does it get some problems with MIXED
based replication?
I replicated all databases
I used both InnoDB and MyISAM storage engines
Developers use alot of temporary tables

Should I:

Don't use the unsafe queries
Ask developers put all the temporary tables into a separated database

Is there anything else? In case of out of sync, is mk-table-sync reliable enough to re-sync automatically? Does anyone use it on production?

UPDATE: Tue Feb 28 23:27:13 ICT 2012

  My Slave database (~ 40GB) has been out of sync from the Master. I
  cannot find anything interesting in the error log.

To get more information about what was happening, the Slave should be started with --log-warnings=2.

OBSERVATION #1

You mentioned Ask developers put all the temporary tables into a separated database

If your developers are using CREATE TEMPORARY TABLE commands to create temporary tables, they need to use CREATE TABLE instead. Here is why:

With MySQL Replication processing a temporary table, this is what occurs

1) Master run CREATE TEMPORARY TABLE
2) Command inserted into binary log
3) Replication copies this over to the Slave's Relay Logs via I/O Thread
4) Slave SQL Thread runs CREATE TEMPORARY TABLE
5) Slave  processes data with that temp table

Once in a while, someone may run STOP SLAVE; to run a backup. If STOP SLAVE; is issued just after step 4, the temp created disappears and so does its data. When you run START SLAVE; Replication breaks instantly complaining the table does not exist. This is normal because when a DB Connections terminates deliberately or accidently, all temp tables opened using CREATE TEMPORARY TABLE in the DB session are dropped. Running  STOP SLAVE; kill the SQL thread who was holding opening the temp table.

The only workaround for this is to create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE. When run STOP SLAVE;, the temp table you created normally does not disappear.

I have seen this happen maybe 10 times in my DBA career. Fixing it using the binary logs to find out the name of the temp tables, to create those tables using CREATE TABLE, then starting replication up was the only maintenance possible without just brute force copying the master.

OBSERVATION #2

mk-table-sync only works on tables with primary keys and/or unique keys. It works maybe 99% of the time. I have seen instances where the checksum of a table on the master and slave were different. I would run mk-table-sync, there were still differences (Of course, I was doing mk-table-sync in circular replication with 3 masters, which can be a little dangerous. Using it in Master/Slave is far more stable)

OBSERVATION #3

You mentioned There is some unsafe queries. Does it get some problems with MIXED based replication?

It depends. The most popular unsafe query is any UPDATE or DELETE that uses ORDER BY ... LIMIT. With SBR, this could possibly cause MySQL to UPDATE or DELETE rows from a table on the Slave in a different order tham that of the Master. With RBR, I believe the exact changes in a row are more identifiable to UPDATE or DELETE on the Slave.

SOLUTION : Avoid using unsafe queries. Then, you will not worry !!!

OBSERVATION #4

I just read your second link. ROFL !!! I am familiar with the poster of the answer.


Unable to select the records from specific partition in mysql? I've created a table using the following query,
CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

Then, i've inserted the data to the table using the partition information and the query as follows,
INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

Now, I'm trying to select the records from specific partition, and my query was,
SELECT * FROM employees PARTITION (p1);

But i'm keep on getting the same error as,
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1) LIMIT 0, 30' at line 1
FYI, initially i was using InnoDB database engine, now i've changed it to MyISAM, but nothing helped me out.

The syntax you are proposing (being able to explicitly select from a partition list) does not exist until MySQL 5.6.2:

  Starting in MySQL 5.6.2, SELECT supports explicit partition selection using the PARTITION keyword with a list of partitions or subpartitions (or both)... [src]

And indeed, your example comes from the MySQL 5.6 manual. I am assuming you are running MySQL 5.5 or even 5.1, but not 5.6.


Issues with setting a global setting in PostgreSQL 9.5/PostGIS2.2 I am trying to set a global setting/GUC variable postgis.gdal_enabled_drivers from the PostGIS extension for a while. I am a non-admin user but can ask admin to change settings if needed. But I tried several different methods following the instructions but none worked.

I tried the old fashion way to change environmental variables. I had the following added to /etc/environment:

POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

so that echo $POSTGIS_GDAL_ENABLED_DRIVERS returns ENABLE_ALL

Now, in my non-admin PostreSQL account,

SELECT short_name, long_name FROM ST_GdalDrivers();

returns 0 rows, meaning no GDAL drivers are enabled.

I also tried to have postgres to:

ALTER SYSTEM SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

but I got an error:

ERROR:  unrecognized configuration parameter "postgis.gdal_enabled_drivers"

I tried to:

ALTER DATABASE my_db SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

but I got:

ERROR:  permission denied to set parameter "postgis.gdal_enabled_drivers"

In sum, I can ask admin to change some settings once as in methods 1 and 2, but not every time e.g. when I do a full database restore (method 3).

My question is:

How can I make the above methods work?

FYI, my PostgreSQL version is 9.5.1, OS is Lubuntu 16.04 beta

    postgis_version
---------------------------------------
 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

In method 3, I can use postgres to ALTER DATABASE but I can't ask amin to do that every time I restore db, where I do something like:

dropdb my_db && createdb -T template my_db $$ psql < my_pg_dump_file

Edit the /etc/postgresql/9.x/main/environment file and set following environment variables POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL POSTGIS_ENABLE_OUTDB_RASTERS=1. Restart then the server.


Query JSON in Azure SQL DB to get one array element per row I have a stored procedure in a SQL Azure DB instance, and the input parameter is a JSON string formatted like this:

{
    "OrderNumber":["OD12034"],
    "Site":["New York"],
    "LineNumber":[1000,2000],
    "ItemNumber":["BX12345","BX12349"],
    "Quantity":[23,15]
}

I would like to parse this so that I can insert records into a table like this:

ArrayKey    | ArrayElement
------------|-----------
OrderNumber | OD12034
Site        | New York
LineNumber  | 1000
LineNumber  | 2000
ItemNumber  | BX12345
ItemNumber  | BX12349
Quantity    | 23
Quantity    | 15

So far I have been able to get the data out of the JSON, but not in the format I want. Here is one simplistic example.

SELECT * FROM OPENJSON(@JsonParameter)

key              |value                      |type
-----------------|---------------------------|----
OrderNumber      |["OD12034"]                |4
Site             |["New York"]               |4
LineNumber       |[1000,2000]                |4
ItemNumber       |["BX12345","BX12349"]      |4
Quantity         |[23,15]                    |4

And here is another (opposite of simplistic) example.

SELECT
       [ON].OrderNumber
     , [S].Site
     , [LN].LineNumber
     , [IN].ItemNumber
     , [Q].Quantity
FROM OPENJSON(@JsonParameter)
    WITH (
         [OrderNumber]      NVARCHAR(MAX)   '$.OrderNumber'     AS JSON
        ,[Site]             NVARCHAR(MAX)   '$.Site'            AS JSON
        ,[LineNumber]       NVARCHAR(MAX)   '$.LineNumber'      AS JSON
        ,[ItemNumber]       NVARCHAR(MAX)   '$.ItemNumber'      AS JSON
        ,[Quantity]         NVARCHAR(MAX)   '$.Quantity'        AS JSON
    ) AS jsonValues
CROSS APPLY OPENJSON(jsonValues.OrderNumber)
    WITH (OrderNumber NVARCHAR(25) '$') AS [ON]
CROSS APPLY OPENJSON(jsonValues.Site)
    WITH (Site NVARCHAR(25) '$') AS [S]
CROSS APPLY OPENJSON(jsonValues.LineNumber)
    WITH (LineNumber INT '$') AS [LN]
CROSS APPLY OPENJSON(jsonValues.ItemNumber)
    WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
CROSS APPLY OPENJSON(jsonValues.Quantity)
    WITH (Quantity INT '$') AS [Q]

OrderNumber  |Site        |LineNumber |ItemNumber    |Quantity
-------------|------------|-----------|--------------|-----------
OD12034      |New York    |1000       |BX12345       |23
OD12034      |New York    |1000       |BX12345       |15
OD12034      |New York    |1000       |BX12349       |23
OD12034      |New York    |1000       |BX12349       |15
OD12034      |New York    |2000       |BX12345       |23
OD12034      |New York    |2000       |BX12345       |15
OD12034      |New York    |2000       |BX12349       |23
OD12034      |New York    |2000       |BX12349       |15

Is there way to get one row per array element through the use of JSON tools in Azure SQL DB?

Well, I figured out an answer that gives me the result I want, but it looks bad and I assume it is inefficient. The Estimated Subtree Cost of the plan that is generated is 976507. Here is the query.

SELECT DISTINCT
    ArrayValues.ArrayKey
  , ArrayValues.ArrayElement
FROM
(
    SELECT
           [ON].OrderNumber
         , [S].Site
         , [LN].LineNumber
         , [IN].ItemNumber
         , [Q].Quantity
    FROM OPENJSON(@JsonParameter)
        WITH (
             [OrderNumber]      NVARCHAR(MAX)   '$.OrderNumber'     AS JSON
            ,[Site]             NVARCHAR(MAX)   '$.Site'            AS JSON
            ,[LineNumber]       NVARCHAR(MAX)   '$.LineNumber'      AS JSON
            ,[ItemNumber]       NVARCHAR(MAX)   '$.ItemNumber'      AS JSON
            ,[Quantity]         NVARCHAR(MAX)   '$.Quantity'        AS JSON
        ) AS jsonValues
    CROSS APPLY OPENJSON(jsonValues.OrderNumber)
        WITH (OrderNumber NVARCHAR(31) '$') AS [ON]
    CROSS APPLY OPENJSON(jsonValues.Site)
        WITH (Site NVARCHAR(31) '$') AS [S]
    CROSS APPLY OPENJSON(jsonValues.LineNumber)
        WITH (LineNumber NVARCHAR(31) '$') AS [LN]
    CROSS APPLY OPENJSON(jsonValues.ItemNumber)
        WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
    CROSS APPLY OPENJSON(jsonValues.Quantity)
        WITH (Quantity NVARCHAR(31) '$') AS [Q]
) ExpandedJson
UNPIVOT
(
    ArrayElement FOR ArrayKey IN (OrderNumber, Site, LineNumber, ItemNumber, Quantity)
) AS ArrayValues

And here is the result.

ArrayKey       |ArrayElement
---------------|-------------
Quantity       |23
ItemNumber     |BX12345
Site           |New York
LineNumber     |1000
LineNumber     |2000
ItemNumber     |BX12349
Quantity       |15
OrderNumber    |OD12034


"The attribute cannot be found when processing" error I'm facing following error while processing SSAS MDX cube:

Errors in the OLAP storage engine: The attribute key cannot be found when
processing: Table: 'FACT_TABLE_NAME', Column: 'COLUMN_NAME',
Value: 'VALUE'. The attribute is 'Attribute Name'.

Upon checking, I can see that this VALUE is indeed missing in FACT_TABLE_NAME but it exists in associated DIM_TABLE_NAME. However, this situation for me is perfectly fine and there's a chance that certain values won't exists between DIM and FACT (assuming that DIM can have more data).

Question is - is there anything I can do except processing data with using custom error configuration and ignoring "Key not found" errors?

Additional information

Some screens

I had to blur out value due to data security. Table that is being returned is the view behind WEEKLY MEASURES table.

Join between WEEKLY MEASURES and CUSTOMER happens on CustOrgHier_id both in DSV and in the Cube.

You should only get that error when a value exists in the fact table that doesn't exist in the dimension table, the error is "attribute key", only dimensions have attributes, facts don't, they have measures and dimension relations, maybe you have a dimension based on your fact table?

Having keys in your dimension that don't exist in the fact table is perfectly fine. Think of it this way: you can have a date dimension containing every date, but some days you haven't sold anything, so the date wouldn't appear in your sales fact while it exists in the dimension table. That's a perfectly reasonable scenario.

If you don't want to use a custom error configuration you will have to solve that in your source data (which would be the best solution anyway).
If you have an ETL process that would be the best point to solve your missing foreign keys but if you don't you can resort to named queries in the dmv where you UNION the missing records with the actual data.

SELECT 'missingkeyvalue' as key, 'explanation' as description
UNION
-- select actual data

The (unknown) dimension member doesn't really tell your users anything useful when they view it in a report, and they often forget to select them in filters.

Copied from my answer here:

If you have missing data in your foreign keys in your fact table (i.e. when your ETL process looks up the surrogate keys in your dimension table but the related row doesn't exist in your dimension) you need to decide based on your user requirements, but the general approach is to convert them to blank, 'unknown, invalid or whatever your requirements are.

If you have blank attribute values you should probably leave it as blank as to not confuse the users.

One thing to keep in mind specific to SSAS is that using error configurations in SSAS to let SSAS handle missing dimension keys has a huge impact on processing performance so you should probably solve that in your ETL process. The (unknown) dimension member is also very confusing for your end users as they don't know what it means when seen on a report and often forget to include it in filters (they have to select the unknown member on every dimension or they will be missing data).

As usual Kimball has a best practice on handling missing data in your dimensional model: Design Tip #128 Selecting Default Values for Nulls


Help to retrieve data set I have data set as below, for each Parent Record there can be multiple Cancel and Un-Cancels. I am trying to pull for each Parent, I am trying to find when it when it got cancelled and when it got un-cancelled (if it did); as in my second table below.

Also, another complexity is, for ParRecId A, it got un-cancelled and recancelled on same day 11-26 so it should stay Cancelled.

Same case ParRecId B, it got into Cancellation and then reactivation same day so it should stay Active.

Any help is greatly appreciated.

Given this dataset

+-------+----------+------------+------------+
| RecId | ParRecId |  Created   |   Status   |
+-------+----------+------------+------------+
|    17 | A        | 2015-11-26 | CANCEL     |
|    16 | A        | 2015-11-26 | INPROGRESS |
|    15 | B        | 2015-11-25 | Active     |
|    14 | B        | 2015-11-25 | CANCEL     |
|    13 | B        | 2015-11-25 | ON-HOLD    |
|    12 | E        | 2015-11-25 | CANCEL     |
|    11 | D        | 2015-11-25 | ACTIVE     |
|    10 | C        | 2015-10-19 | CANCEL     |
|     9 | C        | 2015-09-19 | ACTIVE     |
|     8 | C        | 2015-09-12 | CANCEL     |
|     7 | C        | 2015-09-05 | ACTIVE     |
|     6 | A        | 2015-09-01 | CANCEL     |
|     5 | B        | 2015-08-29 | INPROGRESS |
|     4 | C        | 2015-08-29 | INPROGRESS |
|     3 | B        | 2015-08-12 | CANCEL     |
|     2 | B        | 2015-08-03 | ACTIVE     |
|     1 | A        | 2015-06-01 | ACTIVE     |
+-------+----------+------------+------------+

The result should look like this

+----------+------------+------------+--------+
| ParRecId |   START    |    END     | STATUS |
+----------+------------+------------+--------+
| A        | 2015-09-01 |            | OPEN   |
| B        | 2015-08-12 | 2015-08-29 | CLOSED |
| C        | 2015-09-12 | 2015-09-19 | CLOSED |
| C        | 2015-10-19 |            | OPEN   |
| E        | 2015-11-25 |            | OPEN   |
+----------+------------+------------+--------+

Try the query below.

Query:

with ord as(
    SELECT ParRecId
        , m = ROW_NUMBER() over(partition by ParRecId, Created order by RecId)
        , r = DENSE_RANK() over(partition by ParRecId order by Created desc, RecId)
        , Created, [Status]
    FROM @data
)
SELECT o1.ParRecId
    , [START] = o1.Created
    , [END] = CASE WHEN o2.Status <> 'CANCEL' THEN o2.Created END
    , [STATUS] = CASE WHEN o2.Status IS NULL OR o2.Status = 'CANCEL' THEN 'OPEN' ELSE 'CLOSED' END
FROM ord o1
LEFT JOIN ord o2
    ON o2.ParRecId = o1.ParRecId
        AND o2.r = o1.r-1
WHERE o1.[Status] = 'CANCEL'
    AND (o1.Created <> o2.Created OR o2.Created IS NULL)
ORDER BY o1.ParRecId, o1.Created ASC

Your data:

declare @data table(RecId int, ParRecId char(1), Created date, [Status] varchar(10));
INSERT INTO @data(RecId, ParRecId, Created, [Status]) values
    (17,'A', '2015-11-26', 'CANCEL')
    , (16, 'A', '2015-11-26', 'INPROGRESS')
    , (15, 'B', '2015-11-25', 'Active')
    , (14, 'B', '2015-11-25', 'CANCEL')
    , (13, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'E', '2015-11-25', 'CANCEL')
    , (11, 'D', '2015-11-25', 'ACTIVE')
    , (10, 'C', '2015-10-19', 'CANCEL')
    , (9, 'C', '2015-09-19', 'ACTIVE')
    , (8, 'C', '2015-09-12', 'CANCEL')
    , (7, 'C', '2015-09-05', 'ACTIVE')
    , (6, 'A', '2015-09-01', 'CANCEL')
    , (5, 'B', '2015-08-29', 'INPROGRESS')
    , (4, 'C', '2015-08-29', 'INPROGRESS')
    , (3, 'B', '2015-08-12', 'CANCEL')
    , (2, 'B', '2015-08-03', 'ACTIVE')
    , (1, 'A', '2015-06-01', 'ACTIVE');

Output:

ParRecId    START       END         STATUS
A           2015-09-01  NULL        OPEN
B           2015-08-12  2015-08-29  CLOSED
C           2015-09-12  2015-09-19  CLOSED
C           2015-10-19  NULL        OPEN
E           2015-11-25  NULL        OPEN


AWS RDS : Setting Create and other grants only for single Database is not working We are using AWS RDS Mysql, and we are trying to give SELECT access to all databases i.e RO only, but for a single database, we want to give all permissions i.e CRUD. It's not working out unfortunately with Create, user is not able to create a table.

user data-team-user

Commands I tried :

GRANT SELECT ON *.* TO 'data-team-user'@'%';

GRANT ALL PRIVILEGES ON `api-gateway-copy`.* TO 'data-team-user';

GRANT CREATE, SELECT,INSERT,UPDATE,DELETE ON `api-gateway-copy` TO 'data-team-user'@'%';

GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON *.* TO 'data-team-user'@'%';
SHOW GRANTS FOR 'data-team-user';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, REFERENCES, TRIGGER ON `api-gateway-copy`.* TO 'data-team-user';
flush privileges;

GRANT CREATE ON `api-gateway-copy`.* TO 'data-team-user'@'%';

Still :

 SHOW GRANTS FOR 'data-team-user';
+--------------------------------------------------------------------------------------+
| Grants for data-team-user@%                                                          |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'data-team-user'@'%'                                           |
| GRANT SELECT ON `tmp`.* TO 'data-team-user'@'%'                                      |
| GRANT SELECT ON `sys`.* TO 'data-team-user'@'%'                                      |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                            |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                         |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                 |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                    |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                           |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                   |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                              |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `api-gateway-copy`.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'                                |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'

RDS only allows you to grant privileges on the schemas you create. System level schemas are blocked as the “master” user only has certain privileges with the with grant option.

That being said the usual statements of GRANT ALL PRIVILEGES and *.* don’t work. You have to grant privileges explicitly by schema to a user and those grants can only be at most the same ones you have as the “master” user.

This article is for creating another “master” user and you can use it as a reference to model the permissions you are trying to setup by removing the privileges you don’t want the user to have.

Additional Master User in AWS RDS


Is MySQL syntax GROUP BY ... ASC/DESC removed or not? I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was applied first time.

By this db-fiddle demo it still works well on any MySQL version (but on 5.7 and 8.0 sql_mode ONLY_FULL_GROUP_BY must be disabled or ANY_VALUE() must be used to avoid error ER_WRONG_FIELD_WITH_GROUP). I know that aim in this demo can be re-written using inner join and subquery but in my use case it is 5 times slower on table with 4.500.000 rows. Demo is simplified. My real use case is much similar to this fiddle.

MySQL doc says that results from GROUP BY, used that way I used, are non-deterministic and I should not rely on it. But in my case result are always identical on any MySQL version (can be tested be switching version in db-fiddle). There ore only two requirements: engine must be InnoDB and I have to create index for columns used in GROUP BY (both requirements are not problem - InnoDB and index is performance advantage). I'm using this solution because it is much faster then any other solution using inner join, subqueries etc.

However syntax is removed or not there is alternative which produces identical result. So GROUP BY col1 ASC/DESC can be rewriten as GROUP BY col1 ORDER BY col1 ASC/DESC. Which one is better to use in my case? Both are identical what about results and performance too.

Thanks for any advice.

From the "changelogs".  This was unusually fast from 'deprecation' to 'removal'!
----- 2018-07-27 8.0.12 General Availability & 2018-07-27 5.7.23 General Availability -- -- -----

Explicit ASC or DESC qualifiers for GROUP BY clauses are now deprecated and will be removed in a future MySQL version.

----- 2018-10-22 8.0.13 General Availability -- -- Incompatible Change -----

The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
Queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses should be amended. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.


Can a database restore fail after it has begun? I've noticed that a lot of times if a database restore is going to fail, it will fail before it even starts restoring in SQL Server 2008 Management Studio (displaying an error about space or something similar). I need to set up a schedule to restore a database overnight, but am not sure if I'll need to monitor it overnight or not. The hardware is stable enough that I believe this won't be a point of failure.

A better question might be: What checks are done against a backup prior to the restore process beginning?

Are you restoring to an existing instance of the DB? If so then space issues shouldn't be particularly relevant. At our place we run the following as job steps in restore jobs:

USE [master]
GO

ALTER DATABASE nameofDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DECLARE @Kill nvarchar(1024) = ''

SELECT @Kill = @Kill+'KILL '+CAST(spid AS varchar(4))+'; '
FROM sys.sysprocesses s
JOIN sys.databases d ON s.dbid = d.database_id
WHERE name = 'nameofDB'

EXEC(@Kill)
GO

ALTER DATABASE nameofDB SET MULTI_USER;
GO

This will kill all system processes (spids) against your DB. As someone else mentioned ensure there is an agreement with users that this will occur, or that other system processes require it etc.

Hopefully these steps will add some resiliance.

NB: Assumes you run the DB in multi_user !


Why doesn't SQL Optimizer use my constraint? I want to create a table with a NOT NULL bool column.
I use TINYINT with CHECK constraint BETWEEN 0 and 1.  The constraint is new and thus trusted

Now I would expect that SQL optimizer now knows that this column can only be 0 and 1 so when I write query col >= 2 I will see Constant Scan in actual execution plan (like when I would when check for NULL or SELECT TOP (0)

But this is not the case, it opts for the table Scan. Do I also need to have index on this column?

In my test below I use TINYINT with CHECK constraint. User Defined Type based on TINYINT with bound RULE and good old BIT.

GO
CREATE TYPE dbo.myBool
FROM [INT] NOT NULL
GO

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
go

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'
GO

DROP TABLE IF EXISTS dbo.RuleTest
CREATE TABLE dbo.RuleTest
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , oldSchoolBool TINYINT NOT NULL CHECK (oldSchoolBool BETWEEN 0 AND 1)
    , customBool dbo.myBool NOT NULL
    , myBit BIT NOT NULL
)

;WITH tally (n)
AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS a(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS b(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS c(n)

)
INSERT INTO dbo.RuleTest
(oldSchoolBool, customBool, myBit)
SELECT
    ABS(CHECKSUM(NewId())) % 2
    ,ABS(CHECKSUM(NewId())) % 2
    ,ABS(CHECKSUM(NewId())) % 2
FROM tally t

SET STATISTICS IO ON;

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool IS NULL

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2

go
SELECT * FROM dbo.RuleTest rt
WHERE rt.customBool >=2
go
SELECT * FROM dbo.RuleTest rt
WHERE rt.myBit >= 2

SET STATISTICS IO OFF;

I see one Constant Scan for the NULL check and 3 table scans for the rest.

Query 2

The issue is Auto parameterization.

In your case the constant 2 gets replaced with a tinyint parameter @1 rather than the literal 2 - as this parameter could have the value 0 or 1 it wouldn't be valid for the query optimiser to assume the check constraint contradicts this.

You can use the following query to get a plan that does use the contradiction detection (the 1=1 prevents the Auto parameterization) The contradiction detection then happens as part of simplification (see the Optimization Pipeline diagram here).

SELECT *
FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2 and 1=1

the resulting plan is simplified to a constant scan

Query 3

Rules have been discouraged/deprecated for about 20 years. 2000 BOL describes them as

  a backward-compatibility feature ... CHECK constraints are the
  preferred, standard way

The CREATE RULE topic states

  Rules do not apply to data already existing in the database at the
  time the rules are created

So I imagine these are never trusted by the query optimiser as it is possible to do the following and have data that does not adhere to the rule

CREATE TYPE dbo.myBool FROM [INT] NOT NULL

GO

CREATE TABLE dbo.RuleTest
(
  customBool dbo.myBool NOT NULL
)

INSERT INTO  dbo.RuleTest VALUES (10)

go

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
GO

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'

Whilst technically it might be possible to maintain a trusted rule concept analogous to trusted constraints I don't believe this exists.

Query 4

You need to add a redundant check constraint on CHECK (myBit BETWEEN 0 AND 1) or equivalent if you want it to do this contradiction detection. Even though a not nullable bit can only hold those two values you don't get this contradiction detected without that


Querying sys.master_files over a linked server Querying sys.master_files through a linked server, I get no results.

Why? How do I work around this issue?

This approach works fine for me:

SELECT * FROM [linked_server_name].master.sys.master_files;

What you need to be sure of is that the linked server is running in the context of a user who has enough privileges to see the contents of the DMV. You can check who you're executing as using:

EXEC [linked server name].master.sys.sp_executesql N'SELECT SUSER_SNAME(),
   ORIGINAL_LOGIN();';


INSERT... ON DUPLICATE KEY UPDATE not working as I expect I have a table called "Example"

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

I want to insert values if not exists and if the value exists then update, so I am using following statement:

INSERT INTO example (a, b, c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

After the above queries executed the table look like this:

Again I execute the above statement, the result looks like this:

What is wrong with my statement?

Well this is the insert bit that you are using:

  INSERT INTO example (a, b, c) VALUES (1,2,3) ....

here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c.

The row in this case is updated when you provide a primary key (that you want to check is duplicate or not), along with the rest of the row data.
If you need to check and update according to record id, you have to provide the KEY as well, which in your case is id.

Try something like this:

INSERT INTO example (id, a, b, c) VALUES (1,1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

Now if, the id is duplicate, the row will update.


Quickly establishing if a remote MySQL connection is valid using PHP I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works great until the remote DB goes down (Which it does now and again - cannot be resolved), at which point my website also fails with a PHP MySQL DB connect error.

To resolve this problem, I intend to create a copy of the remote DB locally on the hosted website, and synchronise this copy every hour. And then, when the website is reverting to the local copy, a message will appear on the screen informing the user that they are seeing cached data.

So far so good, except that I'm trying to get the website to test the remote DB first and if that fails, then connect to the local copy, but I can't figure out how to do a simple and quick test to establish this; my code just bombs out on a MySQL connect error after a page time-out when trying the remote DB first.

Of course, creating a replicated slave DB updated instantly when the remote DB is updated, and then pointing the website always and only at the slave would be ideal, but there are technical problems preventing this right now.

Any ideas on how to test a remote MySQL DB connection quickly so that a fallback local (copy) DB can be connected without much disruption the the user experience?

Well, on the mysqli constructor page for the php manual you have several examples on how to deal with connection errors, depending on the PHP version. For example, on the first code snippet, change the die() to

if ($mysqli->connect_error) {
    //try connecting to the backup node
    $mysqli = new mysqli('backup_node_host', 'my_user', 'my_password', 'my_db');
    ...
}

However, doing everything programmatically has its disadvantages, and as you are on DBA.stackexchange, and not on stackoverflow, I could suggest an alternative setup:

 read-only      read/write
application     application
     |              |
     |              |
 HA Proxy           |
     |\             |
     | \            |
     |  \           |
     |   \          |
     |    \         |
     |     \        |
     |      \       |
     |       \      |
     |        \     |
     |         \    |
     |          \   |
     |           \  |
 read-only      read/write
MySQL slave    MySQL Master
     ^              |
     |              |
     ----------------
    MySQL asynchronous
       replication

Obviously, this may cost more than a couple of shared hosting but avoids manual intervention (imports, failover management) and there is better management and control or certain type of errors (network timouts, etc.).