First query is really slow with WiredTiger, not with mmapv1 We recently upgraded to MongoDB 4.0. Loving the performance improvements with WiredTiger, but all of a sudden our test suite is timing out on certain machines.

Before running our tests, we run the following command:

mongod --dbpath ./.test-data --bind_ip 127.0.0.1 --fork --logpath ../testlog.txt

Everything seems great. The database starts. Node connects to it. We create a bunch of collections and indexes.

Then once we run the first query, it takes 10+ seconds to run.

To be clear, at this point the database has never had any data inserted into it. It's brand new.

Using MMAPv1 (with the following command) resolves the problem, but I think it'd be great to run our tests with WiredTiger, if possible.

mongod --dbpath ./.test-data --storageEngine mmapv1 --bind_ip 127.0.0.1 --fork --logpath ../testlog.txt

Running mongod v4.0.3 and OS X 10.13.6. Connecting with node v6.9.1 and mongoose v5.4.19.

After each test run, we nuke the data directory:

mongo admin --eval 'db.shutdownServer()' > /dev/null
rm -rf ./.test-data/*

I also tried setting --wiredTigerJournalCompressor none --wiredTigerCacheSizeGB 1.0 --wiredTigerCollectionBlockCompressor none --wiredTigerIndexPrefixCompression false and it didn't make a difference.

First query is really slow with WiredTiger, not with mmapv1

As per MongoDB blog documentation How to Tell if Your MongoDB Server Is Correctly Sized For Your Working Set For MongoDB users, knowledge of working sets is critical. Understanding the interactions between your working set and physical memory can make a major difference in how your system performs.

In order to optimize MongoDB performance you should consider and know the following:
How working sets fit into the scheme of MongoDB in the first place;
Why it's important to fit your working set within your available RAM;
Why you should avoid pulling from disk; and
When and how it's possible to tell that there's a mismatch between your working set and your RAM.

What is a working set?

MongoDB's documentation defines a working set as follows:

  A working set represents the total body of data that the application
  uses in the course of normal operations… For best performance, the
  majority of your active set should fit in RAM.

Why's this important? Fitting your working set to RAM is vital for performance: Going to disk is costly. As this breakdown from a UC Berkeley study on latency numbers shows, reading from memory is significantly faster than reading from disk. As this visualization of 2017 speeds shows, even if you're using SSD, accessing from memory is an order of magnitude more efficient than accessing from disk.

There are two primary storage engines available for MongoDB:

MMAP

As Mongo's documentation
explains, "MMAPv1 is MongoDB’s original storage engine based on
memory mapped files. It excels at workloads with high volume inserts,
reads, and in-place updates." As of MongoDB version 3.2, however,
MMAP is no longer MongoDB's default storage engine—WiredTiger now
holds that spot.
With MMAP, MongoDB will use all available memory, as necessary. There
are no configuration options to limit the data kept in-memory.

WiredTiger

WiredTiger has an internal data cache, but is configured to also
leave memory for the Operating System’s file cache. It's generally
recommended to leave your cache size at its default setting.
These defaults
settings will use the larger of either of the following: 50% of
RAM minus 1 GB.
256 MB.
Outside WiredTiger's cache, the Operating System will cache
frequently accessed files for the database in its own file cache.
WiredTiger compresses data files by default, so leveraging the OS
cache actually lets more data stay in the server's physical memory.

How can I tell if my working set fits my RAM?

For MMAP, a high number of page faults is a leading indicator that instead of finding data in RAM, it's going to disk to fetch it. That, of course, means the working set isn't fitting within your available RAM. Inversely, you can look directly at your RAM usage: As explained above, MMAP uses all available memory automatically. Therefore, if you're not using all of the RAM, then you can safely conclude that the working set fits into RAM, even with room to spare.

The total number of page faults. The extra_info.page_faults counter may increase dramatically during moments of poor performance and may correlate with limited memory environments and larger data sets. Limited and sporadic page faults do not necessarily indicate an issue.

Windows draws a distinction between hard page faults involving disk I/O, and soft page faults that only require moving pages in memory. MongoDB counts both hard and soft page faults in this statistic.

"freeMonitoring" : {
   "state" : <string>,
   "retryIntervalSecs" : <NumberLong>,
   "lastRunTime" : <string>,
   "registerErrors" : <NumberLong>,
   "metricsErrors" : <NumberLong>
},

For WiredTiger, you can also begin by looking directly at memory usage. If your server has free RAM, that inherently means the working set fits into your memory, as WiredTiger automatically uses its set cache plus your operating system's cache. If your server looks to be using most of its memory, take a look at the pages-read-into-cache and unmodified pages evicted metrics reported for WiredTiger by MongoDB’s serverStatus command. High activity for those two metrics indicates that the server is cycling data through the cache, which means your working set does not fit in memory.

The last metric to consider, of course, is volume of disk reads — if you see a lot of read activity on your drive, it's generally a good sign that your working set is overflowing your memory. Because WiredTiger interacts with your OS cache, this may be a more reliable metric to evaluate working set ratio — e.g. you may be able to see high cache evictions but low IO because of how your cache behaves.

Even though as documented here WiredTiger allows for tuning some of the storage engine parameters. The 3 most important to consider are the cache size, checkpoint interval and logging.

for further your ref here , here and here


Problem compiling view when it is referencing a table in an other view: insufficient privileges Oracle 11g R2
Logged on: SYS / AS SYSDBA

When I try to compile or create a view that is referencing local schema tables. It works fine.

Problem does occur when I try to compile the same view referencing a table in another schema like schema.table in my query.

Oracle throws the exception ORA-01031: insufficient privileges.

Remember I am using SYS account (sysdba).

Even though you are using SYS (which you really shouldn't be), the view is stored in a SCHEMA1, as I will refer to it.  SCHEMA1 is trying to select from a table, via the view, in SCHEMA2.  Therefore, you need to grant SELECT access to SCHEMA2.TABLE to SCHEMA1.

And don't run things as SYS. :)


Restore database with only ibdata1 file I need to analyze the client's data and for that requirement client has provided us only ibdata1 file having size around 150MB.
I am not aware of table structure and number of tables as of now. After some research I found that I will need .frm files in order to recreate the tables.
I have below questions:

Is there a way I can restore the database with only ibdata1 file?

Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?

Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version?

Thank you!

If your client is unable to give you a proper database dump, then whatever task they're asking of you will take far longer and cost much more than anyone should be willing to tolerate.
Is there a way I can restore the database with only ibdata1 file?
⇢ Yes, but not without a remarkable amount of work. People who are not trying to save a multi-billion dollar company from lawsuits should never have to do this.
Shall I need any other files apart from .frm files and ibdata1 file in order to restore the database?
⇢ the .frm files contain the definition of a database. The ibdata1 file can contain many databases (depending on how MySQL was configured). If you must restore the database with only the ibdata1 and .frm files, you will need all of the files in their correct directories. From there you may be able to restore so long as your version of MySQL is either exactly the same or just slightly newer than the version the client is running.
Note that restoring ibdata and .frm files from a foreign MySQL installation will obliterate any databases you already have configured on the receiving machine.
Suppose I can restore the database using just ibdata1 file or I get .frm files in future, Do I need to install a version that matches the client's MySQL version?
⇢ This was answered in the previous bit. While it's technically feasible to take an ibdata1 and accompanying .frm files from earlier versions of MySQL and upgrade them, there can be problems when jumping a large number of releases. Going from MySQL 5.3.x to 8.0.24, for example, is almost guaranteed to fail unless the source database is structured more like a CSV than a series of relational tables.
If your client is able to access the MySQL server, have them create a proper backup with MySQLDump and provide that file to you. This way you do not have to worry as much about versions or anything else. Everybody will save time and hassle.


SSIS - ODBC Parameter Size I have created a SSIS package which runs every hour from my Machine. First part of my Package runs a Execute SQL Task and result set (single row, single column) is a comma separated list of Unique Record Ids which have been updated in last hour. (i.e. FJ1,FJ2,FJ3) I capture this result in a variable and when I User script tasks to display the value of this property, it appears as it should be.

I pass this Variable to Execute SQL Task on different ODBC Server using a Stored Procedure

My SQL Statement is as Following

call DeleteRowsById(?)

And then I have assigned my Variable (as SQL_Longvarchar) to Parameter 1 in Parameter mapping Section. (ParameterSize is -1) but it doesn't delete the records from the table. (I ran the SP manually in Workbench and it works as expected) Then I had an idea and as part of my SP I started making entries in a Temp table just to see whats coming up as variable. Insert in the table shows only first character from the variable and which is why it's not deleting any records.

Can someone please point me to the right direction as I can not find why my variable is being truncated.

I know this was asked a while ago, but maybe someone else will stumble across this answer and not spend the multiple hours that I've been bashing my head against this.

Turns out that you need to use the SQL_WVARCHAR type for Variables.

The following is my evidence, showing the SQL_WVARCHAR works.

OLEDB command from SQL Profiler:

exec sp_executesql N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)','TestMe','01','2015-03-24'

ODBC command from SQL Profiler using SQL_VARCHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T','0','2'
select @p1

ODBC command from SQL Profiler using SQL_CHAR

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 char(20),@P2 char(20),@P3 char(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''','T                   ','0                   ','2                   '
select @p1

ODBC command from SQL Profiler using SQL_WVARCHAR

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(20),@P2 varchar(20),@P3 nvarchar(20)',N'exec dbo.usp_InsertJobLogODBC @P1,@P2,@P3,3,''Task [TestBIMLPackage] Starting''',N'TestMe','01',N'2015-03-24'
select @p1


SQL Server Log Shipping from 2012 to 2014/2016 I am being told that it is NOT possible to do Log Shipping from SQL Server 2012 to SQL Server 2014 or 2016. Is this correct?

If not, are there any caveats to the process when Log Shipping to a SQL Server version higher than the source?

You can happily do ONE-WAY logshipping from lower version to higher version.

The only caveat is that once you failover to higher version, you cannot failback to lower version... same applies to Database Mirroring.

This technique is normally used when you want to do migration from lower version to higher version with minimal downtime.


Mariabackup Throws Unknown Argument Error I have just installed the MariaDB backup tool based on Percona Xtrabackup, Mariabackup, on my CentOS 7 system with MariaDB v10.1. However, when I try to test using this tool, it fails immediately complaining about an unknown argument.

I tried running this command as a full backup, as a full backup using the --xbstream option to pipe it through gzip, and as a full backup using gzip and OpenSSL. I also tried GPG with gzip.

Here are the commands I've tried:

With gzip/openssl:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup     --stream=xbstream | gzip | openssl  enc -aes-256-cbc -k password123 >     backup.xb.gz.enc

with gzip/gpg:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --stream=xbstream | gzip | gpg -c --passphrase password123 --batch --yes -o backup.xb.gzip.gpg

With gzip:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --stream=xbstream | gzip > backup.xb.gz

And just straight up:

mariabackup --user=root --password= `cat /etc/psa/.psa.shadow ` --backup --target-dir=/root/sql_dumps/

And here's the output:

Info: Using unique option prefix 'backup' is error-prone and can break in the future. Please use the full name backup_encrypted instead.

mariabackup: Error: unknown argument: '$AES-128-CBC$GzzuTl0rBKRyCngfmWSypg==$JAmMp9hcbIQ/MTrzqCQ8eWIFQGixpUjZ7ESQqNLfuJM='

I'm pretty sure this error was caused by supplying the wrong username. After I resolved that, the error went away and the backup initiated.


Topics and posts represented by one table or two? I'm currently building a forum component for a larger application and I'm considering different approaches to certain parts of the database schema. In particular, I am considering representing topics and posts in a single table. While I view topics and posts as practically the same, I feel a bit apprehensive as this may make things less flexible in the future.

When topics of a particular forum are queried, the title and first post will be shown as well as some of the user information (basically the name and avatar). In this application, there are various attributes that are used by both topics and posts except for views and replies; and perhaps title, and forum_id(forum_id because that would mean potentially hundreds of records need to be affected if a topic is changed to another forum as opposed to changing the forum_id attribute in the topic relation).

The tables look something like what I have below here:

TOPIC            POST
topic_id         poster_id
forum_id         topic_id
poster_id        content
title            upvote
views            dnvote
replies          closed
post_id          deleted
                 last_edited
                 last_editor
                 parent_id
                 content
                 post_id

Doing it this way, using table inheritance, generating the posts in the topic would require a 4-table join via TOPIC, POST, USER, and TOPIC_TYPE.

On the other hand, if I decide to take the single table approach, should I simply leave the views, replies, title, and forum_id attributes as null if the topic_type is a regular post? (topic_type references an appropriate icon for the type of topic displayed, and will be used for statistics and etc.)

Generally...

A a rule of thumb: don't pre-optimize for performance.  I think a lot of developers assume that joins are inefficient and they don't trust DBMS to do what it's built to do.

Start with a properly normalized design.  Make sure your indexes and queries are optimized for your particular balance of reads and writes.

If and when you start to find that performance can't keep up with the best hardware you can afford, then start to think about denormalizing.

If you denormalize early you are just setting yourself up for maintenance headaches down the road.

More Specifically...

Looking at your suggested table layouts, I would suggest that you're trying to make TOPIC do too much.  Anything which could appear in POST (e.g. poster_id) almost certainly doesn't belong in TOPIC.  I suggest you adjust your thinking a little bit.  I get the impression you are thinking very much about what topics and posts are going to look like on the page.  This may be leading you to thinking of topics as a small super-set of posts, whereas they are probably rather more like subject headings.  The fact that you plan on displaying the first post under each subject heading along with the heading isn't a good reason to co-mingle posts and headings.

I think you may want to rethink some of your cumulative total columns too.  Thinks like up and down votes may need to be tracked in their own tables.  You may need to do this to keep people from up or down voting repeatedly and to allow people to rescind their votes.  Similarly, you may want to know all editors, not just the last editor.


Use MAX() In Where Clause I am attempting to use MAX() in my WHERE clause but I am getting an error of

  Msg 4145, Level 15, State 1, Line 10
  An expression of non-boolean type specified in a context where a condition is expected, near ')'.

This is the output I expect from working syntax

Blue - 3
green - 0
orange - 1
pink - 1
black - 0
white - 2

Here is sample table structure and the query I tried - how can I successfully use MAX() in the WHERE clause?

Declare @Hold Table
(
  id int NOT NULL IDENTITY (1,1),
  name varchar(500),
  logoncount int
)
Insert Into @HOld (name, logoncount) Values
('blue', 1),('blue', 2), ('blue', 3)
,('green', NULL), ('orange', 1), ('pink', 1)
,('black', NULL), ('white', 1), ('white', 2)

Select
*
FROM @Hold
WHERE logoncount = (select max(logoncount) from @Hold)

What you're looking for is probably something like this.

Select
*
FROM @Hold h
WHERE logoncount = (select max(logoncount) from @Hold h2 where h2.name = h.name )


How to restore truncated MySQL table from binary logs? I have a mysql server and while working on it I accidentlly truncated the wrong table. I do have a mysql dump wich I immediately used to restore my database structure, but I still miss some entries in only one table.

SHOW BINARY LOGS shows me 64 logs.

How do I know wich log to restore and what is the procedure of restoring with mysqlbinlog? Using the last log is not changing anything ...

EDIT: I already checked http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html  but didn't accomplish anything

Would it be enough to just manually search all logs for the needed insert statements?

Thank you
Soda

You can restore the database from binary logs only if they were enables from the very beginning and if there are all of them starting from .00001.

Then to restore the database you need to replay all binlogs on a fresh MySQL instance:

for log in mysql-bin.*
do
    mysqlbinlog $log | mysql -h <destination host>
done

If you truncated a table you probably need to restore just it. Then restore the database on some other host and move the table to the original server.


Oracle DB client request process Can any body please explain how Oracle DB handles the client request ? is it creates new user process for every connection to db ?

I had look in oracle docs, seems there is concept 'Dedicated and Shared Server process'  this was not clear to me. Oracle DB experts please help

In Oracle 10G, regardless of whether dedicated or shared mode is used the client
always reaches the listener first: to determine whether the instance is available.
In dedicated server mode, from then on, there is one server process for each
client (user process) that directly handles the client.
In shared server mode, the listener passes back the address of a dispatcher process
to the client to submit requests.

Oracle has 3 types of processes:

Server processes connect to user processes and run on the database server.

User processes run on the database client.

Background processes run on the database server and populate the PGA memory areas
read and owned by the server processes.  In dedicated server mode, the PGA memory
area is held by the server process and contains the data needed by the client.
In shared server mode all the data that would have been in the PGA is stored
in the SGA except for the server process stack space.

In Dedicated Server Mode:
There is One Server Process for Every User Process.

In Shared Server Mode:
The user processes all connect to a dispatcher and there are fewer
server processes to reduce the process count on the server.
There is one request queue shared by all the requestors, but
there are individual response queues for each client.

The TNS connect string is different for shared server mode.
After the client program connects, the client program doesn't know
whether the database is running in dedicated or shared server
mode, but the performance of each mode is different (shared server
is not intended for high throughput applications)..


Switching SQL Server service credentials to AD account To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers.  The first one I did was fine because it was a fresh install.  But for existing servers, making the change, I’m not sure about the “rights” required.  The how-to states:
The SQL Server setup program will grant the necessary rights on the machine to that account during installation.
But if I’m not doing a fresh install, I guess I don’t know what the necessary rights are.  I know I will:

Apply Perform Volume Maintenance Tasks Right
Apply Lock pages in memory right
Disable interactive login for the AD service account
Add AD service account to local Administrators group

Is there anything more needed to be done to successfully transfer the SQL Service account from NT SERVICE\MSSQLSERVER?

Use SQL Server Configuration Manager to change the service account used by the SQL Server services.  That will ensure any necessary changes are made to permissions required by that account.  See the MIcrosoft Docs for precise details.
Regardless of what you set the service account to, any local rights should be applied to NT SERVICE\MSSQLSERVER, as this is the per-service Virtual Account, and even after you change the service account, the SQL Server will retain any rights granted to it.

To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers.

This is only a best-practice if you are using a Managed Service Account or Group Managed Service account, and only if

You've got a cluster and need Kerberos authentication, or

You've got multiple services on the SQL Server and and need to differentiate domain permissions between them.

Otherwise leave the default Virtual Account account and grant any domain permissions for accessing external resources (like file shares) to the machine account (eg MyDomain\MyServer$).
See

Managed service accounts, group managed service accounts, and virtual
accounts are designed to provide crucial applications such as SQL
Server with the isolation of their own accounts, while eliminating the
need for an administrator to manually administer the Service Principal
Name (SPN) and credentials for these accounts. These make long term
management of service account users, passwords and SPNs much easier.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#New_Accounts


Remove a period if it is the first character in a varchar column I am migrating data from an old system into a new system I am building and have found someone is doing a bad practice of adding a period to the start of some people's names to make them appear first in some drop down lists. I am trying to figure out how I can remove these periods when they are the first character of the column with out removing periods later in the name (like for Middle Initials). This change is being made during and INSERT INTO SELECT command.

One option is using a CASE statement, such as this one:

CASE WHEN LEFT(MyColumn,1)='.' THEN RIGHT(MyColumn, LEN(MyColumn)-1) ELSE MyColumn END


What kind of SSD to speed up querying a lot of data? For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).

This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.

Is there a difference between PCIe SSDs and M.2 SSDs?

What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?

You can't fix a performance problem by throwing hardware at it.

Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.

In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index.  Or even "re-thinking" the problem.

Let's discuss the query and see if it can be sped up for $0.00.


Understanding aggregate window functions Consider the following table:

CREATE TABLE T1
(
  keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
  (2, 'A'),(3, 'A'),
  (5, 'B'),(7, 'B'),(11, 'B'),
  (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');

Currently, I am looking into window functions and am trying out aggregate window functions. Although I feel I understand how the windows are defined with the OVER and PARITION clauses, I am unsure how the aggregate window functions are being calculated, such as AVG() OVER ().

I am looking to understand the following three queries.

-- Query 1
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      7
     7 | B    |      7
    11 | B    |      7
    13 | C    |     18
    17 | C    |     18
    19 | C    |     18
    23 | C    |     18

-- Query 2
SELECT keycol, col1, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      3
     7 | B    |      4
    11 | B    |      5
    13 | C    |      6
    17 | C    |      8
    19 | C    |      9
    23 | C    |     11

-- Query 3
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      5
     7 | B    |      6
    11 | B    |      7
    13 | C    |     13
    17 | C    |     15
    19 | C    |     16
    23 | C    |     18

Query 1: I believe RowAvg should be the average of the rows for each col1 level. Are the numbers 2 and 7 the FLOOR of the average or is my understanding incorrect?

Query 2: I am not too sure what is being done to produce RowAvg here. As no PARTITION or framing is used here, I believe the window should be the entire table, is this correct? Also, how is the RowAvg being found?

Query 3: Is this finding the (FLOOR) average for each partition however doing this incrementally? That is, for row 1 of the first partition ('A'), we find the average of that row. Then, for row 2 of the first partition, we find the average of the first 2 rows.

General question: Does introducing ORDER BY into the aggregate window function perform the aggregate function 'consecutively' such as in queries 1 and 2? It is interesting to see that in query 1, AVG is performed to each partition as a whole, whereas in queries 1 and 2 the RowAvg's are almost different for each row.

I suggest you add a sum to understand what is going on:

SELECT col1
     , keycol
     , SUM(keycol)  OVER (PARTITION BY col1) AS mysum
     , AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1
order by col1;

Since you don't have an order by in your window, the aggregate applies to the whole partition:

col1    keycol  mysum   RowAvg
A       2       5       2
A       3       5       2
B       5       23      7
B       7       23      7
B       11      23      7
C       13      72      18
C       17      72      18
C       19      72      18
C       23      72      18

I.e. for partition A, mysum = 2+3 for every row in the partition

If you use an ORDER by clause the aggregate is applied from the beginning to the current row:

SELECT keycol
, col1
, SUM(keycol)  OVER (ORDER BY keycol) AS mysum
, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

since you don't have a partition, the whole result set is treated as 1 partition:

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       10      3
7       B       17      4
11      B       28      5
...

For first row  (according to order by) mysum = 2,    rowavg = 2/1
  , second row                         mysum = 2+3,  rowavg = 5/2
  , third row                          mysum = 2+3+5 rowavg = 10/3
  ...

As you can see the sum(...) becomes a cumulative sum

With both a partition and order, the aggregate applies to each partition, but with the behaviour described above:

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1 ORDER BY keycol) AS mysum
, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1
order by col1;

keycol  col1    mysum   RowAvg
2       A       2       2
3       A       5       2
5       B       5       5
7       B       12      6
...

For A you get mysum 2, 2+3. For B it restarts so it becomes 5, 5+7,

In addition you can override the default behavior which is:

SELECT keycol
, col1
, SUM(keycol)  OVER (PARTITION BY col1
                     ORDER BY keycol
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as ...
FROM T1
order by col1;

by declaring your own, for example if you want a sliding average over 3 rows:

SELECT keycol
, col1
, AVG(keycol)  OVER (PARTITION BY col1
                     ORDER BY keycol
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as ...

...
13  C   15
17  C   16
19  C   19
23  C   21

The first row gets an average of (13+17)/2 (since there is no preceding row), the second (13+17+19)/3, ... and the fourth row becomes (19+23)/2 (no rows following)

Fiddle with examples


Log for database tempdb is not available I have been struggling with this issue from so many months now, I am saying its not a database issue and assigning this case to Storage and Operating system team and they are assigning it to me afterwards. This issue is happening repeatedly and there is no any defined pattern of occurrence.

I checked the same question asked here and I can say this is not an issue with database corruption as I am using Ola Hallengren's script for maintenance job and database integrity check(checkdb) is done on weekly basis for User and system database and no issues reported in that.

Accessed second link also for the similar question and can confirm that tempdb is in simple recovery state.

I added one additional file for data and log both so that if one becomes unavailable, other one will still be accessible however later I came to know that the access of tempdb is sequential, so it will go to second file only when first gets full:

Every time this issue occurs, i could see that we have another error in the windows application log as below:

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

Storage team has excluded these files from anti-virus scanning.

One thing to note here - I checked other system databases and their file locations and could see that for master and model, data and log file is in the same drive(E drive) whereas for tempdb and msdb, data is in E drive and log file is in G drive, not sure if this is relevant.

Is there any sequence of checking system database when a job triggers or any event gets fired? If its an issue with that drive then, msdb is also on the same drive.

This is a clustered server with database drives shared in two servers and server is used for sharepoint application.

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
    Jan  5 2018 22:11:56
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Only option that we are left with is to restart the service or fail-over - it just doesn't sound good to do so.

Any help in this regard is appreciated.

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

Yeah, this is bubbled up from the OS error the logwriter is running into. In this case it's OS error 170 which is the resource is in use. That's pretty damning that the issue is outside of SQL Server (barring any bad 3rd party DLLs loaded into the address space).

Normally when I see this it's anti-virus, host protection, defragmentation software, backup software, etc., that ends up holding long locks on the files or in clustered environments when the drives aren't properly setup on the back end.

  Storage team has excluded these files from anti-virus scanning.

Not trying to be rude, but I'm not buying it. Tell them you want proof. Also, just because the file is "excluded" doesn't mean the anti-virus software doesn't look at it, it still does - just doesn't do all the in-depth checks in most cases.

Now What?

I'd start with checking to see if a rogue application is trying to do something. The problem is that this could happen at any time and offending (if any) application could hold the lock for a short time or for a long time - we don't know.

One of the ways to quickly do this would be to use the sysinternals handle application on the file having the issue. Since this error has an error code in SQL Server for it, you should be able to create an agent alert to execute a job which calls a small batch file or PowerShell or whatever you create to run handle against the file. That should help try to grab the information quickly (but might not be quick enough).

Another way to go about it, especially if you can reproduce it fairly frequently or easy, is to run process monitor (procmon) which is another sysinternals tool to capture what is touching that file other than SQL Server.


Mysql closing immediately after new installation I am using mysql as an backend for my webapp ,the software ran without any glitch but today it
gave unexpectedly ERROR:2003 i tried many solution on the internet but none worked. so reinstalled the mysql software and now its command line closes immediately without even asking for password i am logged in as admin   . This problem is troubling me for the past 5hrs does anybody has a solution

The error (2003) Can't connect to MySQL server on 'server' (10061) indicates that the network connection has been refused. You should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.

Start by checking whether there is a process named mysqld running on your server host. (Use ps xa | grep mysqld on Unix or the Task Manager on Windows.) If there is no such process, you should start the server.

If a mysqld process is running, you can check it by trying the following commands. The port number or Unix socket file name might be different in your setup. host_ip represents the IP address of the machine where the server is running.

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

EDITED

Please go through the link for windows once

EDITED 2

1. Run your cmd as administrator.

2.What you will see is like this
c:\windows\system32>

3.Go to your bin location by using cd..
like C:\mysql\bin(my location of bin in my computer is what you are seeing so chose yours correctly)

4.C:\mysql\bin>mysql --install
  Service successfully installed.

5.C:\mysql\bin>NET START MySql
 The MySql service is starting
 The MySql service was started successfully

Then last step is

6.C:\mysql\bin>mysql -u root - p admin

It will ask for password don't enter anything first time because it will use blank, n just press enter you are done.

N later you can set password too...:)


How can I enforce a one-to-zero-or-one relationship with multiple types? I am creating a database structure to represent a football league, but there is a certain part that I do not know how to solve, that is:

A member and he can be a referee, a management or a player (but only one of these types).

I include the following drawing to exemplify the situation:

The attributes for each type are:

member: id (primary key), name, surname, etc.
referee: id (primary key), practice, league (foreign key to league)
management: id (primary key), practice, club_id (foreign key to club)
player: id (primary key), number, club_id (foreign key to club)

Question

With this scheme, a member can be a referee and a player at the same time, but I want to enforce a rule that dictates that a member can be only a refeere or a player, so how can I achieve this goal?

You can add an attribute to MEMBER that defines the type. Example:

CREATE TABLE MEMBER
( MEMBER_ID INT NOT NULL PRIMARY KEY -- ID is to vague IMO
, MEMBER_TYPE CHAR(1) NOT NULL
, ...
, CONSTRAINT <name> CHECK (MEMBER_TYPE IN ('R','M','P'))
) ;

I've heard that some DBMS support CHECK CONSTRAINTS with SELECT and for such you can add a CHECK constraint in each sub table that verifies the type

For DBMS that does not support that you can add a unique constraint in MEMBER:

ALTER TABLE MEMBER ADD CONSTRAINT <name> UNIQUE (MEMBER_ID, MEMBER_TYPE);

and "inherit" the MEMBER_TYPE attribute to the sub tables:

ALTER TABLE REFEREE ADD COLUMN MEMBER_TYPE CHAR(1) DEFAULT 'R' NOT NULL;
ALTER TABLE REFEREE ADD CONSTRAINT <name> CHECK (MEMBER_TYPE = 'R');
ALTER TABLE REFEREE ADD CONSTRAINT <name>
     FOREIGN KEY (MEMBER_ID, MEMBER_TYPE)
     REFERENCES MEMBER (MEMBER_ID, MEMBER_TYPE);

Another idea is to add validation triggers to the sub tables that validate that the member is of correct type before inserting or updating.


MariaDB - cannot set max_connections and wait_timeout through my.cnf I am struggling to set max_connections and wait_timeout parameters in /etc/my.cnf but MariaDB does not seem to read the parameters from the file (it reads some of the others, I haven't checked all).

My /etc/my.cnf file:

[mysqld]
#skip-grant-tables
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# network
connect_timeout = 61
wait_timeout = 86400
max_connections = 100000
max_allowed_packet = 64M
max_connect_errors = 1000

# limits
tmp_table_size = 512M
max_heap_table_size = 256M
table_cache = 512

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[client]
port = 3306
socket= /data/mysql/mysql.sock

But when I check max_connections and wait_timeout variables in MariaDB, it shows the default value:

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

However, other parameters in my.cnf are correct:

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
+--------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 61    |
+-----------------+-------+
1 row in set (0.00 sec)

I can set max_connections variable from mysql command line but it resets itself when I restart the service:

MariaDB [(none)]> set global max_connections := 10000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 10000 |
+-----------------+-------+
1 row in set (0.00 sec)

Hower, for wait_timeout this does not work:

MariaDB [(none)]> set global wait_timeout = 86400;                                                                            Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'wait_timeout';                                                                         +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

wait timeout is set as a session variable:

MariaDB [(none)]> SELECT * FROM information_schema.global_variables WHERE variable_name='wait_timeout' UNION SELECT * FROM information_schema.session_variables WHERE variable_name='wait_timeout';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| WAIT_TIMEOUT  | 86400          |
| WAIT_TIMEOUT  | 28800          |
+---------------+----------------+
2 rows in set (0.00 sec)

Checked all my.cnf files, none of the is overriding the parameters:

[/] # locate my.cnf
/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf~
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/server.cnf
/root/.my.cnf

OS: RHEL 7

MariaDB version: mariadb-server-5.5.47-1.el7_2.x86_64

wait_timeout is a screwball one:

"On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout. "

max_connections claims to have an upper bound of 100000, but I would say that even 10000 is unreasonably large.  Does it work to put max_connections=1000 in my.cnf?

You understand that you need to, after changing my.cnf,

restart mysqld
disconnect and reconnect to see the change
use SHOW GLOBAL, not SHOW, which defaults to SHOW SESSION.

The interaction between GLOBAL and SESSION (for both VARIABLES and STATUS) varies with the setting.  For many, not all things, session is initialized to global when you login.  And wait_timeout is probably the quirkiest.

Warning

If you had 100000 connections each running complex SELECTs needing, say, 3 tmp tables, then you might need 100000 * 3 * 256M = 76.8TB of RAM to handle the tmp table!  ( 256M = min(tmp_table_size, max_heap_table_size) ).  This is a strong reason not to set all those things so high.


Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit? I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS.  Recently, the database has starting going unresponsive for 10-30 minutes at a time during peak traffic time.  This often happens 3 or 4 times throughout the peak traffic hours.
During these unresponsive times, it is almost impossible to open a connection with the database (timeouts are the most common response).  If you get a connection, queries normally perform as expected for a short while.  The processlist shows dozens to hundreds of items with state "init" and info "commit".  Row operations drop nearly to zero and stay there for minutes on end until the database suddenly begins to recover, the processlist clears, and it becomes responsive to traffic once again.
What we have attempted so far:

We have tried to remove all slow queries during these peak hours, shutting down large swaths of functionality and workers during these times.
We have increased the redo log size in innodb.
We have looked for lock contention and deadlocks.
We have doubled the compute power, memory, and throughput available to the RDS instance.   (DB CPU usage during peak hours hovers around 50% and there are no alarming spikes in memory or disk or network usage.)
We have tried using proxy servers to hold open long-lived connections to the database.
We've looked for any recent changes or new queries introduced in the app.

All of that only helped a little bit.  We often still see at least one "storm" where we get stuck in the bad state, and re-enabling any batch jobs tends to push us over the edge.
Has anyone seen the pattern of processes getting stuck in STATE=init, INFO=commit for minutes on end?  Does anyone have suggestions on how to proceed with debugging or analysis?  What other resource contention could we be running into?

You are seeing the victims.  Probably only a few of them are the villains.
Look at "Time".  A few 'system' processes may have very large times; look at the next couple -- they may be the ones that started the problem
How big is max_connections?  If it is more than a few dozen, it is inviting a log jam.
When a lot of threds are running (check Threads_running), they tend to stumble over each other, waiting for resources (CPU, I/O, buffer_pool space, table cache space, etc).  Meanwhile, the allocation mechanism is "playing fair".  That is, it is given each player a little of what it needs, so as to give a fair amount to the others.  Net effect:  Latency goes through the roof and throughput flattens or declines.

Cures:

Check for swapping -- Swapping is terrible for performance, and it may be a factor in what you are seeing.
Lower (yes, lower) max_connections.
Lower (yes, lower) the number of web server clients that can run simultaneously.
Use the slowlog (with a low value of long_query_time to identify both the long-running queries and the fast, but frequently running, queries.  Improve both.
It is usually easier to give the end-user a civilized message "System is busy, DO NOT HIT RELOAD", than to let things clog up and "never" finish.
Use Replicas to spread the readonly queries across multiple servers.


Viability of a large database without using indexes and some other things I'll explain my situation as briefly as possible. My group is working for another company and I'm mostly creating stored procedures for making reports.

The twist is that, last month, they decided to do some major changes, which increased a lot the time required to what before worked nicely. I don't know the details, but one of the big changes is that what used to be tables now are views that get the data from an external source. Those are defined as:

CREATE view [dbo].[eng_contra_env] as
select ce_serial, ce_empresa, ce_ganado, ce_contra, ce_fecha_env, ce_fecha_rec, ce_observa
  from OPENROWSET('MSDASQL', '***', 'SELECT * from eng_contra_env');
GO

Also, since that change, I'm not allowed to do some basic things like indexing some tables, as my boss tells me not to do so due to those changes that the client did.

So, I'm asked to improve the performance of some procedure (which again, before the changes it finished, now it doesn't). One bit of the procedure requires me to do the following:

https://www.brentozar.com/pastetheplan/?id=SkDJAvZQI

How am I supposed to optimize that? Also, with what I said already, how viable do you think it is to work under these conditions.

Update: Apparently they had a meeting at the client and they decided to revert these changes. Thank god!

The use of views backed by linked server queries puts you in a pretty tough situation.  You get bad cardinality estimates coming out of the "remote query" operators (10k rows estimated, vs. the actuals of 521k and 30k respectively).  This results in SQL Server choosing to sort and use a merge join, when it would probably have chosen a hash join had the estimates been better.

For this specific query, you could try a hash join hint to avoid the sort + merge join plan:

From  eng_facven A
    inner hash Join
   eng_facved B On A.fv_serial = B.fd_serial

As a general solution, you might consider materializing the remote tables into temp tables first.  This will give you better estimates and thus likely better plans.

Of course, the feasability of this option depends a lot on the size of the data / number of rows.  In the query referenced above, you'd only be loading around 16 MB total into temp tables, which is probably reasonable - especially for a report that might only be run once a day, or a few times per day.

The example query you provided would look like this:

SELECT *
INTO #eng_facven
FROM eng_facven;

SELECT *
INTO #eng_facved
FROM eng_facved;

Select  A.fv_empresa, A.fv_ganado, A.fv_contra
   , Sum(A.fv_animales)        fv_animales
   , Sum(A.fv_primales)        fv_primales
   , Sum(B.fd_prima)/Nullif(Sum(A.fv_animales),0)  fv_prima_total
   , Sum(B.fd_kilos)         fv_kg_factu
   , Sum(B.fd_bruto)         fd_precio
   , Sum(A.fv_imp_dto_trans)       fv_dto_fac

   , (Sum(B.fd_bruto) - Sum(A.fv_imp_dto_trans))
    /
    Nullif(Sum(B.fd_kilos),0)       fv_fd_prec_kg_canal

From  #eng_facven A
    Join
   #eng_facved B On A.fv_serial = B.fd_serial

Group by A.fv_empresa, A.fv_ganado, A.fv_contra;

For larger tables, you might need to consider having a scheduled (daily / hourly) job that copies the contents of the remote tables into local tables, and have your queries reference those.  This depends on how up-to-date your reports are required to be.


SQL query for this problem I have one single table.

Table A has two columns

id, sample_id

an id will be associated with multiple sample_id..However both values i.e combined are unique

example,

id, sample_id
1, 10
1, 11
1, 12
2, 9
2, 10
2, 11

Now i want to update all the records of "1" to "2" provided it doesn't have a record in "2"

How to do it? Expected Result

id, sample_id
1, 10
1, 11
2, 12
2, 9
2, 10
2, 11

Thanks

UPDATE test t1
LEFT JOIN test t2 ON t1.sample_id = t2.sample_id AND t2.id = 2
SET t1.id = 2
WHERE t2.id IS NULL;

fiddle


In PostgreSQL, if crashes during VACUUM, will it recover from a checkpoint that is before the frozen transaction ID of VACUUM? When VACUUM starts, it will calculate a frozen transaction ID and freeze all tuples older than it. Will this frozen TXID be younger than the latest checkpoint?
If this is the case, then when the system crashes after VACUUM has deleted some dead tuples and the system recovers from this checkpoint, these tuples will be available to some transactions again during the replaying of WAL.
Is my understanding of this process correct?

It could well be that the freeze limit is younger than the latest checkpoint, if you set vacuum_freeze_min_age low enough, but that is no problem.
Tuples are either frozen (if they are all-visible and older than vacuum_freeze_min_age transactions) or removed (if they are "dead": invisible to everyone and no longer needed). Dead tuples are never frozen.
During crash recovery, all actions by VACUUM are replayed (with the possible exception of hint bits), so when recovery is done, all dead tuples are sure to be gone, and all frozen tuples will be frozen again.
Nobody can connect to the database before crash recovery is finished, so there is no danger of anybody seeing inconsistent data.


Does Amazon Web Services (AWS) RDS support locally redundant storage (LRS) for Microsoft SQL databases? Looking at the documentation for Azure databases I see that they support LRS (local stored in three seperate location) (I'm not interested in GRS) and I was wondering if anyone knew if AWS offered the same functionality? Ideally I just want to prevent against data loss (I realise data corruption and zone availability are issues, but these are not my primary concern)

In essence, if the hardware running my database breaks, I don't loose any information?

Thanks

Gordon

Amazon doesn't call it LRS, but the Elastic Block Store (EBS) storage which backs RDS (see RDS FAQ, "What is the hardware configuration...") is automatically replicated across multiple servers (see EBS product page) within the same Availability Zone (i.e. locally).

Data resilience is extremely high for RDS databases.


Software need db owner permission to master database (sql2016) We have a manufacturer whose software requires authorization for the master database (only db owner working). Unfortunately, the manufacturer can not tell us why this access to the master database is necessary. Without this permission, the application will not start.

What are your opinions? Have you ever seen such software? We have 200 different applications but this is the first one that needs permanent access

Is there any objection to security?

As an alternative, we can install an SQL Express instance because the database is very small. If access to the master DB from your point of view is not a good idea.

I have seen software that requires a sysadmin server role membership, at least during installation. It might be that the software creates dbo-owned objects in master and db_owner is the easiest way to allow that without resorting to sysadmin role membership. Although it might be possible to provide more granular permissions, application code changes might be needed and one cannot the grant the minimal permissions necessary without knowing specifically what the software does and the vendor would need to support such changes.

A separate instance/machine (including in Azure) can help mitigate security concerns. SQL Server Express might be an option to reduce costs if you can work within the resource limitations but be aware your HA options will be limited.


MySQL export/import yields error When trying to export and import an 58G database yields the following error:

  ERROR 1071 (42000): Specified key was too long; max key length is 1000
  bytes

Dump created using mysqldump -q --opt and import is just cat bigfile.sql|mysql ...

MySQL versions:

Import: 5.1.49-1ubuntu8.1
Export: 5.1.41-3ubuntu12.10-log (Ubuntu)

This sounds like a character set conversion issue to me, the clue being bytes rather than characters. Are you going from ASCII to a multibyte charset, e.g. Unicode? Check the target database, check your environment (NLS_ env vars), check the dumpfile itself (e.g. if you were using UTF8 in the source that's Unicode but not multibyte if you only used the lower code page).


Stock Return as Zero due to unsold At the moment I'm bit stuck and need advice, I have looked up the 'ifnull' but nothing I can find 'ifnull' along with join tables.

SELECT Ifnull((SELECT tbl_sku_units_order.sku         AS 'sold_sku',
                  tbl_list_sku.sku                AS 'list_sku',
                  tbl_sku_units_order.units_ordered,
                  COALESCE(Sum(units_ordered), 0) AS 'Today_Sold'
           FROM   tbl_list_sku
                  INNER JOIN tbl_sku_units_order
                          ON tbl_sku_units_order.sku = tbl_list_sku.sku
           GROUP  BY tbl_list_sku.sku
           HAVING units_ordered IS NOT NULL
                  AND Length(units_ordered) > 0
           ORDER  BY units_ordered DESC), '0');

so I have tried different function, at the moment I am using this

SELECT
    tbl_sku_units_order.sku AS 'sold_sku',
    tbl_list_sku.sku AS 'list_sku',
    tbl_sku_units_order.units_ordered,
    COALESCE(SUM(units_ordered), 0) AS 'Today_Sold'
FROM
    tbl_list_sku
        INNER JOIN
    tbl_sku_units_order ON tbl_sku_units_order.sku = tbl_list_sku.sku
GROUP BY tbl_list_sku.sku
HAVING units_ordered IS NOT NULL
    AND LENGTH(units_ordered) > 0
ORDER BY units_ordered DESC

in the end, I got the result what they sold

but we have 100 SKU are unsold, but they are not displayed at all, as I would like to see the 100 unsold SKU displayed as '0'  as two different table on it.
Maybe I am missing something?

I would make the following changes, to solve the issue and fix other minor problems with the query:

Solving the issue:

use LEFT instead of INNER join, in order to get results for skus that don't have orders.
remove the HAVING clause as it would essentially cancel the previous change.
use COALESCE() or IFNULL() to get 0 instead of NULL for these skus.

GROUP BY issue:

use SUM() or MIN() or some other aggregate for all columns in the SELECT that are not in GROUP BY.

If you use IFNULL(tbl_sku_units_order.units_ordered, '0') AS 'unit_order' for example, when the group by is GROUP BY tbl_list_sku.sku, you may have many rows - with different units_ordered values - in the orders table. Which one should be shown?

Now MySQL allows that (in versions before 5.7) but the result is indeterminate (any of those values may be shown). The bug/feature has been fixed in version 5.7 but it's good to use correct code, first to get sane results and second so it won't break when you upgrade.

Easier to read code:

use aliases for table names
use back-quotes or double-quotes for column aliases, or just not put spaces in names/aliases so everything can be typed without quotes.

The query:

SELECT
    ls.sku                                AS list_sku,
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku,
    COALESCE( SUM(suo.units_ordered), 0 ) AS today_sold
FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo
              ON suo.sku = ls.sku
GROUP  BY ls.sku
ORDER  BY today_sold DESC ;


how to purge / shrink .idb tables I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables?

I have tried to use optimize table table_name but after the query the .idb tables stays the same size.

This is my configuration at [mysql] in /etc/my.cnf:

[mysqld]
max_allowed_packet=500M
innodb_buffer_pool_size = 15G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit=2
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=134217728
innodb_file_per_table=1

Am i missing something?

To shrink an .ibd file it's enough to run ALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE does exactly the same internally. ALTER TABLE however is preferred because you can use pt-online-schema-change to avoid blocking the table.

To get better understanding how data use the tablespace I would recommend InnoDB tools https://github.com/jeremycole/innodb_ruby

For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:

# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▄▂░░                                                         │
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3   42.86%
  █ Index 15                                                            1   14.29%
  █ Index 16                                                            1   14.29%
  ░ Free space                                                          2   28.57%


Having multiple ResultSets in parallel on same connection.. Will it cause any problem? [Mysql/Postgres] I have tried out the following in my local machine using jdbc/mysql:

step 1: conn = getNewConnection
step 2: stmt1 = "new statement" using connection 'conn'
step 3: stmt2 = "new statement" using the same connection 'conn'
step 4: resultSet1 = execute stmt1
step 5: resultSet2 = execute stmt2
step 6: operating on both resultset 1 and 2
step 7: proper closing of all resources

worked fine in my machine. Can I be sure of this behaviour always (Mysql/Postgres with JDBC )? Will there be any issue?

SQL is not made for parallelism.  Adding such in your code will only make things more complex, and possibly slower.

For now, focus on writing non-parallel code.  When finished, you may need to ask the question of "how can I make this faster", with the sub-question of "would parallelism help".  The former question may involve indexes, reformulation of the query, merging two queries into one, etc.  Parallelism (in the application) is only rarely the 'right' way to approach SQL work.


How to schedule a weekly backup of a database only if it has been changed since last backup I´m new to SQL Server administration.

I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary).

Is there some way how to do that only with SQL Server, like some automatic procedure or something like that?

Welcome to MS SQL :-)

To start with your question:

Yes there is. First you make a full database backup:

BACKUP DATABASE yourdatabasename
TO DISK = "pathtobackupdir\backupfilename.bak"

Now from that moment on a so called differential bitmap is keeping track of all pages changed.

From that moment every time you create a DIFFERENTIAL backup, only the pages that changed since your last FULL backup will be backupped.

 BACKUP DATABASE yourdatabasename
    TO DISK = "pathtobackupdir\backupfilename.bak" WITH DIFFERENTIAL

So every sunday, asuming you had changes every week, the diff backup will be bigger then the previous one. Untill one sunday, it's just as big as a full backup would be. That would imply that all data pages had changed. At that moment you want to do a new full backup. Since that will reset the differential bitmap and your next diff backup will be small again. (depending on the amount of changes)

You could automate the process of making diff backups untill a certain percentage of the data has changed. Have a look at this blog post: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx
It explains a way on how to programatically look up the percentage of database changes. That way you could say.. make diff backups if percentage changed < 75% otherwise make a full backup.

Now could you tell me why you want this? Because this isn't a setup I would advise a starter (with exceptions). So please elaborate a bit and we can see if this is the best solution for your setup. How big is your database?  how much changes do you have? is batch based changes? Which version and Edition of SQL do you have do you have?


Error 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) This looks like a duplicate of many questions asked here. I haven't found the answer for my specific problem, so I'm posting it here.

I was trying to create a local database and a user with basic permissions to it:

CREATE DATABASE demodb;

  Query OK, 1 row affected (0.00 sec)

USE demodb;

  Database changed

CREATE TABLE demotable(id int, name VARCHAR(25));

  Query OK, 0 rows affected (0.01 sec)

CREATE USER 'demouser' IDENTIFIED BY 'p';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.00 sec)

With this setup, I wasn't able to log in:

mysql -u demouser -p demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h 127.0.0.1 demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h localhost demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

I am using MySQL 5.6 on macOS using the official dmg installer.

The fix was to specify the IP address when creating the user and granting it permissions:

CREATE USER 'demouser'@'127.0.0.1' IDENTIFIED BY 'p';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser'@'127.0.0.1';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

Then you can log in this way:

mysql -u demouser -p -h 127.0.0.1 demodb

Credits to http://www.rathishkumar.in/2017/04/error-1045-28000-access-denied-for-user-user-host-using-password-YES.html


MySQL Benchmark on FreeBSD and Ubuntu I'd like to benchmark two db servers, one is running Ubuntu the other FreeBSD. Unfortunately we are unable to install SysBench on the FreeBSD OS. Could anyone recommend any alternatives? Or should I look into creating some intense queries based on the schema?

In order to compile correctly SysBench 0.5 on a FreeBSD system you have to download the source code from launchpad.net (SysBench v0.5), see bug 1029088

Then, for benchmarking purposes, you can take a look into this post:

Stress test MySQL with queries captured with general log in MySQL

Or you can use the standard oltp test provided by SysBench


Commit() takes 50 seconds after inserting about 100000 rows I have a sqlite database that is, (I think), fairly small.

I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.

I then open a transaction and insert about 100000 rows, the time it takes is about 10 seconds and I think this is acceptable for that number of rows, (and given the fact that my app does some 'other' processing.

My concern is when I do the 'Commit()' itself.

It takes around 50 seconds to write the data to disk... (around 10Mb is actually written).

This is what I do:

-- pseudo code for brevity...
Data Source=database.db;Version=3;Pooling=True;Max Pool Size=100;

PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint = 16;
PRAGMA journal_size_limit = 1536;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;

-- BEGIN TRANS

-- 100K Inserts/ 10K Deletes

-- COMMIT

Can anybody think why the commit would take so much longer than the entire transaction?

Any suggestions on how I could speed the commit up?

In WAL mode, the database must do regular checkpoints to prevent the log from growing infinitely large.

So after a large amount of changes, it decides to do an automatic checkpoint (especially since you have set wal_autocheckpoint to a rather small value).

You can move the checkpoint delay to another time (but not avoid it altogether) by doing checkpoints manually.


Third Normal Form: Composite PRIMARY KEY vs System-Generated Surrogate (IDENTITY) I’m working on a data modeling project and I’m trying to figure out the best data modeling approach for a history table, which has only four columns:

CREATE TABLE FooHistory
(
    SecurityID  INT (FK),     -- Part of the natural PK.
    FieldID     INT (FK),     -- Part of the natural PK.
    DateCreated DATETIME2(0), -- Part of the natural PK.
    Value       VARCHAR(50)
);

The natural composite KEY in this table would be (DateCreated, SecurityId, FieldID), and every 30 minutes the ETL process will add ~ 2K rows to this table.

Questions

Pros and cons of declaring a composite PRIMARY KEY (PK) (DateCreated, SecurityId, FieldID) vs. adding a new IDENTITY column (that is, a system-generated surrogate) and use it as PK?
I believe that, if I add an IDENTITY column and use it as PK, then this table won’t be in Third Normal Form (3NF), because there will be a functional dependency between non-PK columns, that is, (DateCreated, SecurityId, FieldID) and Value.
Since this table keeps historical data I don’t expect to JOIN this table to other external tables, and applications will mainly interact with it using SELECT statements. Based on these assumptions is it worth it to keep the table in 3NF and declare a composite PK, or I should add the IDENTITY column to this table?

According to your description of the case under consideration, it seems that (a) appending a column to hold system-assigned surrogates* (surrogates for brevity) via an IDENTITY property to the History table is (b) superfluous.

A column retaining surrogates can be useful when a tentative natural PRIMARY KEY (PK) constraint has the potential of being referenced from FOREIGN KEY (FK) constraint definitions, but such tentative PK constraint is

wide, i.e., it encompasses a relatively large number of columns and/or
the values to be contained in the potential PK column(s) are physically heavy in terms of bytes (evidently, heavy values behave slower than light ones because of, e.g., larger memory and disk space usage concerning reading and writing processes, with the respective impact on the response speed of the logical data manipulation operations).

As a consequence, having FK constraints that point to a single column that (1) would somewhat work as a “substitute” for a natural KEY like the one described above and (2) would keep lighter values, is a possibility worth to assess. Regarding the History table at hand, it is neither logically wide nor is supported by a heavy physical scaffolding.

Besides, attaching a column for surrogates would necessarily make the table in question wider due to the incorporation of a tentatively needless element (and, perhaps?, of the speculative corresponding physical INDEX), so it might end up behaving slower. Hence, considering its effect on the overall performance of the database, it may not provide any pragmatic value regardless.

A situation in which this peculiar class of column can sometimes be of help is when sticked to tables that stand for entity types that are indpendent† but, as you know, even in said kind of situation each particular table demands (i) individual and (ii) contextual analyses to determine whether sticking a column of that sort is convenient or not (and I guess that is why you asked the question).

At the logical level, a column that maintains surrogates, of course, neither captures more business domain meaning (which hinders readability and interpretation of result sets) nor protects real row uniqueness (a column that keeps meaningless values is an additional non-data artefact, so you still have to ensure row uniqueness of the genuine data elements). See this outstanding Stack Overflow answer by @PerformanceDBA for evidence about these aspects.

However, you “could” add it to the History table, “use” it as the PK and, at the same time, declare the combination of columns (DateCreated, SecurityId, FieldId) as an ALTERNATE KEY‡ (AK) by means of (a) the configuration of a composite UNIQUE constraint and (b) the specification of every one of the columns in said combination as non-NULLable (arrangement that would actually guarantee row uniqueness with regard to the true data elements). In this way, the non-key column named Value would be functionally dependent on both

the column that retains surrogates, “utilized” as PK, and
the meaningful and natural AK made up of the three relevant columns;

thus, the table would still comply with 3NF§ anyway, although the usage of the column with surrogates would not be providing benefits, therefore it would be excessive.

Practical examples of why it would be excessive are:

The History table would mostly be queried by virtue of SELECT statements that include one or more of the columns that are part of the natural AK as conditions in the WHERE clause (while a column enclosing surrogates would hardly be included, because they are meaningles to end users).
INSERT operations INTO the table would be slowed down as a result of the integration of an extra column.

It is important to note that, at the physical level, establishing a good INDEXing strategy to serve the composite PK definition is paramount to optimize the functioning of the pertinent processes, hence some testing sessions should be carried out with respect to, e.g., the determination of the suitable order of the columns assisted by the applicable INDEX(es) configurations.

Endnotes

* System-assigned surrogates were evaluated in the 1979 paper entitled Extending the Database Relational Model to Capture More Meaning, which was written by Dr. Edgar Frank Codd, the creator of the relational paradigm. In said paper, it is stipulated that surrogate values must not be displayed to database users, even though in most databases I have seen that rule is not followed. The present answer is based on the assumption that, in the scenario under deliberation, surrogates are exposed just like ordinary business context values.

† At the conceptual level, an entity type (or entity prototype) is independent when every one of its occurrences (i.e., rows, when retained in a SQL table) is uniquely identified by the values of one or more of its own attributes (i.e., columns) alone. A dependent one is that which requires the use of the value(s) of one (or more) attribute(s) that belong to other entity types so that each of its instances can be uniquely identified.

‡ An ALTERNATE KEY is a column (or a combination of columns) that holds values that uniquely identify each row of the pertinent table but was not chosen as the PRIMARY KEY; each table can have zero, one or more ALTERNATE KEYS.

§ To analyze why this is so, see the genuine definitions of normal form and normalization in A Relational Model of Data for Large Shared Data Banks (1970) and of the successive normal forms (as suggested in Joel Brown’s subsequent answer) in Further Normalization of the Data Base Relational Model (1971), both works —naturally— by Dr. E. F. Codd. It is worth to mention that the original relational model (1970) does not include surrogates.


Insert query result into database row I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

When I execute only the select statement I get:

count   volume  date    year    month   week    day transaction_type    contract_id merchant_id terminal_id status  card_brand  currency
1   3000    2018-11-11  2018    11  45  11  Sale    1   2   1   approved    NULL    USD

But I can't execute the INSERT. I get error:

Field 'id' doesn't have a default value

I use for I sequence:

CREATE TABLE `payment_transactions_daily_facts` (
  `id` int(11) NOT NULL,
  `card_brand` varchar(255) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `terminal_id` int(11) DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `week` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How I can use sequence for id?

You need the AUTO_INCRMENT default for your id field, so:

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

Then, next time you run your INSERT, the system will create an auto incremental id for you automatically.


Can shrink database improve performance Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application.
I don't know yet what the issues are but the solution they propose is to shrink the database (because there is a lot of free space). By my knowledge this can not result in a performance improvement but apparenty they had success with this method when applying at other clients.
The way I think the shrinking happens is by moving pages from the back to the front of the file and like so compacting the database and causing massive fragmentation.

Is it possible that range scans take advantage of compacting the database?
Or are there some other edge cases that can benefit from shrinking a database?

The strong general advice is to never shrink of course.
Still, the question here is: Can shrinking ever improve performance?
Well, maybe. Consolidating all the used pages at the start of each file might have benefits for ramp-up reads, and have favourable cache effects at multiple levels, including at the storage layer. In addition, shrink will remove any empty pages from heap tables rather than move them, so that might help heap table scan performance directly.
Shrink also checks if a b-tree or heap page should be recompressed, which might be relevant if data compression is in use. Shrink also never generates forwarding pointers, so shrinking heap pages can result in "unforwarding" and stub removal.
It would still be better to take more effective action to address whatever the specific cause of poor performance is. That requires understanding the issue and knowing how to fix it, rather than (blindly) trying things that appeared to work before.


Read inside node value of Xml using Sql Server I want to read node values of xml in sql, my xml look like this

I just want to read value of order node that is 'Order 1'
and my code is

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT * FROM OPENXML (@idoc, '/ROOT/Customer/Order',0)
WITH (
    [Order]  varchar(100)
    );

CREATE TABLE test(data xml);
insert into test values
('
<ROOT>
  <Customer>
    <Order>Order 1</Order>
  </Customer>
  <Customer>
    <Order>Order 2</Order>
  </Customer>
</ROOT>');
GO

1 rows affected

If you need first <Order> element of your XML data you can use the next sentence.

SELECT data.value('(ROOT/Customer/Order)[1]', 'varchar(100)')
FROM   test;
GO

| (No column name) |
| :--------------- |
| Order 1          |

If you need to select all <Order> elements you can use a CROSS APPLY solution with .nodes()

SELECT T2.lin.value('(Order)[1]', 'varchar(100)')
FROM   test
CROSS APPLY data.nodes('/ROOT/Customer') as T2(lin)
GO

| (No column name) |
| :--------------- |
| Order 1          |
| Order 2          |

dbfiddle here


why is there an index range scan on max() query? I have a table that logs the transmission of data to another system.
create table transfer_log (id number(10),
                           fk_data number(10),
                           first_sent_time date,
                           last_sent_time date,
                           transfer_status number(3),
                           error_message varchar2(250));

create unique index pk_transfer_log on transfer_log (id);
alter table transfer_log add constraint pk_transfer_log primary key (id);

create index idx_transfer_log_status on transfer_log (transfer_status, last_sent_time desc);

The transfer_status column stores the HTTP status from sending the data.
transfer_status is never null (initially 0), last_sent_time is initially null.
So I have a health check, to see when the last successful transmission was:
select
  max(d.last_sent_time)
from
  transfer_log d
where
  d.transfer_status = 200
;

and I hoped the index would make this query fast. But it's close to 1sec now and the execution plan doesn't look good:
----------------------------------------------------------------------------------------
| Id | Operation           | Name                    | Rows    | Bytes   | Cost | Time |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |                         |       1 |       6 | 2624 |      |
|  1 |   SORT AGGREGATE    |                         |       1 |       6 |      |      |
|  2 |    INDEX RANGE SCAN | IDX_TRANSFER_LOG_STATUS | 1272670 | 7636020 | 2624 |      |
----------------------------------------------------------------------------------------

I hoped the index (especially with the descending last_sent_time) would make this query fast as only the first entry needs to be read.
What did I do wrong?


Specifying Oracle Service Name in UNIX Profile Is there any way we can specify Oracle Service Name in UNIX Profile?

I know that there is an environment variable ORACLE_SID for specifying SID in the Profile,

But how about service name? If there is, then can we remove ORACLE_SID once we set the Service Name in the Profile?

Use the TWO_TASK variable and set it to [server]/[service]

export TWO_TASK=server.example.com/mydb

This will override ORACLE_SID and make a sqlnet connection to the DB.


Google Datastore (NoSQL) backend for PHP CMS I am working on a personal project where I am creating a small CMS using PHP as the developing language, but I want to be able to deploy it to the Google App Engine and I want to use its Google Datastore (NoSQL).

The question I have is how to do relationship between kinds?

For example:

Kind 1: call category
Kind 2: call tags
Kind 3: call post

I want the posts to be able to have one category, but multiple tags. I am having trouble visualizing it. I am coming from SQL world so having the ID of the category and the IDs of the tags to them JOIN them will take care of the same issue.

Note: if there is some tutorial or reading that you could share I will appreciate it.

Try this link https://cloud.google.com/appengine/docs/php/storage . This should get you started on the use and main concepts.
FYI joins are not applicable to the datastore but if you model out your data correctly you should have no problem.


Database Schema with a shared identity Field I'm a developer treading into the database world so bear with me please.

I'm creating a Contract database with separate tables for various types of contracts.

I have 5 tables but would like to share a common 'Identity' field between them, so that there will be a unique contract # for any given contract regardless of type.

What is the recommended way to do this? Something that won't over complicate simple crud transactions?

My application will be using a Sql Server 2008 R2 server..the app itself is Silverlight 4 using WCF Ria services for CRUD transactions.

A schema idea would be great, a tutorial of something similar ideas and technology would be even better.

Thanks for looking
Robert

Until SQL Server supports sequences (next version "Denali") then you'll have to have a common table.

However, if I understand you, I think you're looking at the subtype/supertype pattern. A sequence would be nice but if you designed using, say, Object Role Modelling then you'd generate this pattern/schema

Basically, you have a common "Contract" table:

ContractID, PK, UQ1 (Identity)
ContractType, FK, UQ1
InsertedDateTime
UpdatedDateTime
CounterParty

Notes:

the PK is ContractID to give a unique value
ContractType defines your "various types". Let's go for Tom, Dick and Harry as types
The unique key UQ1 is on both ContractID, ContractType (this is a "super key")
InsertedDateTime, CounterParty etc are common attributes

Then you have sub-tables with specific attributes for each contract type

ContractTom: ContractID, ContractType, TomAttrib1, TomAttrib2, ..., TomAttribn
ContractDick: ContractID, ContractType, DickAttrib1, DickAttrib2
ContractHarry: ContractID, ContractType, HarryAttrib1, HarryAttrib2, HarryAttrib3

Notes:

ContractID, ContractType are both the PK and FK.
ContractType has CHECK constraint to restrict to Tom or Dick or Harry


Adding an identity field to be the new cluster of a large table - how do I get the "right" order? I have a very large table (88m rows, nearly 300GB in size) that has a poorly planned clustered index. I want to add a BIGINT identity field, and make it the new clustered index (having the desirable attributes of unique, narrow, static, and ever-increasing).

The problem is that I also want this new identity field to be ordered in a certain way on the current records (based on an existing date field that is not the current cluster). This is so when we purge the oldest records, those will all be contiguous on disk. (The date field isn't unique, though, so isn't a good candidate for the cluster by itself.)

How can I get the values for this new identity field ordered the way I want?

The first idea I had was to make a new table with the identify field, and just INSERT INTO rows from the current table ORDER BY the date field? Would that work?

If I don't have enough disk space to do that, is there any way to do that "in-place"? Perhaps by changing the clustered index to the date field (wait a long time), then add the identity (and wait a long time again)? Are the values of the identity guaranteed to be ordered by the current cluster?

I just performed the following experiment on a SQL Server 2012 box:

CREATE TABLE rdtest (label varchar(100), value int);

INSERT INTO rdtest VALUES
       ('one', 1)
      ,('two', 4)
      ,('three', 9)
      ,('four', 16)
      ,('five', 25)
;

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Result:

label    value       test_id
-------- ----------- -----------
one      1           1
two      4           2
three    9           3
four     16          4
five     25          5

Next:

ALTER TABLE rdtest DROP COLUMN test_id;

ALTER TABLE rdtest ALTER COLUMN value int NOT NULL;
GO

ALTER TABLE rdtest ADD CONSTRAINT PK_rdtest PRIMARY KEY ([value] DESC);

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Results:

label    value       test_id
-------- ----------- -----------
five     25          1
four     16          2
three    9           3
two      4           4
one      1           5

And, one more test run:

ALTER TABLE rdtest DROP CONSTRAINT PK_rdtest;

ALTER TABLE rdtest DROP COLUMN test_id;

ALTER TABLE rdtest ALTER COLUMN label varchar(100) NOT NULL;
GO

ALTER TABLE rdtest ADD CONSTRAINT PK_rdtest PRIMARY KEY ([label] ASC);

ALTER TABLE rdtest ADD test_id int IDENTITY(1,1);

SELECT * FROM rdtest;

Results:

label    value       test_id
-------- ----------- -----------
five     25          1
four     16          2
one      1           3
three    9           4
two      4           5

I suppose this isn't a guarantee that the clustered index dictates the order a new IDENTITY column gets filled in - but, I'd say it strongly indicates that it's very likely.


How to Log Debug Information About a Stored Procedure We have a group of fairly complex stored procedures that process a transaction queue on a round the clock schedule. These scripts use a number of print statements to document what is being done to aid in debugging.

I want to keep this information while it is running in production (as an SQL Server Agent job) to help with troubleshooting when something goes wrong.  I have looked at this site which has a fairly good method of saving the print output but it also has some downsides (output is not available until the script completes, etc). It also requires SSIS or DTS which I have no experience with.

I have thought of changing all of the print statements to insert statements to a log table. The problem is that much of the work we need debug info for is under transaction control and if there is an error, all of the log information would be rolled back with the actual transaction.

Does anyone have a better solution or an idea of how to work either of the solutions I have looked at above?

One solution to the rollback problem is to log information to a table variable (which isn't affected by the transaction), then roll back, then insert into the real logging table. Quick example:

DECLARE @LogRow TABLE
(
  dt DATETIME, objectid INT,
  errornumber INT /* other columns */
);

