Why does SQL authentication sometimes fail and how can I make it work reliably? I have SQL Server Express installed locally on a machine and it works most of the time but occasionally it fails to make a connection with this error:

  Could not obtain information about Windows NT group/user
  'MYDOMAIN\myUser', error code 0x6fd.

I'm at a loss for what to change. I can't guarantee the laptop can see the domain controller at all times because - well - it's a laptop! But it would seem that has no effect anyway (it works when disconnected from the network usually; it fails when connected sometimes).

Here's the SQL Server connection string:

Data Source=localhost\MYINSTANCE;Integrated Security=True;Initial Catalog=myDbName;Connection Timeout=15

What should I do?

Edit:
I've tried moving it to a localdb and using SQL authentication to the SQL Server Express instance. Both ways STILL FAIL with the "could not obtain information..." message. They succeed if there is no network at all, but they all fail if there is a network that isn't the domain one. Not sure what the next move should be...

When your machine is not connected to the network, it uses the cached information for authentication purposes.  This is how you can take the laptop home and log on with your domain account.  If you created a login for a domain account while you weren't connected to the domain and then they tried to log in with that account, it would fail because that information doesn't exist on your laptop and it can't reach the domain to authenticate the credentials.

When you are on the same network as the domain, your machine recognizes this and tries to communicate with the domain controller on regular intervals.  When a communication failure occurs, your local machine is essentially orphaned at that time so any domain resources become unavailable until it can re-establish communication.  If you disable your network connection and log off/on and then try I would bet it works fine.  It might even work if just disable the network, I don't have a laptop to test this out on so I can't say with certainty. This is based on my understanding of Active Directory and experience so it might not be 100% accurate but it is definitely what I have observed.  Like John M suggested, you might want to use a sql login since you are having problems.


MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client? Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead.

What are the drawbacks of doing this through mysqlcheck; both would defragment the tables, correct?

mysqlcheck just runs a bunch of 'optimize table' SQL statements for you, so functionally they do the same thing.

Note that 'optimize table' for innodb just maps to an 'ALTER TABLE foo engine = innodb' statement.  This rebuilds the entire table.  Depending on the size of your table this may take a very long time, during which the table is locked and unavailable.


Editing built-in templates in Management Studio I'm running SQL Server Management Studio 2008 (10.0.5500.0) and was attempting to edit the built-in template "Create T-SQL Trigger (New Menu)". I open the Template Explorer, right-click the template, choose Edit, modify it, then save.

But when I try to use the template by right clicking "Triggers" underneath any table and selecting "New Trigger", it still uses the default template.

In Process Explorer, I can see it accessing this file when I edit the template:

C:\Users\username\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\Trigger\Create T-SQL Trigger (New Menu).sql

When I try to use the template, it loads the script from this path:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Trigger\Create T-SQL Trigger (New Menu).sql

Is there a certain setting/patch to help it keep the file paths sorted out?

What I did was create a new folder and then save the changes to that folder. You have to close out of SSMS and reopen to see it; you can use the same name for the template in a different folder.

You could also save as inthe original folder and add the company name or something else (like A_) to put them together at the top of the list.


Postgres 12.1 uses Index Only Scan Backward instead of index when LIMIT is present I have a medium sized table (~4M rows) of "functionCalls" which consists of 2 columns, input and function (both ids for another table):

  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 input    | integer |           | not null |
 function | integer |           | not null |
Indexes:
    "functionCall_pkey" PRIMARY KEY, btree (input, function) CLUSTER
    "functionCallSearch" btree (function, input)
Foreign-key constraints:
    "fkey1" FOREIGN KEY (function) REFERENCES function(id) ON UPDATE CASCADE ON DELETE CASCADE
    "fkey2" FOREIGN KEY (input) REFERENCES input(id)

I want to find all rows that match a certain function, which is why I added the functionCallSearch index. Here is my query:

SELECT c.input FROM "functionCall" c
INNER JOIN "function" ON (function.id = c.function)
WHERE function.text LIKE 'getmyinode'
ORDER BY c.input DESC
LIMIT 25 OFFSET 0;

This takes forever (currently ~ 20s) because pg refuses to use the index, and decides to do a Index Only Scan Backward on the primary key instead:

 Limit  (cost=0.71..2178.97 rows=25 width=4) (actual time=12903.294..19142.568 rows=8 loops=1)
   Output: c.input
   Buffers: shared hit=59914 read=26193 written=54
   ->  Nested Loop  (cost=0.71..135662.48 rows=1557 width=4) (actual time=12903.292..19142.561 rows=8 loops=1)
         Output: c.input
         Inner Unique: true
         Join Filter: (c.function = function.id)
         Rows Removed by Join Filter: 3649900
         Buffers: shared hit=59914 read=26193 written=54
         ->  Index Only Scan Backward using "functionCall_pkey" on public."functionCall" c  (cost=0.43..80906.80 rows=3650225 width=8) (actual time=0.040..17083.489 rows=3649908 loops=1)
               Output: c.input, c.function
               Heap Fetches: 3649909
               Buffers: shared hit=59911 read=26193 written=54
         ->  Materialize  (cost=0.28..2.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=3649908)
               Output: function.id
               Buffers: shared hit=3
               ->  Index Scan using function_text on public.function  (cost=0.28..2.30 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)
                     Output: function.id
                     Index Cond: ((function.text)::text = 'getmyinode'::text)
                     Buffers: shared hit=3
 Planning Time: 0.392 ms
 Execution Time: 19143.967 ms

When I remove the LIMIT this query is blazingly fast:

 Sort  (cost=5247.53..5251.42 rows=1557 width=4) (actual time=3.762..3.763 rows=8 loops=1)
   Output: c.input
   Sort Key: c.input DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=6 read=4
   ->  Nested Loop  (cost=0.71..5164.97 rows=1557 width=4) (actual time=0.099..3.739 rows=8 loops=1)
         Output: c.input
         Buffers: shared hit=6 read=4
         ->  Index Scan using function_text on public.function  (cost=0.28..2.30 rows=1 width=4) (actual time=0.054..0.056 rows=1 loops=1)
               Output: function.id
               Index Cond: ((function.text)::text = 'getmyinode'::text)
               Buffers: shared hit=2 read=1
         ->  Index Only Scan using "functionCallSearch" on public."functionCall" c  (cost=0.43..5103.71 rows=5897 width=8) (actual time=0.039..3.670 rows=8 loops=1)
               Output: c.function, c.input
               Index Cond: (c.function = function.id)
               Heap Fetches: 8
               Buffers: shared hit=4 read=3
 Planning Time: 0.514 ms
 Execution Time: 3.819 ms

Things I've tried so far:

After reading Postgres sometimes uses inferior index for WHERE a IN (…) ORDER BY b LIMIT N I have checked n_distinct - but is not that far off, pg_stats says 623 while SELECT COUNT(*) FROM (SELECT DISTINCT function FROM "functionCall") returns 1065.
I've increased the table SET STATISTICS to 10k and ran ANALYZE. That cuts the time in half (9s) but still won't use the index I created.

Why is this? And how can I fix this?

PostgreSQL estimates that there will be 1557 rows that satisfy the condition, so it thinks that it will be faster if it avoids an explicit sort and rather scans the rows in ORDER BY order and does nested loop joins until it has found enough rows.

Unfortunately, that doesn't work out at all, and PostgreSQL has to scan the whole table that way, since there are only 8 matches total.

The problem seems to be that the estimates are quite off: it thinks that the index scan on public."functionCall" for function will produce 5897 rows, when really there are only 8.

As a first measure, try to calculate new distribution statistics on the table:

ANALYZE public."functionCall";

If that alone does not improve the estimate, increase the granularity:

ALTER TABLE public."functionCall" ALTER function SET STATISTICS 1000;
ANALYZE public."functionCall";

That should improve the estimate. You can try with higher values than 1000 too.

If all fails, tell PostgreSQL explicitly not to use that strategy by using

ORDER BY c.input + 0 DESC


Int comparison very slow in query I have this for simplicity fictional table which explains my problem:

