How to solve MySQL “The table is full” error 1114 with Amazon RDS? I am trying to add a new column in a table and the query giving me this error 1114.

My RDS instance size is small. 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU) Table i am trying to modify is having 5 Million rows and 7GB in size. Disk size of instance is 15GB (40% free).

Its critical time for us as we are stuck, AWS RDS not allowing access to all parameters of mysql configurations. How do i get rid of this problem?

Here is the Problem: Whenever you run ALTER TABLE, there must be a temp table used. The temp table is not being written to your data folder. It is most likely written to /rdsdbdata/tmp. Please this query

SHOW VARIABLES LIKE 'tmpdir';

This will give you an idea where that folder is. Again, by default, it is /rdsdbdata/tmp.

Why would that halt things for mysqld? Blame it on MyISAM. Why MyISAM? Here is something to consider: According to MySQL 5.0 Certification Study Guide,

bulletpoint #11 says the following on Pages 408,409 Section 29.2:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

When you run out of disk space, do not just shutdown or kill mysql. The count of the open file handles in any currently used MyISAM will not have been cleared. Thus, the MyISAM table is marked crashed. If you can free up disk space in the data volume with mysqld still running, mysqld will soldier on once disk space is made available. Please note that mysqld itself did not crash. It simply enters a state of suspended animation until free space materializes.

In your case, just kill the ALTER TABLE. The temp table, which uses MyISAM, simply locked up the disk space of /rdsdbdata/tmp. Killing the ALTER TABLE will get rid of the temp table and free up mysqld to continue with life as usual.

I have explained this in my past posts:

Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?
Apr 25, 2012 : Very Large Log Files

WHAT CAN YOU DO?

You may have to perform the ALTER TABLE yourself in manual stages.

For example, let's suppose you have this table:

CREATE TABLE ruchit_table
(
    id int not null auto_increment,
    field1 INT NOT NULL,
    field2 VARCHAR(20),
    primary key (id)
);

and you want to run ALTER TABLE ADD COLUMN field3 CHAR(5);

Here are the steps to perform this inside your datadir:

CREATE TABLE ruchit_table_new LIKE ruchit_table;
ALTER TABLE ruchit_table_new ADD COLUMN field3 CHAR(5);
INSERT INTO ruchit_table_new SELECT id,field1,field2 FROM ruchit_table;
ALTER TABLE ruchit_table RENAME ruchit_table_old;
ALTER TABLE ruchit_table_new RENAME ruchit_table;
DROP TABLE ruchit_table_old;

Note that

The temp table's location is in the datadir
Performed the ALTER TABLE

Added the new column to an empty temp table
Loaded the temp table
Got rid of the old table

These are things mysqld does internally with ALTER TABLE. These steps simply emulate this and does this emulation in the datadir.

Give it a Try !!!


How to get the last non-null value that came before the current row Say I have the following table:
create table tab (ts integer, a integer);

insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3);

When i query the data there may be some nulls:
select * from tab;

+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | (null) |
|  4 | (null) |
|  5 | 3      |
+----+--------+

Now I like to replace the null values with the first non-null value that comes before it. Like so:
+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | 2      |
|  4 | 2      |
|  5 | 3      |
+----+--------+

I've tried using several variations of the FIRST_VALUE and LAST_VALUE window functions but they always only return first value overall, the last value overall or the current value.
SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a IS NULL THEN NULL ELSE ts END NULLS LAST
  )
FROM
  tab
ORDER BY
  ts ASC;

-- OR

SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a is NULL then 0 else 1 END DESC, ts
  )
FROM
  tab
ORDER BY
  ts ASC;

+----+--------+-------------+
| ts |   a    | first_value |
+----+--------+-------------+
|  1 | 1      |           1 |
|  2 | 2      |           1 |
|  3 | (null) |           1 |
|  4 | (null) |           1 |
|  5 | 3      |           1 |
+----+--------+-------------+

You can use a subquery:
select
  t1.ts,
  case when a is null then
    (select a
     from tab t2
     where t2.ts < t1.ts and a is not null
     order by a desc
     limit 1)
  else a end as a
from
  tab t1;

ts |  a
-: | -:
 1 |  1
 2 |  2
 3 |  2
 4 |  2
 5 |  3

db<>fiddle here


How to undo every change made to a database in a controlled environment? I'm doing system testing of a product, and several tests are severely changing the data of a demo database.

The data is quite complex to create queries which will undo the changes done by the tests; moreover, those tests are subject to change.

Using a backup/restore is not an option, since restoring the database takes 30 seconds, which is too long in a context of automated testing.

Using insert into select is complex too: again, tests are subject to change, and can affect tables they don't affect now; copying the whole database this way is not an option neither, for performance reasons.

Microsoft SQL Server has snapshots feature, but given that I don't have Enterprise version on localhost, I can't use it. I can't use transactions neither, since the system tests involve two applications with a complex interaction between them. Of course, they don't share the same connection.

What can be done to quickly undo the last changes of a database?

To make the problem easier,

The database is in a controlled environment:

It is on localhost, so I am the only one who can access it,
The only changes are from system tests. All the changes can (and are expected to) be lost.
Any recovery model can be chosen.

The preparation (for example some sort of backup) can take long time or even be a manual task, since it would be performed very rarely. It's only the speed of undoing the changes from this point which is important, since it's done after every test which affects the database.

If this is just for development/testing, you can get a license for Developer Edition (free for 2014+ and ~$50 for 2012 and lower).  and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production.

You're not going to be able to cook something up that's much simpler or faster than reverting from a database snapshot. But do some research - you want to be aware of things like this.


Identify table usage in sql server before we stop the load I tried browsing this across lot of links but cant get much of help so i am posting this question which sound bit similar.
We have quite some tables which are being planned from our app team that they will stop sending data in next couple of months. So these tables would be good contender for clean up later some time.
However how can i find , if there is way within SQL server, some additional user which we are not aware might be using that table for XYZ operation.
Is there a way natively available or will i need XE to track this? This is an OLTP database server highly active throughout, so running any audit would be something to test.
What should be the approach and how to practically deal with this situation? Please suggest.
Thank you!

I think your best approach would be to start with just tracking the counters in sys.dm_db_index_usage_stats.  Just store the results off to a table (and have that run on a schedule) for as long as you feel necessary.  Then look at the counters to see if there is significant movement on them.  There will always be a little, especially if you have maintenance going.
If you think they are done, then rename the table and see if anyone screams.  You can quickly rename it back if you need to.
Finally, if you want an extended events session to track, the below might work for you.  As you are aware, it has an impact, but it's not going to be as bad as you might think, especially for a table that you think is not being used.  Still, when you first enable it, test it to make sure it catches what you want, as well as that it's impact isn't hindering your users.  However, it's the only sure way I know to catch this type of thing.
The below is what I typically use when I want to catch usage of specific stored procedures or queries that contain certain text strings.  I send it to a ring buffer because I typically only run it until I've got what I'm looking for.  But you can alter the target to whatever you want.  It creates the XE turned off, so you can go back in and edit it before starting it up.
CREATE EVENT SESSION [SpecificSQLWatch] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%<my string here>%')))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


Creating a Stored Procedure that will input data for each day between the date entered as input to or from the 1st of that month i am trying to figure our sql syntax and my initial challenge was to call a stored procedure name GetPIValueAverageTime and write the float value to an table named actuals. and it created the row of data for that inparticular production date. in sql how can i iterate through two dates the production date entered to the 1st of that month and enter that date that the iteration is on into the exec of the stored procedure in order to populate date from the particular date ? this is just the first part i need to add all the float values i get from those dates populate and fill and month to date average but ill handle that later i just what to populate the table with data in an iteration from the prod date to the 1st of the month. this is what i have so far please excuse the mess. i tried to do this with a while loop but it inserts first row for the first day of the month then hangs on "executing query" and i have to force kill it.

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)

--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth

     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);
end

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END

I reduced your statement down to the simplest possible piece of code, and found the exact error.  I removed everything except the loop, and got to this:

