Find the value between two tables I am developing a simple course suggestion application. which suggest a degree course to students based on their diploma result.

here are my tables in my database.

+-----------------------------+
| Tables_in_available_courses |
+-----------------------------+
| course                      |
| course_requirements         |
| qualification               |
| result                      |
| student                     |
| student_result              |
+-----------------------------+

A student have multiple qualifications and a course also have multiple qualifications requirements.

This system should print all suggested courses to every student in the database according to their result.

eg: my results are
Diploma in computing - A
Diploma in management - B
Diploma in software engineering - C

and Computer science degree course's pre-requests are
Diploma in computing - A
Diploma in software engineering - C

So the system should suggest to me Computer Science

    mysql> show columns from student_result;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
| student_id       | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show columns from course_requirements;
+------------------+---------+------+-----+---------+----------------+
| Field            | Type    | Null | Key | Default | Extra          |
+------------------+---------+------+-----+---------+----------------+
| id               | int(11) | NO   | PRI | NULL    | auto_increment |
| course_id        | int(11) | NO   | MUL | NULL    |                |
| qualification_id | int(11) | YES  | MUL | NULL    |                |
| result_id        | int(11) | YES  | MUL | NULL    |                |
+------------------+---------+------+-----+---------+----------------+

How to implement it?

WITH cte1 AS ( SELECT cr.course_id, sr.student_id, COUNT(*) cnt
               FROM course_requirements cr
               JOIN student_result sr ON cr.qualification_id = sr.qualification_id
               GROUP BY cr.course_id, sr.student_id
             ),
     cte2 AS ( SELECT course_id, COUNT(qualification_id) cnt
               FROM course_requirements
               GROUP BY course_id
             )
SELECT DISTINCT cte1.course_id, cte1.student_id
FROM cte1, cte2
WHERE cte1.cnt = cte2.cnt
ORDER BY 1,2

fiddle

Add proper conditions by result_id fields values if needed.


sync_binlog different for master and slave Is it fine if sync_binlog is different for master and slave?

For my system I can see master has sync_binlog=1 and slave has sync_binlog=0.

Yes, as MySQL replication happens logically (either statements or rows are replicated) most of the options can be different between masters and slaves.

In particular, your configuration makes quite sense in several scenarios- you want your slaves to be faster than the master to avoid replication lag. You sacrifice this way consistency of the binary logs- something you may not want to do on the master (assuming it is a SPOF for you), but allowing the slave to get corrupted/loss file writes if it crashes (or maybe you do not care about that binary log at all on the slave).


Does firing off an indefinite WAITFOR increase the log file size? In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue:

WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)

The DBAs tell me that since the addition, the log sizes have gone through the roof. Could this be correct? Or should I be looking elsewhere?

Any active open transaction will pin the log, preventing truncation and eventually causing growth. If you start a transaction, write to the log and then wait forever in hope a message will eventually awake you, you just pinned the log and cause it to grow.

Lately I started recommending people to shun the WAITFOR in activated procedure, along with the loop. Just issue a RECIEVe and be done, let the activation mechanism loop for you (it does) and don't WAITFOR, just plain RECEIVE.

The WAITFOR flavor of RECEIVE creates a savepoint internally. This generates log (at least 3 log records) and does indeed pin the log in place while waiting. Having a long WAITFOR timeout (or worse, an infinite one) would be a very bad practice.


T-SQL Gurus, I need help optimising these queries for performance I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids

In a production environment the table is expected to hold up to 1,000,000 records. Here is what I have upto now.

    /* dbo.Bids structure
    bid     decimal (10,2)
    auctionid   bigint
    msisdn      bigint
    lineid      bigint
    timestamp   bigint

CONSTRAINT [PK_Raw] PRIMARY KEY CLUSTERED
(
    [auctionid] ASC,
    [bid] ASC,
    [msisdn] ASC,
    [lineid] ASC,
    [timestamp] ASC
)

    dbo.bids test data:

    bid     auctionid   msisdn  lineid  timestamp
    1.01    1011        9999    1       0
    1.20    1011        8888    2       1
    0.98    1011        7777    4       2
    1.01    1011        6666    5       3
    0.98    1011        5555    7       4
    0.99    1011        6666    8       6
    0.99    1011        4444    9       6
    0.99    1011        6666    10      6
    0.99    1011        3333    11      8
    1.20    1011        1234    13      10
    1.01    1011        8888    14      11
    */

    DECLARE @auctionid bigint;
    DECLARE @msisdn bigint;
    SET @auctionid = 1011;
    SET @msisdn = 8888;

    SELECT TOP 1 [bid], [msisdn] FROM [dbo].[bids]
    WHERE [auctionid] = @auctionid AND [bid] =
        (
            SELECT [bid] FROM
                (
                    SELECT TOP 1 [bid], COUNT([bid]) AS [tally] FROM [dbo].[bids]
                    WHERE [bid] >= 0.01 AND [auctionid] = @auctionid
                    GROUP BY [bid] ORDER BY [tally] ASC, [bid] ASC
                ) AS LowestTally
         ) ORDER BY [lineid] ASC;

    /* RESULTS SHOULD BE
    bid msisdn
    0.98    7777
    */

    SELECT msisdn, MinBid, Ranking FROM
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN
    (select [bid] from [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = (SELECT [tally] FROM (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally) )
     GROUP BY msisdn ) AS Temp  ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    7777    0.98    1
    5555    0.98    2
    8888    1.20    3
    1234    1.20    4
     */

    SELECT msisdn, MinBid, Ranking FROM
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN
        (SELECT [bid] FROM [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) =
            (SELECT [tally] FROM
                (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally))
     GROUP BY msisdn ) AS Temp  WHERE msisdn = @msisdn ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    8888    1.20    3
     */

For the first query, this version eliminates a CI seek and a TOP N Sort, and cuts the reads against dbo.Bids in half. It performs more reads overall, but those are caused by a worktable, not an actual table. I believe this gives your desired results, but without more sample data, I can't be sure that it matches your requirements.

SELECT TOP (1) bid, msisdn
  FROM dbo.Bids
  WHERE bid >= 0.01 AND auctionid = @auctionid
  ORDER BY COUNT(*) OVER (PARTITION BY bid), bid, lineid;

For your second query, this gets rid of a CI seek, a sort, a nested loop join, and a few other operators:

;WITH x AS
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid
  FROM x ORDER BY tally
)
SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
FROM y ORDER BY ranking;

For the third query, this gets rid of two sorts, a CI seek, and cuts the table reads in half.

;WITH x AS
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid
  FROM x ORDER BY tally
),
z AS
(
  SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
  FROM y
)
SELECT TOP (1) msisdn, MinBid, ranking FROM z WHERE msisdn = @msisdn ORDER BY ranking;

For all of these, you'll have to test whether this leads to better or worse overall performance in your environment. I only tested (both accuracy and plan shape / duration) using your limited sample data. Things could go askew depending on data distribution, cardinality estimates, any other indexes, etc. etc.


A function to check if a column allows NULL Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise?

I would be something like:

UPDATE mytable
SET field = IF(A_FUNCTION_TO_CHECK_IF_ALLOWS_NULL(), NULL, '');

You can query the INFORMATION_SCHEMA:

http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

In the INFORMATION_SCHEMA.COLUMNS table there is a IS_NULLABLE column.

You could turn it into a function, I imagine, but I would probably put this logic in an outer part.


PostgreSQL : cannot connect with a created role I am using PostgreSQL 11.3 on Fedora 30. With the postgres user, I can use psql or connect to the cluster in PgAdmin 4, but when I create another user, for example :

CREATE ROLE myuser LOGIN PASSWORD '…';

I always get an error like :

FATAL: password authentication failed for user "myuser"