CREATE TABLE IF NOT EXISTS `posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `posts` int(11) NOT NULL,
  `comments` int(11) NOT NULL,
  `notes` varchar(100) NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `posts` (`posts`)
  KEY `comments` (`comments`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306;

The table has over 1 million rows, MariaDB version 10.0.20

Now I want to perform an update and set a notice on one post that has more comments than posts

UPDATE posts SET notes = 'more comments' WHERE posts < comments

Now this query takes about 2-4 seconds to complete.

I suppose I'm missing an index or is it necessary to rewrite the query?

When I do queries with a fixed comparison

UPDATE posts SET notes = 'more comments' WHERE posts > 1000

the query runs fast.

First, a query or (update) statement with a condition like WHERE posts < comments that compares 2 columns cannot effectively use your indexes so it will probably have to do a full table scan. It might be better if you had a composite index, (posts, comments) or the other way around, but it would still need to do a full index scan.

If the rows to be updated are very few, say 100, it's not very efficient to scan 1 million rows to update only 100.

So, the easiest thing would probably be to add such a composite index and test. If the resulting efficiency from the full index scan is acceptable, you can keep using this query.

Another thing you could do - since you are using MariaDB - is to add a VIRTUAL (computed) column and index it:

ALTER TABLE posts
  ADD COLUMN comment_posts_diff INT AS (columns - posts) PERSISTENT,
  ADD INDEX more_comments_ix (comment_posts_diff) ;

Then you only need to change the condition of your queries to use:

WHERE comments - posts > 0

or:

WHERE comment_posts_diff > 0

and the index will be effectively used.

Note that there are 2 variants of computed columns, the VIRTUAL and the PERSISTENT ones. As it's obvious from the names, the persistent columns
actually take space on disks and the values are automatically modified when any of the columns in their definition is modified. Their advantage is that they can be indexed.

If the 'more comments' though is just a constant string that applies to rows with comments > posts and there is another string ('less comment'?) that applies to the rest of the rows or a similar need, you don't really need the column notes and any of the above. Your queries could just compute the respective value (more comments, less comment, no comments) at run time.

Or you could have a computed column to do the work for you (vitual this time, not persistent):

ALTER TABLE posts
  DROP COLUMN notes,
  ADD COLUMN notes VARCHAR(20) AS
      CASE WHEN columns > posts THEN 'more comments'
           WHEN columns = 0     THEN 'no comments'
           WHEN columns = posts THEN 'comments = posts'
           ELSE 'less comments'
      END
    VIRTUAL ;


How to update views outside of update/insert transaction in SQL Server Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it?

There will be about 10 views with 20 indexes that depend on the same table. I don't want to wait for updating views and indexes on views when I do an update on dependent table. I want to be sure that tables are updated within a transaction and views are updated after in some kind of a daemon mode.

No, there isn't. The C (Consistency) in ACID dictates that the dependent (indexed) views are updated.

If you want to workaround this, you'd need to build views on a separate table that is updated on demand in your own time. Say via Service Broker or scheduled


List database CONNECT privileges on PostgreSQL How can I view database-level privileges like CONNECT of users?

I found 2 ways already, but neither of them is perfect:

Use function has_database_privilege().

This works, but I have to run it manually for every user one by one:

SELECT has_database_privilege('david', 'postgres', 'connect');

This one seems to return an incomplete result. It returns NULL at first, but david and other users can still connect to postgres DB. Only after I run any GRANT command (and then REVOKE of course) on that DB, the table is filled with some result: {=Tc/postgres,postgres=CTc/postgres}. So I guess I have to GRANT and then REVOKE every time before I use this method?

SELECT datname as "Relation",
       datacl as "Permissions"
FROM pg_database
WHERE datname = 'postgres';

Using PostgreSQL 10.9 on Ubuntu

You can use the function as part of a select that retrieves all users:

SELECT rolname, has_database_privilege(rolname, 'postgres', 'connect')
from pg_roles
where rolcanlogin;

You can extend that, to show this information for every user in every database:

select db.datname, r.rolname, has_database_privilege(r.rolname, db.datname, 'connect')
from pg_roles r
  cross join pg_database db
where r.rolcanlogin
  and db.datallowconn
order by db.datname, r.rolname;


HTML entity decoding with oracle DBMS_XMLGEN.convert Here, I am trying to convert the value, where I don't understand I am going wrong. Your help is highly appreciated!

Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;

to

Here I am, THis is me there's nowhere else on earth I rather be Here I am,

For this I used the following query which didn't work, Can you please tell me where I am wrong?

select DBMS_XMLGEN.convert(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) as message from dual;

Here it is

WITH x AS (SELECT DBMS_XMLGEN.CONVERT(REGEXP_REPLACE('Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44;', '(#[[:digit:]]+;)','&\1'), 1 ) AS message FROM dual) SELECT message, UTL_I18N.UNESCAPE_REFERENCE(message) conv_message from x;


FLUSH TABLE : can't execute the given command because you have active locked tables mysql If I execute these queries one by one, all fail except the first:

Query1 :

flush table tablename1 with read lock;

Works

Query2 :

flush table tablename2 with read lock;

  Error :  can't execute the given command because you have active locked tables mysql

If I execute, in below way, it works:

Query:

flush table tablename1,tablename2 with read lock;

What's happening inside?

This is because

flush table tablename1,tablename2 with read lock;

is contained within one transaction and

flush table tablename1 with read lock;

flush table tablename2 with read lock;

Is not.

In this second scenario the first table is locked for that session. That session can't acquire a second lock on another table because the session already has one table open. If you tried to access another different table by using a SELECT, within that same session, for example tablename2, that would also be refused.

MySQL [testlocks]> select * from tablename2;
ERROR 1100 (HY000): Table 'tablename2' was not locked with LOCK TABLES

So to make a long story short, by locking only one table you are saying that the current session only has access to that one table. At least that's how I understand MySQL's documentation.

Or you could just use

flush table tablename1,tablename2 with read lock;

as you already figured out and this is the syntax given by MySQL's documentation here


Why the sudden increase of my database .mdf file size? Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below:
    SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;

and it showed this result:

I have a service broker on.
Any suggestion to remove or how to minimize the queue_message table size?

SELECT COUNT(*) FROM sys.conversation_endpoints; from here count is 7 and the query in Dan's answer returns 4 activation_procedure and 4 return NULL.

dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver has 361840332 rows, is_retention_enabled 0, Activation procedure- NULL
dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Sender has 0 row  is_retention_enabled 0 and Activation procedure [dbo].[dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_QueueActivationSender]

Messages will be retained in Service Broker queues until they are consumed with RECEIVE or the associated conversations ended with END CONVERSATION. Messages will also be retained in the queue after RECEIVE until the conversation is ended if the queue is set to to RETENTION = ON.
A large number of messages indicates there may be a problem with the application or activated stored procedure where messages are not being dequeued properly. Another possible cause is the queue is disabled.
The query below will return the message counts per queue along with the queue settings to determine next steps.
SELECT
      sch.name
    , sq.name
    , p.rows
    , sq.is_receive_enabled
    , sq.is_enqueue_enabled
    , sq.is_retention_enabled
    , sq.is_activation_enabled
    , sq.activation_procedure
FROM sys.service_queues AS sq
JOIN sys.schemas AS sch ON sch.schema_id = sq.schema_id
JOIN sys.internal_tables AS it ON it.parent_object_id = sq.object_id
JOIN sys.partitions AS p ON p.object_id = it.object_id
WHERE p.index_id = 1;

It seems there is only a single long-running conversation on the queue given the few end points so that rules out conversation leakage as the cause. Also, since there is no activation procedure associated with the problem queue, you should reach out to your app team to determine why the messages aren't being read.
That being said, you can dequeue the messages using a T-SQL script if you know the messages are not actually needed. The example script below does this in batches to avoid filling the log. In the case of full recovery model, make sure the log is large enough to accommodate the messages deleted between log backups.
SET NOCOUNT ON;
DECLARE
      @conversation_handle uniqueidentifier
    , @BatchSsize int = 100000
    , @MessagesDeleted int = -1;
WHILE @MessagesDeleted <> 0
BEGIN
    RECEIVE TOP (@BatchSsize) @conversation_handle = conversation_handle
        FROM [dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver];
    SET @MessagesDeleted = @@ROWCOUNT;
    RAISERROR('%d messages deleted this batch', 0, 0, @MessagesDeleted) WITH NOWAIT;
END;
GO


Oracle rman db restore First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks.  I checked previous threads in Oracle community but nothing works for me. I am trying to restore Oracle 11.1.0.7. multi HDD cluster production on single hdd locally on amazon ec2.

Restore is done from full back-up copy I downloaded from ftp location (I have no access to production,export and etc.) just have access to the files, i.e. I  am not able to change anyhow export procedure.

So here are my steps in brief (I guess that problem might be in some of them):

rman target /
set DBID ...
startup nomount
RESTORE CONTROLFILE FROM 'file destination'
shutdow;startup mount;
Catalog the backup files to rman: catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';for all AL
and BK files
sqlplus / as sysdba
Rename all redo files to match my local directory structure
rman:crosscheck backup;
    Run{
Rename all data files to match my local directory structure

SET UNTIL SEQUENCE 50511;
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

sqlplus sys/sys as sysdba
shutdown immediate;  startup mount;
alter database noarchivelog;
Drop the no longer used log files:-drop these in state UNUSED left Active and Inactive
Drop all temp files(they point to other drive letter;
ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace
  ADD TEMPFILE 'c:\oracle............dbf'
  SIZE 1G REUSE
  AUTOEXTEND ON
  MAXSIZE UNLIMITED;

My problem is that on my second query (get) to db through my app I receive:

ORA-01578: ORACLE data block corrupted (file # 64, block # 2559928)
ORA-01110: data file 64: 'C:\ORACLE\ORADATA\mydb\mydbLOBDATA2_1.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "mydbUSER.POBJECTSTATE", line 78
ORA-06512: at line 1

What I was able to find with validation and it is LOBSEGMENT -   Lob_ObjectState and it logically corrupted.

I try DBMS_REPAIR and with rman advise failure (which suggest to restore my copy from back-up- nothing useful).

Since back-up are refreshed every week I try with new ones from the next week, again the same problem.

Is there a possibility that the problem starts from export (how can I check  this-I do not have access but i may ask db admin to check anything)?

Any other ideas?

This line clearly states the reason for the block corruption:

ORA-26040: Data block was loaded using the NOLOGGING option

NOLOGGING operations are not logged in the redo as traditional operations, the amount of information logged is greatly reduced to an extent, where data recovery is not possible. Because of this, if you did not make a backup of the affected datafiles after these operations had finished, those modifications can not be recovered with archivelogs, and you will receive the above error.

NOLOGGING should be handled with care, and because of the above, you should perform a backup after performing a NOLOGGING operation. If your backup was created earlier, you will not be able to fully recover data using this backup, even if you have the archivelogs.

So, perform the backup after the NOLOGGING operations was finished.

Another way is to control the NOLOGGING attribute of your objects. You can set this at table, index, LOB segment, tablespace level. You can set it to LOGGING, so operations on the object will be logged (or this will be the default inherited value in case of tablespace).

Finally, you can enable FORCE LOGGING at the tablespace or database level, so operations will be logged regardless of the object level setting.


Concurrent UPDATEs on INNODB tables in MySQL I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do not want to wait. As I have a 16 core CPU I would like to UPDATE concurrently.

Currently, as I do UPDATE all rows for each column the whole table is locked and I cannot start a further UPDATE on the same table on another column. Is there a (unsafe) way to do it in parallel? Can I disable locking of the table / rows?

Maybe something equivilent to READ UNCOMMITTED...

I am using MySQL 8.0 with INNODB and I it is a single user system so I do not have to worry about uncommited changes.

Is there a (unsafe) way to do it in parallel?

If there is some mark that a particular record was processed by a particular update (or you can insert a field into table structure for this purposes) you may use parallel chunk updates like

SELECT 1
FROM table
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count
FOR UPDATE;

UPDATE table
SET field = function(parameters)
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count;

Moreover, you can start a lot of parallel updates for the same field.


What is a good solution for reliably transferring data between SQL Server and PostgreSQL? In analyzing some of our business data, we're realizing that we need to join data across disparate data sources. For example, our application data is warehoused in Postgres (ported from MongoDB via MoSQL), while our purchase info (subscriptions, financial info, etc.) is archived in SQL Server.

I've written a basic command line tool using Python to prove that I can move data from Postgres to Postgres and from SQL Server to Postgres, after which I would manipulate the data using plain SQL. This looks like it could work, but I'm quickly realizing that I'll probably run into lots of data translation issues (e.g. None needs to be \N) along the way and there's probably a tool that does this.

I'm having a hard time searching for tools that can be run from the command line. CloverETL showed up in several searches, but that seems pretty complicated for what I need (who knows—maybe I need something complicated). Rather, I'm looking for something where I can give the SQL for one data source and have the tool compensate for the differences and insert that data into a disparate data source. I want to develop on Mac and deploy on *nix.

Are there good, lightweight, scriptable ETL tools that would help with this process?

Pentaho have an open-source tool called Kettle that we use for ETL development with Postgres. It is quite good and very comprehensive.

http://community.pentaho.com/projects/data-integration/


Is it a good practice to isolate login information (username, password) in a separate table? I have a database table called "patient" where I store the patient information. The structure (list of columns) is as follows.

patient table

idPatient (int)
user_name (varchar)
password (varchar)
first_name (varchar)
last_name (varchar)
height (double)
weight (double)

This database is connected to an android app remotly. There is a login process and that is why you can see user_name and password fields.

However I have an idea about the following table structure, instead of above.

user table

idUser (int)
user_name (varchar)
password (varchar)

patient table

idPatient (int)
user_iduser (int) /*The foriegn key of `user` table*/
first_name (varchar)
last_name (varchar)
height (double)
weight (double)

So which type of structure is the best? The only reason I came up with 2nd structure is because to further protect user information by not putting them in same place where it is used for login check.

Please advice.

I would recommend you implement security by encrypting the data you want to keep secure.  Separating the username and password to a different table would not significantly improve their protection.


How can I tell what database/procedure is using a linked server? I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL 2005 which we're trying to eliminate. Server C has copies of some of the databases from Server B, and we're migrating applications from Server B's database copies to Server C's.

When I'm on Server B, I can see connections from Server A to a certain database but I don't know how to tell what procedure, task, or job from Server A is using that linked server connection to Server B.

In order to retire the database on Server B, I need to re-point Server A's connections to a database on Server C; but in order to do that, I need to know what procedures, tasks, or jobs on Server A are using that connection so they can be updated.

Is there a way to see the dependencies on a linked server without disabling the linked server to see what starts failing?

I use this script that I developed to help me find linked server references, but can be used to find any text string in multiple objects.

/*********************************
    Name: Text Searcher
    Author: Jonathan Fite
    Created: 1/16/2015

    Purpose: To enable one to more easily search for objects by an indicated string.

    Usage:
        - Update the "Where To Search" section to turn on or off various sections.
        - Craft an insert statement to populate "@ObjectsToSearchFor" with the text you
            want to look for.
        - Execute.

    NOTE: This works recursively (in a fashion).  The first execution gets just what you are
    looking for.  But if you execute it again without explicitly dropping the "#SearchResults" table
    then it will run again, but this time looking for references to the objects it found in the first pass.

    This is useful when you are trying to find out what is using a linked server.  So you get the name of the
    linked server (and it's host name) and run a search on that.  It tells you a handful of objects.  You then run
    it again, but this time looking for those objects (say, in other stored procedures or in SSIS/Jobs).

    CAUTION: If you want to use the recursive functionality, you will need to save off the results FIRST.

        DROP TABLE #SearchResults

    -Linked Server Query (finds all references to linked servers as configured, as well as all variations
        that would allow for accessing a remote server without using a linked server.

        INSERT INTO @ObjectsToSearchFor
        (TextString)
        SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME
        UNION
        SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
        UNION
        SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
        UNION
        SELECT 'OPENROWSET'
        UNION
        SELECT 'OPENQUERY'
        UNION
        SELECT 'OPENDATASOURCE'

**********************************/

--To hide row counts and make the print statements easier to see.
SET NOCOUNT ON

/** Where To Search **/
DECLARE @SearchSSIS BIT = 1
DECLARE @SearchObjectNames BIT = 1
DECLARE @SearchObjectDefinition BIT = 1
DECLARE @SearchAgentJobs BIT = 1

/** What are you looking for? **/
DECLARE @ObjectsToSearchFor TABLE
    (
    TextString VARCHAR(200) NULL
    )

INSERT INTO @ObjectsToSearchFor
(TextString)
SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME
UNION
SELECT datasource FROM sys.sysservers WHERE srvname <> @@SERVERNAME
UNION
SELECT srvnetname FROM sys.sysservers WHERE srvnetname <> @@SERVERNAME
UNION
SELECT 'OPENROWSET'
UNION
SELECT 'OPENQUERY'
UNION
SELECT 'OPENDATASOURCE'

/*************************************************************************************************************************
    Shouldn't need to mess with anything below this line.
**************************************************************************************************************************/

/** Handle Recursion on #SearchResults.
    Remember to save your data first!

    If you don't want recursion, explitly drop #SearchResults between executions.
    **/
IF OBJECT_ID('tempdb..#SearchResults') IS NOT NULL
BEGIN

    DELETE FROM @ObjectsToSearchFor

    --This allows for successive iterations to search for objects which reference objects which reference....
    INSERT INTO @ObjectsToSearchFor
    (TextString)
    SELECT DISTINCT ObjectName
    FROM #SearchResults
    WHERE LocationFound IN ('sys.sql_modules')
        AND ObjectName IS NOT NULL

    SET @SearchObjectNames = 0

    DROP TABLE #SearchResults

END

CREATE TABLE #SearchResults
    (
    DatabaseName SYSNAME NULL
    , ObjectName SYSNAME NULL
    , LocationFound VARCHAR(100) NULL
    , MatchedOn VARCHAR(200) NULL
    , Misc VARCHAR(100) NULL
    )

/** Various declarations to hold results. **/
DECLARE @SearchTerm VARCHAR(200)
DECLARE @DatabaseName SYSNAME
DECLARE @Query NVARCHAR(4000)

-- Loop through search terms.
DECLARE curSearch CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT O.TextString
    FROM @ObjectsToSearchFor O

OPEN curSearch

FETCH NEXT FROM curSearch
INTO @SearchTerm

WHILE @@FETCH_STATUS = 0
BEGIN

    --Begin Searching.
    PRINT ('Looking for: ' + @SearchTerm)

    /** This will loop over every database, only do it if you need it. **/
    IF(@SearchObjectDefinition = 1 OR @SearchObjectNames = 1)
    BEGIN

        DECLARE curDatabases CURSOR LOCAL STATIC FORWARD_ONLY
        FOR SELECT D.name
            FROM sys.sysdatabases D
            ORDER BY D.name

        OPEN curDatabases

        FETCH NEXT FROM curDatabases
        INTO @DatabaseName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            --Look for object name matching...
            IF(@SearchObjectNames = 1)
            BEGIN

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', O.name'
                    + ', ' + QUOTENAME('sys.objects', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.type_desc'
                    + ' FROM sys.objects O (NOLOCK)'
                    + ' WHERE LOWER(O.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')

                EXEC sp_executesql @Query

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', C.name'
                    + ', ' + QUOTENAME('sys.columns', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.name + O.type_desc'
                    + ' FROM sys.columns C (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = C.[object_id]'
                    + ' WHERE LOWER(C.name) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')

                EXEC sp_executesql @Query

            END --Looking for object names.

            --Look for definitions.
            --Find objects that have the string in question listed, but ignore where the name is what is being searched for.
            IF(@SearchObjectDefinition = 1)
            BEGIN

                SET @Query = 'USE ' + QUOTENAME(@DatabaseName) + ';'
                    + 'INSERT INTO #SearchResults'
                    + ' SELECT DB_NAME()'
                    + ', O.name'
                    + ', ' + QUOTENAME('sys.sql_modules', '''')
                    + ', ' + QUOTENAME(@SearchTerm, '''')
                    + ', O.type_desc'
                    + ' FROM sys.sql_modules M (NOLOCK) INNER JOIN sys.objects O ON O.[object_id] = M.[object_id]'
                    + ' WHERE LOWER(M.[definition]) LIKE ' + QUOTENAME('%' + LOWER(@SearchTerm) + '%', '''')
                    + ' AND NOT(LOWER(O.name) = LOWER(' + QUOTENAME(@SearchTerm, '''') + '))'

                EXEC sp_executesql @Query

            END --Looking for object definitions.

            --Get next database
            FETCH NEXT FROM curDatabases
            INTO @DatabaseName

        END

        CLOSE curDatabases
        DEALLOCATE curDatabases

    END

    --Look in SSIS packages.
    IF(@SearchSSIS = 1)
    BEGIN

        ;WITH SSISFolders AS
        (
        SELECT pf.folderid
            , pf.parentfolderid
            , CAST(pf.foldername AS VARCHAR(MAX)) AS foldername
        FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
        WHERE pf.parentfolderid IS NULL
        UNION ALL
        SELECT pf.folderid
            , pf.parentfolderid
            , Parent.foldername + '\' + ISNULL(CAST(pf.foldername AS VARCHAR(MAX)), '') AS foldername
        FROM msdb.dbo.sysssispackagefolders pf (NOLOCK)
            INNER JOIN SSISFolders Parent ON Parent.folderid = pf.parentfolderid
        )
        INSERT INTO #SearchResults
        SELECT NULL
            , ISNULL(F.foldername, '') + '\' + CAST(ISNULL(S.name, '') AS VARCHAR(MAX))
            , 'msdb.dbo.syssispackages'
            , @SearchTerm
            , NULL
        FROM msdb.dbo.sysssispackages S (NOLOCK)
            LEFT OUTER JOIN SSISFolders F (NOLOCK) ON F.folderid = S.folderid
        WHERE LOWER(CAST(CAST(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS VARCHAR(MAX))) LIKE ('%' + @SearchTerm + '%')

    END

    --Look in SQL Agent Jobs
    IF(@SearchAgentJobs = 1)
    BEGIN

        --Job Name
        INSERT INTO #SearchResults
        SELECT NULL
            , J.name
            , 'msdb.dbo.sysjobs'
            , @SearchTerm
            , 'Job Name'
        FROM msdb.dbo.sysjobs J (NOLOCK)
        WHERE LOWER(J.name) LIKE ('%' + @SearchTerm + '%')

        --Job Description
        INSERT INTO #SearchResults
        SELECT NULL
            , J.name
            , 'msdb.dbo.sysjobs'
            , @SearchTerm
            , 'Job Description'
        FROM msdb.dbo.sysjobs J (NOLOCK)
        WHERE LOWER(J.[description]) LIKE ('%' + @SearchTerm + '%')

        --Step Name.
        INSERT INTO #SearchResults
        SELECT NULL
            , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
            , 'msdb.dbo.sysjobsteps'
            , @SearchTerm
            , 'Step Name'
        FROM msdb.dbo.sysjobsteps S (NOLOCK)
            INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
        WHERE LOWER(S.step_name) LIKE ('%' + @SearchTerm + '%')

        --Step Code.

        INSERT INTO #SearchResults
        SELECT NULL
            , (ISNULL(J.name, '') + ' - ' + ISNULL(S.step_name, '') + ' (' + CAST(S.step_id AS VARCHAR(10)) + ')')
            , 'msdb.dbo.sysjobsteps'
            , @SearchTerm
            , 'Step Code'
        FROM msdb.dbo.sysjobsteps S (NOLOCK)
            INNER JOIN msdb.dbo.sysjobs J (NOLOCK) ON J.job_id = S.job_id
        WHERE LOWER(S.command) LIKE ('%' + @SearchTerm + '%')

    END --End Looking at Jobs.

    --Get next search term.
    FETCH NEXT FROM curSearch
    INTO @SearchTerm

END

--Cleanup.
CLOSE curSearch
DEALLOCATE curSearch

/***************** Display Output **********************************/

--Display search terms.
SELECT O.TextString
    , COUNT(S.MatchedOn) AS MatchCount
FROM @ObjectsToSearchFor O
    LEFT OUTER JOIN #SearchResults S ON S.MatchedOn = O.TextString
GROUP BY O.TextString

--Display search results.
SELECT DISTINCT * FROM #SearchResults


Remove Duplicate Entries from DB w/ Unique ID that is NOT Primary Key - Keep one of course! I have a CDR Table with a "unique id" column which is not a Primary Key. During an upgrade, there were a few days where instead of one entry for a caller ringing a queue, there was multiple entries for this caller. All the entries have the same unique ID.

What I would like to do, is delete all entries with the same unique ID, but leaving one entry. Is there a simple way to do this? All my searching turned up results on either, DB's with no unique ID, or with a Primary key. I have both a unique ID and No Primary key lol.

Thanks for your guidance!

PS: MySQL 5.5

Eg:

If more than, say, half the table needs to be removed, it is faster (etc) to

CREATE TABLE new LIKE real;
ADD PRIMARY KEY (...);   -- or UNIQUE
INSERT INTO new
    SELECT DISTINCT ... FROM real;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;


How to test whether unique index can be created in MongoDB? I want to add a new unique compound index to our production system. Before doing the change, I would like to check whether the field combination is unique, i.e. will the index creation succeed or fail. (The combination should be unique, but there's always the chance something has gone wrong earlier.)

How can I check whether creating a unique index would succeed, without actually doing the change?

You can use an aggregation query to check for duplicates before trying to create a unique index.

For example, if you want to create a unique index on fields a and b:

Insert some test data:

db.mycollection.insert([
    {a: 123, b: 456},
    {a: 123},
    {a: 123, b: 456},
    {c: 789 },
    {d: 321 }
])

Find documents with duplicate values:

db.mycollection.aggregate([

    // Group by the field combination you plan to add a unique index on
    { $group: {
        _id: { a: "$a", b: "$b"},
        count: { $sum: 1 }
    }},

    // Find any groups which are not unique (more than 1 matching document)
    { $match: {
        count: { "$gt": 1}
    }}
])

Aggregated output for the sample data above would indicate that there are two documents missing the expected fields a and b, and two documents with duplicate values of a and b:

{ "_id" : {  }, "count" : 2 }
{ "_id" : { "a" : 123, "b" : 456 }, "count" : 2 }

In this example I intentionally included some documents with expected fields missing, since those will be indexed as null values if you create a unique index. By default the unique requirement still applies even if one or more fields in the unique index is missing for a given document, because all documents in the collection are added to the index.

If you only want the field combination to be unique if both fields are present (or using some other criteria) you can create a Partial Index with Unique Constraint.


Is there a way to watch for schema changes in mySQL? ALL,

Is there a way to watch for a schema changes in mySQL? Or I will have to continuously query information_schema tabes?

TIA!!

If you use standard MySQL or Percona Server (not RDS, for example), you will be able to use Percona Audit Plugin. You can configure it to only log ALTER TABLEs, DROP TABLEs, etc, see this page:
https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html#filtering-by-sql-command-type

MariaDB has its own plugin, with a very similar functionality to filter queries (it allows to only log DDL).


REGEXP does not show the right result? I have in the name column:

3 boxes of frogs
7 boxes of frogs
48 boxes of frogs

When I execute:

SELECT name FROM items WHERE name REGEXP "[^1-5] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

When I tried:

SELECT name FROM items WHERE name REGEXP "[^1-50] boxes"

and

SELECT name FROM items WHERE name REGEXP "[^1-48] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

and

7 boxes of frogs

...respectively.

Why won't "3 boxes of frogs" show with REGEXP "[^1-48] boxes and others.
The result of REGEXP "[^1-48] boxes and REGEXP "[^1-50] boxes is so weird.
Can someone explain why?

Regex doesn't work like that. You cannot specify numerical range.

  [] brackets specifies character sets to match. ^ character inside
  [] means negation = anything outside the specified character set. I
  assume you wanted to indicate the start of the string which is also
  marked with ^ but outside of the [] part.

[^1-5] means any character which is not a number between 1 and 5
[^1-50] means any character which is not a number between 1 and 5 and not 0

In more details:

[^1-5] boxes

7 boxes of frogs matches because 7 is not a number between 1 and 5 followed by a spaces and the word boxes

48 boxes of frogs matches because 4 doesn't match (regular machine not in the first step) -> 8 is not a number between 1 and 5 followed by a spaces and the word boxes the actual match is: 8 boxes of frogs

[^1-48] boxes

Doesn't match 48 boxes of frogs because the rules specifies now characters from the set of (1,2,3,4,8) and the string fails on this check.

Fix your regex

In your case probably you want to use name regexp '^[0-9]+ boxes of frog'


Debugging SSAS Timeouts I have a drill-through problem apparently related to security. Users in one role are seeing timeout failures when trying to invoke a drill-through action. They have permission on the action through that role and seem to have all necessary dimension and cell security rights.

Profiler is not showing me any obvious reason this is failing. What else can I use to debug this?

If I modify the user's role membership, the action works as expected, so I'm confident this is related to security somehow.

In my specific case, this was a result of cell security. The role did not have access to all the fields returned in the drillthrough action. I missed this because there are two very similarly-named fields and my eye did not notice. It's still odd that a security denial creates a query timeout, but at least this note is here for the next person.


SQL Server Truncates Transaction Logs with Copy Only Backups I have a database that is set to "Full" recovery mode. The database doesn't show that a backup of the transaction log has ever been made.  backup_finish_date shows null for my logs.

I take backups nightly of the database Full (Copy Only). The backup appears to truncate the transaction log. The transaction log is about 40GB.  Before the backup it is 75% full.  When the backup runs, it is only 1% full. Using DBCC SQLPERF(logspace).

The sys.databases shows: recovery_model_desc FULL, log_reuse_wait 0, log_reuse_wait_desc NOTHING.

I have a copy of that database on a different server, same setup, but the transaction logs are NOT truncating.  Just growing.

The sys.database on the copy shows FULL, 2, LOG_BACKUP.  Which is what I would expect.

I understand the copy of my database is the way things should work.
But would like to know why/how the logs are being truncated on my production environment.

No Mirroring, No Transaction Log Shipping.

I recommend you use the code in one of the two existing answers to validate that t-logs are not being taken.  Depending on the access your users have, someone may be taking backups without your knowledge.

There was a comment and link by Denis Rubashkin talking about Pseudo-Simple SQL Server Recovery Model Essentially it says that if you are in full recovery and have never taken a full backup (or otherwise disrupted the LSN chain) , SQL knows there is no way to recover, and it does not save the t-logs.

But given your description:

  the backup appears to truncate the transaction log. The transaction log is about 40GB. Before the backup it is 75% full. When the backup run, it is only 1% full.

I don't think either of the two possibilities above are the cause of you issue.

I suspect that your backup job sets the database to SIMPLE recovery at some point in the process, this would clear the t-logs.  Then it sets the database back to FULL recovery. This scenario would create the symptoms you are seeing.

Look through the backup code, if you find that a change to SIMPLE and back to FULL is occurring, you will want to change something.  Either leave the database in SIMPLE recovery all the time, or take regular t-log backups.  Which choice you make is a business decision, dependent on your recovery objectives.

There is no good reason to be in full recovery if you are not taking t-log backups.

Edit I just noticed another clue in your question.

  nightly of the database Full (Copy Only).

Why are you taking Full (Copy Only) backups?  The only reason to do this is you are taking a single backup and want to preserve the backup chain when you are relying on differential backups for your your recovery.  See Copy-Only Backups

This clue suggests that there are Full, Differential and t-log backups occuring that you are not aware of, it would suggest that whomever wrote your nightly backup job as copy only was aware of the differentials and wanted to preserve the backup chain. If this is the case, I would expect t-logs to be taken several times a day, the normal scenarios is Fulls once per week, Differentials the other 6 days a week, and t-logs regularly through the day.

Or it could mean that the person who put the change to SIMPLE and back to FULL just randomly added it because they did not know the reason or impact of copy only either.


Find clients who bought more than 1 million units in 3 consecutive months I want to find the client's code (cCode), who bought more than 1 million units, in three consecutive months.

Here is my data:

gDate        cCode        buy
2014-10-22  15108164    1653410
2014-12-22  15108164    1653410
2014-10-22  16100286    1255720
2014-11-22  16100286    1739850
2014-12-22  16100286    1255720
2014-09-22  12400573    1336000
2014-10-22  12400573    1336000
2014-12-22  12400573    1336000
2014-12-22  12600125    3592800
2014-10-22  12600125    1266200

where cCode is the client's code, or ID.

Result must:

------------------------------------------------------------
cCode       2014-09-*   2014-10-*   2014-11-*   2014-12-*   |
------------------------------------------------------------
15108164        -           Yes         -           Yes     |
16100286        -           Yes         Yes         Yes     |  <- OK
12400573        Yes         Yes         -           Yes     |
12600125        -           Yes         -           Yes     |
------------------------------------------------------------
Answer is(cCode) : 16100286

I update function

DELIMITER $$

CREATE FUNCTION `milad`(D date,I int ,mMonth int ,minBuy int) RETURNS tinyint(1)
BEGIN
DECLARE result TINYINT Default 1 ;
DECLARE m_counter INT Default 0 ;

myloop: WHILE m_counter < mMonth DO
 if((SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL m_counter month))
   and month(gDate) = month(DATE_ADD(D,INTERVAL m_counter month))
   and buy>minBuy
   and cCode=I)=0) THEN SET result = 0;
 END IF;
 SET m_counter = m_counter+1;
END WHILE myloop;

IF(result=1)
 THEN RETURN true;
 ELSE RETURN false;
end if;
end

Before the edit

I created a table to test here the create code ( you will have to alter your table and add unique identifier for this to work )

CREATE TABLE `milad` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cCode` int(10) unsigned NOT NULL,
 `gDate` date NOT NULL,
 `buy` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
)

