Find Uncompressed/Compressed Sizes of TimescaleDB Database/Table For a database/table in TimescaleDB and PostgreSQL 11 running on ZFS with both ZFS compression and native TimescaleDB compression enabled, how can you find the uncompressed and compressed sizes when

using native TimescaleDB compression and ZFS compression
using native TimescaleDB compression but not ZFS compression
not using native TimescaleDB compression and using ZFS compression
not using native TimescaleDB compression and not using ZFS compression

I want to use these values to find out the compress ratio due to TimescaleDB native compression and due to ZFS

If you want to find out savings from TimescaleDB's native compression, use this information view for the hypertable: https://docs.timescale.com/latest/api#timescaledb_information-compressed_chunk_stats

In the field, we're typically seeing seeing 90-98% storing savings across a large numbers of users.

For measuring ZFS, look at the numbers returned by zfs get compression and zfs get compressratio (see, e.g., https://www.servethehome.com/the-case-for-using-zfs-compression/)

If you are running native compression on top, you'll see that this is the amount of compression that ZFS adds on top of TimescaleDB's native compression.  Would love to hear what you see; would expect that to actually be pretty minimal (or even negative).


Creating a View with results from join of scalar tables I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help!

So, here's my objective : I need to show the trending graph for the last 12 months and I need to generate a view out of my existing table having two columns : Month+Year and Count. So it will be like "Jul-2014 97" ish records.

I tried the following approach where I am first creating a scalar table with last 12 months Month+Year combination like "Jul-2014", etc. Second I am creating another scalar table with "Month+Year" and "Count" combination like "Jul-2014 97". The thing is the second table will not have data for all the last 12 months. So I am doing a LEFT OUTER JOIN to get all Month+Year data.

My query is as follows:

DECLARE @MonthYears TABLE (LNo INT IDENTITY(1,1), Name Varchar(50))
DECLARE @MonthYearsData TABLE (Yr INT, Mon INT, MonYr varchar(50), CrCount INT)
SET LANGUAGE English;
WITH R(N) AS
(
SELECT 0
UNION ALL
SELECT N+1
FROM R
WHERE N < 12
)
INSERT INTO @MonthYears (Name)
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' +
       CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Last12Mon
FROM R

INSERT INTO @MonthYearsData(Yr, Mon, MonYr, CrCount)
SELECT
YEAR(RequestedOn),
MONTH(RequestedOn),
SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr,
count(*)
FROM TempTableTest
WHERE RequestedOn >= DATEADD(MONTH, -13, GETDATE())
GROUP BY YEAR(RequestedOn), MONTH(RequestedOn)

SELECT M.Name, ISNULL(N.CrCount,0) as CRCount
FROM @MonthYears M left outer join @MonthYearsData N ON M.Name = N.MonYr
GROUP BY M.Name, N.CrCount, M.LNo
ORDER BY M.LNo DESC

So, I am getting response as follows:

Name      CRCount
Dec-2013    0
Jan-2014    0
Feb-2014    0
Mar-2014    0
Apr-2014    0
May-2014    0
Jun-2014    0
Jul-2014    0
Aug-2014    1
Sep-2014    0
Oct-2014    0
Nov-2014    0
Dec-2014    2

Now I need this in a View, so I can use it against my reporting tool. However, I am unable to create one using this scalar table approach. Even temporary table fails. I am really in a deadlock. Can someone please point out an approach?

P.S. : I tried dynamically creating the scalar tables inside the last SELECT query, but haven't had any luck.

Thanks in advance!

Did you try chaining your WITH statements together?

N.B. This may not be the most-optimal method of solving this problem, but should work based on your existing approach

CREATE VIEW dbo.MyViewName
AS
WITH R(N) AS
(
   SELECT 0
   UNION ALL
   SELECT N+1
   FROM R
   WHERE N < 12
), MonthYears AS
(
  SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' +
       CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Name
  FROM R
), MonthYearsData AS
(
   SELECT
     YEAR(RequestedOn) Yr,
     MONTH(RequestedOn) Mon,
     SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr,
     count(*) CrCount
   FROM TempTableTest
   WHERE RequestedOn >= DATEADD(MONTH, -13, GETDATE())
   GROUP BY YEAR(RequestedOn), MONTH(RequestedOn)
)
SELECT M.Name, ISNULL(N.CrCount,0) as CRCount
FROM MonthYears M
left outer join MonthYearsData N ON M.Name = N.MonYr
GROUP BY M.Name, N.CrCount, M.LNo
ORDER BY M.LNo DESC;


how do I merge two views I have basically two views named customerV and productV.

the SQL statement in the customerV view is :

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE,
                      RECIEVABLE.PHONE#, RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE
FROM         CUSTOMER_REGISTRATION INNER JOIN
                      RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#

while the SQL statement in the productV view is :

SELECT     PRODUCT, MAKE, MODEL
FROM         PRODUCTV

I just want to create a new view called serial_products which would join all the columns of the two views into a single view calledserial_products.

Since you provide no common field to join on, I'm going to assume you want a  cross join, where every customer will be linked to every product.

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME,
           CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE,  RECIEVABLE.PHONE#,
           RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE, PRODUCT, MAKE, MODEL
FROM        CUSTOMER_REGISTRATION
INNER JOIN  RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#
CROSS JOIN  PRODUCTV


How do I modify the user interface language in SSMS? How do I modify the user interface language in SQL Server Management Studio?

For instance, if I installed in English, but wish to view in Japanese (to name but one possible example).

I am running this environment in a non-[English-as-a-first-language] environment, and would prefer all my windows to read in my natural language. (my particular natural language isn't really important is it?)

To change the language in Microsoft SQL Server Management Studio 2014:

> Tools > Options... > Environment > International Settings > Language


Minimizing Page Fetches I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables.  As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records?  As a more concrete example, is this:

Select
  Col1, Col2, Col3
From
  Table1
Where
  (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition"

Superior or Inferior to something like this:

Select
  T1.Col1, T1.Col2, T1.Col3
From
  T1
Inner Join
  T2
    On T1.FK = T2.PK
Where
  T2.RefCol = "Condition"

--  Edited --

As a corollary question:  Is it productive to create Views which contain Intermediary validations?  IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view:

Create View T1Validated As
Select
  Col1, Col2
From
  T1
Where
  Col1 = 1
And
  Col2 = 0

And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?

Since you are selecting fields from Table1 only, it is pointless to get the data from a join applied on table 1 and table 2.
After testing both queries on my server, i have noticed that the processing time has reduced using the first query (nested selects).
hope this answers your question.


MariaDB/MySQL IO limit in test environment I have a test environment (all Linux) where we start our server application with MariaDB (which runs in docker in the case if it matters), run some tests, save the test results and drop the DB.

I want to limit the MariaDB disk usage (IOPS), because we really use the storage (with another systems) and I try to save some resources. I've realized, MariaDB shouldn't even write to the disk, as it is a test environment and I don't really need persistence. (If the node dies, the test anyway fails and we run it from scratch somewhere else.) As a side effect the test would be even faster (but it's not important).

What I've found is the Memory storage engine, what is not useful for me, because I don't want to change anything on SQL level as the same SQL scripts used on live systems (where a by mistaken left behind "ENGINE = MEMORY" would make a disaster).

I also could use a RAM disk as a filesystem and MariaDB wouldn't even realize the change. That's sound good, but in this case I have to maintain a right size of RAM disk and also waste of RAM as the data is (most likely) already in the RAM and we put it there again (on a RAM disk).

So I am looking for a solution which doesn't require changes on the DB level (SQL), but in the MariaDB config to reduce or completely eliminate the disk usage (IOPS) regardless of data durability.

InnoDB only processes in "RAM" -- specifically its "buffer_pool".  InnoDB will persist things to "disk".  So InnoDB will be doing a lot of ram-to-ram copies if you use a RAM-disk.  However, you could save some RAM by decreasing innodb_buffer_pool_size and various other settings.  Caution:  don't lower them too much, or there can be failures.


What does the Postgres  operator do? I was reading through the Postgres documentation on materialized views and in one example they use the following:

SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

I tried to run a similar query on my own database, but I get an error.

[42883] ERROR: operator does not exist: character varying <-> unknown

I (probably) don't need to use the operator, but I'm just curious what it does.

Notes:

I am running the same version as the docs (9.3)
I did try Googling the question, but Google just ignores the <-> even if you wrap it in quotes.

It's supposed to be the "distance" operator from the additional module pg_trgm.

The manual:

  text <-> text   real  Returns the "distance" between the arguments, that is one minus the similarity() value.

The module has to be installed (once per database) with:

CREATE EXTENSION pg_trgm;

More details:

How is LIKE implemented?

Theoretically, any user with the necessary privileges could create an operator with that operator name using CREATE OPERATOR - but not in the example you link to.


Carry forward non-null value with a windowing function? In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    NULL
John     1/5    NULL
John     1/6    NULL
John     1/7    18
Mary     1/2    35
Mary     1/3    NULL
Mary     1/4    -15
Mary     1/6    NULL
Mary     1/7    0

Is there a way to do something like:

SELECT
    [name],
    [date],
    CARRY([happiness]) OVER (PARTITION BY [name] ORDER BY [date])

So that the intermediate results look like:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    20
John     1/5    20
John     1/6    20
John     1/7    18
Mary     1/2    35
Mary     1/3    35
Mary     1/4    -15
Mary     1/6    -15
Mary     1/7    0

CARRY isn't a real function clearly, just want that functionality.

You can use a recursive CTE and COALESCE to return the most recent non-NULL value for Happiness. See this fiddle for a working example.

In the example, I am first using a CTE to create a pseudo ID column for use in the recursive CTE. Then in the recursive CTE, we select the ID = 1 row (the earliest row) as the anchor and recursively select the next row by ID for each name group. The COALESCE function picks the previous value if the current value is NULL for happiness.

Query:

;WITH DataC AS(
  SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS ID,
    [name],
    [date],
    [happiness]
  FROM Data
), CTE AS (
  SELECT ID,
    [name],
    [date],
    [happiness]
  FROM DataC
  WHERE ID = 1
  UNION ALL
  SELECT d.ID,
    d.[name],
    d.[date],
    COALESCE(d.[happiness], c.happiness) AS happiness
  FROM DataC d
  INNER JOIN CTE c ON c.Name = d.Name AND c.ID = d.ID - 1
)

SELECT *
FROM CTE
ORDER BY name, date

Result:

ID  name    date                        happiness
-------------------------------------------------
1   John    2020-01-03 00:00:00.000     20
2   John    2020-01-04 00:00:00.000     20
3   John    2020-01-05 00:00:00.000     20
4   John    2020-01-06 00:00:00.000     20
5   John    2020-01-07 00:00:00.000     18
1   Mary    2020-01-01 00:00:00.000     35
2   Mary    2020-01-02 00:00:00.000     35
3   Mary    2020-01-03 00:00:00.000     -15
4   Mary    2020-01-04 00:00:00.000     -15
5   Mary    2020-01-05 00:00:00.000     0


Space used by databases file through linked server I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers).
I created view on client size, for all databases:

CREATE VIEW [dbo].[view] AS SELECT name AS 'DataFileName',
   physical_name AS 'DataPhysicalName',
   ROUND(size / 128, 1) AS 'DataTotalSizeInMB',
   ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS
   'DataSpaceUsedInMB',
   ROUND(size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS
   'DataAvailableSpaceInMB' FROM  [sys].[database_files] WHERE type = 0

and on server side I used loop to query this view. But column "DataSpaceUsedInMB" works only for default database for my login.

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE
If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd +
N''
INSERT INTO #dbspace
SELECT
    name AS [DataFileName],
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB],
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB],
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB]
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Make sure your login has the access to all the databases on the remote server.


