How to create a mysql stored procedure through linux terminal In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the  command   delimiter for only the mysql client(client side command).

but  I am using the mysql client which does not have the command delimiter
like dbslayer, on this kind of clients how can I define the procedures.

for now consider:

create procedure test_pro()
begin
select 'hello pro';
end

I tried the following :

mysql -u root -pmypass  test < proc_file

where  proc_file contains the above procedure;

but that is giving me the following error:

ERROR 1064 (42000) at line 1: 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 '' at line 3

Event I tried the following

 create procedure test_pro()
    begin
    select 'hello pro';
    end;

(added the trailing semicolon)
but am getting the same error .

Same I am experiencing with the dbslayer , if I am able to define the above procedure through terminal I think I should be able to do that through the dbslayer

There is no way around it. You must use the DELIMITER command. Why ?

If you ever perform a mysqldump of the stored procedures, each stored procedure begins with

DELIMITER ;;

and ends with

DELIMITER ;

Here is a post where I mentioned this before : DROP PROCEDURE IF EXISTS not included in mysqldump

Try dumping one stored procedure with mysqldump and see for yourself

I also wrote some code to do this:

how to dump a single stored procedure from a database
Dump only the Stored Procedures in MySQL

As for the answer posted by @altmannmarcelo, it directly answers your question (+1 for his answer). Otherwise, mysqldumps could never restore stored procedures.

There are two things you can do to accommodate a new DELIMITER:

TRY THIS #1

Give the delimiter on the command line itself

mysql -u root -pmypass --delimiter="//" test < myproc.sql

It is a command line option for the mysql client program

[root@****]# mysql --help | grep -i delimiter
  --delimiter=name    Delimiter to be used.
delimiter                         ;

TRY THIS #2

You can write the code into a text file and execute against the text file as @altmannmarcelo suggested

echo "DELIMITER //" > myproc.sql
echo "create procedure test_pro()" >> myproc.sql
echo "begin" >> myproc.sql
echo "select 'hello pro';" >> myproc.sql
echo "end" >> myproc.sql
echo "//" >> myproc.sql
mysql -u root -pmypass  test < myproc.sql

GIVE IT A TRY !!!


SQL statement to hide all system databases in Server from a server login I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model.

I know that we can control view definition in database level e.g. database user, database role but I don't want to configure them to all system databases.

Therefore, I started by:

Create Windows authentication login
Create custom server role
Deny view definition to a custom server role
Add an login to a role

I hope the login shouldn't see all system databases but it still can see master and tempdb under System Databases node.

This is the screenshot of the result.

and these are my SQL statements.

-- Create login from Windows User
USE MASTER
GO

CREATE LOGIN [domain\web_user] FROM WINDOWS WITH
DEFAULT_DATABASE = MyTestDB
GO

USE MyTestDB
GO

CREATE USER web_user FOR LOGIN [domain\web_user]
WITH DEFAULT_SCHEMA = dbo
GO

EXEC sp_addrolemember @rolename = 'DB_DataReader',
@membername = 'web_user'
GO

--Create custom server role, deny view definition and add login to role
USE master
GO

CREATE SERVER ROLE junior
GO

DENY VIEW ANY DEFINITION TO [junior]
GO

ALTER SERVER ROLE junior ADD MEMBER [domain\web_user]
GO

From this post hide system databases sql server management studio.

I think it solves my problem.
However, for normal login and user, we can't hide master and tempdb database.

The following information are in this document.
hide system databases sql server management studio

  After this permission is denied, a login can see only metadata for
  master, tempdb, and databases that the login owns.


usage percentage of Mongodb internal cache I've ran two performance test on a MongoDB server with identical environment/settings AFAIK.
I found that the throughput was 10% apart.

When I inspected the mongostat logs, I found that Mongodb in the first test with faster throughput used 95% of its cache_size. In the second test, it stayed at 80%.

I rebooted the server system between the runs, but didn't change any of the settings of the server or the client. mongodb log reports the exact same setting in both tests:

First test:
2019-08-14T00:42:32.588-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=96122M,cache_overflow=(file_max=0M),session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),statistics_log=(wait=0),verbose=(recovery_progress),

Second test (after reboot):
2019-08-14T01:11:15.722-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=96122M,cache_overflow=(file_max=0M),session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),statistics_log=(wait=0),verbose=(recovery_progress),

Here is a sample from mongostat of the first test:

insert  query update delete getmore command dirty  used flushes vsize   res   qrw   arw net_in net_out conn                time
    *0 104209     *0     *0       0     9|0  0.0% 94.9%       0 96.7G 94.7G 107|0 128|0  27.0m   40.2m  272 Aug 14 00:51:13.759
    *0 102610     *0     *0       0     0|0  0.0% 94.9%       1 96.7G 94.6G 109|0 128|0  26.6m   39.6m  272 Aug 14 00:51:44.092
    *0 104724     *0     *0       0     9|0  0.0% 94.9%       0 96.7G 94.6G  87|0 128|0  27.1m   40.4m  272 Aug 14 00:52:13.767

And a sample from the second test:

insert  query update delete getmore command dirty  used flushes vsize   res   qrw   arw net_in net_out conn                time
    *0  94009     *0     *0       0     9|0  0.0% 80.9%       1 83.1G 80.9G 127|0  81|0  24.4m   36.3m  272 Aug 14 01:17:29.289
    *0  93680     *0     *0       0     0|0  0.0% 80.3%       0 83.1G 81.0G   0|0 107|0  24.3m   36.2m  272 Aug 14 01:17:59.119
    *0  93068     *0     *0       0     9|0  0.0% 80.9%       1 83.1G 81.0G 130|0 128|0  24.1m   35.9m  272 Aug 14 01:18:29.123
    *0  92599     *0     *0       0     0|0  0.0% 80.0%       0 83.1G 80.9G 137|0 128|0  24.0m   35.8m  272 Aug 14 01:18:59.186

What could have caused the difference in the cache percentage usage and throughput?
is it safe to change explicitly this settings so Mongodb uses more of its cache?
Can I do this setting change from the command line ? (I'm starting mongodb from command line and not as a service)

What could have caused the difference in the cache percentage usage and throughput?
MongoDB uses both file system as well as WiredTiger's cache when required
Is it safe to change explicitly this settings so Mongodb uses more of its cache?
Yes. It is totally safe to change the cache settings
Can I do this setting change from the command line ?
The setting has be done in your mongod config file under storage option

Refer the below link on how you should decide on the cache size
https://docs.mongodb.com/manual/faq/storage/

Similar to the below:

storage:
  engine: wiredTiger
  wiredTiger:
    engineConfig:
      cacheSizeGB: 7

Thank you!

-Anban


What happen with the Log backup if I set the SQL Server clock back? What happen with the Log backups if I set the SQL Server clock back? In countries with daylight saving this happens at least once a year. Will the point in time restore with the STOPAT option fail?

Using fn_dump_dblog I see this after setting the clock back two hours.

A point-in-time restore to an a timestamp that appears twice in the log file will either fail, or arbitrarily select one occurrence of the target time.  I don't know which, and it doesn't really matter. You can restore to LSN instead of a timestamp using STOPMARK instead of STOPAT.


How can I disallow restore from backup on SQL Server? I want to disallow restore from backup. How can I do this? One Of my friends said to me

  "Take a certificate for your database and anyone cant restore from your backup"

I searched about certificate but I didn't find anything. I want to be sure that nobody copy my database from backup or the other ways.

Is there anyway about this problem?

Quick answer: You can't.

Physical access to the backup file will allow someone to restore it. The password feature isn't secure and will be removed anyway. See "Security Considerations for Backup and Restore" on MSDN

To prevent someone running RESTORE and destroying a working database, then ensure that only the correct folk have permissions to do this


Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7 I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)

CREATE TABLE IF NOT EXISTS `docs` (
  `field1` int(6) unsigned NOT NULL,
  `field2` int(6) unsigned NOT NULL,
  `field3` int(6) NOT NULL,
  `field4` int(6) NOT NULL,
  PRIMARY KEY (`field1`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES
  ('27908', '82', '1', '17'),
  ('27907', '82', '1', '50'),
  ('402', '25', '1', '90'),
  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query.  I'm just breaking apart my original query:

SELECT * FROM
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
)

Result (formmated with table generator):

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|    312 |     25 |     10 |      8 |
|  27908 |     82 |      1 |     17 |
|  27907 |     82 |      1 |     50 |
+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
) temp
GROUP BY field2

Result:

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|  27908 |     82 |      1 |     17 |
+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?

You are correct, this was different in earlier versions

  ...However, in MySQL prior to version 5.7 the default behaviour has been
  more forgiving because it will not complain and then arbitrarily
  choose any value! It also has an ANY_VALUE() function that could be
  used as another solution to this question if you really needed the
  same behaviour as before...

Source

