Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script I am trying to add new articles to an existing publication in SQL Server 2012.

I am adding the article to the publication with the syntax

--add the table into the correct publication
sp_addarticle @publication='<publicationname>', @article='CardBookingType_tbl', @source_object='CardBookingType_tbl'

Previously on SQL Server 2008 I used to use the following syntax to subscribe to 1 table only and then run the snapshot. This no longers work on SQL 2012 as you have to subscribe to all articles.

--add the table into the subscription
EXEC sp_addsubscription
        @publication = '<publicationname>',
        @subscriber = '<Servername>',
        @destination_db = '<dbname>'

GO

In SQL Server 2012, if I add the table via the GUI and then run the snapshot agent it picks up the new table fine. But if I add via the script it appears in the gui but the snapshot reports no new items were detected.

Seems like I am missing a piece of code to flag the subscribers as missing a article?

Maybe Brandon's idea here will help:
How to add an article to existing replication

Summary of his suggestions to fix:

execute sp_refreshsubscriptions for pull subscriptions
or in push subscriptions use the sp_addsubscription as you mentioned (worked
for me)

I wrote some code to help identify the "orphaned articles on subscriber's":

--Run from distributor
SELECT  pub.data_source AS Publisher ,
        a.Publisher_db ,
        p.Publication ,
        sub.data_source AS Subscriber ,
        s.Subscriber_db ,
        a.Article
FROM   distribution.dbo.MSarticles as a
LEFT JOIN distribution.dbo.MSpublications AS p ON a.publisher_id = p.publisher_id AND a.publication_id = p.publication_id
JOIN sys.servers AS pub ON p.publisher_id = pub.server_id
LEFT JOIN distribution.dbo.MSsubscriptions AS s ON a.publisher_id = s.publisher_id AND a.publication_id = s.publication_id AND a.article_id = s.article_id
LEFT JOIN sys.servers AS sub ON s.subscriber_id = sub.server_id
WHERE s.subscriber_db IS NULL --Leave off for great documentation, but this will show the "orphaned" subscribers effected by this issue.

I have faced similar issue.If you script out subscriber details you can find the details. If you find sp_addsubscription in parameters. If you find addsubscription only for specific articles then you need to modify parameter Articles as 'all'
Then drop that subscription and recreate with modified script and reinitialize. It will work.


Difference between oracle database desktop and server class I installed Oracle database 11g enterprise edition to my laptop. When I was installing Oracle, at the beginning I selected Desktop Class (not server class) option.

Now I am trying to make remote connection to my Oracle database from another PC but I cannot connect to Oracle and get this error message:

  "ORA-12545: Connect failed because target host or object does not exist"

even though all Oracle services have been already started.

May the reason be why I installed Oracle as desktop class instead of server class?

My tnsnames.ora file:

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

DERS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = Ders)
    )
  )

May the reason be why I installed Oracle as desktop class instead of server class?

No, 'Desktop Class' just means you only get basic choices from the installer1, according to the docs:

  Desktop Class—This installation class is most appropriate for laptop or desktop computers. It includes a starter database and
  requires minimal configuration.
  Server Class—This installation class is for servers, such as you would find in a data center, or used to support enterprise-level
  applications. Choose this installation class if you need access to
  advanced configuration options.

  During a Desktop Class installation, you make only basic choices. For
  a Server Class installation, you choose either typical installation
  (where you make only basic choices) or advanced installation.

  During a Desktop Class or a typical installation, Oracle Database
  automatically installs the sample schemas.

You'll need to go through the kind of troubleshooting steps suggested in the comments to find out what is causing the ORA-12545 error:

  An ORA-12545 error occurs when the client is not able to contact the
  listener on the node specified. For example: when the listener has not
  been started on the node, if the node address has been specified
  incorrectly, or a firewall blocks access.

1 So 'Desktop Class' and 'Server Class' are poor names for the installation types. 'Basic' and 'Custom' might be better?

When installing Oracle Enterprise Database on Windows with setup.exe you are asked to choose the systemclass. You have to decide the systemclass to be one of these two choices at installation:

Server class
Desktop class

When I had to decide, I didn't have further information about the differences of the two possible system classes. As my installation is for Dev-usage on a desktop computer, I supposed Oracle would use this information for resource configuration and decided for (2) Desktop class. That was definitely the wrong choice.

Result: I wasn't able to connect from a client computer to the DB, even though the listener was up, running and correctly configured by listener.ora.
Exactly the situation described in the question.

After endless and desperate research and even a lot more of trial/error measures, I completely uninstalled the DB. After reinstallation with Systemclass=Serverclass, the remote access was fine then, immediately.

It's a pity but I didn't find more information about the meaning of Systemclass. Looking back, the option to setup with systemclass=Desktop class seems a bit pointless to me. Even more as I think there is no way to change this once installation is completed.

The problem with systemclass=Desktop class configuration is:

The DB is installed in localhost::DBname instead of MyHostname::DBname.


Reset postgres password v9.1 I'm trying to reset my lost password for Postgres v9.1 (windows 7) like written at https://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F  :
1. edit pg_hba.conf to allow trust authorization temporarily

In pg_hba.conf I've put :

local all all trust
host all all 127.0.0.1/32 trust

2. Reload the config file (pg_ctl reload)

Done. I have also restarted the Postgres service (via services.msc)
3. Connect and issue ALTER ROLE / PASSWORD to set the new password

Then I execute 'psql.exe' but it asks me for a password !
Why ?
Thanks for your help.

Try to run psql -h 127.0.0.1 (...)

When you use the -h that will tell psql to use a TCP/IP connection
('h' is from host), and the parameter after -h is the name of the host.

In that case 127.0.0.1 is a default address, meaning localhost.

If you don't use the -h (with host next to this parameters) it will
use a "local way" of connection called unix domain sockets.


Does running pg_dump on live db produce consistent backups? I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).

My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to find out when disaster strikes.

Postgres documentation http://www.postgresql.org/docs/8.3/static/app-pgdump.html doesn't say anything about this.

From the manual:

  It makes consistent backups even if the database is being used
  concurrently.

So yes, you can trust the backup. Of course, it's PostgreSQL, you can trust your data in PostgreSQL.

First sorry for answering on this old thread. But from my experience I can not confirm the statement that you can trust pg_dump/

I switched some month ago for a project to postgres. And of course I made all backups with pg_dump from the live system as suggested. But after the first backup check I figured out that all dumps having different sizes. After restoring random some of them, I figured out that some tables are missing. So I started to analyze why this is happen and I figured out that if the overlaying application, e.g. app servers, locking some tables and pg_dump is not waiting until they are released. I was needed every time to stop the server for the backup period which is not a good solution.

So I still looking for a fix but as I said I tottally disagree with the statement that you can trust pg_dump.


Dump PostgreSQL without DROP DATABASE I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file).

Actually I use this command:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

But, in top lines of file, I have this:

DROP DATABASE...
CREATE DATABASE...

