SQL Availability Group I have a question in regards to SQL Standard availability groups.  I currently have an AG setup with one primary and one secondary replica.  I am using synchronous commit but was curious about asynchronous.  How far will/can the secondary replica be behind?  If the secondary replica goes offline for any reason will it catch completely up once it comes online? How long can the secondary be down before it will not catch up?  I understand the difference between the 2 commit types (one verifies the transaction was committed before handing back to the application whereas the other commit type doesn't).  What I am not sure of is how long the secondary can be offline in an asynchronous commit type.

How far will/can the secondary replica be behind?

That depends on a number of factors.  Network speed between the 2 nodes, disk speed on the secondary, volume of data being transmitted.
Many of these KPIs are in the AG Overview dashboard (Right-lick the AG in SSMS and select 'show dashboard').  You can select a slew of different metrics including log send queue, log redo queue, estimated recovery time, estimated data loss, etc.  As stated in the comments below from scsimon, when in asynchronous, the secondary isn't every truly 'caught up'.  It will always show a 'synchronizing..' status and not 'synchronized'.

  If the secondary replica goes offline for any reason will it catch
  completely up once it comes online?

Yes.  Also worth noting that during the time where your secondary replica is unavailable/disconnected, you will not be able to back up any transactions from the log that haven't been sent to the secondary.  In in sys.databases the column log_reuse_Wait_desc will be populated with 'AVAILABILITY REPLICA' as transactions cannot be flushed out unless they are committed on the secondary.

  How long can the secondary be down before it will not catch up?

The secondary won't just 'not catch up'.  If you take the secondary down (without removing it from the AG), log backups won't flush transactions out, log files will fill up and then you'll run out of room, causing any future transactions to fail.  The secondary server's amount of time that it can be down is largely dependent on how much space you have and what your transactional volume is.  Once the secondary comes back up, it will begin applying transactions that occurred while it was down.  I've seen this take from a few minutes to several hours.

For brief outages/patching, leaving the secondary connected is fine, but if you're looking at a long duration outage, it may be easier to just remove the secondary replica and not worry about the hassle of log files filling up.  Then you just re-initialize the DBs into the AG once the outage completes.


Can connect to SQL Server through tsql but not from the bsqldb After installing FreeTDS I was able to connect to our SQL Server using tsql but not yet through bsqldb.
With bsqldb it's trying to connect to the master even though the db was specified to  testDB.

serverName is setup in /etc/freetds.conf

[serverName]
        host = database.windows.net
        port = 1433
        tds version = 8.0

Using tsql:

[gpadmin@mdw ~]$ /usr/bin/tsql -S serverName -U user -D testDB -P password

works fine

With bsqldb it's trying to connect to the master even though the db was specified to  testDB.

[gpadmin@mdw ~]$ /usr/bin/bsqldb -U user -P password -S serverName \
                                 -D testDB -i tag.sql -o tag.csv

Msg 4060, Level 11, State 1
Server 'fj5j2jtt5k', Line 1
        Cannot open database "master" requested by the login. The login failed.
bsqldb: error: severity 11 > 10, exiting

I know this is an old question but for the sake of "drive by googlers" I shall attempt an answer.

I'm assuming you know the backslash doesn't belong in your cmd line and that the servername showing in your output is something random you typed to hide the actual name.

Check this page http://www.freetds.org/userguide/confirminstall.htm

Its got info on troubleshooting your config.

Use this command to confirm your instance / server config:

tsql -LH servername

Make sure your pointing to the proper port, remember you can only specify an instance OR a port, not both.

Once you confirmed your settings use this command to connect to the server and open an interactive prompt from which you can run SQL commands on the server:

tsql -S servername -U username

You will then have to input your password. Remember this user should be created on the DB server itself and have proper permissions to access your DB. If your dead set on using some directory services instead of a local user read the manual http://www.freetds.org/userguide/

Once your logged in enter this:

USE databasename
GO

You are basically making sure your connecting at the right place, then that you are actually able to log in and then manual try to USE the database. This process should give you a better idea of whats going on.

If it still bugging you with the db "master" after that Id say check your server / instance config. Also make sure your using the proper TDS protocol version.


Does disk errors on a mongo primary instance trigger election process? On the MongoDB site here states that a elections occurs when primary becomes unavailable. What happens if there is a disk read/write error on the primary node? Does heartbeat check covers disk health, system load or other checks or just covers connectivity?

It depends on exactly what you mean when you talk about a disk read / write error. If the error is that the data db volume is read-only or unmounted (e.g. An EBS is removed from a running EC2 instance) from a MongoDB primary will not trigger an election process as long as the mongod primary remains running and is exchanging heartbeats, then no failover will occur. The primary will not accept writes but it will not step down in this situation.

The heartbeat checks connectivity between members within the replica set and you can look at the code for it here. It is sent every 2 seconds and will timeout after 10 seconds.


Which columns (and order) to choose for a clustered index to maximize query performance In our database we have a table that looks more or less like the example below. In the past we always created the clustered index on the ID column on the table.

CREATE TABLE [Measurement]
(
    [ID] INT NOT NULL PRIMARY KEY NOT NULL,
    [ParameterID] INT NOT NULL,
    [Measuretime] DATETIME NOT NULL,
    [Value] FLOAT NOT NULL,

    CONSTRAINT [FK_Measurement_Parameter]
        FOREIGN KEY ([ParameterID]) REFERENCES [Parameter]([ID])
)

CREATE INDEX [IX_Measurement_Measuretime_ParameterID]
    ON [Measurement] ([Measuretime]) INCLUDE ([ParameterID]);

CREATE INDEX [IX_Measurement_ParameterID_Measuretime]
    ON [Measurement] ([ParameterID]) INCLUDE ([Measuretime]);

Our data gets written in 1-5 second intervals with successive timestamps for all Parameters.

We decided that it would probably be a better idea to create the clustered index on ParameterID and/or Measuretime as most queries are on those to columns.

Here are some example how most our queries look like:

Example A

SELECT *
FROM Measurement
WHERE ParameterID = 1
    and Measuretime between '2015-01-24' and '2015-01-25'

Example B

SELECT ParameterID, cast(Measuretime as date), avg(value)
FROM Measurement
WHERE ParameterID = 1
    and Measuretime between '2015-01-01' and '2015-02-01'
GROUP BY ParameterID, cast(Measuretime as date)

Example C

SELECT DISTINCT
    ParameterID,
    FIRST_VALUE(cast(Measuretime as date))
          OVER (PARTITION BY cast(Measuretime as date), ParameterID
                ORDER BY Measuretime ) Measuredate,
    PERCENTILE_CONT(.25)
         WITHIN GROUP(ORDER BY Value)
         OVER (PARTITION BY cast(Measuretime as date), ParameterID) as [q1],
    ...
FROM Measurement
WHERE Measuretime between '2015-01-01' and '2015-02-01'
    -- and ParameterID = 1
ORDER BY Measuretime, ParameterID

Which of those three ways that come to my mind for INDEX creation is the best suited for such a scenario?

CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([Measuretime],[ParameterID]) as this is also the order data gets written, and both columns are queried.
CREATE CLUSTERED INDEX [CIX_Measurement] ON [Measurement]([ParameterID],[Measuretime]) as nearly all our queries need to filter by ParameterID in one way or another and only afterwards Measuretime.
On only one of those to and go for a normal INDEX for the other column.

Generally, you want to order the columns in the index based on cardinality. That is, the most unique column first, then the second most unique, etc. So, you need to answer whether parameterid or measuretime will have the least duplicate values. Example: if Measuretime has fewer duplicate values than parameterid use option 1. Then, for optimal performance you need to edit your queries to reference the columns in the correct order.

Something to consider, however, is the pattern of writes to this table. Is the data inserted in the order of Measuretime? Will a new ParameterID always be a higher increment of a previously added one? Are there lots of updates and deletes to this table?

The advantage of having a surrogate key, like your ID column above, is that it ensures ordered inserts into the table/clustered index. Among other benefits, this avoids page splits on inserts.

Also, if the combination of ParameterID and Measuretime will always be unique, you could consider a composite primary key. This has its downsides, but is a valid option.

Here's a good explanation of using composite primary keys: http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx


This can be done in a step-by-step fashion.

First: we have the table sales differentiating between the PurchaseDateTime and the PurchaseDate:

CREATE TABLE sales
(
    UserName         varchar(100),
    PurchaseDateTime datetime,
    Currency         character(3),
    Price            numeric(10,2),
    Quantity         numeric(10,2),

    PurchaseDate AS CAST(PurchaseDateTime AS date),  -- Computed column
    PRIMARY KEY (UserName, PurchaseDateTime)
) ;

-- Secondary index, to help some JOINs
CREATE INDEX idx_sales_date ON sales(PurchaseDate, UserName);

This will make our life easier, as we already have the date precomputed.

Next thing you need to do is to unpivot your exchange_rate table:

CREATE TABLE #exchange_rates_EUR
(
    FXDate date,
    Currency char(3),
    ExchangeRate decimal(10,4),
    PRIMARY KEY (Currency, FXDate)
) ;

INSERT INTO #exchange_rates_EUR
SELECT
    FXDate, 'EUR' AS Currency, EUR AS ExchangeRate
FROM
    exchange_rates_pivot
UNION
SELECT
    FXDate, 'USD' AS Currency, USD AS ExchangeRate
FROM
    exchange_rates_pivot
// do the same for all currencies .. omitted here for simplicity

NOTE: I've used the name exchange_rates_pivot for your original pivoted exchange rates table.

You need this intermediate step to be able to do later on the proper JOINs (you can't actually JOIN in row and column. You cannot treat column names as if they were values [at least, not in any easy fashion]).

You can also use the UNPIVOT infrastructure from SQL Server, but this is more SQL Standard, although probably not as efficient.

The content of this table looks like:

SELECT TOP 12 * FROM #exchange_rates_EUR ORDER BY FXDate, Currency ;

FXDate              | Currency | ExchangeRate
:------------------ | :------- | :-----------
19/06/2017 00:00:00 | AUD      | 1.4705
19/06/2017 00:00:00 | BGN      | 1.9558
19/06/2017 00:00:00 | CAD      | 1.4827
19/06/2017 00:00:00 | CHF      | 1.0870
19/06/2017 00:00:00 | EUR      | 1.0000
19/06/2017 00:00:00 | USD      | 1.1199
20/06/2017 00:00:00 | AUD      | 1.4674
20/06/2017 00:00:00 | BGN      | 1.9558
20/06/2017 00:00:00 | CAD      | 1.4812
20/06/2017 00:00:00 | CHF      | 1.0854
20/06/2017 00:00:00 | EUR      | 1.0000
20/06/2017 00:00:00 | USD      | 1.1156

Second thing you need to do is finding the exchange rate for all your sales and put the info together.

For each specific purchase, the exchange rate is found by means of:

SELECT TOP 1
   ExchangeRate
FROM
   #exchange_rates_EUR er
WHERE
   er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
ORDER BY
   er.FXDate DESC ;

Note that the combination of TOP 1 and ORDER BY will give you the currency exchange for the day, and if not available, for the closest previous date where this info is available; as I've inferred from your example. For the sake of completeness, it should actually be divided by the exchange rate of EUR, in case the exchange data is not based on EUR = 1.00. I haven't done at this point not to overcomplicate things.

This will lead us to the following query:

SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate,
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity,
        -- Subquery to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1
              ExchangeRate
           FROM
              #exchange_rates_EUR er
           WHERE
              er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
           ORDER BY
              er.FXDate DESC
           )
           AS decimal(10,4))
        AS ExchangeRate
    FROM
        sales s
    ) AS q1
