MySQL - Can I have quick queries in a table with 200 million rows? THE PROBLEM
I have following trivial SELECT statement:
select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500;

The query is pretty simple (no join statements, no aggregate functions). The query is being executed on a high-class AWS RDS Aurora machine (db.r5.4xlarge). Yet, the query executes for 0.70 seconds the first time and for 0.13 seconds afterwards constantly.
CONTEXT
Here is my EXPLAIN result (as you can see I am using an index):
| id | select_type | table             | partitions | type | possible_keys                                                            | key                                   | key_len | ref   | rows | filtered | Extra                 |
| -- | ----------- | ----------------- | ---------- | ---- | ------------------------------------------------------------------------ | ------------------------------------- | ------- | ----- | ---- | -------- | --------------------- |
|  1 | SIMPLE      | web_notifications | NULL       | ref  | web_notification_users_seen_at_idx,web_notification_users_created_at_idx | web_notification_users_created_at_idx | 4       | const |  912 |   100.00 | Using index condition |

Here is the create table statement:
CREATE TABLE `web_notifications` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`seen_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
`user_id` int(10) unsigned NOT NULL,
`operation` varchar(64) DEFAULT NULL,
`start_year` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`),
KEY `web_notification_users_created_at_idx` (`user_id`,`created_at`),
CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2000000000171904234 DEFAULT CHARSET=utf8

The table has 170 million rows.
Am I doing something wrong or I should look for an alternative storage solution (probably, noSQL) for such highly-loaded and highly-intensive table (as my table web_notifications)? 0.70 is absolutely unacceptable execution time for such straightforward query.
P.S. I removed the order by phrase and the query time didn't fell down significantly.

The best thing you could do to improve query performance for that query is to change your clustered index to an actual key and not an auto-incrementing row identifier.
Right now, all records for a given user are spread out across the pages of your table roughly based on order of insert.  This is not optimal as to locate these records you will have to:

Traverse a b-tree on a secondary index (not a huge deal)
Read all the pages with entries for that user, which could be a great deal more than just 1 or 2.

So assuming your key is (user_id, created_at)1, you would set your table up as:
CREATE TABLE `web_notifications`
(
  `user_id` int(10) unsigned NOT NULL,
  `text` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `seen_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `operation` varchar(64) DEFAULT NULL,
  `start_year` smallint(5) unsigned NOT NULL,
  CONSTRAINT PK_webnotifications PRIMARY KEY (`user_id`,`created_at`),
  /* KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`), */ /* This index would probably not be necessary or even utilzed with the new primary key */
  CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8

This has the added benefits of:

Allowing the engine to directly access the rows via the clustered index
Saving a 8 bytes per row
Actual uniqueness
Potentially eliminating an additional index

However, there will be the need to additional maintenance, namely periodically rebuilding the table if performance degrades due to index fragmentation2.  Fragmentation may be managed by tweaking the fill_factor, but that will depend on the maturity of the table, the distribution of activity by user_id, and how long you retain data in the table3.
Other things to consider:

Don't do SELECT * - don't waste I/O on columns you don't need.
If you haven't already, wrap the query in a stored procedure.  Better from a security standpoint, and having the plan cached should save some compilation time.
You probably don't need deleted_at.  Upon delete, just change the value for updated_at and set a flag IsDeleted = 1.  It will be understood that the value for updated_at was the time the record was deleted.
operation looks like it might be something that has a limited number of values - consider creating a shorthand code for each of those and store that in a reference table.
If start_year is derived from another column, there is no need to persist it.  If it doesn't change depending on information in web_notifications consider moving it to another table.
You still may be limited by network I/O if you're returning a large amount of data, this is just the reality of being in the cloud.  But at least you'll be eliminating all other bottlenecks.

1 If there is an additional column required for uniqueness, just add it to the key.  Depending on the queries you run (and how many records each user has), it may or may not make sense to keep created_at as the last column in the index or the one immediately following user_id.
2 Or don't bother as Rick James mentions in the comments.  He has more MySQL experience so I'll defer to him.
3 Tweaking fill factor in other DBMSs with clustered indexes can reduce the rate at which a table fragments, but again I'll defer to Rick.


Service Broker to Send Messages Specific Database bases based on critiera In Message Broker can you configure to use one queue but based on criteria send to a different Databases or is it a one to one relationship? I am tryibg to figure out if I should use Message broker or SSIS package.

Service Broker conversations are between services, not between queues. You send from a service, to a service. A conversation is tied to exactly a pair of services and cannot change. The same service can participate in any number conversations, with any number of peer services. If you want to send messages to multiple peers, you need to start multiple conversations.


Accidently Named all tables in my database starting with a number I've accidently named all my tables (100+) in my sql database with the prefix 2019_

Wasn't aware this was not a good idea until just now (just returned to SQL after 2 years)

Is there any way to bulk swap it to a suffix or even delete all the tables? I can't even seem to open them. Really don't want to go through them all manually.

Appologies if this is a silly question, potentially googling the wrong terms

Thanks!

In your situation, I would probably use a script something like this to generate all the sp_rename commands for me:

SELECT SCHEMA_NAME(schema_id) as schemaname, name as tablename,
cmd = 'EXEC sp_rename ''' + SCHEMA_NAME(schema_id) + '.' + name + ''', '''
  + SUBSTRING(name, 6, 99) + ''';'
FROM sys.tables
WHERE type = 'U'
AND is_ms_shipped = 0
AND name LIKE '2019[_]%'

This takes the name from the sys.tables system table, and finds all the user-created tables with a name starting with 2019_. It generates a list of sp_rename commands, which you can copy into a new SSMS window and carefully review to make sure they look correct before running.

Note that this is going to break any other objects (views, triggers, stored procs,  etc.) that refer to these tables, so I hope this database is very early in development.


Immediate e-mail alerting for error 229 (permission denied) For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL Server Agent error alerts, which rely on the SQL Server error log (I've tested this - I get no alert).

I figured I'd peek in mssqlsystemresource and look at the view definition for sys.messages, thinking maybe I could update is_event_logged for the message in question. But this view gets the system error message from OpenRowset(TABLE SYSERRORS), so that's a no-go.

Is there a reasonably simple way I can get all 229 errors emailed to me immediately (or within maybe 30 seconds) without harming server performance? A 60-second cool-down between emails would probably be a good idea too.

Oh, damn it, not 30 seconds after I post this, I stumble across sp_altermessage (naturally, after spending a significant amount of time trying to figure out if I could do this with extended events). So if anybody else is wondering how to do this:

EXEC sp_altermessage 229, 'WITH_LOG', 'true'


SSRS 2014 Exec Log gives the Service Account as UserName instead of Actual User name I am using the below query for accessing the Users who are using the Application Reports.

SSRS 2014 gives the Service Account(IIS Account used for Application) as User Name instead of Actual User name.

USE ReportServer;
GO
SELECT el2.username,
el2.InstanceName,
el2.ReportPath,
el2.TimeStart,
el2.TimeEnd,
el2.[Status],
isnull(el2.Parameters, 'N/A') as Parameters
FROM ExecutionLog2 el2
GO

Can you please let me know why I am not able to see exact username who accessed the Report instead why I am seeing the Service Account in the place of User Name exactly.

There is only one DISTINCT RequestType which is Interactive. Where as when we read IIS logs using Log parser it does contain the User Name individually

Thanks

You may be seeing a Subscription execution. You can spot-check this by...

In the UI by going to the report listed on the ReportPath and going to Manage > Subscriptions
In the DB by checking the dbo.Subscriptions table (see some helpful queries at this SO post)


Design for user profile information and track changes I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like:

street
city
zip code
mobile phone
country
contact name

And the user's email and a hash of their password (of course).

I know storing all of this data in one table is a bad idea, but I cannot come up with a better solution for now. Can you help me with that and suggest a way of dividing this data into different tables?

I would also like to keep track of all changes that user does with his account data (physical address/name/phone number/email/password hash).

You can follow a proven design from data warehousing called a Slowly Changing Dimension.

Essentially on each row you add some combination of an ActiveFlag, DateTimeStart and DateTimeEnd. When you get a change you end the old record and write a new one, which will include the changes that triggered the historic change. If you do not care about some fields' history you can simply update in place on the active row.

There will only ever be one active row for any given login and any number of historic versions of the information. If you keep the start date and end dates you can see when changes occurred in addition to simply keeping older records around. You can always move ended rows to a different table, and retain only the most recent record in your system table... I'd make sure that the performance hit and maintenance costs of doing so outweigh simply keeping them in place.


What could cause a sleeping session to have an open transaction? For one of our SQL servers there has been a proc which creates a blocking mess:
That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from sp_whoisactive is status = sleeping and open tran 1 for duration of approx 5-6 minutes. In this duration heaving blocking chain shows up.
This SP do not have any transaction like BEGIN and END TRAN. It does some basic select col,col2,col3.... into #temptables from table1 inner join table 2 ... and then select from that #temptable
While we are checking from app why there could be a transaction left open, i was reading in such scenarios to use XACT ABORT ON in SP itself. But when there is no transaction involved how XACT ABORT setting will help in  this case?
Please advise

Scenarios that may explain these symptoms include:

The application code has started a transaction and not committed

The application SET IMPLICIT_TRANSACTIONS ON

The stored procedure includes SET IMPLICIT_TRANSACTIONS ON

This DMV query will help troubleshooting. The transaction name value will be "implicit_transaction" if started implicitly. Other possible values include "user_transaction", "DTC Transaction", or a user-specified transaction name. A "user_transaction" value (with no explict proc transaction) indicates the transaction must have been started on the client side and not committed. A value of "DTC Transaction" indicates a distributed transaction, also indicating an  issue with transaction management on the app side.
SELECT
      session_tran.session_id
    , active_tran.name
FROM sys.dm_tran_session_transactions AS session_tran
INNER JOIN sys.dm_tran_active_transactions AS active_tran ON session_tran.transaction_id = active_tran.transaction_id;

It is a good practice to include SET XACT_ABORT ON in stored procedures with explict transactions to ensure transactions are rolled back, especially following a client timeout. That probably won't help here since your proc has no explict transaction, unless the proc happens to raise an error and rollback the erroneous transaction as result.


Relational tables with same content, custom solution So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this:

Table foo and table foo_comments, where foo_comments contains id, foo_id and other columns.

Right now this is the only way that I am using comments in my app. I did get a note from somebody that, if they will update the app in the future and they will want to add comments also for BAR, I should add something custom from right now, even if I don`t need it right now. So I was advised to add something like this

A table comments (for general use), that would contain a type column (where type can be an int representing the table which is referring to: foo, bar or something else) and a type_id column that would contain the id of the row from the main table.

So it`s this a good practice?
Should I build something like this, just because the app can be changed in the future? Or I just keep it with foo_comments table, and then if the app will add comments for BAR table, I would create a bar_comments table also.

I would keep them separate. Although the details differ, your circumstance smells a lot like the One True Lookup Table antipattern. By combining you will have problems defining foreign keys (which parent to point at?), amongst other things. Indexing strategy will essentially be about spliting the combined table into its constituent parts. And so on...

The justification for combining would be if Foo and Bar are sub-types of some more abstract entity type and all comments could hang off this super-type.


Questions since MySQL Startup Have a weird question / (bug?) here. Using PHP My Admin I installed via apt-get on Ubuntu 13.10

Versions are as follows:

Server version: 5.5.35-0ubuntu0.13.10.2 - (Ubuntu)
Apache/2.4.6 (Ubuntu)
Database client version: libmysql - 5.5.35
PHP extension: mysqli Documentation
PHPMyAdmin Version information: 4.0.6deb1

Under the Stats page it is showing the following:

Questions since startup: 2,144
per hour: 405
per minute: 7

Here is the weird thing - about 5 minutes ago it said 5000+ questions since start-up.

I am the only user with access to the database besides the "WWW User" I have created for my PHP pages. My web server is not open to the public - port 80 is blocked on my router, and I have password protection set up in my Apache2.conf file. In order to access my LAMP server on the fly I use ConnectBot for Android to tunnel port 8080 to my computer's port 80. I am not running 405 queries per hour! I have only probably ran at most 20 on the database in the last hour including queries made by PHP scripts. The only way someone other than me could be accessing my LAMP server is if they were using a wifi sniffer and cloning their MAC address to bypass MAC filtering to get past my router - but my router shows nothing unusual connected.

Additionally - it is showing the following, which really has me concerned:

show binlogs    73  13.8    3.40
show tables 34  6.4     1.59
show variables  28  5.3     1.31
show master status  27  5.1     1.26
show status 27  5.1     1.26
show slave status   27  5.1     1.26

With the exception of show tables, I did not know these commands existed. I have not ran a show tables command in several days.

Is someone hacking my database? Or is PHPMyAdmin reporting inaccurate information?

You may have been unaware of this, but everything and its grandmother done in MySQL is a question.
In light of this, the real question is : To mysqld, what is a Question ?
According to the MySQL Documentation on Questions:

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Anything you issue for the sake of running queries or just checking a status of some kind is a Question. Even checking how many questions in your session is itself a Question. Here is proof: (I will connect to MySQL 5.6.14 for Windows and ask for Questions in my session):
Microsoft Windows [Version 6.2.9200]
(c) 2012 Microsoft Corporation. All rights reserved.
C:\Windows\system32>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.14 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 3     |
+---------------+-------+
1 row in set (0.11 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 5     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

This is normal to expect, so there is really nothing to worry about.
YOUR ORIGINAL QUESTIONS

Is someone hacking my database?

No, you are not being hacked.

Is PHPMyAdmin reporting inaccurate information?

PHPMyAdmin asks mysqld Questions all day long. The result is that it just runs up the global count (As seen from SHOW GLOBAL STATUS;). I wrote an answer to a post in ServerFault about 2.5 years ago entitled 1 billion mysql queries in 24 days? Can something be wrong?. I attributed the runaway stats to monitoring then, and I still strongly assert this now.


Changing a user from being linked to a login to being without login I can create a user like this:

create user [foobar] for login [foobar];

I can also create a user like this:

create user [foobar] without login;

Is there a way to change a user that was created with the first method into a login-less user as though it had been created with the second method?

It seems that the opposite isn't possible (i.e. mapping a login-less user to a login) and it's not immediately clear to me why that would be the case. So if anyone can shed light on the reasons for that as well, I'd consider it a bonus.

There is no method of directly altering a user to be loginless.  Having said, you could simply drop and recreate the login:

DROP USER foobar;
CREATE USER foobar
WITHOUT LOGIN;

Of course, you'd want to gather details about what rights the user has prior to dropping it, so you can recreate those permissions after the CREATE USER statement.

Attempting to connect a loginless user to a login, via the following syntax:

ALTER USER foobar
WITH LOGIN = foobar;

Does not work, contrary to what I reported here previously.  That attempt results in the following error:

  Msg 33016, Level 16, State 1, Line 5
  The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.


Delete document from nested array in MongoDB I've created a collection with following insert statement

db.test.insert({
"_id": 1,
"updatedAt": ISODate("2016-05-27T05:37:44.928Z"),
"created_at": ISODate("2016-05-13T09:41:23.000Z"),
"batchId": "BATCH-Con-0",
"size": 7,
"currentlySubscribed": 0,
"startDate": ISODate("2016-05-15T18:30:00.000Z"),
"endDate": ISODate("2016-07-08T18:30:00.000Z"),
"dailyHours": 6,
"trainer": null,
"status": "Started",
"attendance": [
    {
        "date": ISODate("2016-05-04T18:30:00.000Z"),
        "students": [
            {
                "studentId": 1,
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            },
            {
                "studentId": 2,
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            },
            {
                "studentId": ("5735a3266c1e1dc423c302cf"),
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            }

        ]
    },
    {
        "date": ISODate("2016-05-05T18:30:00.000Z"),
        "students": [
            {
                "studentId": 1,
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            },
            {
                "studentId": 2,
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            },
            {
                "studentId": 3,
                "entryTime": ISODate("1969-12-31T19:31:00.000Z"),
                "exitTime": ISODate("1969-12-31T19:31:00.000Z"),
                "attendanceStatus": "Present"
            }
        ]
    }
    ]})

Now I want to delete the attendance record of some of the students with this query:

db.test.update({},{$pull: {"attendance.$.students": {"studentId":{$in:[1,2,3]}}}},{multi:true})

But this gives me an error stating "The positional operator did not find the match needed from the query. Unexpanded update: attendance.$.students"

If you want to use the $ positional operator, you have to have matched the array items already, something like this:

db.test.update(
  {"attendance.students.studentId":{$in:[1,2,3]}},
  {$pull: {"attendance.$.students": {studentId: {$in:[1,2,3]}}}},
  {multi:true}
)

Remember that this will only update the first matching item in the attendance array in each document, so you will have to run this update more than once to make it update each attendance item.


Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the varchar column is not Unicode and that that's the reason it is changing some of the characters to ??.
However, how come existing value written in Japanese is stored in varchar while ideally it should be in nvarchar?
Is there a way to convert nvarchcar to varchar?
The database is out of our control and we cannot change the schema.

As Tibor mentioned, the Japanese_Unicode_CI_AS collation (all Japanese_* collations, actually) can store Japanese characters in VARCHAR columns due to Windows code page 932 being a Double-Byte character set (DBCS). I believe there are just under 7800 Japanese characters mapped to the Windows-932 code page. However, Unicode contains more than 7800 Japanese characters.
It would be very helpful (maybe even necessary) to know some things in order to narrow down the problem:

Exactly how you are importing the CSV file. Are you using BCP or BULK INSERT / OPENROWSET(BULK...)?
An example of at least one Japanese name that is getting converted to ??
You say both that the "Japanese names from CSV file that are changing into question marks" and "it is changing some of the characters to ??", so:

Are all Japanese characters changing into question marks, or only some?
Are the Japanese characters changing into one question mark ( ? ), two ( ?? ), or some of each?

Without knowing the answers to those questions, I can say that there are two main possibilities:

(all Japanese characters imported as ?):   You aren't telling the import tool what the encoding of the CSV file is. Is it encoded as Windows-932 (or possibly Windows-31J)? Or is it a Unicode encoding such as UTF-8 or UTF-16 (which might be listed as "UCS-2" or "Unicode" depending on the tool)? If you are using BCP, you would need to use the command-line options of -c -C 932 for Windows-932, or -c -C 65001 for UTF-8. This problem should be fixable simply by setting the tool to use the correct code page.

(some Japanese characters imported as ? or ??):   If you are telling the import tool the correct encoding of the file, there are still Japanese characters that are not encoded in the Windows-932 code page. For example:
-- DROP TABLE ##BCP;
CREATE TABLE ##BCP ([Value] VARCHAR(50) COLLATE Japanese_Unicode_CI_AS);
INSERT INTO ##BCP ([Value]) VALUES (N'ヤ :: ㋾ :: ');
SELECT * FROM ##BCP;

returns:
ヤ :: ? :: ??

This problem can only be fixed by doing one of the following:

Change the datatype of the column to be NVARCHAR (though you said that you can't change the schema)
Change the collation of the column to use a UTF-8 collation (i.e. collation names ending in _UTF8; introduced in SQL Server 2019)

For info on working with collations / Unicode / encodings, please visit: Collations Info


complex update query postgres I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish):

update activities
set activities.created_at = messages.created_at,
set activities.updated_at = messages.updated_at
from messages
where messages.id in (1,2,3)
activities.trackable_id in (1,2,3)

In activities, the trackable_id is foreign key to the messages table. So I need to set the right created_at and updated_at time in activities table for some messages.

So basically the activity table is the copy of messages table but only in another format.

How can I update created_at and updated_at in the activities table for some message ids, but using the data from messages table (data being updated_at and created_at)?

You do not seem to join your activities and messages tables in your where clause. The more correct syntax would be:

update activities
set created_at = messages.created_at,
updated_at = messages.updated_at
from messages
where messages.id = activities.trackable_id
  AND messages.id in (1,2,3)


Because you only provided pictures i populated the tables with different data but this should alse apply to your case. Is this what you are looking for?
EDIT:
I changed the query to be dynamic. This should do the trick. Of course, you have to change table name #tbl to your table name.
/*Creating a table and populating it*/
CREATE TABLE #tbl (product nvarchar(50), region nvarchar(10), [year] int, sales int);
DECLARE @sql nvarchar(max);
DECLARE @columnname nvarchar(max);

INSERT INTO #tbl(product, region, [year], sales)
SELECT  'TEA',  'D1',   2018,   40
UNION ALL SELECT    'TEA',  'D2',   2018,   30
UNION ALL SELECT    'TEA',  'D3',   2018,   20
UNION ALL SELECT    'TEA',  'D4',   2018,   10
UNION ALL SELECT    'TEA',  'D5',   2018,   7
UNION ALL SELECT    'COFFEE',   'D1',   2018,   70
UNION ALL SELECT    'COFFEE',   'D2',   2018,   100
UNION ALL SELECT    'COFFEE',   'D3',   2018,   200
UNION ALL SELECT    'COFFEE',   'D4',   2018,   300
UNION ALL SELECT    'COFFEE',   'D5',   2018,   400
UNION ALL SELECT    'TEA',  'D1',   2019,   1
UNION ALL SELECT    'TEA',  'D2',   2019,   2
UNION ALL SELECT    'TEA',  'D3',   2019,   3
UNION ALL SELECT    'TEA',  'D4',   2019,   4
UNION ALL SELECT    'TEA',  'D5',   2019,   5
UNION ALL SELECT    'COFFEE',   'D1',   2019,   6
UNION ALL SELECT    'COFFEE',   'D2',   2019,   7
UNION ALL SELECT    'COFFEE',   'D3',   2019,   8
UNION ALL SELECT    'COFFEE',   'D4',   2019,   9
UNION ALL SELECT    'COFFEE',   'D5',   2019,   10
UNION ALL SELECT    'TEA',  'D1',   2020,   13
UNION ALL SELECT    'TEA',  'D2',   2020,   26
UNION ALL SELECT    'TEA',  'D3',   2020,   39
UNION ALL SELECT    'TEA',  'D4',   2020,   52
UNION ALL SELECT    'TEA',  'D5',   2020,   65
UNION ALL SELECT    'COFFEE',   'D1',   2020,   78
UNION ALL SELECT    'COFFEE',   'D2',   2020,   91
UNION ALL SELECT    'COFFEE',   'D3',   2020,   104
UNION ALL SELECT    'COFFEE',   'D4',   2020,   117
UNION ALL SELECT    'COFFEE',   'D5',   2020,   130
;

SELECT @columnname=COALESCE(@columnname+ ',', '') + QUOTENAME(CAST(q.region AS nvarchar(20)),'[]')
FROM
(SELECT DISTINCT t.region
FROM #tbl t
UNION ALL
SELECT DISTINCT t.region+'D' AS region
FROM #tbl t) AS q

/*for your case, just replace the table name #tbl with the name of your table*/

SET @sql='
SELECT product, [year], '+@columnname+'
FROM
(SELECT product,
CASE WHEN salee=''diff'' THEN region+''D'' ELSE region
END AS sales, [year], sale
FROM
(SELECT t.product, t.region, t.[year], t.sales, ISNULL((t.sales - LAG(t.sales) OVER(PARTITION BY t.product, t.region order by t.[year] asc)),t.sales) AS diff
FROM #tbl t) AS a
UNPIVOT
(sale for salee IN (sales, diff)) AS up) AS q
PIVOT
(MAX(sale)
FOR sales IN ('+@columnname+')) AS p
ORDER BY product'

EXECUTE sp_executesql @sql

Output:
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| product | year | D1 | D2  | D3  | D4  | D5  | D1D | D2D | D3D  | D4D  | D5D  |
+=========+======+====+=====+=====+=====+=====+=====+=====+======+======+======+
| COFFEE  | 2018 | 70 | 100 | 200 | 300 | 400 | 70  | 100 | 200  | 300  | 400  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2019 | 6  | 7   | 8   | 9   | 10  | -64 | -93 | -192 | -291 | -390 |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| COFFEE  | 2020 | 78 | 91  | 104 | 117 | 130 | 72  | 84  | 96   | 108  | 120  |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2018 | 40 | 30  | 20  | 10  | 7   | 40  | 30  | 20   | 10   | 7    |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2019 | 1  | 2   | 3   | 4   | 5   | -39 | -28 | -17  | -6   | -2   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+
| TEA     | 2020 | 13 | 26  | 39  | 52  | 65  | 12  | 24  | 36   | 48   | 60   |
+---------+------+----+-----+-----+-----+-----+-----+-----+------+------+------+


How to prevent asking for password when creating new database in PostgreSQL 10? I use the following batch file to create a new database in PostgreSQL 10.

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
createdb -h localhost -p 5432 -U postgres myDB

But it asks to enter the password. How to prevent asking for the password? I tried the -w option explained in this link to not ask for password prompt. But this did not work. How to prevent asking for password?

use the PGPASSWORD environment variable,

@SET PGPASSWORD=something_secret
psql -c "CREATE DATABASE mydb" -U postgres postgres

or just use a connection string

psql -c "CREATE DATABASE mydb" "user=postgres dbname=postgres password=something_secret"


How to terminate all DB usage of a PostgreSQL user How to terminate all DB activities of a particular PostgreSQL user?

I know how to select them: SELECT * FROM pg_stat_activity WHERE usename='foo_user'?

And I found this here: https://stackoverflow.com/a/35319598/633961

  What I did is first check what are the running processes by

SELECT * FROM pg_stat_activity WHERE state = 'active';

  Find the process you want to kill, then type:

SELECT pg_cancel_backend(<pid of the process>)

  If the process cannot be killed, try:

SELECT pg_terminate_backend(<pid of the process>)

But how to apply this to several processes?

Use Case

Sometimes a test in CI takes too long and the system should be destroyed.

pg_cancel_backend takes a process id as its argument.
It doesn't care if that comes from you typing it in, or a from a query.

That makes it possible, albeit quite risky, to combine these two into one:

SELECT pg_cancel_backend( pid )
FROM pg_stat_activity
WHERE usename = 'foo_user'
AND state = 'active' ;

OK, this doesn't give you option to escalate the "cancel" to a "terminate", but it might be enough.

Obviously, inappropriate use of this could cause huge amounts of Trouble.
Use with caution.

A better solution might be to not let them into the database in the first place, but that's a little Draconian and rarely a Real World option (no matter how satisfying it is to "slam the door" on a troublemaker).

The best solution would be to find out why the User is doing what they're doing and improve their process/query to "play" better with everything else that your database is doing.


What is the difference between Interbase and Firebird? I'm interested in a broad overview of how they are diverging and why.

InterBase version 6 was released under an open-source license in mid-2000, and was immediately forked to become the Firebird project.

Later versions of InterBase reverted to closed-source and the projects have diverged. Both are still actively developed. Developer’s tools and libraries traditionally support both servers but they are growing apart.


why innodb generate table-lock when two sql using different index? I know innodb-engine row lock by add lock to index item. But I don't understand the following scene.

Prepare sql:

DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE tbl_order (
    `order_id` BIGINT NOT NULL,
    `product_name` VARCHAR(16) NOT NULL,
    KEY `order_id_idx`(`order_id`),
    KEY `product_name_idx`(`product_name`)
) Engine=InnoDB CHARSET=utf8mb4;
INSERT INTO tbl_order(`order_id`, `product_name`) VALUES(1, 'prod1'), (2, 'prod2');

Session-1 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE order_id=1 FOR UPDATE;

Session-2 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE product_name='prod1' FOR UPDATE;

I know session-1 use order_id_idx index item, session-2 use product_naem_idx index item. I don't understand why session-1 blocks session-2.
The locks information:

The GEN_CLUST_INDEX indicates innodb use table-lock.
Could anyone help me?

Every InnoDB table should be given an explicit PRIMARY KEY.  It must be "unique" and can either be a column (or combination of columns) or can be an AUTO_INCREMENT surrogate.

GEN_CLUSTER_INDEX is the fallback when you don't provide a PK.

VALUES(1, 'prod1') -- You are locking the one row from two different approaches (the secondary indexes).  Hence, it is quite proper to have a lock.  This lock will either lead to delaying one of the transactions until the other finished, or lead to a deadlock which will abort (and rollback) one of the transactions.

I don't believe you getting a "table lock", but instead a "row lock".

If your table is a "many-to-many" mapping table, please follow the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table


Are many NULL columns harmful in mysql InnoDB? As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance.

Does the presence of excess columns slow down mysql performance?

P.S. I tried to practically test, but no significant effect. However, I think it should be compared under heavy load. This is the reason that I am curious to learn about technical reasoning on this matter.

The only harm I could see is doing COUNT queries against large tables.

Doing SELECT COUNT(*) FROM mytable on an InnoDB table should produce a full table scan. However, think of what COUNT() actually does. The * represents a whole row. COUNT() can determine if non-NULL columns are present. The easiest way for that to happen it to make sure PRIMARY KEY columns are the first columns in ordinal position. PRIMARY KEY columns are always NOT NULL by definition. Thus, SELECT COUNT(*) FROM mytable would be just as fast as SELECT COUNT(1) FROM mytable.

I would not be worried about excessive columns as InnoDB places a cap on the number at 1000. Of course, IMHO having 20-30 columns in a table (regardless of Storage Engine) is too high, which indicates either poor design (begging for normalization) or column data that is just too big.

PostgreSQL kind of solves that be having TOAST tables. TOAST stands for The Outside Attribute Storage Technique. That manages column data that is way too big for normal row storage.

InnoDB has no mechanism like TOAST, so I expect row chaining of some sort in the .ibd files or within ibdata1. Notwithstanding, NULL columns will prevent the physical manifestation of oversized row data. Anyone could live with that.

As long as InnoDB tables are properly indexed, NULL columns would not be an issue. Besides, all non-unique indexes have internal rowids back to the Clustered Index (aka gen_clust_index). Therefore, well-tuned queries will always be accessing data via the Clustered Index.


cross database trigger: server principal is not able to access the database under the current security context I have a two databases Source and Target hosted in the same SQL Server 2008 R2 instance. I have a Login which is mapped to Users in both databases. Each mapped User has a Role in its respective database which grants access to all objects in that database.

I have a cross-database trigger which propagates updates from a table in Source to a table in Target.

When I connect to the server in SQL Server Managment Studio with the Login and run an update query on Source, the cross-database update succeeds.

But when an equivalent update is initiated using the same Login from within an application, the trigger fails with error:

The server principal 'name' is not able to access the database 'Target' under the current security context.

By running a trace with SQL Server Profiler, I can verify that the update is being run under the same LoginName in both cases.

Questions:

What is special about SSMS that allows the cross-database trigger to work?
What does "current security context" mean?
What do I need to modify to allow the update+trigger to work in the Application's context?

I've read about object ownership chaining, and I've not fully investigated that yet. But since it works in SSMS, I'm tempted to believe that a broken ownership chain is not the issue. But I could be wrong about that! I'd be really grateful for any diagnosis suggestions.

Update: I'm attempting to implement srutzky's suggestion. This is what I have run so far.

use [SourceDB];

create certificate [Access_TargetDB]
   encryption by password = 'password123'
      with subject = 'Cross-DB Access to TargetDB',
      expiry_date = '2099-12-31';

add signature to [MyTrigger]
    by certificate [Access_TargetDB]
    with password = 'password123';

backup certificate [Access_TargetDB]
    to file = 'C:\Access_TargetDB.cert';

use [TargetDB];

create certificate [SourceDB]
    from file = 'C:\Access_TargetDB.cert';

create user [SourceDBUser] for certificate [SourceDB];

exec sp_addrolemember 'StandardUserRole', 'SourceDBUser';

I am still getting the same error when attempting to update the table in SourceDB with the Application Role context.

I'm attempting to implement srutzky's 2nd suggestion. In addition to the above, I have also run the following. But the suggestion assumes a target stored procedure which I do not have, so maybe this isn't actually a valid test.

use master;

create certificate [SourceDB]
    from file = 'C:\SourceDB.cert';

create login [SourceDBLogin] from certificate [SourceDB];

grant authenticate server to [SourceDBLogin];

Still no change in the permissions available in the Application Role context.

This should be fairly simple to accomplish via module signing. Look at my other answers here on DBA.SE:

Permissions not allowing Update or Select on a trigger executed function
Permissions in triggers when using cross database certificates

The basic concept is as follows:

Create a Certificate in the Source DB (i.e. where the Trigger exists).
Sign the Trigger using ADD SIGNATURE.
Create that same Certificate in the Target DB.
Create a User in the Target DB from that Certificate.
Grant that Certificate-based User INSERT, UPDATE permission on the table in the Target DB.


Availability Group backup preference not changing to primary I have an availability group setup and I'm using Ola Hallengren backup scripts. At setup of the Availability group, the default option of Prefer Secondary was set, where it should have been changed to primary.

Once I noticed this, I tried to set the backup preference to Primary, but it doesn't change, it stays on Prefer Secondary.

Has anyone come across this before?  I'm using the user interface (availability group wizard) to make the change.

I tried to set the backup preference to Primary but it doesn't change, it stays on Prefer Secondary

I'm not sure if you mean the user interface, or if the actual effect of the change didn't occur as you expected.  My answer assumes the latter.

To be clear about something that I find a little confusing: this setting is not something that automatically affects how backups are run.  It's simply a place where you, as a DBA, can store "metadata" about how you prefer backups to be taken.

This is documented, but it's not obvious at all from the user interface.  Here's the quote from the docs:

  ❕ Important

  There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad hoc backups.

After setting this, you need to update your backup jobs with code like this (also lifted from the docs):

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))
BEGIN
      Select 'This is not the preferred replica, exiting with success';
      RETURN 0 - This is a normal, expected condition, so the script returns success
END
BACKUP DATABASE @DBNAME TO DISK=<disk>
   WITH COPY_ONLY;

Using sp_helptext to view the source of sys.fn_hadr_backup_is_preferred_replica, it can be seen that this takes into account both the AG-level AUTOMATED_BACKUP_PREFERENCE setting, and the replica-level BACKUP_PRIORITY setting.

Note: since you're using Ola's scripts, this is already taken into account

If you just didn't see the value change in the user interface, I would suggest using the "script" button to show the T-SQL that's being run by the UI, and then running that script manually to see if the change is properly made.


Query input must contain at least one table or query error I'm working on a java program that connects to a Microsoft Access database and displays the data in a GUI. I'm currently trying to code a button that adds a new row to a table called Grade that holds grades for students that exist in another table called Student for courses that exist in a third table called Course.

The condition for adding a new grade is that the student and course already exist in another table. I thought I had gotten the SQL statement right but when I run it, an error in access appears that says "Query must contain at least one table or query".

Here is my SQL statement:

INSERT INTO Grade (StudentID,CourseID,Semester,Year,Grade)
VALUES((SELECT StudentID FROM Student WHERE StudentID = 5812356),(SELECT CourseID FROM Course WHERE CourseID = 'CS101'), 'Fall', '2016','B');

The StudentID and CourseID specified exist in the Student and Course tables.

I don't understand this error since I have specified which table I'm trying to add to and I have written out a query.

I appreciate any help given.

I think you've got your VALUES clause a bit mixed up.

Is this what you're after?

INSERT INTO Grade
       (StudentID, CourseID, Semester, Year, Grade)
SELECT s.StudentID, c.CourseID, 'Fall', '2016','B'
FROM Student s
JOIN Course c
ON s.StudentId = c.StudentId
WHERE s.StudentID = 5812356
AND c.CourseID = 'CS101';


SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure I have a stored procedure that creates a dynamic SQL Query. I use sp_executesql, to have it SQL injection safe.

I have a second stored procedure that generates a where logic.
For example if I run the second procedure EXECUTE dbo.getWhereLogic @FilterID, @parOutput = @WhereLogic OUTPUT it will return as output (1 = 1)
I want to extend my first stored procedure with this where logic.

Let's assume that @SQLQuery = "Select * FROM Blabla Where 1 = 1" and @WhereLogic = (1=1)

If I do it like this:
SET @SQLQuery = @SQLQuery + ' AND ' + @WhereLogic
It will work, but it is not SQL injection safe.

If I do it like this:
SET @SQLQuery = @SQLQuery + ' AND @WhereLogic'

I get an error that there is a non-boolean type defined. There is a comparison missing. I've tried a couple of possibilities.

I think running the second procedure with sp_executesql will have no effect. That procedure returns the WhereLogic as varchar without problems, even if I simulate an SQL injection attack.

The two procedures are too long and complex to post it here. The main problem is also without code example clear.

To put the second procedure in the first and have a logical where query would be a possibility, but then I have to re write the second stored procedure and it was a lot of work :-( This should be the last option.

If someone has faced this problem already, I would appreciate an answer.
Links to other posts would be helpfull too.
Thanks in advance!

EDIT:
I try to create later a dbfiddle example.

Typical things you can make SQL injection safe:

Values

DECLARE @UserSuppliedValue varchar(32) = 'foo';

DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.bar
  WHERE value = @value;';
EXEC sys.sp_executesql @sql, N'@value', @UserSuppliedValue;

Entities, like table/column names

DECLARE @UserSuppliedTable  = N'dbo.bar',
        @UserSuppliedColumn = N'foo';

IF EXISTS -- make sure table exists
(
  SELECT 1 FROM sys.tables
    WHERE [object_id] = OBJECT_ID(@UserSuppliedTable)
)
AND EXISTS -- or make sure both exist
(
  SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@UserSuppliedTable)
      AND name = @UserSuppliedColumn
)
BEGIN
  DECLARE @sql nvarchar(max) = N'SELECT '
    + QUOTENAME(@UserSuppliedColumn) + N' FROM '
    + @UserSuppliedTable + N';';
  EXEC sys.sp_executesql @sql;
END

I listed both there for illustration but you would only need one or the other in most scenarios.

If you're trying to pass an entire WHERE clause to a dynamic SQL statement, there's little you can do to make that injection safe, except maybe try to execute it with PARSEONLY to make sure it's not garbage. That doesn't help prevent valid SQL injection commands but it does at least help prevent utter garbage.

DECLARE @where nvarchar(max) = N' WHERE junk;'

DECLARE @sql  nvarchar(max) = N'SELECT * FROM sys.objects ' + @where;
DECLARE @test nvarchar(max) = N'SET PARSEONLY ON;' + @sql;

DECLARE @hr int;
BEGIN TRY
  EXEC @hr = sys.sp_executesql @test;
  EXEC sys.sp_executesql @sql;
END TRY
BEGIN CATCH
  PRINT N'Statement was garbage.';
END CATCH;

If @where were the following...

DECLARE @where nvarchar(max) = N' WHERE 1=1; DROP TABLE dbo.bobby;';

...then this parsing trick doesn't work (since parsing just validates syntax, not object existence or permissions).

So you need to put proper logic into the stored procedure that produces @where and make sure there are appropriate controls, permissions, and auditing around that stored procedure, as well as reducing the permissions of the user/group executing the final @sql - this should not be sa, for example.

I've written a little about this topic:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


PostGIS ERROR: operator does not exist: postgis.geometry @ geometry I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby (2.0.0) on Rails (4.0.0) development with them.

I was able to successfully run typical operations, for example using @ to determine whether a point (postgis.geometry(Point,3785)) is in a bounding box. Most recently I took the exact points and query I was successfully running in dev and made an equivalent feature test. This is when I began noticing issues...

Whereas a point on the dev side was stored like this (SRID 3785):

POINT (-13740247.269752283 6126532.67447704)

...the test side saw it like this:

POINT (-8238307.241152702 4970299.628630948)

I wanted to post a Stack Overflow question about the issue, so I wanted the exact DB version. Whereas select postgis_full_version(); previously worked (it's still in my Terminal scrollback history), now it was returning ERROR:  function postgis_full_version() does not exist. At that point I didn't think to check the search_path, and instead I followed https://stackoverflow.com/a/8459682 -- first I tried CREATE EXTENSION postgis; but got ERROR:  extension "postgis" already exists -- and then I executed:

psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/spatial_ref_sys.sql
psql -d myapp_dev -f /Applications/Postgres93.app/Contents/MacOS/share/postgresql/contrib/postgis-2.1/postgis_comments.sql

Unfortunately those did not do the trick, and worse, now when I run my usual query I am seeing:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

How could things go so wrong? I have not yet reset my computer. I only tried restarting PostgreSQL so far. Any ideas? Thank you for reading.

Sounds to me like you have PostGIS installed in your database more than once, possibly once via CREATE EXTENSION and again via a load script, or from a dump.

The hint is that:

PG::UndefinedFunction: ERROR: operator does not exist: postgis.geometry @ geometry

mentions the schema qualifier postgis.geometry for one operand, and omits it for another. I'd say you have two copies of the the geometry type in different schemas, probably public and postgis.


Which hot backup tools for MySQL InnoDB database : Percona XtraBackup, Zmanda Recovery Manager Enterprise or Community or…? I manage a PHP/MySQL website :

max 500 simultaneous users / average of 4000 per hour in update/read
1 MySQL Community Server (5.1) - (Xeon / 16 Go RAM)
InnoDB Storage engine on 100 tables for 1.4 Go of data

Actually, every 30mn a cronjob make a full dump in file... But since several, it's cause fatal errors on PHP (because of timeout)

So I would like to have a tool to do real Hot Backup... I googled and "stackoverflowed" and it seems that " products seems to do the job :
 - Percona XTraBackup (free)
 - Zmanda Recovery Manager Community (free)
 - Zmanda Recovery Manager Enterprise (500$)

But I can't see what are really the difference and what could be the criteria. My need seems to be simple : made full (or incremental) backup to restore my database if a crash occurs without putting my website down...
I'm not a DBA so I need a "simple" product.

Many thanks for you help.

Disclaimer: I work for Percona and am one of the Percona XtraBackup developers.

You'd need to try the options and see what works best for you.

Given your needs I'd evaluate Percona XtraBackup first. It will perform the full and incremental hot backups just fine. In fact, Zmanda Recovery Manager uses Percona XtraBackup as its backend for the hot InnoDB backups.


Fetch data from same table using two group by clauses in mysql I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month.

In my table, user_type is individual and group (values I,G) with 10 entries.

4 are individual and 6 are group
2 individuals are registered in July and 2 are in August
4 group members registered in July and 2 in August.

So my output should be the following:

count of individual register in month of July ?
count of group register in month of July ?
count of individual register in month of August ?
count of group register in month of August ?

Sample table Data

usertable

+----+--------+-----------+---------------------+
| id | Name   | user_type | joined_date         |
+----+--------+-----------+---------------------+
| 1  | john   |  I        | 2014-07-16 00:00:00 |
| 2  | james  |  G        | 2014-07-11 00:00:00 |
| 3  | george |  I        | 2014-08-11 00:00:00 |
| 4  | aby    |  I        | 2014-07-11 00:00:00 |
| 5  | padma  |  G        | 2014-08-11 00:00:00 |
| 6  | Mick   |  G        | 2014-08-16 00:00:00 |
| 7  | Bony   |  G        | 2014-07-21 00:00:00 |
| 8  | Sebas  |  I        | 2014-08-11 00:00:00 |
| 9  | danie  |  G        | 2014-07-11 00:00:00 |
| 10 | davi   |  G        | 2014-07-01 00:00:00 |
+----+--------+-----------+---------------------+

expected result

+------------------+-------------+--------+
| Individual_Count | Group_Count | Month  |
+------------------+-------------+--------+
| 2                | 4           | July   |
| 2                | 2           | August |
+------------------+-------------+--------+

I need to populate This date in a Bar chart. I am bit confuse for this query for last days.Please help me if possible.
Thanks in advance

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    DATE_FORMAT(dt,'%M')       "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    DATE_FORMAT(dt,'%M') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

If you would like the counts summed up, include WITH ROLLUP

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;


ORA-01654: unable to extend index  by 128 in tablespace I am not a DBA but have to resolve this issue.

There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO tablespaces

Last week I got the exception

  ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>.

I got the tablespace size increased considerably. But still I am getting this error.

I deleted a lot of data thinking that would free-up some space. But that’s of no use.

Will shrinking the tablespace or re-sizing the datafiles of the permanent tablespace resolve this issue?

Also, the statistics hasn’t been gathered since March. Will gathering the latest statistics help me in any way?

Deleting data will not make any difference as Oracle will have kept the space ready to be re-filled. The top space usage in a table is known as the high water mark. Tom Kyte has a great post about it.

You reduce the high water mark by rebuilding the table:

alter table my_table_name move;

If you're still getting the error after increasing the size of the tablespace there are a couple of possible reasons...

The index is too big even for the newly increased tablespace. How big is the table and what are the combined size of the columns you're indexing?
You're not specifying a tablespace when creating the index and it's not using the one you think, whatever the default tablespace for your schema is.

The answer to 1 to is add more space.

To find out your default tablespace:

select * from dba_users where username = 'MY_USER'

If this is not your index tablespace you have to specify it when creating the index.

create index i_my_table
    on my_table ( my_column )
       tablespace my_index_ts
       < other options >

Yes, if you're in 9i it's definitely worth collecting statistics as it doesn't collect them automatically when enough changes have been made to the table - it does in later versions. Use DBMS_STATS.GATHER_TABLE_STATS() and only do this after rebuilding the table.

Unfortunately in Oracle you can't reduce the size of a tablespace. To use this option you'd have to re-create the tablespace, but smaller, and move everything to it.


Removing the uses of MySQL temporary tables We have a very huge PHP/MySQL application with a lot of legacy code, this application utilized MySQL for most of it's work. It does most of the processing on the database side using temporary tables, or tables that are used temporarily but are not strictly declared as TEMPORARY.

Also this application uses MySQL replication with 3 replicas for each instance, and we use write on those replicas.

In our current setup we use replication filtering to ignore those tables ,We are moving all of our application instances, which is about 15 deployments, to Amazon's RDS, and Amazon RDS does not allow setting "replicat-ignore-tables" param

So now we want to remove the usage of temporary tables, and we are checking the alternatives.

We mainly use them to process joined data from huge tables (billions of rows) and some matrix processing utilizing the use of indexes to process matrix rows.

What would be the best way to replace temporary tables usages using PHP and MySQL?

EDIT
The answer below would solve our first usage, which is direct selects.

Another usage goes as this:

We SELECT a huge amount of data from a huge table into a file using locally located.
We create a TEMPORARY table and load the data in the file into it.
Then we do some calculations and update some rows on that table in every calculation, for example we calculate the height of a building, then we update all rows with that height in the temporary table.
We then run some aggregation calculations on that table, and put the results in a final table.
We drop that table.

Sometimes i use temporary tables to create complex reports. A relatively simple (and meaningless) example could be something like

drop temporary table if exists some_temporary_table1;
create temporary table some_temporary_table1
    select t1.someColumn, max(t2.someOtherColumn) as someMax
    from some_table1 t1
    join some_table2 t2 using(someJoinColumn)
    where t1.someConditionColumn = 'someConditionValue'
    group by t1.someColumn
;

drop temporary table if exists some_temporary_table2;
create temporary table some_temporary_table2
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from some_temporary_table1 tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
;

select *
from some_temporary_table2 tmp2
join some_table3 t3 using(someDifferentJoinColumn)
where t3.someFancyConditionColumn = 'someFancyConditionValue'

Same result can be achieved without temporary tables, eliminating them step by step.

Step 1 - eliminate some_temporary_table1, replacing its occurrence with its creating code:

drop temporary table if exists some_temporary_table2;
create temporary table some_temporary_table2
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from (
        select t1.someColumn, max(t2.someOtherColumn) as someMax
        from some_table1 t1
        join some_table2 t2 using(someJoinColumn)
        where t1.someConditionColumn = 'someConditionValue'
        group by t1.someColumn
    ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax

Step 2 - eliminate some_temporary_table2

select *
from (
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from (
        select t1.someColumn, max(t2.someOtherColumn) as someMax
        from some_table1 t1
        join some_table2 t2 using(someJoinColumn)
        where t1.someConditionColumn = 'someConditionValue'
        group by t1.someColumn
    ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
) tmp2
join some_table3 t3 using(someDifferentJoinColumn)
where t3.someFancyConditionColumn = 'someFancyConditionValue'

If more temporary tables are involved, this can end up in a highly unreadable (and unmaintainable) code. To keep your code readable you can build the query in PHP using string variables for subqueries:

$subQuery1 = "
    select t1.someColumn, max(t2.someOtherColumn) as someMax
    from some_table1 t1
    join some_table2 t2 using(someJoinColumn)
    where t1.someConditionColumn = 'someConditionValue'
    group by t1.someColumn
";

$subQuery2 = "
    select tmp1.someColumn, tmp1.someMax, min(t3.someCompletelyOtherColumn) as someMin
    from ( {$subQuery1} ) tmp1
    join some_table3 t3 using(someJoinColumn)
    where t3.someOtherConditionColumn = 'someOtherConditionValue'
    group by tmp1.someColumn, tmp1.someMax
";

$mainQuery = "
    select *
    from ( {$subQuery2} ) tmp2
    join some_table3 t3 using(someDifferentJoinColumn)
    where t3.someFancyConditionColumn = 'someFancyConditionValue'
";

which is similar to the original temporary table solution.

In terms of performance - it usually doesn't make much difference.
Sometimes the temporary-tables-solution is faster.
Sometimes it's the one-statement-solution. But mostly it's not more than 10%.
In an old example i just tested, the one-statement-solution was a bit was faster,
even though i gave some hints like to use smaller data types (smallint instead of bigint),
creating indexes on temp tables to use for the next join and using a temp table twice.
In another example i remember from work, the temporary-tables-solution has been faster,
even though i didn't use any hints.


syntax error on mathematical operation postgresql I'm trying to declare this function to be used as a trigger:

create or replace function atualiza_qtfunc()
returns trigger as
$f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários
declare qt integer;
begin

select tabemissora.qtfunc into qt from tabemissora;

if (TG_OP = 'INSERT') then

UṔDATE tabemissora set qtfunc = qt + 1;

elseif (TG_OP = 'DELETE') then

UṔDATE tabemissora set qtfunc = qt - 1;

end if;

end;
$f_at_qtfunc$ language 'plpgsql'

But I'm getting:

ERROR:  syntax error at or near "UṔDATE"
LINE 12: UṔDATE tabemissora set qtfunc = qt + 1;

What am I doing wrong?

@ypercube pointed out the immediate cause for the syntax error. But there is a lot more:

CREATE OR REPLACE FUNCTION pg_temp.atualiza_qtfunc()
  RETURNS trigger AS
$f_at_qtfunc$ --f_at_func = função atualiza quantidade funcionários
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      UPDATE tabemissora SET qtfunc = qtfunc + 1
      WHERE  some_id = NEW.some_id;  -- reference NEW row
   WHEN 'DELETE' THEN
      UPDATE tabemissora SET qtfunc = qtfunc - 1
      WHERE  some_id = OLD.some_id;  -- reference OLD row
   END CASE;
END
$f_at_qtfunc$ LANGUAGE plpgsql;

Remove the SELECT completely, you can refer to the column itself in the UPDATE statement directly.
Consequently, remove the needless variable declaration altogether.
Don't UPDATE without WHERE condition, that would alter all rows in the table, which is most probably not what you want. io made the command depend on the inserted / deleted row at hand.
I used CASE instead of IF to simplify. Optional.
Don't quote the language name.

Even with the fixed function, your whole approach may be a bad idea in a multi-user environment. Multiple concurrent inserts / deletes for the same parent-ID can produce race conditions that this kind of function is not able to resolve. If that can happen, you'd rather do the count after the fact in a view or materialized view and drop the trigger altogether.


SQL Query for the following structure Given the following type of data structure.

    ID  NAME  TREELEFT  TREERIGHT  PARENT
    1   XYZ       1         2         0
    2   ABC       3         6         0
    3   DEF       4         5         2
    4   HIJ       7         10        0
    5   KLM       8         9         4
    6   NOP       11        16        0
    7   QRS       12        15        6
    8   TUV       13        14        7

The methodology is more or less identical to the nested set model except I've included a parent, just to make my life a little easier. (https://en.wikipedia.org/wiki/Nested_set_model#Example) I need to come up with a way of detecting gaps. For example if ID=6 had a different treeright say 17. There would technically be a gap of 1.

For the moment I've done this in a series of different queries.

One to detect if treeright doesn't exceed 2n, another to detect if treeright is always > than treeleft. Another couple to ensure that no treelefts or treerights are identical to each other.

However if I could add just check to ensure that the tree had no gaps I'm sure a couple of the other tests would become redundant. Can anyone think of a good way to do this. I've been racking my brain on this one for quite a long period of time.

It took me a while to figure this one out.

        SELECT  t1.id      + 1 AS `start`,
                MIN(t3.id) - 1 AS `stop`
          FROM (SELECT  table1.id
                  FROM (SELECT treeleft  AS id FROM structure
                        UNION ALL
                        SELECT treeright AS id FROM structure
                       )
                    AS  table1)
             AS t1
LEFT OUTER JOIN (SELECT  table2.id
                   FROM (SELECT treeleft  AS id FROM structure
                         UNION ALL
                         SELECT treeright AS id FROM structure
                        )
                     AS  table2)
             AS  t2
             ON  t1.id = t2.id - 1
LEFT OUTER JOIN (SELECT  table3.id
                   FROM (SELECT treeleft  AS id FROM structure
                         UNION ALL
                         SELECT treeright AS id FROM structure
                        )
                     AS  table3)
             AS  t3
             ON  t1.id < t3.id
          WHERE  t2.id IS NULL
            AND  t3.id IS NOT NULL
       GROUP BY  t1.id,
                 t2.id;


How to INSERT into mySQL without buffering, or force immediate buffer commit? I have a script I am running that will allow the user to add test junk to the mySQL DB for testing backups. The script offers the choice to add records of size X until the DB reaches a target size. The script works, but always overshoots the target size because when you get the DB size

SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) \"DB Size in MB\"
FROM information_schema.tables
WHERE table_schema='dbName'
GROUP BY table_schema;

it doesn't seem to account for the buffer. So the script eventually detects I've reached the target size, but only when the buffer happens to flush (during debugging, I added 1MB text blocks one at a time, and the DB size reported as 2.5MB a few hundred times before jumping up to ~450MB).

I did try to FLUSH TABLES between each insert, but this just made the DB size constantly report as 0.0 even with 7000+ rows.

How can I ensure I am getting an accurate DB size after each insert (preferably without disabling innodb for the whole DB or restarting mysql)?

Edit: I am using TEXT for the data columns.
COMMIT before pulling DB size doesn't work (still wind up at ~450MB).
SQL_NO_CACHE on SELECT statement above doesn't work (still wind up at ~450MB).

Plan A:

Shrink innodb_buffer_pool_size so as to force flushing to disk sooner.

Be cautious -- too small (a few MB) a buffer_pool may lead to a hang or crash.
There is still some caching, so you still cannot get precisely what you want.

Plan B:

Insert 1000 rows.
Shutdown MySQL (only this once)
Do the math.
Extrapolate to see how many rows to insert.
Finish to the computed limit.

Caveat:  The math won't be quite right, so you might under/overshoot.

Plan C:

As with Plan B, but do a second shutdown and extrapolation after inserting half the number of rows computed from Plan B.  This should give you a much more accurate target.

Plan D:

From my experience, InnoDB tables are usually 2x to 3x larger than MyISAM tables with the same schema and same data.  Since MyISAM table size is rather easy to compute, will doing that, then multiplying by 2 to 3 be good enough?

If all columns are TEXT, that is a pretty messy schema.  The storage of data will be compromised.  SELECTs will have to work harder, especially for range tests on numeric values stored in TEXT columns.


Many columns vs few tables - performance wise Yes, I am aware that data normalization should be my priority (as it is).

I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth,  in total 65.
Now, I can divide that and have a Vehicle table, VehicleInterior, VehicleExterior, VehicleTechnical, VehicleExtra (all one-to-one with main Vehicle table).

Let's assume I'll have about 5 million rows (vehicles).
On SELECT with a WHERE clause: Will the performance be better searching through (both cases indexed at least on IDs):

Vehicle table with 65 columns or
Vehicle table with JOINS on four other tables (all with 5 million rows) to return all the data related to Vehicle?

(As per database engine, consider PostgreSQL and/or MySQL).
Truly appreciate any detailed insights you might have from your previous experience?
Updates will be rare if any, and selects will be mostly for all columns (vehicle details page) and main info (few columns) for search results list, and in fact maybe the best solution would be two tables: one with main info (few columns) and the other table with rest of the columns.

Assuming we are talking about 1:1 relationships among all tables.

Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate (redundant) index on each of these columns ... Size does matter for performance.

This is even true if many columns are NULL in most rows because NULL storage is very cheap:

Configuring PostgreSQL for read performance

While retrieving all columns a single table is substantially faster than 5 tables joined together. It's also much simpler. Five tables may be tricky to join if not all rows are present in all tables. With WHERE conditions targeting a single table, it's easy enough to append other tables with LEFT JOIN. Not as trivial if you have predicates on multiple tables ...

Vertical partitioning may still improve performance of certain queries. For example, if 90 % of your queries retrieve the same 5 columns out of the 65 available, this would be faster with a table just holding these 5 columns.

What is retrieved from disk during a query? (my answer)

OTOH, you might be able to cater for such queries on a few selected columns with a "covering" index allowing for index-only scans.

Another candidate for vertical partitioning: If you have lots of updates on just a few columns, while the rest hardly ever changes. It might be considerably cheaper to split rows in such a case, since Postgres writes a new row version for every update. There are exceptions for big values stored out-of-line ("TOASTed"). More details:

What is retrieved from disk during a query? (Daniel's answer)
Update all columns from another table

It really depends on the complete situation. If in doubt, go with the simple solution of having a single table, especially if it portraits reality well: In your example, those are all attributes of a car and make sense together.


SQL Server chooses unselective index I was testing SQL Server indexes and found very strange behavior. Here is my code:

DROP TABLE IF EXISTS  dbo._Test
DROP TABLE IF EXISTS  dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL,
UserSystemID INT NOT NULL,
Age INT
)
GO
INSERT INTO dbo._Test
        ( ID, UserSystemID, Age )

SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns
CROSS JOIN sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

; WITH cte AS (
SELECT ID, UserSystemID,  age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn
FROM dbo._Test
)

SELECT cte.ID ,
       cte.UserSystemID ,
       cte.Age
INTO _newTest
FROM cte
WHERE cte.rn = 1

CREATE UNIQUE NONCLUSTERED INDEX  IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age)
GO
ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID)
GO

At this point, I have two indexes on the same table and on the same columns. First one is nonclustered and the second one is clustered. The Id column is more selective (about 5000000 unique values) and UserSystemID is not (two unique values).

Then I run the following query to test which index is used:

SELECT id,  UserSystemID, age
FROM _NewTest
WHERE id = 1502945
AND UserSystemID = 1

It seeks the clustered index. You can see the plan here.

The question is why SQL Server prefers the clustered index instead of the unique nonclustered index.

My leading column of clustered index is much less selective than the other unique nonclustered index. So I expect that the performance must be worse with clustered index but in practice it is not.

Given the unique indexes, your query will select at most one row.

The optimizer knows it will need to descend the index b-tree just once, and will not need to scan forward or backward from that point to find more matches. This is known as a singleton seek (equality test on a unique index).

The current index matching implementation happens to always choose the clustered index when it can use a singleton seek.

The choice between clustered and nonclustered index here is generally not very important.
There might be a tiny extra cost as the upper levels of the b-tree are navigated (using binary search or linear interpolation) but this would be challenging to even measure. Remember only the ID and UserSystemID key components are present on non-leaf index pages.

One could argue that wider clustered index leaf pages are less likely to be in memory on average. There are a few other edge case consequences, but I don't see this behaviour being changed any time soon.

  But my leading column of clustered index is much less selective than the other unique nonclustered index. So I expect that the performance must be worse with clustered index but in practice it is not.

The selectivity does not matter for equality seek on a compound b-tree index.

Your unique clustered compound index has keys (UserSystemID, id).

To find a row with (UserSystemID = 1 and id = 1502945), SQL Server does not find all rows where UserSystemID = 1, then find rows where id = 1502945. That would be very inefficient.

You can tell how many pages your test query touches using SET STATISTICS IO ON. Your example builds a clustered index with two non-leaf levels. In total, finding the row you want means touching three pages - one at each level of the index.

Rows are ordered in the index by UserSystemID and id. My copy of your demo table has the following layout on the root (top level) page of the clustered index:

Performing a binary search on this page is easy:

Start at the middle row.
Compare the UserSystemID with the one you are looking for.

If it is not equal, continue the binary search in the usual way (choose a new midpoint in the earlier or later rows as appropriate).
If equal on UserSystemID, Compare the id with the one you are looking for, and continue the binary search

Following that logic, we will quickly find the child (next lower level) index page the searched keys will be found on if they are present. Repeat the binary search on that page, and so on until we reach the single leaf-level page that must contain the row we are looking for, if it exists.


How to apply ORDER BY and LIMIT in combination with an aggregate function? A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.

I have a simple table layout:

class
person: belongs to a class

I want to select all classes, and for each class, I want the first two person identifiers of the belonging persons sorted by descending name.

I solved this with the following query:

select     c.identifier, array_agg(p.identifier order by p.name desc) as persons
from       class as c
left join lateral (
             select   p.identifier, p.name
             from     person as p
             where    p.class_identifier = c.identifier
             order by p.name desc
             limit    2
           ) as p
on         true
group by   c.identifier
order by   c.identifier

Note: I could have used a correlation subquery in the SELECT clause, but I am trying to avoid that as part of a learning process.

As you can see, I am applying order by p.name desc in two places:

in the subquery
in the aggregate function

Is there a way to avoid that? My train of tought:

First, obviously I cannot remove the order by in the subquery, as that would give a query which does not meet my requirement as stated above.
Second, I think that the order by in the aggregate function cannot be left out, as row order of the subquery is not necessarily preserved in the aggregate function?

Should I rewrite the query?

I am applying order by p.name desc in two places ... Is there a way to avoid that?

Yes. Aggregate with an ARRAY constructor in the lateral subquery directly:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) AS persons
   ) p
ORDER  BY c.identifier;

You also don't need GROUP BY in the outer SELECT this way. Shorter, cleaner, faster.

I replaced the LEFT JOIN with a plain CROSS JOIN since the ARRAY constructor always returns exactly 1 row. (Like you pointed out in a comment.)

db<>fiddle here.

Related:

Preserve order of array elements after join

Order of rows in subqueries

To address your comment:

  I learned that order of rows in a subquery is never guaranteed to be preserved in the outer query.

Well, no. While the SQL standard does not offer any guarantees, there are limited guarantees in Postgres. The manual:

  This ordering is unspecified by default, but can be controlled by
  writing an ORDER BY clause within the aggregate call, as shown in
  Section 4.2.7. Alternatively, supplying the input values from a sorted
  subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

  Beware that this approach can fail if the outer query level contains
  additional processing, such as a join, because that might cause the
  subquery's output to be reordered before the aggregate is computed.

If all you do in the next level is to aggregate rows, the order is positively guaranteed. Any yes, what we feed to the ARRAY constructor is a subquery, too. That's not the point. It would work with array_agg() as well:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT array_agg(identifier) AS persons
   FROM  (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) sub
   ) p
ORDER  BY c.identifier;

But I expect the ARRAY constructor to be faster for the case. See:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?
convert right side of join of many to many into array


How to add ansi null and ansi warning in connection string Using RDO Connection

Connection String

Set RdoVisPay = rdoVISENV.OpenConnection("VISA", 1, False, "DSN=VISA;UID=sa;PWD=sa;APP=Visual Basic;WSID=" & ServerName & ";DATABASE=" & Database1)

Showing error as "heterogeneous queries require the ansi_nulls and ansi_warnings options to be set for the connection"

How to add the ansi_nulls and ansi_warning Connection in the connection string.

Need Suggestion and Help.

You can't. They are set per connection, but not in the connection string. So run some SQL after creating the connections

SET ANSI_NULLS, ANSI_WARNINGS ON;

Note though, the error you have is caused by a linked server call from your SQL Server. Depending on how you set this up (say via a stored procedure) you may need to change the stored proc or view, or set the values in the code. Hard to say as it stands.


How to overwrite csv file of MySQL result set? My SQL query is given below,

select company.Company_name as COMPANY_NAME,
IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE,
IFNULL((select Full_name from user where user_id = followup.user_id), ' ') AS Employee,
IFNULL(fix_appoinment_order.appointment_dt, ' ') AS APPOINTMENT_DATE,
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_tme), ' ') as TELEMARKETING_EXECUTIVE,
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_me), ' ') as MARKETING_EXECUTIVE
from company_order
inner join company on company.Company_id = company_order.Company_id
left join company_history on company_order.Compordr_id = company_history.compordr_id
left join followup on followup.followup_id = company_history.followup_id
left join fix_appoinment_order on fix_appoinment_order.fix_appointment_order_id = company_history.fix_appointment_order_id
where
followup.contact_date = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d") OR
fix_appoinment_order.appointment_dt = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d")
INTO OUTFILE '/var/www/backup/AppandFllups.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I know that, the query working fine, I have to set the cron job to get the result set as csv file and send email.

At first time when I execute the query, there is no issue, but next time I got the error as follows,

Error Code: 1086. File '/var/www/backup/AppandFllups.csv' already exists

I understood, the file is already created while I run at first time, but I am unable solve this issue. I used another cron job to fix this issue but it I am not satisfied, is there anyway to do achieve this?

According to the MySQL Documentation on SELECT ... INTO

  The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. The character_set_filesystem system variable controls the interpretation of the file name.

I suppose the easiest way would be the following:

STEP #1

Change this line in the query

INTO OUTFILE '/var/www/backup/AppandFllups.csv'

to this

INTO OUTFILE '/var/www/backup/AppandFllups.tmp'

STEP #2

Add these lines to the cronjob just after the dump

cd /var/www/backup
mv AppandFllups.tmp AppandFllups.csv

The main idea is to have /var/www/backup/AppandFllups.tmp be nonexistent just before the dump. The output file gets renamed to something you already use elsewhere. So, there is no need to change other processes or scripts.

Give it a Try !!!


Stored Procedure Processing and Error Log I used to work for a company where they had this 3rd party Data warehouse Solution. Obviously all the objects and tables were hidden in the Supporting database so I don't have clear idea on what exactly happened inside some of the stored procedure. I saw this interesting stored procedure there and want to replicate that in my own solution but I just can't get my head around on how it works.  I am describing the stored procedure below and it will be really helpful if someone can give me some ideas on how to achieve this. Even better if you can suggest me how I can make this even better.

Stored procedure was called process log.  It had Parameters like DBID, ObjectId, Step, Status, Remarks, Reads, Inserts, Updates, Deletes

What we had to do was, inside every stored procedure, we have to execute this stored procedure with Status as 2 (In Progress)  During the process where can execute this stored procedure multiple time at the end of each step or section after increasing the value of variable step. based on the row counts of Insert update select and delete statements we were supposed to record values in respective stored procedure parameter variables.
In the end you can execute same stored procedure with Status of 3 (Completed)  or if the procedure ended up in catch block, status will be 4 (Failed) in the remarks section we can copy SQL's error messages.

To see all this information, we were given access to a Report, For which obviously I didn't have the source code but the report showed what time stored procedure started when it finished, what was the status how many inserts updates deletes and reads it did. if it failed, what was the error message?

I already have few ideas for improvements to store, who started it?, what were the values of parameters? For who started the stored procedure part, I have a confusion. Most of these stored procedure run as part of different Jobs. All our jobs run as a Service Account user but jobs are started manually by various users. I need to find out which user started it, as inside stored procedure, as current user, it will always show service account. Also for Values of Paramters, is there a better dynamic way of finding this out? rather then setting a value for a variable manually. I was hopping to use output from INPUTBUFFER but it only shows the name of the parameter not values.

If someone can guide me regarding the back end table structure and script for this auditing SP, it will be really helpful. Also any more ideas for improvement are welcome as well.

My main confusion:
I believe they had some table where these stored procedure values were stored and if the SP is running already, they did update in the record then doing an  Insert but how would they identify to do an insert instead of update in the scenario where stored procedure failed critically and catch block wasn't executed.

Here is a structure that is at least very close.

There is no programmatic way to get the parameters (unfortunately). You need to format them into XML to pass in.

The Login that initiates a SQL Agent job seems to only be logged in the message column of msdb.dbo.sysjobhistory, for step_id = 0. This value can be extracted, but not during the execution of the job.

You get ObjectID to pass in from @@PROCID.

Below is the schema (2 tables) and stored procedures (3 procs). The concept is to separate "init", "in process", and "completed (success or error)" logging. This allows for setting certain columns only at the appropriate time (e.g. only need to set DatabaseID, StartedAt, etc at the very beginning).  Separating the type of event also makes it easier to have event-specific logic (yes, can have it even in a single proc, but then you still have all the input parameters when you only need a subset per each event-type).

A "process" record get updated via its IDENTITY (and clustered PK) value. This is another benefit of having the "event type" separation: it makes it much easier to handle capturing the SCOPE_IDENTITY() and passing it back to be used for the two other logging stored procedures. If a stored procedure fails and doesn't go to the CATCH block, then there is no need to worry about accidentally updating that process record as the next time any stored procedure (that is being logged) starts, it will get a new / unique ID to update.

Cleanup (optional) and Schema

/* -- optional cleanup
DROP PROCEDURE [dbo].[ProcessLogDemo];

DROP PROCEDURE [Logging].[ProcessLog_Log];
DROP PROCEDURE [Logging].[ProcessLog_Start];
DROP PROCEDURE [Logging].[ProcessLog_Stop];

DROP TABLE [Logging].[ProcessLog];
DROP TABLE Logging.[Status];

DROP SCHEMA [Logging];
*/

CREATE SCHEMA [Logging];
GO

Tables and Indexes

CREATE TABLE Logging.[Status]
(
  [StatusID] TINYINT NOT NULL
              CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED,
  [StatusName] VARCHAR(50) NOT NULL
);

CREATE TABLE [Logging].[ProcessLog]
(
  ProcessLogID  INT NOT NULL IDENTITY(-2147483648, 1) -- start at INT min value
                 CONSTRAINT [PK_ProcessLog] PRIMARY KEY CLUSTERED,
  DatabaseID INT NOT NULL,
  ObjectID INT NULL, -- NULL = ad hoc query
  SessionID SMALLINT NOT NULL
             CONSTRAINT [DF_ProcessLog_SessionID] DEFAULT (@@SPID),
  Step TINYINT NOT NULL, -- if you have more than 255 steps, consult psychiatrist
  StatusID TINYINT NOT NULL
            CONSTRAINT [FK_ProcessLog_Status]
                FOREIGN KEY REFERENCES [Logging].[Status]([StatusID]),
  Remarks NVARCHAR(MAX) NULL, -- or maybe VARCHAR(MAX)?
  Params XML NULL,
  RowsSelected INT NULL,
  RowsInserted INT NULL,
  RowsUpdated INT NULL,
  RowsDeleted INT NULL,
  StartedBy [sysname] NULL,
  StartedAt DATETIME2 NOT NULL
             CONSTRAINT [DF_ProcessLog_StartedAt] DEFAULT (SYSDATETIME()),
  UpdatedAt DATETIME2 NULL, -- use to show progress / "heartbeat"
  StoppedAt DATETIME2 NULL
);
GO

Stored Procedure to call at the very beginning of "logged" stored procedures

CREATE PROCEDURE [Logging].[ProcessLog_Start]
(
  @DatabaseID INT,
  @ObjectID INT,
  @Params XML,
  @ProcessLogID INT = NULL OUTPUT
)
AS
SET NOCOUNT ON;

-- First, capture the MAX "instance_id" from sysjobhistory if this process is a SQL
-- Server Agent job (use later to get the "invoked by" Login), else grab the Login.
DECLARE @StartedBy [sysname];

IF (EXISTS(
           SELECT *
           FROM   sys.dm_exec_sessions sdes
           WHERE  sdes.[session_id] = @@SPID
           AND    sdes.[program_name] LIKE N'SQLAgent - TSQL JobStep (%'))
BEGIN
  DECLARE @JobID UNIQUEIDENTIFIER;

  SELECT @JobID = CONVERT(UNIQUEIDENTIFIER,
                           CONVERT(BINARY(16),
                                   SUBSTRING(sdes.[program_name],
                                        CHARINDEX(N'(Job 0x', sdes.[program_name]) + 5,
                                             34), 1
                                  )
                          )
  FROM  sys.dm_exec_sessions sdes
  WHERE sdes.[session_id] = @@SPID;

--SELECT @JobID;

  SELECT @StartedBy = N'sysjobhistory.instance_id: '
                       + CONVERT(NVARCHAR(20), MAX(sjh.[instance_id]))
  FROM   msdb.dbo.sysjobhistory sjh
  WHERE  sjh.[job_id] = @JobID;
END;
ELSE
BEGIN
  SET @StartedBy = ORIGINAL_LOGIN();
END;

-- Now it should be safe to create a new entry
INSERT INTO [Logging].[ProcessLog] ([DatabaseID], [ObjectID], [Step], [StatusID],
                                    [Params], [StartedBy])
VALUES (@DatabaseID, @ObjectID, 0, 1, @Params, @StartedBy);

SET @ProcessLogID = SCOPE_IDENTITY();
GO

Stored Procedure to call after all but the final step

CREATE PROCEDURE [Logging].[ProcessLog_Log]
(
  @ProcessLogID INT,
  @Step TINYINT,
  @RowsSelected INT = NULL,
  @RowsInserted INT = NULL,
  @RowsUpdated INT = NULL,
  @RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;

UPDATE pl
SET    pl.[StatusID] = 2, -- In process
       pl.[Step] = @Step,
       pl.[UpdatedAt] = SYSDATETIME(),
       pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
       pl.[RowsInserted] = ISNULL(@RowsInserted, pl.[RowsInserted]),
       pl.[RowsUpdated] = ISNULL(@RowsUpdated, pl.[RowsUpdated]),
       pl.[RowsDeleted] = ISNULL(@RowsDeleted, pl.[RowsDeleted])
FROM   [Logging].[ProcessLog] pl
WHERE  pl.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
 RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
           @ProcessLogID);
  RETURN;
END;
GO

Stored Procedure to call after the final step and/or in a CATCH block

CREATE PROCEDURE [Logging].[ProcessLog_Stop]
(
  @ProcessLogID INT,
  @Step TINYINT,
  @StatusID TINYINT,
  @Remarks NVARCHAR(MAX) = NULL,
  @RowsSelected INT = NULL,
  @RowsInserted INT = NULL,
  @RowsUpdated INT = NULL,
  @RowsDeleted INT = NULL
)
AS
SET NOCOUNT ON;

UPDATE pl
SET    pl.[StatusID] = @StatusID, -- 3 = Success, 4 = Fail
       pl.[Step] = @Step,
       pl.[Remarks] = @Remarks,
       pl.[StoppedAt] = SYSDATETIME(),
       pl.[RowsSelected] = ISNULL(@RowsSelected, pl.[RowsSelected]),
       pl.[RowsInserted] = ISNULL(@RowsSelected, pl.[RowsInserted]),
       pl.[RowsUpdated] = ISNULL(@RowsSelected, pl.[RowsUpdated]),
       pl.[RowsDeleted] = ISNULL(@RowsSelected, pl.[RowsDeleted])
FROM   [Logging].[ProcessLog] pl
WHERE  pl.[ProcessLogID] = @ProcessLogID;

IF (@@ROWCOUNT = 0)
BEGIN
 RAISERROR('No initial or in-process record for ProcessLogID = %d !', 16, 1,
           @ProcessLogID);
  RETURN;
END;
GO

Demo stored procedure (input parameters are formatted as XML)

The reason for putting "StepNumber" in a variable is so that the value can get passed to the CATCH block. The @StepNumber variable is incremented prior to each operation. If the operation succeeds, that value is used to call the "Log" stored procedure that captures the number of rows affected for that step and the time it was called. If the operation fails, that same @StepNumber value is used to call the "Stop" stored procedure that marks the process as "failed" and passes in the error message. This makes the data less confusing since the Step column on failed records will be the step it was actually working on when the error occurred.

CREATE PROCEDURE [dbo].[ProcessLogDemo]
(
  @Param1 INT,
  @Param2 DATETIME,
  @Param3 NVARCHAR(50) = NULL
)
AS
SET NOCOUNT ON;

DECLARE @ProcessID INT,
        @DB_ID INT = DB_ID(),
        @Params XML,
        @StepNumber TINYINT;

SET @Params = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3]
   FOR XML PATH(N'Params')
); -- missing elements mean the value == NULL
--SELECT @Params;

BEGIN TRY

  EXEC [Logging].[ProcessLog_Start]
    @DatabaseID = @DB_ID,
    @ObjectID = @@PROCID,
    @Params = @Params,
    @ProcessLogID = @ProcessID OUTPUT;

  SET @StepNumber = 1;

  -- do something

  EXEC [Logging].[ProcessLog_Log]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @RowsSelected = @@ROWCOUNT;

  SET @StepNumber = 2;

  -- do something else

  EXEC [Logging].[ProcessLog_Log]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @RowsUpdated = @@ROWCOUNT;

  SET @StepNumber = 3;

  -- do final thingy

  EXEC [Logging].[ProcessLog_Stop]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @StatusID = 3, -- success
    @RowsInserted = @@ROWCOUNT;

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();

  EXEC [Logging].[ProcessLog_Stop]
    @ProcessLogID = @ProcessID,
    @Step = @StepNumber,
    @StatusID = 4, -- fail
    @Remarks = @ErrorMessage;
END CATCH;
GO

NOTES:

With regards to getting the "invoked by" Login for SQL Server Agent jobs: the step_id = 0 record (which is the only place that this info exists) does not exist until the job completes (success or failure). Hence, it isn't available while the stored procedure is running, let alone at the beginning. For now we capture the MAX(sjh.[instance_id]) FROM msdb.dbo.sysjobhistory sjh for the currently executing job for the current session. Later (i.e. after the job completes), that can get replaced with the job invoker Login.
I would generally recommend against adding this type of logging to Stored Procedures that are executed very frequently as the additional read and write operations will have a negative impact on performance.

ADDENDUM

Here is an Inline Table-Valued Function (ITVF) to get the job outcome info (including the "invoked by" user or schedule or whatever) based on the instance_id value that was captured into the ProcessLog.StartedBy column. The instance_id value returned in the result set is the row for step_id = 0.

CREATE FUNCTION dbo.GetSqlServerAgentJobOutcome
(
  @InstanceID INT
)
RETURNS TABLE
AS RETURN

WITH cte AS
(
  SELECT TOP (1)
         sjh.[instance_id],
         sjh.job_id,
         sjh.[message],
         sjh.[run_date],
         sjh.[run_time],
         sjh.[run_duration],
         sjh.[run_status],
         sjh.[sql_message_id],
         sjh.[sql_severity],
         (CHARINDEX(N' was invoked by ', sjh.[message]) + 16) AS [invoker_begin],
         CHARINDEX(N'.  The last step to run', sjh.[message]) AS [invoker_end]
  FROM   msdb.dbo.sysjobhistory  sjh
  WHERE  sjh.[job_id] = (SELECT sjh2.[job_id]
                         FROM   msdb.dbo.sysjobhistory sjh2
                         WHERE  sjh2.[instance_id] = @InstanceID)
  AND    sjh.[step_id] = 0
  AND    sjh.[instance_id] >= @InstanceID
  ORDER BY instance_id ASC
)
SELECT [instance_id], [job_id],
       --[message],
       [run_date], [run_time],
       [run_duration], [run_status],
       [sql_message_id], [sql_severity],
       SUBSTRING([message], invoker_begin, ([invoker_end] - [invoker_begin]))
          AS [InvokedBy]
FROM   cte;
GO