Then I inserted the date you used and test with 3 days not 3 months

SELECT t2.cCode,sum(t2.buy) AS total
FROM milad As t1
INNER JOIN milad AS t2
ON t1.cCode=t2.cCode
WHERE t1.gDate<=DATE_ADD(t2.gDate,INTERVAL 3 day)
group by (t1.id)
having total>1000000
ORDER BY t1.cCode , total DESC

Here is the result

+----------+---------+
| cCode    | total   |
+----------+---------+
| 12400573 | 4008000 |
| 12400573 | 2672000 |
| 12400573 | 1336000 |
| 12600125 | 4859000 |
| 12600125 | 3592800 |
| 15108164 | 3306820 |
| 15108164 | 1653410 |
| 16100286 | 4251290 |
| 16100286 | 2995570 |
| 16100286 | 1255720 |
+----------+---------+

After the edit

SELECT t1.cCode
FROM milad As t1
INNER JOIN milad AS t2
ON t1.cCode=t2.cCode
and (
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 1 month))
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 1 month))
  )
  or
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 2 month))
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 2 month))
  )
)
group by t1.cCode,t1.gDate
having count(t1.gDate)=2

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

Another solution using a function

Function code

DELIMITER $$
CREATE FUNCTION milad(D date,I int) RETURNS boolean
BEGIN
if
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 1 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 1 month))
    and buy>1000000
    and cCode=I
 )>0