ORDER BY
    PurchaseDate, UserName ;

... which results in:

UserName | PurchaseDate        | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:------- | :------------------ | :------- | :---- | :------- | :----------- | :-----------------
John     | 24/06/2017 00:00:00 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20
Jude     | 24/06/2017 00:00:00 | USD      | 4.00  | 2.00     | 1.1173       |  8.94
Jack     | 25/06/2017 00:00:00 | USD      | 6.35  | 2.00     | 1.1173       | 14.19
Jill     | 25/06/2017 00:00:00 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15
Joe      | 26/06/2017 00:00:00 | USD      | 4.50  | 1.00     | 1.1187       |  5.03

Last, we compute also the 'DAILY TOTAL', and perform a UNION ALL with what we already have. A WITH statement comes in handy in this case. Some smallish trickery is needed to ease the ordering (look for the OrderBy column).

This is done with the following statement:

; WITH exchanged_sales AS
(
SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate,
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR, 0 AS OrderBy
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity,
        -- Subqueries to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate
           ORDER BY  er.FXDate DESC) /
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = 'EUR' AND er.FXDate <= s.PurchaseDate
           ORDER BY  er.FXDate DESC) AS decimal(10,4)) AS ExchangeRate
    FROM
        sales s
    ) AS q1
)
SELECT
    UserName, PurchaseDate, Currency, Price, Quantity, ExchangeRate, ConvertedAmountEUR
FROM
    (-- The purchases date, user
    SELECT   *
    FROM     exchanged_sales
    UNION
    -- The daily totals
    SELECT   'DAILY TOTAL' AS UserName, PurchaseDate, NULL AS Currency, NULL AS Price, NULL AS Quantity, NULL AS ExchangeRate,
        SUM(ConvertedAmountEUR) AS ConvertedAmountEUR, 1 AS OrderBy
    FROM     exchanged_sales
    GROUP BY PurchaseDate
    ) AS q
ORDER BY
    PurchaseDate, OrderBy, UserName ;

... whose result is:

UserName    | PurchaseDate | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:---------- | :----------- | :------- | :---- | :------- | :----------- | :-----------------
John        |   24/06/2017 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20
Jude        |   24/06/2017 | USD      | 4.00  | 2.00     | 1.1173       |  8.94
DAILY TOTAL |   24/06/2017 |          |       |          |              | 25.14
Jack        |   25/06/2017 | USD      | 6.35  | 2.00     | 1.1173       | 14.19
Jill        |   25/06/2017 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15
DAILY TOTAL |   25/06/2017 |          |       |          |              | 18.34
Joe         |   26/06/2017 | USD      | 4.50  | 1.00     | 1.1187       |  5.03
DAILY TOTAL |   26/06/2017 |          |       |          |              |  5.03

You can check all the step-by-step details at dbfiddle here

If you would need everything in just one statement, you could put the equivalent of #exchange_rate as part of the WITH. This will give you a very large statement, not so easy to read... For the all-in-one version dbfiddle here


Query is executing without starting the PostgreSQL 9.6 Windows Service My postgres service has not started but I am able to execute any queries on the database. Will it happen like this?
Im using win10 OS, Postgres 9.6

When Iam trying to start the service, I'm getting an error message as the below.

PostgreSQL can be started using different methods.

You (or most probably some tool) are likely to have started PostgreSQL by using:

pg_ctl start -D /path/to/data

(Assuming pg_ctl.exe is in one directory in your PATH)

Note that this doesn't start PostgreSQL as a Windows Service; but it starts PostgreSQL anyhow (if all parameters and settings are correct).

Depending on which installer you used and which settings you chose, the installer created also a Windows Service that should start when your system starts. Normally, when you use this installation method, it also starts the service.

If you can restart your machine, it most probably will start the PostgreSQL service in the normal fashion. If it doesn't, it is probable that some other of your services is starting PostgreSQL behind the scenes.

We can probably be more precise if you let us know which installer you used (EnterpriseDB PostgreSQL installer tends to be the most commonly used for Windows).

References

pg_ctl
PostgreSQL Windows Installers


How can I create and initialize a schema for a embedded MySQL DB? I want to create an embedded MySQL database for running tests, but I'm not sure how to do it.

How do I initialize the database?
How do I run my create table statements?

I'm using Perl, so it needs to be done with a language and not a couple of commands.

Here are the functions from the C/C++ API

mysql_library_init() : Should be called before any other MySQL function is called, preferably early in the main() function.
mysql_library_end() : Should be called before your program exits.
mysql_thread_init() : Should be called in each thread you create that accesses MySQL.
mysql_thread_end() : Should be called before calling pthread_exit()

Personally, I have searched high and low for the Perl equivalent of these C/C++ API calls that would open an embedded MySQL Server. Apparently, none exists for the Perl API.

Here is an excerpt from dev.mysql.com that verifies this:

  The embedded MySQL server library
  makes it possible to run a
  full-featured MySQL server inside a
  client application. The main benefits
  are increased speed and more simple
  management for embedded applications.

  The embedded server library is based
  on the client/server version of MySQL,
  which is written in C/C++.
  Consequently, the embedded server also
  is written in C/C++. There is no
  embedded server available in other
  languages.

You may have to manually trigger the start of mysql yourself with something like this:

@args = {"/sbin/service","mysql","status"};
$rc = 0xFFFF & system(@args);
if ( $rc != 0 ) {
    @args = {"/sbin/service","mysql","start"};
    system(@args) == 0 or die "mysql start failed : $?";
}


Notification email when deadlock is encountered in Oracle alert log How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log)

Node 1:

Mon Sep 08 16:23:59 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA1/trace/LUMA1_lmd0_4814.trc.

Node 2:

Mon Sep 08 16:37:47 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA2/trace/LUMA2_lmd0_2602.trc.

I am using DB Control (11g).

You might try using ADRCI.

ADRCI>
ADRCI> show alert -p "message_text like '%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE"

This will show your error within the past 5-minutes. You could put this into a shell script and add a cron job that runs it every 5-minutes.

#!/bin/bash
LOG = /home/oracle/log.txt
adrci_homes=( $(adrci exec="show homes" | grep -e rdbms ))

for adrci_home in ${adrci_homes[@]}
do
   echo $adrci_home' Alert Log' >> $LOG
   adrci exec="set home ${adrci_home}; show alert -p \\\"%Global Enqueue Services Deadlock detected%' and originating_timestamp > systimestamp + INTERVAL '-5' MINUTE\\\"" -term >> $LOG
done

num_errors=`grep -c -e 'Global Enqueue Services Deadlock detected' $LOG`

if [ $num_errors != 0 ]
then
     mutt -a $LOG -s "Deadlock Detected" admin@mydomain.com
fi