Best practices for ensuring up to date sequences after table import We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on the destination table after a load.

Is the following sequence of events a safe and acceptable way of handling this?

Backup sequence properties to a table
Drop sequences
Load tables (with metadata)
Find which has the greatest last cached value, backup sequence table or new imported sequence.
Update accordingly

Are there any pitfalls with this method, and if so are there tried and tested methods?

For sequences the best way is to copy the current value from dba|all|user_sequences.last_number. Fetching the current value from the sequence is not safe; it can have higher values in other instances of the database.
Also make sure that your app is not using sequences for ordering ....


Unique values depending on context So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500.

Now, a entity in my database can have many PIN codes, and they must all be unique, but ONLY to that entity ID.

So two different entities can have the same PIN code.

How should i implement this? Should i implement this on the application level, or is it possible to do in the database with some constraints?

Im using Postgres 9.5

You can do this with a simple composite UNIQUE constraint: UNIQUE (entity_id, pin_code). Example table:

CREATE TABLE pin_codes
  ( entity_id  int NOT NULL,
    pin_code   text NOT NULL,
    FOREIGN KEY (entity_id)
        REFERENCES entities (entity_id),
    UNIQUE (entity_id, pin_code)
  ) ;


Is it possible that a same INSERT operation performance will suddenly degrade? I am working on a code performance test project.

Let me first describe the enviorment: MsSql Server, Ria Service

The automation test runs a big set of Test cases in midnight where have minimum network traffic or CPU usage on the server.

The tests will calls the Ria Service and the Ria Service will make calls to the database to do same operation repeatedly for couples times.

The performance metric is measured using Sql Profiling Trace, so we got all sql calls that made within each test and we can associates each of them.

I notice sometime a same SQL calls in a test its performance will suddenly jump up.

i.e. An INSERT operation of a test, usually is around 0.807 ms and its jumps to 200ms.

I wonder is this a normal behavior?  Say there is no environment impacts to the test...

It could be blocked by another process, it could have forced an autogrow, something else may have taken the CPU's attention, data may have changed affecting cardinality, the plan could have been flushed, who knows?

Basically, yes, the same query can behave differently at different times due to a variety of environmental factors, both inside SQL Server and external.


How should I model a worflow of status/activities ? (rules like "if is 3 go to 4, if is 4 go to 5" etc) I'm trying to model a workflow (status and activities) to create "rules".

Let me give you an example.

The main "object" let's say are "vehicle requirements", I will have this table:

idRequirement, idVehicle,DateOfRequirement, idStatus, LastUpdate
1                2       2012-01-02 10:20:00, 1     ,2012-01-02 10:20:00

In the example above as the register is just created, the DateOfRequirement and LastUpdate is the same, LastUpdate it will be updated after every change of status.

Status TAble

 idStatus,Status
 1         New
 2         Vehicle Assigned
 3         Closed

Activities Table

 idActivitie, Activitie
 1            Create Vehicle Requirement
 2            Assign Vehicle
 3            Close requirement.
 4            ReAssign Vehicle.
 5            Confirm

Until the tables above I have no problems, but my problem is that
for example:

I know I cannot close a requirement (update to idStatus=3 on "vehicle requirements")
if the current status is not "Vehicle Assigned" (idStatus=2).