BEGIN TRANSACTION;

BEGIN TRY

  SELECT 1/0;

  COMMIT TRANSACTION;

END TRY
BEGIN CATCH

  INSERT @LogRow(dt, objectid, errornumber /* , ... */)
    SELECT SYSDATETIME(), @@PROCID, ERROR_NUMBER() /* , ... */;

  ROLLBACK TRANSACTION;

  INSERT dbo.RealLogTable(/*...columns...*/)
    SELECT /*...columns...*/ FROM @LogRow;

END CATCH


How to use "WHERE" and also "WHERE NOT EXISTS" in the same query and table? I am trying to add both WHERE and WHERE NOT EXISTS in same query. Below mentioned query1 is working fine, but it returns all the t1 data which does not belong to company1. So I tried query2, but no luck. How can I get the desired result?

1.

select * from t1 where not exists
(select * from t2 inner join t3 on(t3._id = t2.department_id)where t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
 ORDER BY "ctsName"

2.

select * from t1 where company_id = 1 AND where not exists
(select * from t2 inner join t3 on(t3._id = t2.department_id)where t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
 ORDER BY "ctsName"

WHERE is a separator, not an operation , the bit after WHERE describes the rows you want to see in the result.

To apply two conditons use AND or OR to combine them.

select * from t1
where not exists (select * from t2 inner join t3 on(t3._id = t2.department_id)
  AND  t2.category_id = t1._id AND t3._id = 27 AND t3.company_id = 1)
ORDER BY "ctsName"


I'm getting a PL/SQL: unhandled user-defined exception error but there's no error We have a .net application that suddenly started hanging. The log indicates the error is an unhandled user-defined exception and gives a line number. We can't find any error in the function, yet it hangs indefinitely. We have had to kill the process in the past but we don't think we killed this one. Trace log shows:

errors in file
/u01/app/oracle/diag/rdbms/ccccc/ccccc/trace/prdccccc_ora_22685.trc
(incident=52744):
ORA-00603: ORACLE server session terminated by fatal error
ORA-24557: error 6510 encountered while handling error 28; exiting server process
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "FUNCTIONNAME", line 13
ORA-06512: at line 1
ORA-00028: your session has been killed
Incident details in: /u01/app/oracle/diag/rdbms/ccccc/ccccc/incident/incdir_52744/prdccccc_ora_22685_i52744.trc
Fri Oct 14 17:30:41 2016
Dumping diagnostic data in directory=[cdmp_20161014173041], requested by (instance=1, osid=22685), summary=[incident=52744].
Fri Oct 14 17:30:41 2016
opiodr aborting process unknown ospid (22685) as a result of ORA-603
Fri Oct 14 17:30:41 2016
Sweep [inc][52744]: completed
Sweep [inc2][52744]: completed
Fri Oct 14 17:43:05 2016
Memory Notification: Library Cache Object loaded into SGA
Heap size 51200K exceeds notification threshold (51200K)
Details in trace file /u01/app/oracle/diag/rdbms/ccccc/ccccc/trace/prdcrccf_ora_27648.trc
Fri Oct 14 17:43:05 2016
KGL object name :2387355888921555146 DEFAULT

This is the function containing the "error"

CREATE OR REPLACE
FUNCTION
  fnGetCarriersForGroup(pGroupID IN INTEGER DEFAULT NULL)
  RETURN IDsTable
  PIPELINED
  AS
             lRow IDs;
             CURSOR cCarriers IS SELECT * FROM vwCarrierGroups
                                      START WITH ID=NVL(pGroupID,0)
                                      CONNECT BY NOCYCLE PRIOR ID = ParentID;
  BEGIN
             lRow := IDs(NULL, NULL, NULL, NULL);

             FOR cRow IN cCarriers LOOP
                         IF (cRow.EntityID IS NOT NULL) THEN
                                      lRow.ID := cRow.ID;
                                      lRow.GroupName := cRow.GroupName;
                                      lRow.EntityID := cRow.EntityID;
                                      lRow.ParentID := cRow.ParentID;

                                      PIPE ROW(lRow);
                         END IF;
             END LOOP;

RETURN;
END;

This turned out to be a false lead -- there was not an error at all, but rather it had been killed by the developer after it hung. I wonder why it indicates "Line 13" in every case where the error occurs.


"Could NOT find Curses" error when installing MySQL from source I am installing MySQL Server from the source distribution which I downloaded from:

  http://dev.mysql.com/downloads/mysql/#downloads.

I have downloaded the file (mysql-5.5.19.tar.gz).

I am using Debian/Ubuntu Machine. I am following the instructions as given in MySQL Documentation.

After issuing cmake . at the shell I am getting the following error:

-- MySQL 5.5.19
-- Could NOT find Curses  (missing:  CURSES_LIBRARY CURSES_INCLUDE_PATH)
CMake Error at cmake/readline.cmake:83 (MESSAGE):
  Curses library not found.  Please install appropriate package,

      remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
  cmake/readline.cmake:127 (FIND_CURSES)
  cmake/readline.cmake:217 (MYSQL_USE_BUNDLED_LIBEDIT)
  CMakeLists.txt:257 (MYSQL_CHECK_READLINE)

-- Configuring incomplete, errors occurred!

Why am I getting this error?

The clue is in the error message (which I also encountered today):

install the correct Curses library. If you're on Debian/Ubuntu then install libncurses5-dev ( use sudo apt-get install libncurses5-dev )
then remove CMakeCache.txt and rerun cmake. i.e. just do rm -f CMakeCache.txt.
Re-run cmake .


Flashcards and templates I'm having trouble with an appropriate DB design for flashcards that display their data with one of numerous templates.

A user has many flashcards. They pick the card template during creation. There are only 4 templates, but up to infinity in the future.

My current DB design is as follows (all id fields are indexed with a foreign key - but not all will be filled on every flashcard):

create_table "flashcards", force: :cascade do |t|
  t.datetime "created_at"
  t.datetime "updated_at"
  t.text "fields_n_data"
  t.integer 'template_type'
  t.integer 'word_id'
  t.integer 'example_sentence_id'
  t.integer 'meaning_id'
  t.integer 'grammar_id'
  t.integer 'image_id'
  t.integer 'user_id'
end

Some use user-inputted data, some use data from our DB. Some have just 2 fields (front, back), while others have numerous (up to infinity in theory). Some pertain to a combo (or none) of: 1) a word, 2) a meaning, 3) a grammar point (from which information can be auto-filled).