CREATE PROCEDURE dbo.test
(
    @ProductionDate As DateTime
    , @Debug BIT = 1
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BegMonth As DateTime

    SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

    WHILE @productionDate >= @BegMonth
        SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
END

The WHILE statement does not have BEGIN directly afterwards.  This means the WHILE loop is essentially this:

WHILE @productionDate >= @BegMonth SELECT @endDate = DATEADD(DAY, 1, @BegMonth)

and nothing else.  Just, loop forever.

To fix it, make your code like this:

USE [OperationsView]
GO
/****** Object:  StoredProcedure [Interface].[proc_GetChemicalDaily] Script    Date: 1/30/2017 4:03:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Daily Chemical>
-- =============================================
ALTER PROCEDURE [Interface].[proc_GetChemicalDaily]
-- Add the parameters for the stored procedure here

@ProductionDate As DateTime,
@Debug BIT = 1

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- pi sproc variables Declaration
DECLARE @result As Float
DECLARE @tag_in As varchar(80)
DECLARE @pi_server As varchar(50)
DECLARE @enddate As DateTime
DECLARE @FCChemGALPERMIN float
DECLARE @CAUSTICDAILYUSAGE As Decimal(16, 8)
DECLARE @constant As Decimal(16, 8)
DECLARE @BegMonth As DateTime

--Actual variables for insertion
DECLARE @Group As VARCHAR(200)
DECLARE @Item As VARCHAR(200)
DECLARE @Total As DECIMAL(20, 4)
DECLARE @Days As Int
DECLARE @Type As VARCHAR(1)

--Pi Sproc variable setting
   SELECT @tag_in = 'I-FC-835'
   SELECT @pi_server = 'valpi'
   SET @constant = (18318.0/2000.0)*.5
   SET @BegMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

   --Setting Actual insertion variables
   SET @Group = 'CHEMICAL'
   SET @Item = 'CAUSTIC CHEM'
   SET @Days = 1
   SET @Type ='a'
   --create temp table to grab decimal value
   create table #temp
   (
    float_col   float,
    percent_col float
   )
--Grabbing gal/min value from PI
While @productionDate >= @BegMonth
BEGIN
     SELECT @endDate = DATEADD(DAY, 1, @BegMonth)
     INSERT into #temp(float_col, percent_col)
     EXEC Interface.proc_GetPIValueAverageTime @result, @tag_in, @BegMonth, @endDate, @pi_server, @Debug
     SELECT @FCChemGALPERMIN = float_col FROM #temp

  -- daily dry ton usage calculation
  IF @FCChemGALPERMIN > .5
 SET @CAUSTICDAILYUSAGE = cast(@FCChemGALPERMIN*@constant as numeric(38,10))
  ELSE
 SET @CAUSTICDAILYUSAGE = 0.0000

--Inserting in Actuals table
IF @Debug = 1

begin
    INSERT INTO [OperationsView].[dbo].[Actuals]
            ([Group]
            ,[Item]
            ,[ProductionDate]
            ,[Days]
            ,[Total]
            ,[Type])
        VALUES
            (@Group
            ,@Item
            ,@ProductionDate
            ,@Days
            ,@CAUSTICDAILYUSAGE
            ,@Type);
end

DROP TABLE #temp
Set @BegMonth = DATEADD(DAY, 1, @BegMonth)
 END
END


Innodb primary key advice We have a database table growing at a rate of ~20 million records per month, we're expecting this to double shortly.
The existing schema has a uuid4 as a primary key, and then two additional indexes on two other uuid4 columns. These columns are searched frequently for diagnosing issues. We can't change the UUID4 to something else as they are populated by an application we can't change.
We're planning on creating a new table and then back populating the data required from the old table into this one, fortunately, there's no real-time requirement for historic data.
The basic structure of the table is as follows

Column
Type

Started
Date Time ( will always be increasing )

app_id_one
uuid version 4

app_id_two
uuid version 4

app_id_three
uuid version 4

My questions are as follows:

As we don't control the uuid generation process does (started, app_id_one ) make sense as a primary key for the above structure, are there similar performance issues using uuid version 4 as the second column in a composite key?
We regularly have to search on app_id_two/app_id_three but I assume even have a secondary index of type uuid4 is bad for performance, so I am thinking we should also index these (started, app_id_two), (started, app_id_three )?
If we do option 2 this leads to a duplicate of the date time in the secondary index ( due to the chosen primary key ), space isn't too much of an issue we've got lots available on the servers, but would it be better just to generate a more suitable primary key instead?
We're considering populating this new table via a trigger as changing the application that populates the existing table is also not without its challenges. I'm worried about this adding to our already struggling write performance, but if I remove the two indexes from the existing table ( app_id_two / app_id_three ) I assume we'll get more than enough performance back from doing so?

Thank you!

Simply put, as the table (or index) grows, the performance approaches a minimum of one disk hit per lookup.
This is because UUIDs (other than a version 1 UUID with the bits suitably shuffled) are effectively "random".  That is whatever is in the buffer_pool cache is approaches being useless for the next lookup.
You seem to have 3 such indexes; this means that even inserting one row is threatening to require 3 disk hits!  20M new rows per month = 8 rows per second = upwards of 24 disk hits per second just for the insert traffic.  You could tackle this with a huge amount of RAM or by avoiding the 3 UUIDs.
Similarly, any SELECT that uses an INDEX starting with one of those uuids will incur 1 disk hit.
Keep in mind (for InnoDB) that the PRIMARY KEY is a big BTree with all the data, sorted by the PK.  And a secondary index is a smaller BTree with the key column(s) plus the PK's column(s).  So, some things stated about the PK and the seocndary key are interchangeable.
Of note,...  When looking up a row by a secondary key, two BTree lookups are performed, one per BTree involved.  If both are UUIDs that could mean 2 disk hits.  If you have started, and used composite indexes starting with started, and most queries reached for data around the same time, then you get "locality of reference benefits.  That is, everything might be cached and you won't need disk hits.
All of this leads to the beauty of using UUID, version 1, and rearrangin the bits so that the "time" part is in the high order bits.
(For your Q2 and Q3)  Assuming you now have
PRIMARY KEY(started),
INDEX(app_id_one),
INDEX(app_id_two),
INDEX(app_id_thre),

And assuming you are using InnoDB, then you already have started implicitly tacked onto the end of the 3 secondary indexes.  If, instead, you had
PRIMARY KEY(started),
INDEX(app_id_one),
INDEX(started, app_id_two),
INDEX(started, app_id_thre),

then the indexes would take exactly the same amount of space.  (OK building a BTreee leads to some unpredictables variations in "fragmentation", etc.)
Using a DATETIME as the PK is risky unless you are very sure you won't have any duplicates.  Changing the PK to INT or BIGINT (presumably AUTO_INCREMENT) would not change the principles here; just a slight difference in space needed.
pt-online-schema-change or gh-ost or MySQL 8.0 can change the existing table's indexes with minimal interruption.
I don't understand how you can change from the current WHERE app_id_two = ... to WHERE started = ... AND app_id_two = ....  If you currently have started available, then just use it and get rid of the other columns??


MySQL - Service Name in Windows What is the MySQL service name?

Get-Service | where{$_.name  -like "*MySQL*"}

Is that correct?

You can utilize Get-Service and the -Name property to find the exact server name. It has the same effect as doing the search with Where-Object but is less typing.

Get-Service -Name MySql

or just

Get-Service MySql

If you want to start or stop the service you can pipe that to the appropriate command

Get-Service MySql | Stop-Service


Loop Block with execution doesn't persist I need to iterate over a result set and update records from the data.  I am using an anonymous block but the information does not persist.

Like this...

do $$
declare dados Record;
begin
   for dados in
        select
           vd."Id"
        from "Vendas" vd
           inner join "ItensVendas" iv on iv."VendaId" = vd."Id"
        where vd."Especie" = 'NFE' and coalesce(vd."ClienteId", 0) = 0 and iv."CodCFOP" in (5949, 5927)
        group by vd."ChaveNFE", vd."Id", vd."DataCadastro"
   loop
        execute format('update "Vendas" set "EhConsumoProprio" = true where "Id" = %L', dados."Id");
   end loop;
end $$;

And again...

do $$
declare dados Record;
declare cmd varchar(300);
begin
    for dados in
        select
            vd."ChaveNFE",
            vd."Id",
            vd."DataCadastro"
        from
            "Vendas" vd
        inner join "ItensVendas" iv on
            iv."VendaId" = vd."Id"
        where
            vd."Especie" = 'NFE'
            and coalesce(vd."ClienteId",0)= 0
            and iv."CodCFOP" in (5949, 5927)
        group by vd."ChaveNFE",
                 vd."Id",
                 vd."DataCadastro"
    loop
        raise notice '%',dados."Id";
        update "Vendas" set "EhConsumoProprio"=true where "Id"=dados."Id"; -- It don't persists
        cmd := format('update "Vendas" set "EhConsumoProprio"=true where "Id"=%L', dados."Id");
        raise notice '%', cmd;
            execute cmd; -- It don't persists
    end loop;
end $$;

IMHO just a single UPDATE can solve your issue. Something like this:

UPDATE "Vendas"
   SET "EhConsumoProprio" = TRUE
  FROM "ItensVendas"
 WHERE "ItensVendas"."VendaId" = "Vendas"."Id"
   AND "Vendas"."Especie" = 'NFE'
   AND "Vendas"."ClienteId" IS NOT DISTINCT FROM 0
   AND "ItensVendas"."CodCFOP" IN (5949, 5927);


Loop through all databases and remove all users We are doing a conversion from one system to another which is requiring backup/restores.  I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.  I have it working for a single database, but when I try and loop through all databases and run my process I have issues.  Does anyone have suggestions on how I can enhance my script to get the results I am looking for.

DECLARE @SqlCommand VARCHAR(4000)
DECLARE @MaxRowNum INT
DECLARE @MinRowNum INT
DECLARE @DatabaseCount INT
DECLARE @MaxDatabaseCount INT
DECLARE @DatabaseName VARCHAR(50)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #DatabaseList FROM sys.databases WHERE database_id > 4 AND [name] NOT IN ('DBAdmin', 'FS_TEST', 'Garry') ORDER BY [name]
SELECT @DatabaseCount = (SELECT MIN(RowNum) FROM #DatabaseList), @MaxDatabaseCount = (SELECT MAX(RowNum) FROM #DatabaseList), @DatabaseName = DB_NAME()

WHILE @DatabaseCount <= @MaxDatabaseCount
BEGIN
SELECT @SqlCommand = 'USE [' + name + ']' + CHAR(13) + 'GO'FROM #DatabaseList WHERE RowNum = @DatabaseCount

EXEC (@SqlCommand)

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY Name), [name] INTO #UserList FROM sys.database_principals where (type_desc like '%use%' OR type_desc LIKE '%group%') and name not in('dbo','guest','INFORMATION_SCHEMA','sys','public')
SELECT @MinRowNum = (SELECT MIN(RowNum) FROM #UserList), @MaxRowNum = (SELECT MAX(RowNum) FROM #UserList), @DatabaseName = DB_NAME()

WHILE @MinRowNum <= @MaxRowNum
BEGIN
    SELECT @SqlCommand = 'DROP USER [' + name + ']' FROM #UserList WHERE RowNum = @MinRowNum

    print (@SqlCommand)
    PRINT (@SqlCommand + ' --- User Dropped from ' + @DatabaseName)

    SET @MinRowNum = @MinRowNum + 1
END

SET @DatabaseCount = @DatabaseCount + 1
DROP TABLE #UserList
END
DROP TABLE #DatabaseList

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server.

There is no need to delete all the users.

What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server.

Once you move your database/s to the new server, you even dont have to sync up the users as the logins that you created from above script will be created with the SID from the old server.

As a side note, below script will help you fix the orphaned users:

WITH cte AS
(
SELECT dp.name AS DBUser,
       dp.sid AS DBSid
  FROM sys.database_principals dp
       LEFT OUTER JOIN sys.server_principals sp
         ON dp.sid = sp.sid
 WHERE sp.sid IS NULL
   AND dp.type = 'S'  -- SQL_USER
   AND dp.principal_id > 4
)
-- join to logins with the same name to get the sql statement to un-orphan the user
SELECT cte.*,
       sp.name,
       -- generate the SQL script to un-orphan the users that can be.
       LinkOrphanUserToLoginSQL = N'ALTER USER [' + cte.DBUser + N'] WITH LOGIN=' + sp.name + N';'
  FROM cte
       -- LEFT JOIN allows seeing all orphaned users
       LEFT JOIN sys.server_principals sp
         ON cte.DBUser = sp.name
        AND sp.type = 'S'
ORDER BY DBUser;


VIEW instead of FUNCTION for inserting data I have a separate schema called "api" where all my "front-end" functions reside. My API server only have access to this schema. This works great for getting information out of the database in a controlled way.

To insert data in a controlled way I can either create functions in the api schema. But functions with many parameters (i.e. all fields to be inserted) are cumbersome to work with because:

It's easy to mix up parameters in calls
I don't know of any syntax to call functions with named parameters

So, I'm thinking of creating "proxy-views" in my api schema instead. The views would show no data (i.e. WHERE false). The API server would do ordinary inserts into these views and a trigger would take care of the actual insert logic.

The code for inserting data with INSERT statements are more readable than calling functions with a list of unnamed parameters.

Have I missed anything?
Is this a bad idea?
Maybe there is someway to name the input parameters for functions?

Thanks!

Named parameters should actually be passed using => not = and not :=.

e.g.

select my_function(argument_one => 42, argument_five => 'foobar');

The => is defined by the SQL standard for named parameters, the := was used historically by Postgres, but its use is discouraged in newer versions in favor of the standard compliant =>

Using Named Notation in the manual.


What permissions does HAS_DBACCESS check? I assume that

SELECT HAS_DBACCESS('database name')

is equivalent to

SELECT HAS_PERMS_BY_NAME('database name', 'DATABASE', 'permission name')

for some permission (or set of permissions, the results of which need anding together).

Is this assumption correct? If so what are the permissions?

HAS_DBACCESS returns 1 if:

the current Login / User has the ability to access the DB (whether through an Instance-level permission, or by having a User in that DB that is mapped to the current Login)
the DB is not OFFLINE or SUSPECT
the DB is in

MULTI_USER mode, or
SINGLE_USER mode and the current Login / User is that single user
RESTRICTED_USER mode and the current Login / User is either a member of the dbcreator or sysadmin fixed server roles, or is a member of the db_owner fixed database role

As far as my testing (with a low-privileged Login / User) shows, this appears to be equivalent to the following:

SELECT HAS_PERMS_BY_NAME('{database_name}', 'DATABASE', 'ANY');

I tried other permissions besides ANY (i.e. AUTHENTICATE and CONNECT) and they did not always match HAS_DBACCESS.


Permissions needed for dbms_metadata.get_ddl in a function Oracle Enterprise 11.2.0.3

I'm receiving:

ORA-31603: object "string of type "string" not found in schema "string"

when executing a function that calls dbms_metadata.get_ddl.

I know that procedures/functions need have to have the permissions granted explicitly instead of through a role: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

I also know that the permissions needed to execute dbms_metadata.get_ddl is in the SELECT_CATALOG_ROLE role: https://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/

However, the SELECT_CATALOG_ROLE role contains over 2400 permissions.  So, what are the explicit permissions needed to execute dbms_metadata.get_ddl so that I can call it from a function?

Test Case:

create or replace function getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

create table utilities.mytable (mycol varchar2(1));

select dbms_metadata.get_ddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
ORA-31603: object "MYTABLE" of type TABLE not found in schema "UTILITIES"

EDIT

So far, I have explicitly granted the following to the user that is owns getddl():

select any table
select any dictionary

Based on Raj's comment, the following works:

create or replace function utilities.getddl (p_type varchar2, p_object varchar2, p_owner varchar2)
authid current_user
return varchar2 as
begin
  return dbms_metadata.get_ddl(p_type, p_object, p_owner);
end getddl;

select getddl('TABLE', 'MYTABLE', 'UTILITIES') from dual;
<DDL>


What is the default username and password to the oracle pre-built virtual machine? I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

My only problem is I cannot login to the database.

I tried:

[oracle@vbgeneric oracle]$ sqlplus oracle

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 5 17:32:22 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: oracle
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

my ORACLE_SID=orcl12c

How do I login to the database itself?

The default password for the HR, sys and system accounts is oracle.

The Unix password for the oracle user is also oracle.


Granular Security in SQL Server Consider a database with the following structure:

Tables

[dbo].[Customer], [dbo].[Address], and [dbo].[Report]

Schemas

[dbo] and [Reporting]

What I would like to be able to do is to have a principal (could be user or role) be able to manage views (CREATE, ALTER, DROP) in the Reporting schema only.  Further, this principal would need SELECT permission on all of the tables in the dbo schema (to support the creation of views in the Reporting schema).  The last thing I would like to be able to do is give INSERT, UPDATE, and DELETE permissions to the Report table only in the dbo schema.

Is this something that is possible?  I have yet to find the right combination of permissions.  I can use any combination of users, roles, etc. in order to accomplish this.

Using the GRANT Shema permissions on my made-up ReportUsers domain group, I can assign SELECT permissions as follows:

GRANT SELECT ON SCHEMA :: dbo TO [domain\ReportUsers];

For the Reporting schema, that can be tackled in a few ways. Do you want to give them full control over the schema? Is it just for creating views?


"Display width out of range for column" error when importing mysql 5.1 dump into 5.5 I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server.

I created a dump with

mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql

When I try to restore it on the new server, I get an error:

mysql -u root -p dbname < dbname.sql
ERROR 1439 (42000) at line nnnn: Display width out of range for column 'ID' (max = 255)

The col. is defined as ID bigint(1536).

Edit:  The table it is attempting to restore is actually a view.

This is likely to be a bug in the version of MySQL 5.1 that you are running, causing the server to provide incorrect metadata to mysqldump when it uses DESCRIBE to learn about the composition of views.

http://bugs.mysql.com/bug.php?id=65379

Background: any time you create a view that references a table, that table must already exist before you can create a view that references it.  For this reason, mysqldump creates all the tables, and then all the views.  That solves the "views depend on tables" issue... but when views reference other views, the referenced view also has to exist before a view that references it can be created.  To work around these semi-circular dependencies without having to actually resolve them, mysqldump generates a dummy placeholder table for each view, with the same columns the view has, first ... and then later drops each dummy table and replaces it with the actual view.

The bug causes mysqldump to generate invalid CREATE TABLE statements for these dummy placeholder tables.

You can edit the existing dumpfile manually by fixing the dummy table definition, or upgrade the old server and rerun the dump, or -- possibly -- use the version of mysqldump that ships with the latest GA version of MySQL, which (I'm working from memory, here) may also contain a workaround that avoids trying to generate bad statements when the server returns nonsense metadata.  The version of mysqldump for 5.6 is backwards-compatible with 5.1 servers, barring bugs or regressions.

If you don't have any views depending on these views, you should be able to simply remove the dummy table definitions from the dump files, which should then load without a problem.

Here's an example from a dumpfile, with some noise removed.

With the other tables, the dummy table is created, after dropping any table or view that already exists on the destination:

--
-- Temporary table structure for view `staff_list`
--

DROP TABLE IF EXISTS `staff_list`;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

...

/*!50001 CREATE TABLE `staff_list` (
 `ID` tinyint NOT NULL,

...

) ENGINE=MyISAM */;

Then, much further down in the dump file, the placeholder table is dropped and the real view is created:

--
-- Final view structure for view `staff_list`
--

/*!50001 DROP TABLE IF EXISTS `staff_list`*/;
/*!50001 DROP VIEW IF EXISTS `staff_list`*/;

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`mezzell`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `staff_list` AS select `s`.`staff_id` AS `ID` ...


How to add userAdmin user in Mongodb Atlas How to add a user with userAdmin role in Atlas?

The Cluster Security "Add New User" UI https://cloud.mongodb.com/v2/.....#clusters/security/users shows only limited set of built-in roles without any option to enter role name manually:

So I am a bit stuck here - there are no options in the UI, and non of the existing roles allow to add users in a shell.

UPDATE:

To make it crystal clear I went To "Security" tab in MongoDB Atlas cluster, from there I clicked "Add a new User", clicked "Show advanced options" and got the screenshot above.

UPDATE 2:

None of the roles in the list have userAdmin privilege.

The most "powerful" atlasAdmin covers only:

readWriteAnyDatabase
readAnyDatabase
enableSharding
dbAdminAnyDatabase
clusterMonitor

and doesn't have enough privilege to execute db.createUser() command.

You cannot grant this role. The only way to create new MongoDB users in Atlas is either via the UI or the API. See Add MongoDB Users for details and note that this page states:

  Atlas rolls back any user modifications not made through the UI or API. You must use the Atlas UI or API to add, modify, or delete MongoDB users on Atlas clusters.

See Create a Database User for details on how to create MongoDB users via the API.


Alter user - is password stored in audit trails I was asked an interesting question the other day and I have not been able to find a definitive answer.

If one of my DBA's changes a password, will the password the password show up in plain text in the audit trails?

Eg. sys as sysdba changes the password of system.

ALTER USER system IDENTIFIED BY <password>

Oracle states that it will not if the user changes their own password, but says nothing about another account changing the password of another account.
The suspicion is that the password would show up in clear text in trails such as v$SQL.

This seems like an egregious oversight if Oracle didn't account for this but I cannot find anything to prove otherwise.

You could just try it out.

This is logged in locally sqlplus / as sysdba:

SQL> alter user mat identified by COOLPASSWORD ;

User altered.

SQL> select substr(sql_text,0,40) from v$sqlarea where lower(sql_text) like '%alter%' ;

SUBSTR(SQL_TEXT,0,40)
----------------------------------------
select substr(sql_text,0,40) from v$sqla

SQL> select substr(sql_text,0,40) from v$sql where lower(sql_text) like '%alter%' ;

SUBSTR(SQL_TEXT,0,40)
----------------------------------------
select substr(sql_text,0,40) from v$sql
select substr(sql_text,0,40) from v$sqla

The alter commands are not present in v$sql or v$sqlarea. I have sys operations auditing on that test database, and the audit file for the above password change contains:

<AuditRecord><Audit_Type>4</Audit_Type>...<OSPrivilege>SYSDBA</OSPrivilege>...
<Sql_Text>alter user mat identified by * </Sql_Text>
</AuditRecord>

That's a literal * in the XML file. The normal audit logs contain the same * instead of the password, including if I change another user's password (logged in as a normal DBA user).

Now if you use things like rlwrap to run sqlplus, or fancy GUI clients, the password may well stay in some command line history/buffer. But the database server can't do much about that.


Multiple on conflict targets I have two unique indexes on columns a and b. I need something like this:

insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'

So generally I want to make different update based on conflict target - syntax above is not supported (only one on conflict statement is supported). Is there any other way to do this?

Your example suggests duplicate rows in the VALUES clause itself - which would result in:

  ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time

Can be solved by folding duplicates in the input. See:

How to include excluded rows in RETURNING from INSERT … ON CONFLICT

But a related problem affects your outlined query as a whole, and that's not as easy to overcome.

What to do if the same input row triggers multiple unique violations?
What to do if multiple input rows trigger the same unique violation?
What to do if multiple input rows trigger distinct unique violations of the same target row?
And combinations thereof.

Postgres developers probably didn't want to open this can of worms and restricted the UPSERT feature to a single constraint.

ON CONFLICT DO NOTHING - without conflict target - works for any applicable violation. The alternative action for this variant ("do nothing") is unambiguous.

You would have to be a lot more specific (also about concurrency and possible write load) to get a more specific answer.


Can expdp be piped directly into impdp, without intermediate files? We often need to copy fairly large databases from one server to another. The current method uses expdp on the source, and then impdp on the destination. This gets pretty horrible because:

It takes up a lot of space to store the intermediary files.
It takes a lot more time than necessary because the writing and the reading happen in sequence rather than in parallel.

Is there a way to arrange things so that the stdout of expdp are piped directly into stdin of impdp? For example:

expdp / parfile=export.par | ssh remote impdp parfile=import.par

What should I put into these hypothetical export.par and import.par respectively?

If not straight stdout into stdin, can expdp write to and impdp -- read from -- TCP-socket(s)? I could have those plumbed with netcat...

As @mutap stated in the comments, you can expdp/impdp over the network.

Technically, you will be doing the DataPump export and import over a database link without the need for an intermediate file.

Bash PIPE is not needed.

Example:
https://oracle-base.com/articles/10g/oracle-data-pump-10g#NetworkExportsImports


WITH cte AS (
SELECT *, DENSE_RANK() OVER (ORDER BY ColumnA) dr
FROM source
),
cte2 AS (
SELECT DISTINCT ColumnA, NULL ColumnB, NULL ColumnC, NULL ColumnD, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, ColumnB, NULL, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL , NULL, ColumnC, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, NULL, NULL, ColumnD, dr
FROM cte
)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM cte2
ORDER BY dr, ColumnA DESC, ColumnB DESC, ColumnC DESC, ColumnD DESC

fiddle

PS. Edit final ORDER BY clause (except dr first) as you need.


Effects of turning the transaction safety OFF I am considering to turn off the transaction safety to maximize the performance.

Before that i am interested in finding the current status of transaction safety. Is there any command that we can get the status ?

I want to know the real use of turning this off. I found that it could increase performance. please let me know if there are any other advantages

http://msdn.microsoft.com/en-IN/library/ms189061.aspx

and also I would like to know if there are any disadvantages with turning  off the transaction safety.

The main disadvantage of high-performance mode is going to be your possibilities of failover.  Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss.

If you had to do a forced failover to the mirror server, any transactions that weren't sent to the mirror server would virtually be lost.

The real use of using high-performance mode would be that the following two conditions are true for you:

You want to incur the least amount of database mirroring overhead on the principal
You don't care if there is data loss

You should read up on the following BOL reference on Asynchronous Database Mirroring.  It will answer all of your questions more in-depth.


How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS I've created a master - slave (replication) in Google Cloud SQL.
I've also created a master - slave (replication) in Amazon RDS.

I want to sync two master MysQL from Google Cloud SQL and Amazon RDS. What's the approach can be done?

Theoretically, just adding replication masters to the two servers should work over the MySQL protocol, and without needing any additional configuration.

You can review the procedures at the below links for details on each side of the process:

AWS - How can I replicate an Amazon RDS for MySQL instance by using binary logs to an on-premises environment?
GCP - Configuring external replicas
GCP - Replicating from an external server


How to efficiently copy millions of rows from one table to another in Postgresql? I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history one.

What I did was to run:

INSERT INTO history SELECT * FROM daily

And it did the trick for a while, but it started to get slower and slower as the number of records kept growing. Now I have around 2 million records that need to be copied from daily to history in single operation and it takes too long to complete.

Is there another, more efficient way of copying data from one table to another?

Dump the table in csv format

COPY table TO '/tmp/table.csv' DELIMITER ',';

use the COPY command which is far more efficient for large amounts of data.

COPY table FROM '/tmp/table.csv' DELIMITER ',';

Check postgres docs at http://www.postgresql.org/docs/current/static/sql-copy.html for more info


Select a single row from two distinct columns Possible Duplicate:
  How to get the MAX row

I need to fetch the "latest" row for certain conditions in my tkhist1 table. Tickets are bound to TicketNo and LocationID, and I need to select one row for DISTINCT values of both of those columns.

Multiple rows may share the same TicketNo and LocationID though, they are additionally defined by a UniqueID column. I need to select a single row for each combination of TicketNo and LocationID, SELECTING the row with the single highest UniqueID value. I do not need Unique ID in my SELECT statement.

The following query selects some of the information I need, but it does not only grab one row per UniqueID. If a row has different values for FreightPay (or any other column) it will be SELECTED as a new row.

SELECT DISTINCT
    [TicketNo],
    [LocationID],
    [FreightPay]
  FROM [Tkhist1]

I need a single row for each distinct pair of TicketNo and LocationID, selecting only information from the highest value (DESC) UniqueID

Just INNER JOIN on a subquery:

SELECT  t1.[TicketNo],
        t1.[LocationID],
        t1.[FreightPay]
FROM [Tkhist1] t1
INNER JOIN (
                SELECT TicketNo,
                       LocationID,
                       MAX(UniqueID) UniqueID
                FROM Tkhist1
                GROUP BY TicketNo, LocationID
             ) sub
    ON sub.UniqueID = T1.uniqueId


Mysql : Maximum number of connections on a per user basis - Possible? Is it possible to set a maximum number of open connections that a user (NOT all users combined, just one specific user) may have at a given time in mysql?

Eg:

If I have a user "user1234", and want to only allow him to have a max 10 connections open at any given time.

Thanks.

You can set this per-user on a per-hour basis.
Under the WITH option for the MySQL GRANT command yu gave the following:
with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

You can set

queries per hour
updates per hour
inserts per hour
connection per hour

This can give you finer per-hour granularity not only in terms of the number of connections made, but what can be executed.
The columns for these user grants are locate in mysql.user:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29 MySQL Community Server (GPL)

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

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

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

mysql> select column_name from information_schema.columns
    -> where table_schema='mysql'
    -> and table_name='user'
    -> and column_name like 'max%'
    -> order by ordinal_position;
+----------------------+
| column_name          |
+----------------------+
| max_questions        |
| max_updates          |
| max_connections      |
| max_user_connections |
+----------------------+
4 rows in set (0.01 sec)

mysql>

I wrote about these things before

May 24, 2011 : What are user connections - when are the created and destroyed?
Jun 16, 2011 : Would it be sensible to create a MySQL user for each user account of a web application?
Mar 22, 2012 : How can I limit MySQL connections?

Your Original Question
As shown above, you can set user per-hour user limits. However, I came to notice that you want just DB Connections open for any given user.
You may require some special customized authentication that counts how many connections a user has. In MySQL 5.1+ you could possibly script this:
SELECT COUNT(1) ConnectionCount,user
FROM information_schema.processlist
WHERE user <> 'system user'
GROUP BY user;

This would allow you to know the counts for any connected user.
You may want to look into connection pooling and persistent connections to regulate the number of users connected.


How can you tell when a query plan reuse is causing performance problems When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot.

This feels like a rather quick and dirty approach - is there a more conclusive, definite way of ascertaining whether clearing the plan cache is the best approach? Making a mistake with this decision could result in a performance issue.

Apologizes if this question is too vague :)