I had a look at other similar questions here (including this one, but I don't have a case folding problem), and I still cannot make it work.

Here is my pg_hba.conf file :

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

What I am doing wrong? Thank you for any help

I found where the problem was! I had changed the line

password_encryption = scram-sha-256

in /var/lib/pgsql/data/postgresql.conf

but had forgotten to uncomment it…


Chat/conversation history, entity relationship diagram Totally aware that there are very similar questions, went through each of them, but those are pretty simple/basic solutions from where i took some things and ideas to create below entity relationship diagram, according to my needs. What i'm up to is to create database schema (postgresql) for chat/conversation history, where i need to support couple of tricky things, such as:

Chat have a name and initial creator (customer_id)
Chat message created by customer_id can either be a plain text message or a file, while
those two are sharing some of the columns, but in a sense those are
different in a way that file can have file_name, file_mime_type,
external_path like location where it is saved (e.g file system),
and file can be marked as being relevant or not, by anybody in
conversation. Due to many differences between the two, to me at least, seems
more natural to divide the two and to have "common parent" table, chat_lines. Other approach would be to have single table with many NULL values or so. Highly interested to see your opinion about it.
One of the biggest worries that i have is that chat participant being
able to reply_to existing chat line or start a thread on existing
chat line. I have no better idea than to make chat_lines table having two separate
references to itself. I do not see any better way of how to model threads and replies.
Each message can be seen by anybody in discussion, hence
introducing seens table. Not quite sure is seens correct english
word.
Finally anybody in conversation/chat can react to each chat line,
like thumb up, thumb down, no more than that, so ENUM seems like
a nice approach.

After hours and hours of thinking i came to this:

One of the worries here for me is that chat lines table have become like a God table, everything is concentrated around that table and probably every future table will be related to it. Further, each SELECT query must JOIN literally everything in order to rebuild chat history. Worrying about performances a lot, would expect that chat_lines table will become huge over time, just imagine that one single chat between 5 to 10 participants can have around 1k-5k chat lines, with few file uploads along the way. Looking for proper indexes on those table. Each SELECT query that i can imagine now will be probably using heavily chat name, customer ids (like participants of the chat). So thinking to create some indexes on those.

Of course, whatever participant can search for mentioned things. When it comes to plain text messages, seems like way to go is postgresql tsvector against text column in messages table. Participant will be able to search against file_name when it comes to file uploads, so seems reasonable to have index on file_name in files table.

Please sorry for a little bit "opinionated question", highly interested in your opinion about how much this solution will scale in the future, would highly appreciated any advice what could be changed. Any other idea how replies and threads could be modeled? Where you would add indexes? Whatever i do, i guess database partions based on something from this schema should be created in future when data starts to grow very much.

Some loose opinions which may be useful but may be completely off:

The model looks OK and should be scalable as far as I can tell.
Relational model (3NF) is the king of storage size, and data consistency. It's not the king of "easy querying". See customer_id note below.
In actual long term business, maintainability is often more important than performance.
Don't worry about partitioning too soon. You may never see this first 0.5 TB of customer data in your database. But consider making customer_id the part of PK in all tables if this is going to be a multi-tenant app. It will make scaling up (think: sharding / separating tenants) easier.
The thread ID might be as well synonymous to chat_lines.id of top level item.
tsearch is OK, but for large scale, fancy fulltext search you might need to employ some outside tools like ElasticSearch / Sphinx / etc.


Insert values from a record variable into a table I am developing a user-defined function that takes two arguments:

create or replace function gesio(
    events_table_in regclass,
    events_table_out regclass)
returns void as $$ ... $$

events_table_in and events_table_out have exactly the same schema.

Simply explained, I loop through the records of events_table_in, manipulate the records and want to append (insert) the manipulated records into events_table_out in the following fashion:

OPEN recCurs FOR execute
format('SELECT * FROM %s order by session_id, event_time', event_table_in);

LOOP
    FETCH recCurs into rec;
    if not found then
      exit;
    end if;

    -- 1. do something with rec

    -- 2. insert the rec into events_table_out

end loop;

How can I save the rec into events_table_out?

There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).
CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):
SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.
Note that the polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

Insert values from a record variable into a subclass table

Major ingredients

Implicit cursor of a FOR loop instead of explicit cursor. That's generally preferable.

Polymorphic types

Object identifier types

Dynamic SQL in plpgsql

VALUES can take a row type directly.

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.
I am handing in a NULL value of type t, which serves three purposes:

Provide table name.
Provide table type.
Serve as loop variable.

The value of the first parameter is discarded. Use NULL.
Consider this related answer on SO with more details. The most interesting part being the last chapter Various complete table types.
SQL Fiddle demo.
If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.


Few Insert queries rollbacked due to system crash Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked.

I am sure that they were proper inserts as we fetch results after inserts to print a PDF and PDF was generated properly. and system was crashed after 2-3 minutes of that INSERT operation.

System is PHP - MySQL based and we are using MyISAM as storage engine for our tables with autocommit ON

This is unique as earlier, after a system crash we normally had crashed tables but nothing like rollback operation. all the table are intact and operational after system crash.

Can anyone explain to me how is that possible if autocommit is ON. Thanks in advance

MyISAM does not support transactions, commits, or rollbacks. You have to switch to the InnoDB.
InnoDB Architecture

InnoDB has mechanisms, log files, and associative memory structures for rollback, commit, transaction isolation, and MVCC.
MyISAM has no infrastructure like this. Each MyISAM table is its own entity.
Here is a great reason to switch to InnoDB: MyISAM does not cache data. It lets the OS do that. That's a scary thought for MySQL/Linux, and horrific nightmare for MySQL/Windows. I wrote about this difference in the past : What are the main differences between InnoDB and MyISAM?
Thus, in the event of a crash, not only do you lose any data the OS did not flush to disk, but you also may have to run CHECK TABLE/REPAIR TABLE to fix open file counts in the headers of the MyISAM tables affected.
In your case, you are playing Russian Roulette relying on data from a MyISAM tables after a server crash or a mysqld crash.
Please see my past posts on using and tuning InnoDB

When to switch from MyISAM to InnoDB?
Which first: upgrade mysql version or convert storage engine? (Code Provided to Convert to InnoDB)

Give it a Try !!!
UPDATE 2014-01-03 10:29 EST
In your comment, you asked

is it possible that data that was inserted 2-3 minutes before crash also lost in MyISAM?

Absolutely !!! I actually saw this happen three ago. I create a MyISAM table in /dev/shm (Shared Memory). I loaded it with a 3GB .MYD and 2GB .MYI. While loading it, I ran ls -l on the table and it kept saying 0 bytes for .MYD and 1024 bytes for .MYI. After 5 min, I killed the mass INSERT. Suddenly, data and index pages started to pour into the table files. Had mysqld actually crashed, those data and index pages would never have made it to the table. In your case, this would especially be true for all open MyISAM tables in a write-heavy database environment.


Converting VARBINARY HEX to VARCHAR I have a fun problem,

I have data stored in SQL Server 2008 VARDATA column as Hex.

I need to see what this translated to string is.

I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a Varchar value of the hex exactly the same as it is in the VARDATA.

If I put the value that is in my VARDATA into an online converter I get the exact value I expect back.

Any help would be appreciated.

Details.

Example of the Hex is

0x003200300031003400300037003200340030003000300035003600380038003300300039

It should work out to.

201407240005688309

EDIT: some additional information. This data starts out in ORACLE it is in a RAW type column.

We bring the data to SQL Server 2008 via Linked Servers using the below open query.

Select TABNAME, VARKEY, DATALN, VARDATA
from sapsr3.KAPOL

When I use the suggested function on this SQL Server 2008 table below.

Select CONVERT(NVARCHAR(MAX),VARDATA)
from KAPOL

I get these symbols:

㈀　　㄀　㄀　㄀　　　　　　㜀㜀㠀㠀

If I do the conversion of HEX in Oracle first and then try and bring it over I get an empty column.

Select TABNAME, VARKEY, DATALN, utl_raw.cast_to_nvarchar2(hextoraw(VARDATA)) VARDATA
from sapsr3.KAPOL

Lastly, in the first instance with no conversion in the oracle table the data type for the column VARDATA is VARBINARY

When I try the conversion in Oracle first is NVARCHAR

You need to have a distinction between varchar and nvarchar. There is a big difference. If I try to create varbinary from your "should work out to" value:

SELECT CONVERT(VARBINARY(64), '201407240005688309')

I get this value, which looks kind of like the value you're converting, but not quite:

0x323031343037323430303035363838333039

So now if I change the input to be nvarchar:

SELECT CONVERT(VARBINARY(64), N'201407240005688309')

Now I do get the value you wanted:

0x3200300031003400300037003200340030003000300035003600380038003300

So if I take that value and try to convert it to nvarchar instead of varchar, we're back where we started:

SELECT CONVERT(NVARCHAR(MAX),
  0x3200300031003400300037003200340030003000300035003600380038003300300039)

Result:

201407240005688309

The online converter you're using must be using Unicode behind the scenes. You need to do that, too.


Archive partition before delete it? I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use percona tools or mysqldump itself?

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' ))) THEN
    SET @stmt := CONCAT(
        'ALTER TABLE testPart1 DROP PARTITION '
    ,   ' p'
    ,   DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL 14 DAY ), '%Y%m%d' )
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'testPart1' AND PARTITION_NAME =
  CONCAT(' p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' ))) THEN
  SET @stmt := CONCAT(
        'ALTER TABLE testPart1 ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 2 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 2
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;

If you're going to use Percona tools, you could just use pt-archiver, which will do this part for you as well.

But mysqldump will work just as well here, since you've customized this archiving for your schema.


Why are event sourcing (CQRS) databases not popular? The idea of a database storing events and showing the net result of these events is realistic to the real world (and what actually happened).

Given the flexibility event sourcing databases offer (e.g. view the database at different points in time), why are such databases not more popular?

For example the only two databases I am aware of, that do event sourcing are Datomic and Event Store.

Arguably the write-ahead-log that pretty much every SQL (and many no-sql) product uses is an event stream, allowing rebuild of objects (tables) in the event an instance fault. Granted most of them are opaque binary formats unsuited for application interrogation.

For that the ANSI standard now defines temporal features for SQL. This records the interval during which a particular value was valid. Recreating history is a matter of including an "as of" date-time value in the predicate. There are products specifically for storing time series data efficiently at volume.

The net result of all change is exactly what each table/ document/ columnstore provides to applications.

To answer the actual question - the outcomes offered by CQRS stores are mostly available from other, better known and supported products. The business risk of adopting one may outweigh the functional advantages so the products are not widespread.

CQRS can be considered a design pattern as much as it can be considered a product. As such any mainstraim store could implement a CQRS.

Accounting, banking and finance systems in general will store full information about each transaction i.e. each state change. Think of your bank statement - it has a complete list of all the credits and debits which occured within the reporting period. Chances are your bank uses a relational database to achieve this. I know Xero uses SQL Server. It is a tenet of double entry book keeping (accountancy to you an me) that no change can happen to an account without a transaction (ie an "event") and every transaction, once recorded, is immutable.


Connecting SQL Server Developer Edition to Visual Studio Windows Forms Application I downloaded the free SQL Server Developer Edition in hopes of being able to use SQL Server for my Visual studio project, but I can't seem to understand how to connect it. :(

In my SQL Management Studio I never received login credentials to log in with, for the server named LAPTOPXXXX/OwnerName, so I can't connect through SQL Authentication in Visual Studio. Nor is there a create new credentials option anywhere to be found. Also when trying to connect to the server using my computer name or IP address it also fails to connect to the server I downloaded.

How do I make log in credentials for my free server and how do I connect it using like a web.config or app.config file's connectionstring (but winforms version)?

1) Create an application user for your app on the SQL Server instance.  Never use an administrative user or SA.