fields_n_data stores all manually inputted information in an array of key/value pairs, where each key is the name of the field, and each value is the data to be filled in that field.

When time to display, I retrieve the appropriate template, iterate through the display fields - for each, I first check if the foreign key object is present, and if so I retrieve and fill using that info; if not, I go to the fields_n_data to get the value of the appropriate key.

This is working, but feels quite wrong, is a pain in the butt, and won't allow a user to create their own template in the future. Is there a better way?

Your app requires an an infinite amount of ways to store an infinite amount of arbitrary data. At that point you're critically limiting what you can do with a schema. You may as well just store the environment, and an array of templates.

CREATE TABLE flashcards (
  id          serial  PRIMARY KEY,
  environment jsonb,
  templates   jsonb   CHECK ( jsonb_typeof(templates) = 'ARRAY' )
);

Now, in the client (browser/javascript), you call (using pugs but you could also use Handlebars or even compile in into an Angular Component)

pug.compile(templates[a])(environment)

Using this kind of schema you can meet the requirements of your application in storing and rendering flashcards, but I have no idea what kind of data querying you'll be doing on them.


Use which schema to query V$OSSTAT I need to query the values of V$OSSTAT and am able to do so using tools like dbVisualizer. But when I take the identical query and put it into some Javacode I fail to find the table.