Thanks!

Clearing the plan cache is never a good idea.  You're starting with a cold cache then, and you'll see a performance hit as SQL Server will have to compile everything from that moment on until plans are then cached to be reused.

  He came to this conclusion after noticing performance improved after a reboot.

Oftentimes when I see DBAs reboot, restart the service, and/or clear the procedure cache that gives a really misleading sense of the "fix".  Sometimes, the poor performance can be caused by a poorly chosen execution plan.  By doing any one of the three ways I mentioned above to get an empty cache, you're just forcing SQL Server's hand to compile everything.  Sometimes (although not always) the first query in after that is a typical workload and appears to be better performance....for the typical workload.  This is all rooted in bad parameter sniffing.

Parameter sniffing is a good thing.  But, if the first query that comes into SQL Server uses a parameter set to "Costa Rica", then SQL Server will compile with that parameter value and make an optimal plan for that parameter value.  But if 99.9% of the workload coming in uses the parameter set to "United States", then 99.9% of the workload will be reusing the plan that "Costa Rica" generated.

Fast forward and clear the cache (don't clear the cache, please, I'm just using this as an example).  Now you have a cold cache, and the next query that comes in uses the parameter "United States", and voila!  SQL Server compiles with that parameter value and now 99.9% of the workload is using an optimal plan.

