Compare timestamp type columns in SQL Server I am trying to get data since last timestamp. In my legacy database, all tables have a column named as recordStamp type timestamp. I want to rely on this to select records modified after the max(recordStamp) from previous select.

I tried with T-SQL; but this tells only equality. What are my options..

CREATE TABLE Cars (
    id int identity,
    model varchar(20),
    brand varchar(20),
    recordstamp timestamp)

INSERT Cars (model)
VALUES
('accord'),
('camry'),
('corolla'),
('civic'),
('prius')

--latest recordstamp: 0x0000000001DD8AAF
select * from cars order by recordstamp desc --latest recordstamp: 0x0000000001DD8AAF

--Update a record
Update Cars set brand = 'honda' where model = 'accord'

--I want only the accord in result.
select * from cars order by recordstamp desc

TIMESTAMP is easily the worst naming decision Microsoft has made, at least in SQL Server. Not only does the data not have any date or time information, using the name actually violated the ANSI standard.

I asked them long ago to deprecate the keyword and always refer to this as ROWVERSION. But still the tools and metadata show and script out TIMESTAMP even when you explicitly created the table using ROWVERSION. It's horribly misleading.

In any case, there is no way to derive date/time information from this column, or to tell how much time has passed when the column information changes by n. And I'm not sure how you're going to know what recordstamp to base it off of anyway, if you're trying to compare. In an isolated scenario you can do something like this:

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars;

DECLARE @ts int;
SELECT @ts = MAX(convert(int, recordstamp)) FROM dbo.Cars;

UPDATE Cars SET model += 'x' WHERE id = 1;

SELECT *, CONVERT(int, recordstamp)
  FROM dbo.Cars
  WHERE recordstamp > @ts;

But how are you going to do this when it's another session updating the table? And outside of a very controlled environment, you have no idea what's going to happen if this database is backed up and restored elsewhere, fails over, or even when the instance is restarted. The documentation states that it is increasing, but doesn't explicitly state that this is forever, and also warns against using it for tracking date/time in any way.

If modification date is important to you, do it right: add a datetime2 column to the table and keep it maintained with a trigger.


How to get newly inserted row ids when inserting with execute? I have a function which returns newly inserted row count:

   BEGIN
   EXECUTE (

      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column

      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
  END;

Instead, I have to return a list which contains newly inserted row ids. How can I do that?

I tried with result_oid,@@identity but I got no result.
This function is returning only one value. How can I get all the values?

CREATE OR REPLACE FUNCTION public.test2(_tbl regclass,_id integer)
RETURNS integer[]
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL UNSAFE
COST 100
AS $BODY$DECLARE
ab integer;

BEGIN
  EXECUTE (

  SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1 RETURNING id',
                _tbl, string_agg(quote_ident(attname), ', '))
  FROM   pg_attribute
  WHERE  attrelid = _tbl
  AND    NOT attisdropped  -- no dropped (dead) columns
  AND    attnum > 0        -- no system columns
  AND    attname <> 'id'   -- exclude id column
  )
 USING _id
 into ab;

 RETURN ARRAY[ab];
END;$BODY$;

To return a set, Use RETURN QUERY EXECUTE in a function that RETURNS SETOF integer:

CREATE OR REPLACE FUNCTION pubilc.test_set(_tbl regclass, _id int)
  RETURNS SETOF integer AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   WITH (
   SELECT format('INSERT INTO %1$s(%2$s)
                  SELECT %2$s FROM %1$s WHERE budget_id = $1
                  RETURNING id'
                , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call:

SELECT * FROM pubilc.test_set('mytbl', 123);

To return an array, move the INSERT to a CTE and feed the result to an array constructor.

Then we don't need the function to RETURNS SETOF int[], because it always returns a single value. The simplest way is to define an OUT parameter and assign the result of the query:

CREATE OR REPLACE FUNCTION public.test_arr(_tbl regclass, _id int, OUT _ids int[]) AS
$func$
BEGIN
   EXECUTE (
   SELECT format('WITH ids AS (
                     INSERT INTO %1$s(%2$s)
                     SELECT %2$s FROM %1$s WHERE budget_id = $1
                     RETURNING id
                     )
                  SELECT ARRAY (TABLE ids)'
               , _tbl, string_agg(quote_ident(attname), ', '))
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   AND    attname <> 'id'   -- exclude id from column list
   )
   USING _id
   INTO _ids;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT public.test_arr('mytbl', 123);

Related:

RETURN value directly from INSERT with RETURNING clause
Find changed columns for near-identical rows across tables
Why is array_agg() slower than the non-aggregate ARRAY() constructor?


MySQL: parallel restores at same time from backup files? Slightly related to this question, but for MySQL: Would it be possible/wise to execute 2 parallel restores from a single backup?

Is it possible/wise to do a parallel restore of two independent databases from two separate MySQL dump files?

Restoring one of them seems to only take up about 10% of CPU cycles consistently and takes a long time to finish, so I'm thinking that a second restore shouldn't cause any load problems - but might it cause any other MySQL/DB issues?

This should not be a problem as long as the data in the two dump files involves different tables... however, it may or may not be as helpful as you might hope, because if the single restore is only using 10% cpu, that suggests that your disk, as opposed to cpu, may be the more significant determining factor in limiting the speed of the restore.


SSIS data flow gets stuck reading table with no errors When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does nothing with no error output. I have tried waiting for 30 minutes for it to throw an error, but nothing happens (it just spins forever).

I have narrowed this issue down to the source live transactional DB on the top left side (the same process with a local copy of that live IO DB works fine). When I narrow down data selection from the live source DB to start from the row when it stopped last time, it'll then read again so many rows and then stop again.
I have checked the data in the few rows where it stopped and it's fine.

Not sure what causes this issue or how to troubleshoot this further.

DanGuzman from the above comments has pointed me in the right direction. The problem was with queries getting suspended in SQL server.
To resolve this issue, I  used SELECT statement with WITH (NOLOCK) option.


LOG_BACKUP wait type for Transaction log file of a simple recovery model database A few minutes ago I ran into a strange situation with my prod database. I have db X with simple recovery model. SCOM gave me an alert that the log disk is running out of space. I then checked the disk and saw that db X's log file is 11 Gb even though the data file is 300 Mb and the recovery model is simple.

Then I checked DBCC OPENTRAN but there were no open transactions, neither on the database nor on the server.

Then I tried to shrink the log file, but it said that there was no empty space.
I checked the log_reuse_wait_type column and it was LOG_BACKUP. I know that, by definition, it should be impossible to see that wait type for a database using the SIMPLE recovery model, but there it was. I double checked all the steps that I mentioned above and nothing changed. Then I changed the recovery model of db X to FULL and set it back to SIMPLE. After that log_reuse_wait_type reported as NOTHING.

In the end I solved the problem but still wonder why it happened. Has anyone encountered a similar problem?

Are you running SQL Server 2012?  Is your model database in SIMPLE recovery model?  Are you a bit behind with your patching?

This appears to have been the issue for a previous ask of this question, at SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

Fixed in KB2830400  which forms part of SQL Server 2012 CU7 / SQL Server 2012 SP1 CU 4


Compute new records based on existing ones (e.g., sum of 2 records)? Background
I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities.

For some fruits (e.g., here Banana and Cherry), I might not have both records.

My table looks then like this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |

This table gets partially updated over time (i.e. I might only change the Apple | Actual quantity, or add the Banana | Expected record).

Question
➥ Is it possible to automatically compute new records based on the existing ones? If yes, how?

In our example, I might want to compute the Variation between the actual and the expected value: i.e. add the {fruit} | Variation | [{fruit | Actual}(Qty) − {fruit | Expected}(Qty)] record when I have both Actual and Expected records for a given {fruit}.

In our case, I'd like that my table becomes this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
| Apple  | Variation  |   4 | ← newly computed record
| Durian | Variation  |  -1 | ← newly computed record

You can see that there is no Variation record for Banana or Cherry, because they are missing their Expected or Actual record, respectively.

I don't like the design, but sometimes we just have to deal with the what we find even if we don't like it.  Here is a query that gets you the 'Variation' part you are looking for:

select fruit, 'Variation' as recordtype, act_qty-exp_qty as qty from
   (select fruit, qty as exp_qty from foobar where recordtype ='Expected') as ex
join
   (select fruit,qty as act_qty from foobar where recordtype='Actual') as ac
using (fruit);

You can either insert those results into the table (after clearing out the old results) or you can create a view with UNION ALL so that the Variation rows appear in the view, without ever physically existing.


Get all dates from an interval that are missing from other intervals I am working with a database schema shown below and I am struggling with writing a query on that schema.

A hotel has a contract with a period of validity. Also it has many seasons, and each season has many periods. The task is to return all dates from the contract validity period that are not available in the seasonal periods.

This is my schema:

Ideally, I would like to get all possible intervals that do not cover the periods of the hotel. But if it is not possible then at least a list of dates.

Example (all dates are in the DD.MM.YYYY format):

Contract period: 1.01.2018 – 31.12.2018
Hotel periods: 1.01.2018 – 30.06.2018; 1.08.2018 – 31.12.2018

In this case I lose one month 1.07.2018 – 31.07.2018.
And I want to get this period, or several periods if there happen to be several. So, something like this:

BeginDate  EndDate
---------  ----------
1.07.2018  31.07.2018

If it's not possible to get a list of periods, then I want to get all missing dates, and group them in periods on the server side.

If a hotel can never have overlapping seasonal periods, then it is possible to obtain the result as a list of intervals by direct matching of intervals using the method below.

First you need to invert the seasonal period list, which means getting the list of gaps between the items of the list complementing it with two more intervals representing the period before the first item and the period after the last item. In other words, a list like this:

date_from    date_to
----------  ---------
date_from1   date_to1
date_from2   date_to2
.
.
.
date_fromN   date_toN

would be transformed to a list like this:

   date_from            date_to
-----------------   ------------------
   0001-01-01        date_from1 - 1 day
date_to1   + 1 day   date_from2 - 1 day
date_to2   + 1 day   date_from3 - 1 day
.
.
.
date_toN-1 + 1 day   date_fromN - 1 day
date_toN   + 1 day      9999-12-31

The date_fromi and date_toi in the second list refer to the corresponding values from the first list. The one-day adjustments are there to account for the fact that both the input intervals and the output intervals are inclusive.

This is how you could generate the list of gaps in SQL based on your schema:

SELECT
  IFNULL(
    (
      SELECT
        hs_last.date_to + INTERVAL 1 DAY
      FROM
        hotel_season AS hs_last
        INNER JOIN hotel_season_period AS hsp_last
          ON hs_last.id = hsp_last.hotel_season_id
      WHERE
        hs_last.hotel_id = hs_this.hotel_id
        AND hsp_last.date_from < hsp_this.date_from
      ORDER BY
        hsp_last.date_from DESC
      LIMIT
        0, 1
    ),
    CAST('0001-01-01' AS date)
  )                                      AS date_from,
  hs_this.date_from - INTERVAL 1 DAY     AS date_to
FROM
  hotel_season AS hs_this
  INNER JOIN hotel_season_period AS hsp_this
    ON hs_this.id = hsp_this.hotel_season_id
WHERE
  hs_this.hotel_id = @param_hotel_id

UNION ALL

SELECT
  MAX(hs_this.date_to) + INTERVAL 1 DAY  AS date_from,
  CAST('9999-12-31' AS date)             AS date_to
FROM
  hotel_season AS hs_this
  INNER JOIN hotel_season_period AS hsp_this
    ON hs_this.id = hsp_this.hotel_season_id
WHERE
  hs_this.hotel_id = @param_hotel_id
;

Note that adjacent seasonal periods will generate "gaps" for which date_from > date_to. Such ranges would be invalid, of course, and you would need to filter them out before further processing.

The further processing will involve finding the gaps intersecting with the hotel contract period. Those that do intersect with it will actually be the intervals that you want to return, except the you may need to adjust the beginning of the first matching gap as well as the end of the last one, because those might go outside the contract period bounds.

This is the matching condition, which takes into account that both the contract period and the gaps are inclusive intervals:

gap.date_to >= contract.date_from AND gap.date_from <= contract.date_to

That is, a gap is considered as intersecting the contract period when its end either comes after or exactly matches the contract beginning while at the same time the gap's beginning either comes before or exactly matches the contract's end.

As I have already said, the first and the last matching gap may go partly outside the contract period, i.e. like this:

contract:    [                 ]
first gap: [    ]
last gap:                    [    ]

For the output you will want to modify them like this:

contract:    [                 ]
first gap:   [  ]
last gap:                    [ ]

This means you will need to calculate both the beginning and the end for each gap: take the latest between gap.date_from and contract.date_from as the beginning and the earliest between gap.date_to and contract.date_to as the end:

GREATEST(gap.date_from, contract.date_from)   AS date_from,
LEAST   (gap.date_to  , contract.date_to  )   AS date_to

Putting everything together, this is what you get:

SELECT
  GREATEST(gap.date_from, contract.date_from)   AS date_from,
  LEAST   (gap.date_to  , contract.date_to  )   AS date_to
FROM
  list_of_gaps AS gap
  CROSS JOIN contract
WHERE
  contract.company_id = @param_hotel_id
  AND gap.date_to >= contract.date_from
  AND gap.date_from <= contract.date_to
;

For the list_of_gaps you can directly use the first query as a derived table:

...
FROM
  (
    SELECT
      IFNULL(
    .
    .
    .
  ) AS gap
...

Or you can store that query's results in a temporary table first:

CREATE TEMPORARY TABLE tmp_gap_list
AS
SELECT
  IFNULL(
.
.
.

and use the temporary table as the list_of_gaps instead:

SELECT
  GREATEST(gap.date_from, contract.date_from)   AS date_from,
  LEAST   (gap.date_to  , contract.date_to  )   AS date_to
FROM
  tmp_gap_list AS gap
.
.
.

The latter option – doing the job in two distinct steps – might be faster, depending on whether MySQL will find the single-query solution too complex to come up with an efficient plan, and also depending on the amount of data in your tables. Test both options for yourself to choose the one that works best for you.


ALTER DATABASE MODIFY FILEGROUP [filegroup] AUTOGROW_ALL_FILES Does anyone know of a way to accomplish setting a filegroup to AUTOGROW_ALL_FILES without setting the database to single user mode?  I frequently add new filegroups/files to a 24/7 production database where killing active sessions and rolling back transactions (via SET SINGLE_USER WITH ROLLBACK IMMEDIATE) is definitely not preferred.  IMO this operation should be able to be accomplished with other active sessions on the database.

It seems there is no way around it.  This is per an offline conversation with Paul Randal from SQL Skills.  So that would mean I would need to either have no other open sessions on the db, or set it to single user mode prior to running the set statement.  Thanks for the assistance Paul.


Get the total usage (in percent) between two specified dates I have table at SQL server 2017:

Number 1 represent the test equipment, it is connecting and 0 is disconnected.
need your help to define query that can give me the amount of usage (in percentage) to :

Connect Statistics In accordance with the selected range of DateTime in
specific "DeviceName" and "PcName"
Running Statistics In accordance with the selected range of DateTime
in specific "PcName"

Thank you for your help

 USE [LabDevices]
  GO
  INSERT INTO [dbo].[Statistics]
               ([DeviceName]
               ,[ConnectStatistics]
               ,[RunningStatistics]
               ,[DateTime]
               ,[PcName])
         VALUES
               (<DeviceName, varchar(50),>
               ,<ConnectStatistics, bit,>
               ,<RunningStatistics, bit,>
               ,<DateTime, datetime,>
               ,<PcName, varchar(50),>)
  GO
  INSERT INTO [dbo].[Statistics] (DeviceName,ConnectStatistics,RunningStatistics,DateTime,PcName)
    VALUES ('Regatron_1', 1 , 0 , '2019-02-27 08:00:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 08:10:00','PC1'),
           ('Regatron_1', 1 , 0 , '2019-02-27 08:50:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 09:20:00','PC1'),
           ('Regatron_1', 0 , 0 , '2019-02-27 09:40:00','PC1'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:00:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 08:10:00','PC2'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:50:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 09:20:00','PC2'),
           ('Regatron_2', 0 , 0 , '2019-02-27 09:40:00','PC2')

Hi

First thank for your help

Let me clarify the question and expected result:

The query should generate the used time for each device in percent

In case the selected time is between '2019-02-27 08:00:00' to '2019-02-27 09:40:00' -> time range is 1.4

Regatron_1_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_1_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1

Regatron_2_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_2_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1 The answer:

Regatron_1_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_1_RunningStatistics(%) = (1/1.4)*100

Regatron_2_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_2_RunningStatistics(%) = (1/1.4)*100

Can you help to define the correct query?

Thanks

if I understand your requirment correctly your command should be like this, if not please provide example result.

Select DeviceName, (Count(ConnectStatistics)* 100 / (Select Count(*) From [dbo].[Statistics])) as Score, pcname
From [dbo].[Statistics]
where datetime = '2019-02-27 09:40:00'
Group By DeviceName,pcname


SQL Server Agent logs in as guest on a linked server I have a job called @test_credentials which runs the following query
select * from openquery(SERVER2,
'select USER_NAME(),* from openquery(SERVER1,''SELECT USER_NAME() '')')

and outputs those results to a file.

Job '@test_credentials' : Step 1, 'test_credentials' : Began Executing 2015-08-31 17:53:45
guest                                                                                LinkedServerUser
(1 rows(s) affected)

The linked server definitions for both SERVER1 and SERVER2 are using
the linkedserveruser to login to one another (security context option).
Both linked server definitions are defined with the same options
The linkedserveruser user account exists on both servers and is not disabled in anyway.
The service account running the AGENT is identical for both SERVER1
and SERVER2.

So my question is this:
Why is SQL Server Agent logging in as guest on SERVER2 when executing this job?

Personally I would try using ORIGINAL_LOGIN() instead of USER_NAME().  USER_NAME() returns the database principal or user associated with your login in the database you are connecting to.  In this case you are probably connecting to master in both cases.  If LinkedServerUser doesn't have a specific login in master on SERVER2 then you will see GUEST.

If on the other hand you use ORIGINAL_LOGIN() you will get the server principal that connected, which will probably be LinkedServerUser in both cases.


SQL Server 2014 Transactional Replication Partition Switching I am working with SQL Server 2014 Transactional Replication with tables that have partitions.  The source tables are partition switched when they are loaded and so far I have been able to replicate this successfully.

The replication source I am working with performs some dynamic partition management by creating partitions on the Partition Function/Partition Scheme during the load into the table.  This is not natively supported by Transactional Replication per the documentation found here:

  After the Subscriber is initialized, data changes are propagated to the Subscriber and applied to the appropriate partitions. However, changes to the partition scheme are not supported. Transactional and merge replication do not support replicating the following commands: ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or the REBUILD WITH PARTITION statement of ALTER INDEX. The changes associated with them will not be automatically replicated to the Subscriber. It is the responsibility of the user to make similar changes manually at the Subscriber.

This is where I am getting hung up.  We've worked through most of the problems with the loading and switching of partitions and we are now dynamically creating the new partitions, as they come in, on both the replication source (publisher) and replication target (subscriber) through the use of a stored procedure as part of the load to the publisher.  The stored procedure exists on both the publisher and subscriber (manually put there).

When running a test last night, we saw that partitions were dynamically created on the publisher and subscriber, but there was a new error on the Log Reader Agent.  At this point, I don't even know where to begin to start tracking this down.

  Error messages:
  The process could not execute 'sp_replcmds' on 'RNCAZFAST2'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
  Get help: http://help/MSSQL_REPL20011

  No catalog entry found for partition ID 72057598976393216 in database 27. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption. (Source: MSSQLServer, Error number: 608)

I started looking up the error and it looked like it was a bug back in 2005, but we are on SQL Server 2014 Enterprise on an Azure VM.  Any help is greatly appreciated!

We ended up coming up with a solution for this.  Basically, we had to manage the partition creation for both the publisher and subscriber databases/tables when we made a change.  We were loading data using SSIS and there were mechanics that were built in to create new partitions on the publisher, so we implemented the same logic to do the work on the subscriber too.

Of course, this can be difficult if you get into having multiple subscribers.  I suppose one solution would be to query the subscription details and dynamically perform the same ALTER PARTITION statement to any subscriber database.


Cannot Copy Database I am working with SQL server 2012. I want to copy a database along with all the data. I just want to create a dev DB.

I know SQL Server 2012 has a Copy Database... wizard. But when I run that wizard, I get an error on the last step:

On error, it tells me that error log in inside a text file on the shown path. When I go to that path, there is no such file there.

I tried backup and then restore with a different name as well. But it gave similar results. I got error while restoring. But I couldn't find the error log file.

Can anyone please help me with this?

These are the steps of the SQL Agent Job:  detach the database, copy MDF and LDF files to the destination, then attach both source and destination to their respective instances.

It is possible that there was something preventing the detach.
You cannot detach a database that is replicated/published.
A database snapshot exists on the database.
It is being mirrored.
It is suspect.
It is a system database.

There could have been an issue with permissions copying the MDF and LDF files (not your login, but the SQLAgent service account login).

There could have been an issue with the attach.
A database created by a more recent version of SQL Server cannot be attached in earlier versions.

For the non-existent log file:  Ensure write permissions for the account that runs SQLAgent service on the folder where the log of the error would be written.


Question about alerts in Oracle 11.2 I inherited an Oracle 11.2 server.  I wanted to see if whomever configured it before me had any alerts configured, perhaps to notify on audit events.

Logging on as sysdba, I issued this command per the examples in https://docs.oracle.com/database/121/SUTIL/GUID-8D62D6A0-99F4-465C-B088-5CCF259B7D80.htm#SUTIL1519

show alert

I got this error:

SQL> show alert
SP2-0158: unknown SHOW option "alert"

Is there any way from the command line to show what alerts on configured on the server?  Thanks!

An Oracle database can generate "Server-Generated Alerts".

You can view the alert thresholds in DBA_THRESHOLDS view, and query the alerts from the DBA_OUTSTANDING_ALERTS and DBA_ALERT_HISTORY views.

But I do not think this is what you are looking for. I have yet to see someone who uses this feature specifically and deliberately for alerting and notifications.

Usually there is Enterprise Manager (provided by Oracle) or some kind of monitoring software like Nagios, Zabbix, etc. that periodically logs in to the database, runs its checks, and sends notifications if needed. Still, some companies just simply use custom scripts scheduled in crontab for monitoring the database. Others use custom Scheduler jobs for monitoring. So no, there is no universal way to find what you are looking for.

The command you tried to execute is not a sqlplus command, it can be executed in adrci, and all it does it shows the default log of the selected location. Example:

[oracle@o71 ~]$ adrci

ADRCI: Release 12.2.0.1.0 - Production on Tue Sep 18 18:59:29 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the home from which to view the alert log:

1: diag/rdbms/etalon/ETALON
2: diag/clients/user_oracle/host_2676003343_107
3: diag/tnslsnr/o71/listener
Q: to quit


What is better? Delete and insert or update? I have a python script in which I have to update a column in 67000 rows. The value of the desired column can be unique for each row. I found that inserting 67000 rows takes about 5 seconds but updating takes around 2 minutes! Since I have to update large number of rows and there is huge speed difference I am thinking of deleting the 67k records and inserting fresh. Are there any dangers with this approach that I should be aware of?

There's a lot more to worry about with a DELETE/INSERT methodology. From the top of my head:

Transactions will need to be manually handled. An UPDATE is a single operation that is atomic. With DELETE/INSERT it's possible to have the delete succeed but the insert fail, in which case you have no data. You need to handle this manually and the engine handles the update automagically.
Foreign keys/references. If you use any data in that table or it's referred to elsewhere, the DELETE gets a lot more complicated, as does the INSERT with linking back to the correct record from the child table if you dropped the key.
Log file growth. Depending on your settings, the statement, and the table the 2 operation method could log more to the transaction log.
Indexes. It's a lot more expensive to D/I through indexes than to update. Could also lead to fragmentation in the indexes more quickly.


How to delete from two tables same field name in single query? Help me,

I want to delete same filed name from two tables in single query, I Attached query but it's not working...

DELETE PersonalTrainerMasterData ,PersonalTrainerCheckboxData
  FROM PersonalTrainerMasterData INNER JOIN PersonalTrainerCheckboxData
 WHERE PersonalTrainerMasterData.ButtonName = PersonalTrainerCheckboxData.ButtonName
   and PersonalTrainerMasterData.ButtonName='" + deleteButtonForm.getButtonToBeDeleted()
                                               + "'and TabID=1";

A DELETE only affects one table at a time and removes all rows that are defined in the criteria.  Notice the syntax description:

DELETE [table.*] FROM table WHERE criteria

You should be able to wrap two DELETE statements in a transaction. Because PersonalTrainerMasterData contains the criteria, DELETE it last. E.g.

BEGIN TRANSACTION
   DELETE * FROM PersonalTrainerCheckboxData WHERE ...
   DELETE * FROM PersonalTrainerMasterData WHERE ...
COMMIT

If needed you could also insert logic to decide whether the DELETE should be allowed to COMMIT or if it should ROLLBACK.


TSQL Bulk Insert Syntax Error I run the following code, received a syntax error.

SQL Server 2012 Express (version 11)
  OS: Windows 7 home premium (x64 bit)

The server complains that I have a syntax error near ROWTERMINATOR='\n',
I couldn't figure what is the cause for the issue.
I don't understand how to quotation mark works in bulk insert weblog from '''+@datapath+'access1.log''

My understanding is I should use double quotes to represent a single quote.

This is my code:

declare @datapath varchar(128);
set @datapath =
    'C:\Users\admin121\sql-server-2014\sql2012_class3_20141028\WebLog\';

exec
('
  bulk insert weblog from '''+@datapath+'access1.log''
    with
    (
      firstrow = 1,
      FIELDTERMINATOR ='  ',
      ROWTERMINATOR='\n',
      MAXERRORS=99999999

    )
');

You need to escape the single quotes in the parameters, like:

exec
('
bulk insert weblog from '''+@datapath+'access1.log''
with
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'',
  MAXERRORS=99999999

)
');

Use the PRINT command to see what the command contains prior to EXECing it:

DECLARE @datapath VARCHAR(260);
SET @datapath = 'C:\some\path';
PRINT
('
bulk insert weblog from ''' + @datapath + '\access1.log''
with
(
  firstrow = 1,
  FIELDTERMINATOR =''  '',
  ROWTERMINATOR=''\n'',
  MAXERRORS=99999999
)
');

This provides the following output:

bulk insert weblog from 'C:\some\path\access1.log'
with
(
  firstrow = 1,
  FIELDTERMINATOR ='  ',
  ROWTERMINATOR='\n',
  MAXERRORS=99999999
)

The query pattern you are using is referred to as "dynamic SQL" and can be very problematic, especially for beginners, due to the problems with single quotes.  Try running the following:

SELECT 'this is a string';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + @somevar + ' test';
GO

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a '' + @somevar + '' test';
GO

At first glance, it might appear the third option should print something like:

  this is a 'test' test

However, the output is:

Note the third result includes the name of the variable, instead of the content of the variable.  In order to make the result correct, we need to write the statement like:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ''' + @somevar + ''' test';
GO

Notice the + @somevar + is now wrapped with three single quotes.  This is equivalent to writing, and is indeed shorthand for, this:

DECLARE @somevar VARCHAR(50);
SET @somevar = 'test';
SELECT 'this is a ' + CHAR(39) + @somevar + CHAR(39) + ' test';
GO

Please refer to Erland Sommarskog's article on Dynamic SQL for the last and definitive word on how to use this powerful, yet sometimes confusing, feature.

Also, please be aware that dynamic SQL, although powerful and irreplaceable, can be a security nightmare through something known as SQL Injection.  Please take a look at this technet page for details about how to identify and mitigate that.  Also, you may want to search Duck Duck Go for "Little bobby tables", which takes you to this:


Can only execute mysqladmin as root account created with unix socket, all other accounts fail I have a new install of MariaDB 10.2.27 on RHEL7. Our security scans have flagged the root account, so we have modified the name to orgdba, assigning all of the applicable privileges. I have not encountered any issues, except with mysqladmin. I know I have my password correct as I can connect via mysql using it. It is only erroring on mysqladmin. I ended up creating a root account specifically to execute mysqladmin commands. I cannot execute with that account either. It is not until I create the root account using the Unix Socket plugin that I can successfully execute mysqladmin commands. I cannot find anything in any documentation that expands on this topic. I have no idea if this is a bug in the version of MariaDB that I am using. Does anyone have any insight?
Error connecting to mysqladmin:
root@db01 $ mysqladmin -uorgdba -p status
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)'

root@db01 $ mysqladmin -u orgdba -p status
Enter password:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'orgdba'@'localhost' (using password: YES)'

Same user/same password, successfully connects to mysql:
root@db01 $ mysql -u orgdba -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 52347
Server version: 10.2.27-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

Any insight on this, or additional documentation on mysqladmin, would be appreciated.

I have finally figured this out.  I have SSL enabled, which I may have not mentioned in my original post.  When I added the --ssl-ca parameter to the connection string, I was able to connect.
mysqladmin -u orgdba --ssl-ca=/etc/mysql/ssl/ca-cert.pem -p status

now returns the status as expected.
Hopefully this helps someone out in the future.


What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL? What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL?   I'm used to MySQLWorkbench for working with MySQL databases.  Now I have some sqlite databases to work with and would like to find a similar tool.

I particularly like this one which works from within your (firefox) browser. If you're not a firefox user, then here is a list of alternatives.


PostgreSQL + Citus - multi master - replication of data I have a question, I have found a Citus extension which is recommended for a replication of data between multi-master nodes (I was using Galera Cluster prior to PostgreSQL).

However I cannot find any example of how to replicate tables without creating any additional tables (distributed) as replication has to work without any modification of C# code that is using PostgreSQL as a database.

It has to work in a same way as Galera Cluster (multi-master replication between nodes).

Perhaps I've tried wrong extension, but I've tried to review the options for 3 days and seemed like a good idea.

Perhaps someone could give the recommendation for a good extension that can offer multi-master replication without any additional change in existing queries that are used by our software.

Answer compiled from part-answers originally left as comments

Citus is not a multi-master solution. It's a sharding solution. It does not replicate data. It distributes data between PostgreSQL instances. Read How Citus Works article for further information. - Sahap Asci

From Multimaster in the PostgreSQL Wiki (contributed by dezso):

  2ndQuadrant provides Bidirectional Replication for PostgreSQL (BDR). In this case updates can be performed at any node of the cluster and then propagated to other nodes. BDR is essentially asynchronous: changes are applied at nodes some time later after committing transaction at master and various ways of resolving conflicts are proposed. BDR is really fast (provides almost the same speed as hot standby), but certainly there is no global consistency in such model.


SQL Server, connecting without windows authentication? I am confused about how this works:
I was reading the SQL Server documentation on authentication here:
Choose an Authentication Mode
It says:

Windows Authentication is always available and cannot be disabled

This makes me think that ALL connections to SQL server NEED windows authentication (as it cannot be disabled), but it goes on to talk about the advantages of having only SQL Server authentication, for example:

Advantages of SQL Server Authentication

Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.

So, can you connect to SQL server without Windows Authentication or not?

Windows Authentication is always available, SQL Server Authentication is only available if Mixed Mode is enabled.

  During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode.

So:

For an instance using Windows Authentication mode, connections can only be established using Windows Authentication.

For an instance using Mixed Mode, connections may be made using either Windows Authentication or SQL Server Authentication.

In other words, Windows Authentication being always available does not mean it is always used. A company may choose to use only SQL Server Authentication, even if Windows Authentication cannot be disabled (it will remain enabled but unused).


Linked server error not caught by TRY-CATCH I am setting up a job to loop through a list of linked servers and execute a specific query against each one. I am trying to execute the query inside a TRY-CATCH block so if there's a problem with one particular server I can log it but then carry on with the other servers.

The query I'm executing inside the loop looks something like this:

BEGIN TRY
    SELECT *
    FROM OPENQUERY([server1], 'SELECT 1 AS c;');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

PRINT 'We got past the Catch block!';

If there is a problem connecting to the server the code just fails immediately and doesn't transfer to the CATCH block.  If the server connects but there is an error in the actual query, e.g. divide by zero, then this is caught as expected by the CATCH block.

For example, I created a linked server to a name that I know doesn't exist.  When executing the above I just get:

OLE DB provider "SQLNCLI" for linked server "nonserver" returned message
    "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "nonserver" returned message
    "An error has occurred while establishing a connection to the server.
    When connecting to SQL Server 2005, this failure may be caused by the
    fact that under the default settings SQL Server does not allow remote
    connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

I've read BOL on TRY-CATCH and know that it won't catch level 20+ errors that break the connection but this doesn't seem to be the case (this is only level 16).

Does anyone know why these errors aren't caught correctly?

One thing you can try is to use sp_testlinkedserver. You can also issue the OPENQUERY using dynamic SQL (as Max correctly pointed out), to defer the parser validating the server name until runtime.

BEGIN TRY
    EXEC sp_testlinkedserver N'server1';

    EXEC sp_executesql N'SELECT * FROM OPENQUERY([server1],
      ''SELECT 1 AS c;'');';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;

PRINT 'We got past the Catch block!';

While this works equally well without sp_testlinkedserver, that procedure can still be useful in preventing you from trying a whole bunch of code against that server...

Also, since if sp_testlinkedserver fails it actually fails at compile time, you can defer that and still capture it by using dynamic SQL there, too:

BEGIN TRY
  EXEC master.sys.sp_executesql N'EXEC sp_testlinkedserver N''server1'';';
  ...
END TRY


How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table? I have created two tables, the parent in this case being "department" and "employee" being child.

CREATE TABLE department
(
  department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL,
  department_name VARCHAR2(30) CONSTRAINT department_name_not_null NOT NULL,
  location_id NUMBER(4),
  manager_id NUMBER(6),
  CONSTRAINT manager_unique UNIQUE (manager_id),
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

CREATE TABLE employee
(
  employee_id NUMBER(6)  CONSTRAINT employee_id_not_null NOT NULL,
  first_name VARCHAR2(20),
  last_name VARCHAR2(25) CONSTRAINT last_name_not_null NOT NULL,
  email VARCHAR2(25) CONSTRAINT email_not_null NOT NULL,
  phone_number VARCHAR2(20),
  hire_date date CONSTRAINT hire_date_not_null NOT NULL,
  job_id VARCHAR2(10) CONSTRAINT job_id_not_null NOT NULL,
  salary NUMBER (8,2),
  commission_pct NUMBER(2,2),
  manager_id NUMBER(6),
  department_id NUMBER (4),
  CONSTRAINT manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id),
  CONSTRAINT department_fk FOREIGN KEY (department_id) REFERENCES department (department_id),
  CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);

The problem is that whenever I try to create the second table "employee" it gives me this error:

  ORA-02270: no matching unique or primary key for this column-list

I haven't been sure about how to reference the unique key in the child table so I also tried using the UNIQUE constraint instead of the FOREIGN KEY constraint but to no avail. The table is created fine without the unique key reference though.

Thanks to ypercube,

I had to drop the parent table "department", recreate it and then use ALTER TABLE employee ADD CONSTRAINT fk_manager_unique FOREIGN KEY (manager_id) REFERENCES department (manager_id); everything worked just fine and the foreign key reference to the unique manager_id key were finally created. Note that I had to change the name of the constraint "manager_unique" in the second table because of duplication issues.

Thanks again!


How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db? (Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure this out myself.)

This query gives me a rowcount of all the tables in the archive db:

query0:

SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   ARC_DB.sys.tables ta
   INNER JOIN ARC_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN ARC_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
ORDER  BY Sum(pa.rows) DESC

query1:

SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   LOB_DB.sys.tables ta
   INNER JOIN LOB_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN LOB_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
ORDER  BY Sum(pa.rows) DESC

But how can I use this to compare the ARC_DB table names and row counts to the line of business (LOB_DB) db, whose tables have much larger rowcounts? Note: I do not care about those LOB_DB tables whose ARC_DB counterparts have no rows. Could I do something like giving query0 a variable in the "from" clause, giving query1 a variable through an inner join, then comparing one variable to another? Does that make sense?

Any help is super appreciated. I am on a deadline, and will respond immediately.

;with t1 as (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   ARC_DB.sys.tables ta
   INNER JOIN ARC_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN ARC_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
   and pa.rows > 0
GROUP  BY sc.NAME,
      ta.NAME
)
select t1.tablename, t1.rowcnt, t2.rowcnt, t1.rowcnt-t2.rowcnt as diff
from t1
join (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   LOB_DB.sys.tables ta
   INNER JOIN LOB_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN LOB_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
) as t2 on t2.tablename = t1.tablename


Can't start system_health extended events session On some SQL Server instances, (I did not find a pattern yet), when I try to manually start this session, I get the following error :

For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid (Microsoft SQL Server, Error: 25641)

If we look into the SQL Server logs, each time the instance starts (i.e. after Windows reboots for patches to get applied), we get :

Error: 25641, Severity: 16, State: 0. For target,
"5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the
parameter "filename" passed is invalid. Target parameter at index 0 is
invalid
Error: 25710, Severity: 16, State: 1. Event session
"system_health" failed to start. Refer to previous errors in the
current session to identify the cause, and correct any associated
problems.
Error: 25709, Severity: 16, State: 1. One or more event
sessions failed to start. Refer to previous errors in the current
session to identify the cause, and correct any associated problems.

Has anyone observed this issue ? I suspect it's due to permissions, as the definition of the session does not indicate a path, but simply the name of the .xel file. That is, it writes directly to the SQL Server logs folder, where obviously the SQL Server service account is granted full access.
I get the same issue for the AlwaysOn_health session.
Thanks in advance for your help !

First, identify the folder that is the problem. In SSMS, with results set to grid, run the following query:

SELECT target_data = CONVERT(xml, target_data)
  FROM sys.dm_xe_session_targets
  WHERE target_package_guid = '5B2DA06D-898A-43C8-9309-39BBBE93EBBD'
  AND target_name = N'event_file';

Should return one row, and you should be able to click on it from the grid result. This will open a new tab with an XML document, which should look something like this:

<EventFileTarget truncated="0">
  <Buffers logged="0" dropped="0" />
  <File name="C:\...path_to_xe_files...\system_health_0_131278486394000000.xel" />
</EventFileTarget>

If the file name does not include a path component, the Extended Events default file location can be determined by looking in the system registry.  This code should do that:

EXEC master.sys.xp_instance_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
       , @value_name   = N'ErrorDumpDir';

Once you have the path where the Extended Events files are being saved, try to do something like this:

BACKUP DATABASE model TO DISK = 'C:\...path_to_xe_files...\model_trash.bak'
  WITH INIT;

This should give you a better idea of what the problem is - could be permissions, could be that the folder doesn't exist (maybe the configuration for the instance was moved), could be a whole slew of things. But this should point you in the right direction and get you a better error message.


How to relate Agencies at the State, County, and Local Government levels I'm having a bit of trouble deciding how to properly relate government agencies to their respective state, county, and local government levels.

I would like to be able to query for agencies that are specific to the level requested. For example, user requests all state level agencies for the state of Florida.

I would also like to be able to query for all agencies for any combination of levels requested.  For example, user requests all county level agencies specifically for Orange County AND all local level agencies (cities, towns, villages, etc) found in Orange County.

That being said, looking at my database diagram that I have so far (attached below), would "junction" tables be the way to go to properly reference agencies to their respective governments to make those queries possible?

Current database diagram:

Any help or pointers in the right direction will be greatly appreciated!

Responses to comments:

  Be cautious about assuming that the flow will always be Locality → County → State. There are cases of cities (localities in your diagram) that are not contained in any county. See https://en.wikipedia.org/wiki/Independent_city_(United_States). So you'll want to include a mechanism for having a locality belong directly to a state.

Interesting, thanks for sharing. Perhaps the easiest way to account for those scenarios of independent cities would be to allow a NULL county_id in the FK of the localities table? What do you think?

  If you use a NULL county_id, how will you connect an independent city to it's state? Could you add a state_id FK to the localities table, and allow one or the other of it and county_id to be NULL, but not both?

I should be able to. It had crossed my mind to make state_id a FK in localities along with county_id, but because I wasn't aware of the existence of independent cities in the U.S., I decided not to include it at the time. I will add that additional FK to the design, and only allow for the county_id to be NULL.

  What are those id columns for?

I made the id column in each table serve as both a PK and identity that self-increments to allow me to relate each table. I will look into using the codes provided by the ISO standard.

  Is this database meant to contain data about a one or more countries?

Only focused on U.S. currently.

Is there anything wrong with having one table for agencies and a column for the agency name and three columns for governments. State, county and locality. Assuming this is a national database, the government tables look OK.

This way if you wanted every county agency in Florida you would select where state is Florida and county_id is not null and locality_id is null or something like that. Or use blanks instead of NULL.


Update Statement in SQL I have one table named as temp_stock_reco. I have created one procedure for update two columns in that table.

CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
is
CURSOR RECEIPT IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM')Month,
sum(quantity)Receipt from transdetail
where transtype IN('STKREC','RP','RECADJ','SRET') and trunc(transdate,'mm')
between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
CURSOR ISSUE IS
select sr_cd,to_char(trunc(transdate,'mm'),'YYYYMM') Month,sum(quantity) Issue
from transdetail
where transtype IN('SINV','IP','STA','ISSADJ','PURRET')
and trunc(transdate,'mm') between '01-April-2013' and '31-March-2014'
group by trunc(transdate,'mm'),sr_cd;
BEGIN
OPEN RECEIPT;
LOOP
UPDATE temp_stock_reco SET RECEIPT = I.RECEIPT
WHERE SR_CD = I.SR_CD
AND YYYYMM = I.MONTH;
OPEN ISSUE;
LOOP
UPDATE temp_stock_reco SET ISSUE = J.ISSUE
WHERE SR_CD = J.SR_CD
AND YYYYMM = J.MONTH;
END LOOP;
CLOSE ISSUE;
END LOOP;
CLOSE RECEIPT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||'
-ERROR- '||SQLERRM);
END;

i want to reduce this one a single by using decode function.Kindly help me to do this one

You need to avoid catching and reraising errors with a user defined error number. That will only make it harder to see where the error is happening. You should also avoid using explicit cursors and try using PL/SQL collections. As long as you are using 9i or above PL/SQL collections are the better way to go. You should probably have an index on transdetail as follows:

CREATE UNIQUE INDEX transdetail_idx1
  ON transdetail ( transtype, transdate, sr_cd, quantity);

This will greatly reduce the number of rows that Oracle needs to look at. It will do a range scan of the transdate column for each of the transtype values that you are searching for, plus the index has all of the columns that you want. Hence Oracle won't need to look at the index, then look at the table.

-- Let unhandled exceptions remain unhandled
-- EXCEPTION
-- This is a terrible example of error handling, don't use it
-- WHEN OTHERS THEN
-- raise_application_error(-20001,'An error was encountered - '||SQLCODE||'
-- -ERROR- '||SQLERRM);
-- END;
-- /

CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
AS
   TYPE transdetail_rec IS RECORD (
      sr_cd     transdetail.sr_cd%TYPE,
      transdate transdetail.transdate%TYPE,
      quantity  transdetail.quantity%TYPE );

   TYPE transdetail_type IS TABLE OF transdetail_rec
      INDEX BY PLS_INTEGER;

   transdetail_tab transdetail_type;
BEGIN
   SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month, sum(quantity)Receipt
     BULK COLLECT INTO transdetail_tab
     FROM transdetail
    WHERE transtype IN('STKREC','RP','RECADJ','SRET')
      AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
    GROUP BY trunc(transdate,'mm'),sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

    SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month,  sum(quantity) Issue
      FROM transdetail
     WHERE transtype IN('SINV','IP','STA','ISSADJ','PURRET')
       AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
     GROUP BY trunc(transdate,'mm'), sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

   COMMIT;
END;
/


Can CPU / memory influence restore performance I want to restore a database and it takes minutes.

I am using a virtual machine with 4 CPUs and 16GB of memory and I want to know if there is any relation between the CPU/Memory to the restore DB process?

For example: if I increase CPUs to 16 and memory to 48GB, should it be faster?

Can CPU / memory influence restore performance

Restoring involves bringing data pages into memory so yes memory can be a factor but cannot be a show stopper. Things which really can affect backup performance are the drives on which data file and backup resides and the network connectivity. Ofcourse you will see faster restore speed on SSD's as compared to SATA disks.

Read How SQL Server backup and restore operation works. This will let you know how memory is internally used while backup and restore operation

If you read this BOL document it shows how you can improve performance of SQL Server restore process


MySQL: What is SQL profiler, does it different for MySQL? Whenever SQL profiling comes in mind, focus goes to two below sections:

GUI tools like - MONyog, Neor Profile SQL
set profiling =1; Execute Query; show profiles; show profile;

I am really confuse between these two about SQL profiling.

Which one is correct or it is something other than these two?
SQL profiling in MySQL different than SQL profiling in common RDBMS?
How SQL profiling is useful and why we do we use it?

The whole point about profiling is to ascertain where your system is spending time. This can often give developers some clues as to how to optimise their queries (by adding indexes for example).

The 600 pound gorilla in this area is Oracle's Wait Interface (OWI) which is one of the most sophisticated monitoring and profiling tools available to database professionals. MySQL has made strides in the last couple of years with the Perfomance Schema (commonly written P_S).

Again, the P_S allows the DBA/Developer to see what files are being accessed - and other information, i.e. where the system is spending time - knowing this can be very helpful for tuning or hardware purchasing decisions.

From here, MySQL's SHOW PROFILE command is now being deprecated in favour of the P_S.

I don't know the Neor Profile SQL 2 tool, but it looks interesting, although not Open Source :-(. There's also MySQL Workbench or Percona's tools. It's important to remember that none of these tools is a "silver bullet" - they just use the same information that's available in the P_S - they might present it nicely, but you're better off learning the P_S than a particular tool.

As for MySQL being different to Oracle (and to PostgreSQL, or Microsoft SQL Server, IBM DB2 or Firebird), then yes, there are differences between them that arise from the product's history but they all try and give some information to DBAs and Developers as to how the server is performing your query. Some more than others - Oracle's is very well developed, MySQL getting better (unsure of status of others). For the principles check out either Garcia-Molina or Lahdenmaki and Leach. You can then go to books on the particular server you are interested in/have to work with.


exporting some table table from oracle DB to Mysql DB I need to export some of the tables from Oracle DB to Mysql DB..i dont have any previous experience of export and Import. Could some please tell me how to do that ?

Oracle export tools use their own proprietary format that MySQL can not process.

Just unload your data to CSV or insert statements with SQL Developer, then load the result in MySQL

https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer


Incomplete MysqlDump Files - Mysqldump Inside For Loop I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below.

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; done;

The Problem is I am only getting incomplete dump files for all databases except the last one. The last dump is complete.

I have tried gzip the sql file by the following command

for DB in $(mysql -uroot -proot -e 'show databases'); do mysqldump -uroot -proot --complete-insert $DB > "$DB".sql; [[ $? -eq 0 ]] && gzip "$DB".sql; done;

Got the same result. Only the last database is getting dumped and gzipped completely.

Note: It seems the next iteration happens before the previous finishes. I have tried using wait and sleep inside but nothing works. I have also tried using mysqldump options --single-transaction, --force, --quick but none seem to work.

I have created a batchfile which runs under Windows.
It will create the .sql files in the current directory.
I left out Zipping the output.

The parameter '-N' will delete the header in the output from "show databases".

@echo off

set mysql=C:\Program Files\MySQL\MySQL Server 8.0\bin\
set mysql=c:\progra~1\mysql\mysqls~2.0\bin\
set password=********

FOR /F "usebackq" %%F IN (`%mysql%mysql -N -uroot -p%password% -e "show databases"`) do (
    ECHO %%F
    "%mysql%mysqldump" -u root -p%password% %%F >%%F.sql
)

I only got 1 error running this script (besides the warnings... ):

performance_schema
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES


Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application? The Situation:

I have a desktop application that currently uses a database which is stored locally on the computer that is running the application, and I am starting to implement a server DB so that some of the information in this application can be shared between users on different machines. The local DB schema was not designed to support RLS, or anything close to it. I'm now trying to design a schema that holds the same data, but restricts access to it based on a users 'role' or 'group', etc.

My Understanding:

I would ideally like to use PostgreSQL, but others are fine, and saw that they have RLS support, but I'm unsure how to proceed. I'm thinking of using something like a RL_Groups and Users table, with a junction table like RL_Group_Users to determine row level access instead. I'm not sure of performance / security implications of either option, or even the alternative options. I've attached a simplified diagram to give some context to my current thoughts.

Supporting Info:

The local database is SQLite 3.
There are roughly 100 tables at the moment.
These 100 tables will all need some kind of RLS to restrict information to users and user_groups.

If I missed anything or you have any questions please comment so I can get back to you.
NOTE: The attached diagram has been simplified, data has been removed, etc. but it should be plenty to illustrate the problem.

I believe you're problem is similar to one I've faced a few years ago, while trying to design a multi-tenant web application with a single underlying database schema.
There's an interesting article by AWS that can point you in the right direction.
The core of it is:
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id::TEXT = current_user);

There are several different approaches, but the one I ended following is this:

Use a different database user for each connection coming from your desktop PCs. This should match your RLGroupId, if I understood correctly.
Apply a Policy to every table where data segregation by RLGroupId should be enforced

Hope this helps.


How can I filter my query on a left joined table, if I don't know the exact value I'm searching? I had a hard time coming up with an appropriate title and it might be hard for me to explain the issue, but I'll try to be as clear as possible.

I have a dynamic query that is pulling assets based on their asset title or one of many "identifiers" that are attached to that asset. Every asset as a "snapshot" ID that is tied to a asset identifier snapshot table, that is then tied to a version table that holds the actual value. That relationship looks like this:

SELECT
    *
FROM
    vwAsset AS Asset
        INNER JOIN dbo.tblAssetIdentifierSnapshot ON tblAssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
            INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = tblAssetIdentifierSnapshot.AssetIdentifierVersionId

The value in the tblAssetIdentifierVersion table is what I'm trying to get at. Here is what the full query looks like:

And here's a pastebin.

If I run this query without the two highlighted joins and remove the OR, it runs instantly. But adding these joins makes the query run for 25+ seconds.

When I run this through Sentry Plan Explorer, it shows that some 274K+ rows are being searched for each join, before it finally filters down to the 137 it returns.

This is the query columns that sentry plan explorer shows:

I'm not an expert, so take this with a grain of salt, but every looks mostly okay. I don't think anything can be done about index scans, right?

Is there any way to filter down the left joins more without knowing the snapshot ID or the versions that are tied to the asset?

Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=HJtsYtGLe

I don't see any way in the immediate query to assist performance. You have indices on the requested columns and it has to do a full scan to figure out all of the data.

There are two solutions I see, both take architecture changes.

Full Text Search
Processing and mapping tables with foreign key implementation.

If you are able to create a mapping table and a key column on the tblAssetIdentifierVersion and tblAssetIdentifierSnapshot table, you could have a process that runs that helps map to the table. This causes processing time that would typically be done in the select to be done in the update as it does not resolve the like operator in the where clause. You'll have to weigh whether this is a good option for your database or Full Text Search is a better idea or if it's best to be left alone.

Example of the mapping table:

DECLARE @m_people TABLE
    (
        M_Job_ID INT IDENTITY(1 , 1)
        ,M_JOB VARCHAR(255)
    )

DECLARE @people TABLE
    (
        Name VARCHAR(255)
        ,JOB VARCHAR(255)
        ,Job_ID INT
    )

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Timmy' , 'Data Slave' , NULL)

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Joann' , 'Manager' , NULL)

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Data Slave')

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Manager')

UPDATE  @people
SET Job_ID = M_job_id
FROM @m_people
WHERE JOB LIKE '%' + m_job + '%'

SELECT * FROM @people
SELECT * FROM @m_people

Note: Due to variable tables, the objects due not exist in the sys.objects table so there is no obvious foreign key constraint declares. The FK would be Job_ID and M_Job_ID if that is not obvious.


How can I model direct ownership alongside group ownership? I want to model the following:

Companies sell their own Products.
Sometimes, Companies will package multiple Products into a Bundle, and sell those as well.
A Company may only sell their own Products and Bundles.

I feel like it should be possible to model this just using foreign keys but I can't work it out. The possibilities I have considered, do not properly ensure data integrity.

I don't want to rely on something that will only work in a single kind of database, but could use something Oracle specific as a last resort.

Failed Attempt #1

products
  id

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)
  company_id REFERENCES companies(id)

companies
  id

This doesn't allow individual products to be related to a company. They are only related when they are part of a bundle, but they may not necessarily be part of any bundle.

Failed Attempt #2

products
  id

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

This is the exact same problem as #1.

Failed Attempt #3

products
  id
  company_id REFERENCES companies(id)

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id
  bundle_id

companies
  id

In this model, it is possible to put a product for one company, into a bundle owned by a different company.

Failed Attempt #4

products
  id
  company_id REFERENCES companies(id)

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

In this model, the ownership of a bundle could be derived from the products in the bundle. However, there is nothing to prevent products owned by different companies being put into the same bundle which breaks this possibility.

Failed Attempt #5

products
  id
  company_id REFERENCES companies(id)

  UNIQUE (id, company_id)

bundles
  id

products_bundles
  bundle_id REFERENCES bundles(id)
  product_id
  company_id

  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)

companies
  id

This is better but I'm slightly uncomfortable about the need for the the composite foreign key. Much of what I have read suggests that a composite foreign key is a sign of the data not being fully normalised but I'm not sure if that is true here and, if it is, how to improve it further.

This also doesn't allow me to enforce a company-wide unique naming scheme for bundles (although I can do that for products).

Possible Attempt #6

products
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

bundles
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

products_bundles
  bundle_id
  product_id
  company_id
  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)
  FOREIGN KEY (bundle_id, company_id) REFERENCES bundles(id, company_id)

companies
  id

I guess this might be the right solution. Are these composite foreign keys the right way to go?

A simple way is to make Companies sell only Bundles.
A Bundle can contain one or more Products
A Bundle has a flag that enforces "single product" and/or handling of it

Schema:

company
  company_id (PK)
  ...

bundle
  bundle_id (PK)
  company_id REFERENCES company(company_id)
  is_single_product
  ...
  UNIQUE (bundle_id, company_id)

product
  product_id (PK)
  company_id REFERENCES company(company_id)
  UNIQUE (product_id, company_id)
  ...

product_bundle
  bundle_id REFERENCES bundle(bundle_id, company_id)
  product_id REFERENCES product(product_id, company_id)
  company_id REFERENCES company(company_id)
  PK (bundle_id , product_id )
  ...

Logic to enforce (trigger, view, whatever)

  NOT "count(*) > 1 FROM product_bundle JOIN bundle where is_single_product"

Variation which allows FKs only is_single_product to restricted_product_id

bundle
  bundle_id
  company_id REFERENCES company(company_id)
  restricted_product_id REFERENCES product(product_id) (nullable)

Then the logic is "no rows in product_bundle if restricted_product_id is NOT NULL"

Edit, added company_id to bundle and products with super keys to push down "unique company per product"


Search for the root words using root words with prefixes or suffixes Is it possible to search the word "FILL" saved in the SQLite database using the words "REFILL" or "FILLED"? Not just these words specifically but you get the idea of searching the root word using the same word but with prefixes or suffixes.

I might get misunderstood as wanting to use the LIKE operator/command. But what I need is somehow reverse of that. The case is that the root words (FILL) are the ones in my database and the words that I am going to use in the query are those with prefixes or suffixes (e.g REFILL, FILLED)

I cannot use those word-trimming API because it's not going to be done in English. A code for android/Sqlite would be great but the query command is already a great help. Thaanks.

What you're talking about is "stemming" where a word -- refill -- is related to its "root" or "stem" word -- fill. This is usually implemented through Full Text Search, which I believe SQLite supports, though I've never used it.

To implement this yourself in your own tables and code will be a complex task requiring a thorough understanding of the target language(s), how word-forms are constructed and how the grammar is defined, both theoretically and in practice.


Really Small details about storage ( data size ) on columns I'm doing tests to better understand those minimal technical details about tables, pages, and etc.

I'm using sp_spaceused to better understand this with INT values ( I will do a lot of more tests with other datatypes, But I think the logic behind each datatype is the same).

OK. First I created a simple table with id int.

create table Aliens( id int)

Inserting only 1 value (Insert into table value (1)), I have this:

| name | rows | reserved | data | index_size | Unused |
|------|------|----------|------|------------|--------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       | 0 KB   |

OK. I can understand that we have here USED 8KB because this is the size of a page. correct? But why does SQL server reserves 16KB for this?

Then, I delete this row, and the result is the same:

| name | rows | reserved | data | index_size |
|------|------|----------|------|------------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       |

This is because I didn't rebuild the table. After rebuilding the table:

| name | rows | reserved | data | index_size | unused |
|------|------|----------|------|------------|--------|
| id   | 0    | 72 KB    | 8 KB | 8 KB       | 56 KB  |

Why now I have more reserved ( 72KB )? I deleted data and rebuilded. Now, is my table bigger? Is this because of log? and why is unused 56KB? 72-56 is 16. Is this because DATA and INDEX are separated? my table has no PK, nor indexes.

Now, when I insert 286 values ( all 1's, with go 286):

| name | rows | reserved | data  | index_size | unused |
|------|------|----------|-------|------------|--------|
| id   | 286  | 24 KB    | 16 KB | 8 KB       | 0 KB   |

Now I can see data = 16kb because There's so much information, that the SQL is now using 2 pages. But why index_size is still 8kb?

and How about NULL VALUES? according to This question here, null values on int field takes 4 bytes. a Char(10) takes 10 bytes. So, null values store the same amount as the datatype itself? Unless we are using sparse columns.

Thanks. any additional information would be appreciated.I think it's time to improve my knowledge with this details.

What you want to read up on is how SQL Server allocates pages (try this). Looking at data size in a row will take different tools.

Here's sample test code for a later version of SQL Server (I ran this on 2016)

USE [TestDB]
GO

DROP TABLE IF EXISTS dbo.Aliens
CREATE TABLE dbo.Aliens(ID INT)
INSERT dbo.Aliens VALUES (1)

SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')

EXEC sp_spaceused 'dbo.Aliens'

This shows an IAM page and a data page for the table, and bunch of blank pages. Why the blank pages? SQL Server reserves an entire extent for the table (because I'm using 2016 - earlier versions behave differently), expecting to use those blank pages for later inserts. In 2008r2, your table starts with a single page in a mixed extent, but the rebuild pushes it to a uniform extent.

Here's code to use if you want to look at rows

DECLARE @pageid BIGINT,
@file BIGINT,
@db INT = (SELECT DB_ID())

SELECT TOP 1 @pageid = allocated_page_page_id, @file = allocated_page_file_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')
WHERE page_type = 1

DBCC TRACEON(3604)

DBCC PAGE(@db,@file,@pageid,3)


Where to store administrative (DBA) data? I am a SQL Server DBA by trade, investigating MongoDB for a project.  One of the conventions we use is to store configuration-style information in a SQLDBA database on each server (index rebuild thresholds, backup exceptions, etc).

I am wondering if this same technique would be useful here. My question is:

Should/could I use the admin or local database for this information, or should I create a new database to store essentially a single collection and document?
Would this change in a sharded/replica-set environment?

We have a central hub SQL server/database for sure, but we also store some configuration data locally on each server to drive certain processes.  We wouldn't want these to fail because a server couldn't contact the hub.

I am wondering if this same technique would be useful here.

Since MongoDB is a distributed database, nodes tend to be part of a larger deployment (replica set or sharded cluster) where configuration differences in individual nodes are usually managed externally. For example, using Configuration Management software like Chef or Puppet to generate mongod and mongos configuration files.

  Should/could I use the admin or local database for this information?

Each mongod node has a local database which is used for storing node-specific administrative or logging data such as the startup_log (a capped collection recording history of startup parameters and versions) and oplog.rs (the replication oplog).

You could store node-specific information in the local database, but I think it would make more sense to use a normal collection in your deployment in order to coordinate maintenance tasks and availability. Aside from backup, there aren't many node-specific maintenance tasks that should be performed without manual intervention.

  or should I create a new database to store essentially a single collection and document?

If you do find the need for node-specific tasks I imagine your maintenance database would have more than a single collection and document. For example, you might have a configuration document per node and perhaps other collections such as a history of maintenance tasks. Capped collections can be useful for storing a rolling snapshot of "recent" activity using a preset storage allocation.

  Would this change in a sharded/replica-set environment?

Assuming the details are node-specific, there would be no change in a sharded or replica set environment if you are accessing data in the local database. The contents of the local database are excluded from replication/sharding and you should only access the local database by connecting directly to a mongod node.


Are SQL unit tests supposed to be so long? I am writing stored procedures with some non-trivial business logic. I am trying to unit test them, but the actual tests end up being quite long (shorter ones starting at 40-50 LoCs, using commonly 4 different tables), which doesn't seem very "unit". (Admittedly, I format my code in a way where it takes a lot of space.)
In context of "normal" programming languages I've heard the advice to refactor the complex procedure into smaller chunks. But I don't want to do that here because:

I don't want to pollute  "global namespace" by small routines called from one place only.
Passing around tables from and to stored procedures is cumbersome.
Custom functions can have negative effects on performance.

Am I wrong about this reasoning?
I am new to unit testing, so perhaps I am just writing my tests wrong?
Is SQL longwinded language and thus it's unit tests are longer as well?

(I am using SQL Server with tSQLt framework, but I believe the question is system-agnostic.)

Am I wrong about this reasoning? I am new to unit testing, so perhaps I am just writing my tests wrong? Is SQL longwinded language and thus it's unit tests are longer as well?

I think this is a great (though somewhat opinion-based) question. The past few years I've attempted to implement tSQLt unit testing in a personal OSS project (https://dba-multitool.org/) to attempt to answer it via real world application. Unit testing for any type of SQL definitely breaks from classic norms and patterns. My tests definitely don't feel like unit tests and are usually more complex/longer than I'd prefer, but they have saved me a bunch of times from introducing breaking changes.
The stored procedures in my example are quite complex and long, so I came to a similar impasse as you: should I suck it up and deal with very complex "unit" tests, or fundamentally modify how the stored procedure works so that it is more amicable to unit testing in general? As with many database questions, the ultimate answer, I believe, is it depends. Performance and maintainability take a front seat here, in my experience.
I found that I wasn't able to justify largely changing the complex stored procedures to benefit from unit testing - adding more objects, as you noted, can increase complexity and in this case I was wary of asking end users to house a bunch of single-use objects in their database instead of offering an all-in-one utility stored procedure.
One technique that I did come up with was using optional parameters to induce test cases better. For example, I have parameters @IsExpress, @SqlMajorVersion, and @SqlMinorVersion that end users should not use (they are fetched at runtime if not passed) but that my tests utilize to make sure I'm catching errors I want around unsupported versions. Adding a lot of errors will increase the overall reliability of the code without having to necessarily explicitly test the more complex logic areas that may be hard/not reasonable to do. This marginally affects the stored procedure's length and complexity, but lets me easily isolate functionality and reliably test for it. You could take this further and use parameters to trigger select portions of the stored procedure that need testing the most, or to achieve better code coverage percentages by avoiding a messy area that isn't worth covering or is out of scope.
With this approach I was able to get a fairly high code coverage percentage (98%+) and while I definitely don't test all functionality, I know that almost all of the code gets executed, errors when I need it to, and will parse on execution.
To close with a quote, Wikipedia has unit testing defined as (emphasis mine):

In computer programming, unit testing is a software testing method by which individual units of source code—sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures—are tested to determine whether they are fit for use.

I won't say that Wikipedia (or the book being quoted by Wikipedia) is the end-all for technical definitions, but I do like this definition since it focuses on the end goal of unit testing, which I think tSQLt can reasonably achieve.
Besides, some unit tests are better than none and having lines of code that aren't covered by unit testing defined may be very valuable in the future. To expand on the latter:
In 3 years, you've left the company and Employee #948 is trying to address a bug in the stored procedure. They look and see that only lines 50-65 out of 300 aren't covered by a unit test, otherwise all other functionality has been tested. They now have a pretty good shot at spending time on a likely problematic area rather than reverse engineering the entire 300 lines. Or, the next intern/new hire can be asked to fill in missing unit test coverage gaps as a learning exercise. Knowing what you aren't testing yet can help prioritize future work and diagnose problems faster.


Accidental DBA: Unsure why I have a broken log chain? First of all, thanks for having a look at this - We've got a large database in our production environment (1.26 TB), it's got a few hundred corrupt pages in it and has done for months, so the same corruption is in all of the backups available.

I got dragged into this late last week as it seems the scheduled jobs for reorganizing indexes have been failing for some time due to the corruption and we are now at the stage where the indexes on the largest and most commonly used tables range between 50% and 80% fragmentation which is seriously degrading application performance.

I've entertained a number of ideas on how I could remedy this situation (believe me, I'm more than open to alternatives) and from what I've read I think the following sounds like a good idea:

Take a compressed backup of the corrupt database from production (SQL Server 2008R2), copy down to our (local) development server (SQL Server 2016) and run the repair (there isn't enough space on the production system to have two copies of the corrupt db attached concurrently).
Restore two copies of this corrupt backup onto the development server where we do have enough space.
Run the repair down on the development server with the allow_data_loss option for one of these dbs, rename to "DbNameHereRepaired" (most of the corrupt pages that I looked at with DBCC PAGE appeared to have had their data all set to 0x00 anyway).
Rename other copy to DbNameHereCorrupt, attempt to run the page level restore using the following code:

    alter database DbNameHereCorrupt set single_user with rollback immediate

    --set db to FULL recovery mode
    alter database DbNameHereCorrupt set recovery full

    --Declare paths for backups
    declare @fullBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-FullBackup.bck'
    declare @tranLogBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-LogBackup.bck'

    --Take full backup to begin new TLogChain
    backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

    --Immediately after whilst in single user mode, begin the t-log chain, this will also put the db in a restoring state
    backup log DbNameHereCorrupt to disk=@tranLogBackupPath with init, norecovery;

    --get corrupted pages
    declare @corruptedPages nvarchar(max) =
    (
        select stuff
        (
            (
                select ',' + cast(s.file_id as nvarchar(20)) + ':' + cast(s.page_id as nvarchar(20))
                from msdb.dbo.suspect_pages s

                where s.database_id = 20

                for xml path('')
            ), 1 ,1, ''
        )
    )

    --push page-level restore
    restore database DbNameHereCorrupt
    page=@corruptedPages
    from disk=N'D:\Restore\DbNameHereRepaired.bak'
    with norecovery;

    -- restore log over db
    restore log DbNameHereCorrupt from disk=@tranLogBackupPath
    with norecovery;

    -- put db back into usable state
    restore database DbNameHereCorrupt
    with recovery

    --set db to SIMPLE recovery mode
    alter database DbNameHereCorrupt set recovery simple

    alter database DbNameHereCorrupt set multi_user

This appears to work how I think it's supposed to up until the page-level restore statement, where it errors out saying:

  Msg 4346, Level 16, State 1, Line 35
  RESTORE PAGE is not allowed with databases that use the simple recovery model or have broken the log backup chain.

This is my first time using the full recovery model, so I've probably done something wrong...I can see what looks like a small gap between the last lsn of the differential and the transaction backup, which I'm guessing that there shouldnt be.

Can anyone see what I've done wrong here? I've tried to follow examples on the web that all seem to follow a similar pattern, but I guess I'm missing something?

Your command for full backup

  --Take full backup to begin new TLogChain

  backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

does not make full backup.

  DIFFERENTIAL

  Used only with BACKUP DATABASE, specifies that the database or file
  backup should consist only of the portions of the database or file
  changed since the last full backup.

As @dbamex mentioned full and restore commands use different file names.


innodb_table_stats length mismatch This is very strange. I just copied all the binary files from one server to another. The new server comes up just fine with one exception. I keep getting this error message in my log every couple minutes...

2018-09-06T01:27:56.710923Z 284261 [ERROR] InnoDB: Column table_name in table `mysql`.`innodb_table_stats` is VARCHAR(576) NOT NULL but should be VARCHAR(192) NOT NULL (length mismatch).
2018-09-06T01:27:56.711052Z 284261 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mydatabase`.`mytable` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Now I know for a fact that the error is bogus. I have used ALTER TABLE to change the field size all the way down to 64 and the exact same error shows. So i dropped the table and I get the error that the table is missing as expected. Then I create the table with the proper length and this error comes back. It always says the field is 576 characters no matter what the actual size is. And it is only this one field and one table. None of the other innodb_ tables are coming up with an error. And running a database check says all the tables are correct but the log loads up with this error as each table gets checked.

For the life of me, I can't think of anything else to do. Any help out there?

MySQL version: 5.7.22

Script used to drop and rebuild the table run one line at a time checking the log each time.

drop TABLE if exists `innodb_table_stats`;
CREATE TABLE if not exists `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(192) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
flush tables;

I am not 100% sure what solved the issue because I ended up writing a script to do the following and when it finished (unattended) several hours later, the errors stopped. I am betting it was the mysqlcheck command but only in conjunction with rebuilding every index on every table. Not sure which one actually was the issue.

My script did the following. (not posting the code because it is ugly and quick and dirty)

1) Alter every table only setting the engine to innodb (which they already were but this forces a rebuild)
2) ran mysqlcheck on each database one at a time

I logged all of this to a file but no errors were detected. Everything said it was OK.

So with everything saying it was OK that would make me think nothing was ever wrong but it was and it no longer is so this was just some bit of confusion in some supporting file.

Bogus error with a bogus fix but it actually worked so I don't care any more.


How do I Order By on multiple rows in Postgresql I have a table with columns with severities like:
name   | critical | major | trivial
----------------------------------------
Apache |2         |3      |2
Eclipse|5         |0      |4
TestNg |0         |0      |5
JUnit  |0         |2      |7

I want to list the rows in the order by their severities in descending order - critical, major and trivial.
For the above example, I'd like to get back:
name  | critical | major | trivial
----------------------------------------
Eclipse|5         |0      |4
Apache |2         |3      |2
JUnit  |0         |2      |7
TestNg |0         |0      |5

critical has highest severity, then comes major and then trivial. Notice eclipse has to come first since 5 > 2 (Critical, internal sorting)

Simply order them  with the right orde4r with DESC for descending
Schema (PostgreSQL v9.6)
CREATE TABLE test (
  "name" VARCHAR(7),
  "critical" INTEGER,
  "major" INTEGER,
  "trivial" INTEGER
);

INSERT INTO test
  ("name", "critical", "major", "trivial")
VALUES
  ('Apache', '2', '3', '2'),
  ('Eclipse', '5', '0', '4'),
  ('TestNg', '0', '0', '5'),
  ('JUnit', '0', '2', '7');

Query #1
SELECT * FROM test
ORDER BY "critical" DESC, "major" DESC, "trivial" DESC;

| name    | critical | major | trivial |
| ------- | -------- | ----- | ------- |
| Eclipse | 5        | 0     | 4       |
| Apache  | 2        | 3     | 2       |
| JUnit   | 0        | 2     | 7       |
| TestNg  | 0        | 0     | 5       |

View on DB Fiddle


MySql Condition not working in trigger I am developing a social networking website, where I have 3 tables - 1st for users(userid- pk,auto increment,username,etc.), 2nd for Album(albumid - auto increment,userid - of person who owns the album) and 3rd for photo(photoid - auto increment,albumid -where photo belongs to,path- to photo in disk). Now, Before inserting into photo, my trigger checks if the user owns the album given by album id. The trigger is created using MySql workbench which gives the following:

USE `mydb`;
DELIMITER $$
CREATE TRIGGER `photo_BINS` BEFORE INSERT ON `photo` FOR EACH ROW
BEGIN
    DECLARE found_it INT;

    SELECT 1 INTO found_it FROM album WHERE albumid= NEW.albumid and userid=@userid;
    IF found_it!=1 then
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User does not own album';
END IF;
END

I am passing the value to @userid by php as follows:-

$con=NULL;$stmt=NULL;
$con=mysqli_connect('localhost','username','pswd','mydb');
$stmt= mysqli_prepare($con,'set @userid=?');
mysqli_stmt_bind_param($stmt,'i',$user);
mysqli_stmt_execute($stmt);

$stmt= mysqli_prepare($con,'insert into photo(albumid,path) values(?,?)');
mysqli_stmt_bind_param($stmt,'is',$albumid,$path);
mysqli_stmt_execute($stmt)'
$pid=mysqli_insert_id($con);

The problem is that everytime the value gets entered. Even when I oppose the condtion i.e. fount_it=1 , the insert always takes place. Other variant I've tried is:

if(SELECT 1 FROM album WHERE albumid= NEW.albumid and userid=@userid)!=1 THEN

... still the insert always takes place. Please help.

Found_it is null when declared. If no row matches the query that looks for album and userid found_it is not updated, i.e. it is still null. The test

found_it!=1 (use found_it <> 1 instead)

therefor evaluates to NULL ( NULL <> 1 => NULL ). You can prevent this by a default constraint in your declaration:

DECLARE found_it INT DEFAULT 0;

Now the test will evaluate to FALSE.


Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL I am researching migration of a major system from Oracle to PostgreSQL.  Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically within a Function I can create a TYPE as an array.

The syntax in Oracle is:

TYPE VAR_STRING IS VARRAY(10) VARCHAR2(30);

This creates a 10 element array TYPE named VAR_STRING to hold 10 strings of 30 characters.

I am playing with PG 9.6, and even in the Create Type dialog, it is not obvious to me to make a pre-defined TYPE that can be an array.

You can just use varchar(30)[]. Example:

DO
$$
DECLARE
   var_string varchar(30)[] := '{a,b,c,d,e,f,g,h,i,j}';
BEGIN
   RAISE NOTICE '%', var_string;
END
$$;

(Assigning at declaration time is optional; defaults to NULL.)
(Lower case identifiers are advisable for Postgres - in contrast to Oracle.)

The manual on array declaration.

Of course, the number of elements is not limited to 10 for the basic type. Do you even need that? And do you need to enforce a maximum string length of 30? Typically a plain text array text[] should do the job.

You can specify the size of the array:

varchar(30)[10]

But that's not effective beyond documentation. The manual:

  However, the current implementation ignores any supplied array size
  limits, i.e., the behavior is the same as for arrays of unspecified length.

To positively enforce a maximum of 10 elements, you could create a DOMAIN over the basic type with a CHECK constraint.

CREATE DOMAIN varchar30_arr_max_10_elem AS varchar(30)[]
CHECK(cardinality(VALUE) <= 10);

Then use varchar30_arr_max_10_elem like any other type.

Related:

Issues with PL/pgSQL code

Aside: Why use the name var_string for an array? It's not a string.