I can do that by code (or I think so), but is there anyway to model this kind of rules to tables ? I mean a table where I retrieve the next idStatus and the activitie to perform (to save a "kardex/historical" of each requirement and it's activities), checking for example the Activitie or the current status ?

The rules also will have 3 "alerts" accordin the time and status. For example the limit time "green" to assign vehicle is 30 minutes or less the yellow "alert" it will be between 31 and 45 after the requirement has been created (status new) and the red alert it will show it after 45 of being in status "new"

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Green

After 39 minutues it will something like this:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Yellow

And after 45 minutes:

   idRequirement,Vehicle,CurrentStatus,NextStatus,       Alert
       1         N/A      New          Vehicle Assigned  Red

If there's no enough clear please tell me to try to explain better.

In the meantime I just need a single "workflow" I mean, always the order it will be idStatus=1, after that idStatus=2 and aftear idStatus=3, but if you want to help me considergin cases like go to idStatus=3 after idStatus=1, I'll be really grateful.

Some idea (probably bad) I had:

Table rules:

idRule   Name      idCurrentState  idNextState  idActivitieToPerform  GreenLimit  YelloLimit
1        Assign            1             2           1                  30          45
2        CloseFromNew      1             3           3                  30          90
3        ChangeVehicle     2             2           4                  45          90
4        CloseRequirement  2             3           3                  90          120
5        ConfirmVehicle    2             2           5                  30          90

But I have lot of trouble because of the "idCurrentState" and "idNextState" refers to the same primary key on status table.
My regards and thanks for your time.

Updates:

Remove "redLimit" (as Kevin Feasel mentioned (and I figure out a couple of hours after post, it's implied))
Add some Rules to let the sample more clear:

CloseFromNew
ChangeVehicle
CloseRequirement

Add activitie "ReAssign Vehicle"

To consider:

I'm 99% sure there are only 3 "colors" (so we can't ignore relationships with Colors and any other table as status/activities).
Different activities derive on the same status (ChangeVehicle and
Confirm) (muliple activities)
Different activities in different status derive on same status (no
linear workflow)
No linear workflow

"CloseFromNew" perform "Close" activity and set  Status to "Closed" from status "New", just as "CloseRequirement" set status to "Closed" but from status "Vehicle Assigned"

Well based on the comments of Kevin Feasel, I just extend my sample/problem (after "Update" text explain the constraints I need to accomplish)

My regards again !

What you have can work, given a few constraints:

You move between states in a linear fashion
You have multiple states with multiple activities.
You have only one consideration for what "color" the alert indicator should be.
You have three colors and only three colors (no more and no fewer).

Based on what you mentioned as requirements, that solution fits most of the constraints, but not #2.  There are a couple of things I would look at, though.

One thing I would question is whether you need that Activity table at all.  If it really is a 1:1 correspondence between states and activities, you could wrap it up into one table without loss:  your activity is just the action which pushes you to the next state.  If, however, there are multiple activities per state, this can make sense.  If you have an overarching status category (e.g., when the row is in "Vehicle Assigned" status, there are 5 separate activities which need to occur to get it to "Closed" status), then this general idea is alright.

So if you have one activity per state, the Status table would have the following attributes:

Name
Activity required to get to this state (if you, for example, have a web application and you want to populate the button text with a verb instead of a noun)
The next status ID (assuming this is a linear workflow)
GreenMax (the maximum number of minutes during which the row can be "green")
YellowMax (the maximum number of minutes during which the row can be "yellow")

RedMax is implied:  it's red whenever the difference between the current time and whichever time you want to use (DateOfRequirement, LastUpdate, etc.) is greater than YellowLimit.

Alternatively, if you have different categories for display such as "step 1 has red-yellow-green, but step 2 is just red-green and step 3 is red-pink-orange" (or if you believe that you will), I would move the colors out to their own table and create a Status-Color bridge table with status ID, color ID, and maximum time.  That makes retrieval a bit more complicated, though.

Now, if you do have multiple activities per status, then I would turn Status into a parent-child relationship with Activity, instead of a many-to-many relationship like you have now with Rules.  So Status would have Name, and Activity would look like:

Name
Status ID (to get the current status)
The next Activity ID (assuming this is a linear workflow)
GreenMax
YellowMax

And on your main object, you would actually hold the activity ID instead of the status ID, because you can derive the status from the activity.

The other thing I would do is drop the Rules table.  The only time that you would need a Rules table is if you have multiple statuses, multiple activities, and there are multiple relationships between these (for example, that both the New and Closed statuses both be able to perform the Assign Vehicle activity).  Based on your example, this does not appear to be the case.  It is possible that you actually do that many-to-many relationship between activities and statuses, if the relationship is not like your example.  In that event, your Rules table looks okay, with the caveat that you don't need the Red limit.

Incidentally, I highly recommend that you put the actual workflow logic in a stored procedure, whether you keep the linear flow (like you have now) or move to a non-linear workflow model.  You can use a combination of triggers and check constraints to force a workflow to operate in a particular manner, but it's a lot easier to understand if you just do it in a stored procedure:  all of the code is in one place not in an easy-to-forget area like a trigger.  Putting this in a stored procedure also abstracts it away from the application and business object layers, so they just need to call a method which says "go to the next workflow step" (and maybe pass in a detail to help you figure out which workflow step, in the event that this is not a linear process).

EDIT (post question update)

Based on your question update and comment, I'm okay with a three-table approach.  I'm not positive that you need it, but this could simplify things at the application level.

At this point, what you have is a state machine, with a State (status) table and a Transition (activity) table.  Your rule table then defines which transitions are valid for a particular state.

I would change the Rule table slightly, to look like this:

Surrogate key
Rule Name
Starting State ID
Transition (Activity ID)
Ending State ID
Green Limit
Yellow Limit

There is one minor change:  the position of the different keys.  Your rule table now reads like a book:  starting from one state, perform a transition and end at a different state (or the same one--you have an example in which the final state is the same as the initial state).

You would have a unique key constraint on starting state ID and activity ID, to guarantee that a particular transition from a particular state always ends in a single state.

On your vehicle table, I would put idRule instead of idStatus, to make it easy to tell which set of limits you need.  You might also want a history table showing the vehicle ID, rule ID, and time added, so you can track what happened in the workflow.

This means that your "go to the next workflow step" stored procedure is actually pretty simple:  given a vehicle ID and activity to perform, you can do a quick select against the Rule table to see if there is a valid transition from the vehicle's rule's state via the activity ID you get in the proc.  If there is, perform that transition:  update the vehicle table accordingly (setting idRule and LastUpdate); if not, return an error indicating that something must have gone wrong.

There is also no problem with having a table with two foreign keys back to another table.  idCurrentState and idNextState can both be legitimate foreign keys back to State.  You just need different names for the two foreign keys (e.g., FK_Rule_State_CurrentState and FK_Rule_State_NextState).


MySQL BEFORE INSERT trigger with IF conditions In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ebsms.inbox then insert to dbsmsra_pit2.smsra_sms and dbsmsra_pit2.smsra_trash

Here's my complete code :

DROP TRIGGER IF EXIST dbsmsra_pit2.smsra_sms.filterNoHP;
DELIMITER $$
CREATE TRIGGER `filterNoHP`
BEFORE INSERT ON smsra_sms FOR EACH ROW

BEGIN

DECLARE IDx int(11);
DECLARE NumberHP varchar(20);
DECLARE WaktuKirim timestamp;
DECLARE IsiSMS text;

SELECT ID, SenderNumber, ReceivingDateTime,
TextDecoded into IDx, NumberHP, WaktuKirim, IsiSMS FROM ebsms.inbox
WHERE NumberHP in (SELECT NoHP FROM smsra_blokir_number)
and DATE(ReceivingDateTime) = curdate();

IF NumberHP = null then
insert into smsra_sms
set idsms = IDx, SendingDateTime = WaktuKirim,
isi = IsiSMS, pengirim = NumberHP
ON duplicate key update idsms = idsms + IDx;

ELSE
insert into smsra_trash
set Nohp = NumberHP
ON duplicate key update Nohp = Nohp + NumberHP;

END IF;

END$$

DELIMITER ;

here's the error message :

Error Code: 1235. This version of MySQL doesn't yet support
'multiple triggers with the same action time and event for one table'

Please I need help.

As @dezso says, you can't compare NULL with arithmetic comparison operators as described in the MySQL's documentation.

  You cannot use arithmetic comparison operators such as =, <, or <> to
  test for NULL. To demonstrate this for yourself, try the following
  query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Edit this part:

IF NumberHP IS null then # <-- IS replaced =
    insert into smsra_sms
    set idsms = IDx, SendingDateTime = WaktuKirim,
    isi = IsiSMS, pengirim = NumberHP
    ON duplicate key update idsms = idsms + IDx;
ELSE
    insert into smsra_trash
    set Nohp = NumberHP
    ON duplicate key update Nohp = Nohp + NumberHP;
END IF;


what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server When this command is run, there is SQL server error

  'Arithmetic overflow error converting expression to data type int'.

SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

Because the file size is less than 16TB, bigint is not required. Still I tried to execute cast(size as bigint), the error persists.

SELECT sum(cast(size as bigint)) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

So the question is, what are the reasons for arithmetic overflow error other than exceeding the file size limit of 16TB.

-- casting using DECIMAL(38,2) throws exception, Size needs to be casted as bigint

  select [SizeInMB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files

-- This fix is sufficient, but cannot limit to 2 decimal digits

  select [SizeInMB1] = ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0)  from sys.master_files

-- 2 decimal digits are not available in fix, as divide by 1024.0 converts to default 6 digits

  select [SizeInMB2] = ((SUM(CAST(Size AS DECIMAL(38,2)))* 8) / 1024.0)  from sys.master_files

-- so 2 casts are required to eliminate Arithmetic overflow exception and limiting 2 decimal digits

  select [SizeInMB3] = CAST( ((SUM(CAST(Size AS BIGINT))* 8) / 1024.0) AS DECIMAL(38,2) ) from sys.master_files


Database Unallocated Space - Correct way to measure I am confused over the web regrading few scripts on calculating the database size and free space within same.
Most of the scripts over the web uses sys.databases_files dmv to calculate free space like here and here
However if we see one from sp_spaceused or database properties via GUI amount of free space is different. I get the reason that it is diff as it has filter on type in (0,2,4) which excludes Log file.
But i am not able to understand which one is correct and why Log file is excluded from sp_spaceused or is it that scripts over web are not correct and showing something else?

You'd have to provide the scripts you're referring to for us to be able to advise you on their accuracy, but if they're based off sys.database_files and the DMVs then good chance you're looking at something that's correct for the purpose they're intended for.
sp_spaceused was specifically designed to tell you the size of the data inside the database, either the whole database itself or for an individual Table or Indexed View:

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

The Transaction Log is stored in a different file and is a different type of object and concept than the database and it's files. The use cases to measure the space consumed by the database either as a whole or for an individual Table or Indexed View is usually more useful to do so without including the Transaction Log files which have a different purpose.
The Transaction Log is a running log of transactions (hence the name) since the last time the log file was backed up, to assist in the recovery of the database to ensure the ACID principles are guaranteed. It generally fluctuates in size (internally) as more transactions are written to the log file, until a transaction log backup occurs. It has no specific concept of indexes, or the data as a whole for the current point in time, rather it is the history to how the data became what it currently is in the database.
sp_spaceused is beneficial in providing you information at the database level on the total space reserved from the disk, how much of that space is used by the data in the database vs how big is the total index_size, and the remaining unused space that will become used by the data and indexes as they grow. Additionally, at the Table or Indexed View level, it even tells you the number of rows in the table (efficiently using meta-data) besides the previously mentioned data points.
To understand the general growth trend of the database, one could routinely view or log the results of sp_spaceused since the change in the data size (and index size) is what will be indicative of that metric, not the Transaction Log size at any given time, per se.


SQL Agent Job Logs - Can't get the Error Log I've some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to get that information with this query:

DECLARE @job_id UNIQUEIDENTIFIER

SELECT
    @job_id = job_id FROM msdb.dbo.sysjobs
WHERE
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'

;WITH Error_Output (job_id, error_log) AS
(
    SELECT
        JS.job_id,
        CASE
        WHEN JSL.[log] IS NULL THEN JH.[Message]
        ELSE JSL.[log]
        END AS LogOutput
    FROM
        msdb.dbo.sysjobsteps JS
        INNER JOIN msdb.dbo.sysjobhistory JH
            ON JS.job_id = JH.job_id AND JS.step_id = JH.step_id
        LEFT OUTER JOIN msdb.dbo.sysjobstepslogs JSL
            ON JS.step_uid = JSL.step_uid
    WHERE
        INSTANCE_ID >
        (SELECT
            MIN(INSTANCE_ID)
        FROM
            (SELECT top (2)
                INSTANCE_ID, job_id
            FROM
                msdb.dbo.sysjobhistory
            WHERE
                job_id = @job_id
                AND STEP_ID = 0
            ORDER BY
                INSTANCE_ID desc
            ) A
        )
        AND JS.step_id <> 0
        AND JH.job_id = @job_id
        AND JH.run_status = 0
)
SELECT
    sj.name AS [Job Name]
    ,MAX(sja.run_requested_date) AS [Start Date]
    ,MAX(sja.start_execution_date) AS [Effective Start Date]
    ,MAX(sja.stop_execution_date) AS [Effective End Date]
    ,DATEDIFF(MINUTE, MAX(sja.start_execution_date), MAX(sja.stop_execution_date)) as [Job Execution Time -  M]
    ,CASE WHEN error_log IS NULL THEN 'Job runs successfully' else error_log end as [Job Log]
FROM
    msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    LEFT OUTER JOIN Error_Output AS error ON error.job_id = sja.job_id AND error.job_id = sj.job_id
WHERE
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'
GROUP BY
    sj.name, error_log

However, I'm always getting that the Job runs 'Job runs successfully' and I already test and see that the job failed in execution. I even configured SQL_Agent_Job to receive email whenever the job failed, and I have received the emails and the status in the query does not change to the error log (failed).

What I'm doing wrong?

I have written a post about retrieving job status from msdb, which you can see here.  The code in that post does not pull in data from dbo.sysjobstepslogs, but it's pretty straightforward to extend that code to pull in the last error message from dbo.sysjobstepslogs if you are logging to that table.

By default, job steps do not log to msdb. To configure your job steps to log to dbo.sysjobstepslogs, you must go into the job step properties for every step in the job, visit the "Advanced" tab, and select the option for "Log to table":

You can then create this Table-Valued Function to get the most recent job status. I've modified the code from the post above to also pull the most recent error from the job, if it is available:

 CREATE FUNCTION dbo.SqlAgentJob_GetStatus (@JobName sysname)
     RETURNS TABLE
 AS
 RETURN
 SELECT TOP 1
     JobName        = j.name,
     IsRunning      = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL
                          THEN 1 ELSE 0
                        END,
     RequestSource  = ja.run_requested_source,
     LastRunTime    = ja.start_execution_date,
     NextRunTime    = ja.next_scheduled_run_date,
     LastJobStep    = js.step_name,
     RetryAttempt   = jh.retries_attempted,
     JobLastOutcome = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN jh.run_status = 0 THEN 'Failed'
                        WHEN jh.run_status = 1 THEN 'Succeeded'
                        WHEN jh.run_status = 2 THEN 'Retry'
                        WHEN jh.run_status = 3 THEN 'Cancelled'
                      END,
     LastError = (SELECT TOP 1 jsl.log
                 FROM msdb.dbo.sysjobstepslogs jsl
                 WHERE jsl.step_uid = js.step_uid
                 AND jsl.date_modified >= ja.start_execution_date)
 FROM msdb.dbo.sysjobs j
 LEFT JOIN msdb.dbo.sysjobactivity ja
     ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
 LEFT JOIN msdb.dbo.sysjobsteps js
     ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
 LEFT JOIN msdb.dbo.sysjobhistory jh
     ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
 WHERE j.name = @JobName
 ORDER BY ja.start_execution_date DESC;
 GO

Finally, you can leverage the TVF to query for your job status, last step to run, last logged error, etc:

 --Specific jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE
        j.[name] like '%JOB_1%'
     OR j.[name] like '%JOB_2%'
     OR j.[name] like '%JOB_3%'
     OR j.[name] like '%JOB_4%'
     OR j.[name] like '%JOB_5%';
 --Failed Jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE sts.JobLastOutcome <> 'Succeeded';


How to use JOIN and SUM function to Calculate the total of query returned column? I ran bellowing query;

Query:

SELECT  j.jobId,
        j.productId,
        p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId

And got bellowing result;

Query result:

jobId | productId | unitPrice
_____________________________
  1   |     4     |   175.99
  1   |     5     |   100.00
  1   |     6     |   125.00

Now, I want to calculate all unitPrice to get the total. the result I expect is;

Expecting result:

jobId | productId | Total
____________________________
  1   |     4     |   175.99
  1   |     5     |   100.00
  1   |     6     |   125.00
 null |    null   |   400.99

Therefor bellowing is the query I tried;

Query I tried:

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY j.jobId,j.productId WITH ROLLUP

But it didn't give me the result I expected, where I went wrong and what I gotta do to get the result I expect?

You can use grouping set also

declare @JobRequiredProducts table (jobId int, productId int);
declare @Product table(productId int, productUnitPrice decimal(8,2))

insert into @JobRequiredProducts(jobId,productId)
values(1,4),(1,5),(1,6);

insert into @Product(productId, productUnitPrice)
values(4,175.99),(5 ,100.00),( 6 , 125.00)

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  ROLLUP ((j.jobId, j.productId));

with grouping sets

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  GROUPING SETS( (j.jobId, j.productId),());

output:

jobId   productId   Total
1       4           175.99
1       5           100.00
1       6           125.00
NULL    NULL        400.99

dbfiddle here


Wrong behavior in Backups Checking my Native backup this morning something wierd happened. I will describe the problem here.

1) The msdb table backup set shows me the backup was done successfully.

2) The errorlog shows me that the backup was done successfully.