The point I'm trying to get at is DBCC FREEPROCCACHE is like using a bulldozer to shovel snow off of a walkway.  You really should be finding out where the poor performance is and what is causing it.  And then, if it is a particular query that is giving you issues, and you've narrowed it down to a poorly selected plan, there are many potential routes to get around bad parameter sniffing (OPTIMIZE FOR hint, recompile, and a few others).


determining user types upon authentication I've got 3 different types of users (entities) that will log into a website in developing for a client.

"FITTER", "CLIENT", "ADMIN"

the first method I came up with was this.

CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id              | id           | id
client          | title        | firstname
office          | firstname    | surname
address_ordinal | surname      | username
postcode        | username     | password
username        | password     |
password        |              |

a separate table for each user with the usernames and password inside.

This would require the user to select the  type of user they want to log in as via a drop down box.

I thought that was rubbish, I just want a form with a username and password box and the application will do all the logic.

so i came up with this:

AUTHENTICATE | CLIENT TABLE    | FITTER TABLE | ADMIN TABLE
id           | id              | id           | id
username     | client          | title        | firstname
password     | office          | firstname    | surname
type         | address_ordinal | surname      |
type_link    | postcode        |              |

Now there is one table with ALL the usernames and passwords.

The Authenticate.type record would either be a string of "FITTER", "ADMIN" or "CLIENT"