So, I my case, I want duplicate this database, and, if I use this script with pg_restore I drop the other database (I don't want that).

My understanding of pg_restore (which you'll have to use because you created a pg_dump with format=custom) is that if you supply the --dbname=... flag you will restore into that database (without dropping it).

If you supply the --clean command the db and objects will be dropped before being recreated.

If no --dbname=... flag is supplied, the creation commands will spool to stdout, which you can redirect to a file and load using psql.

To test, try:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql

Try below example, omitting the '-c' flag in pg_dump command:
pg_dump -C database_name | xz > backup-$(date -u +%Y-%m-%d).sql.xz

Answering comments (not sure if this is the best way to do it):

can you explain what that does exactly?

Performs a dump of the database and compress it to a file named with the current date

OP didn't have a -c or -C flag, and it was a custom format dump. I don't see how your answer contributes anything useful.

Well my answer tries to provide a solution to the post title, which is: Dump PostgreSQL without DROP DATABASE. If there are additional requirement to OP question, worth editing the post title.


Cannot connect to an Hana database via its ODBC driver I have not found any documentation about ODBC connection strings for Hana and am trying to connect using the examples in SAP HANA Database Client Interfaces.

An attempt to connect via ODBC with the connection string:

DRIVER=HDBODBC; SERVERNODE=192.168.0.213:30015; ID=SYSTEM; PWD=PASSWORD;

fails with this error:

ERROR [08S01] [SAP AG][LIBODBCHDB DLL][HDBODBC]
Communication link failure;
-10709 Connection failed (RTE:[0]  (192.168.0.213:30015))

whereas the corresponding ADO.NET connection works:

Server=192.168.0.213:30015;UserName=SYSTEM;Password=PASSWORD;

What may be wrong with the ODBC connection? I have the correct driver installed.

you might require to install SAP HANA Database Client for ODBC configuration

Please refer to above linked document for a sample case where HDBODBC is installed for connection to HANA database via ODBC

After HDBODBC configuration is created as illustrated in referenced tutorial, you can build your connection string as follows referencing the System DSN name with DSN argument

"DSN=KodyazHANADb;SERVERNODE=myhdb.kodyaz.sap.biz:30815;UID=A00011462;PWD=myPassword+;DATABASENAME=K0D"

You can find the database name in SAP HANA Studio connection properties or by querying M_Databases system view


SQL Developer - trying to import an excel file into a table I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008  12:00:00 AM
and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM

To import the file, I am right clicking on the table in SQL Developer, hitting import data and then selecting the file, I map the columns and set the format to above and get the above error. Here's the table:

--------------------------------------------------------
--  DDL for Table DRUG_INFO_NOS
--------------------------------------------------------

  CREATE TABLE "DANIEL"."DRUG_INFO_NOS"
   (    "DIN" VARCHAR2(20 BYTE),
    "TRADE_NAME" VARCHAR2(50 BYTE),
    "FORM" VARCHAR2(40 BYTE),
    "GENERIC_NAME" VARCHAR2(60 BYTE),
    "MANUFACTURER" VARCHAR2(100 BYTE),
    "CR_DATE" DATE,
    "NOC_DATE" DATE,
    "FIRST_CLAIM_DATE" DATE,
    "SEQUENTIAL_ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATAL03" ;
--------------------------------------------------------
--  Constraints for Table DRUG_INFO_NOS
--------------------------------------------------------

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("DIN" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("TRADE_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("GENERIC_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("MANUFACTURER" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("SEQUENTIAL_ID" NOT NULL ENABLE);

but I am getting the above error. What's up?

This link shows how to deal with importing excel file and invalid date format: how-to-import-excel-to-oracle. Hope this helps.


Killing a process without login to MySQL Here is the situation:

I was running a very complex query on a database which was expected to execute within a few hours over night(server configuration is not good and data set is large). This query is using table tblA
while the query is being executed, application A (running on the Server) executes  DROP TABLE tblA.
This DDL Statement locks the metadata for tblA since table is in use by another transaction (my query).
Application B comes in and wants to perform an update on tblA.
Update statement does not execute since the DROP TABLE tblA holds the metadata lock.
Application B is multithread and executes another update on tblA
Application B keep doing that until max_connections limit is met and I receive Too Many Connection error every time I try to connect to server.
Right at this moment the application I was using to execute the query (HeidiSQL) stop working and crashes.
I just noticed all the connection to server is through Root user from local host so there is no way to log in to MySQL to kill the DDL statement.

Question is:

Is there anyway to end this without having to kill or stop MySQL Server?

I already tried killing Application A and B but the process still remains in MySQL queue. It also sounds like my query is not willing to end any time soon.

For cases like this, there is "one extra connection available".  Anyone with SUPER priv (eg. "root") can get in to kill the DROP, which would have uncorked the mess.

However, a prerequisite for doing that is to not have users and applications log in as "root".  Instead they should have their own logins, preferable (for security reasons) limited to the database that they need access to.

Reserve "root" for 'admin' actions such as the one you needed in this case.

Try "mysqladmin flush-hosts" on the mysql server.


How to convert numeric values to text in sql for select query I have string values like this

'[123, 124]'

I could trim the value using below command

select trim('[123, 124]', '[]'); --returning '123, 124' as text

I want to pass the value above as

select *
from mytable
where numeric_column in (select trim('[123, 124]', '[]'));

I can understand, numeric_column is numeric. But the inner query is returning the data as text. I could not convert the inner query to numeric since it has a comma.  If I want to convert the result to '123', '124', I could run the following command and get the expected result:

select *
from mytable
where numeric_column::text in (
    select func_to_change(select trim('[123, 124]', '[]'))
);

How can I achieve this? What is the func_to_change logic I need to write?

Note: I am using Postgresql 9.1.

Once you have removed the [ and ] you can use string_to_array() to convert the list to an array of integers. This can be used directly in the where clause:

select *
from mytable
where numeric_column = ANY(string_to_array(trim('[123, 124]', '[]'),',')::numeric[])

Simply,

from

123, 124

to

'123','124'

You can use String_agg function


For loop in query - how can i use Union? hello friends I ´m working on a module that graphs data from a database, the thing is that I must graph a series of data that went through a mathematical formula first. So far no problem, but I must take data from the months of a year, therefore I must repeat this process twelve times. My truth question is this:

  How can i get just one row an column per response?

If I try something like this ...

DECLARE @cnt INT = 0;
WHILE @cnt < 12
BEGIN
select idestatus from order
SET @cnt = @cnt + 1;
END;

I'll get 12 queries executed & 12 tables And i just need one.
I will eventually transform this into a json, so I would like to have everything in a single response table
.This my query:

declare
@a decimal(5,2) = 0,
@d decimal(5,2) = 0,
@f1 decimal(5,2) = 0,
@f2 decimal(5,2) = 0

set
@a = (
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set
@d = (
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)
set @f1 =
(
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set @f2 =
(
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)

select
 (((@a + @d)/2) *100)/((@f1 + @f2)/ 2) rf

If also some can tell me how to order this by a UNION , that could be really usefull to.

You can simplify your query above with this example, but it suffers from some limitations caused by our lack of knowledge of the schema of your table as well as what your final objective really is.

This should do in one step what your query does, but it has hard coded idEstatus values (which may be ok) and is limited to one month.

It's not clear form your example query if you really need the second half since it appears to be the same date (if you wanted year over year then the correct answer is different).

 ;WITH CTE_Details AS
    (
    SELECT YEAR(dat) AS [year]
        , MONTH(dat) AS [month]
        , idEstatus_Count_2 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 2 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
        , idEstatus_Count_3 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 3 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
    FROM t1
    WHERE idEstats IN (2,3)
        AND dat BETWEEN CAST('1/1/2017' AS DATETIME) AND CAST('1/31/2018 11:59:59 PM' AS DATETIME)
    GROUP BY YEAR(dat)
        , MONTH(dat)
    )
SELECT [year]
    , [month]
    , idEstatus_Count_2
    , idEstatus_Count_3
    , rf = (((idEstatus_Count_2 + idEstatus_Count_3) / 2.0) * 100) / ((idEstatus_Count_2 + idEstatus_Count_3) / 2.0)
FROM CTE_Details

It sounds like you need a group by

    select
    datepart(year, dat) as year, datepart(month, dat), COUNT(idEstatus) A_D
    from t1
    where idEstatus = 2
    group by datepart(year, dat), datepart(month, dat)


We are running database maintenace with indexing the system, indexing fails due to locking? We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be due to deadlocks

the indexing script we use is:

--------  REBUILDING THE INDEXES----------------------

CREATE TABLE #TableIDs (Tablename nvarchar(200),IndexName nvarchar(1000))
        --Database to be indexed--
        INSERT INTO #TableIDs (Tablename, IndexName)
        SELECT a.object_id,name
        FROM sys.dm_db_index_physical_stats (DB_ID(N'RAL'), NULL, NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
        Where avg_fragmentation_in_percent > 20 and name is not null  and OBJECT_SCHEMA_NAME(a.object_id) = 'dbo'
        And object_name(a.object_id) not in (SELECT
                                             OBJECT_NAME(p.object_id) BlockedObjectName
                                             FROM sys.dm_tran_locks AS tl
                                             INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                             INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                             INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                             INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                             INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                             CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                             CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                             Where db.name = 'RAL')

declare c cursor for select object_name(Tablename) as Tablename ,IndexName from #TableIDs
declare @TableName varchar(1000)
declare @IndexName varchar(1000)
open c
fetch next from c into @TableName, @IndexName
        while @@FETCH_STATUS = 0
        BEGIN

              if ((SELECT
                                         count(OBJECT_NAME(p.object_id)) BlockedObjectName
                                         FROM sys.dm_tran_locks AS tl
                                         INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                         INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                         INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                         INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                         INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                         CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                         CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                         Where db.name = 'RALMYT' and OBJECT_NAME(p.object_id) = @TableName) ) = 0
                BEGIN
                    EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (FILLFACTOR=90,STATISTICS_NORECOMPUTE = OFF,ONLINE=OFF,MAXDOP=8)')
                    PRINT 'Reindexing ' + @IndexName + ' on  ' + @TableName + ' table'
                END
     fetch next from c into @TableName, @IndexName
End
CLOSE c
DEALLOCATE c

DROP TABLE #TableIDs

Please let me know..... how to avoid the errors or failures while indexing

If the requirement is to rebuild the indexes while letting users continue to work, you will find this extremely challenging, if not impossible, to meet if you can't do online index rebuilds, which are only available in Enterprise Edition.

There are really only two types of solutions:

Use online index rebuilds.
Do index REORGANIZE instead of REBUILD -- reorgs are always online operations, but may not do as good a job as a rebuild, and may run more slowly in comparison.

Regardless, instead of rolling your own scripts, I would highly recommend using an already-built-and-tested solution such as Ola Hallengren's maintenance scripts, which will intelligently maintain your indexes based on their current state of fragmentation. The indexes may not actually need to be rebuilt all the time.

Rather than re-index which will lock tables why not set up a defragment index solution instead?. I use one that combines showcontig with a defragment and does not seem to cause any issues. you can set the fragmentation level...

CREATE PROCEDURE  dbo.[zz_maint_Defragindexes]

AS
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 25.0--can adjust this

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist


Table function in oracle I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that will do that. Now, as an example, to see if I can get it working, I've created this so far:

create or replace type test_row as object (date_id number,day date)

and

create or replace type test_table as table of test_row

and written this function:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select td.date_id, td.day
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;

but the compiler throws an error. Specifically is says:

PL/SQL: SQL Statement ignored for the select td.date_id, td.day part and

PL/SQL: ORA-00947: not enough values on  from T_D_DATES td

but I am not completely sure how to fix it. I've tried to make a PIPELINED
 function but got lost on how to insert all my values from my select into the table type.

Funtions aren't really my forte and especially ones that return a table as a result. So, how can I write a function that would work with the sample select?

P.S: I've looked online for solutions but it's either a super simple loop that writes some hardcoded values or something really complex and I can't decipher it.

That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select test_row(td.date_id, td.day)
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;
/

Can you please try to use the proper alias syntax?

from T_D_DATES as td

But I don't really see the advantage of receiving a temporary table made up of select results over JUST receiving said select results....

So in my eyes the whole test function (if you want some kind of stored procedure or similar) could be shortened


T-SQL CREATE VIEW returning 'There is already an object named x' SQL Server 2014. I have a database on which I created a view called 'Sales-Analysis'. I also have a second database with the same schema as the first and I want to add the view to that. So in SSMS I performed 'Script view as CREATE to new Query Editor window'. I changed the first line of the script to reflect the name of the second database and ensured it was selected in the SSMS databases dropdown too. However when I execute I get:

"There is already an object named 'sales-analysis' in the database."

There isn't other than the one in the original database. No temporary tables involved. VS 2015 is closed.

It also doesn't work even if I take the original db offline, or if I save the script as an .SQL file and execute it with SQLCMD.

Same server. Refreshed views. Can successfully create a view with a '2' appended into the target database. It's just when the view name is the same as the original database. use [database] is already at the beginning of the script -'Script view' adds it. I changed it to the target database name. The view 100% does not exist in the target db. I checked via querying sys.views.

Turns out the target db had a TABLE (not view) with the same name. I didn't create it, I suspect what has happened is that an Entity Framework project which I have created it when I changed the app.config connection string to point at the second database and ran the project.

Naughty Entity Framework.

Add a drop statement in front of your create.

Take note that this is dangerous.


How can I rearrange these two queries to get the second column from inside the IN() How can I get the random.id column from inside this first select

select id, name from random where id IN(1,2,3,4);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Frank     |
|  2 | Tracey    |
|  3 | Bos       |
|  4 | Charlotte |
+----+-----------+

select `where`, `who`, COUNT(*) from cards where who = "." OR who IN(select name from random where id IN(1,2,3,4)) GROUP BY `who`, `where`;
+-------+--------+----------+
| where | who    | COUNT(*) |
+-------+--------+----------+
| pack  | .      |       78 |
| hand  | Frank  |       15 |
| hand  | Tracey |       15 |
|       | .      |       20 |
+-------+--------+----------+
4 rows in set (0.00 sec)

out when using the second more complicated select, above. Both tables have more columns than show and I do not want to add an extra row to the random table simply to define the . as comes out of the cards table. I think it should be possible to rearrange the second query to provide the desired output of where, id, count but sadly it eludes me.

The results I would like would look like below

+-------+-----------+--------+----------+
| where | random.id | who    | COUNT(*) |
+-------+-----------+--------+----------+
| pack  | NULL      | .      |       78 |
| hand  | 27        | Frank  |       15 |
| hand  | 32        | Tracey |       15 |
|       | NULL      | .      |       20 |
+-------+-----------+--------+----------+

The random.id column can be NULL or a consistent number, zero would be fine, and the who column is only a nice to have. Thanks for the welcome.
The Mysql version is 8.0.19-0ubuntu0.19.10.3 however it may well be better if the version was considered to be broader as I hope to run it on a server over which I have no control.

~~~

Well I have an answer of sorts, not what I was looking for but by divide and conquer two "simpler" queries supply me with the data I need. The data has changed so the numbers in the answers look different but the rules are still the same.

select `where`, 0 AS id, who, COUNT(*) AS cnt from cards WHERE who = "." AND `order` != 0 GROUP BY `where`;
+---------+----+------+-----+
| where   | id | who  | cnt |
+---------+----+------+-----+
| pack    |  0 | .    |  63 |
| dis_top |  0 | .    |   1 |
| discard |  0 | .    |   1 |
+---------+----+------+-----+

The data when there is no who defined.

SELECT `where`, id, who, cnt FROM random AS a RIGHT JOIN (SELECT `where`, who, COUNT(*) AS cnt FROM random AS b INNER JOIN cards AS c ON b.name = c.who GROUP BY `who`, `where`) AS d ON a.name = d.who;
+-------+------+-----------+-----+
| where | id   | who       | cnt |
+-------+------+-----------+-----+
| hand  |    1 | Frank     |  11 |
| hand  |    2 | Tracey    |   7 |
| table |    2 | Tracey    |   2 |
| hand  |    3 | Bos       |  11 |
| hand  |    4 | Charlotte |  11 |
+-------+------+-----------+-----+

The data when there is a who defined.

I have not claimed to have answered my own question as I still think it must be possible to do it a single query, but....

#Inner Join both the tables:

SELECT random.id, `where`, `who`, COUNT(*)
FROM cards
JOIN random
ON cards.who=random.name

#I assume you want all the values from `who` column from table:cards, so there's no need to use WHERE again.


Teradata: How to design table to be normalized with many foreign key columns? I am designing a table in Teradata with about 30 columns.  These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc.  It is bad design to store the actual string values in the table since this would be an attrocious repeat of data.  Instead, what I want to do is create a primitive lookup table.  This table would hold Daily, Monthly, Weekly and would use Teradata's identity column to derive the primary key.  This primary key would then be stored in the table I am creating as foreign keys.

The table would be designed like this:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
2   Interval     Monthly
3   Interval     Weekly
4   TimeFrame    24x7
5   TimeFrame    8x5

This would work fine for my application since all I need to know is the primitive key value as I populate my web form's dropdown lists.  However, other applications we use will need to either run reports or receive this data through feeds.  Therefore, a view will need to be created that joins this table out to the primitives table so that it can actually return Daily, Monthly, and Weekly.

My concern is performance.

I've never created a table with such a large amount of foreign key fields and am fairly new to Teradata.  Before I go on the long road of figuring this all out the hard way, I'd like any advice I can get on the best way to achieve my goal.

Disclaimer: I have never built a Teradata system, so I can't claim this from first-hand experience, but I will explain the reasoning.

I think that Teradata will be able to produce this view efficiently.  From what you say, it appears to do little more than join some very small dimension tables against a fact table.  The join operations will be relatively efficient.  Unless I misunderstand your requirements these columns are allowing your application to select various rollups of data from a multi-grain fact table.

Even though Teradata is a shared-nothing system, I can't see any requirement for the view to push large semi-joins across nodes or anything like that.

Beyond that, all I can suggest is that you suck it and see.  If you don't have anywhere to experiment you could download the express version of Teradata off their web site and see if you can prototype this structure to see what the query plan actually is.

You can consider using a "smart key" instead of a meaningless sequential value, which you would then be able to use without necessarily joining with this lookup table:

ID  Type         Value
--- ------------ ------------
1   Interval     Daily
7   Interval     Weekly
30  Interval     Monthly
85  TimeFrame    8x5
247 TimeFrame    24x7

You could then have queries on the fact/main table filtering by the lookup (schedule?) column, without the need for JOINs:

SELECT ...
  FROM MainTable
 WHERE schedule = 30

This way, you can have your dropdown being populated with values from the lookup table, and have efficient queries on the fact/main table.


pg_dump 9.2.x command does not work with pg_dump 9.2.3 This is how i used to create all the time backups of my databases with PostgreSQL 9.2.x:

pg_dump -Fc -Z 6 -U postgres mydatabase > 2013-xx-xx_xxxxx.db

However on a machine where PostgreSQL with version 9.2.3 is running I get all the time the error "too many command line arguments" starting with "-Z". If I remove -Z he is complaining about "-U"?
What is wrong?

Okay there is definitely sth. wrong. I removed argument for argument until i just started pg_dump. Then i added argument by argument and it worked. So I opened another command line window and tried the same: First "too many arguments", after calling only "pg_dump" and adding further arguments it worked...

I'd venture that it has nothing to with pg_dump version but that it may be an invisible spurious character on the command line that is causing this mess. As a demo, if I type this in cmd.exe, it works as expected at first:

  pg_dump -U postgres postgres >out.db

But now if I come back on this command with Arrow-Up, position the caret just before the -U option, add a non-breaking space by hitting Alt+255 on the numeric keypad, and validate this command, it yields the error you mention:

  pg_dump: too many command-line arguments (first is "-U") Try "pg_dump
  --help" for more information.

pg_dump --host <host> --port 5432 --username <login> --format custom --blobs --verbose --file /home/backup/databasename.backup databasename

To run query without asking for password use export PGPASSWORD=your_postgres_password (linux) or SET PGPASSWORD=your_postgres_password (windows)


SELECT id
    ,product_id
    ,date
    ,weight
FROM
(
SELECT t.id
    ,t.product_id
    ,t.date
    ,t.weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date < t.date ORDER BY date LIMIT 1) AS prev_weight
    ,(SELECT weight FROM table WHERE product_id = t.product_id  AND date > t.date ORDER BY date LIMIT 1) AS next_weight
FROM table AS t
) AS t1
WHERE weight <> prev_weight OR weight <> next_weight

select
    d.id,
    d.product_id,
    d.date,
    d.weight,
    d2.weigth "PREVIOUS WEIGHT",
    d3.weigth "NEXT WEIGHT"
from dataset d
cross join (
    select weight
    from dataset dp
    where dp.product_id=d.product_id and dp.date<d.date
    order by dp.date desc
    limit 1
      ) d2
cross join (
    select weight
    from dataset dp
    where dp.product_id=d.product_id and dp.date>d.date
    order by dp.date asc
    limit 1
      ) d3


Speed up a MySQL BETWEEN query SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo

I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind,
dateFrom, dateTo and a combination of dateFrom and dateTo.

But nothing changed, with or without indexes the query time is the same. I know BETWEEN doesn't use indexes, is it true?

As  M Khalid Junaid suggested, this is the EXPLAIN PLAN of the above query:

+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
| id | select_type | table        | type | possible_keys                  | key  | key_len | ref  | rows  | Extra                                          |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | table1       | ALL  | dateToFind                     | NULL | NULL    | NULL | 32027 |                                                |
|  1 | SIMPLE      | table2       | ALL  | dateFrom,dateTo,dateFrom_dateTo| NULL | NULL    | NULL |  6951 | Range checked for each record (index map: 0xE) |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+

Any suggestion?

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add:
Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |


DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format
Thought to get the experts opinion.

Below options to convert / cast didn't work:

CONVERT(VARCHAR(19),CONVERT(DATETIME, wfm.date,3),112)
convert(varchar(50), cast(wfm.date as datetime2), 112)
cast(cast(wfm.date as datetime2(15))as datetime)

Getting the error message for convert syntax as

  Conversion failed when converting date and/or time from character string.

For cast syntax getting the error message:

  Line 1: Specified scale 15 is invalid.

That's very complex. It's easier than that. Just run,

ALTER TABLE foo
    ALTER COLUMN dt datetime2 NOT NULL;

Where dt is the name of your varchar date field. Now you have it in a date field, and you can simply,

SELECT convert(varchar, mydate, 112)
FROM foo;


What happens if transaction rollback fails? So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original state?

The database in question is postgresql.

I think your question hits the core of the database reliability properties; the ACID properties. They are there in order to guarantee the database's reliability. The transaction log plays a vital role here. You probably know the ACID properties, so Iam not diving into that.
If a rollback fails, then you would have a serious problem. The reliability of the database cannot be guaranteed. In other words; you probably have some sort of corruption in your transaction log and will end up with an inconsistent database. I am not sure how it works with PostgreSQL, but for MS SQL Server it will set the DB offline and restarts it, which will trigger a recovery action and it will try to rollback the transaction again. If that doesn't work you will probably have to restore the database from an earlier backup (log, differential or full).


Always On log files bigger than database files We had SQL RAP and we got warning that Transaction log files larger than the database files.

They advised me to remove databases from Always ON, change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day  transaction log files are larger than the database files.

As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always On cluster, which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is no such problems.

So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

I have looked at Transaction log is growing very fast. I only have log_reuse_wait=2 in between log backups, everything looks to be working as expected but it is not.

I checked the log send queue and REDO queue size, and it looks good, as it only has "2 Log backup You are waiting for a log backup to occur." while it waits for next log backup. AOAG health is OK and bandwidth is more than enough. Log backup is every 15 minutes.

So I am not sure why is this happening, is it related to Always On?

It is and also isn't related to Always On. Always On Availability Groups requires that the database be in the full recovery model - which is why you need log management in the first place. So long as your secondary replicas are staying very close to the primary (i.e. no lagging secondary replicas for hours or days) log management shouldn't be affected very much due to AGs.

  I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

Always On Availability Groups does not inherently need larger log files. Generally it ends up being the cause because most databases are in the simple recovery model before the change is made to Availability Groups and thus log managements is a new concept to that database.

If the database was in the Simple recovery model and the log grew to X GBs, then the minimum log size will be X GBs regardless of recovery model, Always On, etc. However, since log management isn't automatic anymore a random number (generally 30 minutes or an hour) is arbitrarily chosen and log management happens on those intervals. Now the log will need to grow to accommodate the changes in log management automation.

Much as @Shawn Melton and @RDFozz have stated in the comments - if you don't want the log growing too large and all of your replicas are properly in sync, then have your log management happen more frequently.

  As this is running SharePoint 2013 databases ...

SharePoint is known for having their own timer jobs which run internal maintenance items and storing blob data. This, on its own, will cause poor log management and there is simply nothing you can do about it. Thus it doesn't surprise me that the log the this database is larger than the data files.

It's never a good sign when the datafiles are smaller than the log, but I do believe in this case - unless you change the log management automation to shorter intervals [And even then it depends on what SharePoint does to the DB] it'll most likely end up staying that way - and that might be normal. In that case we can mark it as normal and go about our day.

  We had SQL RAP and we got warning that Transaction log files larger than the database files.

That's a great step in the right direction (I work for Microsoft) of having a risk and health check of your instance(s). This specific check is something that we us to look for databases that may be having improper or no log management. In some cases (I've personally dealt with) the DBA has said, "Ooops that shouldn't be in bulk-logged..." and we've changed it back to Simple because it didn't need any point in time or extended recovery options. That's ok! The check does, however, give everyone a opportunity to look at the databases in question and make sure that something isn't wrong and that's the key distinction. Like I said above, this might just be normal and that's ok - however we wouldn't be doing our jobs properly if we just left it alone and didn't say anything, it's important.

Anecdotally when I was a DBA for a large clothing company, we had a 3rd party application that used a cursor to loop through all employees every 15 minutes and run a stored procedure which deleted all of their history and then rebuilt it from scratch... in a single transaction. The database was 50 GB and the log file for it was.... 280 GB. That's how the app worked, I logged complaints and possible code changes with the 3rd party vendor and was summarily dismissed. It happens and some application just run that way.

I followed these steps it worked for me:

  From sp_Blitz Result: Transaction Log Too Large?

  Make sure you’re in the right recovery model
  Set up transaction log backups if you need point in time recovery
  Shrink your transaction log file (maybe)

More details in the linked post.


If all tables are myisam, should I use myisam engine instead of default innodb? I have read all of the innodb/myisam threads and they are confusing.  Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.?

The software I am using creates myisam tables so I was wondering if I should switch from the default innodb engine to myisam engine and disable innodb since I do not have a single table that is innodb?

Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software.  There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide.

Turning off InnoDB because you aren't using it is also an exercise in futility and short-sighted at best.  It is essentially just tinkering with your server for no good reason.  Leave that setting, and virtually all of the other settings, at their default values unless you have a specific reason to change them.  Tweaking parameters requires discipline and deliberate, well-informed action.

Oracle has officially deprecated the ability to do this, anyway.  As of now, in 5.6.23, disabling InnoDB still works, but results in a warning that it is a capability that will subsequently be removed... which should tell you something about whether it's a good idea.
http://dev.mysql.com/doc/refman/5.6/en/innodb-turning-off.html

"Using innodb engine" means that INNODB would be the default engine for creating tables, i.e. if you CREATE TABLE a ... without specifying an engine for the table, it will be created according to the default engine.

If your tables are already created as MYISAM tables, it's meaningless to "use innodb". You are actually using MYISAM.

In other words - the DB has no engine, only tables have an engine...

See the manual here.

EDIT After question was edited.

YES - you should disable InnoDB.
Why? I assume this would save memory (but that's guessing).

From the manual, see the 2nd paragraph:

  Turning Off InnoDB

  Oracle recommends InnoDB as the preferred storage
  engine for typical database applications, from single-user wikis and
  blogs running on a local system, to high-end applications pushing the
  limits of performance. In MySQL 5.5, InnoDB is the default storage
  engine for new tables.

  If you do not want to use InnoDB tables, start the server with the
  --innodb=OFF or --skip-innodb option to disable the InnoDB storage engine. In this case, because the default storage engine is InnoDB,
  the server will not start unless you also use --default-storage-engine
  to set the default to some other engine.


Query with conditional JOIN running slow I have a query the first part of which looks like this -

With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    OR
                    B.col3= 'Y' and B.col4=A.col4
                    OR
                    .
                    .
                    .
                    .
                    )

)

Now this query runs for more than 4 minutes and the execution plan showed a table spool which was data size of 600 MB and looked like this -

I changed this to conditional JOIN to UNIONs -

With CTE AS (
select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'X' and B.col4=A.col3
                    )
    UNION
    select
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A
                    JOIN tableB B ON
                    (
                    B.col3= 'Y' and B.col4=A.col4
                    )
     UNION
     .
     .
     .

 )

Now the spool with the large number of rows vanished and the run time reduced to 17 seconds. However when I check the IO statistics, the logical reads are higher than the previous slower query. What could the reason be ?

The reason is that AND and OR has the same precedence and the long condition x AND y OR z AND w OR v AND... without round brackets has a bit different meaning than you think.

Your query can be rewritten that way:

FROM tableA AS A
JOIN tableB AS B ON ( B.col3 = 'X' AND B.col4 = A.col3 )
                 OR ( B.col3 = 'Y' AND B.col4 = A.col4 )
                 OR (. . . . .)


Changes access method for non-correlated subquery Oracle 11g R2

Unfortunately our application has per row security "features".
We have a query that looks about like this:

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM bigTableA_securitymapping b
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
       b.accesscode in (SELECT accesscode
                              FROM accesscodeView
                              WHERE user = :someUserID)
)

There a unique index on bigTableA_securitymapping(PrimaryKeyTableA,accesscode).

The accesscodeView could potentially return more than one accesscode for a given user, so it must be IN() and not =.

The issue is that this query ignores the unique index for bigTableA_securitymapping and chooses to do a full table scan.

If I change the IN() to an = then it does a UNIQUE SCAN on the unique index on bigTableA_securitymapping and is about 50 times faster.

Good, fast but not possible:

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
           b.accesscode =(SELECT distinct accesscode
                                  FROM accesscodeView
                                  WHERE user = :someUserID)
    )

But, I cannot do that because the accesscodeView may return more than one row.

(There's a distinct in there because the accesscodeView needs it given the =, putting the DISTINCT on the original query makes no difference.)

If I hardcode the accesscodes, it also does a UNIQUE SCAN on the unique index for bigTableA_securitymapping.

Good, fast but requires large application change:

SELECT someRow, someOtherRow
      FROM bigTableA a
      WHERE EXISTS (
        SELECT 0 FROM bigTableA_securitymapping b
        WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
             b.accesscode in (1,2,3,4)
      )

Changing to a join inside doesn't really help either. It still does a full table scan.

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM accesscode ac INNER JOIN
                bigTableA_securitymapping b ON
                  ac.accesscode = b.accesscode
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA
        AND user = :someUserID
)

So why the difference between = and IN() in.  And why does a non-correlated subquery (the accesscodeview subquery) cause such a plan difference?  Is there any way to rewrite it to do what I want?  The difference in 'good plan' costs vs 'bad plan' costs here are 87 vs 37,000 and a large amount of time in real runtime for the same results.

The database may be ignoring your index if your statistics are stale (including both the system and table statistics) or if your initialization parameters are not optimized. The default optimizer cost adjustment is likely wrong for most applications, for example.

Here is what I would do, although I do not exactly see the whole picture here and maybe I am missing something. An example would help refine my answer if its not useful.

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA
           AND EXISTS (SELECT 1 FROM accesscodeView
                          WHERE user = :someUserID
                          AND accesscode = b.accesscode)
    )

It will always return a single row rather than scanning for a second (or more, if there are more)


How to find the max date stamp in a database? I want to find the most recent date in a database.

I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last server restart

I could use MAX()

use adventureworks
select Max(ModifiedDate)
from HumanResources.Department

But I would need to look at every table in the database, find each datetime (or date, smalldatetime, datetime2) field and run the query multiple times, then decide which was most recent.

I want something like

use <some_db>
select MAX(<FieldThatIsADate>)
from <anytable_in_db>

I have Googled around and am not finding a way to use the datatype in place of a field name. I have not attempted to find a way around naming the specific table.

How can I make this query as portable as realistic?

If I have to build and run a unique query for every table in each database, well that is what I have to do.

There is no way to query on a datatype much less across various tables like that, it would imply that the entire database is kept a second time in a set of tables that resemble the system tables. However, dynamic SQL using the INFORMATION_SCHEMA can be of assistance in your search, but it will not be portable to other types of SQL Servers. Most servers should have something similar available but the syntax will be radically different for each one.

You can do the following to find what you're looking for:

SELECT 'SELECT max(modifiedData) FROM ('+
        (SELECT 'SELECT MAX(['+C.COLUMN_NAME+']) as modifiedData FROM ['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+'] UNION ' AS [text()]
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.DATA_TYPE IN ('date','datetime','smalldatetime','datetime2')
        FOR XML PATH('')) + ') AS AllDateTimes'

That dynamic SQL will output a query you can run to find your MAX date. Please note that you will need to trim a trailing UNION from the inner SELECT after you've copied the output to a query window. You can create the same query with the sys tables but you will have to join a few of them together and the query will be a bit more complex if you aren't familiar with how the sys tables are setup.

That all said, this will return the maximum date stored in your database regardless of context. Depending on how it was used there is no guarantee some value could not have been set in the 'future' for any number of reasons.

this query can give all datetime columns with their max value in a table on sql server. latest date values are on the top.

SET NOCOUNT ON;

SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS ID, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 'SELECT @Result=CAST(MAX(['+COLUMN_NAME +']) AS DATETIME2)  FROM [' + TABLE_SCHEMA + '].['+ TABLE_NAME +']'  AS Script, CAST(NULL AS VARCHAR(MAX)) AS Value
INTO #datetimeColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE  IN ('datetime', 'smalldatetime', 'datetime2');

DECLARE @ID INT, @script NVARCHAR(MAX), @Result VARCHAR(50);

DECLARE cursor_datetimeColumns CURSOR FOR
SELECT ID, Script FROM #datetimeColumns;

OPEN cursor_datetimeColumns;
FETCH NEXT FROM cursor_datetimeColumns INTO @ID, @Script;

IF @@FETCH_STATUS <> 0
        PRINT 'No datetime column in this database'

    WHILE @@FETCH_STATUS = 0
    BEGIN

            EXEC sys.sp_executesql @script, N'@Result varchar(50) output', @Result OUTPUT;

            UPDATE #datetimeColumns
            SET Value = @Result
            WHERE ID = @ID;

            FETCH NEXT FROM cursor_datetimeColumns INTO @ID, @Script;
    END
CLOSE cursor_datetimeColumns;
DEALLOCATE cursor_datetimeColumns;

SELECT *
FROM #datetimeColumns
ORDER BY CAST(Value AS DATETIME2) DESC;

DROP TABLE #datetimeColumns;


Interpretation of multiple join order I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.

If I use joins in between, I don't know how to use Left, Right, etc. in the SQL statement from priority aspect. I mean is it organized with parentheses or something else?

Relations for example are like following:

My query is as following:

SELECT T1.col1, T2.col2, T3.col3, T4.col1 AS Expr1
FROM T1
INNER JOIN T1_T2 ON T1.id = T1_T2.T1_FK
INNER JOIN T2 ON T1_T2.T2_FK = T2.id
LEFT JOIN T2_T3 ON T2.id = T2_T3.T2_FK
INNER JOIN T3 ON T2_T3.T3_FK = T3.id
LEFT JOIN   T3_T4 ON T3.id = T3_T4.T3_FK
INNER JOIN T4 ON T3_T4.T4_FK = T4.id

How does SQL Server interpret the joins? Can one represent it like execution priorities with parentheses? I mean: ((T1 INNER Join T1_T2) INNER JOIN T2) LEFT JOIN T2_T3) ?

select tbl1.col1,tbl2.col2,tbl3.col3,
tbl4.col4,tbl5.col5,tbl6.col6,tbl7.col7,tbl8.col8,
tbl9.col9,tbl10.col10

from tbl1.col1 as tbc1

left outer join tbl2.col2 as tbc2 on tbl1.PK=tbl2.FK
left outer join tbl3 as tbc3 on tbl2.PK=tbl3.FK
left outer join tbl4 as tbc4 on tbl3.PK=tbl4.FK
left outer join tbl5 as tbc5 on tbl4.PK=tbl5.FK
left outer join tbl6 as tbc6 on tbl5.PK=tbl6.FK
left outer join tbl7 as tbc7 on tbl6.PK=tbl7.FK
left outer join tbl8 as tbc8 on tbl7.PK=tbl8.FK
left outer join tbl9 as tbc9 on tbl8.PK=tbl9.FK
left outer join tbl10 as tbc10 on tbl9.PK=tbl10.FK

where tbl1.col1='some particular column value'
Group By tbl1.col1
Order tbl1.col1;

I am writing the sql query but it will depend on your table structure . How many there is PK and FK in your each table.


Database Blocking, kill the session which is responsible after 5 minutes I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This application uses typical primary key-foreign key relationship and all the tables are tightly coupled. Few tables have as many as 30 foreign keys and hence whenever there is an insert/update/delete, all of those underlying tables get blocked. I have communicated this to vendor and they also have tried to remove PK-FK for possible tables and deployed in the test environment however its taking ages to go to production as lot needs to be tested and its failing in some or other scenario.

Since, I am supporting vendor based application and any change needs to be tested by multiple stakeholders, I don't know when will it really go to production. Blocking becomes so severe at time that whole portal goes down and I am left with no option but to kill the blocking SPID.

I am using below query to check the blocking by using sp_whoisactive procedure Mr. Adam Machanic:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC',

So far, I was working on reactive approach meaning, I was killing SPID who are responsible for blocking however this doesn't seem to go well and sometime blocking escalates and I have to run like fire-fighter. I would like to switch my side from reactive to proactive and planning to put a procedure in place which will take input as SPID from above procedure, check the duration and if blocking is more than 5 minutes, kill it.

At times, I have seen there are many blockers responsible for blocking as shown below:

Don't really know how will these kind of blocking be handled. Read something similar by Mr. Jason Hall at this link and second one at this forum however I am looking for something having base of sp_whoisactive.

This is to add very pertinent point about database isolation that I have changed database isolation level from default to "Read Committed Snapshot". On the very same topic, I had asked this question when I joined this forum however it was closed as I was very new to this forum and was unclear on my question.

Any input/help on this will be appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)   Sep  7 2018 01:37:51   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3  (Build 14393: ) (Hypervisor)

