MySQL replication working, but data is out of date I'm trying to figure out why even though SHOW SLAVE STATUS isn't reporting any errors, nothing is replicating (see below):

both nodes are running:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

on

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
#

master:

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
            File: mysql-bin.000107
        Position: 228679653
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

slave:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_HOST
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000107
          Read_Master_Log_Pos: 228681259
               Relay_Log_File: mysql:PR_DB-relay-bin.000150
                Relay_Log_Pos: 228398599
        Relay_Master_Log_File: mysql-bin.000107
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: DB1,DB2,DB3,DB4
          Replicate_Ignore_DB: information_schema,mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 228681259
              Relay_Log_Space: 228681667
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

master:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    97786 |
+----------+
1 row in set (0.03 sec)

mysql>

slave:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    95678 |
+----------+
1 row in set (0.01 sec)

mysql>

at the initial time of setting up replication, SELECT COUNT(*) returned same values.

due to:

MySQL :: MySQL 5.1 Reference Manual :: 16.1.3.3 Replication Slave Options and Variables

Warning

To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

my.cnf's incorrect syntax:

--replicate-do-db=DB1,DB2,DB3,DB4

was replaced with correct syntax:

--replicate-do-db=DB1
--replicate-do-db=DB2
--replicate-do-db=DB3
--replicate-do-db=DB4


SQL Server and Over-Indexing Tables Possible Duplicate:
  Where can I find some guidance on index strategies?

I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many indexes to a table, because this can lead to performance problems with querying from the table.  With the release of SQL Server 2012, are these kinds of problems still a concern?

Yes, of course. Every index you create requires maintenance (it needs to be updated for every insert, update and delete operation) and occupies space. Indexes aren't free performance boosts, and should certainly be considered as a trade-off. You should also be very careful about throwing an index on a table to satisfy one query that is called once a week if that table is written all week long. In other words, don't look at the missing index DMVs or a single execution plan and judge the validity of a suggested index in isolation.


Is Oracle DB immune to the InnoDB deadlocks found in MySQL? It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction"

Does Oracle DB work around this issue? After all, Oracle owns InnoDB.

No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database.  The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to resolve the problem.  Your application ought to avoid creating a deadlock in the first place-- one option is to always generate locks in the same order in every session.


Gaps and islands with GPS breadcrumb data I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life.  Most of the GAI problems I've seen involve data in the form of:

[Id] [StartDateTime] [EndDateTime]

But mine in this case comes from GPS breadcrumbs/data points, so we don't have a start and end, just a timestamp.

What I want to do is the following:

Given a dataset that contains a truck number, a location, and a datetime, if a row shares a truck, and location and is within 1 hour of another row, then join together.

Trucks can run 24/7 (team drivers).

My dummy data would look like this (the duplicate rows are intentional as they are a feature of the real data):

DECLARE @TruckLoc TABLE
(
    TruckId VARCHAR(50),
    LocationName VARCHAR(12),
    LocDateTime DATETIME
)
;

INSERT INTO @TruckLoc (TruckId, LocationName, LocDateTime)
VALUES
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'),
('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:56:00.000')

The output that I'm attempting to get is the following:

Monster Truck | Home | 2019-10-22 12:57:40.000 | 2019-10-22 11:22:40.000
Monster Truck | Gas Station | 2019-10-22 12:43:00.000 | 2019-10-22 13:43:00.000
Monster Truck | Home | 2019-10-22 16:43:00.000 | 2019-10-22 17:43:00.000
Monster Truck | Home | 2019-10-22 20:43:00.000 | 2019-10-22 20:56:00.000

What I've tried:

I've tried everything.  In my real statement, I have them ranked by truck and datetime.  But that's the furthest success I've had.  I've gone up to try to use LAG/JOIN functions and CTEs but the crux of the problem is that I don't need to look at the previous record, but I need to look at all the previous records that satisfy a condition concerning other previous records i.e each record is within an hour of the preceding one.

Any help on this is greatly appreciated.

This was a tricky one. I've used PostgreSQL for the fiddle - you should be able to "translate" into SQL Server dialect - with a bit of modification, it'll work on any server that has Common Table Expressions.

I'll outline the steps. Full code as well as data are on the fiddle as well as at the end of this answer. There are a few "fossils" - i.e. extra fields that helped in the problem solving process that I haven't deleted, but they should be easy to clear up.

The first thing to do (a bit like relationships) is to establish boundaries.

Find the upper limits of the 1 hour differences between tranches of measurements - start by using the LEAD() function:

SELECT
  truck_id,
  location_name,
  loc_date_time,
  LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lead
FROM truck_loc
ORDER BY loc_date_time;

Result (for brevity, only 3 records are shown):

truck_id      location_name loc_date_time           the_lead
MonsterTruck       Home 2019-10-22 10:27:40 2019-10-22 10:27:40
MonsterTruck       Home 2019-10-22 10:27:40 2019-10-22 10:33:10
MonsterTruck       Home 2019-10-22 10:33:10 2019-10-22 11:22:40

Then, find the gaps which are greater than or equal to 1 hour:

SELECT t1.truck_id, t1.location_name, t1.loc_date_time
FROM
(
  SELECT
    truck_id,
    location_name,
    loc_date_time,
    LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead
  FROM truck_loc
) AS t1
WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR'
OR t1.the_lead IS NULL
ORDER BY t1.loc_date_time;

The OR t1.the_lead IS NULL test is there because the last LEAD() of a series like this is always NULL.

Result:

truck_id        location_name   loc_date_time
MonsterTruck             Home       2019-10-22 11:22:40
MonsterTruck      Gas Station       2019-10-22 13:43:00
MonsterTruck             Home       2019-10-22 17:43:00
MonsterTruck             Home       2019-10-22 20:56:00

Inspection shows that this is correct.

Then, you basically do the same again for LAG() - the code is in the fiddle (extra fields are "fossils")!

substr  location_name   loc_date_time           t           tlag            diff
Mon          Home   2019-10-22 10:27:40 1571740060
Mon   Gas Station   2019-10-22 12:43:00 1571748180
Mon          Home   2019-10-22 16:43:00 1571762580  1571743360  19220
Mon          Home   2019-10-22 20:43:00 1571776980  1571766180  10800

So, now you form 2 CTEs (Common Table Expressions) from the two queries above and run the following SQL:

SELECT
  SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2,
  SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2,
  c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2,
  (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff
FROM cte1 c1
JOIN cte2 c2
  ON c1.truck_id = c2.truck_id AND
     c1.location_name = c2.location_name
WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600
ORDER BY c2.loc_date_time;

Result:

tid_1   tid_2   loc_1   loc_2    ldt_1                   ldt_2                  diff
  Mon     Mon     Hom     Hom    2019-10-22 10:27:40     2019-10-22 11:22:40    3300
  Mon     Mon     Gas     Gas   2019-10-22 12:43:00     2019-10-22 13:43:00 3600
  Mon     Mon     Hom     Hom   2019-10-22 16:43:00     2019-10-22 17:43:00 3600
  Mon     Mon     Hom     Hom   2019-10-22 20:43:00     2019-10-22 20:56:00 780

Which is the desired result! Full SQL below. It requires a bit of tidying up, but I'll leave that to you - I've left the extraneous stuff in so that it will (should!) be easier to follow my thought processes as the solution evolved.

In this thread, even though I didn't answer the question asked, this might prove helpful in getting the hang of LAG() and LEAD(). Also you could take a look at the accepted answer in that same thread.

=============== Full SQL and DDL and DML for data ================

SQL:

WITH cte1 AS
(
  SELECT t1.truck_id, t1.location_name, t1.loc_date_time
  FROM
  (
    SELECT
      truck_id,
      location_name,
      loc_date_time,
      LEAD (loc_date_time, 1) OVER (PARTITION BY truck_id ORDER BY loc_date_time) AS the_lead
    FROM truck_loc
  ) AS t1
  WHERE t1.the_lead >= t1.loc_date_time + INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530
  OR t1.the_lead IS NULL
  ORDER BY t1.loc_date_time
),
cte2 AS
(
  SELECT t1.truck_id, t1.location_name, t1.loc_date_time,
         EXTRACT(EPOCH FROM t1.loc_date_time) AS t, EXTRACT(EPOCH FROM t1.the_lag) AS tlag,
         EXTRACT(EPOCH FROM t1.loc_date_time) - EXTRACT(EPOCH FROM t1.the_lag) AS diff
  FROM
  (
    SELECT
      truck_id,
      location_name,
      loc_date_time,
      LAG (loc_date_time, 1) OVER (PARTITION BY truck_id, location_name ORDER BY loc_date_time) AS the_lag
    FROM truck_loc
  ) AS t1
  WHERE t1.the_lag <= t1.loc_date_time - INTERVAL '1 HOUR' -- https://stackoverflow.com/a/13828231/470530
  OR t1.the_lag IS NULL
  ORDER BY t1.loc_date_time
)
SELECT
  SUBSTR(c1.truck_id, 1, 3) AS tid_1, SUBSTR(c2.truck_id, 1, 3) AS tid_2,
  SUBSTR(c1.location_name, 1, 3) as loc_1, SUBSTR(c2.location_name, 1, 3) AS loc_2,
  c2.loc_date_time AS ldt_1, c1.loc_date_time AS ldt_2,
  (EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) AS diff
FROM cte1 c1
JOIN cte2 c2
  ON c1.truck_id = c2.truck_id AND
     c1.location_name = c2.location_name
WHERE ABS(EXTRACT(EPOCH FROM c1.loc_date_time) - EXTRACT(EPOCH FROM c2.loc_date_time)) <= 3600
ORDER BY c2.loc_date_time;

DDL:

CREATE TABLE truck_loc
(
    truck_id VARCHAR(50),
    location_name VARCHAR(12),
    loc_date_time TIMESTAMP
);

DML:

INSERT INTO truck_loc (truck_id, location_name, loc_date_time)
VALUES
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'),
('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:56:00.000');


How to drop SQL Server database currently in use and in Single user mode I have a Database on SQL Server 2008, which I want to drop.
Currently it is in single user mode and it is currently in use.

select * from sys.sysprocesses

returns

Msg 924, Level 14, State 1, Line 1
Database 'G_MAIN_DE' is already open and can only have one user at a time.

and I do not know how to identify the session I have to kill.

An attempt to set it offline

ALTER DATABASE G_MAIN_DE SET OFFLINE WITH ROLLBACK IMMEDIATE

yields

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'G_MAIN_DE' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

To identify the session currently using the database you can try

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('G_MAIN_DE')


Can I Compress a huge table on SQL? On my production SQL Server Enterprise Edition database 2014, I am trying to find why data files (.mdf) growing day by day.

I get the following report From Management Studio: right click on the database> reports > standard reports > disk usage by top tables.

I found some tables contain more than 70 million rows. One table takes more than 13625168 KB - almost 12 GB! What should I do?

Is there away to optimize the size of table even if contains a huge number of rows?

Some data is needed, other data only needed for archiving purposes.

Updated:

Can I Compress the huge table?

Since you're using SQL Server 2012 Standard, you're not able to take advantage of DATA COMPRESSION.  It's an Enterprise feature up until SQL Server 2016 SP1.  When you're able to upgrade, you should check out that functionality.  'In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.'

However, there are a few things you can do now to possibly reduce the size of your databases.  Look for the tables that are taking up the most space and...

Archive (and delete) any data that doesn't have to reside in the database.  Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages.
If you haven't done so already, consider increasing the fill factor on your indexes to 100%.  Having your pages as full as possible can reduce the overall space needed requiring fewer pages in memory.  Afterwards, rebuild indexes on any affected tables so you can consolidate the data into fewer pages.  Check out Does index fill factor affect fragmentation?
Try to find unused or redundant indexes.  If an index is not being used, it's just taking up space in the database for no reason.  Likewise, you may be able to consolidate several redundant indexes into fewer ones.  There are numerous free tools and queries available by searching for 'sql server unused indexes'.  A personal favorite of mine is sp_BlitzIndex.


Why is my SP_HELPINDEX showing a lot of indexes? Using sp_helpindex My_table I have this list:

But my table has only these indexes:

Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is returning a LOT of indexes that I don't even have in the table.

WITH IndexColumns AS (
SELECT '[' + s.Name + '].[' + T.Name + ']' AS TableName,
i.name AS IndexName,  C.name AS ColumnName, i.index_id,ic.index_column_id,
COUNT(*) OVER(PARTITION BY t.OBJECT_ID, i.index_id) AS ColCount
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.indexes AS i ON I.OBJECT_ID = T.OBJECT_ID
JOIN sys.index_columns AS IC ON  IC.OBJECT_ID = I.OBJECT_ID
AND IC.index_id = I.index_id
JOIN sys.columns AS C ON  C.OBJECT_ID = IC.OBJECT_ID
AND C.column_id = IC.column_id
WHERE IC.is_included_column = 0
)
SELECT DISTINCT a.TableName, a.IndexName AS Index1, b.IndexName AS Index2
FROM IndexColumns AS a
JOIN IndexColumns AS b ON b.TableName = a.TableName
AND b.IndexName <> a.IndexName
AND b.index_column_id = a.index_column_id
AND  b.ColumnName = a.ColumnName
AND a.index_column_id < 3
AND a.index_id < b.index_id
AND a.ColCount <= B.ColCount
ORDER BY a.TableName, a.IndexName;

Where are these indexes coming from?

The indexes you are seeing are hypothetical indexes created by Database Engine Tuning Advisor.

DTA leaves a bit of a mess behind if it crashes or if you kill it because it runs too long.

They all include _dta_ in the name and you can see the hypothetical in the description.

Try the query from Brent as you will most likely have some of these on other tables too.

WITH hi AS (
SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1
AND INDEXPROPERTY(i.[object_id], i.[index_id], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
SELECT [hi].[Table] ,
       [hi].[Index_or_Statistics] ,
       CASE [hi].[Type]
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
       END AS [T-SQL Drop Command]
FROM [hi]

You should delete those, they aren't used anyway

EDIT:
Not sure why but the OP had issues with the query from Brent and this select worked on his environment

SELECT t2.NAME AS table_name
    ,t1.NAME AS index_name
FROM sys.indexes t1
INNER JOIN sys.tables t2 ON t2.object_id = t1.object_id
WHERE t2.NAME = 'MyTable'
    AND t1.NAME LIKE '%_dta_%'
    AND t1.is_hypothetical = 1


Optimizing for data that does not fit in memory We have a database that contains around 235 GB of data (calculcated using pg_total_relation_size for each table) on a server with around 77GB of memory. The server is used for nothing else (aside from SSH, backup and similar administrative stuff). We are running postgres 9.2.

I extracted one table of 13GB and tested it on a seperate server with 4GB of memory to benchmark some queries, indexes, etc. outside of our production environment. I had some questions for this which can be seen here: https://stackoverflow.com/questions/26468982/avoiding-external-disk-sort-for-aggregate-query.

On the seperate server, the queries use index-only scans and run relatively fast (compared to pre-index creation with sequence scans). The same queries against the same table on the production machine does a sequence scan almost no matter what settings I set up (short of setting seq_page_cost lower than random_page_cost, which is a Bad Idea(tm).)

Our workload is to extract analytical data - we have around 10 columns that are raw numbers that we do a lot of SUM() aggregate queries on, usually grouped by an item ID. The data is updated constantly, mostly with UPDATE queries to existing rows to update per-day data is it comes in during the day (so a row for a specific day may be updated ~20 times in one day). The 13GB table is for one of our biggest customers and have around 6 million rows.

The customized settings from the production config looks like this:

shared_buffers = 20000MB
effective_cache_size = 56000MB
work_mem = 512MB
maintenance_work_mem = 2048MB
checkpoint_segments = 64
temp_buffers = 16MB
max_stack_depth = 4MB

wal_level = archive
archive_mode = on
archive_timeout = 6h
checkpoint_timeout = 1h
wal_writer_delay = 100
wal_keep_segments = 32

I am looking for some insights for how to create indexes (and alter queries), as obviously in the larger setup the query planner acts differently based on the amount of data.

Is it better with small indexes that only cover the small parts of the query to help with lookups or full covering indexes (for index-only scans)?
Does the above setup match my server specifications and workload?
Any good tips to speed up aggregate queries (mainly SUM) for all rows and grouped by item ID (I am aware we may need to look into column-store databases)?

Also, does any of the above stand out as being "wrong" or should I modify it? We are running it off locally attached disks, but backup is being sent off to NFS disks (SAN).

it is important to know, which queries you are running.
otherwise it is close to impossible to give you any real advise.

however, there are two things you should know:

UPDATE benefits nicely from a thing called FILLFACTOR. it can speed up things a lot.
the order of columns does have an impact on the size of the table (http://www.cybertec.at/shrinking-the-storage-footprint-of-data/). it can change things quite nicely

but as i said: i got to see your queries to really help.


Transaction Isolation level different threads massive number of Writes I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and  tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data.

I also need the writing to be superfast.

While checking the options on improving the writes (asuming reading time is nu so important) a friend of mine told me that I need to check the Transaction Isolation Level that is appropriate for my logic.
after reading some articles regarding this issue what I understand is that this property influence reading.

Is there a Transaction Isolation Level that affect writing? Which isolation level is "best" for lots of Threads running on lots of connections?

Am I looking in the wrong place?

What can I do to improove massive writing?

Im using SQL server, the threads are coming from TomEE server that writes data that came via HTTP requests via JDBC and JPA (ORM).

Thanks!

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction.

No amount of tinkering with isolation levels will eliminate these 2 or 3 calls.

If you want more performance, then the best concept stored procedure to do an atomic write (in a transaction) to both tables in one database call. This means 50% or 66% reduction in database calls

Going further, you have to decide what "superfast" really means

For example, should you be using SSDs for your transaction log files: the drive hosting the log files determines overall write speed because of write ahead logging.

Then there the design: are you using IDENTITY columns or GUIDs or Hibernate style nvarchar keys? Has the ORM designed the database for you?


Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)? So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins?

Is the only solution to add all potential Windows logins and set them to restricted privileges as a preventative (or reactive) procedure?

As far as I'm aware it is not possible to disable Windows authentication on SQL Server 2008 + R2:

http://msdn.microsoft.com/en-us/library/ms144284%28v=sql.105%29.aspx

The only Windows accounts that will have access are those that are explicitly added as logins (or are members of a group which has a login).


Output File Default Location using MultiServer Agent Jobs I've used Ola's scripts for quite a while at several companies, and just started a new job at a company who has already implemented MultiServer Agent Jobs to execute backups, DBCC CheckDB and IndexOptimization. For whatever reason, the person who implemented the solution in mulitserver jobs removed the output file "string" (i.e. the path to the Log folder on the instance). When I copied that value from Ola's scripts after I deployed them on my local machine, it caused 2 of the full backup jobs to fail because it "couldn't find" the path. The machine that the failures occurred on has 2 instances, and so far as I know, the path to "Errorlog" is default and based on the installation selection of that default. Both instances are SQL Server 2008 R2 Standard, while all the rest of our instances are 2014 Enterprise (which really should make no difference...but who knows?).
I have removed the output file on the backup jobs so they will run successfully, but I'd really like to figure out the best way to implement this on all of our instances.
At the very least, I suppose I could remove the 2 instances that are barfing with the path and recreate them on the local instances...but that really kills the benefit of using MultiServer SQL Agent jobs.

Anyone have any ideas?

I found the answer already asked about the new variable, releases in SQL Server 2014, "SQLLOGDIR", which makes perfect sense that my 3 instances of SQL Server 2008 R2 wouldn't know what to do with that.
Here's the link:SQLLOGDIR


How do I temporarily disable sp_send_dbmail? I have just taken over responsibility for a SQL 2000 system which made heavy use of xp_sendmail.

We are in the process of upgrading to SQL Server 2008 R2 and I have implemented sp_send_dbmail.

We are currently performing user acceptance testing on the new SQL Server.  The application sends out many emails directly to our customers.  Since our test database is a copy of our live database, there are many valid customer email addresses in our test database.  Is there a way to either temporarily redirect the emails or shut down the Database Mail service, without impacting our testing?

USE msdb
GO
EXECUTE dbo.sysmail_stop_sp
GO

Will stop the server from sending mail.  EXECUTE dbo.sysmail_start_sp  will start up the service again.


Transform SELECT query to DELETE to remove extra taxonomy entries I try this query shows me the products that have duplicated, term_taxonomy_id in my web corresponds the stock of the product
(none, medium, high, low)

I would like to transform it into one query DELETE... that will delete those that have repeated and leave only the most current by the term_order, because if the product have two stock tag or more is not displayed on the web.

They only have to have a tag

object_id=1 have high,none
object_id=3 have none, medium*

table: wp_term_relationships

+-----------+------------------+-----------+
| object_id | term_taxonomy_id | term_order|
+-----------+------------------+-----------+
| 1         | high             | 0         |
| 1         | none             | 1         |<---delete none
| 1         | bbb              | 10        |
| 1         | ccc              | 10        |
| 2         | high             | 0         |
| 2         | aaa              | 11        |
| 2         | bbb              | 11        |
| 2         | ccc              | 11        |
| 3         | none             | 0         |
| 3         | medium           | 1         |<---delete medium
| 3         | high             | 12        |<---delete high
| 4         | jjj              | 12        |
| 5         | kkk              | 12        |
| 5         | lll              | 12        |
| 5         | high             | 12        |
| *         | *                | *         |
+-----------+------------------+-----------+

SELECT object_id,term_taxonomy_id,term_order
FROM wp_term_relationships
WHERE (    term_taxonomy_id = none
        OR term_taxonomy_id = medium
        OR term_taxonomy_id = high
        OR term_taxonomy_id = low)
GROUP BY object_id
HAVING COUNT(*)>1

It can only have one type of taxonomy related to stock, but there can be many more taxonomies. That's why I put aaa,bbb,ccc,zzz the only exclusive ones are those related to high,medium,low,none stock.

I want to delete all the records related to the stock (high,medium,low,none) just leave one for the lowest order of completion and leave all the items values that are not related to the stock.

My platform is MySQL 5.7 (can't use except)

If I unserstand the task correctly:

Delete "wrong" records

DELETE t1.*
FROM wp_term_relationships t1
WHERE t1.term_taxonomy_id IN (`none`,'low','medium','high')
  AND EXISTS ( SELECT NULL
               FROM wp_term_relationships t2
               WHERE t2.term_taxonomy_id IN ('none','low','medium','high')
                 AND t1.object_id = t2.object_id
                 AND t1.term_order > t2.term_order )

Forbid "wrong" records insertion:

ALTER TABLE wp_term_relationships
ADD COLUMN term_taxonomy_check VARCHAR(8)
           AS (CASE WHEN term_taxonomy_id IN (`none`,'low','medium','high')
                    THEN term_taxonomy_id END),
UNIQUE INDEX idx_term_taxonomy_check (object_id, term_taxonomy_check);


MySQL JOIN query takes too much time to get the result I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 minute. And if added more it takes infinite time. Any idea how to resolve this problem?

This is my query:

SELECT  c.`user_name`, e.`event_name`, e.`event_code`, e.`event_id`,
        COUNT(distinct ep.`participant_id`) as participants,
        COUNT(DISTINCT pm.`program_material_id`) as material_count,
        COUNT(DISTINCT ev.`event_news_id`) as news_count ,
        COUNT( DISTINCT  es.`event_speaker_id`) as speaker_count,
        COUNT( DISTINCT  epr.`event_program_id`) as program_count,
        COUNT( DISTINCT  sw.`social_id`) as socail_wall_count
FROM `event` e
LEFT JOIN `event_participant` ep ON ep.`event_id` = e.`event_id`
LEFT JOIN `program_material`  pm ON pm.`event_id` = e. `event_id`
LEFT JOIN `event_news` ev     ON ev.`event_id` = e. `event_id`
LEFT JOIN `socialwall` sw     ON sw.`event_id` = e. `event_id`
LEFT JOIN `event_speaker`     es ON es.`event_id` = e. `event_id`
LEFT JOIN `event_program`   epr ON epr.`event_id` = e. `event_id`
LEFT JOIN `event_customer`    ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer`          c ON ec.`customer_id` = c.`user_id`
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

I indexed all tables' primary keys and the columns which I used to JOIN in the subsequent tables. Here event is the master table and all other tables will have event_id.

Giving the system specs if it might helpful,

SPECS

mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Intel(R) Core(TM) i5-7200U CPU @ 2.50GHz- Thinkpad

Your query looks like a different approach will be better than simply using more and more LEFT JOINs.

Instead, move to using subqueries in the SELECT clause for most of these "added tables". You shouldn't need the DISTINCT anymore, either, if that was included just to avoid the explosion of rows from so many JOINs.

SELECT
c.`user_name`,
e.`event_name`,
e.`event_code`,
e.`event_id`,
(
SELECT COUNT(*)
FROM `event_participant` ep
WHERE ep.event_id = e.event_id
) as participants,
(
SELECT COUNT(*)
FROM `program_material` pm
WHERE pm.event_id = e.event_id
) as material_count,
(
SELECT COUNT(*)
FROM `event_news` ev
WHERE ev.event_id = e.event_id
) as news_count ,
(
SELECT COUNT(*)
FROM `socialwall` sw
WHERE sw.event_id = e.event_id
) as socail_wall_count,
(
SELECT COUNT(*)
FROM `event_speaker` es
WHERE es.event_id = e.event_id
) as speaker_count,
(
SELECT COUNT(*)
FROM `event_program` epr
WHERE epr.event_id = e.event_id
)  as program_count
FROM `event` e
LEFT JOIN `event_customer` ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer` c ON ec.`customer_id` = c.`user_id`
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

Give this a try and see if it is returning the correct results (I had to make some assumptions based on the limited information I have available), and is performing well enough or not.


Oracle patch conflict I'm attempting to apply patch 23530402 to Oracle 11.2.0.4 with OPatch 11.2.0.3.15 on a 64-bit Windows 2008 server.  Our Tenable security software indicates this is one of two patches that must be applied to resolve a finding.  Patch 23530402 is the prerequisite for the second patch.
There are two patches already applied:  22839608 and 22839614.
OPatch bombs out with the following error in the log:

Conflicts/Supersets for each patch are:
Patch : 23530402
Conflict with 22839614
Conflict details:
D:\dbApplicationX\Oracle\product\11.2.0\dbhome_1\rdbms\admin\oracle.sym
D:\dbApplicationX\Oracle\product\11.2.0\dbhome_1\bin\oracle.exe
Bug Superset of 22839608 Super set bugs are: 17551261, 18226143, 18689530, etc.

The log then recommends using the MOS Patch Conflict Checker at https://support.oracle.com/epmos/faces/PatchConflictCheck to resolve the conflict.  Unfortunately, the Conflict Checker utility states that "patch(es) on Windows platform are not supported for conflict analysis".  (You have to float your mouse over the button long enough to activate the tool tip containing this information; otherwise it gives no indication of what's wrong with it.)
The log also has this advice:

OUI-67073:UtilSession failed: Please rebuild the superset patch
[23530402] to make sure it supersedes all the relevant patch(es)
[22839608,22839614].
The rebuild patch should contain bug fix
[17551261, 18226143, 18689530, 18904062, etc.].

It wants me to rebuild an Oracle patch?  How?  Is this an appropriate time to use opatch apply -force?  Thanks for any help you can provide.  Definitely not an Oracle master here, so it's appreciated.

22839608 is the prerequisite patch for 22839614. If you try to replace it with a newer one (23530402), the prerequisite will not be fullfiled for 22839614 -> conflict.

I would try to rollback the conflicting patch (160419 JavaVM) and install the newer version of it:

opatch rollback -id 22839614

sqlplus / as sysdba
startup upgrade
@?/sqlpatch/22839614/postdeinstall.sql
shutdown immediate
startup

Now try to apply 23530402, and after that, apply 23515277 (160719 JavaVM, you did not mention this, but that is the newer version of 22839614).


Filtered Stats and Incremental Stats on Partitioned table We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental Statistics but we are thinking to create filtered stats which would allow for a much more granular level of statistics....

At this point, i'm not sure if a partitioned table can have both Filtered stats and Incremental Stats and if we implement would it confuse the optimizer ? Also , can anyone help me on how to implement filtered stats on a partitioned table ?

Thanks in advance...

Incremental stats and filtered stats serve completely different purposes. Incremental stats are not used by the query optimizer. They are a feature designed to make maintenance easier for large tables. They can only indirectly help with query performance because they can make it more practical to keep statistics up to date on large tables.

Filtered statistics are used by the query optimizer (if you write your queries carefully enough). They technically add to database maintenance but can significantly improve query performance in some scenarios.

In summary, you should not have to worry about filtered stats and incremental stats confusing the query optimizer. If you need specific help adding filtered stats to a table please post the table DDL and explain what you're trying to do along with what you've already tried.


Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014 I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size.
Now I am stuck using Diskpart to set the perfect offset. As far as I understand I have 63 hidden sectors and to align it with my formatting I can use an offset of 1024k , did I get the right?
Anyway when I list my partition afterwards I get this result:

Using command line wmic partition get BlockSize, StartingOffset, Name, Index

I get a starting offset of 135266304

Some websites/blogs recommend 32K other do recommend 1K for 2012, whom can I trust? Currently I have issues understanding it correctly. May the force (you) enlighten me?

For what is worth, I only use the 64K allocation unit size when in production or UAT environment and if I have a cluster with a SAN in which I have a LUN for the Data files, another LUN for the LOG files and another LUN for the TEMPDB files, at least.

Other consideration is that if your environment only have one disk and this is the OS disk, leave the allocation unit size as is. If you are trying to setup SQL Server 2014 on a different disk from your OS disk, perhaps in that disk you will get better performance with the 64K allocation unit size, but do consider that a best practice, both for performance and maintenance, states that you separate your data files, log files and TEMPDB files, at best, in different disks. This from a suggestion in this blog post from the SQL Server product group: http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/19/disk-and-file-layout-for-sql-server.aspx


how to return table on sql function I am very new to sql create function

i wanted to check data column value at table

the purpose is to return full table where data is null , <0 , >1

the problem is i am trying make data is dynamic so the table column more than 10 or more than  that ( can 50 column)

my sql syntax is

CREATE FUNCTION Checkdatavalue
(
     @tables varchar(50)
)
RETURNS table
AS
BEGIN
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)

        DECLARE oCur CURSOR FOR
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56'

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END

            SET @ssql =  left (@ssql,len(@ssql)-3)
            set @sql = 'select * from '+@tables+' where ' + @ssql
            --select   @sql

           close ocur;
        deallocate ocur;
            return @sql
END
GO

the @sql statement like this

 select * from tablename where  [column1] is null or
 [column1] < 0 or [column1]>1 or [column2] is null
 or [column2] < 0 or [column2]>1 or etc...

there is following error like

A RETURN statement with a return value cannot be used in this context
Incorrect syntax near 'BEGIN'.

The table-valued function requires static columns (names and types). If you want dynamic columns in result-set, use procedure instead of function.

E.G.

Create Procedure X(@tables varchar(50)) as
     DECLARE @colId int, @sql nvarchar(max),@colName nvarchar(400), @ssql nvarchar(MAX)

        DECLARE oCur CURSOR FOR
            SELECT [name] AS colName, colId
               FROM syscolumns
            WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @tables )
            and xtype = '56'

            SET @ssql = ''

            OPEN oCur

            FETCH NEXT FROM oCur INTO @colName, @colId
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ssql = @ssql + ' [' +@colname + '] is null or [' +@colname + '] < 0 or [' +@colname + ']>1 or'
                FETCH NEXT FROM oCur INTO @colName, @colId
            END

            SET @ssql =  left (@ssql,len(@ssql)-3)
            set @sql = 'select * from '+@tables+' where ' + @ssql
            exec(   @sql)

           close ocur;
        deallocate ocur;
GO
X 'Tree'


How can I defragment tables in MySQL? I have a database named COUNTY. COUNTY has a fragmented table GEO.
I executed OPTIMIZE TABLE GEO against the GEO table, but this returned the  error

  "Table does not support optimize, doing recreate + analyze instead
  OK".

Then I executed  ALTER TABLE GEO ENGINE='InnoDB'; and then went for ANALYZE TABLE GEO. But still it has fragmentation.

At last I dropped it and created it again. Still it has fragmentation.
Can anyone please help me, how can I defragment it?

ASPECT #1
The message you saw did not say

"Table does not support optimize, do recreate + analyze instead
OK"

but the message said

"Table does not support optimize, doing recreate + analyze instead
OK"

This means the InnoDB Storage Engine already executed
ALTER TABLE GEO ENGINE='InnoDB';
ANALYZE TABLE GEO;

There was no need to do it again.
ASPECT #2
There is a reason why fragmentation still exists
If the primary key of the table is an integer with auto_increment, all the numbers generated for the primary key are in ascending order.
When the primary key was being loaded into the BTREE index, fragmentation was made. Why?
You may find this surprising, but I wrote an answer to a post about 3 years ago entitled How badly does innodb fragment in the face of somewhat out-of-order insertions?
In that post I explained how AVL binary trees will cause height rotation about 45% of of the time. For a BTREE, this would be represented as BTREE page splits. What does means for sorted data ?

When loading data in ascending order, tree pages that split to the left would always be half full
When loading data in descending order, tree pages that split to the right would always be half full

Those half full pages is what is manifesting the fragmentation.
This is further illustrated in another post I wrote last year (Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?) where I mentioned this same issue for MyISAM.
CONCLUSION
The only way to really reduce fragmentation would be to drop all indexes and recreate the indexes. If the tables are gigantic, this is not worth your time.


How to store multiple foreign key values in a single field? I have following two tables in an MS SQL-Server Database:

user_info
  ( user_id PRIMARY KEY,
    email,
    password
  )

and

messages
  ( time_stamp,
    message,
    sender,
    recipients,
    FOREIGN KEY(sender)
      REFERENCES user_info (user_id),
    FOREIGN KEY(recipients)
      REFERENCES user_info (user_id)
  )

I am wondering if it is possible to store multiple user_id values in the recipients field of messages table. I am a newbie, please help me how to solve this problem.

The best way to do this is with a one-to-many relationship message_recipients table.

user_info
(
  user_id INTEGER PRIMARY KEY,
  email VARCHAR(256),
  password VARCHAR(256)
);

messages
(
  message_id INTEGER PRIMARY KEY
  time_stamp DATETIME,
  message VARCHAR(4000),
  sender INTEGER -- references user_info.user_id
);

message_recipients
(
  message_id INTEGER, -- references messages.message_id
  recipient_id INTEGER -- references user_info.user_id
);


Getting generate_series(date, date) does not exist When I run commands like this,

SELECT * FROM generate_series('YESTERDAY'::date, 'TODAY'::date);
SELECT * FROM generate_series('YESTERDAY'::timestamp without time zone, 'TODAY'::timestamp without time zone);
SELECT * FROM generate_series('YESTERDAY'::timestamp with time zone, 'TODAY'::timestamp with time zone);

I am getting errors like this,

ERROR:  function generate_series(date, date) does not exist
ERROR:  function generate_series(timestamp without time zone, timestamp without time zone) does not exist
ERROR:  function generate_series(timestamp with time zone, timestamp with time zone) does not exist

I also get this same error when I try to generate a date series with timestamp without time zone

PostgreSQL 8.4+

This is because generate_series currently requires a step when the inputs are not int, bigint or numeric from the docs

returns: timestamp or timestamp with time zone
generate_series(start, stop, step interval)

As compared to

returns: int, bigint or numeric, step defaults to 1.
generate_series(start, stop, step DEFAULT 1)

To remedy, simply add a day,

SELECT d
FROM generate_series(
  'YESTERDAY'::date,
  'TODAY'::date,
  '1 day' -- REQUIRED STEP
) AS gs(d);

PostgreSQL pre-8.3

Note: you may also get this error if you're using PostgreSQL 8.3 or lower in which case the generate_series doesn't support non-numeric types

Amazon Redshift

generate_series may or may not be currently supported in Amazon Redshift, which isn't really PostgreSQL anyway. If it still isn't supported you're likely to get an error with or without the step,

ERROR:  function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist

See also,

Amazon Redshift API Version 2012-12-01
Unsupported PostgreSQL Functions

Many people report this to work, but the docs have not been updated to reflect that.


Traversing tree-like data in a relational database using SQL Is there a way to traverse tree data in SQL? I know about connect by in Oracle, but is there another way to do this in other SQL implementations?  I'm asking because using connect by is easier than writing a loop or recursive function to run the query for each result.

Since some people seem to be confused by the phrase "tree data" I will explain further: What I mean is with regards to tables which have a parent_id or similar field which contains a primary key from another row in the same table.

The question comes from an experience where I was working with data stored in this way in an Oracle database and knew that the connect by isn't implemented in other DBMSs.  If one were to use standard SQL, one would have to create a new table alias for each parent one would want to go up.  This could easily get out of hand.

Celko's book is a good resource - if a bit overly "academic" at times.

I also have really found  this method, known as 'closure tables' to work fairly well.

If you're using a database that allows recursive CTEs (such as PostgreSQL 8.4 or newer, or SQL Server 2005 or newer), they're really the best way to go. If you're on Oracle, there's always the venerable "connect by".

It is my experience that it's far more common to be handed a set of tables in a "naive tree" schema, and have to figure out how to extract the correct tree from that storage, than it is to have an opportunity to create the cleaner "closure tables" structure.


Oracle SQL Developer 4: how to tell under which user I connected I'm using Oracle SQL Developer 4 to connect to multiple databases at the same time, sometimes using different credentials that those saved in connection properties. So I have multiple tabs in main window labeled with DB names, but I'd like also have indication of under which account I'm connected (seems natural to have it in tab label/name).
Any option setting to achieve this? Probable I'm looking in wrong place or can do some query to check currently using credentials?

You could save multiple connections to the same db but with different credentials. Give them useful names like user@thisdb and you'll be able to view that in SQL Developer at a glance in the top right corner of the worksheet.

And my favorite part: if you need to run the same SQL as multiple users or in different environments or both, you'll find that the connection name in the top right corner of the worksheet is actually a dropdown list, so it can be changed on the fly.


Is it possible to run multiple versions of MySQL Workbench? I'm doing an application migration from MySQL 5.5 to workbench v8... 5.5 supports MySQL Workbench up to version 6.3... However, MySQL server 5.5 only supports up to version 6.3 of the workbench.

I have an instance of MySQL server 8.0 on the same machine also that I need to manage.  Is it possible to have two different versions of MySQL Workbench installed on a Windows 10 dev machine at the same time?

I don't see why not. Are you encountering errors installing two versions at once if you specify different install paths? You may have to use different versions of the MySQL installer as well.

It does look like Workbench 8 is compatible MySQL 5.5, though (and I can confirm because I have been using it). The documentation says some queries will not work like showing the processlist (although I just tested this and I can SHOW FULL PROCESSLIST of my MySQL 5.5 server with MySQL workbench 8.0.12.)


How Can I Create a Temporary Password When I create a new user, I give them a "temporary" password and tell them to change it as soon as they log in. How can I effectively enforce this?

I tried using ALTER USER VALID UNTIL 'near-future-timestamp'; but as it turns out this expiration date does not get reset when the user changes their password.

I don't think you can by using PostgreSQL internal authentication.

Check https://www.postgresql.org/message-id/4836F43E.4050607@otg-nc.com:

  I think the best you could do would be to tie authentication into some
  external system that requires a password change after a specified
  interval.  This can be accomplished with LDAP, Kerberos, or PAM.
  However, I don't think that the native mechanism will allow you to do
  what you are trying to accomplish.

This is rather old, but, AFAIK it's still valid.


A Common Username for Both Database hosted in Azure I have 2 azure databases created in one SQL Server. I.e.

Database 1 = db1 in xsqldb01.database.windows.net
Database 2 = db2 in xsqldb01.database.windows.net

I would like to create a username or user login where I can read only data (Select grant only) of both databases. How can I achieve this? Do I need to create a user login with same name in both database?

You can create Login in master database using syntax ..

CREATE LOGIN <SQL_login_name, sysname, login_name>
    WITH PASSWORD = '<password, sysname, Change_Password>'
GO

and then create user in individual databases using syntax

CREATE USER <user_name, sysname, user_name>
    FOR LOGIN <login_name, sysname, login_name>
    WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

GRANT SELECT  TO <user_name>

This can be done via SSMS from local machine or VM. I believe you can achieve this using Query Editor in Azure as it does not allow switching context to master database .


How to JOIN a table with itself while using a sub-query? I JOIN a table to itself as

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

How can I limit the rows retrieved for each table (t1 and t2) by

WHERE TagID IN(SELECT TagID FROM Tags WHERE Articles>50)

Note that the table TagMap is huge with over 200M rows, and I wish to limit its size before JOIN.

In fact, the reason I want to limit the number of rows of TagMap is that the table is too big and I cannot perform the JOIN.

You use a subselect query as basis for t1 and t2

Like

SELECT
    t1.TagID AS TagID1, t2.TagID AS TagID2, COUNT(1)
FROM
    (SELECT
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t1
        JOIN
    (SELECT
        *
    FROM
        TagMap
    WHERE
        TagID IN (SELECT
                TagID
            FROM
                Tags
            WHERE
                Articles > 50)) AS t2 ON t1.ArticleID = t2.ArticleID
        AND t1.TagID <> t2.TagID
GROUP BY t1.TagID , t2.TagID


How to change default database to all logins? I'm bit new to SQL Server and I'm trying to script out Microsoft SQL Server (2005-2014) post installation activities and I got bit stuck on altering logins. Corporate policy states that none login should have master database as default, thus my plan is to identify all logins that has master as default and change master to tempdb.

Single login can be altered using such query:

ALTER LOGIN [sa] WITH DEFAULT_DATABASE = tempdb

Trying to achieve this on all logins (without knowing their amount nor names), I know I was naive, when hoping something like this would work (but had to try):

UPDATE master..syslogins
SET dbname = 'tempdb'
WHERE dbname = 'master'

Obviously output was an error (logically):

Ad hoc updates to system catalogs are not allowed.

SQL Servers are in MIX authentication mode, so pretty much anything can be login - SQL login, Windows local or domain user or group (not sure if this can make any difference for solution). Solution should be pretty much version independent, but let's say starting SQL Server 2005 forward (...yes, still have some ancient version around...)

Can anyone please help me to find proper query to achieve this?

You should never try to update system tables directly, in most cases it is not going to let you as you have found.

In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query should give you those logins that are "non-default" or installed via the installation process:

SELECT name
FROM sys.server_principals
WHERE type <> 'R'
    AND name NOT LIKE '##%' --Exclude policy based accounts
    AND name NOT LIKE 'NT%' --Exclude system accounts
    AND sid <> 0x01 --exclude the sa

From this you just need to build out your statement to execute via sp_executesql. You could use a simple cursor or while loop per your preference, below is a template for just a basic cursor that I keep handy:

DECLARE @v1 VARCHAR(50)

DECLARE v_cursor CURSOR FOR
/* Query of data to iterate through */

OPEN v_cursor
FETCH NEXT FROM v_cursor INTO @v1

WHILE @@FETCH_STATUS = 0
BEGIN
    /* build your dynamic statement */
    /* Use @v1 within query to perform action on each row */
    FETCH NEXT FROM v_cursor INTO @v1
END

CLOSE v_cursor
DEALLOCATE v_cursor;


Avoiding performance hit from GROUP BY during FULLTEXT search? Is there any clever way to avoid the performance hit from using group by during fulltext search?

SELECT p.topic_id, min(p.post_id)
FROM forum_posts AS p
WHERE MATCH (p.post_text) AGAINST ('baby shoes' IN BOOLEAN MODE)
GROUP BY p.topic_id
LIMIT 20;

In this example it's fetching the lowest post_id for unique topic_ids that match the text.

With the group by to find the min, it's taking 600ms in a million row database, with about 50K rows examined.

If I remove the MIN but leave the GROUP BY, it's the same slowness, so it's the GROUP hit.

I suspect this is because it can only use one index, the fulltext ?

key: post_text | Using where; Using temporary; Using filesort

Query_time: 0.584685  Lock_time: 0.000137  Rows_sent: 20  Rows_examined: 57751
Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0

Without the GROUP BY it's 1ms so this has to be filesort speed?

(I've removed ORDER BY and everything else to isolate where the hit is)

Thanks for any insight and ideas.

(using MyISAM under mariadb if it matters)

You could try looking at tmp_table_size variable and increasing it. The group by must do a file sort using a temporary table. It's probable the temp table exceeds the size allowed and is dumped to disk for the final sort processing (very slow).

If you do:

SHOW VARIABLES LIKE 'CREATED_TMP%'

And look at the ratio of Created_tmp_tables vs created_tmp_disk_tables, this should tell you if the value is too small.

see also: http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html


Counting sessions - TSQL If we have a table in SQL Server with the following data:

ID      Log_Time
1110    2016-10-31 20:34:50.000
1110    2016-10-31 20:34:58.000
1110    2016-10-31 20:35:03.000
1110    2016-11-01 01:28:29.000
1110    2016-11-01 01:28:33.000
1110    2016-11-01 01:28:37.000
1110    2016-11-01 01:28:42.000
1110    2016-11-01 01:28:46.000
1110    2016-11-01 01:28:50.000
1110    2016-11-01 01:28:54.000
1110    2016-11-01 01:28:59.000
1110    2016-11-01 01:29:03.000

Let's say each user action generates a log_time entry. The business calculates the number of sessions for billing purposes like this - start with session 1 and for each log activity, if the time difference is more than an hour, increment the session count by 1.

This is a fairly large table with different user ids. I have tried a combination of cursors for looping through distinct users and WHILE LOOPS to increment by doing the session counts iterating row by row. It takes long time to complete and when this table grows bigger, this might not even be the right approach. There must be a better way to do this. Any pointers?

The resultset I need is this:

ID     SessionCount
1110   28
1145   42
1116   38

As Joe suggested, you should use the LAG() function to compare values to the previous row. It could be implemented like this:

With LaggedLogTime As (
    Select ID, Log_Time,
        PreviousTime = Lag(Log_Time, 1) Over (Partition By ID Order By Log_Time)
      From #log)
Select ID,
    SessionCount = Sum(iif(DateDiff(Minute, PreviousTime, Log_Time) > 60 Or PreviousTime Is Null, 1, 0))
  From LaggedLogTime
  Group By ID;


How to know what index is set on the table with command line in informix? I'm using the Informix DB. I want to detect what index the table has using command line. I used

dbschema -d dbName -t  tableName | grep index

to detect the index. What's my problem is that the dbschema output format is not specified (hard to automatically parse it) so I don't think it is reliable. Is there any better way to automatically detect the index instead of read by human?

I think understand your problem.

The dbaccess break lines at the middle of the statement and the table name goes to line bellow...
Something like this (dbschema output)

create unique index "informix".ix1c_h_dbases_2012_06 on "informix"
    .h_dbases_2012_06 (seq,site_name) using btree  filtering

I consider two solutions 100% functional...

First suggestion

Parse the dbschema with awk to join the lines until found a semicolon.
This will give to you long lines...
Then you can work little more with awk/sed to get only the index and table name... not so hard.
But this probably will become a shell script.

$ dbschema -d dba  -t h_dbases_2012_06| awk '{printf $0} /;/{print ""}' | egrep "create.index"
create index "informix".ix10_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagwrites) using btree ;
create index "informix".ix11_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,total_sorts) using btree ;
create index "informix".ix12_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,isreads) using btree ;
create index "informix".ix13_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iswrites) using btree ;
create index "informix".ix14_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,iscommits) using btree ;
create index "informix".ix15_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_write_mb) using btree ;
create index "informix".ix16_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,horini) using btree ;
create index "informix".ix1_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq) using btree ;
create index "informix".ix1b_h_dbases_2012_06 on "informix".h_dbases_2012_06     (seq,hostname) using btree ;
create index "informix".ix2_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,username) using btree ;
create index "informix".ix3_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,cpu_time) using btree ;
create index "informix".ix4_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,programa) using btree ;
create index "informix".ix5_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,tarefa) using btree ;
create index "informix".ix6_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,hostname) using btree ;
create index "informix".ix7_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,maxlog_kb) using btree ;
create index "informix".ix8_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,net_read_mb) using btree ;
create index "informix".ix9_h_dbases_2012_06 on "informix".h_dbases_2012_06     (datini,pagereads) using btree ;