3) The job history doesn't show any entry, even it is successfully or error.

4) Checking the backup media family table the physical device name column shows me something like this: {F2F1E11C-589A-4AAE-8880-0120D8D1998D}1
and other days it shows me the right directory of my backups.

4) The DPM also do system state and database backup after the SQL Server Backup and runs well.

Well.. if anyone need more informations, please let me know.

Thanks.
Marcos Freccia
@SqlFreccia

This can be VSS based DPM backups. Do you also have info in the Errorlog about "I/O Frozen for database xxx" and "I/O thawed for database xxx" ? That is sign of VSS (Snapshot) backups. If you rely on that kind of backups you should (as always) ensure that you can restore the backups, and you should pay special attention to Log backups.


Why does pt-table-checksum gives exit status 0 if slave is unreachable? I'm using pt-table-checksum version 2.1.4 for checking my master-slave set up. In the case when I forget to set up the right privileges on the slave, it writes an error to stderr like this:

# pt-table-checksum -d employees
Cannot connect to h=node03.mydomain.local                                 <== error
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
10-04T15:33:33      0      0        9       1       0   0.014 employees.departments
[...]

However, the exit status of it is 0.

# echo $?
0

The number differences it will find is always zero, as it has nothing to compare against. And yes, documentation says to exit with status 0 if no differences or errors have been reported. But this does not make sense in most cases.

Is there a way to make pt-table-checksum not failing silently and reporting this as a general failure? Should I file a bug or is this configurable?

It proves to be confusing to more users, for example in this question: "pt-table-checksum help required".

pt-table-checksum runs on the master primarily, but as binary logging on MySQL is statement based, the actual calculation statements are being executed on the slave as well. One should then run pt-table-checksum on the slave to check and compare the checksums it can't access the slave from the master. Add the --replicate-check-only option to have it only check for differences based on the checksums already present in the checksums table.


What tools are there for data masking? (MySQL, Linux) I'm looking for (ideally free, open-source) data masking tools.  Do any such exist?

Note: this related question deals with tools for generating test data, but in this question I'm more interested in starting with real data, and masking it for use in test without losing any special relationships that make it interesting for test purposes.  Generated data is fine for some test purposes, but real-world data will bring up issues you never thought of.
Tool to generate large datasets of test data

I would be very surprised if there was a generic tool for this - how would it "know" what is sensitive data and what wasn't? For example it would need to examine all your data and recognize all possible formats of credit card number, phone number, postcode, email address, and whatever other data is considered sensitive. It would also need to be smart about your schema - e.g. should it rewrite all customer email addresses to "nobody@company.com" - or does any part of your database, applications, other tools assume that a customer's email address (or SSN or whatever) is unique? Or do you have some part of the application that checksums credit card numbers, that would break if you reset them all to 0000 0000 0000 0000? Or does your telephony system assume that a customer's dialing code corresponds to the country in their address?

Basically, configuring any tool to do it will be as much or more work than just writing your own script, using your knowledge of the application. At my site, we simply made it policy that anyone who adds a column with such data in updates the script to anonymize it at the same time, after an initial audit to find all those columns and write version 1.


Why does this execution plan have Compute Scalars? I have a simple SELECT statement.

USE [AdventureWorks2014]
GO

SELECT *
FROM Sales.SalesOrderDetail sod

The execution plan has two Compute Scalar.

Why is this?  I was expecting to just get the Index Scan or maybe a Table Scan?

The first (rght-most) has

 [[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sod].[OrderQty],0),(0.000000)))

When second has:

[[sod].LineTotal] = Scalar Operator([AdventureWorks2014].[Sales].[SalesOrderDetail].[LineTotal] as [sod].[LineTotal])

The Compute Scalars are related to the computed field LineTotal. Script out the table, and you'll see that field defined as:

[LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

SQL Server has two operations to perform. It has to first run the calculation of:

[UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty]

Then it has to check to see if that value is null, and if so, substitute 0.0.


Can not install oracle apex on GNU/Linux (db 12c) I have to install oracle apex on oracle database 12.2.0.1.0, database is on GNU/Linux server.
I followed instructions from this pages:

https://oracle-base.com/articles/misc/oracle-application-express-apex-5-0-installation#installation]

https://docs.oracle.com/cd/E71588_01/HTMIG/downloading-and-installing-OracleAE.htm#HTMIG29384]

but when i came to this step:

@apexins.sql APEX APEX TEMP /i/

I got this error:

  PL/SQL procedure successfully completed.

  PL/SQL procedure successfully completed.

  PL/SQL procedure successfully completed.

  Performing installation in multitenant container database in the
  background. The installation progress is spooled into apexins_cdb*.log
  files.

  Please wait...

  catcon: ALL catcon-related output will be written to
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb_catcon_64683.lst]
  catcon: See
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb*.log] files
  for output generated by scripts catcon: See
  [/u01/app/oracle/product/12.2.0/dbhome_1/apex/apexins_cdb_*.lst] files
  for spool files, if any validate_con_names: ORCLPDB is not open
  catconInit: Unexpected error returned by validate_con_names Unexpected
  error encountered in catconInit; exiting

  Installation completed. Log files for each container can be found in:

  apexins_cdb*.log

  You can quickly scan for ORA errors or compilation errors by using a
  utility like grep:

  grep ORA- *.log grep PLS- *.log