You can use the SQL script I wrote up below to build a dynamic SQL string that will kill all blocking sessions that have been running for at least 5 minutes.
You'll want to create a SQL Agent job (SQL Agent Job Docs) that executes this SQL script. (You can schedule the SQL Agent job to run as often as every second if you want, but the Agent Job GUI only allows you to select a minimum of 1 minute when scheduling, so if you need greater frequency you have to script the job out.)
-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = '';

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER
INNER JOIN CTE_BlockingSessions AS BS
    ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
    AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL

Try with NOLOCK it may solve your problem.  It will probably will
not create any type of locking by executing Select query.

Disadvantages of NOLOCK, you may face dirty read problem. so use
NOLOCK very carefully.


Virtualized SQL Server Memory Oddity I've not seen this before, though perhaps it is common.  I am looking at a virtualized (VMWare 5.5) SQL Server (2008R2 on Windows 2008 R2). What I am seeing is that in Task Manager*, sqlserver.exe is using about 163 MB of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

The VM has 32GB of RAM and Task Manager shows 31.7 GB of that RAM being used.  The VMWare Perfmon counter does not seem to be indicating any ballooning (perhaps I read something the wrong way).

Thoughts/guidance appreciated.  I am trying to right size a new SQL VM.  At this point I have not yet obtained access to vSphere or the vCenter database.