But this change is a good thing, as the error used to be silenced.
I would recommend changing the query to get the results you want. I believe you want the one where field1 is the largest.

Field1 is the primary key, so you could possibly do something like the following

SELECT docs.field1,docs.field2,docs.field3,docs.field4
FROM
(
SELECT max(field1) as field1 ,field2 FROM docs
GROUP BY field2
) temp
INNER JOIN docs
on temp.field1 = docs.field1
ORDER BY field2, field1 desc;

DBFiddle

The other way around this are to either disable the functionality or use ANY_VALUE() but I would advise against that.

ANY_VALUE() can give an inconsistent resultset, an example could be the number 50 instead of the expected 17 as field4 where the field1 column = 27908.

DBFiddle


Drop a 500 GB table, any consequences? I need to drop a table that is 500 GB in size. It contains historical data that's not needed or used. There are no FK constraints. Any consequences of just dropping the table? I'm also using DB Mirroring. Will there be blocking or some other things that I should be concerned?

Microsoft official statement:

  Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

From MS:

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Where possible I'd run it in a dev environment first and run sp_whoisactive @get_locks=1. This will give you some reassurance over what's going to happen in terms of locking/blocking. Appreciate this may not be possible with a massive DB.

Regarding the mirror, TRUNCATE is still a logged operation so the table will also be truncated on the mirror. This may help https://www.sqlservercentral.com/forums/topic/truncate-table-on-mirrored-db


Postgres role does not inherit permissions from its member role I want to group my postgres database permissions to set of roles and then assign users to these parent roles to manage all permissions from a central place. I created a role called admins and granted permission as below (had to use EXECUTE command since my statement is dynamic. role_name is 'admins' and schem is 'core').
    EXECUTE format('CREATE ROLE %I WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION', role_name);

    EXECUTE format('GRANT ALL ON ALL SEQUENCES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I WITH GRANT OPTION', schem, role_name);

    EXECUTE format('GRANT ALL ON ALL FUNCTIONS IN SCHEMA %I TO %I WITH GRANT OPTION', schem, role_name);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON FUNCTIONS TO %I WITH GRANT OPTION', schem, role_name);

After creating the role, I created a user call 'kasun' and assign to the above created 'admin' role.
CREATE USER kasun WITH PASSWORD 'password' INHERIT;
GRANT admins to kasun

I could login to the database from user 'kasun', but still I can't run even a select statement. (this schema and tables are already there created by application using a high privileged user)
ERROR:  permission denied for schema core
LINE 1: select * from core.partners;

When I check the user settings it shows user 'kasun' has been created correctly and it is a member of role 'admins'. When I check the permission using following two queries, I could see 'admins' role has all the permission I granted, but role 'kasun' doesn't have any record.
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'admins';
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'kasun';

I can't think of anything I did wrong. Next option is to grant the permissions to each individual user. I don't like to go on that path when there is a clean way of doing this. I hope someone could shed some light to see what is going on here.
(I am running PostgreSQL 11.6 on Amazon RDS)
Thanks in advance!
Kasun

Thanks to one of my colleague, I could resolve this issue. You have to first grant permission to the schema. EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schem, role_name);.
Until then things won't work. Also, there was a typo in my passing parameter which I found after I passed this step.
I was debugging the issue in another direction since I was referring the admin user created by RDS when database getting created. That user has all the grant records in role_table_grants table. That is why I thought this is an inheritance issue. Good learning BTW :)


How to count the amount of values grouped by some column? I need to create a view based on data from another table and grouped by some column value. Here is a source table:

CREATE TABLE source
AS
  SELECT *
  FROM ( VALUES
    ( 6, 1000 ),
    ( 6, 2000 ),
    ( 7, 5000 ),
    ( 7, 6000 )
  ) AS t(client_id, score);

and the desired view is:

 client_id | score
----+---------+---
 6         | 3000
 7         | 11000

As far as I know, it's convenient to use window functions in this case. But what way will be most effective for grouping by client_id field?

Thank you.

You really don't need a view for something this simple. But this is what it would look like

CREATE VIEW desired_view
AS
  SELECT client_id, sum(score)
  FROM source
  GROUP BY client_id;

Output

 client_id |  sum
-----------+-------
         6 |  3000
         7 | 11000
(2 rows)


Why is Query Store missing details? This is slightly complex to explain, but very easy to reproduce.

Short version:

For about 10%-30% queries (by count) significant amounts of Query Store data is not available. I have looked at SQL 2016 and SQL 2017 where Query store had been running for weeks+, where there was activity on the database(s).

The query below will return two sets of data, the top set does not have a value for query_store_query.last_execution_time, while the bottom set does have data in the field.  The 'have nots' are also missing most the run time stats data.

EXEC sp_query_store_flush_db;  --Same results without this, but just to rule it out in the examples.
go
Select * from sys.query_store_query
where query_store_query.last_execution_time is null -- there are bunch of these, also missing other data, why?

Select * from sys.query_store_query
where query_store_query.last_execution_time is not null

Problem Solving:

I Initially discovered this using exported Query Store data My data is in an Excel sheet, I sorted and compared and did not find any common factors separating the 'haves' from the 'have nots'
To rule out data export issues, I used the code above to get data directly from the system dataview.  Similar results to findings in exported data.
To rule out the system dataview, I used "Tracked Queries" to report from the root data. (Query Store > Tracked queries > Configure)

3.A. For the 'haves', the query plan(s) and such are displayed, just as you would expect

3.B. For the 'have nots', there is no query plan and no metrics
The only scope limiting factor I have found is that on very active databases, the 'have nots' are limited to the last few hours.  But on slow databases, the 'have nots' can have initial_compile_start_time dating back several weeks.  (I suspect the 'have nots' are being purged then recreated the next time the now "Never seen before, new" query is run)
The 'have nots' can have any range of compile counts, number of plans, etc.

Question

What do the 'have nots' have in common? Why are they missing data when others are not?

Why this matters: If as I currently suspect, the run time data is not being captured correctly on the 'have nots' some/all of the most resource intensive queries may not be reported in query store reports.

Update: Query Store Capture Mode All does not eliminate this issue.

Using the suggested setting by Erin Stellato I have Query Store Capture Mode set to Auto, as pointed out in a comment and answer this could be the cause. But after testing (purged the QS on one of my database and set it to ALL), I am still seeing some 'have nots' after the database has run for a while

I see this on my SQL Server 2016 instance as well (same settings as yours: 15 min data flush, and 1 hour stats collection).  I noticed that the plans with missing information correlated with AG failovers and maintenance-related reboots (which I found by looking in the SQL Server error log).
If you are following the best practices related to "mission critical servers" like I am, you may have taken these steps:

Use trace flags on mission critical servers
The global trace flags 7745 and 7752 can be used to improve availability of databases using Query Store. For more information, refer to Trace Flags.

Trace flag 7745 will prevent the default behavior where Query Store writes data to disk before SQL Server can be shut down. This means that Query Store data that has been collected but not been yet persisted to disk will be lost.

Trace flag 7752 enables asynchronous load of Query Store. This allows a database to become online and queries to be executed before the Query Store has been fully recovered. The default behavior is to do synchoronous load of Query Store. The default behavior prevents queries from executing before the Query Store has been recovered but also prevents any queries from being missed in the data collection.

This explains all of the missing runtime stats in my setup.  While it's not explicitly called out in this article, it appears that the queries and plans might get into the query store without aggregated runtime stats.  This might vary based on your "Data Flush Interval" and "Statistics Collection Interval."


Storing Phone Numbers - Proper design First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career.  One of the things I remember (maybe incorrectly) is that when designing the ERD that you take the real world into account as it serves as the context for your design.