Second suggestion

Query the database.
For this , they should be a script and your user should have access to database with dbaccess

$ cat x.sh
dbaccess -e - <<EOF
database $1 ;
select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("$2*")
  and t.tabid > 99
order by 1,2
;
EOF

$ sh x.sh dba h_dbases_2014_04
database dba ;
Database selected.

select t.tabname[1,20] as table, i.idxname[1,20] as idx
from systables t , sysindices i
where t.tabid = i.tabid
  and t.tabname matches lower("h_dbases_2014_04*")
  and t.tabid > 99
order by 1,2
;

table                idx

h_dbases_2014_04     ix10_h_dbases_2014_0
h_dbases_2014_04     ix11_h_dbases_2014_0
h_dbases_2014_04     ix12_h_dbases_2014_0
h_dbases_2014_04     ix13_h_dbases_2014_0
h_dbases_2014_04     ix14_h_dbases_2014_0
h_dbases_2014_04     ix15_h_dbases_2014_0
h_dbases_2014_04     ix16_h_dbases_2014_0
h_dbases_2014_04     ix1_h_dbases_2014_04
h_dbases_2014_04     ix1b_h_dbases_2014_0
h_dbases_2014_04     ix1c_h_dbases_2014_0
h_dbases_2014_04     ix2_h_dbases_2014_04
h_dbases_2014_04     ix3_h_dbases_2014_04
h_dbases_2014_04     ix4_h_dbases_2014_04
h_dbases_2014_04     ix5_h_dbases_2014_04
h_dbases_2014_04     ix6_h_dbases_2014_04
h_dbases_2014_04     ix7_h_dbases_2014_04
h_dbases_2014_04     ix8_h_dbases_2014_04
h_dbases_2014_04     ix9_h_dbases_2014_04