the Authenticate.type_link would be the ID of the table that is in authenticate.type!

So this record...

AUTHENTICATE
id | username | password     | type   | type_link
-------------------------------------------------
3  | john     | BF45DE4192DF | FITTER | 4

will authorize whoever's ID is 4 in the FITTER TABLE.

My question is...

 Is there a better way to do this?

There must be right?

because this method essentially uses authenticate.type and authenticate.type_link as a composite key.

For the entities and attributes you've described so far and assuming I've understood the domain, I can't see a need for anything more than the tables below. If you there are attributes specific to a Fitter or Admin, add distinct tables for those attributes.

CREATE TABLE UserType
(
    UserTypeId INT
    , UserType NVARCHAR(50)
    , CONSTRAINT PK_UserType PRIMARY KEY (UserTypeId)
)

CREATE TABLE Address
(
    AddressId INT
    , Line1 NVARCHAR(50)
    , Line2 NVARCHAR(50)
    , Line3 NVARCHAR(50)
    , Line4 NVARCHAR(50)
    , Line5 NVARCHAR(50)
    , Postcode NVARCHAR(20)
    , Country NVARCHAR(50)
    , CONSTRAINT PK_Address PRIMARY KEY (AddressId)
)

CREATE TABLE [User]
(
    UserId INT
    , UserTypeId INT
    , UserName NVARCHAR(50)
    , Password NVARCHAR(50)
    , Title NVARCHAR(50)
    , Forename NVARCHAR(50)
    , Surname NVARCHAR(50)
    , CONSTRAINT PK_User PRIMARY KEY (UserId)
    , CONSTRAINT FK_User_UserType FOREIGN KEY (UserTypeId) REFERENCES UserType(UserTypeId)
)

CREATE TABLE Client
(
    ClientId INT
    , Name NVARCHAR(50)
    , AddressId INT
    , CONSTRAINT PK_Client PRIMARY KEY (ClientId)
    , CONSTRAINT FK_Client_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId)
)

CREATE TABLE ClientUser
(
    ClientId INT
    , UserId INT
    , CONSTRAINT PK_ClientUser PRIMARY KEY (ClientId, UserId)
    , CONSTRAINT FK_ClientUser_Client FOREIGN KEY (ClientId) REFERENCES Client(ClientId)
    , CONSTRAINT FK_ClientUser_User FOREIGN KEY (UserId) REFERENCES [User](UserId)
)


SQL Server: How to track progress of CREATE INDEX command? SQL Server 2014, Std Ed

I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help.

I currently use the method below, which at least shows me movement (that the index creation is not blocked). But I have zero idea if I am %10 through the process or %99.

I tried the method described here:
https://dba.stackexchange.com/a/102545/6229
but it shows a clearly wrong est completion time (it basically shows 'now' for a 60+ minute process that I am 10 min into)

How can I get a clue?

SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, *
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id = 58

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it to me via this related DBA.StackExchange Answer: Progress of SELECT INTO statement :-).

Please note:

!! You will need to add SET STATISTICS PROFILE ON; or SET STATISTICS XML ON; in the query batch that is doing the CREATE INDEX (and placed before the CREATE INDEX statement, if that wasn't obvious), else no rows will show up in this DMV for that SPID / session_id !!
The IN operator is used to filter out the Index Insert row that, if included, will increase the TotalRows values, which will skew the calculations since that row never shows any rows processed.
The row count displayed here (i.e. TotalRows) is double the row count of the table due to the operation taking two steps, each one operating on all of the rows: first is a "Table Scan" or "Clustered Index Scan", and second is the "Sort". You will see "Table Scan" when creating a Clustered Index or creating a NonClustered Index on a Heap. You will see "Clustered Index Scan" when creating a NonClustered Index on a Clustered Index.
This query does not seem to work when creating Filtered Indexes. For some reason, Filtered Indexes a) do not have the "Sort" step, and b) the row_count field never increases from 0.
Not sure what I was testing before, but my tests now indicate that Filtered Indexes are captured by this query. Sweet. Though just beware that row counts might be off (I will see if I can fix that someday).
When creating a Clustered Index on a Heap that already has NonClustered Indexes on it, the NonClustered Indexes need to be rebuilt (to swap out the RID -- RowID -- for the Clustered Index Key(s)), and each NonClustered Index rebuild will be a separate operation and hence not reflected in the stats returned by this query during the creation of the Clustered Index.
This query has been tested against:

Creating:

NonClustered Indexes on a Heap
a Clustered Index (no NonClustered Indexes exist)
NonClustered Indexes on the Clustered Index/Table
a Clustered Index when NonClustered Indexes already exist
Unique NonClustered Indexes on the Clustered Index/Table

Rebuilding (table with Clustered Index and one NonClustered Index; tested on SQL Server 2014, 2016, 2017, and 2019) via:

ALTER TABLE [schema_name].[table_name] REBUILD; (only Clustered Index shows up when using this method)
ALTER INDEX ALL ON [schema_name].[table_name] REBUILD;
ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD;

DECLARE @SPID INT = 51;