adding fields to 2LIS_12_VCITM datasource in ECC From what I have read the datasource 2LIS_12_VCITM is built off of the ECC tables LIKP, LIPS, VBUK, and VBUP. I would like to add a couple of fields that are in the LIPS table to this datasource and am wondering on the correct way to do it. Do I simply add the fields to the extract structure of the datasource under the include section for the LIPS table?

There are several different ways to add fields to a logistics datasource like 2LIS_12_VCITM. If it is a pool field (a field already available in a source table) you can go to t-code LBWE and simply add it to the comm structure of the datasource. Note that before doing this you will need to drain the V3 and V2 update jobs for the datasource.

If the field you want to add is not a pool field then custom code needs to be written to append the field to the datasource or add an exit routine that populates the field when the datasource data is being transferred to BW. An exit routine is less problematic than adding an append structure for the datasource.


Is it possible to use a default partition for all reads? I have a legacy application which queries one of its database (MySQL on AWS Aurora) tables extensively. Lots of queries and lots of query types. 90% of the rows in this table are outdated and are marked is_deleted. I want to try and make the performance better so I thought of partitioning the table by is_deleted and making all queries go to the partition where is_deleted=false. The problem is that there are many hundreds, if not thousands, of queries I'd need to rewrite in order to insert is_deleted to the where clause of every query.
Is there a way to define the is_deleted = false partition as the default for all reads? If not is there some other way to do this?

If there are no situations where you need to query soft-deleted records, a simpler mechanism would be to have a table just for deleted records. You could then implement a scheduled job to copy all is_deleted = true records to the historical table and remove the originals from the source table.
Doing this would ensure the primary table contained all of the active records and no application queries would need to be updated.
If writes don’t happen very often, you could even look at using an AFTER UPDATE trigger to automatically copy (and maybe remove) soft-deleted records from the table as they’re invalidated.
This, I believe, would be much simpler to implement if going through all of the application SQL queries is untenable


Logical condition when declaring handler procedure I have a procedure in MySQL that executes a handler for SQLEXCEPTION to read the errno code and insert it to a table.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

I'm expecting something like this

DECLARE EXIT HANDLER FOR SQLEXCEPTION OR SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

Is there a chance to use OR or AND logical operators in sql handler?

Or, can I get some advice to get the WARNING and ERROR messages in one DECLARE line?

According with the Mysql documentation https://dev.mysql.com/doc/refman/5.6/en/handler-scope.html you can create a handler with two handlers and the same scope, be aware of the precedence

Example

CREATE PROCEDURE p1()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
    SELECT 'SQLSTATE handler was activated' AS msg;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SELECT 'SQLEXCEPTION handler was activated' AS msg;

  DROP TABLE test.t;
END;


Vehicle spare parts inventory datadase model I am modeling a database to be used for vehicle spare parts inventory management and I am stuck...

Some spare parts can be broken down from a kit into component parts.  I would like to create a model that supports this, but do not know how to build it.

Spare parts can be issued in sets or pieces.  The database should keep information about how many complete sets we have and how many have been broken into pieces.  For instance, one piston ring set will have 6 different individual piston rings.

Can you point me in the right direction?

Great, the part/whole modelling problems.

Here is how LedgerSMB does it. We are likely to change this at some point but this part works well enough.

Basically we have two tables, called parts and assembly.  The exact names/layout will change over time but this basic concept will probably stay the same.

In the parts table, we have parts (goods in inventory), services, and assemblies (as you put it, sets).  In the assembly table we store the assembly to connect with, the part or assembly that is a part of it, and the number required.  This allows sets to be parts of other sets recursively.  This effectively creates a components tree in assembly and a sales component tree in parts.

It works well enough, especially when done on a database which supports WITH RECURSIVE common table expressions (i.e. not MySQL...)

Of course then you have a table or two to manage inventory movements and this would handle the sets and their deconstruction according to your business rules.


Effects of enabling service broker I would like to use SqlCacheDependency to update a dataset in real-time, but we have not enabled the service broker on our production database. Could enabling the service broker have any adverse effects (performance, reliability, etc)?

Most likely Service Broker is enabled in your production database. It is enabled by default, it only gets disabled on restore in order to prevent distributed split-brains scenarios.


Set the Priority for two different "after insert" triggers in SQL Server I'm trying to create triggers by functionality,instead of DML operation.

For exp,I have 2 triggers A and B. A is for "AFTER insert, update , DELETE" , and B is for "AFTER INSERT". how can I know which one(A or B) is ran first after insert a new record?

You can specify the first and/or last trigger to fire using sys.sp_settriggerorder. If you have two or three triggers, this allows you to completely control the execution order of all triggers. If you have more than three, you can make one first, another fire last, but the order of execution for the triggers in the middle will be non-deterministic.

In your case, to make sure A always fires first:

EXEC sys.sp_settriggerorder N'dbo.A', 'First', 'INSERT';

To make sure B always fires last (while not necessary when there are only two triggers, you could add more later, just note that if you later attempt to specify another trigger as last, it will generate an error):

EXEC sys.sp_settriggerorder N'dbo.B', 'Last', 'INSERT';

If you're not asking how to control the order but rather to determine after the fact which one fired first for a specific insert, then you will have to add logging to both triggers. Once you are logging both triggers' activity to a table, you can determine order by an identity column on the logging table, or a date/time column populated with the current time.

Of course, you are probably better off combining the two triggers into one (and the part that is insert only would have conditional logic around it). I did not test the overhead of additional insert logic in an insert/update/delete trigger getting bypassed when invoked by a delete or update operation, but in general, my tests concluded that a single trigger has less overhead than multiple even when the end result affected the same number of rows:

T-SQL Tuesday #64 : One Trigger or Many?


Remove all special characters without removing accented characters I need to clean up a column that has all types of weird characters with in the string data. The weird characters are pretty much these []!@#$%.&*`~^-_{}:;<>/\|()

I only need to allow [0-9], [a-z] but also accented characters due to some spanish words being included.

I've been looking at https://dev.mysql.com/doc/refman/8.0/en/regexp.html to try to devise a regex that might fit this but my regex foo is weak.

Is there anyway to clean these up without having to look for and replace the special characters individually while still leaving the accented characters?

MySQL 8 has the REGEXP_REPLACE function that should work.

If you only need to leave alphanumeric characters, including accented characters, this would be simply

SELECT REGEXP_REPLACE(your_column, '[^[:alnum:]]+', ' ') ...

to replace any non-alphanumerics with spaces. If you want to only eliminate characters on your list, you'd use something like

SELECT REGEXP_REPLACE(your_column, '[\]\\[!@#$%.&*`~^_{}:;<>/\\|()-]+', ' ') ...

Note that in the latter case you will need to escape ']', '[', '\', and possibly '-' (or put the latter as the first or last element in the set).

dbfiddle link.


Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture) I am eagerly trying to design the perfect multitenant data architecture for my database.

I am getting inspiration from an article posted by Microsoft, based on scenario 3.

I have been given all my tables a tenant-column, and afterwards created a matching VIEW

ALTER TABLE {table_name} add TenantID varbinary(85) DEFAULT SUSER_SID()

CREATE VIEW {view_name}
AS
   SELECT *
   FROM {table_name}
   WHERE TenantID = SUSER_SID()

Each of my clients has their own database user account, these users has been given following rights:

db_datareader
db_datawriter
public

And then I changed the following:

DENY SELECT ON {table_name} TO {user_name}
GRANT SELECT ON {view_name} TO {user_name}

Anyway, am I missing something for now? :)

I suspect it is how SSMS enumerates tables

However, you don't need DENY. Just don't GRANT.

Best practice would be something like this

Remove membership of the db_datareader and db_datawriter role(I never use them)
Create a specific role (with CREATE ROLE) and GRANT on the view to this.

Then, you don't need to DENY on the table because it won't be checked anyway due to "ownership chaining"

Saying that, I'd also use SCHEMAs so I don't even require GRANT to the view. GRANT once to the SCHEMA. For more, see

Schema design - best practices?
Over use/correct use of schemas?


Difference between idx_tup_read and idx_tup_fetch on Postgres On Postgres 8.4 when you do:

select * from pg_stat_all_indexes where relname = 'table_name';

It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?

When looking at the source code of the view, then you'll see that idx_tup_read is the result of calling pg_stat_get_tuples_returned() and idx_tup_fetch is the result of calling pg_stat_get_tuples_fetched()
The manual describes the two functions as follows:

pg_stat_get_tuples_returned(oid)
Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index
pg_stat_get_tuples_fetched(oid)
Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index


Untraceable error importing Huge dataset with SQLCMD I am currently doing an internship and I received a 42 gb ms sql-server import script.
I tried to import the script using the ms SQLCMD tool all at once.
It ran for 30 minutes and then I got the following error:

msg 102 level 15 state 1 line 37 incorrect syntax near '7500'

So naturally I checked line 37 and searched for the value 7500 but I could not find it.
Then I tried to find the last executed query in my server log.
Having found the last inserted query, I knew what line in my script was causing the error.

Unfortunately I could not find the value 7500 in the error causing line.
And the next 3 "batches" imported just fine...

So I cleared my database, and tried to import the script a second time with the exact same error as result.

So I tried to split the 42 gb script into 3000 smaller scripts.