I have a situation where we have the concept of a customer, who can have two and only two phone numbers.  A customer can have zero to many addresses as well, and each address can have only two phone numbers (and a flag indicating if the number is a mobile number).  The numbers on an address would only be used to contact someone at that particular address, while the numbers for the customer are used to contact the customer (who's address may not be in the system).

The design I came up with was to have a Phone1 Phone2 as columns on the customer table as well as on the address table.  I've had others suggest that this isn't a good design and that I should have created a PhoneNumber table instead (I'm not sure how they were suggesting I relate the numbers to the other records).

Certainly I can see that being valid as well, but I either still need to have Phone1Id and Phone2Id in the customer and address table, or have something in the Phone table that tells me which record owns the number.  The issue I have though is that of course makes the appliction logic more complex since I now need to add, remove or update the record in the phone table instead of just blanking or nulling the value in the respective tables.

Is my design also acceptable?  Is one or the other preferable?  Or are both just as valid?

The anti-null brigade would insist you normalize further to create the suggested PhoneNumber table. The practical folk would consider your original design perfectly acceptable. I'm in the latter camp.

Normalize until it hurts, de-normalize until it works.


Logging in to phppgadmin remotely Ubuntu 14.04 Amazon EC2 I created an Ubuntu server instance on Amazon EC2 and installed php, postgreSQL, phppgadmin, and any other required features.

After SSH'ing into the server using command prompt, I executed the following operations:

'sudo apt-get update'
'sudo apt-get install apache2 libapache2-mod-php5 php5'
'sudo apt-get install postgresql postgresql-contrib'
'sudo apt-get update'
'sudo apt-get install phppgadmin'

'sudo nano /etc/apache2/conf.d/phppgadmin'
- uncomment line 'allow from all', save and exit

'sudo nano /etc/apache2/sites-enabled/000-default'
add the following text:

<Directory "/usr/share/phppgadmin">
        AuthUserFile /etc/phppgadmin/.htpasswd
        AuthName "Restricted Area"
        AuthType Basic
        require valid-user
</Directory>

- save and exit

'sudo apt-get install apache2-utils'

'sudo service apache2 restart'

'sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf'
'sudo service apache2 restart

The result:
http://ec2-54-174-161-124.compute-1.amazonaws.com/phppgadmin/

My issue now is that I don't know what to do so that I can access phppgadmin and start creating a database.  A tutorial explained how to setup a password to access the page that was linked to above, but this did not work because as you can see, the page is accessible without a password.

My questions:
1) It is an issue (in terms of security) that there is no password to access the page that I linked to? If so, how can I fix this?
2) How can I gain access to phppgadmin after I click on the 'PostgreSQL' link in the sidebar that shows a container with a red X on it? I have searched and searched and I can't figure out how to setup a user or access it as an administrator/root/postgres user

So I received help on this issue from a colleague who is an experienced programmer.

In order to add a password to the page:

sudo nano /etc/apache2/conf-enabled/phppgadmin.conf

Then, modify the .conf file to include the following text within the  block (I placed it underneath the 'allow from all' line that I uncommented previously):

AuthUserFile /usr/share/phppgadmin/.htpasswd
AuthName "Restricted Area"
AuthType Basic
require valid-user

Save this file and exit.

Change directory to phppgadmin:

cd /usr/share/phppgadmin

Create password file (change 'your username' and get rid of triangle brackets):

sudo htpasswd -c .htpasswd <your username>

Enter password twice when prompted.

Restart the apache service and the page will now be password protected.

In order to add a superuser to access phppgadmin(again, (change 'your username' and get rid of triangle brackets):

sudo su - postgres
createuser -P -s <your username>

Again, enter password twice when prompted. This will now allow you to login to to phppgadmin.

Erwin's answer is also helpful too, it just seems as if he takes a different approach to solving the issue.  Although I did not use Erwin's method, it should accomplish the same thing.  However, I did not need to adjust listen_address in postgresql.conf in order to make it work.


Improving the performance of STIntersects Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index:

CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN]
(
[Coord]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

T_POLYGON has:

CREATE SPATIAL INDEX [T_POLYGON_COORD] ON [dbo].[T_POLYGON]
(
[COORD]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 128, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY];

A query to find the intersection of T_PIN and T_POLYGON takes more than 45 minutes to execute:

SELECT COUNT(*)
FROM T_PIN
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1;

The result is 4,438,318 rows.

How can I accelerate this query?

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item.

Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first.  Matches against these simplified polygons could then be run through the primary filter to get the final results.

1) Add a new geography and geometry column to the [dbo].[T_POLYGON] table:

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeom geometry;
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog geography;

2) Create the bounding box polygons (this involves an initial conversion to geometry to take advantage of STEnvelope()):

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeom = geometry::STGeomFromWKB(
    COORD.STAsBinary(), COORD.STSrid).STEnvelope();

UPDATE [dbo].[T_POLYGON] SET SimplePolysGeog = geography::STGeomFromWKB(
    SimplePolysGeom.STAsBinary(), SimplePolysGeom.STSrid);

3) Create a spatial index on the simplified geography column

4) Get the intersections against this simplified geography column, then filter again on the matching geography data types. Roughly, something like this:

;WITH cte AS
(
   SELECT pinID, polygonID FROM T_PIN INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.SimplePolysGeog ) = 1
)
SELECT COUNT(*)
FROM T_PIN
INNER JOIN T_POLYGON
    ON T_PIN.Coord.STIntersects(T_POLYGON.COORD) = 1
    AND T_PIN.pinID IN (SELECT pinID FROM cte)
    AND T_POLYGON.polygonID IN (SELECT polygonID FROM cte)

EDIT: you can replace (1) and (2) with this computed, persisted column. credit to Paul White for the suggestion.

ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS  ([geography]::STGeomFromWKB([geometry]::STGeomFromWKB([COORD].[STAsBinary](),[COORD].[STSrid]).STEnvelope().STAsBinary(),(4326))) PERSISTED


MDX calculated member to band a measure I am trying to add a calculated member to an MDX query which will create a set of "Age Bands" for people in my database. I have a measure which contains the age of each person.

I have started with this MDX

SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

I have another measure ([Measures].[Age]) which I would like to band up and return with the rows, so eg anyone aged 0-25, 26-50 etc

Here is what Ive tried

WITH SET [Age Band]
    AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))
SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes],
        [Age Band]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

However the error I get is

  The  function expects a tuple set expression for the  argument. A string or numeric expression was used.

What am I doing wrong? Is there an easier/better way to do this without chaining IIF commands?

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.


Why storing product fees in another table is preferred? MySql:
What is the benefit of storing several types of fees in another table like

Product Table -> (productid...),
ProductFee Table -> (productid, feetypeid, feeamount),
ProductFeeType -> (feetypeid, feetitle)

over

Product Table -> (productid, feeXamount, feeYamount, feeZamount) ?

The former would require too many joins and when creating/updating the prouct page, it would require too many inserts/update. What would be the benefit of such structure? The former seems to be preferred approach. Obviously, if the number of types of fee amounts start to go crazy like 40-50, it's best to follow the latter approach. However, if it's only 5-10 such columns, database-size is trivial, and especially when you're not writing a generic CMS like WordPress but a custom-tailored CMS for a specific shop?

Granted: The fees are optional but that's what null's are for, right?