and
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 2 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 2 month))
    and buy>1000000
    and cCode=I
 )>0
THEN RETURN true;
ELSE RETURN false;
end if;
end$$
DELIMITER ;

Then just use this query

SELECT cCode
FROM milad
where milad(gDate, cCode)

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

This will work if there are two rows in same month for the same person.

Please if someone can do it without a function, write the answer and mention me.


How to setup solution for multi-dimensional distance search with SQL Server spatial data types I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows.

After some searching I came across the SQL Server spatial data types where you can define a geometry based on Point or MultiPoint where you can search with a distance. I think this might be a solution but I need some help to get started in the right direction.

My current table structure is as follows where each row contains 2 (eventually more) XYZ values:

    | ID | X1  | Y1  | Z1  | X2  | Y2  | Z2  |
    | 1  | 1.1 | 2.2 | 5.1 | 1.2 | 2.1 | 4.1 |
    | 2  | 3.2 | 5.1 | 4.1 | 3.2 | 3.1 | 3.1 |
    | 3  | 4.1 | 2.3 | 6.3 | 4.2 | 4.1 | 2.1 |
    | 4  | 2.4 | 3.5 | 2.1 | 3.2 | 2.1 | 4.1 |

Im performing search with a tolerance for each X, Y and Z value.

So for example:

    (pseudocode)
    declare targetXYZ1 = (4.1, 2.2, 3.1);
    declare targetXYZ2 = (2.8, 2.2, 4.2);

    declare toleranceXYZ = (2,2,2)

    Select ID from MyXYZTable tb
    Where
           Math.Abs(tb.X1 - targetXYZ1.X1) < toleranceXYZ.X
       AND Math.Abs(tb.Y1 - targetXYZ1.Y1) < toleranceXYZ.Y
       AND Math.Abs(tb.Z1 - targetXYZ1.Z1) < toleranceXYZ.Z

       AND Math.Abs(tb.X2 - targetXYZ1.X2) < toleranceXYZ.X
       AND Math.Abs(tb.Y2 - targetXYZ1.Y2) < toleranceXYZ.Y
       AND Math.Abs(tb.Z2 - targetXYZ1.Z2) < toleranceXYZ.Z