18 row(s) retrieved.

Database closed.


SQL Substring in WHERE clause I'm trying to build a SQL query that will show me all the products that have products.description IS NULL but also have a corresponding description in the descriptions table.   So code is always a substring of info.

The products.code is a substring of descriptions.info, so I'm not sure how to pull this.

products

id |  code   |  description
--------------------------
1    12345-1      NULL

descriptions

id |  info
---------------------------
1     12345-1_Hammer

You need t o compare product.info with descriptions.info. If the needed condition is for one to be a substring of the other, it can be done with LIKE.

Since there may be more than one descriptions matching (having as substring) a product code, we should use an EXISTS subquery:

SELECT p.id, p.code
FROM products AS p
WHERE p.description IS NULL
  AND EXISTS
      ( SELECT *
        FROM descriptions AS d
        WHERE d.info LIKE '%' + p.code + '%'
      ) ;


Database index on FK with only NULL values We have in our database an index with table cardinality over 10.000 but full key cardinality only 1. The reason for this that all the values for the column are NULL, hence cardinality 1. Does this index still perform badly in practice? Or is it omitted because the values are all NULL? Our database is IBM DB2 9.7.

The only situation where an index with full key cardinality = 1 will help is when you are looking for something that does not exists. By using such index you can quickly conclude that nothing will match your predicate, whereas without the index you will have to scan the table.