* Using Task Manager because I was looking at overall memory utilization, not just SQL Server. I was warned by a performance touchy admin to keep my treading very very light.

What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

You should never look at task manager to check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 and above, to check SQL Server memory utilization.

select
(physical_memory_in_use_kb/1024) as PhyMemory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 ) as Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 ) as Total_MemoryUsed_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

Note:

PhyMemory_usedby_Sqlserver_MB is the committed memory and is backed by physical RAM.

Total_MemoryUsed_in_MB is the total memory used by SQL Server (RAM and page file)

You are making a mistake, which probably many 'newbies' make when referring to memory used by SQL Server. It would never show you correct memory utilization in task manager if Locked pages in memory privilege(LPIM) is there for the account running SQL Server service.  This is because the task manager only shows Process Private Bytes, the memory allocated via Virtual_alloc function which is pageable. Now if the SQL Server service account has LPIM, most part of the memory allocation would be done by AWE API. The memory allocated via AWE API is NOT pageable and thus does not shows up in task manager, which, in the end shows you different (incorrect) values.

Further reading:

Fun with Locked Pages, AWE, Task Manager, and the Working Set… (CSS SQL Server Engineers)

The tool I needed to figure it out was RAMMAP, which showed the AWE pool as the memory hog.  Yes, someone enabled AWE on a 64-bit OS with 64-bit SQL.