I am now running the 3000 scripts sequentially and it seems to be running just fine.
It has been running for 4 hours now and it is past the point of the previous error.

My question is: Did I find a bug in SQLCMD or is this a known problem with larger import scripts? If this is a know problem what is the best way to counter this (Splitting the file took a long time)

Thanks.

It's possible that you hit SQL's size limit for batch size. Check out https://msdn.microsoft.com/en-us/library/ms143432.aspx.

If your file is just line after line of INSERT INTO [Table] (field, field) VALUES (value, value); then you have some better options than splitting the file.

Add GO statements every few thousand lines, which helps break things up into smaller batches.
Use Powershell or another language to create a stream reader send every line to sqlcmd. Chrissy LeMaire created a great script to do this for CSV files that you could use for inspiration: https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9. I've used a variation on this method to import files with a half-million INSERT statements in less than a minute.


Change column datatype without rewrite when data remains unchanged For example:

timestamp(tz) to int8
date to int4
json to text

The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly and instantly.

How do I check if this is actually the case?
If it is not the case (table gets rewritten), how do I make it do it without rewriting?

Like the documentation says:

  [...] changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

So the key is that the type conversion is binary coercible, in other words, you need a type cast between these types that is defined WITHOUT FUNCTION.

This is not the case for the data types you mention in your question, but if you are a superuser, you can create such a cast and then use it with ALTER TABLE:

CREATE CAST (date AS int4) WITHOUT FUNCTION;
ALTER TABLE mytable ALTER datecol TYPE int4 USING (datecol::int4);
DROP CAST (date AS int4);

Of course you have to understand the internal representation of date for that to make sense.

It is a good idea to remove the cast after you are done, because adding type casts to PostgreSQL can easily lead to ambiguities during type resolution, which will cause errors.


ORACLE DB literal does not match format string I' am having this error:

 ORA-01861: literal does not match format string
    01861. 00000 -  "literal does not match format string"
    *Cause:    Literals in the input must be the same length as literals in
               the format string (with the exception of leading whitespace).  If the
               "FX" modifier has been toggled on, the literal must match exactly,
               with no extra whitespace.
    *Action:   Correct the format string to match the literal.

It happened overnight without any changes in the database configuration. It looks like there is a problem with sysdate format but I'm not 100% sure. We have many places in our procedures where we use to_date(to_char(sysdate, 'YYYY-MM-DD')) from dual which actually breaks. Is there any way to format sysdate to match the following expression?

to_date(to_char(sysdate, 'YYYY-MM-DD')) from dual

First you convert sysdate to a char type, then you convert it back to a date type without specifying the format string, which may or may not work based on the settings of the session running this code.

This is completely unnecessary, just use trunc(sysdate) to remove the hours, minutes and seconds.

Also, if you return a date type, then the format depends on the nls_date_format variable, not the SQL that produces the date value.


Can MongoDB manage balancing between the Mongos Dynamically using TCP Balancer If I have a business where I maintain 10 applications and I need to divide the request load of these 10 applications between 3 Mongos Dynamically.

Like the TCP Loadbalancer need to connect to MongoDB config servers and taking the Health status of 3 Mongos constantly and distributing the request load from application depending on the health state(getting to whether a particular Mongos is busy or free).

Is anything like the above can be achieved using TCP Balancer? or Its just can be used to configure the load static.

Please, confirm.

If those (10) applications are at own nodes (you have multiple application nodes), every node can have mongos installed, so application Connect's localhost mongos. Those mongos then connect's to your config servers and cluster.

You just use LB to distribute client load to those application servers.


SQL Server: allocated vs reserved vs commited What exactly is the memory that is allocated to the a memory component in SQL Server?

I believed committed memory is the amount of physical memory that is being used, and reserved is the amount of memory in Virtual Address Space that does not yet map to physical memory.

But I am puzzled when it comes to allocated memory. When a memory component requests memory from a Memory Manager, what is the nature of this memory being granted? Real/Physical or VAS?

Here is an example DMV:

SELECT pages_kb AS A
,virtual_memory_committed_kb AS C
,virtual_memory_reserved_kb AS R
FROM sys.dm_os_memory_clerks;

There is no relationship whatsoever between columns A, C and R. Initially I was expecting A = C + R, which implied that A, the allocated memory pages, should start out as memory in VAS, and represent the intent to use this much of memory. But clearly it is not right.

How do I interpret allocated memory? Thanks

What exactly is the memory that is allocated to the a memory component in SQL Server?

Memory allocated is memory given to SQL Server for doing various processing. Allocation is wide terms which means providing memory you should not attach any specific meaning to it.
You are correct committed memory is physical memory used because committed memory is backed by physical memory while reserved memory is memory reserved by process which it thinks it might need it may necessarily not be committed.

When a memory component requests memory from a Memory Manager, what is the nature of this memory being granted? Real/Physical or VAS?

VAS: Virtual address space is total amount of virtual memory a process can see in the system when created.
When SQL Server process requests memory it is first mapped to VAS address in the region. VAS is 8TB for 64 bit system and 4 GB for 32 Bit system. Every process which requests memory sees virtual memory which equal to 4GB or 8 TB as per architecture(32 or 64 bit) it is running on. Now after process is mapped to VAS memory manager finds out physical memory to which it can be mapped and finally commit memory to this process when this is eventually done the memory is allocated and is called physical memory allocated to process.
Virtual_memory_committed: This is virtual memory committed as reported by the  clerk.
Virtual_memory_reserved: This is the VM reserved as tracked by the clerk..
Pages_kb: Amount of page memory allocated to process.
I hope this clears few things for you


psql is not prompting me for a password, then denies access I'm using Postgres 9.4 on Debian Linux.  I created a database with a user, cindex with access to the database.  Yet when I try and login at the command line, I'm not even prompted for a password:

myuser@myuserserver:~ $ psql -Ucindex cindex
psql: FATAL:  Peer authentication failed for user "cindex"

What else do I need to do to enable the user?  Below you can see the privileges that I have already set up:

postgres@myuserserver:~$ psql
psql (9.4.13)
Type "help" for help.

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA cindex TO cindex;
GRANT
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cindex    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =T/postgres          +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cindex=c/postgres
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 cindex    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

Peer authentication

This means it's using a unix socket connection, and connections for unix sockets are set to use peer authentication in pg_hba.conf. It just checks that the unix user name is the same as the postgres user name requested, and doesn't care about passwords.

If you want password auth, use md5 auth in pg_hba.conf instead.

See the manual.

The way PostgreSQL splits authentication between SQL configuration and a config file is definitely confusing, so you're not alone. Being able to set a password for a user, but having that password be ignored in some contexts and used in others, takes some getting used to. Once you understand the system it makes sense but it's definitely not discoverable and intuitive.


Moving SQL Server Database Files to New Location - Why permission error? I was trying to move a database (.mdf and .ldf files) to a different directory on the same server hosting the SQL server.  I followed these steps found in this Q&A:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET OFFLINE
ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF')
ALTER DATABASE MyDB SET ONLINE
ALTER DATABASE MyDB SET MULTI_USER

When I got to Step4, I got "Access Denied".  Unfortunately, my maintenance windows were very short and no time to troubleshoot.  So I decided to drop the database and restored it using the WITH MOVE clause to place the DB files in the right directory.  No issue.

My question is why did I get "Access Denied"?  I didn't change MSSQL service account.  It's the same account that ran the RESTORE.

The ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF') statement doesn't actually move the files.  It merely moves the definition of where the files reside.  You'd need to actually move the files manually before continuuing to step 4.

When you move the files prior to step 4, you must ensure the target directory has the correct permissions for the SQL Serve service account.  This document details the requirements.


Getting the "ORA-00911" error when I am trying to insert more than 1 row Lets say I have the follow insert statements.

INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');

I am using Oracle Express Edition 10g and using the web interface to execute these instructions.

If I insert them one by one, they work OK but if I enter them all at once and try to execute I get the following error.

ORA-00911: invalid character

How I can fix this issue?

Thanks.

I suspect you want to put the whole thing in an anonymous PL/SQL block and run that, i.e.

BEGIN
  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
END;

Depending on the tool (SQL*Plus, TOAD, iSQL*Plus, SQL Developer, and the APEX query tool may have slightly different syntax requirements), you could also add a '/' character after each INSERT statement

  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
  /


Is RMAN backup platform independent? I have RMAN backup from AIX server and want to restore on Linux OS. Could you please let me know if there is a way to do it?

Thanks a lot for help in this regard.

Query this table in order for you to see the platforms to which rman can convert your

SYS@EMR> select * from V$DB_TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                                                                                ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
          7 Microsoft Windows IA (32-bit)                                                                         Little
         10 Linux IA (32-bit)                                                                                Little
          5 HP Tru64 UNIX                                                                                Little
         11 Linux IA (64-bit)                                                                                Little
         15 HP Open VMS                                                                                Little
          8 Microsoft Windows IA (64-bit)                                                                         Little
         13 Linux x86 64-bit                                                                                Little
         12 Microsoft Windows x86 64-bit                                                                          Little
         17 Solaris Operating System (x86)                                                                        Little
         19 HP IA Open VMS                                                                                Little
         20 Solaris Operating System (x86-64)                                                                     Little
         21 Apple Mac OS (x86-64)                                                                                Little