If you have a large table and you frequently look for things that does not exists, you can benefit from such index (fairly rare situation).

The downside of such index is that the only leaf-page will have a long rid list that has to be maintained during modification of the table.

In most cases you will see an overall performance improvement by dropping that index, but it is impossible to tell for sure without having access to the system. Ideally you have a test system where you can run a representative load for your system and compare the performance with and without the index.

For the big picture, what is the purpose of the attribute that never exists? You may want to consider normalizing this table and move this attribute to a separate table.


View last several innodb deadlocks I see that I can view the latest deadlock in mysql/innodb, but is there a way to view past deadlocks? We have two deadlock issues, one being important and the other not. The less important deadlock happens several times a day so it becomes the "latest" deadlock.

There is a setting that was introduced in MySQL 5.5.30 : innodb_print_all_deadlocks

  When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. Otherwise, you see information about only the last deadlock, using the SHOW ENGINE INNODB STATUS command. An occasional InnoDB deadlock is not necessarily an issue, because InnoDB detects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue DML or SELECT ... FOR UPDATE statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.

Just add this setting to my.cnf

[mysqld]
innodb_print_all_deadlocks = 1

or

[mysqld]
innodb_print_all_deadlocks = on

You don't have to restart mysql. Just login to mysql and run

mysql> SET GLOBAL innodb_print_all_deadlocks = 1;