2) Create a DSN (Data source Name) on the machine that the app will connect to the SQL Server database.  This applies even if the two are on the same machine (which would be poor architecture, but possible in a solitary dev environment).

2a) The login credentials for this DSN should be the app's user in SQL Server

3) Go to ConnectionStrings.com and generate a SQL connection string based on the DSN you just made. (https://www.connectionstrings.com/dsn/)

4) Use the connection string in your app/web config and reference it in the SQLConnection call.


Is it possible to store historical configuration settings for each row of data without cramming all the configuration settings into each row of data? For background: I was recently hired as a database engineer for a water treatment company. We deploy water treatment machines to sites across the country, and the machines treat water and send continuous data back to us regarding the state of incoming water (flow rate, temperature, concentration of X in incoming water, etc.), and regarding the treatments the machine applied to that water at that point in time. Over time, sites (and their various components) change a lot: a machine might break down and need to be replaced, a different concentration of chemical may be used to fill the machine's tanks, its flow meters and other sensors might be recalibrated or set to scale differently, its chemical pumps might be replaced, and on and on. These affect the interpretation of the data: for example, if 5 mL of chlorine was added to the incoming water at 01/01/2021 12:00:05, that means two completely different things if the chlorine was 5% concentrated or 40% concentrated.
Water treatment datapoints are identified by a composite key consisting of the ID of the site, and a timestamp. It would be easy to store configurations if the only data that mattered was current data, as I could keep configuration settings in the Site table and pull them up for datapoints as needed. But we need to be able to correctly interpret older data. So, I thought about storing configurations in its own table table, which would track all the settings for each site over each time period, but it's not possible to create a foreign key between the continuous timestamps of the datapoints and the start/end dates of the configurations - the closest thing would be some kind of range check, like "Datapoint.TimeStamp BETWEEN Configuration.Start AND Configuration.End". So the only other option I see is to store every configuration setting for every datapoint alongside each datapoint, but that seems like a terrible solution given how many configuration settings there are and how many datapoints are generated, especially since most of the settings don't even change often.
So, is there a way to store historical configurations for each row of data in a way that is at all normalized, or is the only possible solution to cram all the settings into each datapoint?

Here are some random thoughts. I guess the configuration could change without the existence of a datapoint at that particular point. First I would have a table for the sites:
CREATE TABLE sites
( site_id ... NOT NULL PRIMARY KEY
, <other attributes>
);

Now, you can have your table relate to that
CREATE TABLE measurements
( site_id ... NOT NULL REFERENCES sites (site_id)
, measurement_ts ... NOT NULL
, <measurements>
, PRIMARY KEY (site_id, measurement_ts)
)

It is not clear to me from your description if you should futher normalize the measurement values or not, so I'll leave them as-is.
Now you can add the configuration as:
CREATE TABLE configurations
( site_id ... NOT NULL REFERENCES sites (site_id)
, begin_time TIMESTAMP NOT NULL
, end_time TIMESTAMP
, ...
, PRIMARY KEY (site_id, begin_time)
)

Neither is it clear for me whether the configuration values should be further normalized, so I'll leave that as well
When you change the configuration, you close the previous one by setting end_time to now, and add a row with begin_time = now, end_time = null.
@J.D. mentions one of the commercial vendors with some support for temporal data as an alternative. You may want to have a look at IBM Db2 community edition as well. The limitations are 100Gb storage, 4 cores and 16Gb ram. In case you are interested, you can find out more at ibm-db2-developer-community-edition
For temporal tables, in particular, you can find more info at Time Travel Query using temporal tables


Cannot restore database with transaction log I make every day at 0300 AM a Full Backup (NOT a COPY-ONLY) of a database (11 GB) and every minute (from 03:05AM till 02:55AM) a TRANSACTION LOG Backup.
When I tried to Restore to a point in Time eg 03:07AM there is an error that the backupsets are not inline.

When I took a look at the backupset table in msdb database I see all the backups (FULL en LOGS), but the first_lsn of the First TRNLOG after a FULL Backup is not the same as the last_lsn of the FULL backup, but it is the same the last_lsn of Prev TRNLOG (which is before the FULL backup).

So it is not possible to restore the FULL backup and eg the First TRNLOG backup.

What is going wrong in my backup process?

See image below

To restore to 03:07 AM you would require the following backup files:

SW_SAM_GEMAAL_backup_2017_09_21_030159_6588486 (FULL)
SW_SAM_GEMAAL_backup_2017_09_21_030501_0515333 (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001 (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030701_....... (TLOG)
SW_SAM_GEMAAL_backup_2017_09_21_030801_....... (TLOG; possibly)

In your screenshot you are only listing the files up until the SW_SAM_GEMAAL_backup_2017_09_21_030601_6958001 TLOG backup.
However, this file does not contain any modifications past 03:06:01 AM and this is possibly why you are unable to restore to the point-in-time at 03:07:00 AM.

The FULL backup is always consistent as is stated by the official Microsoft (Docs) documentation (emphasis mine):

  DATABASE

  Specifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up. During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored.

  When you restore a backup created by BACKUP DATABASE (a data backup), the entire backup is restored. Only a log backup can be restored to a specific time or transaction within the backup.

Reference: BACKUP (Transact-SQL)

Depending on your error message, you might find that because you ommitted one TLOG backup file, that the restore is failing.

Reproducing Issue

Inserting data / creating backups

I reproduced your situation on my local SQL Server with the following steps and was able to restore to a point-in-time with a transaction still open during the FULL and TLOG backups:

-- 11:23 BEGIN TRANSACTION WITH INSERT
BEGIN TRAN TESTBACKUP
INSERT INTO dbo.Good_Morning
(
    Test
)
VALUES
(
    'A Text '
)
-- 11:24 TLOG Backup
-- 11:25 FULL Backup
-- 11:26 NEXT INSERT
INSERT INTO dbo.Good_Morning
(
    Test
)
VALUES
(
    'A Text 2'
)
-- 11:27 TLOG BACKUP
-- 11:28 COMMIT
COMMIT TRAN TESTBACKUP
-- 11:29 TLOG BACKUP

Backup History

My backup history looked like this:

database_name backup_start_date       backup_finish_date      backup_type physical_device_name                                                          first_lsn         last_lsn          checkpoint_lsn    database_backup_lsn
AdminDB2      2017-09-21 11:24:08.000 2017-09-21 11:24:08.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112408.trn   35000001910900001 35000001911200001 35000001908000042   35000001908000042
AdminDB2      2017-09-21 11:25:15.000 2017-09-21 11:25:15.000 Full        C:\SQL\Backup\NB31710\AdminDB2\FULL\NB31710_AdminDB2_FULL_20170921_112515.bak 35000001911100002 35000001913500001 35000001911700042   35000001908000042
AdminDB2      2017-09-21 11:27:03.000 2017-09-21 11:27:03.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112703.trn   35000001911200001 35000001914300001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 11:29:06.000 2017-09-21 11:29:06.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112906.trn   35000001914300001 35000001914600001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 11:31:59.000 2017-09-21 11:31:59.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_113159.trn   35000001914600001 35000001914900001 35000001911700042   35000001911700042
AdminDB2      2017-09-21 12:15:00.000 2017-09-21 12:15:00.000 Log         C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_121500.trn   35000001914900001 35000001915200001 35000001911700042   35000001911700042

Restoring Database

I then restored my database to 11:28:00 AM with the following statements:

RESTORE DATABASE [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\FULL\NB31710_AdminDB2_FULL_20170921_112515.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112703.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [AdminDB2] FROM  DISK = 'C:\SQL\Backup\NB31710\AdminDB2\LOG\NB31710_AdminDB2_LOG_20170921_112906.trn'
WITH FILE = 1, NOUNLOAD, STATS = 5, STOPAT = N'2017-09-21T11:28:00'

Solution

Add your last TLOG backup of your database to the restore statement and you should be able to restore to your point-in-time at 03:07:00 AM.


Botched migration from 9.4 to 9.5 and I'm not sure how to fix it For reasons I can't totally explain, I had both 9.4 and 9.5 installed, which was giving me a headache as I tried to enable PostGIS, which was only installed for 9.5.

I used Synaptic to uninstall postgresql-9.4, and restarted my server with sudo service postgresql start but now I'm finding that I have a bunch of issues:

PGAdmin can't connect. I'm connecting over localhost on port 5432 -- that wouldn't have changed. The connection error says:

  Server doesn't listen
  The server doesn't accept connections: the connection library reports
  could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

In addtion, psql can't see my old databases, and it doesn't recognize any of the roles that I had set up.

I feel like I'm missing something basic here.

Since you had 2 instances installed, the first (I guess 9.4) was using the default 5432 port so the 2nd got another port (probably 5433). When you uninstalled the first instance, the 2nd doesn't know anything about it and still uses the 5433 port.

You can use pg_lsclusters to find what instances have been installed, and some more details like whether the are running or not, their ports, their data directories, etc. Example:

ian@jupiter:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
9.3 main    5437 online postgres /var/lib/postgresql/9.3/main   /var/log/postgresql/postgresql-9.3-main.log
9.3 test93  5438 down   postgres /var/lib/postgresql/9.3/test93 /var/log/postgresql/postgresql-9.3-test93.log
9.4 main    5435 online postgres /var/lib/postgresql/9.4/main   /var/log/postgresql/postgresql-9.4-main.log
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main   /var/log/postgresql/postgresql-9.5-main.log
9.5 repl    5439 online postgres /var/lib/postgresql/9.5/repl   /var/log/postgresql/postgresql-9.5-repl.log
9.5 stage   5433 online postgres /var/lib/postgresql/9.5/stage  /var/log/postgresql/postgresql-9.5-stage.log
9.6 main    5434 online postgres /var/lib/postgresql/9.6/main   pg_log/postgresql-%Y-%m-%d_%H%M.log
9.6 repl    5440 online postgres /var/lib/postgresql/9.6/repl   /var/log/postgresql/postgresql-9.6-repl.log
10  main    5441 online postgres /var/lib/postgresql/10/main    /var/log/postgresql/postgresql-10-main.log
ian@jupiter:~$

So the solution is pretty simple. Tell pgAdmin to connect to port 5433 or whichever port your 9.5 has been setup to listen to.

Alternatively, change the configuration of your 9.5 (second and now only one) instance to use port 5432. You need to edit the postgresql.conf file and restart it.


Changing tx_isolation and binlog format on master server - do i also need to adjust slave? We are running a Drupal Commerce site with MariaDB 10.0 and master/slave replication. Because of the way our application works, we end up running in to a ton of deadlocks under heavy load. Apparently this is a fairly common problem for large Drupal Commerce sites.

We've successfully tested changing two settings on our test servers (which are standalone; not master/slave) which has eliminated the deadlock problem:

transaction-isolation = READ-COMMITTED
binlog_format = ROW

My questions are:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

3) Do we need to 'stop slave' while applying this change?

The answers we got back from one of the senior DBA's at our hosting provider are as follows. I'm sharing in case anyone else runs in to this.

  It is generally quite critical to have the same settings on the slave
  as the master. An easy reason recommend this is that you might promote
  that slave to master some day and do not want surprises. There are
  other technical reasons for this, so I suggest that the slave use the
  same settings (aside from server-id and maybe the names of log files)

  You can technically change the settings dynamically without a restart
  but this is pretty tricky to get right since existing connections will
  use the old settings and a restart is sometime faster than trying to
  update all sessions to use a new binary log format by forcefully
  disconnecting them, then repeating the process with tx isolation (this
  matters for long lived connections which may not exist in your
  environment)

  I would suggest you add the settings to the slave and master's my.cnf,
  then restart them in any order. You do not need to manually run "stop
  slave" or "start slave"

  The slave does not have binary logging on, nor log-slave-updates, so
  there is nothing specific about the order of operations to manage.

To recap:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

Both, primarily to be able to promote a slave to master without surprises.

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

Not really. Just adjust settings and restart in any order.

3) Do we need to 'stop slave' while applying this change?

No.


how to find the database context of the last sql statement? I would like to find out from the code below,
what was the name of the database where the last command was run.

In this case I am looking for my_other_database.

his there any way of finding this?
Is there any DMV that I should be looking at for this info?

Also, what if the update was wrapped in a dynamic sql, would it be possible to track it then?

use my_database
go

begin tran t1

UPDATE my_other_database.REF.applicationReference
SET uploadPaperReferences = 0
WHERE REFERENCEID IN (
69361,
69690,
69354,
69358,
69362,
69732,
69863,
70187
)

commit tran t1

what am I trying to accomplish?
something like on the example below
at the end I print out the name of the server and the name of the database that I am on:

but on the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

is there a way to print out apcore, instead of cola?

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN T1

PRINT ''
PRINT 'AFTER BEGIN TRAN'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT '@@SERVERNAME: ' + CAST(@@SERVERNAME AS VARCHAR) + CHAR(10) + 'Databasename: ' + DB_NAME()
PRINT ''

DECLARE @row INT

USE COLA
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

            update t
            set documentstateid = 5
             from apcore.upl.applicationDocument t
            where applicationid in (
                                    319761
                                    ,320455
                                    ,333433
                                    ,351642
                                    ,371539
                                    ,372508
                                    )

             and documentStateId not in (1,5)

SELECT @row = @@ROWCOUNT

PRINT ''
PRINT 'AFTER RUNNING THE UPDATE(s)'
PRINT ''
PRINT 'The number of rows updated:' + SPACE(1) + CAST(@row as varchar)
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()

COMMIT TRAN T1

PRINT ''
PRINT 'AFTER COMMIT'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()

The context of this script is my_database. It's just that the query references objects in a different database. Consider a scenario where a single query uses objects in multiple databases. Which database would you want returned?

What are you trying to accomplish?


Any better way out of MySQL InnoDB log "in the future"? I've got this InnoDB error in MySQL 5.0.  Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward.  Now after a clean startup, InnoDB has done its "crash recovery".  I went through the innodb_force_recovery=4 business, repaired a hung MyISAM table, and now replication is ready to go, apart from this.  Big numbers commified:

111116 15:49:36  InnoDB: Error: page 393457 log sequence number 111 561,760,232
InnoDB: is in the future! Current system log sequence number 70 3,946,969,851.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: for more information.

This is on a slave server.  The above error spews by the hundreds.  I found this answer: "insert and delete > 64 GB worth of data, so that the log sequence number becomes inflated big enough".

http://forums.mysql.com/read.php?22,50163,50163#msg-50163

That magic number of 64GB comes from 4GB*16 where that guy's innodb log "major number" needed to increase from 0 to 15.  Mine's going from 70 to 111 = 164 GB.  This will take 5 days.  I'll keep working on speeding up my script, and running it in parallel to speed this up.  In the meantime, I'm hoping someone else has a better answer.  This is silly.

This was a pretty rare situation.  I hope to never end up there again, with an InnoDB "log sequence number is in the future!" error.  Because of my particular details, rebuilding/restoring my server's data was a last resort.  Some cheats in helping that were good ideas, but in the end, I decided to just keep improving my Perl script to play this silly game and churn through as many gigs/hour as I could.  What the heck, it's a good system stress test.

Remember: the goal is to increase a single counter ("log sequence number") which is stored somewhere in the headers of ib_logfile0 and ib_logfile1.  This is to fake out InnoDB so it will ignore an apparent time warp and get on with life.  But no one knows how to edit that number. Or if they do know, no one's talking.

Here's my final product.  YMMV, but using mysql's REPEAT function to generate the data internally is highly efficient.

 #!/usr/bin/perl
 use DBI;
 $table = shift || die;
 $dbh = DBI->connect("DBI:mysql:junk:host=localhost", "user", "pass"); #Edit "junk" (DB name), user, and pass to suit.
 $dbh->do("DROP TABLE IF EXISTS $table");
 $dbh->do("CREATE TABLE $table (str TEXT) ENGINE=INNODB");
 $sth = $dbh->prepare("INSERT INTO $table (str) VALUES (REPEAT(?,1000000))");
 foreach (1..50) {
    $sth->execute('0123456789');   # 10 MB
 }
 $dbh->do("DELETE FROM $table");

My suggested recipe:

Create a 'junk' database
Save the above perl script as junk.pl.
Run junk.pl data1, and junk.pl data2, and junk.pl data3, etc. all at once, for as many CPU cores as your database server has, to start.  Open multiple shells and wrap each run in a Bash loop: while true; do date; junk.pl dataX; done.

Watch your LSN grow, perhaps in another loop:

 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 3871092821
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 124 4209892586
 silly# echo "SHOW INNODB STATUS \G" | mysql -p'xxxxxx' | grep '^Log seq'
 Log sequence number 125 85212387

The big number is an unsigned 32-bit INT which will wrap at 4GB, increasing the smaller number every time.  In this case above it just rolled from 124 to 125.  Your goal is hidden in the mysqld.log that sent you Googling for this ridiculous solution in the first place.  Once you cross that finish line, that's it!  Blow the horns!  Release the confetti!

  Sidebar: This uncovered an interesting bug in mysqld 5.0 w/REPEAT: if you go to 20 MB, it flips some internal counter and rolls over to ~ 96 KB.  No warning or error anywhere.  I wasn't about to waste time tracking that down.  10 MB works great.  If you do hit some other limit, it may complain.  I have various innodb buffers increased from default.  Season to taste.  As always, watch mysqld.log in one window.


Difference between star schema and data cube? I am involved in a new project, where I have to create data cube from the existing relational database system.

I understood that, the existing system is not properly designed, I am not sure where to start.

My question are:

What is difference between Star Schema and data cube?
Where I have to start? From star schema or directly data cube?
Is data cube generated from star schema?

I have little experience in relational data modeling, this question may seems too basic, I tried to figure out from few resources, still not clear. Please give your advice and suggestions?

If I missed, something very important related with this question, please share your thoughts on that too.

After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this answer satisfies me and help me to start the task. On the process, I hope I get better understanding (if exists) of these techniques. Here is my findings:

Difference between Star Schema and data cubes:

  Star schema is a dimensional modeling technique. It contains,
  Dimensions and Facts (business measurements). Mostly used in Data
  warehouse technology.

  Data cube is a multi-dimensional table. It means, combination of
  dimension and fact tables. Mostly used in OLAP analysis tools.

  Data cubes are built on the star schema to improve the query
  performance - performing aggregate and summarizing measurements.

  It will pre-calculate the values, instead of calculating on the fly,
  so it boosts performance.

  Example: Total item, Sales Amount, etc

Where I have to start?

I realized I have to start from, star schema and build data cubes on it. Data cubes are not built on the DBMS system, but outside the DBMS system as aggregates and other operations.

I hope this answer will help the people who are new to this technologies. If I am missing something or understanding wrongly, please correct it. Thank you.


how to prevent an oracle block corruption from duplicating a database with a few nologging tables I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option.

Oracle support suggested to turn on the force logging option when perform a duplicate.  I wish that i do not have to change the production settings in order to make a duplicate db in Test.

Any suggestions to prevent data block corruption in the target db from my next duplicate?

ORA-01110: data file 14: '+DATA/xxxxx/datafile/xxxxx01_02.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426036):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27467)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426037):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27019)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option
Errors in file /xxxxx/app/oracle/diag/rdbms/xxxxx/xxxxx/trace/xxxxx_j000_67043338.trc (incident=1426038):

ORA-01578: ORACLE data block corrupted (file # 15, block # 27883)
ORA-01110: data file 15: '+DATA/xxxxx/datafile/xxxxx01_03.dbf'

Oracle enterprise version 11.2.0.3.
Here is the rman script:

SPOOL LOG to '/home/oracle/scripts/REFRESH/target_db_duplicate.150601_1019.log'
run {
allocate channel D1 type sbt_tape;
allocate auxiliary channel T1 type sbt_tape
    parms='ENV=(NB_ORA_CLIENT=server_name,NB_ORA_COPY_NUMBER=3)';
set until time="to_date('01-JUN-2015 02:00:00','DD-MON-YYYY HH24:MI:SS')";
duplicate target database to target_db
    pfile=$ORACLE_HOME/dbs/inittarget_db.ora
    nofilenamecheck;
}
SPOOL LOG OFF;

The reason for the databa block corruption is that ther was data inserted in the  table with nologging in the timespan between the begin of the backup and '01-JUN-2015 02:00:00'. So the information needed to fully restore the datafiles (by applying the archive log) is not contained in the archives log. Mainly there are two possibilities for you:

1) you try to avoid corrupt blocks
 2) you repair the tables