After some analysis I would probably need to make a table with 1 or 2 geometry columns for xyz1 and xyz2 with geometry::Point(0, 0, 0)

My questions:

Is there a solution possible with SQL Server spatial types?
Do I need to use nearest neighbor search or can I use STDistance
Do I need to setup a spatial index for the table?

Any advice or tips and tricks are very welcome!

There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value:

  Z-coordinates are not used in any calculations made by the library and are not carried through any library calculations.

This is by design. Don't think of SQL spatial objects as "true" 3D-geometric objects, think of them as map coordinates with an "elevation tag" that isn't factored into any calculations. So the distance function STDistance() works great for "map distances", but not true 3D distances.

You can define the Z value (and the "M" value) of a point, but it isn't used by any calculations.

To demonstrate:

DECLARE @a geometry = geometry::Parse('POINT(0 0 0 2)');
DECLARE @b geometry = geometry::Parse('POINT(1 0 5 1)');
SELECT @a.Z, @b.Z, @a.STDistance(@b)

will return the values

0   5   1

Clearly the distance between these two points should be more than 1, if it used the Z properly. So, they may not work like you hope.

But if you're ok with ignoring the Z value, then performing these calculations will be pretty straight-forward. You could read these values from your existing table like so:

SELECT geometry::Point(X1,Y1,0).STDistance(geometry::Point(X2,Y2,0))
From myTable;

