Mongodb sharded cluster backup user issue We're using a simple python script to backup our mongodb cluster. the script connects to each mongod instance via ssh, and performs mongodump, all without requiring user/pass

ever since we started using the mms monitoring agent, and thus creating an administrative user on each shard for the mms agent to connect to, we're not able to use the mongodump without credentials.

out problem is that all our users are configured on the mongos instances and not directly on the shards, so we cannot perform the mongodump.

One last thing, Is it possible to disable authentication for a specific shard in order to create/remove users from it?

do you have any ideas?

Once you add a user to the individual shards, which you indicate you have done for MMS, you must then have valid credentials to connect for any purpose, including mongodump.  Up until you added that user for MMS, the shards were running with authentication enabled but with no users populated (this only happens if all your users are in the admin database ans using delegated auth for other databases, otherwise with 2.4 and below you would have at least one shard with users for each database - 2.6+ changes this behavior) and so you were able to connect without credentials.

Essentially this is a loophole left open so that you don't accidentally lock yourself out of your instances when you turn on auth with no users (and one that would probably have stopped working at some point as default security is tightened anyway).

The bottom line is that you will need to add a user for use with mongodump, and it's a good idea to do so anyway rather than allowing non-authenticated users free access to your instances.  If you are running 2.6 or later, then the built in backup role exists precisely for this purpose, if you are on 2.4 or earlier, then the description for that role gives you a great outline of what is needed to backup successfully (and in particular if you want to backup the users themselves).


How to design a database for query builder project? I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store this in database.

I can store query in table as simple string but then editing will be not possible so I need to store it some other way. So I somehow managed to design it following way.

So let's say I have to store this query:

C1 > 8 AND (C2 <= 7 OR (C4 LIKE '%all%' AND (C1 > 15 OR C2 <= 3)))

where: C denotes some column

If I have to store it in DB as shown in image,

I would group each condition and store it in sub_operand table
then there will be recursive mapping entry in op_master table for each entry in sub_operand table
finally there will be master entry in op_master

But it seems too much complicated to handle insert and update. Can someone help me with this? I am very much stuck here.

UPDATE: I think I am missing something here in schema. It won't work as I have thought. I will update question as soon as I can correct it.

One approach you could take is to design a table that implements a stack into which can be placed the components of the logical expression in reverse-polish (postfix) notation.  In such a design, the components of the predicate are each inserted into the table with an order number to specify the reverse evaluation order.  To evaluate them a query is written to read them out by order number, apply the operators when encounter, and store the intermediate result to be applied the next.  The use of the reverse-polish notation eliminates the need for parenthesis and can support nesting of expressions to any level.

Here is an article specific to SQL Server that shows such a solution. Now in this article the author is developing a more complex example of implementing business rules to determine whether an employee is authorized to execute a particular operation.  It can easily be adapted to your use case as in both cases what is being implemented is a logical expression storage and evaluation engine.

I hope this can point you in a direction that will enable you to more easily handle the complexity that parsing nested logical expressions bring!


PostgreSQL and days that don't exist? While updating data in my table i am getting below error.

 date/time field value out of range: "2017-2-29"

I want to skip this error and want update statement to continue further.

How can we do this with update statement.

February has 28 days.

February in 2017 doesn't have 29 days. It has 28. End of story. If you need to store a 29th day, then your definition of date is different than PostgreSQL's and it will not be able to do date math or use date functions with your definition.

What is '2017-02-29'::date + interval '1 day' in your system though? February 30, March 1, or March 2? And, what would '2017-02-29'::date + interval '1 day' - interval '1 day' be?


Trigger not functioning correctly in MYSQL I am trying to set up a trigger to get it so if a new entry is either null or set to '000-00-00 00:00:00', that it will set the current datetime (NOW()).

I can get the trigger to set the datetime if I don't check for null or other value, but if I do, it doesn't fire.

I have tried the following and they are not working:

delimiter // CREATE TRIGGER applicationsolution_OnInsert BEFORE UPDATE ON applicationsolution FOR EACH ROW BEGIN IF NEW.created = '0000-00-00 00:00:00' THEN SET NEW.created = NOW(); END IF; END;// delimiter ;

CREATE TRIGGER applicationsolution_OnInsert BEFORE INSERT ON applicationsolution FOR EACH ROW SET NEW.created = IFNULL(NEW.created, NOW());

Try this code:

DROP TABLE applicationsolution;
CREATE TABLE applicationsolution (myid INTEGER AUTO_INCREMENT PRIMARY KEY,
                                  created DATETIME);

Sample table.

delimiter //
CREATE TRIGGER applicationsolution_OnInsert
BEFORE INSERT ON applicationsolution
FOR EACH ROW
BEGIN
  IF NEW.created IS NULL
    THEN SET NEW.created = CURRENT_TIMESTAMP();
  END IF;
END //
delimiter ;

Trigger - which works - see below.

INSERT INTO applicationsolution (created) VALUES (NULL);
INSERT INTO applicationsolution (created) VALUES (NULL);

Sample data insert - will produce two records.

SELECT * FROM applicationsolution;

myid,created
==== =======
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:50:40"

So far, so good for NULLs. Now for the UPDATE trigger:

delimiter //
CREATE TRIGGER applicationsolution_OnUpdate
BEFORE UPDATE ON applicationsolution
FOR EACH ROW
BEGIN
  IF NEW.created = '0000-00-00 00:00:00'
    THEN SET NEW.created = current_timestamp();
  END IF;
END//
delimiter ;

Now do an update.

UPDATE applicationsolution  SET created = '0000-00-00 00:00:00'
WHERE myid = 2;

SELECT * FROM applicationsolution;

And the results:

myid,created
1,"2016-06-24 21:50:40"
2,"2016-06-24 21:54:30"

Note the timestamp change in record 2 which shows that if you try to INSERT a created of '0000-00-00 00:00:00', you get a CURRENT_TIMESTAMP.

Does this do what you want? I think there are two problems with your code. It's not formatted properly - this made it very difficult to follow the logic and difficult to see that the second trigger was incomplete. You also put the INSERT code in the ON UPDATE trigger and vice-versa.

Furthermore, CURRENT_TIMESTAMP seems to work better than NOW().


Is it a parameter sniffing problem? I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it:

    ALTER PROCEDURE [dbo].[sp_Update_StaffTable]
        @OldStaffId nvarchar(100)
       ,@NewStaffId nvarchar(100)
       ,@FirstName  nvarchar(100)
       ,@LastName nvarchar(100)
       ,@Description nvarchar(100)
       ,@WorkPhone nvarchar(100)
       ,@Photo varbinary(MAX)
       ,@UserType nvarchar(100)
AS
BEGIN

SET NOCOUNT ON;
if EXISTS(SELECT [StaffId] FROM [dbo].[StaffTable_Temp]  WHERE [StaffId] = @OldStaffId)
    BEGIN
        UPDATE [dbo].[StaffTable_Temp]
           SET [FirstName] = @FirstName
              ,[LastName] = @LastName
              ,[Description] = @Description
              ,[WorkPhone]  = @WorkPhone
              ,[Photo] = @Photo
              ,[UserType] = @UserType
              ,[StaffId] = @NewStaffId
         WHERE [StaffId] = @OldStaffId;
    END
END

The console application calls this sp and gets intermittent timeout issue when it updates the first record (we update for the whole table which contains around ~5k items).

I don't think 'parameter sniffing' is the root cause as the estimated number of rows is always = '1' for all scenarios, is it correct?

any help would be much appreciated

If it's the first UPDATE that times-out then I'd look closer at your physical reads.

If [staffid] isn't indexed then you'll be performing a full table scan and pulling the entire table from the IO subsystem through to the buffer.

After the first UPDATE most of the table's pages will be in the buffer and subsequent UPDATES will be able to perform logical reads for the EXISTS part of the SP.


Error: Specified cast is not valid. (SqlManagerUI) I am trying to restore database created on server to my localbox however i am getting 'Error: Specified cast is not valid. (SqlManagerUI)' error message.

Server Info:
Windows Server 2008 Standard 64 bit
Microsoft SQL Server 2008: (Info obtained from SSMS About page)

    Microsoft SQL Server Management Studio      10.0.4064.0
Microsoft Analysis Services Client Tools        10.0.4064.0
Microsoft Data Access Components (MDAC)         6.0.6002.18005
Microsoft MSXML                                 3.0 6.0
Microsoft Internet Explorer                     8.0.6001.19190
Microsoft .NET Framework                        2.0.50727.4216
Operating System                                6.0.6002

My Localbox:
Windows 7 64bit
Microsoft SQL Server 2008:

Microsoft SQL Server Management Studio          10.0.4064.0
Microsoft Analysis Services Client Tools        10.0.4064.0
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                                 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                     9.0.8112.16421
Microsoft .NET Framework                        2.0.50727.5448
Operating System                                6.1.7601

Does anyone know how to fix this issue?

Sanjeev

Generate the restore script from the GUI, instead of using the GUI to do the restore.

Then add the PASSWORD option...

--generated from the GUI dialog box
RESTORE DATABASE foo FROM  DISK = N'somepath' WITH ...

-- and add this
PASSWORD = '**password_is_this**'
GO


SSIS To Move Schemas I'm having one heck of a time trying to compare and move schemas for a new database. I've tried Red-Gate, ApeXSQL etc. and every time I turn around it's not working. I have a customer who has 5 databases in which all 5 have similar schemas. Some have additional stored procs etc. Let me take a step-back and state that Red-Gate does work, however, because I have 73 schemas to filter by I cannot enter every single schema into Red-Gates filtering. So, I thought about trying SSIS, which I've never used before. My objectives and end goals are outlined below. Note: I'm using Visual Studio 2017 and SSDT for VS 2017

If using SSIS I need a way to skip\continue the process if an object is already present.

Objectives:
* To compare\copy the schemas between Database-1 to Database-2 in which only the 73 pre-determined schemas will be created on Database-2.
* To create the tables, stored procedures, functions etc. on Database-2 but, only the ones that belong to the 73 schemas.