If the range of fees is guaranteed to be fix - as well as the fees themselves, you might be fine with having them as columns in your product table rather than as rows in additional tables. Otherwise, you will need to adjust the schema and your code in order to add a new type of fee.
Once, you have separate tables, maintaining, e.g., periods, a particular fee value is valid for, is simple (even though, it might require one more table and one more join each). Just imagine trying to do this using columns.
As always in (database) design: You need to balance (application) requirements, efficiency/effectiveness of your solution, flexibility down the road, etc. based on your domain('s characteristics as valid over the lifetime of your system).

In general, this is an example of data (de)normalization…


Add AG Database Add process results in Connectivity Fail Recently I was asked to add a new DB to existing AG group.
Will adding a new AG database create any outage on Application . Especially ongoing In progress App side Sync processes with Server. Any Connection string Outages expexted ?

Sql Server 2014 SQL Server .

Adding databases to an AG group does not disrupt interactions with the database

If you're adding a new database to an existing AG group then there will be no loss in service to SQL Server whatsoever--no severed connections. SQL Server does not need exclusive rights to the database in order to add the database to an AlwaysOn replica. The majority of what's going on behind the scenes on the source database is a database backup and log backup.

There will, however, be an I/O and CPU performance hit for backing up and copying the database and logs to the SQL replica(s). The bigger the database, the bigger the I/O impact. This may affect the performance of your connected application(s) if the database is large.

Note:
Although you may not experience disconnects to the database(s) you are adding to AlwaysOn, you are not using the listener for that AG group either. After you add database(s) to an AG group you will probably want to change the application connection strings to point to the AG listener rather than the SQL Server directly. Doing so will most likely disrupt service.


Mapping similar products in single product in price comparison website I am making a price comparing website. I have data from different stores, but I could not find any way to map similar products to single product.

I have a table 'product1' like this .

  id       title                                               brandName
|  8986 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  8983 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6048 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  6109 | Samsung Galaxy Ace                                 | Samsung   |       1 |
|  9840 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  8905 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|  9097 | Samsung Galaxy Ace Duos                            | Samsung   |       1 |
|   765 | Samsung galaxy Ace Duos S6802 ( Black)             | Samsung   |       2 |
|   766 | Samsung Galaxy Ace Duos S6802 (Chic White)         | Samsung   |       2 |
| 11880 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
| 11218 | Samsung Galaxy Ace NXT                             | Samsung   |       1 |
|   323 | Samsung Galaxy Ace Nxt G313H Black                 | Samsung   |       2 |
|   311 | Samsung Galaxy Ace Nxt G313H White                 | Samsung   |       2 |
|  9099 | Samsung Galaxy Ace Plus                            | Samsung   |       1 |
|  1153 | Samsung Galaxy Ace Plus S7500 Dark Blue            | Samsung   |       2 |

Now I want to map similar products; for example I need results like

title                                  brandIDs
Samsung Galaxy Ace             8986 , 8983 , 6048 , 6109
Samsung Galaxy Ace Duos        9840 , 8905 ,9097 ,  765 , 766
Samsung Galaxy Ace NXT         11880 , 11218 , 323 , 311
Samsung Galaxy Ace Plus        9099 , 1153

Is there any programming logic or mysql which can give me desired result?
I tried different mysql clause i.e LIKE'%%' but it returns me whole data.

Table schema :

CREATE TABLEproduct1(
idint(11) NOT NULL,
titlevarchar(50) NOT NULL,
brandNamevarchar(50) NOT NULL,
  PRIMARY KEY  (id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--

INSERT INTO `product1` VALUES (8986, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (8983, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6048, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (6109, 'Samsung Galaxy Ace', 'Samsung');
INSERT INTO `product1` VALUES (9840, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (8905, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (9097, 'Samsung Galaxy Ace Duos', 'Samsung');
INSERT INTO `product1` VALUES (765, 'Samsung galaxy Ace Duos S6802 ( Black)', 'Samsung');
INSERT INTO `product1` VALUES (766, 'Samsung Galaxy Ace Duos S6802 (Chic White)', 'Samsung');
INSERT INTO `product1` VALUES (11880, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (11218, 'Samsung Galaxy Ace NXT', 'Samsung');
INSERT INTO `product1` VALUES (323, 'Samsung Galaxy Ace Nxt G313H Black', 'Samsung');
INSERT INTO `product1` VALUES (311, 'Samsung Galaxy Ace Nxt G313H White', 'Samsung');
INSERT INTO `product1` VALUES (9099, 'Samsung Galaxy Ace Plus', 'Samsung');
INSERT INTO `product1` VALUES (1153, 'Samsung Galaxy Ace Plus S7500 Dark Blue', 'Samsung');

Can any body please tell me how can i achieve desired result in mysql.

The basics you can do with a simple GROUP BY:

select
 title,
 group_concat(id) as brandIDs
from product1
group by title;

http://sqlfiddle.com/#!9/87532/1

But it will group only products with exactly the same title, no way to simply say in SQL that Samsung Galaxy Ace Nxt G313H Black and Samsung Galaxy Ace Nxt G313H White are the same.

You should normalize your table. You can use a separate table of brands which will save space from the multiplication of the names. And you can add some "hierarchy" of brand->series->product like Samsung->Galaxy Ace Nxt->G313H Black to make these queries easier.

Edit:
I tried to get exactly the results you show with MySQL string functions:

select
 substring_index(title,' ',4),
 group_concat(id) as brandIDs
from product1
group by substring_index(title,' ',4);

http://sqlfiddle.com/#!9/87532/6


Is there an upper limit to the SqlDbType.Structured ADO.NET type? Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc?

I can't find anything to suggest there is so I'm assuming that there'd just be a time-out if a giant data set was sent.

I'm looking at 2000-ish rows, so nothing frightening.  I just want to preempt any potential explosions.

There is no inherent limit to the size of a table-valued parameter.  However, you could run into client memory constraints if you pass the value without streaming, such as using a DataTable or IEnumerable<SqlDataRecord> (without a stream source implementation) instead of a DataReader.

On the server side, the limit is tempdb storage.

2K rows won't be a problem as I've passed millions of rows without issues. One consideration, though, is to be sure to specify the MaxLength for string columns with a DataTable value. See my blog post SQL Server TVP Performance Gotchas.


MSSQL Hashbytes produces different output, string vs column value The following SQL

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'

produces the following hash:

EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF

If I then replace 'test' with the column value from [Key] instead like this:

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2) as 'From Column' FROM MyTable
WHERE [Key] = 'test'

The following hash is produced:

9F7D8627E02F97CC5A52DCB2BA96038FE12F2A34B0FAC50E041359AE13D5EDE8A8A50562DA58BA7916DA378E7343EF91E85EFBD6A0A70AB237ADA4C2274DF13D

Right now we have a couple of rows in our database that I would like to hash, so I want to run the following code:

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2)

But the problem is that the hash produced is not correct, it's only correct if I replace [Key] with the actual string value.

I found this question: Using HASHBYTES() yields different results for nvarchar and a variable and there they "solve it" by prefixing the string with N. But I want to do it the other way around, I want the output to be
EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF.

How can I achieve that?

My table looks like this:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [nvarchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The reason your output is different between a literal string (i.e. writing 'test') or using a column value. In your case [key].

Is because [Key] column is a NVARCHAR column. Whereas 'test' is a VARCHAR string. Typing N'Test' makes it a NVARCHAR string.

If you want it to have the same behaviour, you would need to make your Key column a varchar, then you also don't need to convert it to NVARCHAR again.

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [varchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Or convert it as such before it's hashed.

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', CONVERT(VARCHAR(128), [Key])), 2)

At this point though you should really wonder why you're flip flopping between the chartypes. And once again converting the result.


Does mysql SUPER privilege include ALL? I'm granting the following rights to a user:

grant super on . to 'user'@'host';

does this include the rights on dbname given by:

grant all on dbname.* to 'user'@'host';

thank you

No, it is basically a privilege that allows an account to modify how the database operates, but it's separate from INSERT, ALTER, etc.
From the documentation:

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.
To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 17.7, “Binary Logging of Stored Programs”.


How to optimize concurrent restoration of multiple VLDB user DBs? For purposes of optimizing native sql backup (full & differential) restore times, can you run concurrent restore commands for two separate VLDB native backups (10+ tb a piece) on a secondary sql server?  If so what are the recommendations for optimizing this process (sql version, san configuration, processors, memory, etc)?

I think you are going to have to build your own tests for this one - there will be relatively few people who do this on a regular basis - (but happy to be proved wrong).
Is there a reason why you can't use VSS / SAN Snapshots to meet this requirement ?
Some standard best practices are going to help, i.e. use multiple backups files, use backup compression.
regarding restoring 2 VLDBs simultaneously I would try and ensure that each database has its own separate IO systems - so probably a Diskgroup & dedicated controller on the SAN rather than just a separate set of LUNs, but talk to your storage admins as SANs vary widely with regards to best configuration.


Sub-select takes "ages" - EXCEPT is much faster Script to create the tables

DROP TABLE IF EXISTS history;
CREATE TABLE history (
    id integer NOT NULL,
    ticket_id integer NOT NULL);
ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY KEY (id);
CREATE INDEX history_ticket_id ON history USING btree (ticket_id);
DROP TABLE IF EXISTS ticket;
CREATE TABLE ticket (
    id integer NOT NULL
);
ALTER TABLE ONLY ticket ADD CONSTRAINT ticket_pkey PRIMARY KEY (id);

Dummy data

INSERT INTO history values (generate_series(1, 30000), generate_series(1, 30000));
ANALYZE history;

INSERT INTO ticket values (generate_series(1, 40000));
ANALYZE ticket;

Query with sub-select

explain analyze select distinct ticket_id from history
       where ticket_id not in (select id from ticket);

explain analyze slow sub-select

     HashAggregate  (cost=15510545.50..15510695.50 rows=15000 width=4) (actual time=170892.668..170892.668 rows=0 loops=1)
   ->  Seq Scan on history  (cost=0.00..15510508.00 rows=15000 width=4) (actual time=170892.644..170892.644 rows=0 loops=1)
         Filter: (NOT (SubPlan 1))
         Rows Removed by Filter: 30000
         SubPlan 1
           ->  Materialize  (cost=0.00..934.00 rows=40000 width=4) (actual time=0.006..2.685 rows=15000 loops=30000)
                 ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.038..21.347 rows=30000 loops=1)
 Total runtime: 170892.965 ms

Query with EXCEPT

explain analyze select distinct ticket_id from history
       except select id from ticket;

explain analyze with EXCEPT

HashSetOp Except  (cost=0.29..2449.29 rows=30000 width=4) (actual time=41.641..41.641 rows=0 loops=1)
   ->  Append  (cost=0.29..2274.29 rows=70000 width=4) (actual time=0.024..27.835 rows=70000 loops=1)
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.29..1297.29 rows=30000 width=4) (actual time=0.024..14.527 rows=30000 loops=1)
               ->  Unique  (cost=0.29..997.29 rows=30000 width=4) (actual time=0.022..10.856 rows=30000 loops=1)
                     ->  Index Only Scan using history_ticket_id on history  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.021..6.031 rows=30000 loops=1)
                           Heap Fetches: 30000
         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..977.00 rows=40000 width=4) (actual time=0.018..8.364 rows=40000 loops=1)
               ->  Seq Scan on ticket  (cost=0.00..577.00 rows=40000 width=4) (actual time=0.018..3.808 rows=40000 loops=1)
 Total runtime: 41.702 ms

DBMS version

PostgreSQL 9.3.10

Questions

Why does one take much longer than the other?

in is better for list of constant values. Try using not exists instead.

Query:

explain analyze select distinct ticket_id from history h
       where not EXISTS (select id from ticket t where t.id = h.ticket_id);

And execution plan:

Unique  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.140..23.140 rows=0 loops=1)
  ->  Merge Anti Join  (cost=0.58..2294.04 rows=1 width=4) (actual time=23.139..23.139 rows=0 loops=1)
        Merge Cond: (h.ticket_id = t.id)
        ->  Index Only Scan using history_ticket_id on history h  (cost=0.29..922.29 rows=30000 width=4) (actual time=0.037..6.848 rows=30000 loops=1)
              Heap Fetches: 30000
        ->  Index Only Scan using ticket_pkey on ticket t  (cost=0.29..1228.29 rows=40000 width=4) (actual time=0.026..6.970 rows=30000 loops=1)
              Heap Fetches: 30000
Total runtime: 23.189 ms

Reason for that I think is that for NOT IN Postgres will need to build distinct list of values from table ticket and then only filter history.
NOT EXISTS does not need to create a list. It can just check if value exists in tickets PK index.

Usually when you don't get "Anti Join" in such type of queries - something is written bad.


How to temporarily disable foreign keys in Amazon RDS PostgreSQL? I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes existing data, loads save state and enables foreign keys again.

Currently, the test framework disables foreign keys by disabling all triggers (of course, this requires superuser):

alter table tablename disable trigger all;

On RDS, this fails with:

  ERROR:  permission denied: "RI_ConstraintTrigger_a_20164" is a system trigger

How can I temporarily disable foreign keys in Amazon RDS PostgreSQL?

Note: similar question has already been asked (PostgreSQL on RDS: How to bulk import data with FK constraints?) but it was specifically about offline import and the solution is specific to offline import too.

session_replication_role

I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987

set session_replication_role = replica;

And re-enabling them with

set session_replication_role = default;

This works on RDS but still requires unusual privileges (i.e. not granted by default).

dropping and recreating FKs

Alternative solution is, as suggested in comments, to temporarily drop the FKs. This brings additional advantage that data is verified when FKs are re-enabled.

Dropping

create table if not exists dropped_foreign_keys (
        seq bigserial primary key,
        sql text
);

do $$ declare t record;
begin
    for t in select conrelid::regclass::varchar table_name, conname constraint_name,
            pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
            from pg_catalog.pg_constraint r
            where r.contype = 'f'
            -- current schema only:
            and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())
        loop

        insert into dropped_foreign_keys (sql) values (
            format('alter table %s add constraint %s %s',
                quote_ident(t.table_name), quote_ident(t.constraint_name), t.constraint_definition));

        execute format('alter table %s drop constraint %s', quote_ident(t.table_name), quote_ident(t.constraint_name));

    end loop;
end $$;

Recreating

do $$ declare t record;
begin
    -- order by seq for easier troubleshooting when data does not satisfy FKs
    for t in select * from dropped_foreign_keys order by seq loop
        execute t.sql;
        delete from dropped_foreign_keys where seq = t.seq;
    end loop;
end $$;


How can I SELECT an 'entry' that spans adjacent months? I have an Event table (used in a calendar application I am working on) that holds the following columns:

EventName
StartDate
EndDate

The events stored in said table get shown one month at a time, and that is working great, except that it does not return any events that span over into an adjacent month. For example, an event that runs from 2/25 - 3/05.

I will only get events for the current month, provided the EndDate is also within the month under consideration.

I know that this has to do with the statements I am writing. This is what I currently have:

  SELECT *
    FROM Events
   WHERE StartDate >= '2/1/2011'
     AND EndDate   <  '3/1/2011'
ORDER BY StartDate

The dates above are dynamic.

I keep the following bit of ASCII art around that I drew a few years back as reference for dealing with matching ranges, reformatted, as it was originally in POD; in your case, you're using the second one, which only matches example 3:

Interval Matching

 .........A============B......... Search Range
 ...x--y..|............|......... (never match)
 .........|............|...x--y.. (never match)
 ......x--|--y.........|......... (1)
 ......x--|------------|---y..... (2)
 .........|...x-----y..|......... (3)
 .........|..........x-|----y.... (4)

Intersection ... matches examples 1,2,3,4

A <= y && B >= x
(item fully contained by search range) ... matches example 3 only.

A <= x && B >= y
(item fully includes search range) ... matches example 2 only.

A >= x && B <= y


Cassandra - reinserting a node I had to remove a cassandra dead node on a hurry (because an important script with a truncate query wouldn't work due to consistency level reasons -- any thoughts on that would be nice too).

Anyway, the data is still there and I don't want to risk losing any of it. If my understanding is correct, I'd need to delete the data (move it away) and then reattach the node to the cluster as if it was a new one.

Could I just run the node with all its data there or could it cause any sort of corruption?

the first thing I'd worry about is zombie data. the data on your dead node might overwrite the data that you truncated. to be sure, I'd move that data somewhere else. also, delete the contents of the /commitlog and /saved_caches directories (or move them if you feel the need).

I'm assuming you have a suitable replication factor on your data? So there will be a copy of the data elsewhere in the cluster. When you re-add the node, the data will stream from the other nodes.


Which of these database designs is normalized? I have two possible database designs.

A) a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

B) two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)