Step by Step restore

Connect to the target database using rman and backup the database -
Source Node/ System
copy following files to the Destination node ( backup pieces ,
control file backup and parameter file)
Perform the modificaiton on destionation node to parameter file -
PFILE
Once the pfile is ready, set the environment parameter and connect to
rman
connect to target - start up the db no mount mode
now restore the controlfile from the backup on destination node
(which copied from source db )

Example :

restore controlfile from '/backup/file name';

once the restore is complete - mount the db.

now main step is backup prices which you copied from the source db to destination db catalog themcatalog the backup pieces.
Now, you need to recover the db - find he sequence number

list backup of archivelog all;
Rename the Redologfiles

alter database rename file
Now final step is you need to recover the db to the last sequence number

Execute this series of statements:
set until sequence 123456; -- sequence which you go previous.
    set newname for datafile 1 to '/node2/database/db1/system01.dbf'; -- data file paths modified

restore database;
switch datafile all;
recover database;
alter database open resetlogs;

2 As a second approach this can be done using the Oracle Transportable Database  - follow the link for great article form IBM guys !


Galera - single database backup & restore I have a MariaDB Galera cluster. One of its nodes is dedicated for the backups (pc.weight=0 and the MaxScale SQL proxy doesn't know it exists).

I'm using mariabackup --backup --galera-info --target-dir=/path/to/my/dir to do the daily backups. So this backs up everything.

I want to be able to do 2 kinds of restore:

full restore in case of disaster (e.g. data center explosion)
single database restore in case of data loss (e.g. human error/hacking)

The case 1 is easy, I just take the backup I have and use mariabackup --prepareand mariabackup --copy-back on the new freshly installed Galera nodes.

But how can I achieve case 2?

I know I could do a mariabackup --prepare and then for each table of the database I want to restore:

discard the table space of table X
copy back the file(s) of table X from my backup
import the table space

The problem is that this process is too low-level for Galera/wsrep to notice it and the changes are of course not replicated to the other nodes. So I would need to repeat this process on all the nodes.

I could also:

take a logical backup (mysqldump)
drop the database on one of the Galera nodes
re-create the database
import the dump I just did

This would be replicated to all the nodes. But it's highly inefficient and I would need to setup a temporary MariaDB server to run the mysqldump on the backed up data.

There must be a better way, right? How are you doing it?

Your answers are much appreciated!

There are multiple ways to backup and restore but as always this depends on your needs and varies among setups, here is just an idea that could help minimize the recovery on time point in case need to restore.

Before, is important to understand that in a MariaDB Galera cluster, all the nodes are "masters" and by using pc.weight=0 you are just modifying the quorum check Weighted Quorum for a Master and Multiple Slaves Scenario, from the docs:

node1: pc.weight = 1
node2: pc.weight = 0

  Under this pattern, if node1 dies, all remaining nodes end up as non-primary components. If any other node dies, the Primary Component is preserved. In the case of network partitioning, node1 always remains as the Primary Component.

But this can't be used as a backup strategy since once your data has being modified it will be replicated among all the nodes.

Regarding the backup and restore scenario in case of a human error, hacked DB or hardware malfunction, etc, it will depend on how periodically you were backing up, your last on time restore point will be the latest date of your backup. You could create backups daily or even every 12 hours, a heavy time-consuming process depending on your DB size.

As an easy but more "expensive" alternative to this method, you could attach one or more slaves to your cluster, One to be in sync (no delayed) and a second one using a delayed replication in where you could define a delay for example 1 hour. (you could set the delay to your "reaction" time for example)

However, is important to monitor the state of the cluster(masters) so in case something unexpected happened you could stop the replication (at least on the delayed slave) since you would like to use it as your earlies restore on time point.


Remote connection showing up as from localhost on MariaDB 10.2 I have a MariaDB 10.2.14 server running on a CentOS server (10.0.0.201) and a jobs server with various Java processes running on another CentOS server (10.0.0.193).  MariaDB is configured such that the root user can only connect from localhost - the various job apps have their own users to connect with.

One of the jobs is currently running and takes quite a while - it moves a lot of data using an insert-select statement.  If I do a show processlist in the database it tells me the connection has come from localhost and is running as root:

| 58684 | root             | localhost:57486  | db_name              | Query       |  23420 | Sending data ...

A netstat on the db server shows the connection with that client port is actually established from the jobs server:

netstat -n | grep 57486
tcp        0      0 10.0.0.201:3306         10.0.0.193:57486        ESTABLISHED

There is no job running on the DB server (it doesn't even have Java installed).  Other connections from the job server show as I'd expect - with the remote IP and the application user name.  Is anyone able to explain why this might be happening?  Is the MariaDB server somehow proxying the remote request?  Why would it be running as root?

I would expect a connection to show as coming from localhost if a remote user was querying a table that has the federated, federatedX, connect or spider storage engines (or possible other similar storage engines), and the table was configured to connect as the root user to the local MariaDB instance rather than a remote one.

You can find the storage engine used for a particular table with:

SHOW TABLE STATUS WHERE Name='your_table_name';

You can also find tables using a particular storage engine with:

SHOW TABLE STATUS WHERE Engine='name_of_engine';

You could also do something like:

SHOW TABLE STATUS WHERE Engine IN ('FEDERATED', 'FEDERATEDX', 'CONNECT', 'SPIDER');


What indexes would I create for a query like this? Here's a query I've got:

SELECT "balance_transactions"."fee"
FROM "balance_transactions"
JOIN charges ON balance_transactions.source = charges.balance_id
WHERE "balance_transactions"."account_id" = 123
AND (balance_transactions.type = 'charge'
AND charges.refunded = false
AND charges.invoice IS NOT NULL)
AND ("balance_transactions"."created" BETWEEN '2013-12-29' AND '2014-01-28');

What's hurting my brain is figuring out what I need to index to optimize a query like this.

Would I create individual indexes on each column in that WHERE statement? Or a single index that encompasses multiple columns? Or something else?

I happen to be using Postgres.

Here's the output of EXPLAIN ANALYZE:

 Nested Loop  (cost=40.44..9294.43 rows=744 width=4) (actual time=0.791..32.687 rows=1131 loops=1)
   ->  Bitmap Heap Scan on balance_transactions  (cost=40.02..2266.08 rows=1023 width=22) (actual time=0.737..1.006 rows=1201 loops=1)
         Recheck Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on index_balance_transactions_account_type_created  (cost=0.00..39.77 rows=1023 width=0) (actual time=0.716..0.716 rows=1201 loops=1)
               Index Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
   ->  Index Scan using index_stripe_charges_on_stripe_id on charges  (cost=0.42..6.86 rows=1 width=18) (actual time=0.025..0.025 rows=1 loops=1201)
         Index Cond: ((balance_id)::text = (balance_transactions.source)::text)
         Filter: ((NOT refunded) AND (invoice IS NOT NULL))
         Rows Removed by Filter: 0
 Total runtime: 32.924 ms
(10 rows)

I would suggest the following two indexes:

charges(balance_id, refunded, invoice)
balance_transactions(account_id, type, created, source, fee)

The first is a covering index for charges.  The index should be used for the query with no need for the data tables.

The second is a covering index for balance_transactions.  The first three will satisfy the where clause.  The four column will be used for the join and the final is needed for the select.


percona-online-schema-change recommendation I am currently setting up percona-online-schema-change to do a ALTER on a table with 300m+ rows.

I've verified that percona-online-schema-change can make the required ALTER.

The source table receives updates/inserts in large batches at erratic intervals.

During testing, with defaults values for percona-online-schema-change, the update fails due to the process not being able to obtain a lock on the new table.

My theory is that due to the erratic nature of the updates, percona-online-schema-change is not able to obtain sufficient accuracy for its chunk-size calculations, and is trying to do too much on the new table.

Therefore, I was going to set some hard values to try and obtain some more consistent behaviour.

Specifically, I was going to set a hard --check-size value and disable over-sized chunk checking.

I was also going to increase the number of retries for copy-rows from the default of 10 to 30, and increase the wait between tries from 0.25 to 10.0.

Finally, I was going to increase the innodb_lock_wait_timeout value from 1 to 5.

I know that these measures will slow down the overall operations considerably, but its more important to me that it completes in the first pass that it completes quickly.

Does this seem like a reasonable and safe approach to the update?

You seem to be asking the right questions and that seems like a reasonable course of action.

Don't be surprised if this runs for a few weeks.


Should we create a schema per "project"? This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project from 2-30K rows). All of them contains projectID as link to project.  Currently the database is 400GB.

We are trying to create a schema for each project wherein each schema would contain all attribute tables. Whenever a project is created, it would get a new schema.  This would be mean each table would contain at max 30K rows which would improve select performance. We would be using dynamic SQL in our queries to read/insert.

I feel like this would not be proper application of database but how do I prove to my team that this a bad approach?

Read this paper: Multi-Tenant Data Architecture. It extensively discusses on the pros and cons of 3 possible approaches (tenant ID in each table, schema per tenant, database per tenant). This approach is called "Shared database, Separate schemas" and is a valid approach.  Whether it is best for your case, I'm not in position to make that call. This is what the paper has to say about this approach:

A significant drawback of the separate-schema approach is that tenant
data is harder to restore in the event of a failure. If each tenant
has its own database, restoring a single tenant's data means simply
restoring the database from the most recent backup. With a
separate-schema application, restoring the entire database would mean
overwriting the data of every tenant on the same database with backup
data, regardless of whether each one has experienced any loss or not.
Therefore, to restore a single customer's data, the database
administrator may have to restore the database to a temporary server,
and then import the customer's tables into the production server—a
complicated and potentially time-consuming task.
The separate schema
approach is appropriate for applications that use a relatively small
number of database tables, on the order of about 100 tables per tenant
or fewer. This approach can typically accommodate more tenants per
server than the separate-database approach can, so you can offer the
application at a lower cost, as long as your customers will accept
having their data co-located with that of other tenants.

Read the paper.

which would improve select performance

If you are trying to do this split for performance reasons, you and your team are definitely on the wrong path. Address the perf issues by identifying them and fix them appropriately. Read How to analyze SQL Server performance.  Multi-tenancy ain't cheap, and you need to plan ahead and have an answer to simple questions like 'how do I deploy the next version of my app, which includes a database modification?' and 'how to restore the data for one particular project?'.  The issues you see now are indicative of app tunning problems (hint: you're missing indexes).
As a personal experience note: I've seen databases with millions of objects in them (which the 'shared database, separate schema' approach is naturally going to cause), and it wasn't pretty... I hope all your DB administration is 100% scripted/automated now, expanding SSMS tree views with to select the 1 in 1M objects is not going to work.


