Retrieve total available days in date range compared against multiple records Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates?

| ID | code  | date_arrival | date_departure
| 4  | APT01 | 2015-04-10   | 2015-04-15
| 5  | APT01 | 2015-04-22   | 2015-04-29
| 6  | APT02 | 2015-04-11   | 2015-04-19
| 8  | APT02 | 2015-04-20   | 2015-04-25

For example: The user enters 2015-04-16 as their start date and 2015-04-28 as their end date. The available days over this period are 16th - 21st using APT01, and 26th - 27th using APT02

This means there are 8 available days out of those requested by the user. How can I query the data and get 8 as my result?

First solution

Well, I tried a solution. It works but it is pretty ugly. But it works...

SELECT count(*)
FROM (
    SELECT code, dates.selected_date
    FROM appartments
    INNER JOIN (select * from
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates
    WHERE (code, selected_date) NOT IN (
        SELECT code, dates.selected_date
        FROM appartments
        INNER JOIN (select * from
          (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
          ) dates ON dates.selected_date between date_arrival and date_departure)
    GROUP BY code, dates.selected_date) available_dates_by_code

Change the date period in the line WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates.

Remove the first level SELECT FROM to get all dates of unoccupied apartments for dates between '2015-04-16' AND '2015-04-28'.

You may want to change the '2015-01-01' dates to something earlier (ie. CURDATE() if you're only working with future dates). This query will only return next 30 years dates past '2015-01-01', so change it to something like CURDATE() - '1 YEAR'

I'm very curious to see if someone have a better solution...

How it works

From the bottom to the top :

The first SELECT gets all occupied dates for all appartments.
The second SELECT gets all dates wanted and removes all appartment/date couples that are occupied.
The third select count the number of appartement/date couple available between the provided dates.

Second solution

SELECT
  (
    SELECT adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
    FROM apartments
    INNER JOIN (SELECT * FROM
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) -
  (
    SELECT count (code, dates.selected_date)
    FROM apartments
    INNER JOIN (SELECT * FROM
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
      ) dates ON dates.selected_date between date_arrival and date_departure
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) AS 'days_of_availability'

This one is much more simpler. The second SELECT counts the number of days existing for the two dates multiplied by the number of apartments. The third SELECT counts the number of occupied days for all the apartments. The top SELECT does (number of days) minus (number of occupied days).

Fun fact: it took me almost 30 minutes to get this query working. That's a shame.


Need help with Master Slave Replication I want to set up a Master-Slave Replication. Therefore I followed the MySQL manual. Once I was I wanted to test it. I've let me show the slave status with was as following;

Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 326
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1410
              Relay_Log_Space: 2176
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1051
               Last_SQL_Error: Error 'Unknown table 'Test'' on query. Default database: 'FOOD'. Query: 'DROP TABLE `Test` /* generated by server */'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

As you can see, the slave was waiting. That's the reason, why I made a test INSERT on the MASTER. As the INSERT did not show on my slave it made me think, that I was missing something while setting it up. Also if I want to uncomment the bind-address and restart the mysql-service, it fails to start.

Now I'll show you what I have done:

1. Edit my.cnf on Master ->
set server-id = 1
set binlog_do_db = database name
2. Restart mysql service on Master
3. Create user for replication:
CREATE USER 'slave'@'master IPv4' IDENTIFIED BY 'password';
4. Grant replication slave:
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
5. Restart mysql service on Master

Now on to the slave:

1. Edit my.cnf on Slave ->
set server-id = 2
2. Restart mysql service on Slave
3. Stop slave
4. CHANGE MASTER TO MASTER_HOST='Master IPv4', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000029', MASTER_LOG_POS=827;
5. Start slave

After this configuration the slave was showing: Slave_IO_State: Waiting for master to send event, but id did not receive any changes from the Master. Would be glad if anyone could help me with this.

I use MySQL 5.5.47 and Ubuntu 14.04.1

On the Slave:

stop slave
reset slave

On the Master

reset master

On the Slave

start slave
show slave status

If you can include info like which version/flavour of MySQL and OS you are using that will help people responding to your query.


Why should a SET TRANSACTION command show a long Duration when the CPU is low? I got enlisted to troubleshoot a performance issue on a SQL Server 2016 Enterprise Edition at a customer site and have been handed a SQL Profiler trace captured during a period of "poor" performance. One of my observations is that seemingly innocuous statements like SET TRANSACTION ISOLATION register a duration of the order of seconds.

In the image above, the StmtCompleted event reports a duration as well as a CPU of 0milliseconds but the BatchCompleted event reports a duration of 1907ms (I have translated the profiler duration to milliseconds by dividing it by 1000). Why should there be such a drastic gap in the duration reported by  BatchCompleted event and   the StmntCompleted event ? Also the BatchCompleted event reports a CPU of 15ms but not the StmtCompleted. Why should just sending an ACK back to the client need so much CPU or duration?  There are other statements too which report a high duration on the BatchCompleted with almost 0ms reported against the StmtCompleted event. There are sp_reset_connection events which show a CPU=0ms but high durations of the order of seconds. Ditto with BEGIN TRANS, COMMIT TRANS, SCOPE_IDENTITY etc. And to make complicate matters, there are many instances of the same commands which have executed within  acceptable duration of a few milliseconds during the same period. I am at a loss to come up with an explanation. Wondering if the stack exchange community can come up with a plausible explanation  for these.  The customer swears that the CPU usage & Network Usage as reported at the Operating System level were normal but admitted that the disk usage seemed higher than usual. But most of the commands mentioned above should not be affected by disk activity.

The site unfortunately does not do continuous monitoring. They run a profiler trace whenever they feel that the SQL Server is showing "slowness". Wait stats are not captured either and they periodically reboot the machine.

I am going to suggest that the customer snapshot wait stats in addition to a profiler trace. I am hoping that a very high "ASYNC NETWORK IO" wait shows up :).  Should I request for any other type of monitoring ? Any help appreciated.

For a statement like that one (and the others you mentioned), network I/O is not included in the statement_completed event.  Network I/O is included in the batch_completed event.  Your suggestion to look at ASYNC_NETWORK_IO waits seems like the best next step to me.

The statement_completed can include network I/O time if the statement being executed uses a "streaming"-style execution plan (a query that returns lots of rows via a scan of a single table or simple nested loops join).  This is discussed a bit at the end of my answer here: https://dba.stackexchange.com/a/244783/6141

Note that it might not actually be a problem with slow network traffic per se.  It could be that the application server is maxed out on CPU, or the application is (for one reason or another) unable to continue to receive the results from SQL Server for a second or two.  You might want to see if they can capture performance metrics on the app server during one of these problem times as well.

Also, consider suggesting that they stop using the READ UNCOMMITTED isolation level


Cannot log in with default user on Mac 10.10 I am running PostgreSQL on a Mac 10.10

  PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit

I am having trouble connecting to the _postgres Mac account. As far as I remember, the under Mac created account is not postgres but _postgres. I want to log in as that account to access Postgres and create a new role/database for some user on the system.

sudo -u _postgres psql

Throws me this:

  psql: FATAL:  role "_postgres" does not exist

While:

sudo -u postgres psql

gives me this:

  sudo: unknown user: postgres

So, how to login as the default Postgres user?!

Just try your normal user account.
bash# psql postgres.
Depending on how you installed PostgreSQL (homebrew, ports or Postgres.app) the default account is set to your account instead of a seperate one. Beside this, consider to update to 9.3.11


Firewall security and SQL Server I have been receiving messages in my log file saying :-

"Login failed for user 'sa': Reason Password did not match that for the login provided. [Client:xx.xx.xx.xx]"

the xx are actual numbers.

In the MS firewall, I've added my IP to the "Remote IP address" list on the Scope tab.

When I have gone back to the log file, the message has stopped appearing.  Instead it says "its starting up ReportServerTempDB", does this mean that I've inadvertently by adding my IP to the remote IP list allowed anyone in?

By putting only my IP address in the scope means I've blocked everyone else apart from me connecting to the remote database?

Ultimately, I would like remote access allowed only to my IP.  If that is not possible, I want a block on all remote access.

By putting only my IP address in the scope means I've blocked everyone else apart from me connecting to the remote database?

Correct.

  Exceptions that you add to the firewall can restrict the opening of
  the port to incoming connections from specific computers or the local
  subnet. This restriction of the scope of the port opening can reduce
  how much your computer is exposed to malicious users, and is
  recommended.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15#BKMK_additional_settings


DB Design - Different tables that share a common table - each has their own values I have a  Persons table. This table has two tables depended on, the Clients and Clerks tables, Persons has many Clerks and Persons has many Clients

Something like this, visually:

Persons
|     \

Clerks Clients

Clients and Clerks both need a relation with Locations table; both the Clients and the Clerks has a latitude, longitude combination and many other geographic related elements. I'm not sure about this approach: I cannot link the Clerks and Clients table with a one to one relationship, because Locations will have two foreign keys from different tables.

Persons
|     \

Clerks Clients

 \        /
  Locations

Is there another approach?

Is there another approach?

The alternative approach is to use the "Table-per-Hierarchy" design, and store all types of Persons in a single table, eg

create table Persons
(
  Id int primary key,
  PersonType char(6) check PersonType in ('Clerk','Client'),
  LocationId int references Locations,
  . . .
)


How can I use an array variable inside the IN operator for Oracle SQL? I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();.

I insert some varchars into it, and then attempt to use it with an IN operator.

set serveroutput on;
DECLARE
  TYPE arr_type is TABLE of VARCHAR2(11 BYTE);
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP;
END;
/

I get the error: local collection types not allowed in SQL statements on the line containing: SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;, but it doesn't make sense because if I comment out that line, my_array prints fine, which means TYPE arr_type is TABLE of VARCHAR2(11 BYTE);.

I suspect the issue is because I'm trying to use an unbounded array with the IN operator i.e. PARENT_ID IN my_array.

I tried wrapping the variable inside parenthesis, but it doesn't help. How can I use the IN operator with an array?

I understand that in this case I can use subqueries or joins, but I am wondering is it possible to use an array with an IN operator.

WHERE PARENT_ID IN my_array;

This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist.

So first you define the type:

CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE);

And after that, you can use the collection with the TABLE() function as below:

set serveroutput on;
DECLARE
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST
  WHERE PARENT_ID IN (select * from table(my_array)); -- <==========================

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP;
END;
/


VDI errors but backups are successful Our monitoring tools are showing SQLVDI errors, I think they're coming from the Event Viewer only. But the backups are showing successful in the SQL Server log files and we've successfully restored the backups.

The events look like this:

Event Log Module Status: 0
The Last Record Number of the eventlog type that current event entry belongs to: 0
# of duplicate events: 20.00
Events Source: SQLVDI
Category: None
Event ID: 1
User (If Applicable): N/A
Event Description: SQLVDI:
Loc=TriggerAbort.
Desc=invoked.
ErrorCode=(0).
Process=3056.
Thread=7460.
Server. Instance=MSSQLSERVER.
VD=Global\{08771828-3181-438B-A554-E02DA96EB3F2}7_SQLVDIMemoryName_0.
Event Log Name: Application
Event Log Type: error
Event Log Date Time: 2018-05-11 09:04:06
Notification Generated By: *0 Minute Delay - Priority 4 The Windows Event Log - SQL 2008 service transitioned from a Normal state to a Failed state at 2018-05-11 09:16:21 .

Can I safely stop monitoring SQLVDI errors?

Thank you for your time,

Thomas

This is just me, but no, I wouldn't ignore an event that said:

SQL 2008 service transitioned from a Normal state to a Failed state at 2018-05-11 09:16:21

That would be the kind of event I'd pay a little deeper attention to. If you Google for text involved in that error, you get KB articles like 2615182, suggesting that it's a Windows backup failing, not a SQL Server one.


How to validate negative values in query Hi I am using postgresql in my project. I want to make a validation that if query returns negative then i want to change output to null.
(to_char (NULLIF(coalesce(0::numeric,0) -
(coalesce(26776.94::numeric,0) -
(coalesce(2760::numeric,0) + (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::character varying as clds

it is giving -6.17 as output , I want to make it null

You could do something like this:
CASE
  LEFT
  (
    to_char (NULLIF(coalesce(0::numeric,0) -
    (coalesce(26776.94::numeric,0) -
    (coalesce(2760::numeric,0) +
   (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
    ,1                -- <<<---- the '1' gets the minus sign from the string!
  )
  = '-' THEN NULL
  ELSE
    to_char (NULLIF(coalesce(0::numeric,0) -
    (coalesce(26776.94::numeric,0) -
    (coalesce(2760::numeric,0) +
    (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR
END AS clds

I have to say though that this has to be one of the most horrible SQL constructs that I've ever seen!
I would give serious consideration to devoting some (lots?) of effort towards an in-depth refactoring!


SSRS Reports on WUXGA Displays We use WUXGA displays (1920 x 1200), and have lots of SSRS reports. Since these reports need to be printed once in a while the layout is adjusted to A4. However when the users look at the reports on the display around 40% of the screen is blank. Absolut waste of screen space. How can I adjust SSRS to use the full screen size for reports ?

If you are using the web interface to run and view the reports there is an option to set the zoom level by appending &rc:Zoom=Page Width or whatever percentage you want.

Also it can be configured in the SSRS configuration file see MSDN for details.


How to login in database if we have lost password? Possible Duplicate:
  gain admin access to sql express 2008

Is there any idea if we lost our password to login in database using sql express management studio?
Is there any place where username and password saved on installation time of any sql server?

You can connect to DB with local Windows authetication and change the sa password.


What is a Key/Value store database? I've been looking at the wikipedia page for NoSQL and it lists several variations on the Key/Value store database, but I can't find any details on what it means by Key/Value store in this context.  Could someone explain or link an explanation to me?  Also, when would I use such a database?

Are you familiar with the concept of a Key/Value Pair? Presuming you're familiar with Java or C# this is in the language as a map/hash/datatable/KeyValuePair (the last is in the case of C#)

The way it works is demonstrated in this little sample chart:

Color        Red
Age          18
Size         Large
Name         Smith
Title        The Brown Dog

Where you have a key (left) and a value (right) ... notice it can be a string, int, or the like. Most KVP objects allow you to store any object on the right, because it's just a value.

Since you'll always have a unique key for a particular object that you want to return, you can just query the database for that unique key and get the results back from whichever node has the object (this is why it's good for distributed systems, since there's other things involved like polling for the first n nodes to return a value that match other nodes returns).

Now my example above is very simple, so here's a slightly better version of the KVP

user1923_color    Red
user1923_age      18
user3371_color    Blue
user4344_color    Brackish
user1923_height   6' 0"
user3371_age      34

So as you can see the simple key generation is to put "user" the userunique number, an underscore and the object. Again, this is a simple variation, but I think we begin to understand that so long as we can define the part on the left and have it be consistently formatted, that we can pull out the value.

Notice that there's no restriction on the key value (ok, there can be some limitations, such as text-only) or on the value property (there may be a size restriction) but so far I've not had really complex systems. Let's try and go a little further:

app_setting_width      450
user1923_color         Red
user1923_age           18
user3371_color         Blue
user4344_color         Brackish
user1923_height        6' 0"
user3371_age           34
error_msg_457          There is no file %1 here
error_message_1        There is no user with %1 name
1923_name              Jim
user1923_name          Jim Smith
user1923_lname         Smith
Application_Installed  true
log_errors             1
install_path           C:\Windows\System32\Restricted
ServerName             localhost
test                   test
test1                  test
test123                Brackish
devonly
wonderwoman
value                  key

You get the idea...  all those would be stored in one massive "table" on the distributed nodes (there's math behind it all) and you would just ask the distributed system for the value you need by name.

At the very least, that's my understanding of how it all works. I may have a few things wrong, but that's the basics.

obligatory wikipedia link http://en.wikipedia.org/wiki/Associative_array


Query mail logs for xp_sendmail Using SQL Server 2008, I can query sysmail_log and other tables to see the results of sql emails.

Is anything remotely similar available for querying email logs in SQL Server 2000?

Using xp_sendmail I believe the only history you will find is:

Critical events that were severe enough to warrant an entry into SQL Server's error log. This does not include things like bounces, undeliverables, malformed addresses, etc.
The history of the e-mail account that is used to start Outlook on the server. So for example you will see sent items there, and bounces / undeliverables in their inbox (if the account accepts incoming mail). This will depend, of course, on what's backing that account - e.g. Exchange.

There are many reasons this feature was deprecated in favor of Database Mail. Unfortunately you need to upgrade to take advantage of better functionality.


Internal Online Store Database Design Issue I am trying to create a database for an internal store that we are developing to manage our internal items (like phones, computers, office equipment, etc). The issue Ive run into is that they want each item to be able to support add-ons. The only issue I have with this is they also want the prices to be dynamic based on your region for both items and addons. So a different region may have a different price than some other region. The Items table right now supports this through a foreign key to the Availabilities table. What is the best practice to get the Addon's table to be a foreign key on the Availabilities table so that it to can have multiple prices per region? Is my design totally messed up here?

You'll can solve the problem by adding more linking tables. You can alter your design to resemble something like this:

CREATE TABLE Items
(
    itemID INT PRIMARY KEY IDENTITY(1,1)
    ,itemDetails NVARCHAR(100)
)
GO
CREATE TABLE Regions
(
    regionID INT PRIMARY KEY IDENTITY(1,1)
    ,regionName NVARCHAR(100)
)
GO
CREATE TABLE AddOns
(
    addOnID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnDetails NVARCHAR(100)
)
GO
CREATE TABLE ItemAddOns
(
    itemAddOnID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,addOnID INT
    ,CONSTRAINT [FK_ItemAddons_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
    ,CONSTRAINT [FK_ItemAddons_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)
GO
CREATE TABLE ItemAvailability
(
     itemAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_ItemAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_ItemAvailability_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
)
GO
CREATE TABLE AddOnAvailability
(
    addOnAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnID INT
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_AddOnAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_AddOnAvailability_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)

Something like that setup will allow you to easily query the cost for any Items and AddOns by any region. If AddOns are not restricted to particular Items you can omit the ItemAddOns table.


Database which was set up for logshipping is dropped I dropped an old unused database. Then I found it was set up to be the source database of a logshipping setups. And there are a lot failing jobs in our job running status report now.
How to remove the logshipping settings after the database is already removed?

In order to fix alert issues use database: msdb and

First of all find out details on Primary Server by executing following..

PRIMARY SEVERE

SELECT * FROM dbo.log_shipping_monitor_primary
where primary_database like 'CHANGE_DB_NAME%'

SELECT * FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

SELECT * FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Once verifies DB names execute

delete FROM dbo.log_shipping_monitor_primary
where primary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_primary_databases
where primary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_primary_secondaries
where secondary_database like 'DB_NAME_HERE%'

Follow same steps on SECONDARY SERVER

SECONDARY

SELECT * FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'

SELECT * FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

SELECT * FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

and once confirmed delete details from table by executing following....

delete FROM dbo.log_shipping_monitor_secondary
where secondary_database like 'DB_NAME_HERE%'

delete FROM dbo.log_shipping_secondary_databases
where secondary_database like 'DB_NAME_HERE%'

delete FROM [dbo].[log_shipping_monitor_primary]
where primary_database like 'DB_NAME_HERE%'

Hope this will help you to clear your unnecessary notifications from msdb database. It happens when you do not clear log-shipping clearly or in your case database dropped.


Transaction recovery and rollback As I understand:

UNDO: Undoing a write item operation consists of examining its log entry [write_item, T, X, old_value, new_value] and setting the value of item X in the database to old_value. UNDO is always performed in the reverse order from the order in which the operations were written in the log.

REDO: Redoing a write item operation consists of examining its log entry [write_item, T, X, new_value] and setting the value of item X in the database to new_value.

In a transaction recovery algorithm, for example the one outlined in the Q & A Understanding Transaction Recovery

and let's say we have a schedule such as:

R1(X) R2(Y) W1(X) C W2(Y)

where C denotes a crash. According to the algorithm, we should UNDO T1, but REDO T2, since it is still active at the time of crash. But T2 hasn't had any Write operations, so what exactly does an UNDO entail here? Does anything need to actually happen? And where does the concept of a rollback come in? Is that just implicit in an UNDO?

Assuming that R means Read and W means Write, then the work you're proposing is like this:

W1(x) C

That's it. Reads  are irrelevant for recovery. And a crash denotes the end, you can't have a write after crash. So the task of recovery is to redo the one write and then undo it, since there is no Commit.

The algorithm is very simple:

Redo everything, in forward order
Undo anything not committed, in reverse order, generating a compensating write for every action being undone.

A good explanation is the ARIES paper.

See also How to read and interpret the SQL Server log on my blog.


Converting MySQL 5.6.10 - 32 bit to 64 bit version I am currently using MySQL DB Version 5.6.10 for 32 bit running in 64 bit Window OS.I want to change it to 64 bit without taking any dump..Can anyone help in this?

The data file format is the same for 32-bit and 64-bit editions of MySQL.


Best way to perform backups with filegroups and then restore those backups Scenario:

Database consists of these file groups:

PRIMARY
xxTEST2
xxTEST3
xxTEST4

Database consists of the following files in those file groups:

xxTEST_RESTORE - Rows Data - PRIMARY - xxTEST_RESTORE.mdf
xxTEST_RESTORE2 - Rows Data - xxTEST2 - xxTEST_RESTORE2.ndf
xxTEST_RESTORE3 - Rows Data - xxTEST3 - xxTEST_RESTORE3.ndf
xxTEST_RESTORE4 - Rows Data - xxTEST4 - xxTEST_RESTORE4.ndf
xxTEST_RESTORE_log - Log - N/A - xxTEST_RESTORE_log.ldf

2 tables are created with row:

dbo.COUNTRIES
dbo.CUSTOMERS

Then a full database backup is performed, and rows are added to the Customers table.

Question:

Want to add 1 record and have a backup of this
Want to add a 2nd record and have a backup of that
Want to roll back to the previous time when the 1st record was added (Q #1 above)

Should I perform a TRANS LOG backup after adding the 1st record or a FileGroup backup?
What order of backups should I perform to ensure I can roll back properly as I keep getting errors that either the database is in restore mode, doesn't like the trans log I pick, etc....

Looking for the proper sequence for backup and then restore

You cannot restore a single filegroup to an earlier state, because that will leave the database in an inconsistent state.

You can restore a database one filegroup at a time. However, as long as a particular filegroup is not up to date with the PRIMARY filegroup, any access to a table that is stored (even in part) on that filegroup will cause the query to error out. Only after all filegroups are restored to the same level (meaning to the same "log sequence number") can the entire database be accessed.


How to run a mysqldump from a .txt or .sql file inside a command line window? If I run the follow line directly in command line window it will works good.

C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql

How can I run a .txt or .sql file named testDump.txt or testDump.sql from my cmd line window?
Inside this file we will have the same line:

C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql

The commandline mysql allows \! to do what you want, but you appear to be in Windows, which does not really allow for such a feature.

So, No, you can't do it.

For reading INSERT statements (etc) back in there is the source command.

Can you turn things inside out?  You can get a list of databases, or otherwise create MySQL commands, from

mysql ... -e "statement..."

and you redirect that to a .bat file, which could then be executed.

It seems like you need a .bat file.


Does MySQL support defining a table inside a session without appending it to the database? As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?)
I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2) user variables, nevertheless neither can be a whole table.

Source:

https://stackoverflow.com/questions/13670659/mysql-local-variables

EDIT: I think that using the 'temporary' flag in 'create' is the answer
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

from the docs: https://dev.mysql.com/doc/refman/8.0/en/create-table.html

You are right it is a like a local table to the connection.
According to the link you have under the subheading Temporary Tables, it says:

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed

Simply put, once the DB Connection terminates (normally or abnormally), the table disappears.
Also, please be aware that only the DB Connection that created the temporary table can access it. If 10 different DB Connections perform CREATE TEMPORARY TABLE, each DB Connection would have its own table. No other DB Connection can access it.


How to convert following mysql query to oracle? I have a mysql query like following, Column time contains date in long format.

SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time,
SUM(CreatedInstanceCount) AS CreatedInstanceCount , SUM(InitializedInstanceCount) AS InitializedInstanceCount ,
SUM(ActiveInstanceCount) AS ActiveInstanceCount,
SUM(TerminatedInstanceCount) AS TerminatedInstanceCount
FROM MEMBER_COUNT WHERE ApplicationId='test'
AND Time > ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000-'604800000'
AND Time <= ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)
GROUP BY FROM_UNIXTIME( CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i'), ApplicationId

So far my oracle query is like this but the results are not same.

SELECT to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI')  "Time", SUM(CreatedInstanceCount)
AS "CreatedInstanceCount", SUM(InitializedInstanceCount) AS "InitializedInstanceCount", SUM(ActiveInstanceCount) AS "ActiveInstanceCount",
SUM(TerminatedInstanceCount) AS "TerminatedInstanceCount" ,ApplicationId
FROM MEMBER_COUNT
WHERE ApplicationId='test' AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI') +(time)/1000/60/60/24 between sysdate-7 and SYSDATE
GROUP BY to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI'), ApplicationId
order by to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI') ;

This is my create table statement:

CREATE TABLE MEMBER_COUNT(Time number(20), ApplicationId VARCHAR2(150), ClusterAlias VARCHAR2(150), CreatedInstanceCount int, InitializedInstanceCount int, ActiveInstanceCount int, TerminatedInstanceCount int);

The problem is no of rows returned by oracle query are higher than the mysql query. I believe this has to do something with CEILING function.

Please help.

I found the query:

SELECT TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') "Time",
  SUM(CreatedInstanceCount)     AS "CreatedInstanceCount",
  SUM(InitializedInstanceCount) AS "InitializedInstanceCount",
  SUM(ActiveInstanceCount)      AS "ActiveInstanceCount",
  SUM(TerminatedInstanceCount)  AS "TerminatedInstanceCount"
FROM MEMBER_COUNT
WHERE ApplicationId='wso2is-500-application'
AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI')              +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 BETWEEN sysdate-7 AND SYSDATE
GROUP BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI'),
  ApplicationId
ORDER BY TO_CHAR(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(ceil( TIME/ (20160  * 1000)) * 20160 )/60/60/24 , 'yyyy-mm-dd HH24:MI') ;


Which database to use when you have a 6 billion of rows inside and need to query rows with from list of IDs? We are currently researching our case of storing the road distances between cities. Right now we have 6 billion of those distances.
Our structure right now in SQL Server is that we have a float which represents the relationship between cities.  For example if we have a city with Id 1 inside the Locations table and a city with Id 2 inside the same table, a row with distance from 1 to 2 will look like 1.2,'1000 miles'. That column is indexed.
So to get the distance from the city 1000 to the city 2535, we would find 1000.2535 inside the Distances.
Besides getting single distances, we need to select groups of 1000 distances from those 6 billion rows:
SELECT
  id
 ,distance
FROM
  Distances
WHERE
  id IN (1000.2535, 1.2, ...)

Right now we've only tested SQL Server on a local machine and it gives us around 300 ms for such query of 1000 rows, but only when we set a 50 ms timeout (this is needed for a lot of parallel requests from multiple users), if 50 ms timeout is not used it just grows exponentially like 300 ms for the first, 500 ms for the second, 800 ms for the third, etc.
And right now we taking a look at ElasticSearch specifically for mget.
So my questions are:

Which database would you recommend for such a use case?
What would you recommend besides what we've thought of maybe some other ideas like splitting into two different columns cities IDs, etc?
What would be the best ways to optimize such a database?

My assumption here is using FLOAT in place of the actual composite key is slowing the query engine's ability to create the proper estimates.
So instead of using the "clever" column, just use a composite primary key:
CREATE TABLE Country
(
  CountryCd  CHAR(3)      NOT NULL  --ISO Country Code
 ,Name       VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_Country PRIMARY KEY (CountryCd)
 ,CONSTRAINT AK_Country UNIQUE (Name)
)
;
CREATE TABLE Subdivision  --State/province/region/county etc.
(
  CountryCd      CHAR(3)      NOT NULL
 ,SubdivisionCd  CHAR(3)      NOT NULL  --ISO subdivision code
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT FK_Subdivision_Division_Of_Country FOREIGN KEY (CountryCd) REFERENCES Country (CountryCd)
 ,CONSTRAINT PK_Subdivision PRIMARY KEY (CountryCd, SubdivisionCd)
 ,CONSTRAINT AK_Subdivision UNIQUE (CountryCd, Name)
)
;
CREATE TABLE City
(
  CityId         INT          NOT NULL
 ,CountryCd      CHAR(3)      NOT NULL
 ,SubdivisionCd  CHAR(3)      NOT NULL
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT FK_City_Located_In_Subdivision FOREIGN KEY (CountryCd, SubdivisionCd) REFERENCES Subdivision (CountryCd, SubdivisionCd)
 ,CONSTRAINT PK_City PRIMARY KEY (CityId)
 ,CONSTRAINT AK_City UNIQUE (CountryCd, SubdivisionCd, Name)
)
;
CREATE TABLE CityRoadDistance
(
  SourceCityId       INT  NOT NULL
 ,DestinationCityId  INT  NOT NULL
 ,RoadDistance       INT  NOT NULL
 ,CONSTRAINT FK_CityDistance_From_City FOREIGN KEY (SourceCityId) REFERENCES City (CityId)
 ,CONSTRAINT FK_CityDistance_To_City FOREIGN KEY (DestinationCityId) REFERENCES City (CityId)
 ,CONSTRAINT PK_CityDistance PRIMARY KEY (SourceCityId, DestinationCityId)
 ,CONSTRAINT CK_CityDistance_SourceCityId_LT_DestinationCityId CHECK (SourceCityId < DestinationCityId) --Enforces only one record per city pair
)
;

Easy enough to find the single distance:
SELECT
  RoadDistance
FROM
  CityDistance
WHERE
  SourceCityId = 7
    AND DestinationCityId = 235

But as you said the secondary requirement is to return 1,000 in less than 50 ms.
You won't be able to use IN, so you will need to build a list for the WHERE clause (e.g. (SourceCityId = 7 AND DestinationCityId = 235) OR (SourceCityId = 1345 AND DestinationCityId = 2934)...).
This should allow the query engine to look at things and say: "Oh, I only need to perform 1,000 seeks" and return your data relatively quickly.  However for 50 ms or less you're going to need some very fast SSDs or the pages already cached in memory.
Alternatives would be something like an in-memory key-value store.  This would cut down on compilation and seek time somewhat, but I'd try using an actual composite key and seeing what happens.


How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS? I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When I try to join with information_schema.columns, I received errors below:

ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables.
Column "c.column_name" has unsupported type     "information_schema.sql_identifier".
Column "a.*" has unsupported type "pg_attribute".
Column "t.*" has unsupported type "pg_type".
Function "format_type(oid,integer)" not supported.
Function "format_type(oid,integer)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.

I tried to cast column_name into different types, but had no luck. Would anyone please advise what's wrong and how I can achieve this goal?

PG_TABLE_DEF

Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA.COLUMNS, Amazon sought to define their own proprietary version. For that they made available another function PG_TABLE_DEF which seems to address the same need. Pay attention to the note in the center about adding the schema to search_path.

  Stores information about table columns.

  PG_TABLE_DEF only returns information about tables that are visible to the user. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schemas.

  You can use SVV_TABLE_INFO to view more comprehensive information about a table, including data distribution skew, key distribution skew, table size, and statistics.

So using your example code (rewritten to use NOT EXISTS for clarity),

SET SEARCH_PATH to '$user', 'public', 'target_schema';

SELECT "column"
FROM dev.fields f
WHERE NOT EXISTS (
  SELECT 1
  FROM PG_TABLE_DEF pgtd
  WHERE pgtd.column = f.field
  AND schemaname = 'target_schema'
);

See also,

Official docs on Querying Redshift System Tables


SQL Server Resource Pool and Workload Groups Below is the scenario:

Scenario 1:
I got two workload groups assigned in the same resource pool with MAX CPU 20%. In the event of CPU bottleneck, the limit will come into effect with 10% each workload group?

Scenario 2:
I got two workload groups assigned with two different resource pool with MAX CPU 10% each. In the event of CPU bottleneck, each resource pool/workload group will get 10% each?

Is it both scenario will get the same resource allocation?

Yes, those two scenarios will result in the same resource allocation - in theory.

In scenario 1, if there is CPU pressure, the two workloads running in the "20% resource pool" will share that CPU "equally."  How equally they are able to share the 20% can depend on a lot of things that are workload specific, given how SQL Server's scheduling algorithms work.

In scenario 2, if there is CPU pressure, the two workloads running in the "10% resource pool"s will each get a minimum of 10% of CPU time.

You have a better chance of a "fair" split of CPU in scenario 2, but scenario 1 could work depending on your specific workload.


Unable to set general log to fifo pipe I'm trying to set my general log file to a fifo named pipe and am getting an error

$ sudo mkfifo -m 0666 general.fifo

mysql> set global general_log_file='/var/log/mysql/dradis/general.fifo';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/var/log/mysql/dradis/general.fifo'

mysql> \! ls -l /var/log/mysql/dradis/general.fifo
prw-rw-rw- 1 root root 0 Nov 27 14:27 /var/log/mysql/dradis/general.fifo

Version I'm running 5.6.20-68.0-log Percona Server.

How can I make this work?

It looks like it should work because the general_log_file is dynamic (according to the MySQL Documentation).

Some six(6) years ago, Joel Hanger answered How do I output MySQL logs to syslog?. In his answer, he added it to the my.cnf. Evidently, a mysqld restart would be needed.

In your particular case, you may have to enable the general log first using

SET GLOBAL general_log = 1;

to initiate a general log with the default name of hostname.log.

This will create an open file handle. You may then have to try setting the general_log_file with general_log on and off. You may also have to run FLUSH LOGS; to force the closing and reopening of log file handles.

This answer is just a lazy guess, so try it and see.


Rights to execute stored procedure, but not read underlying tables directly I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel.

Is it possible to make the user to get access only to the stored procedure, and not to the underlying tables and views?

Sure, you just grant rights to those users to the stored procedure, not to the underlying table.

CREATE PROCEDURE dbo.test -- always use schema prefix!
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t1.col1, t2.col2, ...
    FROM dbo.table1 AS t1
    INNER JOIN dbo.table2 AS t2
    ON ... ;
END
GO

GRANT EXEC ON dbo.test TO [user1], [user2], ...;

Here is a completely contained example that you can test. First, create a database (hopefully floob does not conflict with any database you already have):

CREATE DATABASE floob;
GO

Now, create two logins:

CREATE LOGIN floob1 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
CREATE LOGIN floob2 WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO

And create database users to go with them:

USE floob;
GO
CREATE USER floob1 FROM LOGIN floob1;
CREATE USER floob2 FROM LOGIN floob2;
GO

Now, create a table that they don't have access to by default, and a stored procedure that queries it:

CREATE TABLE dbo.whatwhat(id INT);
INSERT dbo.whatwhat(id) VALUES(1);
GO
CREATE PROCEDURE dbo.select_whatwhat
AS
BEGIN
  SET NOCOUNT ON;

  SELECT id FROM dbo.whatwhat;
END
GO

Now, grant execute rights to floob1 only:

GRANT EXEC ON dbo.select_whatwhat TO floob1;
GO

Now, try to execute the procedure and select from the table as floob1:

EXECUTE AS USER = 'floob1';
GO
EXEC dbo.select_whatwhat; -- works
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

You get a successful query resulting from executing the stored procedure, but an error message when trying to query the table directly, because you haven't been explicitly granted access to do so.

  Msg 229, Level 14, State 5
  The SELECT permission was denied on the object 'whatwhat', database 'floob', schema 'dbo'.

Now, try as floob2, who hasn't been given explicit access to execute the stored procedure, either:

EXECUTE AS USER = 'floob2';
GO
EXEC dbo.select_whatwhat; -- fails with error
GO
SELECT id FROM dbo.whatwhat; -- fails with error
GO
REVERT;

This time you get the same error on the SELECT, but also this error on the EXEC:

  Msg 229, Level 14, State 5, Procedure select_whatwhat
  The EXECUTE permission was denied on the object 'select_whatwhat', database 'floob', schema 'dbo'.

Don't forget to clean up:

USE master;
GO
DROP DATABASE floob;
DROP LOGIN floob1;
DROP LOGIN floob2;
GO

Regarding your comment:

  Is it possible to grant the users by using drop and click in Sql server instead of using T-SQL code?

It is, but that doesn't sound like a very good idea. Here is the process: you right-click the stored procedure in Object Explorer, hit Properties, move to the Permissions tab, hit Search..., type your username, hit OK, then check the checkbox where EXECUTE on the left meets GRANT on the top, and click OK. Now repeat for every stored procedure, and multiply that by 20 (all your users). Yeah, that sounds a lot easier than writing a script. Here is a better way to generate the script you need in one step, with a single copy, paste and execute instead of who knows how many points and clicks you'll need to do this interactively through the GUI:

DECLARE @xml NVARCHAR(MAX) = N'';

;WITH users AS
(SELECT name = 'user1' UNION ALL SELECT 'user2' ... UNION ALL SELECT 'user20')
SELECT @xml += N'GRANT EXEC ON ' + QUOTENAME(s.name) +
  '.' + QUOTENAME(p.name) + ' TO ' + QUOTENAME(u.name) + ';
'
FROM users AS u CROSS JOIN sys.procedures AS p
  INNER JOIN sys.schemas AS s ON s.[schema_id] = p.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

You could also add sys.database_principals to the mix to make sure you're getting the right set of users with no typos.


Restore Postgresql data after hdd crash My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this:

pg_dump: Error message from server: ERROR:  could not read block 2 of relation
base/16384/2607: IO error
pg_dump: The command was: SELECT tableoid, oid, conname, connamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = conowner) AS rolname FROM pg_conversion

It seems that the system catalog pg_conversion is broken. How can I fix this problem?

I'm sorry, I don't have a corrupt pg_conversion table to help perform any verifications. My answers here must, by necessity, be heavily based on speculation.

With the limited information we have, I am guessing that you are attempting to do a full database pg_dump, along the lines of

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... "database_name"

Alternative pg_dumps?

If your data tables are still OK, you could try to pg_dump the relevant schema(s)...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --schema "schema_name" "database_name"

or table(s), and use multiple table switches...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --table "table_name" "database_name"

With respect to these approaches, I have no idea if this will help, as I've simply never encountered a failing pg_dump for any other reason than a syntax error, or something else relatively benign.

Use COPY to make CSVs

As a last ditch effort, if you aren't able to pg_dump your schema or tables, in a compressed .backup format, you could always extract the data using the COPY command to make comma-separated variable (CSV) files.

COPY table TO '/folder/table_backup.csv' WITH FORMAT CSV;

Good luck, and let us know if you have any success.


Left outer join ruins query in MySQL I have two tables. One of them is big, long with more than 500 columns (most of those are tinyint) and 360000 rows. It contains info about photos. Second one is small with just three int columns (surrogate primary key and two foreign keys) and one decimal. It has just 45000 rows and contains info about what people are on those photos. It's so small it doesn't even have indexes. What I have trouble with is selecting photos within clause that have no people marked on them.
Query with clause within just first big table takes acceptable few seconds. Then I make something like that

SELECT photos.* FROM photos
 LEFT OUTER JOIN
 photos_people ON photos.photo_id = photos_people.photo_id
WHERE photos_people.photo_people_id is NULL AND ... LIMIT 150;

Now it suddenly takes literally minutes. What am I doing wrong? Is there a way to speed up query?

Add an index on the column photo_id  on both the tables- photos and photo_people.


Can I move / Modify the Object in Object Storage Service (OSS) I am using Alibaba Cloud - ECS and have Storage Bucket - Object Storage Service (OSS).

File Storage structure is like

oss:/bucket/abc/xyz/123.jpg/

My Question is

Can I move or modify the objects in OSS?

The stored files in OSS do not have a directory structure, and the OSS directory structure we see is a simulation with “/“.

If a file on OSS (oss:/bucket/123/456/789.jpg/)  ends with “/“, it is recognized as a directory.

That is why moving an object follows the same principle with renaming an object: you need to manually upload the object to the new path location, and delete the old file (to delete the directory, first delete the files under it).

For more details click here


Display Postgres server output in terminal and record to logs at same time I'm running Postgres 9.1 (Homebrew installation on Mac OSX) and I'd like to monitor my postgres server more closely.

My question relates to logs.  I'd like to get the logs displaying in a terminal pane.  Here's what the Postgres docs say about the logs:

"On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected to a file or piped to another process such as a log rotating program like rotatelogs; otherwise postgres will write its output to the controlling terminal (from the background) and will not leave the shell's process group. On Windows, by default the server's standard output and standard error are sent to the terminal. These default behaviors can be changed by using -l to append the server's output to a log file. Use of either -l or output redirection is recommended."

So, when I get my postgres server running with the following:

pg_ctl start -D /usr/local/var/postgres

The logs display in the terminal window.  When I run:

pg_ctl start -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log

The logs go to my logfile and don't display in terminal.

In short, it would be great if anyone can tell me what command I use after I've directed logs to the file (with the second command) to make the logs also appear at the command line.  It helps when I'm developing (in Django) to watch the SQL statements get executed in real time.

Just use tail -f /path/to/log and you'll be able to see the log contents in a terminal as well. You can use a terminal tool that can "monitor for activity" if you'd like to be notified when something happens.

A more complex solution would be to set PostgreSQL to log to rsyslog, and set rsyslog to log to a tty as well as a file


Shrink Transaction Log While Using AlwaysOn Availability Group We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database.

I have read here doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable. Anyway, the transaction log backup size is big and can be reduce using shrink file:

I have restore the database locally and perform the shrink operation. The log file size was reduces to 160 MB.

My question is on which database should I perform a shrink operation over the transaction log file (primary, secondary or both)?

I guess in the past for several years no back ups of the log file are made, so it become so huge. Executing DBCC SQLPERF (LOGSPACE) I can see that only 0.06% of the file are used - there is no point for me to keep such huge size of the log file. In [sys].[database_files] I check that its max_size is set to -1 with growth to 65536 so I guess when it need more space it will get. Anyway, I can shrink it to 5% for example in order to prevent future growth. I am trying to find some confirmation that I it is not bad idea to do so.

Actually, back ups (on the database and the log files) are performed only on the secondary databases, so it will be easier to perform the shrink file on them, but will the primary log file size be reduced as well?

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.

Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.


How to reclaim disk space after delete without rebuilding table? Our PostgreSQL 9.0 Windows production server is running low on space.

In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the space to the O/S.

We do not have enough space to do a full rewrite of the table, and all my reading of CLUSTER, VACUUM FULL and pg_repack is that they need to rewrite the table. My google-fu has been insufficient so far to find anything else.

Some downtime is acceptable (~2 hours), however the backup/restoration is too slow for our purposes, nor am I keen on dropping the database in between steps.

QUESTION: How can I return the disk space to the O/S without doing a full table rewrite?

(This is a production server so any solutions must be reputable/recommended/supported etc).

(P.S. A separate much larger disk is available which could be used, if the table could be rebuilt on it somehow. Tablespaces?)

If you use anything but CLUSTER / VACUUM FULL / pg_repack (which all manage locks automatically) you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes.

TABLESPACE

Yes, your last idea could work. Create a new tablespace on the other disk.

CREATE TABLESPACE ts1 LOCATION '/data/disk2';

Then create an optimized copy of the table in the new tablespace:

CREATE TABLE new_tbl
TABLESPACE ts1 AS
SELECT * FROM tbl
ORDER BY ....  -- ORDER BY is optional

The copy will be packed tightly without dead rows.

Then you might just drop the old table and rename the new one to keep using the new disk.

Or, if you have to keep it on the old disk for some reason, delete the old table, rename the new one and move it back to the default tablespace. This step is much faster now with:

ALTER TABLE tbl SET TABLESPACE pg_default

Per documentation:

  This form changes the table's tablespace to the specified tablespace
  and moves the data file(s) associated with the table to the new
  tablespace. Indexes on the table, if any, are not moved; but they can
  be moved separately with additional SET TABLESPACE commands. See also
  CREATE TABLESPACE.

Either way, don't forget to (re)create all depending objects. Indexes, foreign keys, views, ...

Well, you could use ALTER TABLE tbl SET TABLESPACE ... to begin with, but then the table would not be optimized as requested, just moved as is. But you would have enough wiggle room then to run pg_repack etc.

COPY

A complete backup / restore might take too long, but you could do that for only the table in question.

COPY tbl TO '/path/to/other/disk/tbl.pgsql';
TRUNCATE tbl;
COPY tbl FROM '/path/to/other/disk/tbl.pgsql';

The table is now packed tightly.

Temporary table

If you should happen to have enough RAM, you could do something similar with a temporary table in RAM. Would be much faster. Detailed instructions:

Best way to delete millions of rows by ID
How to delete duplicate entries?


Is it possible to limit the scope of a SYNONYM in SQL Server? I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against.

I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this:

DECLARE @database NVARCHAR(16) = 'ClientA';
DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database;
EXECUTE sp_executesql @statement;
// Run generic statements

This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts.

Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB?

EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.

Just switch the database context.

A handy way to do this is to call sp_executesql in the target database:

EG, with a static reference to the target database:

exec ClientA.sys.sp_executesql @job;

or dynamically:

declare @dbname sysname = 'ClientA';

declare @job nvarchar(max) = 'select db_name()';

declare @sql nvarchar(max) = concat('exec ', quotename(@dbname), '.sys.sp_executesql @job;');

--print @sql
exec sp_executesql @sql, N'@job nvarchar(max)', @job = @job;


What Is The Point of a Primary Key? Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than the regular cells?

Keys are for identification and data integrity. A key defines how tuples (rows) in a table can be uniquely identified. The integrity of keys is assured because the DBMS prevents users from entering duplicate information into the table. Database users can therefore rely on the keys to identify in the real world the things recorded in the database.

A "primary" key is fundamentally no different from any other candidate key in the same table. It's just a convention used to designate one key per table as significant in some way. Usually it is the "preferred" or "most important" key of a table and the one used in foreign key references.


Update Field Added From Alter Table Statement I am attempting to update a field that was just added from an ALter statement.  I am receiving an error of:

  Msg 207, Level 16, State 1, Procedure TestProc, Line 436
      Invalid column name 'discountamount'.

This is my syntax that I am attempting to use - what would be the proper way to update a field just added in a stored procedure?

ALTER TABLE [dbo].msnbc
ADD saleamount float,
discountamount float;

UPDATE ftri
SET ftri.discountamount = fji.Totaldiscountamount
FROM [dbo].msnbc ftri
INNER JOIN [dbo].halfbaked fji
ON ftri.saleid = fji.saleid

If you need to do this in a stored procedure, your update has to be dynamic.

Here's a demo in case you get lost on the way.

Create a simple table:

USE tempdb;

CREATE TABLE dbo.whatever
(
    Id INT
);

INSERT dbo.whatever ( Id )
VALUES ( 1 );

GO

This is about what your stored procedure will have to look like:

CREATE PROC dbo.dosomething
(
    @i INT
)

AS
    BEGIN

        DECLARE @sql NVARCHAR(MAX) = N'';

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'something' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD something FLOAT;
            END;

        IF NOT EXISTS (   SELECT *
                          FROM   sys.columns AS c
                          WHERE  OBJECT_NAME(c.object_id) = 'whatever'
                                 AND c.name = 'soforth' )
            BEGIN
                ALTER TABLE dbo.whatever
                ADD soforth FLOAT;
            END;

        SELECT @sql = N'
        UPDATE dbo.whatever
        SET something = @s_i,
            soforth = @s_i
        WHERE Id = @s_i
        ';

        EXEC sys.sp_executesql @sql, N'@s_i INT', @s_i = @i;

    END;

There won't be any errors because the dynamic SQL isn't compiled at this stage.

Then you can run it:

EXEC dbo.dosomething @i = 1;

And validate results:

SELECT *
FROM   dbo.whatever AS w;

+----+-----------+---------+
| Id | something | soforth |
+----+-----------+---------+
|  1 |         1 |       1 |
+----+-----------+---------+

Hope this helps!


Copy SQL database with encryption from one Azure subscription to another I need to copy an entire Azure SQL Database from one subscription to another.

The challenge I have is that it has a certificate with symmetrical key and if I try to export it to a .bacpac file I get this error:

  SQL71626 (certificate/symmetric key is not supported in Microsoft Azure SQL Database v12)

Like all things IT there are probably 101 ways of doing this, and a number of sites explain some options (but none that I have found mention encryption and how best to deal with it).

Some additions info:
The destination subscription already exists
It has a SQL Server, I can add more if required.

Yes, you can Copy the SQL database with encryption from one Azure subscription to another. But you have to move your database along with an Azure SQL Server.  Not just the database itself. You can follow my steps as a workaround.

I tested this successfully with the following steps:

Subscription 1 Create a new resource group, an Azure SQL Server and Azure SQL database which is empty.
Create a certificate and symmetric key. Example from Microsoft documentation. Certificate and Symmetric key.

CREATE CERTIFICATE Shipping04
  ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
  WITH SUBJECT = 'Sammamish Shipping Records',
  EXPIRY_DATE = '20201031';
GO

CREATE SYMMETRIC KEY JanainaKey09
 WITH ALGORITHM = AES_256
 ENCRYPTION BY CERTIFICATE Shipping04;
GO

Create another resource group in the same subscription as the staging area. Create a new Azure SQL Server and copy the database once the backup is available. You need this because when you move the resource they will not available at source after the move.

 Restore-AzSqlDatabase `
-FromPointInTimeBackup `
-PointInTime (Get-Date).AddMinutes(-10) `
-ResourceGroupName $Database.ResourceGroupName `
-ServerName $secondaryServerName `
-TargetDatabaseName $secondarydatabaseName `
-ResourceId $Database.ResourceID `
-Edition "Premium" `
-ServiceObjectiveName "P1" }

Move Azure SQL Server and refreshed database to a different subscription. You can move to an existing resource group and create a new one. Use Move-AzResource command. Details here and here.
Now copy the database to your existing SQL Server.
Delete the resources in step 4.

If you try to move only the database this the error message you get.

"properties": {
        "statusCode": "BadRequest",
        "serviceRequestId": null,
        "statusMessage": "{\"error\":{\"code\":\"InvalidResourceMoveRequest\",\"message\":\"The list of resources in move definition cannot be null or empty.\"}}"
    },


Can statistics affect the query logic instead of performance? Is it possible that the frequency of rebuilding indexes (which automatically triggers statistics update) can affect the query logic?

One of the developers I work with claims that a stored procedure failed (there was a type conversion error) because the statistics have not been up to date during the query execution and it used the wrong query plan. He also claims that after having updated statistics the procedure works fine.

Is it even possible?

At least I think it is possible that the procedure gets a different plan and that way it doesn't touch the rows that have "invalid" data, for example numbers or dates stored as varchar that aren't correct and then conversion logic in the procedure.

For example if you're fetching with both date range and customer code, and some of the data for that customer causes the error, but is outside the date range -- so, if the plan uses date range, it works ok, but if it fetches all the rows for the customer, then does something related to the conversion and then last compares the date range, then it fails.

I haven't tested this, but I would assume that could happen, but of course the data / procedure should be fixed.


Insert records from one table to other with default serial I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy:

insert into all_roads select * from new_roads where add_road = 1;

But with serial as first column in both I do get error:

ERROR:  duplicate key value violates unique constraint

I really do not want to copy serial number from one table to the other. I rather use default serial value for all new inserted records. Is there a way without writing insert which manually lists all the columns except the first - serial?

You could list out the non-gid columns in your SELECT:

edb=# create table foo (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# create table foo_new (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# insert into foo values (default, generate_series(1,100)::text,'mytext');
INSERT 0 100
edb=# insert into foo_new values (default, generate_series(1,100)::text,'yourtext');
INSERT 0 100
edb=# insert into foo_new select * from foo where id = 1;
ERROR:  duplicate key value violates unique constraint "foo_new_pkey"
DETAIL:  Key (id)=(1) already exists.
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo where id = 1);
INSERT 0 1
edb=# select * from foo_new where col2 = 'mytext';
 id  | col1 |  col2
-----+------+--------
 101 | 1    | mytext
(1 row)

You could also try creating a temporary table, then drop the gid column, and then copy that table into the new table:

edb=# create temporary table foo_temp AS SELECT * FROM foo;
SELECT 100
edb=# alter table foo_temp drop column id;
ALTER TABLE
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo);
INSERT 0 100
edb=# select count(*), col2 FROM foo_new group by col2;
 count |   col2
-------+----------
   100 | yourtext
   100 | mytext
(2 rows)

If you don't want to list out all the columns by hand, because you have too many, I suppose you can use a script to create a listing of all the non-gid columns, and then copy/paste into your INSERT statement:

edb=# select array_to_string(ARRAY(SELECT column_name::text
                                     FROM information_schema.columns
                                    WHERE table_name = 'foo'
                                      AND column_name::text NOT IN ('id')),',');
 array_to_string
-----------------
 col1,col2
(1 row)


MySQL cluster DataMemory and DiskPageBufferMemory parameters I want to setup a MySQL cluster.

The amount of data is more than double the amount of RAM.
I have 3 Data Nodes each with 8GB RAM.

QUESTIONS

What is a good rule for the DataMemory and DiskPageBufferMemory parameters?
Are 3GB DiskPageBufferMemory and 2GB DataMemory good values?

DISCLAIMER : Not SCMCDBA Certified
If you plan to hold data in memory, you need to

increase RAM on all data nodes to 16GB
increase the DataMemory to 6GB
increase the number of SQL nodes to 3 (Makes SQLNodes to DataNodes 1:1)

If you cannot make these changes, then you should offload data to local storage

MySQL Cluster -- Storing table data on data nodes

Here is one of the best answers from Frazer Clement : MySQL Cluster RAM requirement
He states in his answer

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system. Some physical memory should be reserved for the OS and other processes.

There are three MySQL Cluster Gurus in this forum

Frazer Clement
Mat Keep
Andrew Morgan

Checks out their answers !!!


I need to update a Oracle procedure via a .sql script I need to update a Oracle procedure via a .sql script. I know the procedure works because it is currently running on our server, but I need to update the procedure on a client's box.

I need to be able to hand them a .sql script which they will open and execute with Oracle SQL Developer, which will update the procedure, but when I try to create the .sql file and run the .sql file it fails, because it's trying to run the procedure.

I don't want the procedure to be run, but rather be created or replaced with the following updated procedure:

    CREATE OR REPLACE PROCEDURE updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;

Just save the ddl for the procedure. This can be done using sql-developer 'save to file'. Tell the client to start sql-developer, connect to the schema where the code should be written and have them run the script using 'start' or the equivalent for start '@' script_name
The sql script should be located in the directory defined in the preferences for Database>Worksheet>'select default path to look for scripts'

@your_script

or

start your_script

It works from the worksheet, very similar to sql*plus.


If dbo.DatabaseIntegrityCheck fails, what happens, i.e. how am I notified? I'm new to Ola Hallengren's MaintenanceSolution (which looks fantastic), and DBA-ing generally. I've created a job to run dbo.DatabaseIntegrityCheck (weekly against ALL_DATABASES). If there are errors found by DBCC CHECKDB does this cause the proc to error and therefore the Job to fail, so I'll get a notification in the normal way from my job failing? Will it continue to verify the integrity of all the other databases or does it stop on the first problem DB it finds?
I couldn't find any notes on the overview, sorry if I missed something obvious.

The job will continue. And it will fail (in the end), so it is up to you to get notified when a (this) job fails. For instance send an email on job failure.


sql server database sharding - what to do with common data / non sharded data We have a very large scale enterprise level database.  As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows.  sql proc optimization has been performed and hardware has already been scaled up to a very high level.

We are looking to shard the database now to ensure that we can handle company growth and future loads.

We have decided what particular data should be sharded. It is a subset of our database which is highly utilized.

However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an  Inventory table for instance or possibly an Employee table, user table etc .

I see two options to handle this common/universal data:

1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs.

2) design 2 - Give each shard its own copy of all common/universal data.  Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards.

concerns about design #1

1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update  a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily.  The data now exists on seperate sql instances and databases.   You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data.

2)a loss of referential integrity.  Not possible to do cross database referential integrity.

3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards.

4). increased database trips.  Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases.  Some network latency here but I am not worried about this issue as much as the above 3.

concerns about design #2

In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards.  the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively.

I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see.

thank you in advance.

Your question focused on this:

  However, my question is regarding the non sharded data which is
  common/universal. An example of data like this may be an Inventory
  table for instance or possibly an Employee table, user table etc .

When you're doing sharding, and you have data that all of the shards need to see, you have to classify that data with a few attributes:

Does it change frequently? In your examples, you listed Inventory, Employee, and User. Typically inventory changes very fast, but the Employees records only change periodically (say, a few hundred updates per day).

How much delay can each shard tolerate? Even though the Inventory may constantly be changing, you can typically tolerate a large amount of delay (minutes or even hours) on a table like that. If you're selling unique items with a very limited quantity that you can never restock (think original artworks), then you don't shard that data at all - you only query the original database. However, in most online stores, you're not selling out of every item every day, and you're going to restock things quickly anyway, so you don't really need up-to-the-millisecond counts of inventory. In fact, in most cases, you only need an In-Stock flag that's either 0 or 1, and a central process updates that flag. That way, you don't have to push every up/down bump of item count out to every shard. Employee or User data, on the other hand, may need to be pushed out to every shard nearly instantaneously.

Will you be joining from the sharded tables to the non-sharded ones? Ideally, the answer here is no - you should make two separate queries to get the data, and then join them on the app side. This gets a lot harder from an app perspective, but it gives you the capability to get the freshest data from each source.

Is this original data, or copied? Another way to think of this question: what do you need to back up, and how frequently? Typically in a high-volume sharding environment, you want the backups to be as fast and as small as possible. (After all, you need to protect each node, and you want all of the shards to fail over to DR at the same point in time - not have some shards with newer data than others.) This means the sharded data and the non-sharded data should be in completely separate databases - even if they're on the same server. I may need constant transaction log backups of my sharded (original) data, but I may not need to back up the non-sharded data at all. It's probably easier for me to just refresh my Employees or Users table from the single source of truth rather than back it up on every shard. If all of my data is in a single database, though, I lose that capability.

Now, about your concerns:

"Transactional issues...you will no longer be able to do this easily." Correct. In sharded scenarios, throw the concept of a transaction out the window. It gets worse, too - for the sharded data, you could have one shard up and online, and another shard down temporarily due to a cluster instance failover or restart. You need to plan for failure of any part of the system, at any time.

"Not possible to do cross database referential integrity." Correct. When you split a single table out across multiple servers, you're putting your big boy pants on and telling the database server that you're taking over for tough tasks like point-in-time backups, relationships between tables, and combining data from multiple sources. It's on you and your code now.

"Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards." Correct here as well. There's no easy button for this, but once you've built this into the app, you're able to scale like crazy. I'd argue that the easier way to do this is to split the app's connections by reads.

"increased database trips." - Yes, if you break the data into multiple servers, the app is going to have to reach out to the network more. The key is to implement caching as well so that some of this data can be stored in lower-cost, higher-throughput, lock-free systems. The fastest query is the one you never make.

I've also laid out more pros and cons to dividing up multi-tenant databases here, such as performance tuning on individual shards, different backup/recovery strategies per shard, and schema deployment challenges.


Reviewing Stored Procedure template for handling transaction? I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction.

I have a stored procedure to log error in catch block in a table.

How can I improve my stored procedure to handle transactional error or any kind of error
where stored procedure should not give error on screen.

Can this be improved to handle transaction in proper way or it sufficient?
First Begin tran block or Begin Try block?
Rollback in catch block if @@trancount > 0 sufficient?
Use of Xact_abort on in every Insert, delete, update transaction procedure is ok?

Code:

ALTER PROCEDURE [dbo].[DB_INSERT_MY_LOG]
(@Param1 varchar(30))
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE          @ErrNumber         NVARCHAR(100)
    ,@ErrDescription    NVARCHAR(4000)
    ,@ErrorProcedure    NVARCHAR(200)
    ,@ErrState          INT
    ,@ErrSeverity       INT
    ,@ErrLine           INT
    ,@ErrTime           DATETIME

    BEGIN TRAN
    BEGIN TRY
                   --Insert or Update or Delete or (Insert & Update together and vice  versa) & multiple insert into different tables.
    COMMIT TRAN
    END TRY

    BEGIN CATCH

    IF (@@TRANCOUNT > 0)
    ROLLBACK TRAN

    SET @ErrNumber          = ERROR_NUMBER()
    SET @ErrDescription = ERROR_MESSAGE()
    SET @ErrorProcedure    = ERROR_PROCEDURE()
    SET @ErrState           = ERROR_STATE()
    SET @ErrSeverity        = ERROR_SEVERITY()
    SET @ErrLine            = ERROR_LINE()
    SET @ErrTime            = GETDATE()

    EXEC    MyErrorLogInsert
    @ErrorNumber                = @ErrNumber
    ,@ErrorDescription          = @ErrDescription
    ,@ErrorProcedure            = 'DB_INSERT_MY_LOG'
    ,@ErrorState                = @ErrState
    ,@ErrorSeverity             = @ErrSeverity
    ,@ErrorLine                 = @ErrLine
    ,@ErrorTime                 = @ErrTime

    END CATCH

    SET NOCOUNT OFF;
    SET XACT_ABORT OFF;

It might be worth looking at XACT_STATE instead of @@TRANCOUNT.

From BOL:

  Both the XACT_STATE and @@TRANCOUNT functions can be used to detect
  whether the current request has an active user transaction.
  @@TRANCOUNT cannot be used to determine whether that transaction has
  been classified as an uncommittable transaction.

So there may be some cases where, even though there was an error, the transaction should still be committed to the database.

IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' +
          ' Committing transaction.'
    COMMIT TRANSACTION;
END;

Of course, it's entirely up to you whether you want to commit any transaction where an error was raised, even if it is committable... Just because you can, doesn't mean it's always a good idea!


MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong.

I have a table with ~200M records. MySQL table definition:

CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  t2_id int(11) NOT NULL,
....
  PRIMARY KEY (id),
  KEY index_t2 (t2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Request (returns ~30M):

SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

runs:

25,797ms MySQL (v5.7.11)

1,222,168ms PostgeSQL (v9.5)

Explain:

MySQL:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: index_t2
          key: index_t2
      key_len: 4
          ref: const
         rows: 59438630
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

PostgreSQL

Aggregate  (cost=4469365.02..4469365.03 rows=1 width=0)
 ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=0)
       Recheck Cond: (t2_id = 7)
       ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0)
             Index Cond: (t2_id = 7)

Server: AWS RDS (db.r3.xlarge) vCPU:4 Memory:30Gb

Updated (2016-09-20):

> explain (analyze, buffers) SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4469365.02..4469365.03 rows=1 width=4) (actual time=1213456.539..1213456.539 rows=1 loops=1)
   Buffers: shared read=2734808
   ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=4) (actual time=64015.828..1205542.421 rows=31383566 loops=1)
         Recheck Cond: (t2_id = 7)
         Rows Removed by Index Recheck: 108582028
         Heap Blocks: exact=19929 lossy=2606242
         Buffers: shared read=2734808
         ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=64009.598..64009.598 rows=31383566 loops=1)
               Index Cond: (t2_id = 7)
               Buffers: shared read=108637
 Planning time: 0.080 ms
 Execution time: 1213456.891 ms
(12 rows)

Time: 1213484.579 ms

Updated (2016-09-21):

> explain (analyze, buffers) SELECT t2_id FROM t1 WHERE t2_id = 7;
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=114) (actual time=59954.834..1234070.436 rows=31383566 loops=1)
   Recheck Cond: (t2_id = 7)
   Rows Removed by Index Recheck: 108582028
   Heap Blocks: exact=19929 lossy=2606242
   Buffers: shared hit=4824 read=2729984
   ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=59948.598..59948.598 rows=31383566 loops=1)
         Index Cond: (t2_id = 7)
         Buffers: shared hit=4824 read=103813
 Planning time: 0.086 ms
 Execution time: 1239826.408 ms
(10 rows)

Time: 1239827.053 ms

The way that both RDBMS do the count differs. In InnoDB we have the following behaviour by default:

  To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of
  the table, which takes some time if the index is not entirely in the
  buffer pool.

For Postgres, you may want to try to see if an index-only scan (which is closer to the InnoDB behaviour) can help you on this. More info here. Due the amount of rows and the bad cardinality of that value (almost 15% of the table according stats), I can't warranty that it will work, but you can try:

SELECT COUNT(t2_id) FROM t1 WHERE t2_id = 7;


Are the MariaDB and MySql drivers interchangeable when connecting to a MySQL backend Aurora cluster? I saw a similar question asked before but I didn't get enough supporting information

Does anyone know any new information on this?

Can MariaDB connector (driver) be used instead of the MySQL connector (driver) when connecting to AWS Aurora cluster MySQL instance?

Can MariaDB connector (driver) be used instead of the MySQL connector (driver) when connecting to AWS Aurora cluster MySQL instance?

Yes. I've personally used neither the MariaDB Connector/J nor the MySQL Connector/J against AWS Aurora cluster MySQL. However, according to the MariaDB documentation on its Connector/J - my emphasis:

  MariaDB Connector/J is used to connect applications developed in Java
  to MariaDB and MySQL databases using the standard JDBC API. The
  library is LGPL licensed.

So, MariaDB's Connector/J officially supports MySQL databases, and Amazon Aurora MySQL is supposed to be a drop-in replacement for MySQL.

Also, note that according to the same documentation, MariaDB Connector/J has a special 'aurora' failover parameter. See MariaDB's Knowledge Base article Failover and High availability with MariaDB Connector/J for more details.

I've not found anything specifically mentioning Aurora in the MySQL Connector/J documentation, so it would seem MariaDB's connector could actually be a better choice.

It should also be mentioned that the licences are different: MariaDB Connector/J is LGPL v2.1+ and MySQL Community Connector/J is GLPv2 with a few additions.


How do I index two table having a one-to-many relation but only the latter is being frequently updated? Take the following two tables:

table A has ~100k rows and 50 columns, and it is being updated only twice per month, how long this update takes is not an issue (as long as it doesn't take more than one day, which I think is unlikely :D). It will grow very slightly, not more than 10k rows per year.
table B has ~25k rows and 25 columns, column F is a foreign key to column C of table A. This table is being updated 100-1000 times a day, most of the time to add new rows, so it will easily grow to hundreds of thousands of rows.

Table A has a one-to-many relation to table B, both column F and column C must be not null, have their own index, and column C is also unique but it is not the primary key of table A. Every row of table B has a unique correspondence with a row in table A.

Both tables are being queried many thousands of times every day, individually or using their relation using an application that allows a few dozen different types of query.

Is it a good idea to index every column of table A that is involved in a query, plus making a composite index for every type of query? If no, what are the possible drawbacks?
For table B is it a good idea to make a composite index only for the most time consuming queries? Or should I make a composite index also for every column in table B that is being queried, since those queries may vary in the parameters but not in the form?

The thing about indexes are that they are excellent tools for speeding up queries, but they bring along overhead as well.

If you choose to index every column in a table that is involved in a query, you have chosen to store considerably more data.  This has an effect on the storage required and in the overhead of maintaining all those indexes.  (Or maintaining all columns in a very wide index.)
In past experience, having many indexes, especially several indexes with similar distribution statistics, can make it easier for the server to select a poor index and thus a poor plan.

My experience shows that indexing just about everything will hurt your performance overall, for the reasons mentioned above.

Both table A and table B should only contain the indexes that would be of best use for your queries.

Of course, your first indexing decisions may need to be refined over time as you learn more about your database's behavior.  Do not be afraid to add additional indexes when needed.  And do not be afraid to drop an index that is not being useful.


SELECT fields from table having some conditions I have two tables
1. Named as t1
2. Named as t2

example of the tables are :

t1 :

 id  name
 1   one
 2   two
 3   three

t2 :

 id  name
 2   five

I need to select all the data from the t1 table as well as from table t2 but I don't want to write data from table t1 which is updated

I need a resulting table like this:

 id  name
 1   one
 2   five
 3   three

Please help me to solve this in postgres

The cleanest has to be:

SELECT T1.id
       COALESCE(t2.name, t1.name) as name
FROM t1
LEFT JOIN t2
     ON t1.id = t2.id


Run PostgreSQL without admin / superuser I'm trying to setup a secure append-only database with PostgreSQL or MySQL. My idea for that is to generate a database where no admin/superuser accounts exists and only users with limited privileges can insert data to the database. The lack of superuser accounts should be no problem as the database will be setup completely from scratch if any admin changes need to be made.

My idea was to generate the database with an admin user (with an encrypted hash) and then immediately delete the admin user (or lock him out by setting the password to blank).

Is that a suitable approach?

This answer is about PostgreSQL.

You cannot drop a superuser unless you are a superuser yourself.
Nobody can drop the bootstrap superuser (normally postgres) because he owns the system objects.

Resetting the password won't prevent a user from logging in.

Keep the superuser around and don't allow it to connect.

For that, you could add the following lines at the beginning of pg_hba.conf:

host  all   postgres  0.0.0.0/0  reject
host  all   postgres       ../0  reject
# if you are truly paranoid and want to forbid local connections
local all   postgres             reject

Don't forget to reload PostgreSQL after that.


Implementing soft delete Could someone please show or describe to me how to implement soft deletes?

I have table of vouchers with these fields: id, username, password, serial.

I want to display a given number of rows depending on the customer's request
and once displayed they should be deleted so that the are not displayed again. (They are already invalid.)

I presume soft delete will ensure that no voucher is displayed twice.
If you have any other idea on how I would do it I would appreciate.

I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.

My table structure would be similar to the following:

create table myTable
(
  id int,
  name varchar(50),
  IsDeleted bit,
  DeletedDate datetime
);

The IsDeleted column has a default value of no and the DeletedDate is not populated until the row is actually marked as deleted.  I include a date column in the event I ever want to know when a row was soft deleted.

Then when you query your data, you will include this column in the WHERE clause:

select id, name
from myTable
where IsDeleted = 0

Note: You will want to include an index on the IsDeleted column.