1) To avoid corrupt blocks you can
1.1) schedule you duplicate so, that there is no nologging operation in the critical time interval
1.2) you can change the nologging operation to an operation with loggin
1.3) reduce the size of the critical time interval by doing an incremental backup before the until-point-of-time, e.g. '01-JUN-2015 02:00:00'. (but this has to be done on the production database)

2) you can truncate the affected tablels after the duplicate. If you don't need this tables on the your duplicate then you are done. Otherwise you have to fill them with the correct data , e.g. by loading the table with the same data as the production table. Of course this kind of repairing a table depends of the special situation.


Determining which database instance makes biggest IO Assuming that I have a dedicated server on which I am running multiple instances of mysql and postresql servers. How without iotop determine which instance in particular time (proc/pid/io shows data collected in some peroid of time) makes the biggest IO (so it increases IOWAIT)?

When lots of ppl do something on DB then I clearly see which instance is making the load because of high cpu usage, but I had a situation when the cpu usage was just normal, but very high iowait made a huge load on server and i had problem finding process that was making some outstanding IO

I would suggest you to take a look at atop. When you run it interactively you can select 'i' then press 1 , after that you can select 'd'.

Atop will colour the resources thrashed and 'd' will show only processes hitting disks.


Transaction and Try-catch in SQL Server Job We have DML operations in each step of a SQL Server job. To ensure the update/insert will be rolled back in case something goes wrong, I have wrapped the data modifications of each step in TRY CATCH and TRANSACTION blocks:

BEGIN TRY
    BEGIN TRANSACTION

        [[INSERT/update statements]] ...

    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRANSACTION
        PRINT 'Successful.'
    END

END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
        PRINT 'Unsuccessful.'
    END
END CATCH

Does it ensure the data manipulations will be rolled back in case of error(s)? Or other considerations should be taken into account?

Would be any better way of doing that (using configurations, etc)?

Thank you.

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

  Uncommittable Transactions and XACT_STATE
  If an error generated in a
  TRY block causes the state of the current transaction to be
  invalidated, the transaction is classified as an uncommittable
  transaction. An error that ordinarily ends a transaction outside a TRY
  block causes a transaction to enter an uncommittable state when the
  error occurs inside a TRY block. An uncommittable transaction can only
  perform read operations or a ROLLBACK TRANSACTION. The transaction
  cannot execute any Transact-SQL statements that would generate a write
  operation or a COMMIT TRANSACTION. The XACT_STATE function returns a
  value of -1 if a transaction has been classified as an uncommittable
  transaction. When a batch finishes, the Database Engine rolls back any
  active uncommittable transactions. If no error message was sent when
  the transaction entered an uncommittable state, when the batch
  finishes, an error message will be sent to the client application.
  This indicates that an uncommittable transaction was detected and
  rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie.  your catch should re-raise).


automatic innodb redo log archiving We have an option to do recovery through bin-logs, but we thought recovery through bin-logs is not 99% consistent because there is no LSN(Log Sequence Number)/SCN (System Change Number) in bin-logs (Statement/mixed/row).

Is there any possibility to archive innodb-redo-logs ?

Is there any chance to do recovery by using archived Innodb Redo-logs (at the time of instance start-up) ?

Looking for valuable suggestions.

Note: I'm searching for oracle style recovery in MySQL.

Thank you all :)

Not sure if this the answer you are looking for but Percona Server has a Log Archiving for XtraDB feature and you can restore a backup to a specific point in time by specifying an LSN.

Just be aware of this note from the documentation:

  This feature implementation is considered BETA quality.


Auditing - parameter values not being written to log I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The statement shows the parameter name rather then the value i.e. WHERE CaseId=@P_CaseId rather than WHERE CaseId =100

Now I know there was a hotfix for SQL 2008 (not R2) that made this happen: http://support.microsoft.com/kb/967552.

Did this not get rolled into R2? Is there a different fix I can apply? Is there a workaround anyone knows?

I can confirm in SQL Server 2008 R2 Service Pack 3 (v10.50.6000.34), simple parameterized queries do result in the parameterized values being recorded in the audit log.

I setup a simple test rig, as follows.