End Goal:
* To have a new database that contains all objects for the 73 pre-determined schemas.

Use Data tier Application to upgrade the database.


Reclaiming data file free space after deleting a large amount of data I am an accidental DBA. We have a sql cluster that hosts 10 databases. Suddenly the size of one of the database increased drastically from 100GB to 250GB. When we checked the datafile, the size had grown more than twice over the last few days. We identified the tables and truncated the data and deleted 130GB worth of data. The datafile is still showing 250GB. How do we reclaim the space?

Thanks a lot for all your help.

First, are you sure the data growth won't happen again? If there's a realistic chance it will and the empty space doesn't hurt you, leave it, do NOT shrink the database.

However, if you're positive you want to reduce the data file size, then you should be aware of the pitfalls of data file shrinking:

It causes massive index fragmentation.
Affects performance WHILE it's running and AFTER it's run (one exception - the WITH TRUNCATEONLY option, if you're just removing free space at the end of the file)

I refer to the advice of Paul Randal from his article "Why you should not shrink your data files" (read the whole article to get a clear picture what happens with index fragmentation):

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the
  new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON
  syntax, to move the tables and remove fragmentation from them at the
  same time
  Drop the old filegroup that you were going to shrink anyway
  (or shrink it way down if its the primary filegroup)

  Basically you
  need to provision some more space before you can shrink the old files,
  but it’s a much cleaner mechanism.

  If you absolutely have no choice and have to run a data file shrink
  operation, be aware that you’re going to cause index fragmentation and
  you should take steps to remove it afterwards if it’s going to cause
  performance problems. The only way to remove index fragmentation
  without causing data file growth again is to use DBCC INDEXDEFRAG or
  ALTER INDEX … REORGANIZE. These commands only require a single 8KB
  page of extra space, instead of needing to build a whole new index in
  the case of an index rebuild operation.

  Bottom line – try to avoid running data file shrink at all costs!


MyISAM around 5x slower than InnoDB I thought, MyISAM is for simple tables and selects due to its architecture faster than InnoDB. Therefore I changed the engine for this table from InnoDB to MyISAM:

CREATE TABLE `table1` (
  `DateTime` datetime NOT NULL,
  `BidHigh` decimal(11,5) NOT NULL,
  `BidLow` decimal(11,5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin COMMENT='1 minute rates';

ALTER TABLE `table1` ADD PRIMARY KEY (`DateTime`);

These conditions apply:

I am testing it on a SLES 15.1 VM with 5 GB RAM and 8 CPU cores on a host which is mine and it doesn't have to serve other VM [just an information that there is no other VM which may influence the result].
A PHP script is running thousands of the below SELECT statements.
There are 24 of the above tables in the database which are accessed in the PHP script.
Each table has ~800k rows.
Between each test I restart the server to assure there are always the same conditions. Due to the fact the difference is so huge I am not running multiple test to get an average...

The execution time:

With InnoDB engine the script runs 199 seconds.
With MyISAM engine the script runs 1'026 seconds. More than 5 times longer.

I am running these SELECT statements:

SELECT `DateTime` FROM table1
     WHERE `DateTime` BETWEEN '2018-12-27 07:50:00' AND '2199-12-31 23:59:00'
        AND BidHigh > 0.96604
     ORDER BY `DateTime` LIMIT 1;

-- and --

SELECT MIN( BidLow ) FROM table1
    WHERE `DateTime` BETWEEN '2018-12-27 07:45:00' AND '2199-12-31 23:59:00';

OK, I figured out it is an index problem. Adding these two indexes

ALTER TABLE `table1` ADD UNIQUE `BidHigh` (`DateTime`, `BidHigh`);

-- and --

ALTER TABLE `table1` ADD UNIQUE `BidLow` (`DateTime`, `BidLow`);

fixes the performance issue and now the script needs 245 seconds, still slower than InnoDB - this is not really what I expected...

Adding these indexes to the InnoDB version doesn't improve the performance.

My questions:

Why does InnoDB not need these indexes and is still faster?
Is there a better solution?
And, what did I apparently totally misunderstood as the change to MyISAM caused such a horrible performance.

1) InnoDB will go faster with indexes, too.

2) InnoDB with appropriate indexing is the best solution.

3) MyISAM has been slower than InnoDB for most workloads for well over a decade. There are fundamental difference in how memory and caching work between the two.

In this case InnoDB was picking the first row by primary key. Since InnoDB tables are clustered by primary key, this was very fast, and PK was likely already in memory from when you created the table.

Create an index in BidHigh and it'll go even faster.

Unless you have an overwhelmingly good reason to use MyISAM - you shouldn't. And if you think you have an overwhelmingly good reason, you should re-scrutinize it because they are vanishingly few in 2020.


How can I authenticate mongodb server identity via mongodb shell? I am trying to deploy a mongodb instance in my environment. I have settled a ssl connection so it is possible to authenticate clients (using a certificate they enter and agreed root ca by both the server and the client). Is there a way to authenticate the server identity too? (I could not find any reference to this issue)

I would like to use the mongo shell (without any side programming)

Thank you

I am trying to deploy a mongodb instance in my environment. I have
  settled a ssl connection so it is possible to authenticate clients
  (using a certificate they enter and agreed root ca by both the server
  and the client). Is there a way to authenticate the server identity
  too? (I could not find any reference to this issue) . I would like to
  use the mongo shell (without any side programming).

As per MongoDB documentation here MongoDB supports the following authentication mechanisms:

SCRAM
(Default)
MongoDB
Challenge and Response (MONGODB-CR) (Deprecated as of MongoDB
3.6)
x.509
Certificate Authentication.

New in version 2.6.

MongoDB supports x.509 certificate authentication for client authentication and internal authentication of the members of replica sets and sharded clusters.

x.509 certificate authentication requires a secure TLS/SSL connection.

Certificate Authority

For production use, your MongoDB deployment should use valid certificates generated and signed by a single certificate authority. You or your organization can generate and maintain an independent certificate authority, or use certificates generated by a third-party TLS/SSL vendor.

Client x.509 Certificates

To authenticate to servers, clients can use x.509 certificates instead of usernames and passwords.

Client Certificate Requirements

The client certificate must have the following properties:

A single Certificate Authority (CA) must issue the certificates for
both the client and the server.
Client certificates must contain the following fields:

keyUsage = digitalSignature
extendedKeyUsage = clientAuth
Each unique MongoDB user must have a unique certificate.
A client x.509 certificate’s subject, which contains the
Distinguished Name (DN), must differ from that of a <a
href="https://docs.mongodb.com/manual/tutorial/configure-x509-member-authentication/#x509-member-certificate" rel="nofollow noreferrer">Member
x.509 Certificate. Specifically, the subjects must differ with
regards to at least one of the following attributes: Organization
(O), the Organizational Unit (OU) or the Domain Component (DC).

  WARNING
  If a client x.509 certificate’s subject has the same O, OU, and DC
  combination as the Member
  x.509 Certificate, the client will be identified as a cluster
  member and granted full permission on the system.

For your further ref here


Upgrading 5.6 to 5.7 without losing any data I want to upgrade MySQL from 5.6 to 5.7 but I already have the existing databases and tables. How should I upgrade without losing any data?

The upgrade path is MySQL 5.6 -> MySQL 5.7

see Upgrading MySQL


Tables not showing in database I create a database named Anandschool and tables inside named teacher inside database.
when i see in right hand side in database then it shows only system table but when i apply command ""select * from teacher"" then it showing.
How can i see tables name in right side of database.

The query window is in the context of the master database.

You need to execute USE AnandSchool; at the top of the query window.  Subsequent attempts to SELECT * FROM ... will return an error message.

Prior to creating a table using T-SQL, you need to ensure the query window is using the correct database.


What is the minimum privilege required to create a full text index? I just created a full text catalog on my database.  I now have a developer who needs the ability to create full text indexes on the tables in this database.

The suggestion given in a previous question was to grant db owner to the user. I'd really like to avoid giving such a high level of access.

Is there any way to do this without granting dbo? I wasn't able to find anything regarding the permissions aspect on BOL.

Someone can create indexes if they have been granted db_ddladmin or if their user is the owner for that table, database, or has been granted db_owner.


SQL injection in Postgres functions vs prepared queries In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?

It depends.
SQL functions
With LANGUAGE sql, the answer is generally yes.
Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from the body and pass parameters.
PL/pgSQL functions
With LANGUAGE plpgsql, the answer is normally yes.
However, PL/pgSQL allows for dynamic SQL where passed parameters (or parts) are concatenated to a query string and executed with EXECUTE. This can convert user input to SQL code and makes SQL injection possible. You cannot tell from outside whether the function body handles it properly. Tools are provided.
Only use dynamic SQL where you need it. Plain SQL statements using parameters as values are safe against SQL injection like SQL functions.
For dynamic SQL, preferably pass values as values with:

the USING clause. Example.

Makes SQL injection impossible on principal.
If you concatenate values in the SQL string, use:

format() with format specifier %L. Example.
quote_literal() or quote_nullable(). Example.

Wraps strings in single-quotes safely, thereby avoiding syntax errors and SQL injection.
Process parameters that shall be treated as identifiers in the SQL string with:

format() with format specifier %I. Example.
quote_ident(). Example.
a cast to a registered type - regclass for table names:  _tbl::regclass. Example.

Encloses identifier strings in double-quotes safely where required, thereby avoiding syntax errors and SQL injection.
Related:

Refactor a PL/pgSQL function to return the output of various SELECT queries
Define table and column names as arguments in a plpgsql function?

Never just build a string from user input and execute. This includes identifiers, directly passed by a user or fetched from a system catalog. Everything has to be treated like user input and quoted appropriately when building dynamic SQL!
More about performance implications in this related answer:

Function Performance

Basics on SQL-injection:

http://bobby-tables.com/

Similar considerations apply to other server-side languages that allow dynamic SQL.


What does the output of a JOIN statement look like? I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.