;WITH agg AS
(
     SELECT SUM(qp.[row_count]) AS [RowsProcessed],
            SUM(qp.[estimate_row_count]) AS [TotalRows],
            MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
            MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
                    [physical_operator_name],
                    N'<Transition>')) AS [CurrentStep]
     FROM sys.dm_exec_query_profiles qp
     WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan',
                                           N'Index Scan',  N'Sort')
     AND   qp.[session_id] = @SPID
), comp AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   agg
)
SELECT [CurrentStep],
       [TotalRows],
       [RowsProcessed],
       [RowsLeft],
       CONVERT(DECIMAL(5, 2),
               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
       [ElapsedSeconds],
       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
       DATEADD(SECOND,
               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
               GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

Sample output:

                        Rows                 Percent   Elapsed  Estimated    Estimated
CurrentStep  TotalRows  Processed  RowsLeft  Complete  Seconds  SecondsLeft  CompletionTime
-----------  ---------  ---------  --------  --------  -------  -----------  --------------
Clustered    11248640   4786937    6461703   42.56     4.89400  6.606223     2016-05-23
Index Scan                                                                   14:32:40.547


Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far? In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning.
Beside local indexes, there are two global indexes on it.
Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.

Next night, the feature called Asynchronous Global Index Maintenance background job has started an
ALTER INDEX xxx COALESCE CLEANUP
command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions.

Unfortunately, we need to create another index on this table with different parameters, but it's not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job.
Since we need to deploy a new release, we need definitely the DDL on another index.
For me it looks strange, that alter index ... coalesce cleanup doesn't allow to create another index in parallel. Why should there be any relation?

Question: If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?

For obvious reasons (laziness) I used the example for creating the table and index from this post:

https://richardfoote.wordpress.com/2013/08/02/12c-asynchronous-global-index-maintenance-part-i-where-are-we-now/

create table muse (id number, code number, name varchar2(30)) partition by range (id) (partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue));
insert into muse with g as (select * from dual connect by level <= 1000) select rownum, mod(rownum,100000), 'DAVID BOWIE' from g,g,g where rownum <=  3000000;
commit;
create index muse_id_i on muse(id);
create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

Next drop a partition:

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

Then analyze the index:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         3000000     1000000

Orphaned entries are presented here as deleted entries.

Here comes my addition. Start the coalesce cleanup manually, then interrupt it in a few seconds (Ctrl-C), then analyze the index again:

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2723063      723063

As you can see, even with interrupting the command, some of the entries were cleaned up. Now do it again:

SQL> alter index muse_id_i coalesce cleanup;
^Calter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2532434      532434

Next create an index then continue:

SQL> create index muse_name_i on muse(name) local;

Index created.

SQL> alter index muse_id_i coalesce cleanup;
^C
alter index muse_id_i coalesce cleanup
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL> SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2151894      151894

Finally:

SQL> alter index muse_id_i coalesce cleanup;

Index altered.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
MUSE_ID_I                         2000000           0

SQL>

So no, progess was not lost.


Slow SQL Server query using a DB2 linked server in virtual environment I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:

Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 & ESXi 5)
SQL Server 2005 SP3
Linked Server created using IBM DB2 for iBMASQL OLE DB Provider

The query itself is a select statement with a where clause which selects transactions this month.

Alright, so the issue itself:

Virtual machine is set to 4 vCores, it takes roughly 35 seconds to complete
Virtual machine is set to 2 vCores, it takes roughly 20 seconds to complete
Virtual machine is set to 1 vCores, it takes roughly 5 seconds to complete

I've tried tinkering with:

Network card settings
MAXDOP 1 OPTION
Looking at the execution plan

I'm at a loss as to what to do from here on. We want to use 4 vCores as the server will be heavily used. However, the queries take a lot longer when compared to using only 1 vCore.

Is there any possible way to limit the amount of vCores the linked server uses?

Is there anything else that I should take a look at?

Thanks!

Thanks to Jon Seigel, it seems like the issue in part due to the CPU affinity setting in SQL.

We deployed this VM with SQL from an image which causes a known issue as listed here (scenario #3):

IBM - Slow performance in Controller due to inability to use available CPU cores in SQL server

Removing the automatic affinity option and setting the affinity mask option to use all the CPUs available resolved the issue.

Affinity Mask Option

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 15;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

We have updated our image to include this option. Thanks everyone for the help in this issue.

Please note, this option is deprecated in SQL Server 2012 and is not recommended for use. Additionally, it is related to processor thread handling in Windows 2000 and Windows 2003 operating systems.

Affinity Mask Option - SQL Server 2012


How can I keep a result set without keeping the transaction open? The following documentation describes how to see the refcursor returned from a function, here, like this:

    CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

This works for me. However, if I want to keep the results on my screen, I have to keep the transaction open. When I execute COMMIT, my result set is discarded. When I execute both FETCH and COMMIT at the same time, the first result set is discarded.

Is there a way to commit the transaction but keep the result set? The version of PgAdmin is 1.18.1.

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc:

  WITH HOLD specifies that the cursor can continue to be used after the
  transaction that created it successfully commits

On the other hand, a refcursor opened by a plpgsql function is closed at the end of the transaction. Quoting the plpgsql doc:

  All portals are implicitly closed at transaction end. Therefore a
  refcursor value is usable to reference an open cursor only until the
  end of the transaction.

To create a cursor in a plpgsql function that may be used outside of its "parent" transaction, it's just a matter of syntax. You want the SQL implementation of a cursor, not the plpgsql variant. For this, EXECUTE must be used.

As an example, here's the skeleton of a function similar to yours, but using SQL-level cursors that outlive the transaction:

CREATE FUNCTION dyncursor(name text) RETURNS VOID AS
$$
DECLARE
  query text;
BEGIN
  query='SELECT 1 as col1, 2 as col2'; -- sample query
  EXECUTE 'DECLARE ' || quote_ident(name) || ' CURSOR WITH HOLD FOR ' || query;
END
$$ language plpgsql;

Demo:

test=> begin;
test=> select dyncursor('foo');
 dyncursor
-----------

(1 row)
test=> commit;
test=> fetch all from foo;
 col1 | col2
------+------
    1 |    2
(1 row)
test=> close foo;
CLOSE CURSOR


How to retrieve the Capture Instance for a given table? Working with SQL Server 2012, I have a hierarchical table layout (TPT). All tables "inherit" from the same base table (dbo.DataObjects). All tables have history tracking via CDC enabled.

I am trying to write a generic function that returns the CDC history information for a give type including the relevant information from all base tables.

Supposing my type is User with the table dbo.Users which inherits directly from dbo.DataObjects, my query should look something like this:

DECLARE @Begin_LSN binary(10),@End_LSN binary(10)
SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Users')
SELECT @End_LSN = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_DataObjects(@Begin_LSN, @End_LSN,'ALL') AS a
JOIN cdc.fn_cdc_get_all_changes_dbo_Users(@Begin_LSN, @End_LSN,'ALL') AS b
ON a.__$start_lsn = b.__$start_lsn
WHERE ID = 10;

This will give me the complete history trail for the User with ID 10 including the columns from the base table.

Now I want to build this kind of query dynamically.

One of my problems is that the cdc functions to retrieve the history information include the name of the capture instance:

cdc.fn_cdc_get_all_changes_<capture_instance>

I also need the name of the capture instance in the second line to get the min lsn:

sys.fn_cdc_get_min_lsn('<capture_instance>')

The capture instance name either gets created automatically for me when I enable CDC for a table or is passed manually. If I don't want to keep track of the capture instance name myself, is there a way to retrieve the name of the capture instance for a given table?

I am aware that there can be more than one capture instance per table (max. 2 I think), but I likely always will only use one per table.

PS: I am also aware that the capture instance name is usually built in the way <schema>_<table>, but relying on this assumption seems a bit risky. The naming convention could change for future versions, or someone could supply a manual capture instance name / change the name.

Input (hopefully stored procedure parameters):

DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

Code:

DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1),
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT *
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql,
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;


Migration of SQL Server to Oracle Database In Sql server, my Procedure creates temporary tables where column names dynamically come from every user, then inserts data into temporary tables. I use the data further  based on the requirement. Finally, we drop the temporary tables.

There is no problem when we create  temporary tables with the same name and with different columns because in SQL Server the temporary tables are Session Based.

Now, I want to migrate the logic of the SQL Server code to Oracle. Is their any solution to  achieve this requirement in Oracle Database Server.

Answers are greatly appreciated.

Global temporary tables in Oracle are very different, as they have a permanent logical definition and transient storage, so practically the reverse of your situation.

I would look at trying to avoid temporary tables at all, in favour of Common Table Expressions (WITH clauses). They are very powerful stuff and can transiently use temporary disk space if it aids performance.

Otherwise you'll have to manually code unique table names etc -- it'll be unpleasant to support in future.


Is it possible to change the drive that Microsoft DPM backs up to? A while ago our sysops guys created a 15 minute transaction log backup schedule using Microsoft DPM.  We often have an issue where there won't be enough space on the Log drive to perform a backup because DPM creates its backup on the same drive that the logs live on. Eventually, because DPM can't back up the logs, they get larger and larger and the problem gets worse and worse until I run a manual backup of the bloated logs through SQL Server (and then, sadly, I have to run Shrinkfile on the logs that bloated).

I can't find any setting or option in DPM that allows me to write the backups to a different drive with more space instead of the drive that the logs sit on.  Does anyone know if it's possible to do this and how?