So i find solution for that error on this link:

https://learn-oracle-database.blogspot.com/2016/04/validateconnames-pdbseed-is-not-open.html

I have done, everything like is described on the link, and database was in this state:

SQL> select name,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
MIGRATE

ORCLPDB
MIGRATE

And I came to final step, and executed this order:

$ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin /tmp/utl32k_output utl32k.sql

Then I got this error message:

  /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/tmp/utl32k_output_catcon_
  61307.lst as CATCONOUT catconInit: Unexpected error returned by set_log_file_base_path print() on closed filehandle $CATCONOUT at
  catcon.pm line 6702. Unexpected error encountered in catconInit;
  exiting

I tried to find solution but i could not, so if someone could help what I have to do, to solve this problem.

Check the status of pluggable database.

SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PDBORCL          MIGRATE
PDBORCL2         MIGRATE
PDBORCL1         MIGRATE

SQL> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Check the status of pluggable database.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MOUNTED
PDBORCL          MOUNTED
PDBORCL2         MOUNTED
PDBORCL1         MOUNTED

We need to start PDB$SEED pluggable database in UPGRADE state for that

SQL> SHUTDOWN IMMEDIATE;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE;

ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.
Database opened.

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         MIGRATE
PDBORCL          MIGRATE
PDBORCL2         MIGRATE
PDBORCL1         MIGRATE

SQL> exit

try again
it will solve the issue


Why do ASC and DESC come *after* a CASE clause? This query works:

DECLARE @Ordering NVARCHAR(50)
SET @Ordering = 'aggregateid'

DECLARE @OrderDirectionInt INT
SET @OrderDirectionInt = 0 --1

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 1 THEN AggregateId END DESC,
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 0 THEN AggregateId END ASC
        ) AS RowNumber,
        MyField
    FROM
        MyTable
) AS TempResults
WHERE TempResults.RowNumber BETWEEN 12 AND 23

What I don't understand is why the ASC and DESC are after (or outside of) the CASE statements.  It looks like the ASC and DESC are being put in unconditionally, which doesn't make sense... it might resolve to:

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

Why doesn't the CASE statement contain the ASC or DESC (if you try to do that you get a syntax error)?

Why do ASC and DESC come after a CASE clause?
Why doesn't the CASE statement contain the ASC or DESC (if you try to do that you get a syntax error)?