Let's say I have 2 tables:

CREATE TABLE Cities (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    city tinyblob
);

CREATE TABLE Users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username TINYBLOB,
    city INT UNSIGNED,
    FOREIGN KEY (city) REFERENCES Cities (id)
);

If my application is to run an SQL query to get a user's profile data, how would I use a join to get the city associated with a user's record, and how would the outputted record appear?

Every result from a SQL operation is functionally a new table, whether or not it is stored on disk or in memory

The function of a join is to "Join" two tables together into a synthetic third table that (usually) only exists in memory during the time it is output to the application.

The reason to use a join is to reduce Data Anomalies, by insuring that data appears in one and only one place in the database.

Consider the following:

Person ( ID Integer Primary Key, FName String, LName String, Address String, City String, ZipCode String)

Here, whenever the government changes zip-codes (which happens entirely too frequently for database developers' tastes * the persons table will need to be updated such that all old zipcodes correctly belong to the correct city and new zip-code.

In this instance, the same zip code is in multiple tuples of the table, and if the table is editable manually, will be subject to severe update anomalies as new city/zip-code combinations are entered without the database as a whole being updated.

By having three tables instead, we get the same data, but with increased flexibility and reliability (at the cost of some performance, though questions of "appropriate denormalization" are a topic for another series of answers.)

Person ( PersonID Integer Primary Key, FName String, LName String)

City ( CityID Integer Primary Key, City String, State String, ZipCode String )

Address ( AddressID Integer Primary Key, AddressLine1 String, CityID Integer References City, PersonID Integer References Person)`

And we can get a single "table" out of the above through the following query:

    SELECT Fname, Lname, AddressLine1, City, State, ZipCode
      FROM Person
INNER JOIN Address USING (PersonID)
INNER JOIN City USING (CityID)

This allows us to update the individual tables whenever something specific about them changes, without corrupting the rest of the associated data.

An INNER JOIN is the default type of join. Roughly speaking it notes: "combine tuples where this record from table a matches this record from table b" The other types of joins are OUTER JOINS and what's known as a CARTESIAN PRODUCT. An outer join is "Take everything from the left or right table (as specified) and where the other table matches, join it. Where it doesn't match, fill it with nulls" And a cartesian product (without getting into the relational algebra is: Take every possible combination of tuples in both tables and output them into one table, without matching anything.

* More than never, especially in the case of moving the boundaries of zipcodes because of gerrymandering


Use @@servername and db_name() on notify operator task I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like:

  Backup failed on MyDatabase\Instance_name...blablabla

I guess I should use @@SERVERNAME and DB_NAME(db.database_id) someway but so far can't find how to do it, maybe is trivial but no luck find in it.

Notify Operator Tasks are independent of a database context.

Furthermore, the text that's entered into the Properties dialog box gets passed into a statement resembling the following (obtained by clicking the View T-SQL button):

EXECUTE msdb.dbo.sp_notify_operator @name=N'My Operator',
    @subject=N'Subject',@body=N'Message Body'

So what do you do?

Getting the server context is easily solved by switching to an Execute T-SQL Statement Task where you can put whatever SQL you want in there.

That doesn't solve the database name issue, though, because you'll get back either master or msdb and not any particular user database. I don't believe it's possible using Maintenance Plans to get the database name for what you're trying to do. The Back Up Database Task is entirely self-contained.

What you would need to do is use SSIS proper and put a Back Up Database Task and Notify Operator Task within a loop container, where the flow would be executed for each database individually.

Or, you could take a completely different approach and use a script-based backup solution and customize it to suit your exact needs.


How to do Mysql migration from 5.0 (320bit windows) to 5.6 (64 bit windows)? I want migrate My Mysql 5.0 windows 32 bit to Mysql 5.6 Windows 64 bit?

Please suggest me the best way to do migration, i have around 10gb of data present in Mysql 5.0.

You use mysqldump.

Step 1. Dump your original 32 bit database. mysqldump my32bitdb [options...] > mydb.sql

Step 2. Copy the file mydb.sql to your new server

Step 3. Load the 32 bit database mysql -u root -p my64bitdb < C:\path\to\mydb.sql

With 10GB of data, it shouldn't take much more than ~ 1hr.

For reference, see here and here.


What permission do I need for viewing... all current user permissions? I'm maintaining an application that runs with SQL Server 8.0.760 SP3.

When I try to "see" the permissions of the current user I'm logged with, I do this:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

However, the server returns this error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_my_permissions'.

If I try the following query: SELECT * FROM dbo.fn_my_permissions(NULL, 'SERVER');, I get the same error message.

How can I list all the current user permissions?

I had to write a permissions audit for some compliance report a while back and now I have integrated that report into my suite of administrative DB scripts.

I put a copy of the procedure that generates the report in pastebin for you and I verified that it works with SQL Server 2000 still.

http://pastebin.com/08krN7jf

It generates the report in a tree view with the login and default database listed first, then any server level roles, then any database roles and explicitly granted permissions.

Edited to add the actual code directly in the reply rather than rely on the pastebin link

CREATE PROCEDURE [dbo].[spAdmin_PermissionsReport]
AS
BEGIN
    /*
        Description:    A report of each login in a database server along with its
                corresponding server roles and the databases that the login has access to.
                For each database, the database roles and explicit permissions in that
                database are also listed.

                The report is generated in the following format:

                ServerName
                    LoginName [DefaultDB]
                    Created
                    Full Name

                    ServerRole(s):
                        ServerRole1
                        ServerRole2
                        ...
                    Database(s):
                        db1
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        db2
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        ...
    */

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON

    DECLARE
        @SQL                VARCHAR(8000)
    ,   @DB                 VARCHAR(128)
    ,   @LoginName          VARCHAR(128)
    ,   @DefaultDB          VARCHAR(128)
    ,   @CreateDate         DATETIME
    ,   @MemberSID          VARBINARY(85)
    ,   @UID                SMALLINT
    ,   @ServerRole         VARCHAR(128)
    ,   @DatabaseRole       VARCHAR(128)
    ,   @Permission         VARCHAR(200)
    ,   @FirstServerRole    BIT
    ,   @FirstDatabase      BIT
    ,   @FirstDatabaseRole  BIT
    ,   @FirstPermission    BIT
    ,   @RecordSeparator    VARCHAR(1000)

    -- The 'first' variables flag when permission headers should be printed, 1 means yes.
    SELECT
        @FirstServerRole    = 1
    ,   @FirstDatabase      = 1
    ,   @FirstDatabaseRole  = 1
    ,   @FirstPermission    = 1
    ,   @RecordSeparator    = '================================================================================'

    CREATE TABLE #t_Logins (
        LoginName       VARCHAR(128)     NULL
    ,   MemberSID       VARBINARY(85)    NULL
    ,   DefaultDB       VARCHAR(128)     NULL
    ,   CreateDate      DATETIME     NULL
    )

    CREATE TABLE #t_Databases (
        DBName          VARCHAR(128)    NOT NULL
    )

    -- Roles for a server
    CREATE TABLE #t_SRVRoleMember (
        ServerRole      VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    )

    -- Roles for the current database
    CREATE TABLE #t_RoleMember (
        DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NULL
    )

    -- All database roles by database
    CREATE TABLE #t_DBRoleMember (
        DBName          VARCHAR(128)    NOT NULL
    ,   DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    ,   UID         SMALLINT    NOT NULL
    )

    -- Permissions for a database
    CREATE TABLE #t_Protect (
        Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    )

    -- All permissions by database
    CREATE TABLE #t_DBProtect (
        DBName          VARCHAR(128)    NOT NULL
    ,   Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    ,   UID         SMALLINT    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    )

    -- Populate server logins
--  PRINT 'Populate Server Logins'
    INSERT INTO #t_Logins
        SELECT
            sl.[name]
        ,   sl.SID
        ,   sl.DBName
        ,   sl.CreateDate
        FROM
            master.dbo.SYSLOGINS sl
        WHERE
            [name] IS NOT NULL
        AND SID IS NOT NULL
        ORDER BY
            [name]

    -- Populate the databases
--  PRINT 'Populate Databases'
    INSERT INTO #t_Databases
        SELECT
            d.[name]
        FROM
            master..sysdatabases d
        WHERE
            d.[name] NOT IN ( 'tempdb', 'model' )
        AND HAS_DBACCESS(d.[name])                          = 1
AND d.name = 'TMPSERVICE'
        ORDER BY
            d.[name]

    -- Populate logins' server roles
--  PRINT 'Populate Logins'' Server Roles'
    INSERT INTO #t_SRVRoleMember
        EXEC sp_HelpSRVRoleMember

    -- Populate logins' database roles
    DECLARE cDBs CURSOR FOR
        SELECT
            DBName
        FROM
            #t_Databases
        ORDER BY
            DBName

    OPEN cDBs

    FETCH NEXT FROM cDBs INTO @DB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get the current database role
        SET @SQL = 'INSERT INTO #t_RoleMember EXEC [' + @DB + '].dbo.sp_helprolemember'
--      PRINT 'Insert DB Roles for ' + @DB
        EXEC(@SQL)

        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName' +
        '   ,   ''public'' AS DBRole' +
        '   ,   tl.LoginName ' +
        '   ,   tl.MemberSID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Logins tl ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  tl.MemberSID = u.SID '
        EXEC(@SQL)

        -- Store the current database role by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' ' +
        '   ,   t.DBRole ' +
        '   ,   t.MemberName ' +
        '   ,   t.MemberSID ' +
        '   ,   u.UID ' +
        '   FROM ' +
        '       #t_RoleMember t ' +
        '       JOIN #t_Logins tl ' +
        '           ON  t.MemberSID = tl.MemberSID ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  t.MemberName = u.[name] COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       t.MemberName ' +
        '   ,   t.DBRole '
        EXEC(@SQL)

        -- purge out the current database roles for next database
        DELETE FROM #t_RoleMember

        -- Get the database permissions
        SET @SQL = 'INSERT INTO #t_Protect EXEC [' + @DB + '].dbo.sp_helprotect'
--      PRINT 'Insert Permissions for ' + @DB
        EXEC(@SQL)

        -- Store the current database permissions by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBProtect ( ' +
        '   DBName ' +
        ',  Owner ' +
        ',  Object ' +
        ',  Grantee ' +
        ',  Grantor ' +
        ',  ProtectType ' +
        ',  [Action] ' +
        ',  [Column] ' +
        ',  UID ' +
        ',  MemberSID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName ' +
        '   ,   t.Owner ' +
        '   ,   t.Object ' +
        '   ,   t.Grantee ' +
        '   ,   t.Grantor ' +
        '   ,   t.ProtectType ' +
        '   ,   t.[Action] ' +
        '   ,   t.[Column] ' +
        '   ,   u.UID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Protect t ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           JOIN #t_Logins l ' +
        '               ON  u.SID = l.MemberSID ' +
        '           ON  t.Grantee = u.Name COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       Grantee ' +
        '   ,   Object'

        EXEC(@SQL)

        -- purge out the current database permissions for next database
        DELETE FROM #t_Protect

        FETCH NEXT FROM cDBs INTO @DB
    END

    CLOSE cDBs
    DEALLOCATE cDBs

--  SELECT * FROM #t_Logins
--  SELECT * FROM #t_Databases
--  SELECT * FROM #t_SRVRoleMember
--  SELECT * FROM #t_DBRoleMember
--  SELECT * FROM #t_DBProtect

    -- GENERATE REPORT
    PRINT   'Begin report - ' + CONVERT(VARCHAR, GETDATE())
    PRINT   'Report: spAdmin_PermissionsReport'
    PRINT   'Description: A report of each login in a database server along with its'
    PRINT   'corresponding server roles and the databases that the login has access to.'
    PRINT   'For each database, the database roles and explicit permissions in that'
    PRINT   'database are listed.'
    PRINT   @RecordSeparator
    PRINT 'This report reflects local accounts only.'
    PRINT   @RecordSeparator

    SELECT  @SQL = 'SERVERNAME: ' + @@SERVERNAME
    PRINT   @SQL
    PRINT   @RecordSeparator

    DECLARE cLogins CURSOR FOR
        SELECT
            LoginName
        ,   MemberSID
        ,   DefaultDB
        ,   CreateDate
        FROM
            #t_Logins
        ORDER BY
            LoginName

    OPEN cLogins

    FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQL = CHAR(9) + 'LOGINNAME:' + CHAR(9) + @LoginName + ' [' + @DefaultDB + ']'
        PRINT @SQL
        SELECT @SQL = CHAR(9) + 'CREATED:' + CHAR(9) + CONVERT(VARCHAR, @CreateDate)
        PRINT @SQL

        DECLARE cServerRoles CURSOR FOR
            SELECT
                ServerRole
            FROM
                #t_SRVRoleMember
            WHERE
                MemberSID = @MemberSID
            ORDER BY
                ServerRole

        OPEN cServerRoles

        FETCH NEXT FROM cServerRoles INTO @ServerRole

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF ( @FirstServerRole = 1 )
            BEGIN
                PRINT ''
                SELECT @SQL = CHAR(9) + 'SERVERROLE(S):'
                PRINT @SQL
                SET @FirstServerRole = 0
            END

            SELECT @SQL = CHAR(9) + CHAR(9) + @ServerRole
            PRINT @SQL

            FETCH NEXT FROM cServerRoles INTO @ServerRole
        END

        CLOSE cServerRoles
        DEALLOCATE cServerRoles

        DECLARE cDatabases CURSOR FOR
            SELECT
                DBName
            FROM
                #t_Databases
            ORDER BY
                DBName

        OPEN cDatabases

        FETCH NEXT FROM cDatabases INTO @DB

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE cDatabaseRoles CURSOR FOR
                SELECT
                    DBRole
                FROM
                    #t_DBRoleMember
                WHERE
                    DBName = @DB
                AND MemberSID = @MemberSID
                ORDER BY
                    DBRole

            OPEN cDatabaseRoles

            FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END

                IF ( @FirstDatabaseRole = 1 )
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'DATABASEROLE(S):'
                    PRINT @SQL
                    SET @FirstDatabaseRole = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @DatabaseRole
                PRINT @SQL

                FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole
            END

            CLOSE cDatabaseRoles
            DEALLOCATE cDatabaseRoles

            DECLARE cPermissions CURSOR FOR
                SELECT
                    UPPER(LTRIM(RTRIM(dbp.ProtectType))) + ' ' + UPPER(LTRIM(RTRIM(dbp.[Action]))) + CASE WHEN dbp.[Column] NOT IN ('.', '(All+New)', '(All)') THEN ' (' + dbp.[Column] + ')' ELSE '' END + ' ON ' + dbp.Object + ' TO ' + dbp.Grantee
                FROM
                    #t_DBProtect dbp
                    JOIN #t_Logins l
                        ON  dbp.MemberSID = l.MemberSID
                WHERE
                    dbp.DBName = @DB
                AND l.MemberSID = @MemberSID

            OPEN cPermissions

            FETCH NEXT FROM cPermissions INTO @Permission

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END

                IF ( @FirstPermission = 1)
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'PERMISSION(S):'
                    PRINT @SQL
                    SET @FirstPermission = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @Permission
                PRINT @SQL

                FETCH NEXT FROM cPermissions INTO @Permission
            END

            CLOSE cPermissions
            DEALLOCATE cPermissions

            SET @FirstDatabaseRole = 1
            SET @FirstDatabase = 1
            SET @FirstPermission = 1

            FETCH NEXT FROM cDatabases INTO @DB
        END

        CLOSE cDatabases
        DEALLOCATE cDatabases

        SET @FirstDatabase = 1
        SET @FirstServerRole = 1

        PRINT @RecordSeparator

        FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate
    END

    PRINT 'End report - ' + CONVERT(VARCHAR, GETDATE())

    CLOSE cLogins
    DEALLOCATE cLogins

    DROP TABLE #t_DBProtect
    DROP TABLE #t_Protect
    DROP TABLE #t_DBRoleMember
    DROP TABLE #t_RoleMember
    DROP TABLE #t_SRVRoleMember
    DROP TABLE #t_Databases
    DROP TABLE #t_Logins
END


extract state changes from snapshot-based archive table I have a table snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.

For example, assume the following layout:

    CREATE TABLE snapshot
    (
        "timestamp" timestamp with time zone NOT NULL,
        person text NOT NULL,
        haircolor text NOT NULL,
        city text NOT NULL,
        CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp")
    )

Here, I want to find all persons that moved (from any other city) to NY between 2006-02-01 and 2006-02-14. I want all of them, even the ones that moved in on 2006-02-03 and moved out on 2006-02-05 again. (And of course I do not want to list people who only changed their haircolor in the given time frame).

What would an efficient query look like?

I came up with the following query:

    with city_before as (
        select person, city
        from (
            select
                person,
                timestamp,
                first_value(timestamp) over (partition by person order by timestamp desc) as latest_timestamp,
                first_value(city) over (partition by person order by timestamp desc) as city
            from snapshot
            where timestamp < '2006-02-01'
        ) _
        where timestamp = latest_timestamp
    ),
    city_during as (
        select person, city
        from snapshot
        where timestamp between '2006-02-01' and '2006-02-15'
        group by person, city
    )
    select distinct person
    from (
        select s.person, b.city as city_before, s.city as city
        from city_during s
        left outer join city_before b on b.person = s.person
        where b.city <> s.city and s.city = 'NY'
    ) _;


Need help with LIKE operator and square brackets Sample data in a table column, data type NVARCHAR(MAX). This data is collected by extended event trace capturing database calls generated by Entity Framework.

exec sp_executesql N'SELECT TOP (2)
    [Extent1].[ccusqcode] AS [ccusqcode],
    [Extent1].[ccusname] AS [ccusname],
    [Extent1].[ccusfirstname] AS [ccusfirstname],
    [Extent1].[ccuslastname] AS [ccuslastname],
    [Extent1].[dbirthday] AS [dbirthday],
    [Extent1].[ccellphone] AS [ccellphone],
    [Extent1].[cdriverlicense] AS [cdriverlicense],
    [Extent1].[csocsecno] AS [csocsecno],
    [Extent1].[dfirstvisit] AS [dfirstvisit],
    [Extent1].[dlastvisit] AS [dlastvisit],
    [Extent1].[nmtdrxcount] AS [nmtdrxcount],
    [Extent1].[lduplabel] AS [lduplabel],
    [Extent1].[cemployerid] AS [cemployerid],
    [Extent1].[mnotes] AS [mnotes],
    [Extent1].[cemployercontact] AS [cemployercontact],
    FROM [dbo].[customer] AS [Extent1]
    WHERE ([Extent1].[cusid_PK] = @p__linq__0) AND ([Extent1].[pharminfoid_FK] = @p__linq__1)',N'@p__linq__0 bigint,@p__linq__1 bigint',@p__linq__0=NULL,@p__linq__1=1

I want to use LIKE operator to find all rows with text FROM [dbo].[customer].

SELECT *
  FROM [dbname].[schemaName].[tableName]
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%'

Why it does not work?

There is no need to escape the closing bracket. If there is no opening bracket SQL Server will not consider the closing bracket as part of a placeholder.

So you should be fine escaping only the opening bracket, either using brackets or by defining an escape character (which I personally find more readable in this case).

CREATE TABLE statements (statement nvarchar(max));

INSERT INTO statements (statement)
VALUES (N'SELECT TOP (1)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[customer] AS [Extent1] WHERE whatever')
,(N'SELECT TOP (2)  whatever FROM [dbo].[product] AS [Extent1] WHERE whatever')

-- won't return anything
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo[]].[[]customer[]]%';

-- should return 2 records
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM [[]dbo].[[]customer]%';

-- should return 2 records
SELECT *
  FROM statements
  WHERE STATEMENT like '%FROM \[dbo].\[customer]%' ESCAPE '\';

DBFiddle here

The documentation specifies both methods of escaping and specifically states (under the heading Using Wildcard Characters As Literals):

Symbol          Meaning
LIKE '[[]'      [
LIKE ']'        ]


SQL Server Max number of Times a table can be referenced as a foreign key? Is there any maximum on the number of times a single table can be referenced in a SQL Server database via foreign key?

IE

Table A A_ID

Table B ~ B_ID, A_ID
Table C ~ C_ID, A_ID
Table D ~ D_ID, A_ID
...

The short answer is, "No." The longer answer is that there is no explicit restriction, but since those relationships require metadata, the do consume memory and resources: if you have, say, 10,000 foreign key relationships to a single table's primary key, you're likely to find that a delete against the referenced table might be a little...painful.

Here's what the documentation has to say:

Number of FOREIGN KEY Constraints In A Table

SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.

It should also be noted, though, that, SQL Server's system views (e.g., sys.objects) use 32-bit integers as identifiers, the domain of which puts an implicit upper limit on the total number of foreign keys the database could contain.

Not that you'd ever come close to exceeding that limit.


Ranking not correct in sql server 2012 full text search I am having a table like below:

CREATE TABLE [dbo].[test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [area] [varchar](50) NULL,
    [designation] [varchar](100) NULL,
 CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED
(
    [id] ASC
)

id is the primary key in test_table and this table is having full text indexes on name, area and designation columns.

Now I am using a full text query like below:

SELECT test_table.*, ftt.RANK FROM test_table

INNER JOIN

FREETEXTTABLE(test_table, (name,area,designation), 'ranjit trail engineer') as ftt
ON
ftt.[KEY]=test_table.id

ORDER BY ftt.RANK DESC

I get the results like this:

id,    name,           area,          designation ,      rank

84829,"Ranjit Barasa","marine drive","software engineer",24

85323,"Jimmy David","hayes road",   "software engineer", 13

85223, "Arthur Philip","center trail","Quality Engineer",13

As you can see above, row 3 with name as 'Arthur Philip' is having same rank as row 2 with name 'Jimmy David' though row 3 has more matches.(row 3 matched with word 'engineer' and 'trail' but still has same rank as row 2)

Can anyone help on how to solve this issue?

Since you are using FREETEXTTABLE the ranking is very loose because its interpretation of the string is loose.
The text you supplied for row 2 only has one direct match ("Engineer"), but row 3 has 2 matches("trail" and "Engineer").
But you need to understand the difference between FREETEXT and FREETEXTTTABLE operators and the more precise CONTAINS and CONTAINSTABLE operators.
See https://msdn.microsoft.com/en-us/library/ms176078.aspx which explains in part:

• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Because FREETEXT freely interprets your data using a thesaurus it is likely that "trail" and "road" are interpreted as equivalent in meaning and therefore the two rows get the same score.
If you need more precise answers you should use CONTAINSTABLE.


Azure SQL data warehouse - do transaction limits apply to CTAS opperations? Azure SQL Data Warehouse has limits on the maximum size of a transaction (as documented here). As CTAS operations cannot be performed inside a transaction, I was wondering if these limits still applied?

Ref:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions

The transaction safety limits only apply to fully logged operations. CTAS is capable of being minimally logged.  So answer depends, if CTAS is minimally logged safety limit does not apply and vice versa.

  Unlike fully logged operations, which use the transaction log to keep
  track of every row change, minimally logged operations keep track of
  extent allocations and meta-data changes only. Therefore, minimal
  logging involves logging only the information that is required to
  rollback the transaction in the event of a failure or an explicit
  request (ROLLBACK TRAN). As much less information is tracked in the
  transaction log, a minimally logged operation performs better than a
  similarly sized fully logged operation. Furthermore, because fewer
  writes go the transaction log, a much smaller amount of log data is
  generated and so is more I/O efficient.


Getting generic Oracle error message 'missing right parenthesis' CREATE TABLE IDN_ORG (
  ID VARCHAR2(255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  NAME VARCHAR2(255) NOT NULL,
  CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  LAST_MODIFIED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  HAS_ATTRIBUTE CHAR(1) NOT NULL DEFAULT '1',
  STATUS CHAR(1) NOT NULL DEFAULT '1',
  PARENT_ID VARCHAR2(255),
  PRIMARY KEY (ID),
  CONSTRAINT TENANT_ORG_CONSTRAINT UNIQUE (NAME, TENANT_ID)
);

ORA-00907: missing right parenthesis

I'm trying to execute the above script and getting the error as mentioned. While reading, got to know that this is a more generic error that Oracle throws. I tried changing a few with no luck. Can someone please have a look for me? (Extremely new to Oracle)
Thanks in advance.

This is not a "more generic error", it indicates that the order of some clause in your SQL statement is incorrect, and Oracle does not find a valid clause where it expects it. You should check the manual for the correct syntax for CREATE TABLE.
There you will find in particular this description of a column definition:

column [ datatype [ COLLATE column_collation_name ] ]
  [ SORT ] [ VISIBLE | INVISIBLE ]
  [ DEFAULT [ ON NULL ] expr | identity_clause ]
  [ ENCRYPT encryption_spec ]
  [ { inline_constraint }...
  | inline_ref_constraint
  ]

that tells you that the DEFAULT clause comes before any constraints, of which NOT NULL is one.


Checking existence and syncing data with millions of rows I am developing sms based service platform. In India, we have two different types of routes to send sms - promo & trans. Sms sent via promo route has to be checked against nationally maintained database (DND), which currently holds 231450000 mobile numbers.

My question is

What is the efficient way to store this data (DND).
What is the efficient way to check, whether the number exists within these numbers or not.
Twice in a week, a delta dump of these numbers (DND) is provided by telecom authority. How to sync the dump with existing db.
Should I use mysql to store this data, which will consist of mobile numbers, or any other in-memory storage?

EDIT:

Each row of data consists of mobile number alongwith few preferences like area code, user preference etc
The data dump provided twice a week contains only additions / deletions and not the whole data.
Lookup frequency can be very large. Actually every SMS sent from the platform has to first make a lookup against the data (DND). So if the platform is handling 100 sms per second, it has to make 100 lookups per second against this data. The lookup simply checks existence of a particular mobile number against the data(DND) in database.
The new delta data dump (additions / deletions), has to be synced in few hours only, as the dump is provided by telecom authority in the day and the same has to go live by midnight 00:00 hours. So the total time to sync data will be around 3 hours.

Sample DND dump

Sample of DND dump provided. Here A under Opstype means record has to be added, while as D means record has to be deleted.

I would not design a system to do lookups on the fly. Instead I would design the system to validate if a given number is on the DND list at the time the number is added to your system, and weekly (right after the DND list is updated). Then you don't need to check the number each time you send an SMS.

If you follow that design pattern, you'll only need to interact with the DND table very infrequently, and at times that don't impact the speed of sending SMS messages.

If I was doing this, I'd likely use Microsoft SQL Server, and I'd consider a design similar to the one below.  It should be trivial to adapt these SQL statements into whatever flavor of SQL you want to use.

The table to hold the DND rows:

CREATE TABLE dbo.DND
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DND
        PRIMARY KEY CLUSTERED
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

The table to hold your subscribers phone numbers:

CREATE TABLE dbo.Subscribers
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_Subscribers
        PRIMARY KEY CLUSTERED
    , IsOnDND BIT NOT NULL
    , DNDLastChecked DATETIME NOT NULL
);

This query could be used to update the Subscribers table weekly, right after the DND list is updated.

UPDATE dbo.Subscribers
SET IsOnDND = 1
    , DNDLastChecked = GETDATE()
WHERE EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE DND.TelephoneNo = Subscribers.TelephoneNo
    );

I would handle updating the DND list from the weekly change list using a scheduled SQL Server Agent Job.

The table to hold the weekly changes might look like:

CREATE TABLE dbo.DNDChanges
(
    TelephoneNo BIGINT NOT NULL
        CONSTRAINT PK_DNDChanges
        PRIMARY KEY CLUSTERED
    , Opstype CHAR(1) NOT NULL
    , PhoneType INT NOT NULL
    , ServiceAreaCode INT NOT NULL
);

The scheduled job would perform the following steps:

TRUNCATE TABLE dbo.DNDChanges;

--insert rows into the DNDChanges table using SSIS, BCP, etc

/*
    Here we remove rows from the DND table where the number has
    been deleted (Opstype = 'D')
*/
DELETE
FROM dbo.DND
WHERE EXISTS (
    SELECT 1
    FROM dbo.DNDChanges
    WHERE DNDChanges.Opstype = 'D'
        AND DNDChanges.TelephoneNo = DND.TelephoneNo
    );

/*
    This updates the PhoneType, and ServiceAreaCode from the DND
    incremental change list.
*/
UPDATE dbo.DND
SET DND.PhoneType = DNDChanges.PhoneType
    , DND.ServiceAreaCode = DNDChanges.ServiceAreaCode
    , DND.TelephoneNo = DNDChanges.TelephoneNo
FROM dbo.DND
    INNER JOIN dbo.DNDChanges ON DND.TelephoneNo = DNDChanges.TelephoneNo;
    /* no need to add OPSType = 'A' to the WHERE clause
       since we've already removed those rows from the
       DND table */

/*
    Add new rows from the DND incremental change list
*/
INSERT INTO dbo.DND (
    TelephoneNo
    , PhoneType
    , ServiceAreaCode
    )
SELECT *
FROM dbo.DNDChanges
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.DND
    WHERE dbo.DND.TelephoneNo = DNDChanges.TelephoneNo
    );


What is the most efficient method of performing the FIZZBUZZ test in SQL Server? This may not be completely on-topic, however it's a slow day here.

Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number can be evenly divided by 3, BUZZ when the number can be evenly divided by 5, and FIZZBUZZ when the number can be evenly divided by both 3 and 5?

My attempts are (CAUTION, this will empty your procedure cache, so DON'T RUN ON a PRODUCTION BOX):

DBCC FREEPROCCACHE
GO
/*VARIANT1*/
;WITH t AS (
    SELECT RowNum = ROW_NUMBER() OVER (ORDER BY o.object_id)
    FROM sys.objects o
)
SELECT t.RowNum
    , CASE WHEN ((t.RowNum % 3) + (t.RowNum % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.RowNum % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.RowNum % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM t
WHERE t.RowNum < 50;
GO 100

/*VARIANT2*/
DECLARE @t TABLE
(
    Num INT NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @t (Num)
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o;

SELECT t.Num
    , CASE WHEN ((t.Num % 3) + (t.Num % 5)) = 0  THEN 'FIZZBUZZ'
    ELSE
        CASE WHEN t.Num % 3 = 0 THEN 'FIZZ'
        ELSE
            CASE WHEN t.Num % 5 = 0 THEN 'BUZZ'
            ELSE ''
            END
        END
    END
FROM @t t
WHERE t.Num < 50;
GO 100

SELECT CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
    , MAX(deqs.execution_count)
    , SUM(deqs.total_worker_time)
    , AvgWorkerTime = SUM(deqs.total_worker_time) / MAX(deqs.execution_count)
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
WHERE (dest.text LIKE '%/*VARIANT1*/%'
    OR dest.text LIKE '%/*VARIANT2*/%')
    AND dest.text NOT LIKE '%/*NOT_ME!*/%'
GROUP BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
ORDER BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
/*NOT_ME!*/;

As advised by @AaronBertrand, I've modified my attempts to run each set of statements 100 times each, then show the times recorded by SQL Server through sys.dm_exec_query_stats.

The results:

            Runs    total_time      average time
VARIANT1    100     42533           425
VARIANT2    100     138677          1386

Using a SQL Server 2014 memory optimized table and a natively compiled procedure:

-- Setup
CREATE DATABASE InMem;
GO
ALTER DATABASE InMem
ADD FILEGROUP FG1
CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE InMem
ADD FILE
(
    NAME = 'FN1',
    -- Change to suit your system
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\FN1.mod'
)
TO FILEGROUP FG1;
GO
USE InMem;
GO
CREATE TYPE dbo.FizzBuzzTableType AS TABLE
(
    n integer NOT NULL INDEX i,
    FizzBuzz varchar(8) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);
GO

Native procedure:

CREATE PROCEDURE dbo.FizzBuzz
WITH
    NATIVE_COMPILATION,
    SCHEMABINDING,
    EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'english'
)
    DECLARE @n AS dbo.FizzBuzzTableType;

    DECLARE @i integer = 1;
    WHILE @i < 50
    BEGIN
        IF @i % 15 = 0
        BEGIN
            INSERT @n (n, FizzBuzz)
            VALUES (@i, 'FizzBuzz')
        END
        ELSE
        BEGIN
            IF @i % 3 = 0
            BEGIN
                INSERT @n (n, FizzBuzz)
                VALUES (@i, 'Fizz')
            END
            ELSE
            BEGIN
                IF @i % 5 = 0
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, 'Buzz')
                END
                ELSE
                BEGIN
                    INSERT @n (n, FizzBuzz)
                    VALUES (@i, CONVERT(varchar(8), @i));
                END;
            END;
        END;

        SET @i += 1;
    END;

    SELECT
        N.n,
        N.FizzBuzz
    FROM @n AS N
    ORDER BY
        N.n;
END;

Test:

SET NOCOUNT ON;
PRINT SYSUTCDATETIME();
GO
DECLARE @T AS dbo.FizzBuzzTableType;

INSERT @T (n, FizzBuzz)
EXECUTE dbo.FizzBuzz;
GO 100

PRINT SYSUTCDATETIME();

Typical results:

-- 95ms for 100 iterations, < 1ms each
2014-12-31 10:07:13.7993355
Beginning execution loop
Batch execution completed 100 times.
2014-12-31 10:07:13.8943409

This writes the procedure output to an in-memory table variable, because otherwise we're just testing the speed of displaying results in SSMS.

One million rows

The above native procedure takes about 12 seconds to run on 1,000,000 numbers. There are all sorts of faster ways to do the same thing in T-SQL. One I have written before follows. It runs in about 500ms on my laptop on a million rows when the intended parallel plan is achieved:

IF  OBJECT_ID(N'tempdb..#Result', N'U') IS NOT NULL
    DROP TABLE #Result;

IF  OBJECT_ID(N'tempdb..#Thousand', N'U') IS NOT NULL
    DROP TABLE #Thousand;

SET NOCOUNT ON;
DECLARE @start datetime2(7) = SYSUTCDATETIME();

CREATE TABLE #Thousand
(
    n integer NOT NULL,

    CONSTRAINT PK_#Thousand
    PRIMARY KEY CLUSTERED (n)
);

-- Add 1,000 rows numbered 0-999 to #Thousand
WITH
    L1 (n) AS
(
    SELECT  V.n
    FROM
    (
        VALUES  (0), (1), (2), (3), (4),
                (5), (6), (7), (8), (9)
    ) AS V (n)
),
    Thousand AS
(
    SELECT  n =
        CONVERT
        (
            integer,
            ROW_NUMBER() OVER (
            ORDER BY (SELECT NULL))
            - 1
        )
    FROM L1
    CROSS JOIN L1 AS L2
    CROSS JOIN L1 AS L3
)
INSERT #Thousand (n)
SELECT n
FROM Thousand;

-- To hold the Fizz Buzz output
CREATE TABLE #Result
(
    n integer NOT NULL,
    result varchar(8) NOT NULL
);

INSERT #Result
SELECT
    Million.n,
    Million.result
FROM
(
    -- Modulo operation to encourage few outer rows parallelism
    SELECT  n
    FROM    #Thousand
    WHERE   n % 1 = 0
) AS T1
-- Outer Apply to keep the Compute Scalar parallel
OUTER APPLY
(
    SELECT
        F2.n,
        F2.result
    FROM #Thousand AS T2
    CROSS APPLY
    (
        -- Row numbers 1 to 1,000,000
        SELECT  (T1.n * 1000) + T2.n + 1
    ) AS F1 (n)
    CROSS APPLY
    (
        -- The Fizz Buzz bit
        SELECT
            F1.n,
            result =
                CASE
                    WHEN F1.n % 15 = 0 THEN 'FizzBuzz'
                    WHEN F1.n % 3 = 0 THEN 'Buzz'
                    WHEN F1.n % 5 = 0 THEN 'Fizz'
                    ELSE CONVERT(varchar(8), F1.n)
                END
    ) AS F2
) AS Million
OPTION  (MAXDOP 4, QUERYTRACEON 9481);

PRINT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME());


How does Oracle close and unmount a database? I was reading the Oracle 12c concepts manual. Then I realised that something is not "technically" explained. Below is an excerpt of paragraph 13.2.2.2:

  When a database is closed as part of a SHUTDOWN with any option other than ABORT,
  Oracle Database writes data in the SGA to the data files and online redo log files. Next,
  the database closes online data files and online redo log files......
  After the database is closed, Oracle Database unmounts the database to disassociate it
  from the instance.

I don't technically understand what happens for those two sentences in bold above. Any ideas?

I don't know the whole process but I believe that when a file is closed, the OS or the program takes it off the RAM. I can't quite align that with the bold sentences above.

It helps to understand what 'mount mode' means:

Before DB is mounted, instance is up (SGA and other memory structures are allocated but DB and its files etc have not been verified yet. In mount mode, the controlfile will be read, it will make sure all logfiles/datafiles etc are available and ready to go. This is essential maintenance. Then, after mounting, you open the db during which db will do additional checks including to see if any recovery is needed. Once these are done, DB is available for use.

So, during shutdown, once close is issued, Oracle will start stopping processes in a specific order. Then it flushes modifications data to tables, updates column usage metrics, bunch of internal tables, audits etc.

Then DB dismounts, i.e. opposite of mount, it checks for pending disk operations, completes them and then starts closing files, tells ASM to dissociates diskgroup from this instance. After this, archival process, logwriter stops.

So, when file headers are updated, controlfile is updated and file io stops the db is dismounted. All that remains are memory structures. once they are destroyed, DB instance is fully shutdown.

I am fairly certain someone can give you even more gritty details, but this should help you.


How to stop Log Reader Agent from SQL/Command Line Say I've got replication on a SQL Server 2012 box set up.  Everything is working as expected, and the Log Reader Agent is running.

Now, say that I need to remove all traces of this publication/subscription from the database programmatically.  Previously, I had this working with SQL 2008R2.

The steps I was taking are -

Drop subscription at source database (sp_dropsubscription)
Drop subscription at target database (sp_droppullsubscription)
Drop publisher (sp_droppublication)
Remove everything else (sp_replicationdboption)
Remove databases

This used to work perfectly, but since upgrading to SQL 2012, when I try to drop the publisher, I get the following error -

  System.Data.SqlClient.SqlException (0x80131904): Only one Log Reader
  Agent or log-related procedure (sp_repldone, sp_replcmds, and
  sp_replshowcmds) can connect to a database at a time. If you executed
  a log-related procedure, drop the connection over which the procedure
  was executed or execute sp_replflush over that connection before
  starting the Log Reader Agent or executing another log-related
  procedure.

Now, I think I understand what's going on here, but I don't know how to prevent it.

What's the best way of stopping the Log Reader Agent, either via command line, or via SQL?

The Log Reader Agent is typically run via a SQL Agent job (unless you have some funky process that kicks it off from the command line). Given this you should be able to just query msdb.sysjobs for the job, and execute msdb.dbo.sp_stop_job @job_name = (your agent job).


Mysql Subquery Unknown Column With the following query I am getting the error unknown column a.id in where clause. I'm basically trying to add a limit of 5 to what would have been two left joins. Is it possible to rewrite this query so it can work?

SELECT
    CONCAT_WS(' ',(
        SELECT GROUP_CONCAT(body,' ') FROM (
            SELECT c.body FROM c WHERE c.id IN (
                SELECT id_c FROM b WHERE b.id_a=a.id
            )
            LIMIT 5
        ) c
    )) AS contents
FROM
a

Full SQLfiddle at http://sqlfiddle.com/#!9/c1822/3

Find_in_set works http://sqlfiddle.com/#!9/2d43bb/1 but it is extremely slow with large datasets

Another way:

SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
  ON b.id_a = a.id
  LEFT JOIN c
  ON  b.id_c = c.id
  AND c.id <= COALESCE(
    (  SELECT ci.id
       FROM c AS ci
         JOIN b AS bi
         ON  bi.id_c = ci.id
       WHERE b.id_a = bi.id_a
       ORDER BY ci.id
       LIMIT 1 OFFSET 4
    ), 10000000000)
GROUP BY a.id ;

a variation:

-- variation 2
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
    JOIN c
    ON  b.id_c = c.id
    AND c.id <= COALESCE(
      (  SELECT ci.id
         FROM c AS ci
           JOIN b AS bi
           ON  bi.id_c = ci.id
         WHERE b.id_a = bi.id_a
         ORDER BY ci.id
         LIMIT 1 OFFSET 4
      ), 10000000000)
  ON b.id_a = a.id
GROUP BY a.id ;

and two more, all using the same basic pattern:

-- variation 3
SELECT a.id,
       GROUP_CONCAT(c.body,' ') AS contents
FROM a
  LEFT JOIN b
    ON  b.id_a = a.id
    AND b.id_c <= COALESCE(
      (  SELECT bi.id_c
         FROM b AS bi
         WHERE b.id_a = bi.id_a
         ORDER BY bi.id_c
         LIMIT 1 OFFSET 4
      ), 10000000000)
  LEFT JOIN c
  ON b.id_c = c.id
GROUP BY a.id ;

-- variation 4
SELECT a.id,
       ( SELECT GROUP_CONCAT(c.body,' ')
         FROM b
         LEFT JOIN c
         ON b.id_c = c.id
         WHERE b.id_a = a.id
           AND b.id_c <= COALESCE(
             ( SELECT bi.id_c
               FROM b AS bi
               WHERE b.id_a = bi.id_a
               ORDER BY bi.id_c
               LIMIT 1 OFFSET 4
             ), 10000000000)
       ) AS contents
FROM a ;

Tested in sqlfiddle.


After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM (Intel 64-bit).

As the first step, the additional RAM has been installed, but due to some application-related issue we had to pend the rest of the planned upgrade. To clarify, the server is still running Oracle 10g and SLES 10.4. No change has been made on the software side

Since the installation of the additional RAM, however, the only database instance on that machine has been running out of shared memory (with the error in the title) two afternoons in a row. The depletion was severe enough that the only way to recover was using SHUTDOWN ABORT.

No configuration change has been made, e.g. to the memory settings of the database:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1632M
sga_target                           big integer 1632M
pga_aggregate_target                 big integer 384M
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 960M

It seems that the extra RAM actually cause the Oracle database software to have less RAM available to itself than before, but how exactly is this happening?

The error just means that the shared_pool ran out of free space. This can be caused by many things, mostly application related. Make sure your shared_pool has a minimum size needed to run your application.
Sometimes it helps to increase the shared_pool_reserved_size so larger objects can be loaded in there.


informix rest api not startable I am currently trying to configure the informix REST API, as specified here.

Overall, I came across two error messages:

Unsupported listener type of rest specified
OP Code is not valid: Check Message format

Can anybody help me understand the following error messages, or point me to a website or documentation for further research? Any help is greatly appreciated.

Current Progress

I have configured the INFORMIXDIR/etc/jsonListener.properties

#wire listener port
listener.port=27017

#wire listener type
listener.type=rest

#connection string for the wire listener
url=jdbc:informix-sqli://localhost:9088/sysmaster:INFORMIXSERVER=<servername>;USER=<username>;PASSWORD=<password>

#anybody can connect
listener.hostName=*

Upon starting the service with a script, I am told, the type of wire listener is unsupported.

2018-05-03 17:19:59 [main] ERROR com.ibm.nosql.informix.server.JsonListenerCLI - NOSQL_LISTENER_TERMINATED_UNEXPECTED
java.lang.Exception : java.lang.RuntimeException: Unsupported listener type of rest specified
at com.ibm.nosql.informix.server.JsonListenerCLI.start(JsonListenerCLI.java:116)
at com.ibm.nosql.informix.server.JsonListenerCLI.run(JsonListenerCLI.java:76)
at com.ibm.nosql.informix.server.JsonListenerCLI.main(JsonListenerCLI.java:34)
Caused by: java.lang.RuntimeException : Unsupported listener type of rest specified
at com.ibm.nosql.informix.server.JsonListenerCLI.start(JsonListenerCLI.java:108)
at com.ibm.nosql.informix.server.JsonListenerCLI.run(JsonListenerCLI.java:76)
at com.ibm.nosql.informix.server.JsonListenerCLI.main(JsonListenerCLI.java:34)

This did strike me as odd, as  it clearly states rest is a valid value for the property listener.type (as can be seen here).

If I remove the listerner.type=rest property, the default value (mongo) will be used and I can start the wire listener.

If I now try to curl the url http://localhost:27017/, I recieve an empty reply from the server. The corresponding error message in my log file goes as follows:

2018-05-03 18:19:19 [JsonListener-1-thread-1] ERROR com.ibm.nosql.informix.server.LwfMessageHandler - NOSQL_GENERIC_EXCEPTION
Error processing message;
java.lang.RuntimeException : OP Code is not valid: Check Message format
at com.ibm.nosql.informix.internal.request.MessageHeader.<init>(MessageHeader.java:119)
at com.ibm.nosql.informix.server.LwfMessageHandler.handleMessage(LwfMessageHandler.java:283)
at com.ibm.nosql.informix.server.LwfMessageHandler.run(LwfMessageHandler.java:123)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:736)

The bash script to start the wire listener:

#!/bin/sh

${INFORMIXDIR}/extend/krakatoa/jre/bin/java -jar ${INFORMIXDIR}/bin/jsonListener.jar -config ${INFORMIXDIR}/etc/jsonListener.properties -logfile ${INFORMIXDIR}/jsonListener.log -start & sleep 2
exit

Sorry for the delay answer.

“OP Code is not valid: Check Message format” will happen if you are trying to connect a REST client to the Mongo wire listener.

You need to start the listener with listener.type=rest set in your listener properties file.

As for the same question above, there is clearly an old version of the listener being used.

The listener is giving a very clear error message saying that the REST listener type is not supported. The REST listener was introduced in 12.10.xC4. But from the presence of the JsonListenerCLI class in the exception stack, I can determine that the installation is using a listener from either 12.10.xC2 or xC3.  The JsonListenerCLI class was renamed to ListenerCLI in 12.10.xC4 which is also the first version that include REST listener support.


How can I time SQL-queries using psql from command line? There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run script with (optional) timing from command line – without \timing [on|off] in the script, please?

You could use the shell command time

postgres@db:$ time psql db -c 'SELECT 1'
 ?column?
----------
        1
(1 row)

real    0m0.108s
user    0m0.040s
sys     0m0.032s

Or, to combine a meta command with an SQL command, you could pipe a string to psql:

postgres@db:~/script$ echo '\timing \\ SELECT 1;' | LANG=C psql
Timing is on.
 ?column?
----------
        1
(1 row)

Time: 0.000 ms

Per documentation:

  The special sequence \\ (two backslashes) marks the end of arguments
  and continues parsing SQL commands, if any. That way SQL and psql
  commands can be freely mixed on a line. But in any case, the arguments
  of a meta-command cannot continue beyond the end of the line.


I hope I understand this right and you can use this for at least a start.

SELECT x.startdate + INTERVAL (x.days + 1) DAY enddate,
       x.startdate,
       (SELECT sum(t.volumes)
               FROM tab t
               WHERE t.createdAt >= x.startdate
                     AND t.createdAt < x.startdate + INTERVAL (x.days + 1) DAY) volume
       FROM (SELECT '2018-04-12' startdate,
                    12 days
             UNION
             SELECT '2018-03-29' startdate,
                    12 days
             UNION
             SELECT '2018-03-15' startdate,
                    12 days
             UNION
             SELECT '2018-03-01' startdate,
                    12 days
             UNION
             SELECT '2018-02-15' startdate,
                    12 days
             UNION
             SELECT '2018-02-01' startdate,
                    12 days
             UNION
             SELECT '2018-01-18' startdate,
                    12 days) x;

I haven't quite gotten on how you'd like to specify the dates and intervals though. Maybe you can clarify that, than I can probably edit this in.


Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance I am somewhat confused by these terms in the init.ora file:

db_domain
db_name
instance_name

plus the database name which is provided to the create database command. I do not completely understand how they sum up. I have read the Oracle docs several times but still failed to understand.
As far as I understand, a database can be mounted and used by several instances in a RAC which I do not use. This means that db_name has to be the same as in create database?!

This is my setup:

I have 2 machines host1, host2. Both are in the same subdomain dom1.company.net. Both have an instance which hosts the same database but one in for the production system and the other one is the test system.

If I name both data, do I have a name collision?

Thanks for clearing up.

No, you do not have a name collision.  Your DB_DOMAIN, DB_NAME, and INSTANCE_NAME can all be identical as long as the databases are on different hosts (as you have indicated).  However, as others have stated using the same DB_NAME is a bad idea for anything other than perhaps a recovery operation.  A policy enforcing distinct passwords will be broken.  Connections will be confused.  Most things you can do to ensure that changes are not inadvertently made on production are probably worth the hassle.

Here is the relevant documentation:

DB_DOMAIN

  In a distributed database system, DB_DOMAIN specifies the logical
  location of the database within the network structure. You should set
  this parameter if this database is or ever will be part of a
  distributed system. The value consists of the extension components of
  a global database name, consisting of valid identifiers (any
  alphanumeric ASCII characters), separated by periods.

  Note: Oracle recommends that you specify DB_DOMAIN as a unique string
  for all databases in a domain.

  This parameter allows one department to create a database without
  worrying that it might have the same name as a database created by
  another department. If one sales department's DB_DOMAIN is
  JAPAN.ACME.COM, then their SALES database (SALES.JAPAN.ACME.COM) is
  uniquely distinguished from another database with DB_NAME = SALES but
  with DB_DOMAIN = US.ACME.COM.

  If you omit the domains from the name of a database link, Oracle
  expands the name by qualifying the database with the domain of your
  local database as it currently exists in the data dictionary, and then
  stores the link name in the data dictionary.

DB_NAME

  DB_NAME specifies a database identifier of up to 8 characters. This
  parameter must be specified and must correspond to the name specified
  in the CREATE DATABASE statement.

  If you have multiple databases, the value of this parameter should
  match the Oracle instance identifier of each one to avoid confusion
  with other databases running on the system. The value of DB_NAME
  should be the same in both the standby and production initialization
  parameter files.

INSTANCE_NAME

  In a Real Application Clusters environment, multiple instances can be
  associated with a single database service. Clients can override
  Oracle's connection load balancing by specifying a particular instance
  by which to connect to the database. INSTANCE_NAME specifies the
  unique name of this instance.

  In a single-instance database system, the instance name is usually the
  same as the database name.


Create table permission for a user in specific schema I want to give Create , alter and drop permission to user A on a schema1 of a database.
I guess this question has been already asked, and what i have found is to Grant Alter to the schema and also grant create table to the User A:
GRANT ALTER,  DELETE, EXECUTE, INSERT,  SELECT, UPDATE ON SCHEMA::schema1 TO user A;

GRANT CREATE TABLE TO User A;

If this is right then , will the userA will be able to create table on other schemas too?

I want to give Create , alter and drop permission to user A on a schema1 of a database

The safe way to do this is to make A owner of that Schema.

Granting a user the ability to alter another user's schema gives that user the ability to SELECT, INSERT, UPDATE and DELETE rows in any table owned by the owner of that schema.  This is called "Ownership Chaining" and it's what makes Views and Stored Procedures really simple ways to control security, as a user who has permissions on a View or Stored Procedure does not need to be granted permissions on the underlying Table, so long as the View/Proc is has the same owner as the Table.

However in this scenario you can easily create a security hole with Ownership Chains.  Generally you never want a user to be able to create objects that will be owned by a different (and especially a privileged) user.

EG, just granting CREATE TABLE, ALTER, and INSERT creates a security hole:

use master
--drop database security_test
go
create database security_test
go
use security_test
go

create schema schema1 authorization dbo
go
create user A without login
go
grant create table to A
grant alter, insert on schema::schema1 to A
go
create table dbo.secret(id int, msg varchar(200))
insert into dbo.secret(id,msg) values (1, 'secret data')
go
execute as user='A'

create table schema1.foo(id int)

go
create trigger t on schema1.foo after insert
as
begin
  select * from dbo.secret
end

go

insert into schema1.foo(id) values (1)

go

revert

outputs

id          msg
----------- -----
1           secret data


Cannot restore Amazon RDS SQL Server database to given point-in-time using SQL Server Management Studio I'm trying to restore one of my Amazon DRS SQL Server databases. Using SQL Server Management Studio, I can correct to my DRS instance using the master account I created when creating the database instance. When I start restoring a given database I'm able to see all full backups and transaction logs, but it gives me this error message when trying to restore

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  The EXECUTE permission was denied on the object 'xp_fileexist', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I can restore a full database instance from AWS management console, but that is not what I want. I want to be able to restore a given database to a specific point-in-time and not restoring a whole new database instance. Also looks there isn't much information on how to restore to a specific point-in-time.

Thanks.

I would try scripting out the operation instead of running whatever Management Studio is doing "for" you. You shouldn't need to use xp_fileexist (assuming you can validate that the files actually exist in the place the script says). Using 2012 SSMS, I am restoring to a point in time that does not coincide directly with a log backup:

The resulting script in my case was:

BACKUP LOG ... WITH NOFORMAT, NOINIT, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY
RESTORE DATABASE ... WITH FILE = 1, NORECOVERY, NOUNLOAD
RESTORE LOG ... WITH FILE = 1, NORECOVERY, NOUNLOAD
...
RESTORE LOG ... FROM DISK = [...path from line 1...] WITH NOUNLOAD, STOPAT = <time>


Create user on Azure elastic database and deny view to other databases i same setting I have just shifted to Azure and I'm trying to set up some databases.

Previously I have been able to create users that can only see their own database with the following script. But on Azure this is giving me quite the headache.

declare @login_name sysname
declare @db_name sysname
declare @password sysname
declare @sql nvarchar(max)

SET @login_name = 'Username'
SET @password ='Password'
SET @db_name = 'Database'

USE MASTER

set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name)  + 'WITH PASSWORD = ''' + REPLACE(@password, '''', '''''')
execute( @sql )

set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Set the user to use the database created
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  +
    ' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name)
execute( @sql )

set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) +
    'EXEC sp_changedbowner ' + QUOTENAME(@login_name)
execute( @sql )

Everything I find online tells me just to

Create the login first
Then connect to the database I want to assign
the login to
Then create a user and assign roles to this user

One thing is that this is quite a lot of connecting and disconnecting when I'm creating 10-20 databases at a time assigning users to everyone of them. Another thing is that I want make sure that when the users login to their database, they can only see their own database and not all other databases on that server.

What can I do, is there no way to create login & user, assign roles and limit view at the same time?

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO

USE [master]
GO
CREATE DATABASE [TestPerm2]
GO
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login

USE [master]
GO
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.


How do I calculate working business minutes? I'm working on business inteligence reports and one of my task is to create reports for time tracking.

I have to report on business minutes (10 hours per day) within business week (monday to friday).

My initial solution was to use a scalar function. It did not cater for holidays, but it worked. It was not optimized by database engine, therefore, when I have tried using my function in the following way:

Select
   [dbo].[fn_GetWorkMinutesInRange](CreatedOn,ResolvedOn,1, 6, 7)
From
   Incidents

This was not optimised database engine and it was taking over 5 minutes to execute before I canceled it. There are around 20,000 incidents in the Incidents table, so performance should not suffer imo.

I have given up on scalar function solution and I created a table function which created a calendar. This didn't help either as I don't see how I can join this function with my Incidents table.

If your reports are based on a data warehouse that is dimensionally modelled (generally the case for business intelligence solutions), it is highly probable there is a date dimension and time dimension.

If there are no such objects, it may be very useful to create them as they become highly re-usable as other reports are required.

In the date dimension, there should be an IsHolidayFlag column. In the time dimension, there should be an IsBusinessMinuteFlag column (depending on the grain of this dimension, it may be IsBusinessSecondFlag).

The query would then be relatively straight forward and quick, provided the indexes are correct. There is an overhead with the CROSS JOIN (which could be created as a view for re-usability), but for 200,000 rows, it should be negligible.

SELECT  i.IncidentID,
       'WorkMinutesInRange' = SUM(CASE WHEN d.IsHolidayFlag = 'N' AND t.IsBusinessMinuteFlag = 'Y' THEN 1 ELSE 0 END)
FROM    dbo.Incidents i
        INNER JOIN Dim_Date d
            ON d.CalendarDate BETWEEN i.CreatedOn AND i.ResolvedOn
        CROSS JOIN Dim_Time t
GROUP BY i.IncidentID


MySQL - UUID/created_at cursor based pagination? For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we last left off from the last cursor position.

When it comes to a cursor where it is an auto incrementing id value, it's fairly easily to implement:

SELECT * FROM users
WHERE id <= %cursor // cursor is the auto incrementing id, ex. 100000
ORDER BY id DESC
LIMIT %limit

What we're not certain about, is if instead of an auto incrementing id cursor, the only unique sequential identifiers for the cursor are uuid and created_at on the table rows.

We can certainly query based on the uuid to get the created_at, and then select all users that are <= created_at but the issue is what if there are multiple instances of the same created_at timestamp in the users table? Any idea how to query the userstable based on uuid/created_at cursor combination to ensure we get the correct datasets (just as if we were using auto incrementing id)? Again, the only unique field is uuid since created_at may be duplicate, but their combination would be unique per row.

WHERE   created_at <= x
  AND ( created_at < x OR uuid < y )
ORDER BY created_at DESC,
         uuid       DESC

or this equivalent:

WHERE (     created_at < x
       OR ( created_at = x AND uuid < y )
      )
ORDER BY created_at DESC,
         uuid       DESC

This technique works for any pair of columns where the first one (created_at) could have duplicates and the second is unique (uuid or id).

And this is required:

INDEX(created_at, uuid)

Note that both parts of the WHERE are DESC.  Mixing ASC and DESC will defeat the usability of the INDEX.  (MySQL 8.0 can work around that.)

Note also, that this assumes you don't care what order the rows are in when created_at is duplicated, but you do want a consistent ordering.  Note that uuid will be seemingly random, but still consistent.  With that said, id (with or without Galera) and uuid work equally well.

(UUIDs suck, but that is a different discussion.)

More on pagination without using OFFSET .


Event to update a column won't run I have successfully created an event in MySQL to update a column called dob in a table called auth.

My problem is that the event won't run. Here is my code:

DELIMITER $$
   CREATE EVENT update_event1
   ON SCHEDULE EVERY 1 MINUTE
   ON COMPLETION PRESERVE
  DO
    BEGIN
      UPDATE auth set dob=DATE_ADD(dob,INTERVAL 1 DAY) ;
    END $$;

How to solve this problem?

It seem that your event scheduler is not running causing the problem of event not getting executed. use following command to check

show global variables like 'event_scheduler';

if you get that it is off, you can do if you are in MySQL 5.6 or higher

 set global event_scheduler=1;

or add the following line in cnf file to make this change permanent and in versions <5.6

 event_scheduler=ON

The given code would work once you turn on the event scheduler using above approach.

Hope it helps.