Using Select Alias in where clause I have a table games

+------------------+----+
|      title       | id |
+------------------+----+
| Need for Speed   |  1 |
| Far Cry          |  2 |
| Assassin's Creed |  3 |
+------------------+----+

and I have a table games_played

+-----------+---------+
| player_id | game_id |
+-----------+---------+
|         1 |       1 |
|         1 |       2 |
|         2 |       3 |
|         2 |       1 |
|         3 |       2 |
|         3 |       1 |
+-----------+---------+

What I need here is select all the games that player_id 1 haven't played and also the number of times the game was totally played.

+------------------+----+--------+--------+
|      title       | id |  count | played |
+------------------+----+--------+--------+
| Assassin's Creed |  3 |    1   |   f    |
+------------------+----+--------+--------+

Here is the query I wrote

select * from games, count("games_played.*"),
exists(select games_played.player_id from games_played where
games_played.player_id = 1 and game_id = games.id) as played,
inner join games_played
on games_played.game_id = games.id
where played = false;

This is throwing and error column played does not exist.

Can someone help me here ?

There are several issues with the query. FROM and column expression are misplaced, "games_played.*"  is not doing what it looks like, inner join instead of outer join, etc.

Try this one (I assume that (id) is the primary key of games):

select
    games.*,
    count(games_played.*)
        as count,
    count(*) filter (where games_played.player_id = 1) > 0
        as played
from games
    left join games_played
    on games_played.game_id = games.id
group by
    games.id
order by
    games.id ;

Test at dbfiddle.uk


Looking for a scalable relational database I'm looking for a database that achieves the following:

fully ACID.
relational with joins.
has transactions.
allows the administrator to specify sharding rules (for example: users from Europe should be located in the european data center).
the toughest requirement: upon network partition, like two datacenters disconnected from each other, each datacenter should be able to read and write to the nodes in it. Basically, I'm willing to sacrifice partial availability to partition tolerance. Sadly, most solutions will stall all reads and writes in case of a split-brain. (thus, preferring availability over partition tolerance)

I prefer an open source solution but willing to consider proprietary solutions as well.

I suggest you take a very close look at this :

http://codahale.com/you-cant-sacrifice-partition-tolerance/

It explains fairly well why "stall all reads and writes" is nothing more than an inevitable logical consequence if you want partitions and the data they contain to be consistent.


Ola Hallengreen Backup Retry Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-running the whole script.

Unfortunately the script itself does not have a built in path to retry a single DB for a backup if it failed. You can view the entire script options here on Ola's Site.

What I would recommend (albeit it may be a little painful) is to have a separate job step for each DB and specify the DB you want to backup. Then in each step you can add the retry options.

You could probably start by creating one or two, and then hit the script button in the SQL Agent. From there you can copy and paste the sections of SQL that create each subsequent job step and make a few alterations rather than manually modifying the job steps through the GUI.

From there I would begin evaluating whether Ola's scripts are meeting my organizations needs and I may start to look to other backup options like DBATools or Minionware or custom backup scripts. You could also alter the Ola scripts but this will be significantly more difficult. There are also paid tools that may be worth evaluating.


Statment running significantly slower than component parts and causes high CPU usage I am running the following statement in SQL Server Studio 2008:

Use Systems

select subdirectory
from [Systems].[dbo].[ScanningArchiveFileSystem] f
where f.FileFlag = 1
and SubDirectory not in
(
    Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem
    from  AdminWIP_DS.dbo.fnTblMembers(NULL) AS m
    RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f
    ON m.intMemRef = f.intMemRef
    AND m.intSystem = f.intSystem
    where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

    union

    select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
    from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f
    join p3Test.dbo.MEMBERS m
    on m.MEMNO = f.intMemRef
    and RIGHT(f.strPath,3) = 'pdf'
)

Each select statement runs with the following times:

select subdirectory
from [Systems].[dbo].[ScanningArchiveFileSystem] f
where f.FileFlag = 1

Rows 247769, Time 1 Secs

Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem
from AdminWIP_DS.dbo.fnTblMembers(NULL) AS m
RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f
ON m.intMemRef = f.intMemRef
AND m.intSystem = f.intSystem
where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

Rows 330442, Time 6 Secs

select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f
join p3Test.dbo.MEMBERS m
on m.MEMNO = f.intMemRef
and RIGHT(f.strPath,3) = 'pdf')

Rows 63133 0 Secs

and finally the Union as a whole runs as Rows 237660, Time 14 secs.

When I bring them all together for the "Not in" where clause CPU usage on the SQL Server goes up to 100% and so far I let it run for 20 mins and it didn't complete.

I'm not a SQL whiz so I was wondering if anyone could help with why the whole statement can take significantly longer.

You should check your execution plan.

It can be that cardinality estimation for the last Anti Semi Join is wrong:
you join 247.769 rows with 237.660 rows and there should be Hash Join but if it underestimated cardinality (AdminWIP_DS.dbo.fnTblMembers()) it used NL so you've got your 20 minutes.

Another thing to consider here is to eliminate right join:

You concatenate 2 strings and if one of them is null you'll get null as the result.


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

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

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

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

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

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

Line below 65203:

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

How can I resolve this issue?

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

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

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


Is it possible to force drop of mysql tables with FK? For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.

Is there a short way to drop a table, and its all child tables (in which they have FK to the parent table)?

For example,

CREATE TABLE test
(
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) ENGINE=InnoDB

and id is a FK in 5 child tables. Is it possible to drop TABLE test, and force drop of all 5 child tables having id FK? instead of dropping child tables one by one?

While ON DELETE CASCADE can be attempted, it is worth noting what the output of a mysqldump looks like. At the top of every mysqldump you will see directives which disable foreign key checks and unique key checks:

-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Running a reload of a mysqldump gives the DB Session the opportunity to disable those checks, load the table, then enable the checks again. In view of this, you should manually truncate all the tables in question with those two directives.

Suppose you have table test with foreign keys references in t1,t2,t3,t4,t5

Simply truncate then in order starting with child tables as follows:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
TRUNCATE TABLE t5;
TRUNCATE TABLE t4;
TRUNCATE TABLE t3;
TRUNCATE TABLE t2;
TRUNCATE TABLE t1;
TRUNCATE TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

UPDATE 2018-07-05 09:33 EDT

I never circled back to this question to see the OP clarification, so let me complete this answer. What I should have posted 5 years ago was the following:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
DROP TABLE t5;
DROP TABLE t4;
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
DROP TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Give it a Try !!!


How to maintain visibility of all new transactions in append-only PostgreSQL DB without scanning the whole table Situation

PostgreSQL v11

I have a database with a dozen tables. No rows are ever DELETEd or UPDATEd. A bulk of data is INSERTed to all the tables in a 'few' (up to 1,000) transactions every day. Some tables can add tens of GBs of data during the INSERT (the largest has has almost 2 billion rows as of now).

Problem

I have noticed that at some point SELECT queries I use to read the data from the DB stop using index only scans. After some digging it became apparent this is due to visibility map becoming out-of-date. This is confirmed by running VACUUM as it reverts back to using index only scans. However, VACUUM is very expensive in my case (can take over 10 hours for the largest table) and AUTOVACUUM is never triggered as there are no DELETE or UPDATE operations.

I have looked at running VACUUM FREEZE after each transaction but it seems it will need to scan the whole table after each transaction, which again is going to take ages.

Question

What is the best way to mark all the new transactions as visible for append-only PostgreSQL without scanning the whole table every time?

You should run VACUUM (FREEZE) occasionally. The longer it doesn't run, the more it has to do, and the longer it will take.

To speed up VACUUM, increase maintenance_work_mem.