or

mysql> SET GLOBAL innodb_print_all_deadlocks = 'ON';

This setting is new to me too.

Give it a Try and tells us all what you think !!!


Shirking of Log file on Primary Server of Transaction Log shipping I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

We had setup log backups for every 5 minutes, so there no problem with ldf file increasing.

We had setup Log shipping on Prod server(Primary) and DR Server(Secondary)

Now i have a question,

Can i shrink the log file of the database on primary to 1 GB?
Can i shrink the log file i.e. ldf without changing the recovery model of database to 1 GB?
Can i shrink the log file without using NOTRUCATE option, if i use ssms wizard to shrink.
Will shrinking cause for log file to grow more after shrinking?

Expedite answers would be appreciated

Thank you

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

Leave it as it is, the space would eventually be utilized without any autogrowth and that would really help your SQL Server transactions, so just DONT shrink.

FYI

You can shrink log files on primary server
DONT change the recovery model this would break logshipping. Yes you can  shrink without changing recovery model
The log file will eventually again grow so there is no sense in shrinking.
You may use NO truncate but normal shrinking would just do fine. This is just to answer your queries I would still say do not shrink.

Further reading Why Does Transaction Log file Growing and Runs out of the Space


Mysql: privileges to get access to "mysql" database Tell me please - what minimum set of privileges the user should to have to get access to the "mysql" database on the server.

For example to get ability to execute query:

select * from mysql.help_topic

Thanks!

The very minimal permissions would be:

GRANT USAGE ON *.* TO [user@host] ;
GRANT SELECT ON mysql.help_topic TO [user@host] ;

Do not believe this answer is sufficient. Please check the manual first, because the privilege system in MySQL is far from being obvious.


Why is DistinctSort followed by TopNSort in my query plan? I have the following definition:

CREATE TABLE [dbo].[JobItems] (
    [ItemId]            UNIQUEIDENTIFIER NOT NULL,
    [ItemState]         INT              NOT NULL,
    [ItemCreationTime]  DATETIME         NULL DEFAULT GETUTCDATE(),
    [ItemPriority]      TINYINT          NOT NULL DEFAULT(0),
    [ItemRefreshTime]   DATETIME         NULL,
    -- lots of other columns
    CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);

CREATE INDEX [GetTaskToProcessIndex]
    ON [dbo].[JobItems]([ItemState], [ItemPriority], [ItemCreationTime])

and the following query:

SELECT TOP(1) ItemId FROM JobItems
WHERE ItemState = 5 OR
   ( ( ItemState = 11 ) AND ( DATEDIFF( SECOND, ItemRefreshTime, GETUTCDATE() ) > 14 ) )
ORDER BY ItemPriority ASC, ItemCreationTime ASC

I run this query and inspect the actual execution plan and here's what's going on:

Index seek is done to retrieve items with ItemState=5.
Index seek is done to retrieve items with ItemState=11 and then for each row a separate seek is done to retrieve ItemRefreshTime and the results of two seeks are filtered using nested loops.
Sets from 1 and 2 containing ItemId, ItemCreationTime and ItemPriority are concatenated and then...
Magical DistinctSort happens with ORDER BY ItemId ASC and finally
TopNSort happens with ORDER BY ItemPriority ASC, ItemCreationTime ASC

TopNSort and DistinctSort take something like 32 percent each so I'd be happy to get rid of DistinctSort - I don't even understand its purpose.

What's this magical TopNSort which useful DistinctSort and why is it there?

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality.

UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000

And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER GROUP).

This is an index union plan. The concatenation operator implements UNION ALL. The Distinct Sort changes the semantics to a UNION operation to prevent the same row being returned multiple times. (In the event the table had no index key to act as a unique row identifier the physical rid would have been used here to avoid incorrectly de-duping different rows that happen to have the same column values)

An example of where this might be needed is in the query below. (note the two parameters are set to the same value so an index union plan there would seek the same rows twice)

DECLARE @ItemState1   INT = 5
        , @ItemState2 INT = 5

SELECT ItemId
FROM   JobItems
WHERE  ItemState = @ItemState1
        OR ( ( ItemState = @ItemState2 )
             AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )

The Top N Sort then re-sorts the data to implement the TOP 1.