See the diagrams below. Which of these designs is better and why?

Design A:  a many-to-many relationship between Branches and FiscalYears via a dedicated join table FiscalYearsBranches

Design B: two (or more) many-to-many relationships between Branches and FiscalYears via other tables (Documents, Factors, etc.)

Design A implies a relationship between FiscalYears and Branches which exists separately to the relationships between these tables and Documents and Factors. This relationship is recorded in FiscalYearsBranches

Design B does not do this, it only defines relationships between FiscalYears and Branches via Documents and Factors.

You should think about the relationship between FiscalYears and Branches: if this exists, as a real life relationship you can talk about outside of the database, even when you're not looking at Documents or Factors, then go with A. If not, go with B, otherwise you are suggesting that this relationship exists when it doesn't.


Processing cube freezes SSAS We are processing a partition on a cube in latest version of SQL Server 2017 (CU 13, also tried in CU 12 and CU 4) and sometimes it totally freezes. Task manager shows no movement at all - not memory, cpu, nor IO. The only way out is to kill the SSAS process; can't even stop the SSAS service; that is frozen too. We're importing data using M script. There are about 12 tables in the cube, SQL profiler shows 2 of them completed and the rest have starts but no completions. Something is hanging and can't figure out how to debug. Any suggestions?

In the end the only resolution that we had was to abandon the new connection type that uses "M" and go back to the legacy connection as explained here. So far it has not hung.


MariaDB throws error with documented 'alter user' ... 'password expire' option Normally I use mysql-community on RHEL7, but today I installed MariaDB 10.3.20 on Ubuntu 19. In selectively cloning a database from the RHEL-mysql cluster to the Ubuntu-MariaDB cluster, I used Percona's pt-show-grants tool. It created for me lines such as:

CREATE USER IF NOT EXISTS 'xyz'@'%';
ALTER USER 'xyz'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*157C19AA12C99769CC33EEB0B46XF5A29C000000' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON `xyz`.* TO 'zzzz'@'%';

It fails on both password expire and account unlock. Only by removing both these mariadb not fail with:

ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PASSWORD EXPIRE NEVER ACCOUNT UNLOCK' at line 1

Ugh, as noted, I am using 10.3.20, and these options are not support until the 10.4 series. I had assumed Ubuntu 19 was rather up-to-date. I also assumed percona's output would be more universal. Oh well.


How to see where the default database is configured for a particular username or login name? Once I connect to a database server without specifying a particular database, e.g. with:

sqsh -Usomeuser -Ssomeserver

… I can do a:

select db_name();

… to see the default database for this user. But where is this information kept and / or how can I see the default databases configured for every user name or login name?

Running the following code shows the default database on ASE:

SELECT sl.name
    , sl.dbname
FROM syslogins sl
ORDER BY sl.name;


Splitting a csv column from select query into multiple columns I have a stored procedure

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code,
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX([ShipWorks].[dbo].[OrderItem].[Location])
         ELSE MAX([Picklist].[dbo].[ItemData].[InventoryLocation])
    END AS Location,
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity,
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem]
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID]
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [ShipWorks].[dbo] [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

that returns this:

Code              Location                                  Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AISLE                                   1           M
GM22660003-EA      B1K2                                      1           M

I want to split the Location column into multiple columns .

Code               loc1  loc2  loc3  loc4  loc5 loc6        Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AI                                      1           M
GM22660003-EA      B1K2                                      1           M

I found this answer but that was for postgresql. Also this, but it returns a table with multiple rows and you can select the top 1 but I want all the results and I'm not sure how to use the tvf Split(string, ',')in the stored procedure to get the result set I want?

This select statement results in a error:
'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code,
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
         ELSE MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
    END AS Location,
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity,
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem]
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID]
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