Error 20598 - The row was not found at the Subscriber when applying the replicated command I am facing the error 20598 with Transactional Replication:

The row was not found at the Subscriber when applying the replicated command.

Normally this error occurs when an UPDATE or DELETE statement is executed by the publisher for a primary key value and the record (against which UPDATE/DELETE executed) does not exist in the subscriber database.

But in my case the scenario is different.

I diagnosed and found the record exists in the article/table of subscriber database, because when I executed the command (retrieved with help of sp_browsereplcmds) at the subscriber it executed successfully.

What may be the possible reason of it?

I'm using SQL Server 2016 both side.

What may be the possible reason of it?

There are multiple reasons that could cause this:

It could be someone or a process/apps deleted and inserted the data in subscriber db (while cmds are being applied). Review your subscriber security settings and check what process/apps are accessing the subscriber db.
Multiple publications connected to subscriber db. pub1 (deleted the data first) then pub2 tried to UPDATE/DELETE the data which could cause an error 20598.
Triggers on subscriber tables that could delete/insert/update the data.

HTH


PLS-00306 Error: How to find the wrong argument? PLS-00306: wrong number or types of arguments in call to 'string'

Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.
Action: Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

How do I quickly identify the wrong argument?
I have a stored-procedure with dozens of parameters. Is there an easy way to check the differences between the used and defined procedure? I don't want to check it line by line..

No, there's really no shortcuts here.  Examine things in the following order:

Check the procedure name.
Check the number of parameters.
Check the types of the parameters.
Check the parameter names.


Which is better MySQL search strategy, varchar x 2 or text column? I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting truncated.

I can simply convert the column to text type, but my next issue is when a user searches using keyword for an SMS message the database will have to run search on a text type column through millions of rows in the future, and that could be very slow.

So my strategy is to have three varchar(255) columns to support the overflow of characters like:

sms_msg_1
sms_msg_2
sms_msg_3

I'll just create logic where "if message is over 255 characters, split it and save to the extra columns." And so when I do a search I'll just do a

SELECT *
FROM sms_messages
WHERE sms_msg_1 = '%dog%' OR sms_msg_2 = '%dog%' OR sms_msg_3 = '%dog%'

Is this an "OK" strategy or am I better off, performance wise, just using a single text column, specially if there's not much performance difference anyway?

Search using col LIKE '%xyz%' is bad on varchar and text alike. You probably want to use fulltext index which can work well with a text column.

Your proposed scheme is unnecessarily complex and you might even "split" the word you are searching for so it won't work.


Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL? I know PostgreSQL provides multiple lock modes for concurrency control. When I read through their documents, I cannot see why the ACCESS EXCLUSIVE lock is necessary in any case. The only difference between an EXCLUSIVE lock and ACCESS EXCLUSIVE lock seems to be that ACCESS EXCLUSIVE lock disallows reads from the table.

But isn't it a good thing that people are able to read? In what case should you prevent users from reading? In the documentation I see that usually DROP TABLE, etc. will use ACCESS EXCLUSIVE lock, but why? I was thinking that maybe when you drop a table the data is already partially gone when you are trying to read so the result will be corrupted. But then I realize since everything in Postgres is transactional, a DROP TABLE should not take effect until commit. So my guess should be wrong.

I am still confused. Why is ACCESS EXCLUSIVE lock needed again when you compared it to EXCLUSIVE lock? My best guess is that replacing ACCESS EXCLUSIVE lock with EXCLUSIVE lock is not going to break PostgreSQL. It is just sometimes people do not want others to read. Did I miss something?

If you have a look at the documentation, you see the list of operations that obtains this type of lock:

  Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL,
  and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many
  forms of ALTER TABLE also acquire a lock at this level.

All those are physically (re)moving rows, so reading would be at least very unpredictable during these.

Talking about transactions, when you start reading from a table and another process decides slightly later to drop it, the latter must wait until the read finishes - the earlier will still get the data requested, and when that transaction commits, the second one takes the lock and drops the table.  The other way around, the read will just fail after the wait from the dropping transaction to commit, as there is nothing to read from anymore.

If PostgreSQL allowed to read from a table being dropped, that would mean two things.  One is, if I am not mistaken, that there existed a READ UNCOMMITTED isolation level.  While you can choose that, it is in fact READ COMMITTED, meaning that no transaction is able to see intermediary states of other transactions.  The other one is, as you already guessed, that possibly only a portion of the desired data is returned.  This would lead to unpredictable behaviour, and that's the last thing you want from a database system.

Moreover, CLUSTER and VACUUM FULL are moving data between two files - how could one decide in which file to chase the rows?

All in all, in some cases reading data is not so urgent.  Or in the case of DROP TABLE and TRUNCATE it is not so urgent anymore.


What databases can I use to represent ordered sets? In a SQL database, the only way to represent an arbitrarily ordered set is to give every record an "Order" and every time you update or move an item around in this ordering you have to update or somehow maintain the entire list of ranks with a nightly job or something like that.

For example, I can represent the ordered set [C, B, D, A] in this way in a SQL database:

ID  Name   Order
1   A      4
2   B      2
3   C      1
4   D      3

If I want to move an item to a different position in the set, or prepend a new item, I may have to update a lot of items. In general there is a lot of maintenance overhead with this approach.

Querying the data once it is in the database is not an issue for SQL, the issue is the significant maintenance overhead of reordering the set. There is no simple operation in SQL to move an item to a new position in the set. The ordering is arbitrary and user-defined.

I realize that this can be accomplished using SQL, it's just very clunky to perform certain operations like prepending items, or moving an item to a new position. Even this example operation of reversing the order of the set requires a pretty lengthy, complex, query. The type of database I'm looking for might support such an operation natively, or at least more elegantly.

So, if I am designing an application (like Trello, for example) that very heavily involves ordered sets, it seems SQL is not the ideal database technology for me. Are there any databases whose syntax support ordered sets in a more natural way?

These are some CQL3 queries from the Cassandra documentation that seem close to what I'm looking for. This prepends an item to an ordered set.

UPDATE users SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';

This one will set the value of the item at position 2 in the set. I suspect I could use this to easily perform arbitrary swaps/reorders.

UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo';

Unfortunately the documentation also states

  And while we may (or may not) relax that rule a bit in the future,
  this still means that collections are not meant to be excessively
  large. They are not a replacement for a proper modelisation into
  tables.

which seems to suggest that ordered sets are not (yet) first class citizens in CQL3.

I think you are missing one of the key principles of relational database design. I thought it was Dr E F Codd but I can't find a reference.

Data is stored without sorting, and is sorted when it is retrieved. This was intended to avoid the overhead of re-ordering the data with every update or insert.

Sorting is done with the order by clause when you select data.

The fact that some vendors store the data ordered by a clustered index is a bit misleading. If your data must be sorted you should always use order by rather than rely upon the the underlying storage.

As you must have some criteria for sorting, why not incorporate that in your query so that data is ordered as it is queried every time you select it.


Getting 'specified network password is not correct' when trying to change password After changing my network password, I went straight to the SQL Server Configuration Manager (SQL Server 2008) to update the passwords for the SQL Server and SQL Agent services. (Background info: the services run under my domain account.)

However, I kept getting the below error after clicking on apply:

The specified network password is not correct. [0x80070056]

I guarantee the password entered was definitely the correct one because I have restarted my workstation and used it to log on successfully. Most answers on the web tend to focus on the password but I know that's just the symptom not the root cause.

I know it is difficult to answer questions like this one unless you have experienced it first-hand. So I am asking if anyone else has seen this behavior before and what they did to resolve it.

I have resolved it. Below is what I did and what fixed it.

Ran "net user /domain " and made sure the account was still active and not locked or expired. (The account was active and healthy)
Restarted the workstation. (The only thing it proved was the password was correct and I managed to log on successfully)
Waited for couple of hours. (Sometimes you just have to be patient.)

After doing all of the above, the issue still remained. In the end, it was the action below that resolved it:

I went to the Services mmc snap-in under Control Panel -> Administrative Tools instead of SQL Server Configuration Manager. Changed the password there and started the SQL Server service successfully. Repeated the steps for SQL Agent.

It worked but I am still puzzled why it worked in Services but not in SQL Server Configuration Manager. Active Directory vs WMI? Anyone?


How do I know an index is used for any queries? I've found a likely abandoned index in the production database. It's not included into .sqlproj from which this database it usually deployed. Most likely it was created for some experiments and then left there but I'm not sure. It's not mentioned in any WITH INDEX hints. It's not used for a PK or unique constraint.

I want to make sure that deleting this index will not cause performance degradation. How can I do that?

Be careful - if you have usage statistics recently reset or you have an index which is so rarely used its usage statistics was cleared long ago - this solution is useless for your scenario and can cause problems.

Make use of sys.dm_db_index_usage_stats

SELECT
   OBJECT_NAME(object_id) TableName,
   (SELECT name FROM sys.indexes Indices WHERE Indices.object_id =
       IndexStats.object_id and Indices.index_id = IndexStats.index_id) IndexName,
   *
FROM sys.dm_db_index_usage_stats IndexStats;

This outputs all indices that recently done any actions to - scans, lookups, seeks and updates. This won't list indices for some idle tables which are not accessed for weeks.