Create a server audit:

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
BEGIN
    CREATE SERVER AUDIT AuditTest
    TO FILE (FILEPATH = 'D:\SQLServer\MV\Audits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
    WITH (
        QUEUE_DELAY = 0
        , ON_FAILURE = CONTINUE
        , AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
    );
END
ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
GO

Create a database where we can play with auditing:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest')
BEGIN
    ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AuditTest;
END
CREATE DATABASE AuditTest;
ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
BACKUP DATABASE AuditTest TO DISK = 'NUL:';
ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
GO

Create a table in the database:

USE AuditTest;
CREATE TABLE dbo.TestTable
(
    id int NOT NULL
        CONSTRAINT FK_TestTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(30) NOT NULL
);

Create a database audit spec:

CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
FOR SERVER AUDIT AuditTest
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
WITH (STATE = ON);

Create a stored procedure and trigger an audit action:

CREATE PROCEDURE dbo.AuditTest
(
    @t varchar(50)
)
AS
BEGIN
    INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
END
GO
EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
GO

Perform a direct parameterized test:

DECLARE @t varchar(50);
SET @t = 'runtime value not audited - direct insert';
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

Finally, run a simple-parameterization test:

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
GO
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
GO

The execution plan for the above two simple-parameterized statements shows simple parameterization is working as expected:

Read the audit log, using a cursor and PRINT to preserve formatting:

USE master;
DECLARE @stmt nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT af.statement
FROM fn_get_audit_file('D:\SQLServer\MV\Audits\AuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
OPEN cur;
FETCH NEXT FROM cur INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @stmt;
    FETCH NEXT FROM cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;

Results:

INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');

As you can see in the above output, the simple-parameterized statement run-time values were captured in the audit log.


Handling case statement subquery returning multiple rows select *
from temp tp
join a
on    tp.id = a.id
where (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)
and   a.id1 in (case when (isnull(@id,''<>'')
                     then (select distinct id from #temp2)
                     else a.id1
                end)

The #temp2 query return multiple rows.Is that possible to achieve through case stament or else please do suugest the best possible way.

UDPATE: I am using the case statement as when the @id is null it should return all the records (a.id1=1,2,3,4,5,6)but when the @id=1,2,3 is not null it should return only those records which have the a.id1=1,2,3.

NOTE #1: Without knowing which RDMBS you're using I can't say if your proposed query will work or not (I'm leaning towards 'doubtful').

NOTE #2: I'm assuming the ( just before the isnull is not supposed to be there, otherwise I'm not sure what logic you're trying to implement, ie, you'll need to update your query with matching parens.

I'm thinking you'll want to pull the isnull test out to the top level and use an or to satisfy your conditional test, eg:

-- using '@id in (select ...)' construct

select *
from   temp tp
join   a
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (
            @id is not NULL
        or
           (@id is NULL and a.id1 in (select distinct id from #temp2) )
       )

-- or
-- using 'exists (select ...)' construct

select *
from   temp tp
join   a
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (
            @id is not NULL
        or
           (@id is NULL and exists(select 1 from #temp2 t where t.id=a.id1) )
       )


Storing files with extension in database as BLOB Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database administration that's why any tip can be really useful for me.

In my application project I have several types of files that have relationship with specific tables, for example:

Invoice has one scan
User has one document
Transaction has one receipt
Building has many photos

... etc.

All those files: scan | document | receipt | photo have different extensions(pdf/jpg/png etc.).

I decided that files corresponding for each table will be stored as BLOB datatype. Because of BLOB usage there is need to store file extension somewhere in database.

I was considering few possible solutions:
(For demonstration purposes I will attach simple tables visualization)

user | invoice | building_photo | transaction have require file stored as BLOB or MEDIUMBLOB, but extension of files is missing. What would be the best way of storing that? as VARCHAR column in each table or as separate table?

Example of extension storage in each table:

This solution is the easiest one to implement, extension as additional VARCHAR is handy when I'm inserting new data, but I'm repeating this information since I will have two files with the same extension.

Example of extension storage in separate table:

This solution seems to be more optimized but little bit harder to manage while inserting new data. I would be grateful for any suggestion about problem described above.

Another implementation that I've considered is to not store all files in separate tables but instead create one table called file that will contains file BLOB and then invoice | user | transaction | building etc.
will have relationship to that table. Visualization below:

In this case again I'm keeping extension in separate table, but file data as well. All files are in the same place but I'll end up with large table and a lot one to one relationships. If such solution makes sense also I would be grateful for option, or maybe there is another way of doing such things?

I think "user has one document" (and the others) is really a business rule - something you might need to change in the future, not a data rule. Therefore it should be enforced by triggers / constraints, not your schema

I'd go with a schema like this:

USERS -< USER_DOCUMENTS >- FILES

INVOICES -< INVOICE_SCANS >- FILES

BUILDINGS -< BUILDING_PHOTOS >- FILES

TRANSACTIONS -< TRANSACTION_RECEIPTS >- FILES

Where FILES looks like this:

FILES
file_id
content_type
name
data (BLOB)

While we're at it "transactions" is a very overused term in databases.


Unable to import DMP file that is made in 11g2 to Oracle 18c My OS=CENTOS 7
Oracle=18c XE

I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE.
Here are the errors:

[root@linux gsw]# impdp user/pass remap_tablespace=SYSTEM:myUserName remap_tablespace=USERS:myUserName dumpfile=myUserName.dmp directory=myUserName

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:09:34 2020
Version 18.4.0.0.0

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

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "myUserName.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "SYS.KUPV$FT", line 1035
ORA-06512: at "SYS.KUPV$FT", line 1023

What I did is:

create TABLESPACE myUserName
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

ALTER DATABASE
DATAFILE '/opt/oracle/oradata/XE/myUserName01.dbf'
AUTOEXTEND ON
MAXSIZE UNLIMITED;

SQL> ALTER USER myUserName quota unlimited on myUserName;

User altered.

SQL> create user myUserName identified by pass;

User created.

SQL> grant connect, resource to myUserName;

Grant succeeded.

SQL> grant read, write on directory myUserName to myUserName;

Grant succeeded.

SQL> grant create database link to myUserName;

Grant succeeded.

SQL> grant create table to myUserName;

Grant succeeded.

UPDATE-1

SQL> GRANT CREATE ANY TABLE TO myUserName;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO myUserName;

Grant succeeded.

Now the errors changed as:

Import: Release 18.0.0.0.0 - Production on Thu Jun 4 01:39:50 2020
Version 18.4.0.0.0

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

Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/opt/oracle/admin/XE/dpdump/myUserName/myUserName_110516.dmp" may be an original export dump file

Best Regards

Add the following to the impdp command:

VERSION=11.2.0


Handler_read_key description As per MySql documentation link:

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Handler_read_key

  Handler_read_key - The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

Can someone please explain / simplify / elaborate this definition.

A value of thousands per second is pretty normal.  (Manually compute Handler_read_key / Uptime.)

Do the following to get a better feel for what it, and its siblings, are about:

FLUSH STATUS;  -- resets the Handler counters (and a few other things)
SELECT ...
SHOW SESSION STATUS LIKE 'Handler_%';

You will see read_key for "point queries" and for "range queries", possibly one each.  You will also see a larger value for a "range query" in read_next.

Handler_write% values indicate that a temp table needed to be built and reread -- such as for GROUP BY and/or ORDER BY.

I find the Handler values to be handy in comparing two queries -- bigger numbers is a good indication of a less-well optimized query.

If you like, present us with a small number of SELECTs, together with SHOW CREATE TABLE and EXPLAIN SELECT and the Handler% values.  We can go into more details 'by example'.

Handler_read_key

I think read_key is bumped by one in these cases:

Reaching into a table, either for a specific row, or to start a range scan.
At least once per table in a query with multiple tables due to JOIN or subqueries.
Once per row in a "nested loop join".  This is the usual case for a simple JOIN two tables.  It scans the first table (one Handler_read_key, many Handler_read_next) and reaches into the next table repeatedly (many Handler_read_key)

Think of it this way:  Most reads from a table work in 1 or 2 steps:

Use a "key" to find the row (or the first row of a range) --> Handler_read_key bumped by 1.
If multiple rows needed, then get the "next" row --> Handler_read_next bumped by 1 per extra row.  If using something like ORDER BY .. DESC, then bump Handler_next_prev.


EXCEPT operator vs NOT IN The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?

Does it do the same? I would like a simple explanation with an example.

There are two key differences between EXCEPT and NOT IN.

EXCEPT

EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause.

It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the left and right hand side of the query

For example, you cannot do:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

This would result in the error:

  All queries combined using a UNION, INTERSECT or EXCEPT operator must
  have an equal number of expressions in their target lists.

NOT IN

NOT IN does not filter for DISTINCT values and returns all values from the left-hand table that do not appear in the right-hand table.

NOT IN requires you compare a single column from one table with a single column from another table or subquery.

For example, if your subquery was to return multiple columns:

SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

You'd get the following error:

  Only one expression can be
  specified in the select list when the subquery is not introduced with
  EXISTS.

However, if the right-hand table contains a NULL in the values being filtered by NOT IN, an empty result set is returned, potentially giving unexpected results.

EXAMPLE

CREATE TABLE #NewCustomers (ID INT);
CREATE TABLE #ExistingCustomers (ID INT);

INSERT INTO #NewCustomers
        ( ID )
VALUES
     (8), (9), (10), (1), (3), (8);

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( 1) , (2), (3), (4), (5);

-- EXCEPT filters for DISTINCT values
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN returns all values without filtering
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

From the above two queries, EXCEPT returns 3 rows from #NewCustomers, filtering out the 1 and 3 that match #ExistingCustomers and the duplicate 8.

NOT IN does not do this distinct filtering and returns 4 rows from #NewCustomers with the duplicate 8.

If we now add in a NULL to the #ExistingCustomers table, we see the same results returned by EXCEPT, however NOT IN will return an empty result set.

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( NULL );

-- With NULL values in the right-hand table, EXCEPT still returns the same results as above
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN now returns no results
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

DROP TABLE #NewCustomers;
DROP TABLE #ExistingCustomers;

Instead of NOT IN, you should really look at NOT EXISTS and there is a good comparison between the two on Gail Shaw's blog .


Postgres show count 0 for non-matching rows for 1 table Let's say I have this schema.

create table foo_access (
    id         serial primary key,
    user_id    integer not null,
    created_at timestamptz not null
);

-- This data set uses user_id=0 to mean null. _shrug_
insert into foo_access (user_id, created_at) values (0, '2020-03-20T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-19T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-18T00:00:00Z');
insert into foo_access (user_id, created_at) values (2, '2020-03-18T00:00:00Z');

I want to see how many different users have accessed foo at least once a day, for the last 5 days.

I have a query like this.

select
  date_trunc('day', created_at) as period,
  count(distinct user_id) as n
from foo_access
where user_id > 0 and
  created_at >= now() - interval '5 day'
group by period

Which gives me a table like this.

         period         | n
------------------------+---
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(2 rows)

Almost what I want, but not quite.

Is there a way to also show the days where no one accessed foo? I'd like the table to look more like this.

         period         | n
------------------------+---
 2020-03-15T00:00:00+00 | 0
 2020-03-16T00:00:00+00 | 0
 2020-03-17T00:00:00+00 | 0
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(5 rows)

SQL Fiddle: http://sqlfiddle.com/#!17/ece05/2/0

This question is somewhat similar to How to count matching values and print 0 for non-matching value in PostgreSQL?, except I'm not joining two tables. I only have 1 table.

WITH RECURSIVE
cte AS ( SELECT date_trunc('day', now() - interval '5 day') AS period
         UNION ALL
         SELECT period + INTERVAL '1 day'
         FROM cte
         WHERE period < date_trunc('day', now()) )
select
  cte.period,
  count(distinct foo_access.user_id) as n
from cte
LEFT JOIN foo_access on cte.period = date_trunc('day', foo_access.created_at)
                    AND foo_access.user_id > 0
group by cte.period;

fiddle

  I want to see how many different users have accessed foo at least once a day, for the last 5 days.

In my query you'll get 6-day statistic (except while quering at the midnight). You may fix it if needed.


What is the default sample size of statistics in SQL Server? From MSDN:

  When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are
  specified, the query optimiser samples the data and computes the
  sample size by default.

How to identify default sample size of statistics?

I went through MSDN but didn't find any formula or method to identify the default sample size. Everywhere there are only formulas present to trigger the auto statistics update. Any pointers will be helpful.

How to identify default sample size of statistics?

I would quote from Old Docs article

Auto Update stats Algorithm:
So the Auto Update stats will fire for every 500 + 20% change in table
rows. Of course, we have an improved algorithm in SQL 2012 which is
SQRT(1000 * Table rows) which is much better.
When it fires it will use the  default sampling rate and here is the
algorithm how it calculates the sampling rate.

 If the table < 8MB then it updates the statistics with a

fullscan.

 If the table > 8MB, it follows an algorithm. It reduces the

sampling rate as the number of rows in the table are increased to make
sure we are not scanning too much data. This is not a fixed value but
is under the control of optimizer. It is not a linear algorithm
either.
Example: if we have 1,000,000 rows it would use a sampling rate of 30%
but when the number of rows increase to 8,000,000 it would reduce the
sampling rate to 10%. These sampling rates are not under the DBAs
control but optimizer decides it.

To understand further I suggest you read

Auto Update Stats Default Sampling test By Joe Sack

Sample Size and Duration of Update Stats By Erin Stellato


SQL Server 2005/2008 UTF-8 Collation/Charset I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin and SQL collations.

Is there some option to force/install these collations / charsets in SQL Server engine (for both ver.) 2005/2008 on Win2008 OS

No, there isn't. SQL Server doesn't support UTF-8.
You need to define your columns as nvarchar/nchar if you want unicode data. Note, internally SQL Server stores this as UCS-2.
Note that this has ben requested from MS on Connect and there is an older KB article. And some info on this blog too


Databases going in recovery mode I am in a weird problem. Firstly some information about our env.
We have around 50-55 servers and around 100 instances on them.
Backups are configured through netback and that is using Nt Authority\System account.
Everything is fine for the 99 instances but in one whenever we enable this account to login some databases go in recovery state.
And when we disable the account than we can recover it with normal restore with recovery.

Any pointers will be highly appreciated.

In error log it's saying: FCB open failed: could not open file
  "\sharepath" for file number 1 OS error: 32 the process cannot access
  the file as it's being used by another process

System account has issues with accessing database files on network share

Can you, perhaps, try this?:

grant full access of [domain\serverName$] to
\\sharepath\sharefolder where database files resides
trace if there any open handles to those file, that prevents database to be started


optimize table in stored produce using phpMyAdmin causes out of sync error I get the following error when running the code in phpMyAdmin. NOTE: I can run any other sql code (update, insert, replace) via phpMyAdmin in a stored procedure; but an optimize causes issues.

NOTE: This code works fine when running from CLI.

ERROR: #2014 - Commands out of sync; you can't run this command now

SQL Code:

delimiter //

create procedure update_phppos()

begin
OPTIMIZE table phppos_items;
end;

//

delimiter ;

-- Execute the procedure
call update_phppos();

-- Drop the procedure
drop procedure update_phppos;

For INNODB I was able to do the same thing and it didn't cause an error in php.

Here is what I did:

 ALTER TABLE phppos_items FORCE;


Does SELECT * create additional overhead even when you want all columns? I have read that SELECT * is typically undesirable in that it creates additional overhead. Is this the case if you want to retrieve all columns?

I have a extremely badly performing queries, which is made up of nested 'SELECT *' queries. In all cases I do want all the columns returned to me though...

Strictly answering your question, no there is no additional overhead when you want all columns returned.  But it is undesirable as you mentioned. I do not want to reiterate why it is undesirable as most of the points are discussed in this answer.


Updating/Inserting 2000 entries takes over 12 hours to complete I have three tables of around 10 million rows each in MySQL on Amazon RDS and I am finding the insert/update performance to be VERY slow. Each table contains unrelated data.

Each table is regularly updated with about 2000 rows each time. The rows either update existing values or insert new ones. I am finding that updating or inserting 2000 rows takes over 12 hours to perform.

The database has 100GB of General SSD storage which means that it can sustain 300 IOPS. CPU utilisation is below 20% and RAM is 50% free.

I am using an index and a primary key which was constructed from two columns within the table using: CONSTRAINT PK_1 PRIMARY KEY (DATE, NAME)

I am using the following query:

    INSERT INTO Table (DATE, NAME, COLUMN_1, ..., COLUMN_10)
    VALUES
    ('2015-05-26','David', VALUE_1, ... , VALUE_10),
    ...
    ('2015-05-26','Tom', VALUE_1, ... , VALUE_10)
    ON DUPLICATE KEY UPDATE COLUMN_1=VALUES(COLUMN_1), ... , COLUMN_10=VALUES(COLUMN_10);

The above query inserts/updates 7 rows and 10 columns of data. This happens for all 2000 entries(users).

The reason why I have chosen to use the above statement instead of a REPLACE INTO statement is because whilst the table is 40 columns wide, I only receive 10 of those columns at once. The values of the rows can also change for up to a week.

By default, the data was being inserted into the three tables sequentially. I have tried inserting the data in parallel. However, there was no significant  improvement in the performance.

I don't understand why it takes over 12 hours to insert 2000 rows. What are my options to improving performance?

I'm going to give this my best guess, but I will preface saying that we don't have intimate knowledge of your schema, and while I am knowledgeable about MySQL, I would not call myself an expert.

Index-organized tables

One problem you might be having is due to the way in which MySQL stores data, along with your UPSERT behavior.  Using the InnoDB engine in MySQL, every table is an index-organized table.  This means that the data payload is stored directly within the pages of the index. In effect, the primary key index is also the table structure.

Now, in your case, you are inserting rows with a total of 42 columns.  Without knowing the table structure, I am guessing the some of these 40 unknown columns are of variable length.  So each time you update the data, there is likely to be quite a lot of data movement due to this. InnoDB has to rewrite the index and data pages in order to accommodate the new data.

Change your schema?

So, based on this likely limitation you are seeing, I think one thing you should consider is a schema change.  What you have now is essentially one big flat table.  But, you've stated that you receive only 10 of these 40 unknown columns at a time, so why don't you have at least 5 tables? That is (in loose pseudo code, and make sure to add your FK relationships):

CREATE TABLE record (RecID, DATE, NAME);
CREATE TABLE column_set_1 (RecID,COL1,...,COL10);
CREATE TABLE column_set_2 (RecID,COL11,...,COL20);
CREATE TABLE column_set_3 (RecID,COL21,...,COL30);
CREATE TABLE column_set_4 (RecID,COL31,...,COL40);

This way, you are only performing inserts, and only into the relevant tables without touching all this other data.

Table Partitioning

Also, because you have a DATE field, it may be helpful to partition your table.  I can't say for sure if this will have a strong positive impact, but I suspect it may.  If you go for my suggested schema change, for example, I might choose to hash partition the 4 column_set_* tables based on RecID and then the INSERT...ON DUPLICATE KEY UPDATE can make use of partition lock pruning.

Best of luck, and maybe someone with better MySQL expertise can correct any mistakes I've made.


Getting ApplicationName (sqlserver.client_app_name) to show up in an xevent I am trying to examine some T-SQL with extended events like I used to with SQL profiler. I have the following event session:

IF EXISTS (SELECT name FROM sys.dm_xe_sessions WHERE Name = 'PySoup tracing')
BEGIN
    DROP EVENT SESSION [PySoup tracing] ON SERVER
END

CREATE EVENT SESSION [PySoup tracing] ON SERVER
ADD EVENT sqlserver.rpc_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sp_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_batch_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text )),
ADD EVENT sqlserver.sql_statement_completed (ACTION(sqlserver.client_app_name, sqlserver.sql_text ))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\PySoup tracing.xel')--,
--ADD TARGET package0.ring_buffer(SET max_events_limit=(1000000))
GO

ALTER EVENT SESSION [PySoup tracing] ON SERVER
    STATE = START;

I thought that the ACTION clause was supposed to list the columns that were returned by the event. However, I don't see sqlserver.client_app_name column when I view the event data in the GUI.

What am I doing wrong?

I have seen this issue before, what seems to happen if you have this type of sequence:

create session
start session
stop session
add columnar data (action or field)
start session

The GUI doesn't show the added columnar data. Your options here would be to directly query the target or to clear/rename the old session definition's XEL files.


Ternary Relationship or (n-ary) I'm trying to find the difference in terms of modeling a ternary relationship v/s a binary relationship in the following case. Any piece of advise would help me a lot.

Company has editors who are responsible for 1 or more papers.
An Author works with an editor on each paper, furthermore, he may be working in another paper with another editor.
Each Author may have none or more papers.

I managed to model this problem in two ways

First using a Ternary Relationship.

Second one is using two binary relationships and enforcing that each paper has 1 editor.

Is there going to be a difference in the tables that will follow any of this ways? I've tried to find any but failed to do so.

Yes, there is a difference.

In the 1st design, an author-paper combination is related to one editor. So a paper can have many editors, as many as its authors.

In the 2nd design, a paper is related to one editor. And all its authors are related  - through the paper - to the same editor.


Exists subselect vs inner join? I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher up.

Using PostgreSQL 9.4.2.
Table definitions (/d+): https://gist.github.com/neezer/879f5d3649ca1903c6f3
Cardinalities:

billing_pricequote: 1,462,625 rows
billing_pricequotestatus: 3,331,657 rows
billing_lineitem: 43,687,855 rows

Here's the original query, without modifications suggested for the subquery inside EXISTS:

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
  SELECT s1.quote_id
  FROM billing_pricequotestatus s1
  INNER JOIN (
    SELECT DISTINCT ON (quote_id) quote_id, MAX(created_at) AS max_created_at
    FROM billing_pricequotestatus
    WHERE quote_id=i.quote_id
    GROUP BY quote_id, created_at
    ORDER BY quote_id, created_at DESC
  ) AS s2
  ON s1.quote_id = s2.quote_id
  AND s1.created_at = s2.max_created_at
  WHERE s1.name IN ('adjustment','payment','billable')
)
GROUP BY i.quote_id, i.acct_id
;

The part I noticed looking weird is the SELECT on billing_pricequotestatus and then another subselect on the same table inside the INNER JOIN.

I tried changing this with the modification from my other SO post:

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
  SELECT quote_id, MAX(created_at) AS max_created_at
  FROM billing_pricequotestatus
  WHERE quote_id=i.quote_id
  AND name IN ('adjustment','payment','billable')
  GROUP BY quote_id
)
GROUP BY i.quote_id, i.acct_id
;

That cut my execution time in half (~40 seconds to ~20 seconds), but yielded slightly different results (the original query returned 28,895 rows, but my new query returns 28,917 rows). It's not immediately clear to me why my modification didn't produce equivalent output (which it needs to).

EXPLAIN ANALYZE for both queries
Explain analysis for original query depesz.com.

Would really appreciate any help/guidance on this!

I tried updating @ypercubeᵀᴹ's answer with a LATERAL JOIN, and the performance seems about the same (each has an equal number of wins by less than a second):

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
LEFT JOIN LATERAL
( SELECT name
  FROM billing_pricequotestatus
  WHERE quote_id = i.quote_id
  ORDER BY created_at DESC
  LIMIT 1
) pqs ON true
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND pqs.name IN ('adjustment', 'payment', 'billable')
GROUP BY i.quote_id, i.acct_id
;

Explain analysis.

Any other suggestions to get this below ≈10 seconds?

The purpose of your subquery as I understand it:
Select rows where the latest related entry in billing_pricequotestatus has a qualifying name.
Incorrect 2nd query

It's not immediately clear to me why my modification didn't produce equivalent output

The 1st query picks the latest row from billing_pricequotestatus and checks whether the name qualifies (name IN ('adjustment','payment','billable')).
The 2nd query is backwards: it check for any row with qualifying name (not just the last one). Also, it doesn't make sense to compute an aggregate in an EXISTS semi-join. You don't want that. And it's not equivalent.
Consequently, you get more rows from the 2nd query.
Incorrect time range
This predicate is a mess. Inefficient and possibly incorrect - or at least a ticking bomb:
WHERE pq.date_applied AT TIME ZONE 'PST'
       BETWEEN '2016-02-02T00:00:00'::timestamp
           AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
The column date_applied is of type timestamptz. The construct AT TIME ZONE 'PST' converts it to type timestamp and shifts by the time offset hard-coded into the time zone abbreviation 'PST' - which is a bad move to begin with. It makes the expression non-sargable. That's more expensive and, more importantly, rules out the use of any plain index on date_applied.
Even worse, the time zone abbreviation 'PST' is not aware of DST or any historic shifts in time. If your time zone has (or had in the past) daylight saving time, and your set spans different DST periods, your current expression is most probably incorrect:

"AT TIME ZONE" with zone name PostgreSQL bug?
Ignoring timezones altogether in Rails and PostgreSQL

You would need to use the applicable time zone name instead of the abbreviation to get consistent local time - which is even more expensive.
And there's yet another problem: While the column value is shifted by a hard-coded time offset ('PST'), your upper bound '2016-03-03T22:27:41.734102-08:00'::timestamptz is provided as timestamptz and silently coerced to match the data type timestamp. Since no explicit time offset is provided, the cast defaults to the time zone of the current session. So you can get different results depending on the current time zone setting of your session. I can't think of a use case where this would make sense.
Don't do any of this. Don't translate timestamptz column date_applied to local time at all, don't mix data types like you do and don't mix different ways to cast. Instead, use the column as is and provide timestamptz parameters.
Query
SELECT i.quote_id, i.acct_id AS account_id, sum(i.delta_amount) AS amt
FROM   billing_pricequote pq
JOIN   LATERAL (
   SELECT name
   FROM   billing_pricequotestatus
   WHERE  quote_id = pq.id
   ORDER  BY created_at DESC
   LIMIT  1
   ) pqs ON pqs.name IN ('adjustment', 'payment', 'billable')
JOIN   billing_lineitem i ON i.quote_id = pq.id
WHERE  pq.date_applied BETWEEN (timestamp '2016-02-02T00:00:00' AT TIME ZONE 'PST')  -- !
                           AND timestamptz '2016-03-03T22:27:41.734102-08:00'
GROUP  BY 1,2;

Note the LATERAL join, but not LEFT JOIN, make it an INNER JOIN to implement your predicate right away.
Or use the equivalent correlated subquery outlined by @ypercube. Not sure which is faster.
Also note that I base the LATERAL JOIN on billing_pricequote - before joining to the big table billing_lineitem. This way we can eliminate rows early, which should be cheaper.
Index
Currently, you get:

Seq Scan on billing_pricequote pq

Only 70k of your 1,5M rows are selected, which is around 5 %. An index on date_applied might help a bit, but not much. However, this multicolumn index should help substantially if you can get index-only scans out of it:
CREATE INDEX foo ON billing_pricequotestatus (quote_id, created_at DESC, name);

Even more efficient with name_id instead of name as suggested below.
Statistics
Postgres is over-estimating the selectivity of your time range:

(cost=0.00..88,546.50 rows=7,313 width=4) (actual time=2.353..767.408
rows=70,623 loops=1)

It might help to increase the statistics target for just the column date_applied. Details here:

Configuring PostgreSQL for read performance

Table definition
Exemplary for billing_pricequotestatus:
name seems to be one of a couple of possible types. It would help performance to normalize some more and just use a 4-byte integer referencing a lookup-table instead of a varchar(20) repeated over an over in 3.3M rows. Also, reordering columns (if possible) like I demonstrate would help some more:
   Column   |           Type           |              Modifiers
------------+--------------------------+------------------------------------------
 id         | integer                  | not null default nextval('...
 quote_id   | integer                  | not null
 created_at | timestamp with time zone | not null
 updated_at | timestamp with time zone | not null
 name_id    | integer                  | not null REFERENCES name_table(name_id)
 notes      | text                     | not null
See link above about alignment and padding. To measure row size:

Measure the size of a PostgreSQL table row

And "name" is not a good identifier. I would use something descriptive instead.


Migrate Jira to MySql - Unknown system variable 'storage_engine' I'm migrating JIRA in Windows from HSQL to MySQL and I got stuck with  the message Unknown system variable 'storage_engine'. Though I created a backup, installed MySQL and copied J connector to libs of JIRA.

Then I set the system variable storage_engine = InnoDB and rebooted the machine but the message is still there.

Could you please point me to a good direction.

If you are using MySQL 5.6, InnoDB is already the default.

Please do not use storage-engine because it is deprecated and probably explains the error.

You should use default-storage-engine or default_storage_engine in my.cnf instead

[mysqld]
default-storage-engine = InnoDB

GIVE IT A TRY !!!