(That 0 in this use is not a Z value, it's an SRID.)

or, if you take the time to change how they're actually stored:

CREATE TABLE myPoints (ID INT, P1 geometry, P2 geometry)

INSERT myPoints
SELECT 1, geometry::Point(X1, Y1, 0), geometry::Point(X2, Y2, 0)
FROM oldTable

SELECT ID, P1.STDistance(P2)
FROM myPoints

There is some related discussion in one of my older answers here.

EDIT: Some further thoughts:

Regarding performance, I have absolutely no clue how geometry objects will compare, performance wise, to you doing the math manually. I imagine converting to Geometry objects on the fly will have a certain degree of overhead, but perhaps converting how your raw data is stored will do better. Testing will be key here.
The Z parameter might not be used by the STDistance() function, but clearly you can manually query Z, and use it to do that part of the math yourself, just use Pythagoras: True3D_Distance^2 = STDistance^2 + (a.Z-b.Z)^2
But note that if your goal is to simply rank the points by closeness, and you don't need to calculate the actual distance, you can simplify your calculation. I'm thinking something like WHERE PointA.STDistance(PointB) + ABS(PointA.Z-PointB.Z) < tolerance or perhaps WHERE PointA.STDistance(PointB) < tolerance AND ABS(PointA.Z-PointB.Z) < tolerance


Domain Admin Account Accessing SQL Server I was monitoring some SQL calls using SQL Server Profiler and noticed a Domain Admin account accessing SQL quite a bit.  We do not have a DBA at this point.  There are two developers and I am one of them.  Is this a bad practice and/or a security risk?  I ask because the latest rash of ransomware attacks have a strong emphasis on hijacking Domain Admin account privileges so I am concerned.

You could use the following query to provide some quick details about the domain admin:

SELECT des.session_id
    , des.host_name
    , des.login_time
    , des.is_user_process
    , des.last_request_start_time
    , dest.text
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
WHERE des.login_name = N'DOMAIN\AccountName'

It'll show the name of the client machine where they are connecting from, and the last statement they executed.

In general, you probably want to explicitly control who has access to the SQL Server, especially for security-sensitive accounts such as members of the sysadmin and securityadmin server roles.  The principle of least privilege applies.

This query will show you the members of each server-level role:

SELECT spr.name
    , spm.name
FROM sys.server_principals spr
    INNER JOIN sys.server_role_members srm ON spr.principal_id = srm.role_principal_id
    INNER JOIN sys.server_principals spm ON srm.member_principal_id = spm.principal_id
ORDER BY spr.name
    , spm.name;

In general, this list should be as small as possible.  Pay particular attention to the securityadmin and sysadmin roles.

As an aside, you want to limit the number of people who have access to the Domain Admins AD group since members of that group could restart your SQL Server in such a way that they can gain access to it, even if they haven't been explicitly granted access.  There are a lot of security implications to be aware of domain-wide for highly privileged groups such as the Domain Admins.


How to generate documentation from stored procedures? I'm working with a new set of tables and an increasing list of stored procedures.

Is there a way to generate documentation out of the comments on tables and stored procedures, like autodoc or doxygen?

Note: I'm using mysql.

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA.

Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

and

SHOW PROCEDURE STATUS; -- will give you all procedures available.

SHOW CREATE PROCEDURE procedure_name; -- will give you details about a given procedure.

A good discussion of the topic is available here which shows the problems associated with the various commands.

Extending slightly the example given for the sample alluser() procedure as shown in the link above. I added comments with # and --.

DELIMITER //

CREATE PROCEDURE alluser()

# a proc to show all users
BEGIN
   SELECT *  -- selecting from the users' table
   FROM users;
END //
DELIMITER ;

What I found was that the comments are NOT returned using either SHOW PROCEDURE STATUS or SHOW CREATE PROCEDURE alluser; NOR the SQL statement above returns the comments.

[EDIT - I have further found that comments are not returned with SELECT * FROM Information_Schema.Routines.

I searched the system and found the field ROUTINE_COMMENT in INFORMATION_SCHEMA.ROUTINES - but it didn't return the comments either. What this field is for, I don't quite know - maybe it's not implemented yet?

I would respectfully suggest your best bet is to document as you go - put "full documentation for the procedure" as part of your testing and UAT framework.


Returning only duplicated data I'm searching all over the internet and stack Overflow but every example I find is with only one row, or it just does not work.

I have this table:

TBL_RELATORIOS_TAMANHOS

table_catalog
table_name
column_name
data_type
character_maximum_length
numeric_precision
numeric_scale

Inside it I have all columns from all tables. I need to find only those columns that has a different precision, size or etc.

With row_number I got this:

;WITH CTE AS
(
SELECT DISTINCT table_name 'Tabela',
       column_name 'Coluna',
       character_maximum_length as 'Tamanho',
       numeric_precision as 'Precisao_Numerica',
       numeric_scale 'Escala_Numerica'
FROM TBL_RELATORIO_TAMANHOS
)
SELECT *,
    ROW_NUMBER() over ( partition by Tabela,Coluna order by Tabela,Coluna)
FROM CTE
ORDER BY 1,2

and with this, I can see columns that has a 2 on it, so this one is duplicated ( same column, different size, precision or etc ).

| table_Name              | Column_name      | character_maximum_length | numeric_precision | numeric_scale | Row_number |
|-------------------------|------------------|--------------------------|-------------------|---------------|------------|
| ACOES                   | ID_BMF           | 20                       | NULL              | NULL          | 1          |
| ACOES                   | NO_ACAO          | 20                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CODIGO_MITIGADOR | 40                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 10                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 25                       | NULL              | NULL          | 2          |
| AJUSTE_CODIGO_MITIGADOR | VALOR_PADRAO     | 1                        | NULL              | NULL          | 1          |
| AJUSTE_POPR             | CD_ELEMENTO      | 10                       | NULL              | NULL          | 1          |

As you can see, CONTA_DLO appears 2 times. So, how the hell can I list only those columns with more then 1 row_number, but returning all of them ( all duplicates ). in this case. it should return CONTA_DLO with row_number 1 and 2.

So, you want to show only rows that there is more than one with same table and column name.

We can use COUNT(*) OVER (PARTITION BY table_name, column_name) to count how many rows have the same table and column name and then WHERE ... >= 2. Also:

I don't think you need the DISTINCT.
If you do need the DISTINCT there, it can be with either one more CTE to apply it or use GROUP BY as below.
Please don't use single quotes for table and column aliases. If you need to, use square brackets or double quotes.

The query:

  ;
WITH cte AS
(
    SELECT
        table_name               AS Tabela,
        column_name              AS Coluna,
        character_maximum_length AS Tamanho,
        numeric_precision        AS Precisao_Numerica,
        numeric_scale            AS Escala_Numerica,
        COUNT(*) OVER (PARTITION BY table_name, column_name)
                                 AS Count_Duplicates
    FROM TBL_RELATORIO_TAMANHOS
    GROUP BY
        table_name, column_name,  character_maximum_length,
        numeric_precision, numeric_scale
)
SELECT
    cte.*,
    ROW_NUMBER() OVER (PARTITION BY Tabela, Coluna) AS Rn
FROM cte
WHERE Count_Duplicates >= 2
ORDER BY Tabela, Coluna, Rn
  ;


How do I allow remote mysql access to all users? I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure mysql for the same?

I know about the GRANT ALL PRIVILEGES command, but that would require me to individually grant remote access to all the users.

I highly recommend that you do not run

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with

mysqladmin -hIP_of_DB Server -uusername -p shutdown

You also do not want the SUPER privilege given remotely to just anyone. Why ?

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

If you have multiple users and want them to access specific databases, you need to create the user with database-level privileges. The SHUTDOWN and SUPER privileges are not part of database-level grants (See my post MySQL user without SUPER privilege as to why)

Suppose you have a database called mydb and you want to access it remotely. Do this:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%';

I have a better idea for you. You should restrict remote users by their netblocks

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.40';

Note that you will have to drop 'username'@'%' first before creating DB-level user access.

See my post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for full clarification on MySQL's User Authentication Paradigm

After you have created the needed users, make sure your firewall has port 3306 open.


SQL Server Differential Backup Bloat I perform daily full backups of all databases. Differential backups are performed every hour.

The issue I am having is that around the same time each morning, a few hours after the full backup, the differential backup sizes increase from 20MB to 250MB.  How do I track down what is causing a 200MB jump in the backup size?

This mostly causes problems in backup retention since the total size then balloons dramatically throughout the day.

If the full backup just grows by a few MB but your DIFF is 200MB then you're probably doing a lot of UPDATES which change the page, thus forcing the page to get copied to disk.  However, full backup size won't change much as it copies all the pages anyways.  This is ignoring the transaction log which in the comments you've already specified has very little fluctuation.  Are you perhaps compressing your full backup but not your diff?  Filestream?

At 8K a page you're looking at 25,000+ pages changed.  Is this a high percentage of your database or table size?  Is it very UPDATE heavy?  That's most likely what's causing it based on the available info.

Can you record a extended event session or trace for UPDATES and also check to see what tables are the most updated?  If for some reason you simply are not able to review the data live perhaps you can keep recording 'last updated' on the table and see which one get's a lot.

This is assuming the cause is from DB activity and we simply can't collect the changes live to troubleshoot.  That's one way I'd deal with it, or open up the backup file and see what pages it has


migrating tables from Postgres to SQL Server 2008 (I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...)

I have a Postgres 8.4.x database with over 100 tables.  I need to recreate these tables in SQL Server 2008.  I'm not concerned about the data, just the structure.

Are there any slick tools, shortcuts or suggestions to accomplish this?  Heck, I'll even take the find-and-replace values to run on Postgres CREATE TABLE scripts!

Thanks!

We found a solution for this that ended up being super-fast: using a wizard in our ETL tool Pentaho Data Integration (Kettle).  Open a new job then Tools -> Wizard -> Copy Tables.  I wouldn't be surprised if other ETL tools had similar wizards.


Combining Always Encrypted AND Column level encryption in SQL Server 2016 We have requirements to encrypt sensitive column data using SQL Server 2016 and selected the Always Encrypted(AE) feature to encrypt those columns using deterministic approach..

Since, the AE deterministic encryption doesn't allow inequality, range, or LIKE queries on these encrypted columns, we have tried of doing encryption for these type of columns using the symmetric key (column level) encryption technique.

Is it a good practice to implement the AE feature on certain columns(which doesn't need any inequality, range, or LIKE queries ) and symmetric key type encryption on columns where inequality, range, or LIKE queries needs to be generated?

Is it a good practice to combine the AE encryption and column level encryption on a single table considering the performance, security and maintenance?

Experts advice please.

I have yet to come across a definitive "Best Practices" list regarding multiple encryption types for SQL Server, but in your situation I would probably recommend the following:

Encrypt necessary data using Always Encrypt

Its the new hotness. You're on 2016, enjoy it!
Better security - the DBA can't decrypt data since the key is stored outside the database.
More options for tuning individual queries and reducing the performance hit of the feature.
Easiest to implement on both the database and app side (unless TDE is an option) since you only have to configure driver parameters versus changing your queries as with Column Level Encryption.
The additional decryption load becomes the application's (specifically the driver's) problem (fine for me as a DBA).
This feature is likely to be improved and optimized for a while since it is a new (my opinion).

For columns you need to search on, create a hash value to do lookups on.

Maintains security of the data but is very efficient for lookups. It allows you to have the performance of a searching on a normal row without the overhead of decrypting every row or defaulting to a table scan. You can index it for further performance if desired.

Your app will take the user input and hash it, then use that hashed value to query the hashed version stored in the table. This lookup will be fast and will allow you to only decrypt the desired row(s) versus decrypting rows you don't actually want as part of your result set.

Design of this depends on how many columns you need to look up and if you're using multiple or individual ones in your WHERE clauses.
Extra space is worth it for performance as long as you can afford it.

Don't bother using Column Level Encryption

You'll have to decrypt the entire column to do searches, which will be less efficient than an un-encrypted hash column. This means the larger your table, the more inefficient this method becomes. 1 million rows means you have to spend the CPU to decrypt a million rows even if your filter is only returning 1 row. Just because you can perform a search doesn't mean it will be performant.
I can see long term maintenance of two encryption features within the same table being confusing and becoming at best tribal knowledge.

You can also implement #2 with Column Level Encryption as well, but without any constraints in place, I can't see why you'd choose CLE over Always Encrypted in general.

I joke about making encryption the application's problem, but I find that the database often gets saddled with a lot more business logic and wacky functionality than it should be in most cases. If there is an option to push some computation back to the application side, I've found that is generally the best option and helps prevent troubleshooting and general tuning from becoming a nightmare.

Of course you should fully test the alternatives. If you find CLE, or a combo, works best for your particular scenario, so be it.

Additional reading:

Always Encrypted Performance - Aaron Bertrand
Always Encrypted Performance - A follow Up - Aaron Bertrand
Column Level Encryption Overhead - Dallas Snyder
Always Encrypted (Database Engine) (official documentation landing page)


Oracle Import problem caused by different character sets I'm trying to import an Oracle 11 export into Oracle 11 XE.

I get the following messages:

  import in XE fehlerhaft import done in WE8MSWIN1252 character set and
  AL16UTF16 NCHAR character set
  import server uses AL32UTF8 character set (possible charset conversion)

Any ideas, how I can import this dump into Oracle 11 XE ?

Edit:

Given a table

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

I get errors like this

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "BDATA"."ARTIKEL"."ABKUERZUNG" (actual: 6, maximum: 5)
Column 1 ABL
Column 2 Aufbewahrungslösung
Column 3 AfbLö

Some rows are missing from the import.

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter.  If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character).

Unfortunately, changing the NLS_LENGTH_SEMANTICS probably won't be terribly helpful if you're relying on the import process to create the table-- the dump file will inherently add the CHAR or BYTE keyword so it would actually issue the statement

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);