With this output you can look at each index recent usage. If you see that it only has updates then it wasn't used for anything else recently. This likely indicates it can be deleted.


Graph database with Erlang driver I need a graph database system that has an Erlang driver.

Can you advise which database system to use?

I don't see any better hope of answering this, but you can use Neo4J from Erlang...  see https://github.com/nerlo/nerlo/wiki/howto-use-neo4j-from-erlang


Is it possible to use the same directory for 2 MySQL servers? I have 2 OS-es on my machine: Windows XP and Linux Slackware. Both of them have mysql installed:

Windows - 5.5.25
Linux - 5.5.27

Is it possible to use the data directory from Windows on Linux? So the databases will be shared between MySQL instances.

That would not be possible. Why?
MySQL for Windows is very stringent about locking files. It is impossible for two MySQL Instances on Windows to lock on the same ibdata1 files.
From your question, you mentioned launching one MySQL Instance from Windows and another from Linux hitting the same datadir. At the risk of sounding redundant, MySQL for Windows is very stringent about locking files. Trying to lock ibdata1 exclusively, MySQL for Windows will block another instance from anywhere (Windows, MacOS, Linux, etc) from opening a file handle on ibdata1. The reverse is also true: MySQL for Windows will not be able to acquire an exclusive lock on ibdata1 if it is opened from elsewhere (Windows, MacOS, Linux, etc).
I have seen this done using two Linux-based mysql instances, only to see the data go down in flames due to file corruption.
Even if what you asked was possible, there is another issue you have to account for. The following system variables need to be set properly:

lower_case_file_system
lower_case_table_names

The way Windows handles filenames is radically different from Linux. I can easily see foreign key references going haywire when mixing and matching. I have seen one person mysqldump from a Linux instance and load a Windows instance and had all foreign key references vanish on him. Turns out, the source of the problem was the letter casing for the table names. Believe me, you don't want to troubleshoot this one unless aging rapidly is no big deal to you.
SUGGESTIONS
You are much better off doing the following:

Setup MySQL for Windows with its own datadir on a Windows Disk (not using Samba)
Setup MySQL for Linux with its own datadir on a Linux Disk
Setup Replication from the one Instance to the Other (It's your choice which is Master)
Split your reads between the two
Confine writes to just one server
Use TCP/IP to connect to either instance


InfluxDB batch insert only inserts the last line I'm trying to insert some lines into a InfluxDB instance (running on Windows 7 x64) from both a .txt and a .csv file, but it's only inserting the last line on the file.

I'm currently using the Insomnia interface to send the file through Influx's HTTP API.
Example:

I have the following content inside a data.txt file:

any,mytag=ab value=59
any,mytag=ab value=78
any,mytag=ab value=102

All lines are ending with LF only (verifying it through N++).
Then I send this file via an HTTP request to my running InfluxDB instance:

POST http://localhost:8086/write?db=mydb&precision=ns

Yet, when I do a select * from "any" query, it shows that only the last line (value=102) was inserted. I've also tried inserting through text/plain content but to no success as well.

Any idea on this?

Thanks.

I've tested it out and apparently you need the timestamps on the end of each line. Thus the file should look like:

any,mytag=ab value=59 1555003566890381500
any,mytag=ab value=78 1555003566890381500
any,mytag=ab value=102 1555003566890381500

Now I wonder if you can insert them without the timestamp.


CSV or DB is quicker to read several lines from a file with 1,5 billion lines I have a program which would read several lines according to the indices of lines within a CSV file with 1,5 billion lines(using c fgets, or is there any tool in C faster than fgets?). Compare with storing records into a DB (postgesql or any other) table then reads the ID without any join, would the first propose the better way to do it?

Two great and very fast tools for structured text files are:

xsv https://github.com/BurntSushi/xsv
tsv-utils https://github.com/eBay/tsv-utils


How do you get an average of job history times? The following is a script I found online that has most of the elements of what I desire in the result set.  The only thing missing is how I can get an average of the run times over a decided time period, like a week, 10 days, 23 days, a month, etc.

I've done some searching, but what I've tried has failed in some way or another.  Further, my TSQL are weak, but I'm reading publications by Itzik Ben-gan to change this matter.  I'd truly appreciate any help that anyone can provide.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours.

SELECT
  job_name = name,
  avg_sec  = rd,
  avg_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2)
FROM
(
  SELECT
    j.name,
    rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000'
       + CONVERT(VARCHAR(6),run_duration),6),5,0,':'),3,0,':')))
  FROM msdb.dbo.sysjobhistory AS h
  INNER JOIN msdb.dbo.sysjobs AS j
  ON h.job_id = j.job_id
  WHERE h.step_id = 0
  GROUP BY j.name
) AS t
ORDER BY job_name;


Usage?

You have not listed what kind of queries will hit this table.  You can't really design a table, much less optimize it, until the SELECTs are tentatively written.  More later.

First, some simplification

Get rid of id; make PRIMARY KEY(post_id, meta_key, meta_value).  After all, that has to be unique.  The id is clutter and slows things down for the normal operations which involve WHERE post_id = constant AND meta_key = constant.

Because of the 3 tags vs 4 tags you mentioned, the only reasonable way to do it is

DELETE FROM meta WHERE post_id = ? AND meta_key = ?;
INSERT INTO meta (post_id, ... )
    VALUES
    (...), (...), (...), (...);

Then some more simplification

The EAV schema (which you have) sucks.  You may as well have a table called Tags.  Now you can get rid of meta_key, and everything gets simpler and shorter.

You may choose to keep the EAV stuff for where there is only one value for a meta_key.  That is a separate discussion.  I'm arguing that it is wrong to fit multi-valued "tags" into that mold.

Further

Do you really need those timestamps?  WP may be omnipresent, but it is not necessarily 'right'.

Ordering?

When you fetch everything for some tag, don't you want to put them is some order?  And might you paginate it?  So, maybe you need one of those timestamps for this?

Bottom line

CREATE TABLE `tags` (
    post_id ... NOT NULL,
    tag varchar(55) NOT NULL,
    sort_order ... NOT NULL,
  PRIMARY KEY (tag, sort_order, post_id),  -- for the main select, plus dedup
  INDEX(post_id)  -- for maintenance
) ENGINE=InnoDB;

Further discussion


How to link data from two spreadsheets imported to sql I imported 2 spreadsheets to SQL Server tables. One sheet has data containing persons name & surname, the project they are working on as well as there banking details. The other spreadsheet has only the names & surnames  of persons and banking details.

Sheet 2 data needs to be grouped to the right projects like in sheet 1. The only connection I found is that the Reference column in Sheet 2 has numbers that match the ParentID on sheet 1 but with a suffix appended.

Beneficiaries with the same ParentID belong to the same Project. I need a way to extract the numeric values of Reference and match them to the corresponding ParentID in order to group the Beneficiaries in the right project.

sheet 1 :

BeneficiaryID      float         Checked
BeneficiaryName    nvarchar(255) Checked
BeneficiarySurname nvarchar(255) Checked
IDNumber           float         Checked
DOB                nvarchar(255) Checked
BranchnameID       nvarchar(255) Checked
BranchCode         float         Checked
AccountType        nvarchar(255) Checked
AccountNumber      float         Checked
TotalWages         float         Checked
[(No column name)] float         Checked
ParentId           float         Checked
PPRDate            datetime      Checked

sheet 2 :

Reference     nvarchar(255) Checked
[Account No]  nvarchar(255) Checked
[Branch No]   nvarchar(255) Checked
[Bank Name]   nvarchar(255) Checked
[Acc Type]    nvarchar(255) Checked
Amount        nvarchar(255) Checked
[Action Date] nvarchar(255) Checked
Status        nvarchar(255) Checked

Here is some sample
sheet 1

BeneficiaryID   BeneficiaryName     BeneficiarySurname   IDNumber   DOB Branchname ID   BranchCode  AccountNumber   TotalWages      ParentId    PPRDate    Caption

1202            Chris               Manda                123456     4/6/1995 1               ABC         123             120          10125    June 2018  Agriculture

sheet 2

Reference       Account_No Branch_No    Bank_Name   Amount  Action_Date Status

10125_Motsomane 1234       151258865    CDF         1200    15/01/2016 ERROR

The only connection I found is that the Reference column in Sheet 2
  has numbers that match the ParentID on sheet 1 but with a suffix
  appended.

You need to match float value 10125 (column ParentID) on Sheet1 with NVarchar(255) value 10125_Motsomane (column Reference) from Sheet2.

If data types would match you would simply do a direct INNER JOIN:

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = S2.Reference

But to extract the number before the underscore you can use CHARINDEX to find the position of the underscore and then LEFT to get the value up to that position:

DECLARE @ExampleValue NVARCHAR(255) = N'10125_Motsomane'

SELECT
    Original = @ExampleValue,
    Trimmed = LEFT(
        @ExampleValue,
        CHARINDEX(N'_', @ExampleValue) - 1)

Result:

Original            Trimmed
10125_Motsomane     10125

Applying this on the join (will do an implicit conversion from NVARCHAR to FLOAT):

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = LEFT(S2.Reference, CHARINDEX(N'_', S2.Reference) - 1)

Keep in mind that the LEFT function will fail if there is no underscore on a value for Reference, since it will return -1 as position.