Making use of Jeff Moden's Tally-Ho! CSV splitter from here:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
WITH E1(N) AS (
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
       ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
            SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER()
                                                        OVER (ORDER BY (SELECT NULL)) FROM E4
        ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just
                     -- once for each delimiter)
            SELECT 1 UNION ALL
            SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
        ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
            SELECT s.N1,
                   ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
            FROM cteStart s
        )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final
     -- element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
go

We can code the solution as an apply against Jeff's function and a pivot like so:

with data as (
    select Code,Location,Quantity,Store from ( values
        ('L698-W-EA',          NULL,                                      2, 'A')
       ,('L82009-EA',          'A1K2, A1N2, C4Y3, CBP2',                  2, 'A')
       ,('L80401-A-EA',        'A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20',    17,'A')
       ,('CWD2132W-BOX-25PK',  'A-AISLE',                                 1, 'M')
       ,('GM22660003-EA',      'B1K2',                                    1, 'M')
    )data(Code,Location,Quantity,Store)
)
,shredded as (
    select Code,Location,Quantity,Store,t.*
    from data
    cross apply [dbo].[DelimitedSplit8K](data.Location,',') as t
)
select
    pvt.Code,pvt.Quantity,pvt.Store
   ,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1
   ,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2
   ,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3
   ,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4
   ,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5
   ,cast(isnull(pvt.[6],' ') as varchar(8)) as Loc6
from shredded
pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5],[6])) pvt;
;
go

yielding this:

Code              Quantity    Store Loc1     Loc2     Loc3     Loc4     Loc5     Loc6
----------------- ----------- ----- -------- -------- -------- -------- -------- --------
L698-W-EA         2           A
L82009-EA         2           A     A1K2      A1N2     C4Y3     CBP2
L80401-A-EA       17          A     A1S2      SHIP     R2F1     CBP5     BRP      BRP1-20
CWD2132W-BOX-25PK 1           M     A-AISLE
GM22660003-EA     1           M     B1K2


Optimise many-to-many join I have three tables: posts and tags and posts_tags which forms a many-to-many relationship between posts and tags.

Having a tag with milions of posts. How could I effectively retrieve the first 10 posts ordered by its name which contain the tag?

Schema:

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    post_time   TIMESTAMP
);

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    post_id     INT REFERENCES posts(id),
    tag_id      INT REFERENCES tags(id)
);

I tried the query below but it's too slow.

WITH tag_id AS (SELECT id FROM tags WHERE name = 'tag_which_has_milions_of_posts'),
    post_ids AS (SELECT post_id FROM posts_tags WHERE tag_id = (SELECT id FROM tag_id))
SELECT * FROM posts WHERE id = ANY(SELECT post_id FROM post_ids)
ORDER BY post_time DESC LIMIT 10;

UPDATE:

I've change "post_time" to "name" to make it easier to generate test data.

Full schema (including indexes and test data):

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    tag_id    INT REFERENCES tags(id),
    post_id   INT REFERENCES posts(id)
);

CREATE INDEX ON posts(name);
CREATE INDEX ON tags(name);
CREATE INDEX ON posts_tags(post_id);
CREATE INDEX ON posts_tags(tag_id);