DPM and Windows Server Backup make a determination when the agent is installed as to which drive it would use for System State Backups.

This by default is the drive with the most available free space. That information gets saved into the PSDataSourceConfig.XML file.

WARNING:
If the server you are protecting is a member of a cluster, it is possible that a cluster drive will be selected in this process. If that drive ownership has been switched to another node, then next time system state for the node is done, the drive is no longer available and the backup will fail. In this situation, you will need to modify the PSDataSourceConfig.XML to point it to a local drive.

On the server you are backing up, navigate to C:\Program Files\Microsoft Data Protection Manager\DPM\Datasources.
There you will find a file named PSDataSourceConfig.XML. Right click that file and choose to edit.
Find the section for  which is followed by a drive letter and WindowsImageBackup. This will be the current location WSB will put the files.
Modify the file to reflect the drive letter you want WSB to use.


Monitor source SQL code of inserts into table I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'm not able to repro this it's just happening from time to time. And I wanna know which exact SQL statement and module (eg. procedure/trigger / ad-hoc statement) is the source of that insert. To catch the culprit.

Using Profiler or Extended Events is not the way - because you don't see the data (just statements) and while I'm not able to repro this I cannot correlate the output with the data.

I've tried collect info from sys.dm_exec_input_buffer(@@SPID,0) inside my after triggering on insert (made for logging purposes, collecting every insert in that table). But this is too wide. It's giving just initial SQL statement, let say outer stored procedure, but the insert statement could be invoked in some nested stored procedure or trigger.

I've tried to collet output of sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) - see the whole code below. But this is too narrow in opposite - it is giving me statement of my collecting after trigger.

Is there a way to catch something like it?

SELECT
OuterSQL = CAST(T.text as NVARCHAR(255))
,InnerSQL = CAST((SUBSTRING(t.text,(s.statement_start_offset/2) + 1,
          (CASE
             WHEN s.statement_end_offset = -1
               THEN DATALENGTH(t.text)
             ELSE s.statement_end_offset
           END - s.statement_start_offset)/2 + 1)) as NVARCHAR(255))
FROM sys.dm_exec_connections
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
    INNER JOIN sys.dm_exec_query_stats s ON sys.dm_exec_connections.most_recent_sql_handle = s.sql_handle
WHERE session_id = @@SPID;

You haven't included version or edition info, so if you're running SQL Server 2016 SP1 or higher (or Enterprise Edition for earlier versions), try using SQL Server Audit to trace this information.

You can set up a Database-level Audit to capture INSERT action on that table, then query the audit files to identify the calling application and statements.

SQL Audit: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

Audit Action Groups: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017

Also, Profiler and Extended Events should be able to capture this information for you. Which trace events are you capturing?

If you use the template TSQL_Replay trace and filter it appropriately, you should be able to see the calling code, including the explicit values passed to the stored proc or INSERT statement.


Problem with Error: InnoDB: page ... log sequence ... number is in the future! i know this question was asked before, but i am at a point where i am really stuck and do not know how to continue with this problem and i would really like to know, what causes this error to come up.

we had a replication which worked without any problem. our setup includes one database master and four slaves, all running percona mysql, same version (5.5.x). the other day our database master died because of a hardware issue. so we went on and made a slave the new master. should not be much of a problem and was indeed no problem before. but now after getting things up again, i keep getting lot's of log entries in my syslog like:

Mar 15 13:17:25 dbslave3 mysqld: 120315 13:17:25 InnoDB: Error: page 263337 log sequence number 34159788445
Mar 15 13:17:25 dbslave3 mysqld: InnoDB: is in the future! Current system log sequence number 1096541424.

Ok, so i used xtrabackup -- which i used for this things before -- to create a dump from the new master and used the dump for applying it on the slaves. without success. still the same problem with this error message. next i tried the following:

i took my site offline and made sure, that all data was replicated to all slaves -- indeed: replication is working! no data get's lost, every slave has all the data it should have.

next i did a reset slave on the slaves and on the master i did a reset master. in my opinion this should reset any binlogs and start over again with fresh sequence numbers. next i did a show master status and used the new sequence file and number for the change master to ... command on the slaves. i activated replication again and took my site online again.

without success! still the same problem. so my biggest question:

WTF is going on!? how can this error occur on:

a fresh set-up slave using xtrabackup
after a binlog reset on master and slave

how to solve it? i mean: i read a lot about inserting gigabytes of data in the database, that "the sequence pointer can catch up" ... but this sounds a lot like a dirty hack and can't be a real solution to this problem, no!? and my biggest concern: is the problem indeed solved using this dirty hack?

any other ideas what i could do? dumping the data using mysqldump and importing it again is no option btw., because the database is so big, that it would take to long to import it again.

UPDATE

what i also did is: dumping one of the slaves, including: deinstalling mysql completly, removing all mysql related stuff including binlogs, databases, etc. and setting it up again -- without success. that's the fresh install i am asking about above.

Nobody seems to have a real solution for this issue, at least i was not able to dig anything useful up. But apparently it helps, to just let it run and cleanup the logs on a regular basis, so your server does not get filled up with logs ... at least, since some days i do not have anymore log entries ...

this is all very odd -- in my humble opinion.


Are SQL Server's Date values stored as a single int? SQL Server stores DATETIME internally as two INT values, as SQLDenis explains.

Is it correct (by logical extension) that DATE types (SQL Server 2008+) are stored as a single INT?

Just for fun, let's see what's on the page.

CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.experiment(d DATE NOT NULL);
CREATE CLUSTERED INDEX x ON dbo.experiment(d);
INSERT dbo.experiment SELECT SYSDATETIME();

DBCC IND('floob', 'dbo.experiment', 1);

Partial results (will differ on your system a bit):

----
13

PagePID iam_chain_type PageType
------- -------------- --------
229     In-row data    10
228     In-row data    1

So let's look at page 228:

DBCC TRACEON(3604, -1);
DBCC PAGE(13, 1, 228, 3);

Partial results. We see that the value for d occupies 3 bytes. If we look at the memory dump for the row, we see a hex value for the internal storage component: c8350b02 - what could that be?

SELECT DATEDIFF(DAY, CONVERT(DATE, '0001-01-01'), CONVERT(DATE, '2012-06-11'));

Result:

734664

So:

SELECT CONVERT(VARBINARY(8), 734664);

Result:

0x000B35C8

(Looks amazingly similar to what's on the page, just with bits flipped in the opposite order?)

Then:

SELECT DATEADD(DAY, CONVERT(INT, 0x000B35C8), CONVERT(DATE, '0001-01-01'))

Result:

2012-06-11


Power BI on-prem data gateway for Azure devops pipeline? I'm building a POC for DB devops using redgate SCA and an azure devops pipeline. Need to connect to our SQL Servers that don't have public IPs. Was hoping power BI on-prem gateway would facilitate. Is there a way to make that work, or do I need local build agents?

To clarify, I have :

Azure devops (cloud) on org.VisualStudio.Com
RedGate SQL Change Automation project in: TFSVC based repo (in azure devops services in org.VisualStudio.Com
Integration DB on SQL Server instance in company network - no public IP.

I now have a azure devops build pipeline triggered by source commit. The SCA build artifact is created and stored.  I want to deploy to our integration SQL Server DB.

At this point, I believe I need to install and configure a local windows build agent to enlist in the pipeline. It seems unfortunate, though, since we already have power BI on-premises data gateway. It would be nice to leverage that connectivity in our deployment pipeline instead of having to configure a local build agent.

It's also unfortunate because I'm finding the online docs for configuring a local build agent to be challenging and unclear.

Can you help me get over this hurdle?

I have succeeded in getting the Azure devops pipeline to create releases and deploy to on-prem resources (sql servers) .

The overall solution was indeed to install and configure a self-hosted windows agent for azure pipeline. This agent vm has the redgate powershell components installed on it.

Some interesting hurdles that had to be overcome :

1) when running the .\config.cmd to install and configure the agent, it has to be run in a regular powershell cmd shell. ISE doesn't work.

2) copy and paste didn't work to get my PAT into the powershell window when running config.cmd. I actually had to type it in.

I'd love to see the MS docs get cleaned up a bit. They were pretty unclear in relation to what I saw on my screen in .visualstudio.com

Cheers.


intel p3600 SSD no increase in read speed - postgres rdbms vs hdd We run a custom application that produces analyzable data-sets for medical research.

the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in postgres RDBMS and data is indexed and clustered.

Our application is read intensive(read only).

Usually it took about 7-8 hours on a normal computer running on a HDD to go through all this data and produce a dataset.

Recently we brought a Highend machine (intel i5 5960x, 32GB ram with an intel p3600 2TB SSD) and set up the database and our application.

I was amazed to find out it took almost the same amount of time that it used to take on the old machine to extract a dataset.

EDIT 1 : How the program works

The program is a Java Application

There are about 13Million patient's records in the database.
and each patient has 10000+ entries about their health/diagnosis/prescriptions etc. Hence the massive size.

What we do is look for patients with particular medical conditions(defined in a particular table) and pull out their medical history and other events(from different tables) from the database and do research on it.

The program is not CPU bound as we have very simple logic behind the program

I create a lot of java objects(at least 1 object per patient, which in turn is made up of lot of strings which hold values I pull out of database), hence we can say it is a bit memory intensive