Query on SCAN in RAC I have a two node RAC (Oracle 12c) on linux. I have 5 SCAN listeners configured.

Currently, if I do a srvctl config scan I notice that out of the 5 scan listeners, one scan is running in one node and the remaining 4 in the another node.

Is it necessary for me to relocate one or two listeners running on node 2 to node 1?

Will my connections still be load balanced even if I have no scan listeners running in node 1 and all the 5 are running in node 2? If yes, how?

When does one actually need to relocate the scan to another node?

Is it necessary for me to relocate one or two listeners running on node 2 to node 1?

Its not necessary to relocate these SCAN listener manually, these will be failed over to another node automatically.

  Will my connections still be load balanced even if I have no scan listeners running in node 1 and all the 5 are running in node 2? If yes, how?

SCAN resolves in one of the IP addresses(In your case, its five) in round-robin fashion, so we don't need to worry about the load-balancing on SCAN.

Oracle strongly recommends that you do not configure SCAN VIP addresses in the hosts file. If you use the hosts file to resolve SCAN name, you can have only one SCAN IP address.

Once the SCAN is resolved it tries to find least loaded oracle instance to hand over client connection request to local listener, in your case you have two local listeners running on two different nodes.

I would strongly recommend the following white paper published by Oracle to grasp more information about SCAN in Oracle RAC.

  When does one actually need to relocate the scan to another node?

If the node on which a SCAN VIP is running fails, the SCAN VIP and its associated listener will fail over to another node in the cluster. In your case you have two nodes so one pair of resource(SCAN VIP and listener) is running on one node and rest of these are running on another node. Once your first node hosting the SCAN VIP and listener goes down Oracle Clusterware relocates this resource to second node. If a node becomes available in the cluster again, the formerly mentioned dispersion will take effect and relocate one set accordingly.
More...


Find MIN and MAX Database Backup duration I have this query to find the duration of backups taken which I want to run against registered servers in CMS across the entire enterprise.

The problem though, is how to find only the MIN and MAX duration per database rather than the runtime of all of the backups.

I've messed around a lot with MIN and MAX but have yet to find the correct combination.

Am I going about this the wrong way?

DECLARE @dbname sysname

SET @dbname = NULL

SELECT database_name AS [Database]
    , backup_start_date AS [Backup Started]
    , backup_finish_date AS [Backup Finished]
    , backup_size AS [Backup Size]
    , compressed_backup_size AS [Compressed Backup Size]
    , CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
     + CAST((CAST(DATEDIFF(s, backup_start_date, backup_finish_date) AS int))%60 AS varchar)+ ' seconds' AS [Total Time]
FROM msdb.dbo.backupset
WHERE [type]!='L'
    AND database_name IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name
    ,backup_start_date
    ,backup_finish_date
    ,backup_size
    ,compressed_backup_size;

Code sourced from here.

Use the ROW_NUMBER() aggregate to get the min and max rows, then show only those rows:

;WITH cte AS (
    SELECT d.name
        , bs.backup_start_date
        , bs.backup_finish_date
        , DurationInSeconds = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        , RnMin = ROW_NUMBER() OVER (PARTITION BY d.name
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) ASC)
        , RnMax = ROW_NUMBER() OVER (PARTITION BY d.name
               ORDER BY DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) DESC)
    FROM msdb.dbo.backupset bs
        INNER JOIN master.sys.databases d ON bs.database_name = d.name
)
SELECT cte.name
    , cte.backup_start_date
    , cte.backup_finish_date
    , cte.DurationInSeconds
FROM cte
WHERE cte.RnMax = 1
    OR cte.RnMin = 1
ORDER BY cte.name
    , cte.backup_start_date;


Backup to Azure Blob Failing with Hallengren Framework We are running IAAS VMs in Azure using SQL Server 2016 SP1 CU2.  We are backing up to blob storage using a URL, and the errors received are shown below and described in the MS Connect URL provided.  The defect is marked as corrected in the CU that we are running.

  1117(The request could not be performed because of an I/O device error.)

https://connect.microsoft.com/SQLServer/feedback/details/3119350

According to the article, the workaround is to specific MAXTRANSFERSIZE. When I provide this parameter(@MaxTransferSize = 4194304), the job starts failing with the error

  The value for the parameter @MaxTransferSize is not supported.

I dug into the stored procedure to find the “OR” condition (@MaxTransferSize IS NOT NULL AND @URL IS NOT NULL).  The complete line from the stored procedure is shown below.  Was there a reason for this last condition to be added as Microsoft is recommending this use.  I temporarily commented out that condition and the job succeeded.

IF @MaxTransferSize < 65536 OR
   @MaxTransferSize > 4194304 OR
   @MaxTransferSize % 65536 > 0 OR
   (@MaxTransferSize > 1048576 AND @BackupSoftware = 'SQLBACKUP') OR
   (@MaxTransferSize IS NOT NULL AND @BackupSoftware = 'SQLSAFE') OR
   (@MaxTransferSize IS NOT NULL AND @URL IS NOT NULL)

I’m wondering if this last condition should be removed.

I have added support for BLOCKSIZE, and MAXTRANSFERSIZE, when backing up to Azure Blob Storage block blobs (using Shared Access Signature).

https://ola.hallengren.com/versions.html


Sql Server & NetApp Sql Snap Manager Does anyone use netapp's sql snapmanager?  We are in the process of trying to decide if it is right for us.  I know of the more popular features, but am trying to get some real life experience stories.  Can anyone answer the following:

What problems are you having?
How has it helped in ways you didn't expect?
Can you offer any advice for or against using it?
Was configuring more difficult than expected?
How has your maintenance on Sql changed?

I'm also interested in finding out how quickly they support the new versions of sql, I will try to find that on netapp's site, but if you have any information, that would be great.

Any thoughts are appreciated.

I used snapmanager with SQL 2005, it worked really well.  I would take a snapshot, swing it over, mount it on our reporting server, and voila.

The only problem I had is that most of what I wanted to do required undocumented NetApp commands.  Once I got them, I just put them into a SQL job and our reporting environment was refreshed daily, and used less space on NetApp.


What problems are solved by splitting street addresses into individual columns? We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the size of our organization and how the needs or our clients change over time. There is only one area I'm not clear about the reasons behind their design decision: addresses.

While this mostly focuses on addresses in the United States, I think this could apply to any country that does this. Each piece of an address gets its own column in the addresses table. For instance, take this gnarly U.S. address:

Attn: Jane Doe
485 1/2 N Smith St SW, APT 300B
Chicago, IL 11111-2222

It would get split up in the database like this:

Street number: 485
Street fraction: 1/2
Street pre-directional: N (North)
Street name: Smith
Street type: ST (Street)
Street post-directional: SW (Southwest)
City: Chicago
State: IL (Illinois)
Zip code: 11111
Zip4 Code: 2222
Country (assumed to be U.S.A.)
Attention: Jane Doe
P.O. Box: NULL
Dwelling type: APT (Apartment)
Dwelling number: 300B

And there would be a few other columns related to rural routes and contract routes. Furthermore, our specific application will likely have a few international addresses in it. The data modelers said they would add columns specific for international addresses, which would be the normal line 1, line 2 fields.

At first I thought this was WAY overboard. Researching online repeatedly refers to using address line 1, 2, 3 and possibly 4, then splitting out city, region and postal code. We do have one use case for our new application where this granularity is beneficial. We have to validate that the user is not creating a duplicate business, and checking the address is one of the validations. We can get it to work with address line 1 and 2, but it would be more difficult.

As for our specific application, we need to store multiple kinds of addresses for businesses and people (physical, mailing, shipping, etc). We might need to generate printable form letters, but that requirement hasn't been discussed so far.

Some other things applications in our organization need to support:

Auditing (with full history tables)
Printing mailing labels
Generating printed forms
Reporting (for national and regional governments)

While our application might not be doing everything that every other application is doing, splitting addresses into multiple components is an enterprise standard where I work. Regardless of whether our application would benefit from it, we are forced to do this.

Semi related StackOverflow question: Where is a good Address Parser which was closed, but illustrates how difficult parsing addresses can be.

In order for me to better understand their design decision, and to sell our client on the idea...

What problems are solved by splitting the street address into individual columns?

Bonus points for anyone who has implemented a system like this, because they ran into problems.

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists.  It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise.

Every locality can have its quirks, and that's just in the US. Throw in other countries and things get unmanageable very quickly for any approach that wants to parse every address.  Just two examples:

In Spain, the street number always comes after the street name and a comma, and many addresses contain a floor number ordinal, such as 1° or 3ª, along with abbreviations for "left" ("Izda" meaning left-hand-door after you get up the stairs), "right" ("Dcha") or other possibilities.  Now multiply that quirkiness by the number of different countries and areas with different historical customs for addresses... (Japan? Rural England? Korea? China?)

In Portland, OR, there are N-S and E-W axes that divide the city into NW, NE, SW and SE quadrants (as well as a N "quadrant", but I digress).  N-S streets are numbered incrementally East and West from this axis, and addresses on E-W streets are dictated by the N-S street number being the "hundred block" of the number (i.e. a house on an E-W street between 11th and 12th avenues would have a number like 1123).  Pretty standard stuff for US addresses.

Every so often you run into a Portland address like 0205 SW Nebraska St.  A leading zero? WTF? There goes my integer column for house "number".

When the grid was set up, the N-S axis was defined by the Willamette river.  Everything to the East of the river was NE or SE, and West of the river NW or SW.  As the city grew south they ran into the inconvenient fact that the river meanders to the East, so projecting the axis South you have this problematic area that's on the "West" side of the river but East of the axis.  The solution was to add a leading zero, in effect a minus sign, with the numbers incrementing towards the East from the axis line.

If I were you I'd give up hope of designing the ultimate system.  You cannot cover all possibilities, and new ones will be created as humanity pushes into previously undeveloped land.

For US addresses, take a look at what the USPS has already done in address standardization, and remember to make the house_number column a varchar. While you're at it figure out how you're going to parse 1634 E N Fort Lane Ave.

For the rest of the world, I'd probably try to abstract additional fields to cover 80-90% of what is likely to come up, and provide a set of uninterpreted fields that can handle everything else when necessary. I.e. if your parser fails to handle an address, save it unparsed and flagged as such.  If you do manage to parse an address, make sure you remember the order in which you found the various fields so you can reassemble it into something deliverable.

I was going to say that the most important field is going to be post code, but even that is not a given in many places.

Good luck.  This can be a fun and extremely frustrating endeavor but the key to sanity is to know when to quit trying and just store the input unparsed, or partially parsed with the original input as backup.


Improper removal of SQL Server cluster A node was evicted from a windows cluster without first uninstalling the sql first. When the node was put back, an error was produced when we tried to failover. It was reporting a missing dll. Feels like removing the node before uninstalling sql from it has corrupted or deleted part of the build.

Has anyone else encountered this situation? Can this be repaired or is it best to uninstall the sql from the node and start again installing a new sql node.

Thanks!

I would first run a repair installation of your SQL Server installation on the node that was evicted.  This should at least get the appropriate binaries installed.  Then, run a Discovery Report to validate what is and is not installed.  If your SQL node installation is no longer available, the reinstall the node.


Best way to find out read/write distribution in MySQL? People often say things like "application X is write heavy", 20/80 write vs reads, etc.

What metric(s) is most relevant for MySQL when checking if an application is write/read heavy?

Is checking Com_insert, etc enough, not taking into account number of rows read/modified, or is it checking iostat metrics?

An application being read/write heavy is subject to interpretation based on the following:

How many SELECTs are launched : Com_select
How many UPDATEs, DELETEs, INSERTs are launched

Com_insert
Com_delete
Com_delete_multi
Com_update
Com_update_multi

Prepared Statements (could be a bottleneck if prepared statements are not parameterized)

Com_stmt_prepare
Com_stmt_execute
Com_stmt_fetch
Com_stmt_send_long_data
Com_stmt_reset
Com_stmt_close

How much client byte traffic mysqld sees

Bytes_received
Bytes_sent

How many bytes have been read

Innodb_data_read
Innodb_data_reads

How many bytes have been written

Innodb_data_writes
Innodb_data_written

Perhaps a combination of these

These things are meant to measure SQL command counts and the amount of data passing here and there. There are other metrics within the InnoDB storage engine that should be monitored and tuned so that any write heavy behavior is improved.

Rather than name those features of InnoDB, please go to the  Server Status Variables page and look for any status variable mentioning the Redo Log Writes and Buffer Pool read/writes.


Does making a field unique make it indexed? If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly accessible?)

Specifically, I'm working with Apache Derby for prototyping, although I will probably be moving it to MySQL in the semi-near future. I'm also hoping there might be something in the SQL standard that says something about this.

I will never have a need to search by this field, so I would rather not make a useless index. But I'd rather have a useless index than have an O(n) insert time.

--EDIT--

My original answer (below) is probably not useful to you at all because it does not address the question of unique constraints. As others have said, these constraints are usually implemented with an implied unique index. In special cases this might not be true (eg disable novalidate for Oracle).

The question could be: Is it possible to enforce uniqueness without an index? Generally speaking the answer is no though in some cases a Clustered Index will mean that the index and the table are the same object.

--END EDIT--

You said "I'd rather have a useless index than have an O(n) insert time.", but in general databases do not have O(n) insert time. There are two cases to consider:

A normal table with or without indexes:

New rows are dumped at the top of the heap. The RDBMS probably only looks at 1 block, so not just O(1) but very small O(1).

If the table has indexes, a pointer to the row will be added to each. This will usually be an O(log(n)) operation.
A table with some sort of clustering going on, eg an Index Organized Table or cluster for Oracle, or a Clustered Index for SQL Server and others:

New rows are inserted into a particular block, which may cause the block to split or overflow, but whatever happens it is still O(log(n)) or better, caused by the b-tree or similar structure used to find the block.


PostgreSQL 9.4 query seems very slow I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem except counting the rows takes almost no time at all.

Amazon AWS: It's running on an RDS t2.medium with a SSD (IOPS disabled) according to AWS console.

The table:

CREATE TABLE IF NOT EXISTS ip_range_domains (
  ip_range_domain_id     BIGSERIAL PRIMARY KEY,
  domain_id              BIGINT REFERENCES domains       NOT NULL,
  source_type_id         INTEGER REFERENCES source_types NOT NULL,
  low                    INET                            NOT NULL,
  high                   INET                            NOT NULL,
  auto_high_conf         BOOLEAN                         NOT NULL DEFAULT FALSE,
  invalidation_reason_id INTEGER REFERENCES invalidation_reasons  DEFAULT NULL,
  invalidated_at         TIMESTAMP WITHOUT TIME ZONE              DEFAULT NULL,
  created_at             TIMESTAMP WITHOUT TIME ZONE     NOT NULL DEFAULT current_timestamp
);
CREATE INDEX domain_id_btree ON ip_range_domains (domain_id);

I also created a hash index on it afterwards but it doesn't seem to have any effect.

The slow query:

SELECT * FROM ip_range_domains WHERE domain_id = 400266;

The query above took 224.9 Seconds:

=> SELECT COUNT(*) FROM ip_range_domains WHERE domain_id = 400266;
  count
---------
 1383530

The query above took 164 milliseconds to return:

=> SELECT COUNT(*) FROM ip_range_domains;
  count
----------
 23156869

The query above took 1.9 seconds to complete:

=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE domain_id = 400266;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_range_domains  (cost=26398.17..282905.77 rows=1410288 width=55) (actual time=94.046..476.018 rows=1383530 loops=1)
   Recheck Cond: (domain_id = 400266)
   Heap Blocks: exact=44000
   Buffers: shared hit=47783
   ->  Bitmap Index Scan on test_index_9  (cost=0.00..26045.60 rows=1410288 width=0) (actual time=85.699..85.699 rows=1383530 loops=1)
         Index Cond: (domain_id = 400266)
         Buffers: shared hit=3783
 Planning time: 0.122 ms
 Execution time: 697.753 ms
(9 rows)

Postgres Version:

=> select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

RDS Info:

db.t2.medium
IOPS: disabled

Mostly, this seems to be a misunderstanding. According to your query plan, you are retrieving rows=1410288 and the query itself is not that slow. It does not "take 224.9 seconds":

Execution time: 697.753 ms

You could probably improve the performance of your query some more by increasing the locality of clustered data, i.e., CLUSTER (or pg_repack) your table based on your index domain_id_btree (that appears under the different name of test_index_9 in your query plan). Details here:

Configuring PostgreSQL for read performance

If you do that (arriving at a favorable physical order of rows) and if you mostly just INSERT rows (UPDATE / DELETE is rare) and since there seem to be many rows per domain_id you might also benefit from a BRIN index (new in Postgres 9.5).

(It's remarkable that your current index is used at all, given that you retrieve ~ 6% of all rows. This indicates that your data is mostly clustered on domain_id already - or cost settings / statistics in your DB are inaccurate.)

But that's only going to reduce the 700 ms Postgres needs to execute the query. The rest of your reported 224.9 seconds are spent somewhere else, probably network transfer and processing in client.

Now, if you could retrieve fewer rows or less data per row ...


How to restrict MySQL user to not import and export database I have created a read only MySQL user but this user have an option to export the database through phpmyadmin. How can I disable the options Export and Import?

I believe phpMyAdmin uses plain SELECTs and INSERTs to import and export the database.

I don't think you will be able to prevent import/export.  I don't remember PhpMyAdmin having options to disable those features.