INSERT INTO posts (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO tags (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO posts_tags (tag_id, post_id) SELECT generate_series(1, 1000000), generate_series(1, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 1, generate_series(2, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 2, generate_series(400000, 600000);

Query (from McNets's answers):

select posts.* from posts_tags pt
    inner join tags on tags.id = pt.tag_id
    inner join posts on posts.id = pt.post_id
where tags.name = '2'
order by posts.name desc
limit 10;

Query plan:

Limit  (cost=288.72..288.73 rows=2 width=10) (actual time=893.348..893.349 rows=10 loops=1)
->  Sort  (cost=288.72..288.73 rows=2 width=10) (actual time=893.346..893.348 rows=10 loops=1)
        Sort Key: posts.name DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop  (cost=1.28..288.71 rows=2 width=10) (actual time=0.061..694.762 rows=200002 loops=1)
            ->  Nested Loop  (cost=0.85..287.78 rows=2 width=4) (actual time=0.051..78.175 rows=200002 loops=1)
                    ->  Index Scan using tags_name_idx on tags  (cost=0.42..8.44 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)
                        Index Cond: (name = '2'::text)
                    ->  Index Scan using posts_tags_tag_id_idx on posts_tags pt  (cost=0.43..278.44 rows=89 width=8) (actual time=0.009..54.474 rows=200002 loops=1)
                        Index Cond: (tag_id = tags.id)
            ->  Index Scan using posts_pkey on posts  (cost=0.42..0.46 rows=1 width=10) (actual time=0.002..0.003 rows=1 loops=200002)
                    Index Cond: (id = pt.post_id)
Planning time: 0.562 ms
Execution time: 893.428 ms
(14 rows)

Has a standard join structure less performance?

SELECT posts.*
FROM posts_tags pt
     inner join tags ON tags.id = pt.tags_id
     inner join posts ON posts.id = pt.post_id
WHERE tags.name = 'some tag'
ORDER by posts.name DESC
LIMIT 10;


how to select the particular records with filter conditions when duplicates found in combination of two columns? consider a table "SCHOOL" table, which contains the below data

when, duplicates found in NAME and LAST NAME combination it should look up for the STATUS which is "Active". if still it has duplicates, then look up for JOIN DATE which is most recent.

So the "SELECT" query should fetch S.NO 1,2,3,4,7.

S.NO 7 has ACTIVE status with most recent date

So could you please let me know the SQL Query to fetch these results?

You'll need the RANK function (here is documentation, assuming you're using Oracle). You basically group the records (normally you'd do that with GROUP BY, here it's the PARTITION BY clause) and rank/order them within that group. Then, you select the records with rank 1:

SELECT s.no FROM (
  SELECT s.no, RANK() OVER (PARTITION BY name, last_name
                            ORDER BY status ASC, join_date DESC) AS rank
    FROM school
) temp WHERE rank = 1

(I'm not really familiar with Oracle, I hope this compiles; it would work on sql-server.)


Access denied for user 'root'@'localhost' (using password: NO) on 'service mysql start' Environment

$ uname -a
Linux aurora 4.4.0-59-generic #80-Ubuntu SMP Fri Jan 6 17:47:47 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

MySQL

$ mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.16, for Linux on x86_64

Clearly something is trying to log into my mysql server as root upon

service mysql start

but I don't know what it is.

The last line in /var/log/mysql/error.log related to starting the server is:

[Note] Access denied for user 'root'@'localhost' (using password: NO)

How do I figure the offending config file or server or application that is trying to log in as 'root'@'localhost' on startup? What other information can I provide to help to understand and resolve this issue?

Also, I am able to log in as 'root'@'localhost' if I do so explicitly. It's only the [Note] in the error log that is irritating me and that I want to debug.

In order to reduce the number of variables, I've also shutdown apache and deleted my crontab just in case that was part of the problem.

I am open to all suggestions.

Redirected from https://stackoverflow.com/questions/41601069/access-denied-for-user-rootlocalhost-using-password-no-on-service-mysql

I had similar problem.

https://stackoverflow.com/questions/38098505/mysql-works-with-sudo-but-without-not-ubuntu-16-04-mysql-5-7-12-0ubuntu1-1

And solved id following by:

https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

sudo mysql -u root
DROP USER 'root'@'localhost';
CREATE USER 'root'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;


Recommended Approach to Programmatically Backing Up/Restoring Oracle DB Supposing I have an ever-growing Oracle DB on one server, and I want to duplicate this schema and the data on another server - what would be the best approach to achieving this as part of a bespoke .net app?

What I've Tried/Researched:

RMAN - seems to be the most appropriate technology available for writing batch files to do a backup but I don't feel as though I'd have as much control over it as if it were developed from scratch in C#.
PL/SQL - I could use stored procs to export data to csv and combine it with C#?
DataReaders and official Oracle API's - Seems to me like the most favourable approach as it is what I'm most familiar with - but am I reinventing the wheel given the fact that the above two technologies have the power to create these backups?
'OOTB' oracle executables - https://stackoverflow.com/questions/12524284/taking-oracle-database-dump-via-c-sharp-net http://rockingtechnology.blogspot.co.uk/2011/06/oracle-backup-and-restore-code-in-cnet.html

If you want to clone prod. db into dev. env. regulary I would recommend you to use RMAN for whole database. Or exp/imp (expdp/impdp) for single schema.
RMAN is not easy to use for developers who have no Oracle experience, but finally you will find this approach fastest and safest.

If you're about to clone your prod db into some reporting server maybe you should look at "Active Data Guard".

In Oracle you will find the "right" technology for every purpose. The final decision will depend on your real need(goal).


Suppress display in a column if the value is equal to the value in another column I have 2 date columns that are displayed side-by-side. I have some dates that are equal. I do not want to display the "equal" dates/duplicate date values in one of the columns.

Example:

    column1     column2
    1-1-2014    1-1-2014

I want to display:

    column1     column2
    1-1-2014

How do I achieve that?


If I do a full MySql restore from a dump file, will the user permissions and privileges restore aswell? I had some weird stuff happen after rebooting my system. Something changed that I did not do. Now none of my java client applications can connect to my main mysql database - they get the error

  SQL EXCEPTION: ACCESS DENIED FOR USER ''@''...

I've changed permissions, changed root password, everything I can think of and nothing has worked to fix this problem...

So if I do a complete restore, would that bring all settings and data back to its original state?

Yes, if your mysqldump is backing up all databases!

If not, for future make sure you use --all-databases with mysqldump.

Alert
but this will take your system to the state what it was earlier...
If you know what your java programs are connecting using (user-name and host) then you can choose to fix the permissions for that!!


MySQL server will not start/stop I am currently in the process of setting up a database in MySQL for an application where I will be running PowerBI to do analyses on the data in the database and in a further stage to design an application to upload/download data from this db.
I tried to install the 8.0.23.0 Community Edition through the MySQL Installer. I installed MySQL Server, Shell, Workbench and the ODBC Connector (I want to use Access as a front end) and the NET Connector (I want to import the data in PowerBI). Furthermore I choose to not have MySQL running as a Windows Service as it would keep running in the background. I am currently using my machine with limited hardware resources both as a host and a client. We don't have dedicated infrastructure yet and I am purely using my machine to test/develop.
What I would like to be able to do is the following: starting the server instance through MySQL Workbench when working on the project, open Microsoft Access and PowerBI to work on both the reports and the database. After finishing, I would stop the server instance again through MySQL Workbench.
My problem is the following: I am not succeeding in stopping/starting the instance from Workbench and I get the following error message:
I succeed in stopping the server instance through CMD but when trying to start again I get the following messages:
Does anyone know wha I am doing wrong? Did I not install enough packages to get it running or did I configure someting wrong?
Thanks in advance!

This is bug in Workbench. We have bug report for that start/stop issue. This problem was caused by migration from python2.7 to python3 and only affect last WB release. We working on that and it is already partially fixed.


SQL Server Replication Agent Profile -Output Parameter I'm trying to add the -Output parameter to my log reader agent for transactional replication and getting this error:

Msg 21805, Level 16, State 1, Procedure sp_MSvalidate_agent_parameter, Line 92
The agent profile creation process cannot validate the specified agent parameter value.
'K:\Inst06_TempDB\MSSQL10.INST06\MSSQL\Log\ReplOutput-LogReaderAgent.txt' is not a valid
value for the '-Output' parameter. The value must be an integer. Verify that replication
is installed properly and that sp_add_agent_parameter is invoked with a valid value.

http://msdn.microsoft.com/en-us/library/ms146878(v=SQL.100).aspx shows that -Output expects a path, not an integer as the error message says.

Pub & Sub are both v9.0.4211, Dist is v10.0.2723

My Script (run at distributor):

EXEC sp_change_agent_parameter @profile_id = 18
                              ,@parameter_name = '-Output'
                              ,@parameter_value = 'K:\Inst06_TempDB\MSSQL10.INST06\MSSQL\Log\ReplOutput-LogReaderAgent.txt'
EXEC sp_change_agent_parameter @profile_id = 18
                              ,@parameter_name = '-OutputVerboseLevel'
                              ,@parameter_value = '2' --[0|1|2|3|4] default 2

That link is for logread.exe.  Checking the page for the stored procedure, see:

http://msdn.microsoft.com/en-us/library/ms190313%28v=SQL.100%29.aspx

I'm wondering if the hyphen is needed, as it is not listed like that on the stored procedure's page.


Using environment variables in data pump query I have been trying to use data pump (expdp and impdp) in order to retrieve some data from a database to import it into another.

I am using the expdp using the table and the query parameters in order to select specific data from one table.

Can I use variables from Linux in the expdp and impdp queries?

For example, the directory is not a standard directory. I accept this as a variable from Linux and create a directory in Oracle using a bind variable. The thing is that later on when I want to invoke data pump I cannot have a standard directory but rather a variable. How do I use the variable from Linux in the expdp and impdp query?

Of course, you can. Here is an example:

$ cat expdp.sh
#!/bin/bash

DIR_NAME=dummy
DIR_PATH=/oracle/base/export
QUERY='employees:"WHERE employee_id = 100"'
mkdir -p $DIR_PATH
sqlplus / as sysdba<<EOF
create directory $DIR_NAME as '$DIR_PATH';
exit
EOF
expdp \"/ as sysdba\" directory=$DIR_NAME tables=hr.employees query=$QUERY

Output:

$ ./expdp.sh

SQL*Plus: Release 12.1.0.1.0 Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Directory created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export: Release 12.1.0.1.0 - Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dummy tables=hr.employees query=employees:"WHERE employee_id = 100"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "HR"."EMPLOYEES"                            9.570 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/base/export/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Mar 30 20:06:47 2014 elapsed 0 00:00:19


AlwaysOn failover DR testing I have always-on group running on 4 node cluster. Each DC has 2 node running on it

Data Center1:

Node1: Sync Replica - Automatic failover
Node2: Sync Replica - Automatic failover

Data Center2:

Node3: Sync Replica - Manual failover
Node4: Async Replica - Manual failover

While DR testing, we brought down nodes running on DC1 and I tried to bring Always-ON group on node3 (sync replica), however when I tried to failover, it gives a message that there will be data loss. Would there be any data loss if I failover manually to node3 even though it set as sync replica?

While DR testing, we brought down nodes running on DC1

Bringing down the primary server normally will cause an automatic failover to a sync AG.  However, you also brought down the only other sync secondary configured for automatic failover.  This puts the AG in a situation where there is no primary, which requires forced manual failover.  From Perform a Forced Manual Failover of an Always On Availability Group (SQL Server):

If the primary replica becomes unavailable when the WSFC cluster has a healthy quorum, you can force failover (with possible data loss), to any replica whose role is in the SECONDARY or RESOLVING state. If possible, force failover to a synchronous-commit secondary replica that was synchronized when the primary replica was lost.

A forced manual failover will always warn of possible data loss.  You need to take the steps outlined in that article to make sure you are avoiding data loss.  See the section Potential Ways to Avoid Data Loss After Quorum is Forced.
You're in the "If a synchronized synchronous-commit secondary replica comes online" situation.  You'll want to check the is_failover_ready column (in the sys.dm_hadr_database_replica_cluster_states DMV) for each database that's part of this AG.  If they are all 1, then you can force a failover without data loss.

Another possibility is that, since node3 is in the DR data center (DC2), one or more of the databases in that availability group (AG) is not in the SYNCHRONIZED state.
Even though node3 is configured as a sync replica, it can fall behind the primary and then be considered not ready for failover.  You should be able to observe this in the AG dashboard, specifically the "Failover Readiness" column in the list of databases.  They should all say "No Data Loss" in before you attempt a failover.

You'll need to wait on node3 to "catch up" (synchronize) with the primary before initiating the failover.


Changing data type from mediumint to int without downtime My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need to change the type to int or change it to unsigned without any downtime.

My setup is master-slave MySQL 5.6 on Amazon RDS. I have one master and 4 slaves.

I tried to change the column type to int on slave but it break the replication.
Records are changing, because of this it is not possible to copy the table to another table.
I cannot use pt-table-sync on Amazon RDS to sync another table and then do the changes on new table.

Is there any way to change to int without locking the table?

Update: I am not sure if the attribute is signed or unsigned. I am not able to find where it signed or not. I run show columns from table and here is the result:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
|     id         | mediumint(8) | NO   | PRI | NULL    | auto_increment |

While Rolando has my vote, because he is right in your case, I want to answer your original question (for you in the future and for others) regarding percona toolkit:

pt-table-sync is not what you want. Think about pt-online-schema-change. While there are some hard corners, it works on RDS with the right configuration or minimal changes on the script. pt-table-sync will also fix the issues with rds in the next release. There are other alternatives for online schema changes, oak-toolkit and Facebook ones.

As an alternative, you need a spare slave that can suffer the lag for the time the ALTER is running and then doing a controlled switchover.


Getting a "type" column from each table in query Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, accordingly.

I know how to get the 3 latest entries from each table, but how do I create the temporary column?

Perhaps this is what you are looking for:

(SELECT id, name, 'Book' AS media_type
FROM books
LIMIT 0,3)

UNION ALL

(SELECT id, name, 'Video' AS media_type
FROM videos
LIMIT 0,3)

UNION ALL

(SELECT id, name, 'Album' AS media_type
FROM albums
LIMIT 0,3);

I don't know if you need any particular ordering or not.  According to the docs, UNION produces an unordered set of rows.  You should be able to add an ORDER BY after the last SELECT, outside the parentheses.


Find the SQL query that caused an event in the past I have a database in which I noticed that one of my tables is missing.

I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it happened and what code was used (or generated by SQLMS) for this action.

How can I do that?

This data might still be in the default trace, which can roll over and phase out files based not only on activity but also based on service restarts (so I say it might be there because you may have restarted SQL Server more than 5 or 6 times in three months, even if there hasn't been a lot of activity).

Anyway, it is one of the examples I included in this answer, which I'll repeat a portion of here:

  Question: Who deleted the dbo.EmployeeAuditData table and when?

This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

Please also see the other answer for additional caveats (e.g. details around the lack of recording of the schema of an object that has been dropped, and also the fact that TextData will never be populated - not that it will ever be anything varying far from DROP TABLE dbo.whatever;).


How can I connect to a database after its disk has come back online? I have problems with database after reconnecting a hard disk:

.Net application works with database.
Disconnect hard disk with physical files.
Make any request to the database (of course we will have an exception, Unable to open the physical file).
Reconnect hard disk.
Any query to the database in the future will still throw an exception, Unable to open the physical file.

How can I recover connection with physical files without restarting the SQL Server service?

First, tell your customer to get a real disk. Using a remote disk this way is exactly why you are having difficulty at this stage, and it could very well lead to a corrupt / unrecoverable database. You are rolling the dice every time this drive goes offline, even if only temporarily.
Next, check the status of the database:

USE master;
GO
SELECT state_desc
  FROM sys.databases
  WHERE name = N'your_database';

If this if OFFLINE, and you're sure the drive is available, run:

ALTER DATABASE your_database SET ONLINE;

Now, this might not work - as I suggested the database could come up SUSPECT or it might not come online at all.
If / when it does come back online, take a full backup. Schedule these (and/or log backups if in full recovery) to run much more frequently, to cover your butt.
Tell your customer to get a real disk. This may work in the short term but this is a ticking time bomb.


Can two transactions lock rows in the same table in parallel? I am trying to grasp the concept of MySQL locks and came across this section in the docs: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

That leads me to the conclusion that two transactions can never lock rows in the same table in parallel. Let me explain it on the following example:
Assume I have the following table:
CREATE TABLE t1 (id INT, PRIMARY KEY (id)) ENGINE = InnoDB;

Further, lets assume I have 7 rows inserted. This is the balanced tree of the primary key:

Now assume we lock row with id 3:
SELECT * FROM t1 WHERE id=3 FOR UPDATE

From the above paragrph

A locking read [..] set record locks on every index record that is scanned in the processing of the SQL statement.

I would guess that there is an exclusive lock on row 3,5,10. Is that right?
If that would be the case, then
SELECT * FROM t1 WHERE id=100 FOR UPDATE

would try to set an exclusive lock on row 10, because it scanned in the process to find 100. But that means, it has to wait for the other transaction to be finished.
This implies, any transaction that locks a row in table t1 will always first lock row with id 10. Thus, its not possible that two transactions lock two different rows in parallel.
My questions are:

Is that actually true?
How can I actually see the balanced index tree, so I can do some local testing?

Your example is forgetting one important piece of information, which is the same row of data can be indexed more than once, by different criteria. If a second index exists with a B-Tree, and say for example the index was sorted on id descending, then you'd be able to concurrently access the row with value 100 (which would be your root node of this second index).
Now assuming by row you didn't mean table row, rather you meant a specific row location within a single index. Then yes your example above is true, when only talking about within the same index.
Note this answer is a bit high level just to explain the concepts in the context of your question, and I can't say exactly what else MySQL specifically could be doing under the hood.


Database Size error to restore to SQL Server express 2014 I have a SQL Server 2008 R2 database, about 7 GB
I make a backup from SQL Server2008R2 and  tried to restore it to SQL Server2014 Express  getting an error:

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database

what steps should i follow to avoid that problem?

The backup is 7GB? What are the sizes of the MDF files before the backup? That is what Express Edition checks - it doesn't care about the size of the backup file, it cares about how big the data files are that it has to put on the disk.

If this is for a production instance of SQL Server 2014 Express Edition, you're going to have to go back to the source and shrink those data files - it might be that they are larger than 10GB but not full, or it might be that you'll need to actually clear out some of the data.

If this is for local development, I suggest using SQL Server 2016 Developer Edition, which is free - and doesn't have any silly limits on file size.


SQL Server: how to avoid sysadmin rights but give adequate rights I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g.

Add and modify server logins
Add and modify maintenanace plans (e.g. create backups from the databases)
Schedule Agent jobs

I don't want to give him sysadmin rights, what rights should be given?

For server logins, you can grant "securityadmin". The "newer" way is to run

GRANT ALTER ANY LOGIN TO AColleague

Edit: Securityadmin allows someone to bootstrap themselves to sysadmin. Not good. Don't know how to workaround this at the server level

For jobs, look at the "SQL Server Agent Fixed Database Roles"

For maintenance plans, it looks like "sysadmin" only


Transaction publication with merge publication I have a transaction publication setup on SQL Server 2014 lets call it (node A) that replicates to SQL Server 2008 (node B), then I use merge replication to synchronize changes from (A) through (B) to a SQL Server CE database on a Win CE devices because (A) doesn't support merge replication to compact framework.

The problem is that changes came from the transaction publication (A) isn't replicating through merge publication (B) into the SQL Server CE database on the Win CE device while if I change anything in (B) database, it's replicating just fine.
Am i missing anything?

Please any help would be appreciated.

For anyone who will encounter a similar issue here's what worked for me:

Run the following on your merge publication database :

    EXEC sp_changemergearticle

    @publication = 'MyPublication',

    @article = 'MyArticle',

    @property = 'published_in_tran_pub',

    @value = 'true';

For more info about the problem read this article :https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/publish-data-and-database-objects?view=sql-server-2017


Rename the constraint of a possibly referenced primary key in PostgreSQL Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key?

ALTER INDEX foo_pkey RENAME TO pk__foo__id;

I have just tried it and it seems OK, but I have heard it is necessary to drop foreign keys, rename pkey (drop/recreate) and re-reference the table again.

In case it is not that easy, is there a way how to find all referencing tables, drop their fkeys, alter the index and atach them again in one SQL?

Here is what I am using to find tables and their fkeys referencing my table:

SELECT
    tc.table_name, tc.constraint_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name ='foo';

Not sure why Teradata has that limitation, but ALTER INDEX ... RENAME TO ... should be fine in PostgreSQL even when other tables have foreign keys depending on that index. PostgreSQL has fairly sophisticated tracking of such dependencies -- for example, if you tried to do ALTER TABLE foo DROP CONSTRAINT foo_pkey; you would see a complaint like:

  ERROR:  cannot drop constraint foo_pkey on table foo because other objects depend on it

  DETAIL: constraint bar_fkey on table bar depends on index foo_pkey

  HINT: Use DROP ... CASCADE to drop the dependent objects too.

You can also see in the pg_constraint table (e.g. via SELECT * FROM pg_constraint WHERE conname = 'bar_fkey' for some referencing foreign key bar_fkey) that PostgreSQL is internally tracking this dependency by the OID (in particular, the conindid column in pg_constraint) of the index rather than the name of the index. The OID of the index does not change when you use ALTER INDEX ... RENAME TO ..., which is how Postgres avoids becoming confused by the rename.


Can an AD service account host SPNs for multiple SQL Instances? Trying to set up Kerberos Authentication between two linked server to get past the double-hop issue.
Server A is in a domain with several other SQL Servers. All SQL instances on all servers in this domain are using the same service (user) account for running the instance.
Server B is in another domain using a separate domain account to run its instance. It is currently the only SQL server running with a domain account. Other are using local accounts.
I am configuring Server B's service account to allow for self managing SPN's which should be easy. My problem is I will need to do this with Server A's service account as well.
I can't find what I would like to make this clear to me but I am concerned about how Server A's service account will work once I allow it to "self manage" its SPNs i.e. give the AD account read/write ServicePrincipalNames of itself.
Can an AD service account host SPNs for multiple SQL Instances? Currently the account is not hosting any when I query it via setspn -L [domainname]\sqlengine
All SQL servers are running 2008 or higher. Most are 2016.

Yes it can. Eventually If you register too much spns with the same maybe you can fill the ad attribute. SPN is a non-linked-value attribute, and so you run the risk of overrunning the hard-coded 8K limitation within the underlying ESE database.
What you can't do is to share one spn registration with two service accounts.
My suggestion is to take advantage of this tool:
Microsoft® Kerberos Configuration Manager
https://www.microsoft.com/en-us/download/details.aspx?id=39046
Run it (as admin) and it will generate the proper setspn command for kerberos setup.
Eventually you have to register your instances both with netbios & fqdn names.
And please remember that you can register an spn on a domain user account or on a domain coumputer account.