Yes it is harddrive intensive as we read Billions of records, and that is why I though if i have a really fast harddrive I can get this running very fast. And hence brought a Intel P3600 2TB SSD

SAMPLE QUERY

These kind of queries are run on tables with 100,000+ of records for each patient 4-5 times per program execution.

SELECT Field1,Date
FROM schema.table1
WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date;

Note: Field1,Date,patientID is indexed

EDIT 2:

**Explain Analyze Output:**

"GroupAggregate  (cost=51.75..51.78 rows=1 width=12) (actual time=1.112..1.112 rows=1 loops=1)"
"  Output: Field1, min(Date), Date"
"  Group Key: medical.Date, medical.Field1"
"  Buffers: shared hit=102 read=4 written=4"
"  ->  Sort  (cost=51.75..51.76 rows=2 width=12) (actual time=1.107..1.108 rows=1 loops=1)"
"        Output: Field1, Date"
"        Sort Key: medical.Date, medical.Field1"
"        Sort Method: quicksort  Memory: 25kB"
"        Buffers: shared hit=102 read=4 written=4"
"        ->  Index Scan using patmedmed_index on a6658.medical  (cost=0.42..51.74 rows=2 width=12) (actual time=1.016..1.089 rows=1 loops=1)"
"              Output: Field1, Date"
"              Index Cond: ((medical.patid = '00Nb'::bpchar) AND (medical.Field1 = ANY ('{C100111,C100112,C100z00,C101.00,C101000,C101100,C101y00,C101z00,C102.00,C102000,C102100,C102z00,C103.00,C103000,C103100,C103y00,C103z00,C104.00,C104.11,C104000,C10410 (...)"
"              Filter: (medical.Date <= '2015-01-01'::date)"
"              Buffers: shared hit=102 read=4 written=4"
"Planning time: 0.844 ms"
"Execution time: 1.248 ms"

I really need help

You should store your value for FIELD1 in a TABLE, instead of in a bunch of variables.  Make a three level index, a COVERED index specifically.

your IN clause is preposterous. I mean.. Learn how to Normalize.

THIS:

WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )

is actually SLOWER than this:

SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'aaaaaaa'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'bbbbbbb'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'ccccccc'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'nnnnnnn'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date


Update of db yields "Error Code: 1215. Cannot add foreign key constraint" A phpmyadmin export of a remote MySQL db is used to update a local instance of the db. Disable foreign key checks is selected.  Yet running the export either as a phpmyadmin import or as a script in Workbench results in

Error Code: 1215. Cannot add foreign key constraint

Background: the schema was created by Doctrine in a Symfony 2.8 application.
The import errors out on:
CREATE TABLE `center` (
  `id` int(11) NOT NULL,
  `center` varchar(20) DEFAULT NULL,
  `county_id` int(11) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A standalone script that demonstrates the error, copied out of the export, is:
Edit:
If the following script is used in a db with no tables it will not error out if run 2 or more times.  Only in the context of the full db will errors be thrown.
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Table structure for table `center`
--

DROP TABLE IF EXISTS `center`;
CREATE TABLE `center` (
  `id` int(11) NOT NULL,
  `center` varchar(20) DEFAULT NULL,
  `county_id` int(11) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `center`:
--   `county_id`
--       `county` -> `id`
--

--
-- Dumping data for table `center`
--

INSERT INTO `center` (`id`, `center`, `county_id`, `enabled`) VALUES
(1, 'Tahoe City', 1, 1),
(2, 'Squaw Valley', 1, 0),
(3, 'Kings Beach', 1, 1),
(4, 'Soda Springs', 1, 0),
(5, 'Truckee', 2, 1),
(6, 'Incline Village', 3, 1),
(7, 'N/A', 8, 0);

--
-- Table structure for table `county`
--

DROP TABLE IF EXISTS `county`;
CREATE TABLE `county` (
  `id` int(11) NOT NULL,
  `county` varchar(15) NOT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `county`:
--

--
-- Dumping data for table `county`
--

INSERT INTO `county` (`id`, `county`, `enabled`) VALUES
(1, 'Placer', 1),
(2, 'Nevada', 1),
(3, 'Washoe', 1),
(4, 'El Dorado', 1),
(5, 'Douglas', 1),
(6, 'Carson', 1),
(7, 'Alpine', 1),
(8, 'N/A', 0);

--
-- Indexes for table `center`
--
ALTER TABLE `center`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_40F0EB2485E73F45` (`county_id`);

--
-- Indexes for table `county`
--
ALTER TABLE `county`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `center`
  ADD CONSTRAINT `FK_40F0EB2485E73F45` FOREIGN KEY (`county_id`) REFERENCES `county` (`id`);

SET FOREIGN_KEY_CHECKS=1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Discovered the source of error.  There is a third entity Contact that had a foreign key to both Center and County entities. By removing the county property and thus the foreign key from the Contact entity the error is removed.  The trick now, though, will be to get this schema change into production.


Report Builder CTP 2.3 Installation DLL Error I was attempting to install Report Builder CTP 2.3 and cannot get it to install on any of three tested systems.

First system was Windows Server 2012 R2 Datacenter - MS Azure SQL 2016 preview image with SSDT 2016 Preview installed and Report Server SQL 2016 setup and running. This isn't server software, but all components for SQL 2016 preview and the SSRS service are present...

Second system was Windows 7 Ultimate, and third was Windows 8.1. All result in the exact same error message:

Has anyone successfully installed Report Builder CTP 2.3 and if so how did you accomplish this please?

Simple solution... posting for anyone else testing SQL 2016.

Do NOT supply a default SSRS Server URL during the installation process and it completes without error (Windows 2012 Datacenter and Windows 7 confirmed).

Please help MS fix this issue. Happy reporting!


Postgres - Optimizing an view dependent on an aggregate function? I have a table of about 15 million records.  Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes.  This results in multiple rows with the same charge_id, and related hierarchical columns.  This isn't controlled by me and can't be changed, plus we like having the history available for querying.

The view below is intended to identify the most recent entry for each charge_id, and create a simple key value pair table for joining off of.  The view works fine, but execution time is horrendous.  I've tried a couple of stabs at an index to speed things up, but each time it appears that postgres is skipping the index and scanning everything anyways.  I should also note that most queries where we're joining to this view are going to be full table aggregates, grouped by 2-5 different dimensions in the charges table.

My question specifically is what can I do to speed up execution time on this particular view?

CREATE VIEW current_charge_ids AS
(
SELECT
    c2.id,
    c2.charge_id,
    t1.last_post_date
FROM
    charges c2
LEFT JOIN
    (
        SELECT
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id,
            max(c1.post_date) AS last_post_date
        FROM
            charges c1
        GROUP BY
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id
    ) t1
ON
    c2.client = t1.client AND
    c2.practice = t1.practice AND
    c2.account_id = t1.account_id AND
    c2.encounter_id = t1.encounter_id AND
    c2.charge_id = t1.charge_id AND
    c2.post_date = t1.last_post_date
);

You can hugely simplify the query using DISTINCT ON:

SELECT DISTINCT ON (client, practice, account_id, encounter_id, charge_id)
       id, charge_id, post_date
FROM   charges
ORDER  BY client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC;

Will be considerably faster in any case. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

The last ORDER BY expression id DESC is optional to break ties if rest should not be unambiguous, yet. May not be needed.

Support this with a matching multicolumn index:

CREATE INDEX charges_latest_idx ON charges
(client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC);

Whether such an index will be useful depends on undisclosed details.

Note in particular, that sort order has to match the query. In Postgres 9.2 or later this may even function as covering index, depending on undisclosed details.

Depending on undisclosed details, a materialized view might be a candidate, too. The more write operations the smaller the likelihood this would help. The same goes for the covering index.


Backup data only through command line in SQL Server I must create a post deploy script based on data from a previous sql server database. I'm making a data-only backup, but it must be done table by table due to avoid the cyclic dependency from the forward keys.
For each table, I go to SQL Management Studio, go to task, generate script, configure the backup and generates data only backup. But this proccess done table by table takes much time and due to have to reconfigure each one of those times and is a quite boring process, it is also very sensitive to human error.

I would like to know if there's a way to create a script to make it. Can be some command in tsql, sqlcmd or powershell. Google just gives me the full backup for line command or some another process involving some kind of "windowed wizard" in case of backuping just one table, and it does not satisfy my restrictions.

mssql-scripter

From https://blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views/:

  You can use mssql-scripter on Linux, macOS, and Windows to generate
  data definition language (DDL) and data manipulation language (DML)
  T-SQL scripts for database objects in SQL Server running anywhere,
  Azure SQL Database, and Azure SQL Data Warehouse. You can save the
  generated T-SQL script to a .sql file or pipe it to standard *nix
  utilities (for example, sed, awk, grep) for further transformations.
  You can edit the generated script or check it into source control and
  subsequently execute the script in your existing SQL database
  deployment processes and DevOps pipelines with standard multiplatform
  SQL command line tools such as sqlcmd.

Install instructions on GitHub

Sample usage:

Generate DDL scripts for all database objects and DML scripts (INSERT statements) for all tables in the Adventureworks database and save the script to a file

$ mssql-scripter -S localhost -d AdventureWorks -U sa ––schema-and-data > ./adventureworks.sql