In your case the Distinct Sort is logically not required for multiple reasons. The ItemState = 5 and ItemState = 11 branches are mutually exclusive (and this could be determined at compile time) and additionally the TOP 1 ... ORDER BY ItemPriority ASC, ItemCreationTime ASC semantics wouldn't be affected even if there were erroneous duplicates.

An alternative way of writing the query (that gives a better plan using the indexes to avoid any sorts at all) is

SELECT TOP(1) ItemId
FROM   (SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ItemState = 5
        UNION ALL
        SELECT ItemId,
               ItemPriority,
               ItemCreationTime
        FROM   JobItems
        WHERE  ( ( ItemState = 11 )
                 AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )) T
ORDER  BY ItemPriority ASC,
          ItemCreationTime ASC

You could consider adding ItemRefreshTime as an included column to the index to avoid the key lookup if in practice quite a few may be required before finding a single row satisfying the residual predicate.


Maintenance Plan folder missing in SQL Managed Instance (via VM) Accessing our new SQL Managed Instance via an Azure VM/SSMS, the Maintenance Plan folder is missing from the Object Explorer pane.  Reading other posts (that all relate to other versions of SQL Server) I've checked I have the valid permissions:

Is there a way to enable maintenance plans in SQLMI?
If not (assuming that SQLMI doesn't use maintenance plans), what is the modern day Azure equivalent to perform automated backups/deletion/index maintenance?

Please refer to this other thread on DBA.StackExchange about how to schedule maintenance of indexes and statistics. As you can read on that thread you can use Ola Hallengren's maintenance scripts, and schedule them using SQL Agent on Azure Managed Instance.
You can use also Azure Automation to schedule any maintenance task as described on this article.


search xml column inside large table (7 million rows) Database design:

Table Name : Plays ( 6 million records)

Columns: Play id, col1, col2......col7, PlayFieldValues

PlayFieldValues xml column. sample xml stored in this column

<PlayAttributes>
  <PlayFields>
    <PlayField ID="Play.GenericInt1" >25</PlayField>
    <PlayField ID="X" TS="">0</PlayField>
    <PlayField ID="Y" >OFF</PlayField>
  </PlayFields>
</PlayAttributes>

Each xml has more than 25 elements

Using following query I am returning all Playid's from plays table having "Play.GenericInt1" attribute value 25 (we can add more conditions in where clause. This is just an example.)

Select

playId
FROM
(
SELECT playId, x.v.value('@ID[1]', 'VARCHAR(100)') AS playfield ,
x.v.value('.', 'VARCHAR(100)') AS playfieldvalue
from plays
cross apply PlayFieldValues.nodes('/PlayAttributes/PlayFields/PlayField') x(v)
)T

where
T.playfield in('Play.GenericInt1') and T.playfieldvalue=25

Problem: This query is working great on plays table with 1k or 2k rows, but it is not scaling when running on 5 million rows table. execution time is crossing 20 min. Please advice what types of methods and procedures in sql I can use to search the xml column. I tried XML indexes but it costing us lot of space and maintenance problems.

You may improve your performance by using xml exist, instead of extracting the values.

SELECT playId
from plays
where PlayFieldValues.exist('/PlayAttributes/PlayFields/PlayField[@ID = "Play.GenericInt1" and text()="25"]')=1

...but without any xml indexes this is still going to involve a table scan.


Database model with users, roles and rights I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user.
Below is the table definition of users, roles and items:

CREATE TABLE users
(
  id serial NOT NULL PRIMARY KEY,
  username character varying UNIQUE,
  password character varying,
  first_name character varying,
  last_name character varying,
  ...
);

CREATE TABLE roles
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

CREATE TABLE element_1
(
  id serial NOT NULL PRIMARY KEY,
  name character varying NOT NULL,
  description character varying,
  ...
);

...

Now I have two different ways of designing the rights. One table with a rights type column or 10 rights tables - one for each element I want to control the access to.

What are the pros and cons of one rights table vs. one rights table per element? - or is the a more suitable way to do this?

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control  (DAC)?

  RBAC in the Role-Based Access Control
  (RBAC) model, access to resources is
  based on the role assigned to a user.
  In this model, an administrator
  assigns a user to a role that has
  certain predetermined right and
  privileges. Because of the user's
  association with the role, the user
  can access certain resources and
  perform specific tasks. RBAC is also
  known as Non-Discretionary Access
  Control. The roles assigned to users
  are centrally administered.

  DAC In the Discretionary Access
  Control (DAC) model, access to
  resources is based on user's identity.
  A user is granted permissions to a
  resource by being placed on an access
  control list (ACL) associated with
  resource. An entry on a resource's ACL
  is known as an Access Control Entry
  (ACE). When a user (or group) is the
  owner of an object in the DAC model,
  the user can grant permission to other
  users and groups. The DAC model is
  based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources.
2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2)  DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)


Adding a new primary key ID column to an existing table I want to add a new column to a table. The table is already populated with data.

I can do this by making a complete copy of a table, inlcuding the contents, and add a Primary Key ID column to it.

I can create a new table thus:

create table myTestTable
(
    ID uniqueidentifier NOT NULL,
    SomeId uniqueidentifier NOT NULL,
    SomeOtherId uniqueidentifier NOT NULL,
    constraint table_constraint PRIMARY KEY (ID)
)

-- modify the script below to copy the old table across to the new one,
-- generating a unique ID (GUID) into

insert into myTestTable
(ID,
ClaimPaymentId,
TemplateId)
select
    NEWID(),
    SomeId ,
    SomeOtherId
from [dbo].[TableToBeCopied]

I can then drop the copied table and rename the copy to the old table name.

But I have to do about sixty such tables and I want to write a script which will take a table's name as a parameter and automatically generate the column definitions. But I can't see how to do this. Any ideas?

You can add your GUID ID column with the following.

ALTER TABLE [yourSchema].[yourTable] add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL

To do this for multiple tables you could use something like this:

SELECT 'ALTER TABLE '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL'

FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id

WHERE (     (s.name = 'Schema1' AND t.name IN ('Schema1_TableList'))
            OR
            (s.name = 'Schema2' AND t.name IN ('Schema2_TableList'))
            ... -- Add more schemas and their tables as needed
        )

This will give you all the ALTER TABLE statements to create an ID UNIQUEIDENTIFER coulmn with the ID values pre populated.


Using VARCHAR keys or INT I'm building a Data Warehouse in MySQL based on yelp dataset.

Most of the keys in the dataset are given as strings:

user_id                 review_id               business_id
hzw-qTUVpmLAKjdkoUNh8A  Awq_6cyNjK1-qPZAwnXjjQ  7p6tHUA1Pknh0DVWqz86lA
mldKxVI59o3LhK3ITG6mnA  96YkAuJzlT54qZZWNebFUg  7p6tHUA1Pknh0DVWqz86lA
SaedHW9i7k4lHR8tgwtMgQ  OfZRG7RgKA118zDtj6yo-g  7p6tHUA1Pknh0DVWqz86lA

Should I transfer them to self-generated keys (Auto-increment Integers) or leave them as is (VARCHAR(22)).

What are the considerations in Primary/Foreign Keys data type selection?

Thanks

There's not a clear-cut answer to your question without more information. However, your choice should be basically based on:

Ease of data loading: if you leave the keys as they are, you'll save a lot of trouble creating the equivalent INTEGER ids (you would need an equivalence table between every pair; and use this translation table when you import the data using an ETL process). If all keys are 22 chars in length, you're probably better of using char(22) instead of varchar(22) 1. (Against AUTO_INCREMENT)
Size: if the size of your dataset is very large, making the translation will save you a significant amount of space in both table rows and indexes. If there are lots of indexes with several varchar(22) and extra columns, you can hit the limit on index size 2. The smaller the indexes (and tables) the more performant a system is to query. (Pro AUTO_INCREMENT)
New keys: if you want to add more rows to your dataset, it will be easier to have an AUTO_INCREMENT key than to have a mechanism to generate varchar(22) unique ids. (Pro AUTO_INCREMENT)

Depending on your particular needs, balance the pros and cons, and choose.

Given the nature of the Yelp Dataset, I'd probably go for the INT, just for size efficiency. You'll need to translate business_id, review_id, user_id and photo_id. Provided you'll have to translate from JSON to CSV already, and convert arrays to normalized subtables, before being able to upload the different collections, doing one extra step shouldn't be that difficult.

Notes:

  If your content is a fixed size, you'll get better performance with CHAR.

from: What's the difference between VARCHAR and CHAR?

  maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

from 14.8.1.7 Limits on InnoDB Tables

Side note: Consider using PostgreSQL and MADLib. I think this combination might give you some useful tools for this kind of challenge.


Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables?  I suspect not, but I wanted to double-check before I throw in the towel on this one.

Here's a dbfiddle that shows a basic breakdown of different ways I'm aware of specifying hints for a query, and the only way I can figure out how to pass query and/or table hints that interact with a history table is to convert the query to a UNION ALL between the live and history tables rather than use the FOR SYSTEM_TIME clause.

When trying to specify a history table hint when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 308 Level 16 State 1 Line X
Index '<<HISTORY TABLE INDEX NAME>>' on table '<<LIVE TABLE NAME>>' (specified in the FROM clause) does not exist.

When trying to specify a query hint pointing to the history table when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 8723 Level 16 State 1 Line X
Cannot execute query. Object '<<HISTORY TABLE>>' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

These errors both make sense on the surface as the history table is not specifically referenced in the query, but is there another query hint, trace flag, etc. that I can use so that I can still use the FOR SYSTEM_TIME clause and specify hints against the history table?

While not a great idea in general, a workaround that seems to work is using the USE PLAN hint

Base query to get the XML from

-- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints
SELECT  *
FROM    dbo.People WITH (INDEX(IX_Live_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

UNION ALL

SELECT  *
FROM    hist.People WITH (INDEX (IX_Hist_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Get the XML

Replace ' with ''

Run the query with USE PLAN and the previously generated execution plan

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'
OPTION(USE PLAN N'...')

Result

Using a plan guide to force the plan

Change ' to '''' in the query plan from the base query

Create the plan guide

EXEC sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT    *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = ''2A''
    AND Name = ''John''',
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION(USE PLAN N''Plan Here'')'

Test the query

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Result

The plan guide is used, based on the PlanGuideName fragment in the plan's XML

PlanGuideName="TemplateGuide1"

&

UsePlan="true"

As expected, when we add a couple of spaces the plan guide is no longer used

SELECT  *
FROM      dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Resulting in the query without the index hints

(Tested on build version 14.0.3045.24 / SQL Server 2017 CU12)

Notes

While this is a possible workaround it is only meant to be used if it is A) needed and B) there are no other options.

The use of plan guides is most beneficial when using  paramaterized queries (either by design or force), creating a plan guide for each ad hoc query possibility is not feasible.


How can I monitor running queries in Oracle? We use a closed source application but the built-in reporting tools aren't great so we've started using a seperate database reporting application to generate our reports.

I need to get a report copied across to our new reporting application.

I want to run the report in our old application and monitor what queries it is sending to the Oracle database so that I can copy those queries across to our new reporting application.

How can I do this?

Start the old application, and log in. Find your session in the database (let's say: sid=12, serial#=3456), and enable SQL tracing for that session, for example:

begin
  dbms_monitor.session_trace_enable
  (
    session_id => 12,
    serial_num => 3456,
    binds => true
  )
end;
/

There are several ways of enabling SQL tracing, it is just one of them. It may be useful to enable capturing bind values (as above), so you can match your report input parameter values to the query bind variables.

You can find the name of the trace file as:

select
  p.tracefile
from
  v$process p
  join v$session s on (p.addr = s.paddr)
where
  s.sid = 12
;

You can start running your report from the application, and follow the contents of the trace file (even tail -f, but it will not be human-friendly). After you have finished, disable tracing:

begin
  dbms_monitor.session_trace_disable
  (
    session_id => 12,
    serial_num => 3456
  )
end;
/

You can read the raw trace file (if you are patient enough and interested in more details), but if you want only the query texts, you can use tkprof on the trace file (e.g: orcl_ora_6789.trc):

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no aggregate=no

With aggregate=no added, tkprof will not aggregate the SQL statements and it will generate an output where the order in which the statements were executed is preserved. Without that, you can aggregate multiple executions of the same SQL and sort them, a typical use case would be to sort by elapsed time, so you the output starts with the longest running queries:

tkprof orcl_ora_6789.trc orcl_ora_6789.tk sys=no sort=prsela,exeela,fchela


I am trying to figure out how to get the proper results from a MySQL database I am trying to figure out how to get the proper results from a MySQL database.

Table Structure:  items

PN1            PN2
------------   ------------
702866-001     702866-001
738975-001     702866-001
B9C84AV        702866-001
LMT-256M6M     702866-001

The 2nd part # is a cross reference to the master item (PN1), the master item will have PN1 and PN2 duplicated.

The Select statement I needs is that I can search by any part # from either field (PN1 or PN2) and get a complete list of Distinct PN's

For example:  If I search for B9C84AV it would return all Part #'s in PN1 list since they are crossed in PN2.

Basically, what I need is a way to get PN2 for the record where PN1 is equal to the search parameter and then return a Distinct list of all records where PN1 or PN2 = PN2 (returned from the search parameter)

Any help on this would be greatly appreciated.

Regards,
Bryan

This is rather simple:

SELECT i.pn1 AS pn,
       CASE WHEN i.pn1 = i.pn2 THEN 'master' ELSE 'item' END AS type
FROM items AS i
  JOIN items AS s
    ON s.pn2 = i.pn2
WHERE s.pn1 = @searhed_PN ;


Cattle counter based on events log Given some event data that only consists of an id and a type of event (entry/exit) I'd like to compute a third column which could keep track of the current state of things (count), basically stating how many animals are present at any given time.

Example:

id   event
--------------
1    entry
2    entry
3    entry
4    entry
5    exit
6    exit
7    entry

I'd need an output similar to this (considering it starts empty)

    id    event        count
-----------------------------
    1     entry        1
    2     entry        2
    3     entry        3
    4     entry        4
    5     exit         3
    6     exit         2
    7     entry        3

Where count holds keeps track of the actual number of animals after that event has been processed.

I've been trying to take some windowed approach, but after a couple of hours I just can't seem to nail it (I'm quite new to this yet).

Any help will be greatly appreciated.

My understanding of your requirements: For each event calculate a number based on the prior events (including the current one).
That is indeed possible by using a window function:

SELECT id, event, addend, SUM(addend) OVER (ORDER BY id ASC) AS count FROM (
  SELECT id, event,
  CASE WHEN event = 'entry' THEN 1 WHEN event = 'exit' THEN -1 ELSE 0 END AS addend
  FROM events
) AS events_with_addend

The type of event does not contain any information we can use in the aggregate sum, so we have to provide it (in the inner query).

Instead it is preferred to create a separate table event_types containing the name of the event as well as the change applied to count by it, then referencing event_typesfrom events (look up database normalization) and losing the inner query.

The actual window function then just sums up the addends of each row until (including) the current one (the window frame).

It is very possible this approach might lead to performance problems depending on the (growing) count of rows. If it does, have a look into materialized views


inconsistent type after querying search_condition column in all_constraints table Oracle I'm trying to get the not null constraints in my tables, so I'm using the following sql request :

    select * from all_constraints
    where owner='userx'
    and constraint_type='C'
    and search_condition='%IS NOT NULL';

The problem is that I'm getting the ORA-00997: illegal use of LONG datatype exception
When looking on the Oracle 9i documentation I realize that search_condition column is of LONG datatype.

How to make use of this column ?

if you have xmldb installed (not sure if this will work for 9i) you can use something like this,

SELECT table_name, constraint_name, sc FROM   (SELECT  constraint_name, table_name, search_condition ,to_char(extractvalue(dbms_xmlgen.getxmltype ('select search_condition from user_constraints x' ||' where x.constraint_name  = ''||t.constraint_name||''''),'//text()')) sc FROM   user_constraints t) where sc like '%IS NOT NULL'


Log Shipping with Database Snapshot for Read Queries Company may implement Log Shipping with Snapshot. This provides basic Disaster Recovery with Reporting area. Server A OLTP contains database which log ships to Server B (ReadOnly-Standby), Snapshot is taken on ServerB database. For updated data, we will create database snapshots every 30 min on ServerB.

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

(2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores occurs later on database, will be snapshot break or continue to operate normally?

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

Yes, all users has to be disconnected before the logs can be restored.

  (2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores
  occurs later on database, will be snapshot break or continue to
  operate normally?

This depends on how you setup your Log shipping session. While setting up the Transaction log shipping, you have the option to Forcibly disconnect any users connected to the database or wait for users to disconnect, before the logs can be restored. But either way all users must be disconnected before the transaction logs can be restored.

Depending on your business needs (Preferably, get the business people involved in this decision) you can set the precedence for Log restore or Snapshot.


Linked Server stops responding for particular login We have an issue where the Linked Server does not work for the app team login but tests successfully when the DBA checks the connectivity. On investigating we found that the app login had some Killed/Rollback sessions which are causing this issue. The only resolution we have now is to Restart the instance.

Multiple sessions from the app login which are in Killed/Rollback and would not do anything (0 CPU Time and 0 Disk IO). This is a report login which extracts reports all day and sometimes this happens.

Is there any way to fix it without an instance restart?

Stop restarting SQL Server. Just stop doing that. If you have a particular problem with a particular process that is being killed and rolling back, let it finish. Then, find out what's causing that to happen. Use Extended Events, or a server-side trace, heck, even a third party tool like sp_WhoIsActive. Do something.

SQL Server has a transaction log. All modifications go in there. When these are committed, the changes go into the data file. When they are killed, they have to roll back.

That's just how it works. The rollback has to finish for the database to remain transactionally consistent.

Restarting SQL Server does not make this go faster. In fact, you could easily make things worse, possibly even taking the database offline or corrupting it.

Admittedly, this answer does not address your question, but there's not enough space in the comments to explain why you need to figure out what's causing the killed processes. It could be a DBA, it could be an automated process.

Bottom line: let SQL Server finish what it's doing. If it takes a long time to do something, killing the task will take just as long, if not longer, as that to roll back.


How to refresh AD security group on Sql Server permissions I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP.

I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems there is a delay before the user is recognized in Sql Server.

I am using AD Security Groups for permissions expressly so that I don't need to add individual users in Sql Server. So effectively I don't need to do anything but add the user to the AD Security Group in order to grant access.

But for some reason Sql Server doesn't immediately recognize the addition. I've seen this a number of times. I add the user to the group but that user can't access data until the next day. It seems that it doesn't query the Active Directory in real time. Can you confirm that is so?

What do I need to do so that Sql Server "refreshes" the list of users from Active Directory?

The user need to log off of their workstation and log back on.  That's why the changes appear to take effect the next day.  The reason for this is that when the user logs on the next day they get a new token from the domain controller and this token contains the list of domain groups that they are a member of.  This token with the list of domain groups is only updated when the user logs in to their computer, so if the user never logs out the token isn't ever updated.

There's also multi-site domain replication delays which need to be taken into account if your domain controllers are in different physical locations.