I had this problem before with specific application schemas and was able to solve this by putting the applications schema name in front of the table simiar to this: SELECT NAME FROM FOOAPP.USERS.

But in this case I simply don't know which schema to use for gaining access to V$OSSTAT. I tried prefixing the view / table with SYS and SYSTEM already.

SELECT STAT_NAME, VALUE, COMMENTS FROM SYS.V$OSSTAT
SELECT STAT_NAME, VALUE, COMMENTS FROM SYSTEM.V$OSSTAT

There is no such object as SYS.V$OSSTAT, because V$OSSTAT is a public synonym.
Because of this, there is no need to use a schema specifier for it.
The actual view is called V_$OSSTAT (same for other V$ views as well).
If you want a user to be able to select from that view, grant the privilege on that:

grant select on sys.v_$osstat to user;


How can I insert '&' symbol in data I have a source table where one of column has data like:

Column
SD
SD
..
..
JK
Jk
..
..

I want to load this data into staging in format of:

Column
S&D
S&D
..
..
J&K
J&k
..
..

Meaning I want to insert '&' character wherever we have data like SD and JK, while loading to staging.

I need to convert it for SSIS. So I want to give here SSIS formula to achieve this.

How can we achieve this?

Assuming your source column is called col, I would add a Derived Column Transformation. Use the substring and right function to split the column apart and insert the ampersand in the middle.

SUBSTRING(col,1,1) + "&" + RIGHT(col,1)