ERROR 1166 (42000) at line 65203: Incorrect column name '' I have a big snapshot database size is about 44GB in mydata.sql. When I run it overnight, it show this error message:

$ mysql -u root < mydata.sql
ERROR 1166 (42000) at line 65203: Incorrect column name ''

MySQL Version is 5.5.32-0ubuntu0.13.04.1 and this is a head from SQL file:

$ head -n 10 mydata.sql
-- MySQL dump 10.13  Distrib 5.5.22, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version   5.5.22-55-log

This is a snapshot of the line that making an issue:

$ sed -n '65200,65203p' < mydata.sql
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */

Line below 65203:

/*!50013 DEFINER=`mysql`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_payments` AS select concat(`paypal_payments`.`id`,1) AS `id`,
***SNIP****
,concat(`paypal_payments`.`payer_firstname`,' ',`paypal_payments`.`payer_lastname`,' ',`paypal_payments`.`payer_email`) AS `customer`
***SNIP***
 from `paypal_payments` union select concat(`payment_data`.`id`,2) AS `CONCAT(id,2)`,`payment_data`.`id` AS `id`,`payment_data`.`txndate` AS `txndate`,`payment_data`.`valuedate` AS `valuedate`,concat('DBS - ',`payment_data`.`category`) AS `CONCAT('T - ',category)`,(`payment_data`.`credit` - `payment_data`.`debit`) AS `credit-debit`
***SNIP***
,NULL AS `NULL`,NULL AS `NULL`,NULL AS `NULL`,`payment_data`.`note` AS `note`,`payment_data`.`remaining` AS `remaining` from `payment_data` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

How can I resolve this issue?

The only database object I can think of with the option CREATE ALGORITHM is a VIEW
To see all the views you have, run this
SELECT table_schema,table_name FROM information_schema.views;

or
SELECT table_schema,table_name FROM information_schema.tables WHERE engine IS NULL;

From the given error message, one of the views in your database has a column that no longer exists. This can easily happen if you drop a table or a column from a table that is referenced in the view.
Please look over all your views and make sure you can SELECT from all of them.
CORRECTIVE ACTION
You may have to recreate the mysqldump after you fix the VIEW in the source database.
If you cannot recreate the mysqldump due to the size, you may have edit the mysqldump to ignore the VIEW definition altogether.

In my case the issue happend with a view using union.
It's possible to define a view like

select a as acolumn,b,c from t where ...
union
select a,b,c from t where ...
union
select a,b,c from t where

so that the alias acolumn is only defined in the first part of the select. This
view definition caused trouble.

Changing it to

select a as acolumn,b,c from T where ...
union
select a as acolumn,b,c from T where ...
union
select a as acolumn,b,c from T where ...

fixed things. So two thing were changed:
* the alias was defined for all union parts
* the correct upper/lower case name for the table name was used
I do not know which one of these or whether both together where the culprit


SQL Server, Allow remote connections to this server option. What does this do? Can someone clue me in on what this option actually does and what would it affect if I were to suddenly uncheck it in an established production environment?

This checkbox is the GUI way of setting the remote access configuration option,

EXEC sp_configure 'remote access', 0; -- UI checkbox unchecked
EXEC sp_configure 'remote access', 1; -- UI checkbox checked

The feature is labelled incorrectly in the SSMS UI:

Incorrect: Allow remote connections to this server
Correct: Allow remote connections to from this server

and the documentation describes it wrong:

  Allow remote connections to this server

  Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

It should be:

  Allow remote connections to from this server

  Controls the execution of stored procedures from to remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from to a remote server.

Documentation from BOL 2000

SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:

  Configuring Remote Servers

  A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

  If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

   Note  Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

Disabling the option prevents outgoing connections

If you try to execute a stored procedure on a remote linked server (i.e. sp_addlinkedserver):

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

it will run fine.

If you then disable remote access on the local server:

EXEC sp_configure 'remote access', 0;, or
uncheck "Allow remote connections to from this server"

the same linked stored procedure will fail:

EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

  Msg 7201, Level 17, State 4, Procedure GetUnreadDocuments, Line 1
  Could not execute procedure on remote server 'Hyperion' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

Short version

Remote access controls outgoing access to remote stored procedures.

And the documentation incorrectly notes that using sp_addlinkedserver avoids the problem:

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead.

That is false.

See also

How to execute a stored procedure against linked server?

Enabling this will allow you to communicate with the SQL Server via any medium internally (VPN network) and externally.

Some practical uses are:

Third Party SQL server reporting application like:
https://www.manageengine.com/products/applications_manager/sql/ (You need to enable that checkbox and setup your TCP port using this application)
Multi-server Administration Jobs and S-Procedures

Note that TCP IP configuration is paired with this sometimes.


mysql 8.0, updates are sometimes stalling Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, innodb_flush_log_at_trx_commit or even setting innodb_flush_method to a wonderful knob O_DIRECT you discovered recently, this is not the case.
Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack during these periods.
Mysql config is the same, hardware is similar (same CPU, same amount of RAM and its type, same disk model (only the size is increased)), so the only two thing that changed is the FreeBSD release (11.x -> 12.x) and MySQL version (5.7.x -> 8.0). I really doubt this has something to do with FreeBSD 12, so I assume it's the MySQL 8.0 specifics.
Looking at the server performance I can say there's plenty of CPU available (at least 30% or more) and disk i/o is saturated at 50% at it's most. So the question is - what's in the 8.0 that makes updates to stall at some moments ?
Here's the update statement and it's profile:
UPDATE mp_user SET ts_update = NOW(), latitude = '42.962993621826',
longitude = '47.550437927246', accuracy = '515.91363525391',
source = 'lbs', batteryLevel = '81', steps = '0', turnovers = '0',
setCoordsTime = NOW() WHERE id = '3185997';

'starting','0.000294'
'Executing hook on transaction ','0.000072'
'starting','0.000066'
'checking permissions','0.000068'
'Opening tables','0.000144'
'init','0.000097'
'System lock','0.000095'
'updating','7.698311'
'end','0.000270'
'query end','0.000115'
'waiting for handler commit','0.012015'
'closing tables','0.000301'
'freeing items','0.000185'
'cleaning up','0.000108'

Here's the table structure:
Create Table: CREATE TABLE `mp_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `ts_update` datetime NOT NULL,
  `lastWakeUpPush` datetime DEFAULT NULL,
  `type` enum('parent','child','admin') NOT NULL,
  `latitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `longitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `accuracy` float(10,2) NOT NULL DEFAULT '0.00',
  `source` enum('gps','wifi','lbs') DEFAULT NULL,
  `batteryLevel` int(4) DEFAULT NULL,
  `steps` int(4) DEFAULT NULL,
  `turnovers` int(4) DEFAULT NULL,
  `setCoordsTime` datetime DEFAULT NULL,
  `phoneLogin` varchar(45) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `salt` varchar(15) DEFAULT '',
  `licenseTo` datetime NOT NULL,
  `monthForRepostUsed` tinyint(1) NOT NULL DEFAULT '0',
  `monthForQuizUsed` tinyint(1) NOT NULL DEFAULT '0',
  `isTrial` tinyint(1) NOT NULL DEFAULT '1',
  `deviceUid` varchar(10) DEFAULT NULL,
  `warnings`   set('backgroundDataOff','geoDeviceOff','geoAppOff','noSim','noMobileDataTransfer','pushesOff','batteryOptimization','mic','appStats','noGoogleServices','backgroundServicesOff','wifiOff','accessibilityOff') DEFAULT NULL,
  `locale` enum('en','en-US','ru','ar','zh','cs','nl','et','fi','fr','de','el','he','hu','id','it','ja','kk','ko','lv','lt','pl','pt','ro','esp','sv','tr','uk','vi','bg','sr','pt-BR') NOT NULL DEFAULT 'ru',
  `lastNcPush` datetime DEFAULT NULL,
  `priceGroup` int(11) DEFAULT NULL COMMENT 'Foreign key to mp_geoZonePriceGroup',
  `healthCheckTime` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneLogin` (`phoneLogin`),
  KEY `ts_update` (`ts_update`),
  KEY `setCoordsTime` (`setCoordsTime`,`type`,`lastWakeUpPush`),
  KEY `deviceUid` (`deviceUid`),
  KEY `batteryLevel` (`batteryLevel`),
  KEY `mp_user_mp_geoZonePriceGroup_id_fk` (`priceGroup`),
  KEY `lastLoginidx` (`lastLogin`)
) ENGINE=InnoDB AUTO_INCREMENT=9465064 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this way it can use up to all the amount of available memory.

Did the trick for me. Unfortunately, I wasn't the guy to figure it out, but rather guys from paid percona support desk. I was told this is likey to happen on huge tables.

I am having the same issue. I have a 2 slaves with similar setup one has a lag occasionally and the other one never has the lag.
innodb_flush_log_at_trx_commit = 1 on both the servers
I was not very convinced 'range_optimizer_max_mem_size' will resolve my issue but to my surprise this parameter was actually '0' on the slave that never has lag. Will be changing the parameter to 0 and see if there's any actual difference.


Multiple SELECT INTO I have a ~300 million row table with a column that has 9 different values. I need to be able to SELECT INTO 9 different new tables based on this column.

At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a CASE statement that defines the table to go into, or something similar?

I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO CASE
        WHEN col1=N'tbl1' THEN [dbo].[tbl1]
        WHEN col1=N'tbl2' THEN [dbo].[tbl2]
    END
FROM [dbo][tblBase]

There is no clustered index on the base table. There is an index on col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.

The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).

SELECT INTO <table-decided-by-code>

You can't dynamically set the destination table like that.
If the target tables are part of a partition set partitioned on that column then SQL Server would do this for you, but you are using 2012 standard edition so that won't be possible (partitioned tables were an enterprise only feature until 2016sp1).

  Forcing it increases the cost dramatically as it does an RID Lookup.

Have you verified that this produces a slower result? Sometimes the query cost derived for plans by the planner doesn't not match actual performance.

If you are only copying a few columns from the target table, perhaps you could include those in the index with something like CREATE INDEX [ix_tblBase_col1_plus] ON ([col1]) INCLUDE ([col2] ,[col3]). This will increase the size of the index but remove the need for the extra page lookups by RID, but as you say you are taking most of the rows this is unlikely to be practical.

  There is no clustered index on the base table.

Adding any old clustered index would not help, you'll just replace the extra lookups by RID with extra lookups by clustering key.

You could try defining [col1] as your clustering key. When clustering on a non-unique key SQL Server adds a 4-byte "uniquifier" column which is appended to the indexed values. If your column is 4 bytes long or less this will result in a clustering key no larger than RID values. If the column contains wider data then space efficiency may be an issue as that extra data will be added to every row in every non-clustered index. You would need to experiment to see what difference this makes to the size of the table+indexes and any impact on other parts of your application (we could offer educated guesses if you provide a full table definition, and if your "col1" is variable length some information on the pattern of its values, but trying it in a dev copy of your DB would be more definitive).

it not use INDEX because no any WHERE condition in SELECT statement, so from SQL server point of view - it always make FULL-SCAN of table, so no reason for use index.

possible make changes

declare cursor as (it is pseudo code)
SELECT DISTINCT col1 as tbl_name FROM [dbo][tblBase]
fetch col1 INTO @tbl_name
and after LOOP for each from LIST

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO @tbl_name
FROM [dbo][tblBase] WHERE col1 = @tbl_name

but need test - it could reduce loading of server memory, but may not give benefits in total speed


How to increase Max_used_connections? I have a registration application. 10000 people want to access the server but only 3000 quotas available. 3000 users registered successfully online within 3 minutes on the registration day. However, some users encounter 504 error page (Gateway time out). I checked the max_used_connections variable, the value is 10 only. The max_connections value is 151.
The max_used_connections is defined as

the maximum number of connections that have been in use simultaneously since the server started.

I wonder why the value is only 10. Is it too small compared with 3000 users registered successfully online within 3 minutes?

Change the configuration file to increase max_connections.  But be aware that that can increase RAM usage.  How much RAM do you have?  What is the value of innodb_buffer_pool_size?
You must streamline the "registration process" as much as possible.  Please provide all the SQL that happens for registration so we can help optimize it.
Max_used_connections is a "high water mark".  You never had more than 10 connections at the same time, at least not since the last restart.  10 is fine.  And it cannot be changed.  Since it is much smaller than Max_connections (151), there does not seem to be an issue.  10 connections at a time is fine.
Look at the STATUS Connections.  That is a "counter" that has been running since restart.  It will probably say more than 3000.  3000 in 3 minutes is only 17/second, not difficult if the registration process is simple.
The timeout probably indicates something else -- such as too much data flowing across the network.  Does registration involve showing some biggish images on the registration page?
Instrument your registration process to see how many milliseconds the whole process takes, and how long each SQL statement takes.
What was the value of max_user_connections?

Have you tried below commands on MySQL:
SET GLOBAL max_connections = 10000;
SET GLOBAL max_user_connections=10000;


How to separate name string by the spaces in the name in T-SQL I have a list of names, such as: Doe John James, Doe Jane and Doe Henry Albert Sr

I need to split those out into the four component parts of the name (if the name has four parts) to put each of them in their own column (last name, first name, middle name, suffix), while allowing for names that may or may not have a middle name or suffix.

I've got it figured out for everything but the third instance, where there is a suffix.

For first name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then LEFT(SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 1, LEN(LTRIM(RTRIM(@name)))), CHARINDEX(' ', SUBSTRING(LTRIM(RTRIM(@name)), CHARINDEX(' ', LTRIM(RTRIM(@name))) + 2, LEN(LTRIM(RTRIM(@name))))))
    else
        SUBSTRING(@name, CHARINDEX(' ', @name) + 1, LEN(@name))
end as firstName

It's pretty crazy, but it works...

For middle name I have:

case
    when LEN(LTRIM(rtrim(@name))) - LEN(replace(ltrim(rtrim(@name)),' ','')) = 2
        then
            REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(@name))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(@name))))))
    else ''
end as middleName

Not quite as complex as the first name.

Since the last name is at the beginning that's the easiest:

SUBSTRING(LTRIM(RTRIM(@name)),1,CHARINDEX(' ',LTRIM(RTRIM(@name)))-1) as lastName

But I can't figure out how to allow for names with a suffix (Jr, Sr, III, etc).

Does something like this help you:

With split(id, n, start, pos) as(
    SELECT id, 1, 0, CHARINDEX(' ', fullname) FROM @names
    UNION ALL
    SELECT n.id, n+1, pos+1, CHARINDEX(' ', fullname, pos+1)
    FROM @names n
    INNER JOIN split s ON n.id = s.id
    WHERE CHARINDEX(' ', fullname, start+1) > 0
)
SELECT id
    , lastName = MAX(CASE WHEN v.n = 1 THEN v.val END)
    , firstname = MAX(CASE WHEN v.n = 2 THEN v.val END)
    , middleName = MAX(CASE WHEN v.n = 3 THEN v.val END)
    , title = MAX(CASE WHEN v.n = 4 THEN v.val END)
FROM (
    SELECT n.id, s.n
        , SUBSTRING(n.fullname
                , s.start
                , CASE WHEN s.pos = 0 THEN LEN(n.fullname)+1 ELSE s.pos END -s.start
            )
    FROM @names n
    INNER JOIN split s ON n.id = s.id
) as v(id, n, val)
GROUP BY id
--OPTION(MAXRECURSION  4)
;

The recursive CTE get the start and end position of each name along with a value n from 1 to up to 4.
Based on the value of n the GROUP BY put them in their respective columns.

On a big table, it may not be very efficient though.

Output:

id  lastName    firstname   middleName  title
0   Doe         John        James       NULL
1   Doe         Jane        NULL        NULL
2   Doe         Henry       Albert      Sr
3   Bart        Simpson     NULL        NULL

Data:

declare @names table(id int identity(0, 1), fullname varchar(200), first varchar(50), middle varchar(50), last varchar(50), title varchar(50));
INSERT INTO @names(fullname) values
    ('Doe John James')
    , ('Doe Jane')
    , ('Doe Henry Albert Sr')
    , ('Bart Simpson')
;

You may like to first split the words and then apply your logic to separate the names. You can visit the following link for a custom split function:

T SQL Table Valued Function to Split a Column on commas


Get sum of amount monthly I do not know English, sorry :(

example table:

CREATE TABLE IF NOT EXISTS `__example__` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(10,2) DEFAULT NULL,
`fromDate` datetime DEFAULT NULL,
`toDate` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and rows:

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (1, 1000.00, '2016-01-16 17:05:40', '2016-01-26 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (2, 1000.00, '2016-01-26 17:05:40', '2016-02-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (3, 1000.00, '2015-12-26 17:05:40', '2016-01-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (4, 4000.00, '2015-12-26 17:05:40', '2016-02-04 17:05:45');

I want to see:

year | month | sum
2015 |    12 | 1400.00
2016 |    01 | 4700.00
2016 |    02 | 900.00

Example:

2 rows started at 12/2015 and the sum thereof is 5000.00(1000+4000).

But 12/2015 sum column should be 1400.00.

How can I do that with SQL query? Help me, please.

This query should accomplish your goal.

Select YEAR(fromDate) [Year], MONTH(fromDate) [Month], SUM(amount) [Amount]
FROM '__example__'
GROUP BY YEAR(fromDate), MONTH(fromDate)
ORDER BY Year, Month

I've still never used mysql.  But Looking at your edited requirements, I THINK you're asking for something like this:

Take the total amount and divide it by the number of days between
todate and fromdate.
Find the number of days in the month from the "fromdate" to the end
of the month.
Multiply the average amount (step 1) by the number of days in each
month to get the value in each month.

If that's what you're asking for then the solution is pretty easy.

You'll first need a row for each month in the span.  I'd suggest creating a "DATES" table and joining Examples to the date table.  Create a row in the table for every month between 2015 and 2020 (like '01/01/2015', '02/01/2015', '03/01/2015', etc.).

After this, and using the mysql LAST_DAY() and DATE_ADD functions, you should be able to calculate the average amount per day (=amount/total days).


Transactional replication using articles filters and delete operations I have a problem with my transactional replication when I use articles, filters, and delete operations. When I insert the new data into the publisher all the data that meets the specified filter is inserted to the subscriber - everything works fine. When I delete some data from the publisher, not all data that meets the specified filter is deleted from the subscriber and I don't know why.

I have three servers:

A – publisher (Microsoft SQL Server 2012)
B – distributor (Microsoft SQL Server 2012),
C – subscriber (Microsoft SQL Server 2012).

There are six tables in my publisher database. Each table has a primary key and some of them have foreign key as shown below:

I want to replicate only data for the first manufacturer (manufacturer ID=1) from the publisher to the subscriber, so I use these filters:

SELECT <published_columns> FROM [dbo].[Manufacturer]
WHERE [dbo].[Manufacturer].ID IN (Select M.ID from [dbo].[Manufacturer] AS M where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Catalog]
WHERE [dbo].[Catalog].ID IN (Select C.ID from [dbo].[Catalog] AS C
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Cars]
WHERE ID IN (Select CA.ID from [dbo].[Cars] AS CA
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[CarParts]
WHERE ID IN (Select CP.ID from [dbo].[CarParts] AS CP
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Parts]
WHERE ID IN (Select P.ID from [dbo].[Parts] AS P
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[PartDetails]
WHERE ID IN (Select PD.ID from [dbo].[PartDetails] AS PD
INNER JOIN [dbo].[Parts] AS P ON P.ID = PD.PartID
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

When I insert the new data into the publisher's CarParts, Parts, or PartDetails tables, all the data that meets the specified filter (manufacturer ID=1) is inserted at the subscriber.

When I delete data from the publisher CarParts, Parts, or PartDetails tables, not all data that meets the specified filter (manufacturer ID=1) is deleted from the subscriber - it only deletes data from the CarParts table, not from Parts or PartDetails.

The same filter is used for insert and delete operations. What could be the problem? Maybe it is a Microsoft SQL Server 2012 bug?

The problem was with delete operations sequence.

Problem solved.


Oracle imp crashes without error I have Oracle 10g XE database locally. I export schema with something like

exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp

Exp is 10.2.0.1.0 version - so is the DB.

Than I take this dump to another machine and try to import it to another remote machine. With

imp %USER%/%PASSWD%/'192.168.0.100/%SID%' ignore=y file=dump.dmp full=y

Oracle client with imp is version 11.2.0.1.0 and Oracle DB (normal, not express) is version 11.2.0.2.0

The problem is that after printing several messages about importing tables it suddenly stops:

. . importing table                      "TABLE_1"      17180 rows imported
. . importing table                      "TABLE_2"          0 rows imported
. . importing table                      "TABLE_3"         49 rows imported$:

and does not even give a newline symbol in the end.

The problem persists with rows=N and with only selected tables from this schema.

So can anybody tell:

Where can be some error logs to know what is wrong?
Some workaround (still using exp/imp utilities preferably) - we need to import dumps regularly (it is for continuous integration server), we need to do it from remote host (so impdp is hardly an option with need to mount so remote volumes), dependency on other DB is highly undesirable (dblink).

UPD: Unfortunately for all curious we have solved the problem switching continuous integration system to Oracle XE, where is no such problem. Sysadm has already switched off this server, so I cannot try anything. Sorry.

Maybe an error appears in the target database's alert log?  Run this:

SQL> show parameter background_dump_dest

...and go to the directory given to check the alert_<SID>.log.  Or do a trace of the imp session in the target database instance.  (Usually the errors appear in the exp / imp output themselves (saved to a log with LOG=log_file.log ) but I assume the export ran file with no warnings and completes with):

"Export terminated successfully without warnings."

When you are copying the file, are you sure it's not getting corrupted somehow? (md5sum remains the same)
Did you try importing into an empty schema so you don't have to set IGNORE=Y?
And when you wrote:

  The problem persists with rows=N and with only selected tables from this schema.

Did you mean no matter which one or two tables you choose, it still crashes?
Try simulating an import with SHOW=Y?

Try copying imp.exe from the machine with XE to the machine with SE.

(I had this issue on my virtual test machine with Oracle Standard One 11.2.0.1.0. Imp.exe stops without messages, but in another installation with oracle 11 XE, imp.exe works fine with the same dump file. So I copied the imp.exe file of Oracle XE to the machine with Oracle Standard (replaced imp.exe), and now it's working. Maybe there's a bug in imp.exe of standard one for win32 installation...)