Because a CASE expression (it's neither a clause nor a statement) can have a value/expression as a result, not code words like ASC or FROM. The syntax provided in CASE expression documentation is:

Searched CASE expression:
CASE
     WHEN Boolean_expression
       THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

It might resolve to:

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

No, it cannot. Due to the implied ELSE NULL part that all CASE expressions have as a default, if you don't provide it.


Migrate from innodb_file_per_table to off in MySQL We have a large number (2,500+) of databases and users in MySQL 5.6 on a single virtualized server (4 cpus and 8GB of memory). All the tables are using InnoDB. The actual data size is not very large (~5GB), and queries per second is low, just lots of small databases and users. Each account created for our application get's their own database and user (this architecture cannot change unfortunately).

We are seeing very poor performance, especially when trying to stop mysqld (often times it just times-out). Starting mysqld and spawning (ready for connections) takes over 3+ minutes. Running mysqldump brings the entire server to its knees. Load, CPU usage, and iowait, all skyrocket during mysqldump, and cause other queries to timeout and fail.

It was recommend that we transition off of using innodb_file_per_table because of the excessive number of open file descriptors, shear number of directories and files, and i/o operations.

Changing innodb_file_per_table to off does this, but how can we convert all existing databases and tables to utilize this?

Below is our current /etc/my.conf just for reference, in-case there are further optimizations we can make to optimize for lots of small databases.

[mysqld]
datadir=/mysql/data
socket=/mysql/mysqld.sock
symbolic-links=0
default-storage-engine=InnoDB
slow_query_log=1
long_query_time=2
slow_query_log_file=/var/log/mysql_slow.log
expire_logs_days=30
max_connections=50

bind-address=192.241.X.X
port=3306
max_allowed_packet=4M
net_retry_count=5
max_connect_errors=100
wait_timeout=14400
connect_timeout=10

open_files_limit=65535
innodb_open_files=65535
key_buffer_size=256M
innodb_buffer_pool_size=4096M
innodb_log_buffer_size=4M
group_concat_max_len=16k
max_sort_length=16k
max_length_for_sort_data=16k
query_cache_type=1
query_cache_limit=1M
query_cache_size=64M
innodb_thread_concurrency=8
thread_concurrency=8
thread_cache_size=128
thread_stack=1M
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
table_open_cache=32768
table_definition_cache=65535
tmp_table_size=33554432
max_heap_table_size=33554432
innodb_log_file_size=1024M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table
log-warnings
innodb_flush_neighbors=0

user=mysql
old_passwords=0

[mysqld_safe]
syslog
pid-file=/var/run/mysqld/mysqld.pid

STEP #1

Set innodb_file_per_table to 0 in /etc/my.cnf

[mysqld]
innodb_file_per_table = 0

STEP #2

service mysql restart

STEP #3

Convert every InnoDB table to InnoDB again

echo "SET SQL_LOG_BIN = 0;" > /root/ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-u... -p..."
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> /root/ConvertInnoDBToInnoDB.sql
less /root/ConvertInnoDBToInnoDB.sql

Once you view he script and are satisfied, login to mysql and run this

mysql> source /root/ConvertInnoDBToInnoDB.sql

EPILOGUE

All .ibd files will vanish and all InnoDB tables and indexes will exist inside ibdata1

Give it a Try !!!


Postgres 10. Hide users/roles from other users I have a postgresql 10 installed and I want to hide the name of users/roles to other users. I searched through other posts and somebody suggested this:

REVOKE SELECT ON pg_catalog.pg_authid FROM public;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM public;

After doing all of these, user1 can't dig into the info of a user (Error retrieving roles from the database server. ERROR: permission denied for relation pg_auth_members) but still can list them all as you can see on the next image

Of course user1 is not a superuser.

I already tested this with no effect:

REVOKE SELECT ON pg_catalog.pg_roles FROM user1;
REVOKE SELECT ON pg_catalog.pg_user FROM user1;

Tested this too with no effect:

REVOKE SELECT ON pg_catalog.pg_authid FROM user1;
REVOKE SELECT ON pg_catalog.pg_auth_members FROM user1;

If I launch this next statements users can't login and everything is broken:

REVOKE SELECT ON pg_catalog.pg_roles FROM public;
REVOKE SELECT ON pg_catalog.pg_user FROM public;

Is there a way to achieve this? Is very important to me to hide the name of the users to other users.

Background: I'm creating a hacking ctf (capture the flag) virtual machine. This is one of the challenges... the point is avoid a user can see the "right database user name" to connect. That's something he/she must find inside the database finding hints and other cryptographic stuff.

Edit: a "sad" workaround is to create hundreds of dummy users in order to "try to hide" the real one... but it must be an elegant solution for this. Any help?

pgAdmin is using pg_roles to show that information, so it is enough to run

REVOKE SELECT ON pg_catalog.pg_roles FROM public;

But since the information is available in other views as well, you would need to hide them, for instance the pg_user and pg_shadow views.

It should not stop users from logging in nor breaking anything since those views are not used during authentication, so you should make sure that you didn't change anything else.
The logs might show you the reason for why they can't log in.


Can I rely on the order of columns on an index generated by database tuning advisor I got the following recommendation on a table:

CREATE NONCLUSTERED INDEX
    [_dta_index_MyTable_6_44943632__K16_K52_K8_K9_K15_1_2_4_5_6_7_10_11_12_13_14_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_]
ON [dbo].[MyTable]
(
    [ColumnA] ASC,
    [ColumnB] ASC,
    [ColumnC] ASC
)

However, I am a bit surprised about the order of the columns in the recommendation. I would expect the result to be ColumnB, ColumnA, ColumnC.

Can I rely on the recommendation or should I decide the order of the columns?

You need to provide more info, specifically why do you expect the order you expect. An index on (A, B, C) is completely different from an index on (B, A, C) and one cannot replace the other. The DTA may be wrong sometimes, but is way more often right rather than wrong.


Automate backup using Windows Task Scheduler I want to run an automatic backup of SQL Server 2012 Express using Windows Task Scheduler.

I have 3 user databases. I want to backup all 3 user databases through a single stored procedure and a single batch file.

My environment is:

Windows Server 2012 R2
SQL Server 2012 Express
Windows domain

I have gone through some related articles:

How to schedule and automate backups of SQL Server databases in SQL Server Express
Automated backups utilizing SQL Server and Windows Task Scheduler
Schedule a daily backup with SQL Server Express

However, it is not clear to me how to make a stored procedure and run that stored procedure using a batch file?

I recommend using Ola Hallengren's backup solution.

The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure.

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt


Transactional Replication - Publications with same Name on the same Domain I'm using Transactional Replication and I've currently got three 2008r2 instances Publisher/Distributor/Subscriber.

I need to put three more 2008r2 instances on the same domain in the exact same configuration.

I'll be changing the Publisher Names, but I'd like to avoid changing the Publication Names.

I'm pretty sure this is safe to do, but in the past I've found replication to be "problematic".

Our set-up includes little quirks such as Indexed Views materialised as Tables and I also have concerns about Orphan meta-data lurking under the bonnet.

Has anybody had any problems with duplicate Publication Names and strongly advises against them? Any shared wisdom would be much appreciated.

Yes, it is fine to have same publication names on as many different publishers as you want.  You most likely can't have dup publication names on a single publisher though, but that is not what you are asking.  Further, you can easily test this out in your test/dev environment, on two servers.  Create a single pub/dist/sub server, and on second server, create another pub/dist/sub, and on each, create a publication with same name.

About your comment "but in the past I've found replication to be "problematic" - please explain?


How to setup two mysql servers with group replication to work individually, when no network I have two mysql servers in two different places. Both servers must be able to read and write. All tables are designed to based on unique id (UUID) primary key. I want to sync these two databases live using a replication method when networks are available. But both servers must be able to work individually when networks are not available.

Which replication method is suitable for this situation ? If someone has already tried this, please help me to resolve this problem.

I have tried MySQL group replication, but when network disconnected i can only work on one mysql server which is bootstrapped. Othe one doesn't allow writes.

Group replication is not a good match for scenarios where server diverge in time.

What you want is a Master to Master setup, where given two server M1 and M2, when the network is restored, M1 is a replication slave of M2 and M2 is a replication slave of M1.

A crucial details is that both servers must not do conflicting updates to the same data, so you should partition your data accordingly and you can also use auto increment settings to generate different ids on different machines.


Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do? I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and writing job data to an SQLite database. It stores the job in SQLite and then sends it to the RIP. The RIP communicates back to the bridge when the status changes. It updates the record in SQLite, and through a series of subscriptions and webhooks sends the status update back to the API.

Neither the bridge nor the RIP automatically cleans up after itself, and the bridge imposes a limit of 5,000 tracked jobs at a time. So if we try to send job #5001 while it's storing 5,000 additional jobs, it will fail.

So the solution, at least in theory, would be to clean up jobs as they complete. Which is what we're doing.  When jobs complete my API enqueues a cleanup task, which is run from a separate process and requests the bridge remove that job.

The problem arises here. When we request cleanups the bridge starts failing job submissions due to DB lock errors. We're not deleting active jobs so there should be zero conflict between what is currently being INSERTed or UPDATed and what is being DELETEd as a result of our removal request.  I can only assume SQLite is locking the entire database for the duration of the delete instead of locking only the rows in use.  Would this make sense?

From the outside of the black box that is this software, is there anything I can do to affect this locking behavior in SQLite?

If not I essentially have to work around it by putting a buffer between everything our API does and the bridge that switches between a print mode and a cleanup mode but never does both synchronously. This will work but will slow production, and ultimately requires me to build a hack around a limitation in a $200,000 software product that just can't handle our volume.

SQLite is essentially a single-user "database" and locks entire files, so two concurrent processes attempting to modify the same file will necessarily lock each other out. In the best case the file being locked will contain a single table, in the worst case -- the entire database. You will have to work around the problem by externalizing management of concurrent jobs.


Finding and listing all columns in a SQL Server database with identity I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.

Using the script from the Question as a starting point, you might be better off using the following query:

SELECT
  N'DBCC CHECKIDENT (''' +
  SCHEMA_NAME(tbl.[schema_id]) +
  N'.' +
  tbl.name +
  N''', RESEED); -- WITH NO_INFOMSGS;'
   , clmn.seed_value
   , clmn.increment_value
   , clmn.last_value
FROM       sys.tables tbl
INNER JOIN sys.identity_columns clmn
        ON clmn.[object_id] = tbl.[object_id]
WHERE  tbl.[object_id] <> OBJECT_ID(N'dbo.sysdiagrams') -- skip "dbo.sysdiagrams"
ORDER BY SCHEMA_NAME(tbl.[schema_id]), tbl.name;

The main difference is rather than determining the IDENTITY field by matching the column name found in sys.identity_columns, this query replaces the JOIN to sys.columns with sys.identity_columns and removes it from the WHERE clause. The effect of this is to implicitly filter the rows (since only tables with IDENTITY columns would be in sys.identity_columns) and to now have access to the IDENTITY meta-data: seed_value, increment_value, and last_value.

Matching on the column name only, you run the risk of selecting tables that might not have an IDENTITY column but do have a column that has the same name as one of the columns found in sys.identity_columns.


Reducing the duration of this COUNT() query I have a table with 2,161,524 rows. I think the counting query is taking too long.

select count(mcon_codigo_pk) from tbMovimentoConta
-- count = 2,161,524
-- time = 9 seconds

select count(1) from tbMovimentoConta
where con_codigo_fk = 1
and mcon_data between '2015-01-05' and '2016-01-06'
-- count = 1,034,729
-- time = 13 seconds

Details:

The column con_codigo_fk is bigint and has a foreign key index (non-clustered)
The column mcon_data is datetime and has index (non-clustered)
The table has an auto-increment PK (clustered index)
There's more three foreign that has index (all indexes was created by Entity Framework)

Index creating script

CREATE NONCLUSTERED INDEX [ix_mcon_data] ON [dbo].[tbMovimentoConta]
(
    [mcon_data] ASC
)
WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
);
GO

My computer has 8GB RAM and a Core i7 running Windows 10.

Are my queries really taking a long time or they are on the average expected time? If they are taking a long time, what can I do to make them faster?

There are 2 possibilities for a covering index of the query:

a composite index on (con_codigo_fk, mcon_data). This would cover all similar queries.
If you add this index, you can (most probably) safely remove the index on (con_codigo_fk) and the new one will be used instead. The other index on (mcon_data) can be used by different queries, so I wouldn't remove it.

Code to add the index:

CREATE INDEX ix__con_codigo_fk__mcon_data       -- choose a name
  ON dbo.tbMovimentoConta
  (con_codigo_fk, mcon_data) ;

a filtered index on (mcon_data) WHERE (con_codigo_fk = 1). This would of course be useful only for queries with the specific value (1).
The use cases for such indexes are much more rare so it's probably not useful to you.

Another issue is that you are using BETWEEN with a datetime type. This will give you inaccurate results, as it will include results with the exact datetime '2016-01-06' 00:00:00'

It's better to use inclusive-exclusive ranges:

select count(*)
from tbMovimentoConta
where con_codigo_fk = 1
  and mcon_data >= '2015-01-05'
  and mcon_data  < '2016-01-06' ;

@Aaron Bertrand has blogged about this with much more detailed explanation: What do BETWEEN and the devil have in common?

Another issue - that the index isn't trying to address - is that while the time needed for the query with the WHERE condition might be explained by a bad plan and lack of appropriate index, the time for the 1st query, the whole table count (9 seconds) sounds a bit too much. (and the 1 second for the query after the index added is high, too).

I would be speculating without further details as the explanation might be from numerous different reasons (general/memory SQL Server settings, high load on the server, wide clustered index, etc) so I suggest you either add more details on the question or post a new question (with the CREATE TABLE script if the issue is only for queries involving this table).


How can I speed up my slow query? I have a table of pigeon data.
pigeon_id = ID of pigeon
date_time = time of signal_event
pigeon_station = id of pigeon station
signal_strength = signal strength of pigeon

    CREATE TABLE `pigeon_signal` (
  `pigeon_id` varchar(20) NOT NULL,
  `date_time` int(11) DEFAULT NULL,
  `signal_strength` decimal(10,0) NOT NULL,
  `pigeon_station` varchar(20) DEFAULT NULL,
  KEY `index1` (`pigeon_id`,`pigeon_station`),
  KEY `index2` (`pigeon_id`,`date_time`,`pigeon_station`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Table count: 500,000 rows

Each pigeon has a transmitter. Every few seconds the pigeon station records the signal strength of all the surrounding pigeons.

I want to query the first time a pigeon station sees a pigeon. Here is my sql. It is slow but it returns the correct results:

select ps2.* from pigeon_signal as ps join
 (select MIN(date_time) as date_time, pigeon_id, pigeon_station from pigeon_signal group by pigeon_id, pigeon_station) ps2
 on ps.date_time = ps2.date_time and ps.pigeon_id = ps2.pigeon_id and
 ps.pigeon_station = ps2.pigeon_station

EXPLAIN EXTENDED:

The query takes about 2 seconds to run (too long).
Is there a better way of querying this data?
I would appreciate any tips/hints/advice related to this problem.

Many thanks :D

UPDATE:
Thanks for the answers!! :D
I have changed the pigeon_id column to a varchar(20).

@ypercubeᵀᴹ I changed the index to what you suggested and it is the fastest (thanks!!!)  But why?

distinct pigeon_id:      25306
distinct pigeon_station: 36
distinct date_time:      452322

My thinking: Order the index attributes by the number of distinct elems ascending. Results:
1. pigeon_station,pigeon_id, date_time:  1.8 seconds
2. pigeon_id, pigeon_station, date_time: 0.4 seconds
3. pigeon_id,date_time,pigeon_station:   1.8 seconds
4. date_time, pigeon_id, pigeon_station: 1.9 seconds

Why is option 2 faster than option 1? As there are less distinct pigeon_station values than pigeon_id.
But if the optimal index should order the attributes by the number of distinct values descending then why isn't option 4 the fastest?

Many thanks for your time

Presumably a pigeon cannot arrive at a station twice in one second?  Then add this:

PRIMARY KEY(pigeon_id, pigeon_station, date_time)

This will improve on ypercube's suggestion, perhaps by a factor of 2.

At that point, probably both of your indexes become useless.  But before that, let's see the other important queries.


Is there a way to backup a mysql database without having to run mysqldump? Is there a way to run a backup of a mysql database using sql? We have scripts which run updates against our databases. They only have mysql user credentials, not system login credentials.

Is there anyway to run backups of mysql via a mysql connection instead?

Is there a way to run a backup of a mysql database using sql?

Short answer: No.

  ... scripts ... only have mysql user credentials, not system login credentials.

I'm pretty sure it's possible to run the mysqldump utility against a remote database.

However, I would have to ask "Why?"

Even if you could take a backup, what would you hope to do with it?

It seems to me that you don't have the tools or the credentials necessary to take proper backups (or to restore a database, which pretty much has to be done on the machine itself).  As such, I would guess that someone else is responsible for keeping these databases up and running, recoverable and well-tuned (at least I hope so, or your organisation is in Big Trouble).

Perhaps you should discuss options with them?


How to deny access to any system user who attempts to log in without password usage? I have mysql-server-5.5 in Ubuntu Server 12.04

I realized that when typing mysql command logged as any system user I got access to mysql, despite I can only display test and information_schema databases, how can I disable this accesses?

I don't have those users registered on mysql so I don't want this to have access.

This is what happens

user-one@server:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39950
Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)...

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

user-two@server:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39950
Server version: 5.5.38-0ubuntu0.12.04.1-log (Ubuntu)...

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

When looking as root for this users in case they are registered

mysql> use mysql
mysql> SELECT * FROM user WHERE User = 'user-one';
Empty set (0.00 sec)

mysql> SELECT * FROM user WHERE User = 'user-two';
Empty set (0.00 sec)

By default ''@localhost, and other bunch of accounts exist in a freshly installed MySQL instance.

If you execute SELECT user() you may see something like this:

mysql> SELECT user();
+--------------------+
| user()             |
+--------------------+
| user-one@localhost |
+--------------------+
1 row in set (0.00 sec)

That user doesn't really exist, and it is actually mapped for authentication and authorization to:

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| ''@localhost   |
+----------------+
1 row in set (0.00 sec)

In order to fix this, run the script mysql_secure_installation always after setting up a production database, and it will ask you to set a mysql root password, delete remote access and delete the passwordless default accounts. This should be part of any proper mysql installation process, and it is documented here.

Starting with 5.6 and 5.7, better security defaults are used, but only on official packages.

Remember that by default, all users have write permissions to write on test% databases. That is a very dangerous default, and should be fixed before production.


PostgreSQL-Backup from windows hard drive to linux I'm in a bit of a pickle here. I managed to copy the whole postgresql installation directory to a backup drive but how would I retrieve it from Linux with the same architecture and version. Is this possible? I wasn't able to do a backup.

There's no way to dump your Windows PostgreSQL database directory using Linux directly1.

You need to start up a virtual machine running Windows, then copy the database directory to that virtual machine. You can then pg_dump -Fc it, and pg_restore the dump to your new blank PostgreSQL install on the Linux machine.

You must make sure that if your PostgreSQL data directory was from 64-bit PostgreSQL you install a 64-bit Windows version and install 64-bit PostgreSQL on it.

If your PostgreSQL data dir is 32-bit it doesn't matter whether you install 32-bit or 64-bit Windows, but you must install 32-bit PostgreSQL.

Licensing restrictions mean that you can't just download a virtual machine image for Vagrant or whatever to run Windows. You will need a Windows install DVD or ISO image. It's possible to download these legally from Microsoft's digital distribution system if you don't already have a DVD or ISO. You can install them without a license key, but it'll disable its self after a few days, and it's not legal to use it without possessing a license.

For that reason, unless the database is really huge one of the best options is to sign up to Amazon EC2 (if you haven't already) and launch a Windows Server virtual machine on EC2. You can install PostgreSQL then copy your database to that virtual machine, run pg_dump, and copy the dump back to your computer. Then just shut down the VM. You can use EC2 for free if you use a micro instance.

1 It's possible you might be able to run PostgreSQL using WINE, but I suspect that'd be even more complicated, if it works at all, and probably not particularly trustworthy. So just use Windows.


Running a stored procedure across db Link I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ran which runs some calculations and updates some additional tables. How can I have my script run a stored procedure across a link? Rough idea of what I'm thinking...

Begin

Insert into schema@myLink
 SELECT * from testSchema
;

commit;

schema@myLink.DoMagicCalulcations();

commit;

dbms_output.put_line("schema has been updated");
END;

You will need to add the table to the insert statement...

Insert into schema.table@myLink SELECT * from testSchema;

and re-arrange the procedure call a bit...

schema.procedure@myLink();

You might also consider removing the first commit so that the insert and procedure call are done in the same transaction.


I can't restore my database on SQL Server 2012. Exceeds size I'm trying to restore a database from a .bak file. The file is only 89.3 mb large. I'm using the restore with replace option, so that all other data in the database is removed. We are just trying to get the latest data from out clients.

But I get an error saying that I will exceed my limit of 10GB. My server instance is, using

select @@version:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Could someone please tell me why I can't get my database to restore. Do I have to get the full version of 2012? We are using the Express version

Express Edition limits the size of the database to 10 GB.

The backup is small because it only contains pages that are used -- regardless of whether or not the backup is compressed -- but the original file sizes will be restored to the size they were when they were backed up. The file sizes are what count against the limit.

Assuming this is the problem, the only solutions are to shrink the files before taking the backup, or restore the backup on a different edition (probably Developer edition).


Getting sum of each day of last 30 days I can get a list of each day of last 30 days row by row with this query and it works..
But I don't think that it is the best one since the "WHERE" part is messed up. I draw a graph and I just want to get daily_total_invoice_amount for sum of each day of last 30 days.

SELECT
  sum(b.invoice_amount) daily_total_invoice_amount,
  extract(day from a.order_creation_date)   as day,
  extract(month from a.order_creation_date) as month
FROM orders a
  INNER JOIN order_items b
    ON a.order_id = b.order_id
WHERE
  order_creation_date BETWEEN (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '2 DAY')
AND CURRENT_DATE + INTERVAL '1 DAY'
    GROUP BY month, day
    order by month DESC, day DESC

Why include tomorrow (CURRENT_DATE + INTERVAL '1 DAY')? A future date in order_creation_date would normally indicate a data error. I would not even include today in the statistic, since that is typically incomplete and misleading until the day is over. Consequently I use the last 30 days up until yesterday in my query.

Assuming order_creation_date is an actual date, just GROUP BY and ORDER  BY that date, that's simpler an cheaper than using extracted month and day numbers for the same. If it's not a date, but a timestamp or timestamptz, just cast to date to group by the day: o.order_creation_date::date. Be aware that the result depends on the timezone setting of the session when dealing with timestamptz (Day boundaries are defined by the time zone.)

Also assuming you want a result row for every single one of the 30 days. To make sure, days without orders don't go missing, generate the series of days with generate_series() and LEFT JOIN to that. You can generate timestamps, but I use the simpler integer variant while we are operating with dates. We can just subtract an integer from a date to subtract days.

SELECT CURRENT_DATE - i AS date
     , sum(oi.invoice_amount) AS daily_total_invoice_amount
FROM   generate_series (1, 30) i -- to return exactly 30 rows
LEFT   JOIN orders      o ON o.order_creation_date = CURRENT_DATE - i
LEFT   JOIN order_items oi USING (order_id)
GROUP  BY i    -- effectively group by days
ORDER  BY i;   -- effectively descending dates


Log Shipping Monitor Server Instance We plan to incorporate Log Shipping for basic disaster recovery with reporting needs. Server A Source Database will log ship to Server B database. We will conduct log shipping every few hours.

Looking at setup, there is an option for "Use a Monitor Server Instance". Currently we do not have budget/time resources to setup third monitor server.

In that case, what is the second best recommendation for Monitor Server to store status/history of log shipping activities? On the source primary server or destination server ?

We are mildly concerned about performance/activity of Source Server. Sometimes CPU can get moderate high. So I think Destination Server is better option. Also, in case primary server fails, we have status/history of when last time transaction logs backup/shipped/restored. If monitor was on Primary server failed, we would not know this.

I am trying to research if there is any missing reason to place monitoring on Source Server.

If you are doing log shipping, I would highly recommend (and the best option I believe) you do have a separate monitoring instance. The monitoring instance could be in Express Edition so you don't have to worry about licensing. You just need to know that after you setup log shipping and you then in the future want to change the monitoring instance or add one, you may need to reconfigure your log shipping. The upside is that you can use the monitoring instance for multiple log shipping and have the view of all the primary/secondary databases status in one location. Once you completed the log shipping setup with monitoring instance, on the monitoring instance, right click on the instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.

For reporting side of things, you need to disconnect the users/clients when doing a restore or wait until no one is connected to the database (delay restore). So plan for it. Another thing to be mindful of is that you can't add indexes or statistics on secondary database during standby mode. Doable but personally i would not use log shipping for reporting (only for DR purpose).

Personally, I would consider and recommend transactional replication for reporting. This gives you more control on your reporting database as you can create indexes, stats, create objects, etc on the subscriber database.

HTH


Granting Select access to all tables within a specific Database in MS SQL I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012.
I have a special role set up with 3 users that are apart of that role. I would like to grant Select access to the role for all tables that are within 1 specific database.
The database in question is an archive database that has archive tables for each month for the past 12 years. When originally creating the role, I granted access by just running the following:
GRANT SELECT ON [dbo].[myarchivetable] TO myspecialrole

go
This worked great because I would just have to change the year and month and run it all as 1 script.
I am now finding out that they are needing Select access to every table in the archive database. I could do the above method, however Having to write all of that out 132 times, it would be very easy to make a mistake.
How can I easily add the myspecialrole to every table within that database?

If all your tables are in the dbo schema then this should work, you can just deny single tables if required

GRANT SELECT ON SCHEMA :: [dbo] TO myspecialrole


Upgrading the mysql server from 5.1 to 5.6? My server have CentOS6.  Currently I'm  are using the Percona 5.1.47 version.  Would like to upgrade to 5.6.

What is the best way to do, and what are the prerequisites to keep in mind before doing that.

Added this server is master-master replication topology.

I have a more direct way to upgrade from MySQL 5.1 to 5.6

The idea is to do the following:

Dump the mysql schema from MySQL 5.1 as pure SQL into a grants file

Use pt-show-grants
Emulate pt-show-grants

Dump the data from MySQL 5.1 without the mysql schema into a data file
Uninstall MySQL 5.1
Install MySQL 5.6
Load grants file into MySQL 5.6
Load data file into MySQL 5.6

CAPTURE DATA

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump ${MYSQL_CONN} --databases ${DBLIST} > MySQLData.sql

CAPTURE GRANTS

Here is my personal emulation of pt-show-grants to capture SQL Grants

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQL="${SQL} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQL}"|mysql ${MYSQL_CONN} -AN|sed 's/$/;/g' > MySQLGrants.sql

Here are my posts where I discussed these things before

Mar 26, 2015 : mysql upgrading from 5.1 --> 5.6 do I have to do mysqldump before upgrade?
Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

GIVE IT A TRY !!!


creating a historical database, need help making a decision I'm working with 5 processes that take about 8 hours to run, during each 8 hour process, it changes its location at random times but in a specific order. There could also be all 5 processes running at the same time.

I'm trying to track when the process starts, when it changes locations and when it stops, easy right.

I have gone back and forth trying to decide on a solid scalable design, i came up with the two listed below, I wanted to get everyones thoughts on why or why not either should be used. Any tips or suggestions are greatly welcomed

** The table consists of five columns: id, location, operation, started, finished.

when the process starts, id, location, operation and started are inserted into DB, finished is left NULL. When the process goes to the next location, the table is queried for the last operation at the location with a NULL finished time. Then the finished time is updated and then the next location is inserted into the database.

** I don't like this design but its quick and easy, the only problem i see is if somehow a row does not get closed or finished and there are two operations with the same location with Null finished times.

Same as number one except for when a row is inserted, i get the id of the last inserted row and save it in a variable, then once the process has moved locations i can simply update the row per its id.

** I like this design but since the process can run for 8+ hours, if the program crashes or if there is a pc issue i could lose the id and the script will fail.

i thought about breaking out the start and finished into their own separate tables but i am not in favor of this design unless someone can convince me otherwise.

Thanks for the help and suggestions!

Your first design is the way to go.  Keep everything about a process on a single record.  Your challenge isn't one of database design, it's one of restartability/disaster recovery.

Since your processes are long-running, you have a higher risk of the data getting out of sync with the processes that are being tracked in the data.  For example, you lose power causing a process to crash.

The solution is that you need to change your processes so that when they start they confirm what the data says.  Does the database say that process X is still unfinished?  Is process X still running?  If not, your data is out of sync and you need to mark the record for process X as failed.

You might want to consider adding a couple of columns to your first table design, for example:

last_update - the date/time when the record was last updated.  Update this periodically during your long-running process.
status - keep a code to indicate "Active", "Complete", "Failed"


MongoDB 3.4 Server not starting with bind error following a CentOS 7.4 security update MongoDB 3.4 server is failing to start following a CentOS security update and reboot.

No configuration changes made. The previous update and reboot a couple of days earlier did not cause any problems. Could the CentOS update be the reason for the problem?

Many thanks, any thoughts are much appreciated!

Error messages in mongod.log

E NETWORK  [initandlisten] listen(): bind() failed Cannot assign requested address for socket: xxx.xxx.xxx.xxx:27017
E NETWORK  [initandlisten] Failed to set up sockets during startup.
E STORAGE  [initandlisten] Failed to set up listener: InternalError: Failed to set up sockets

The CentOS upgrade installed before the reboot

Installed:
  kernel.x86_64 0:3.10.0-693.17.1.el7

Updated:
  binutils.x86_64 0:2.25.1-32.base.el7_4.2                  device-mapper-persistent-data.x86_64 0:0.7.0-0.1.rc6.el7_4.1
  dhclient.x86_64 12:4.2.5-58.el7.centos.1                  dhcp-common.x86_64 12:4.2.5-58.el7.centos.1
  dhcp-libs.x86_64 12:4.2.5-58.el7.centos.1                 initscripts.x86_64 0:9.49.39-1.el7_4.1
  kernel-headers.x86_64 0:3.10.0-693.17.1.el7               kernel-tools.x86_64 0:3.10.0-693.17.1.el7
  kernel-tools-libs.x86_64 0:3.10.0-693.17.1.el7            kmod.x86_64 0:20-15.el7_4.7
  kmod-libs.x86_64 0:20-15.el7_4.7                          kpartx.x86_64 0:0.4.9-111.el7_4.2
  libdb.x86_64 0:5.3.21-21.el7_4                            libdb-utils.x86_64 0:5.3.21-21.el7_4
  libgudev1.x86_64 0:219-42.el7_4.6                         net-snmp-libs.x86_64 1:5.7.2-28.el7_4.1
  systemd.x86_64 0:219-42.el7_4.6                           systemd-libs.x86_64 0:219-42.el7_4.6
  systemd-sysv.x86_64 0:219-42.el7_4.6                      tuned.noarch 0:2.8.0-5.el7_4.2

@smile2day, It's pretty clear from your error log

  E NETWORK  [initandlisten] listen(): bind() failed Cannot assign
  requested address for socket: xxx.xxx.xxx.xxx:27017

There is MongoDB 3.4 Server startup problem resist through bind() of IP, not from your  CentOS update.

May be you have mistake of configuration of code. As i am able to see here in one straight line you are writing the bindIp with port number.

As MongoDB BOL documented
Here should be separated in systemLog of mongod settings like such as

net:
   bindIp: 127.0.0.1
   port: 27017


How can I set up a read only odbc connection? I have a user that needs to do queries on an SQL Server 2008 database that I administrate. They have rights to edit the data, but I'd rather they weren't able to do it directly against the table (i.e., I'd prefer they'd use the application that they were given).

What I'd like to do is give them a Read Only ODBC connection, and hang an MS Access MDB/ACCDB off of it, and let them query to their heart's content without accidentally "updating" data.

Is there a way to do this, or am I stuck having to create a RO user for them to use, and then threaten them with much pain if they connect using their regular RW account? In which case why not threaten them with much pain for editing data outside of the application?

Create a user in SQL Server with only the perms you want them to have.
Set up the user ODBC string to use this credential
Profit :)


Service pack and cumulative update Currently these updates are available for SQL Server 2014:

SQL Server 2014 SP1 CU2
SQL Server 2014 SP1 CU1
SQL Server 2014 SP1

If I want to do a slipstream install:

Is it enough to download #1 only and do the slipstream install with it?

Or should I download #3, then do the slipstream install, then apply #1?

If you already have SQL Server 2014 RTM installed

You can't do any slipstreaming - service packs don't support that, only initial installs.
(note: you cannot vote for the suggestion linked directly above as it was never migrated to the newer "Feedback" system; the link merely takes you to an archived version of the request that used to exist)

You can't apply an SP1 CU without first applying SP1.

Cumulative updates are cumulative - if you eventually want to get to SP1 CU2, you can ignore SP1 CU1 altogether.

So, in that case, you would have to download SP1, install it, and then apply Cumulative Update #2.

If SQL Server 2014 is not already installed
You can slipstream in this scenario, so that the system starts for the first time running at SP1 CU2, and no further installs are necessary. Download SP1 and SP1 CU2, extract them to a single folder, and point setup at them, e.g.:
setup.exe /Action=Install /UpdateSource=C:\UpdateFolder

If you are using versions older than 2012
All of the above still applies, however in 2008 and 2008 R2, a single /UpdateSource is not supported; you want to use the following instead, with /PCUSource for the extracted service pack files, and /CUSource for the extracted cumulative update files:
setup.exe /Action=Install /PCUSource=C:\SP1Folder /CUSource=C:\CU2Folder

Other info
Peter Saddow documented the slipstream process here, long ago, but this did not deal with SP + CU scenarios:

Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1

I talk about it a bit more here (and the advice for 2012 will stand for 2014, and from what I've heard so far, 2016 as well):

SQL Server 2012 Service Pack 1 & Cumulative Update 1

For slipstreaming to work, you will have to extract the CU file you get from the hotfix download server (in this case 486657_intl_x64_zip.exe), and make sure that the update source folder has the extracted files.