'SELECT my_table.id' slower than 'SELECT my_table.*' Running MySQL with InnoDB:

I have a SELECT wide_table.* query that I want to refine to SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time with * is faster than with id (though the time to transfer the result over the network is faster with the "refined" version).

Why would this be the case?

The query (with names changed to protect the innocent) is:

SELECT
    `things`.*
FROM
    `things`
WHERE
    `things`.`active` = 1
        AND (owner_id IS NOT NULL
        AND owner_id > 0)
        AND ((`things`.`status` IN (0 , 1)
        OR `things`.`status` IS NULL))
        AND (date < '2015-07-11 00:00:00');

There's a compound index on active and date, which is being used in both versions.

For reference, the ouput of SHOW CREATE TABLE (with irrelevant columns omitted):

CREATE TABLE `things` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `active` tinyint(1) DEFAULT '0',
  `date` datetime DEFAULT NULL,
  `owner_id` int(11) DEFAULT '0',
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_things_on_active_and_date` (`active`,`date`),
  KEY `index_things_on_date` (`date`),
  KEY `index_things_on_owner_id` (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Limits

After playing around with it for a while, I've found that the comparison is affected by the limit imposed on the query. With large limits, >200, the * version reports a faster execution time. As the limit is decreased <200, the id version gains the upper-hand. Still not sure what to make of this...

EXPLAIN

Running EXPLAIN on both versions of the query yields identical output, with select_type: SIMPLE and key: index_things_on_active_and_date.

The EXPLAIN plan for SELECT * and SELECT id, which you said were identical, would be separated by the number of rows accessed. How are the rows being accessed ? Through the index_things_on_active_and_date index.  The SIMPLE in the EXPLAIN means it is a scan. In both cases, it is an index scan based on active=1 and date < '2015-07-11 00:00:00'

How does the index scan occur ?

Since the chosen index has active and date, the range scan would occur from two columns.
Look at the WHERE clause. You are retrieving owner_id and status and checking the values. This requires you accessing the whole row.

SELECT * means you make the whole row part of the result set
SELECT id means you make the id part of the result set

Where is this leading up to ?

In both cases, you are reading an entire row to check two additional columns not in the index.
The result set of each query is either an entire row or single column (id).
It must take some additional time to create a smaller result set. Why ? There comes a point when a longer result of id values would take longer the build that going with the whole row you read anyway. You yourself empirically tested that and discovered the following:

< 200 rows -> SELECT id is faster
> 200 rows -> SELECT * is faster
= 200 rows -> SELECT id and SELECT * are about the same

There is something else you need to realize about InnoDB

InnoDB stores data for a row is stored along with the index pages making up the PRIMARY KEY. This is known as the clustered index (gen_clust_index)
Non-unique indexes will store the primary key fields of row along with the index entries.

This tells you that the index index_things_on_active_and_date actually has three columns: 1) active, 2) date, 3) id.

You are probably saying: Why is SELECT * running better that SELECT id? (which is the original question) It goes back to what I said: the WHERE clause is causing the Query Optimizer to check non-indexed columns status and owner_id. You are creating additional work check an index entry and something from the row that is indexed.

If you create this index

ALTER TABLE things
    ADD INDEX index_everything_and_kitchen_sink
    (active,date,owner_id,status)
;

and run the two queries, then you will see the advantage go to SELECT id no matter how many rows you are accessing. Why ? Because all the columns in the WHERE clause are checked from the index only. This type of index is called a covering index.

Here are some good links about covering indexes

http://peter-zaitsev.livejournal.com/6949.html
http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/

I mentioned these links in some of my answers:

Feb 10, 2012 Unexpected extremely long query time (~5 minutes using nested WHEN-INs)
Oct 17, 2012 : Combining columns in index
Jan 11, 2013 : MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)


How do I migrate data from a SQL Server DB to MongoDB? I wish to migrate a large SQL Server Database (having a million tables) to MongoDB. I am not able to understand how to convert the complex Joins, indexes and keys in SQL Server to MongoDB which has over a million tables and huge data?

Joins you either pre-compute and store the denormalised data in the document. Or the application implements a join algorithm for runtime.

Indexes can be defined for whichever values inside the documents you'd like to have them. The documentation adequately  describes the options. As currently, they help reads but have a write overhead.

Keys: there are no keys. Each row has a unique identifier which you can assign or let the system generate a value. This would correspond to a surrogate primary key. Foreign keys, uniqueness, check constraints, assertions etc. are the appapplication's responsibility.


Postgresql table size growth after adding column I have added boolean type column to table with roughly 100 million rows.
Afterwards I have manually executed UPDATE statements to set the new column value to true/false depending on other column values.
UPDATES were done overnight (took a few hours).

Then I set the column to NOT NULL.

After all these steps table size increased almost twice. ~40GB -> 68GB.

I have used following query for updating in chunks (To not lock the table completely):

WITH cte AS (
    SELECT id from results_storage  WHERE condition < 100   AND newboolean IS NULL  limit 10000
    FOR    UPDATE SKIP LOCKED )

UPDATE results_storage r SET       newboolean = FALSE FROM   cte WHERE  r.id = cte.id RETURNING r.id;

What could be the cause?
Is there any way to reduce the space taken?
I have expected single boolean column to take very little extra space.
(+~100MB for each 100M rows as boolean should take 1 byte storage).

I suppose now tuples did not fit any free space on pages, so each row had to relocate. Assuming most if not all new rows only grown in size, around half rows needed new location, and so table is taking 50% more on disk. Why half of rows?.. Because space released by two old tuples is enough for one new tuple only. I think tuple tetris did not help here, because rows were moved not two closest together, but randomly, so new tuples could not effectively use space released...

If this assumption is correct, the space your table takes is half empty. Alas it is reserved for future operations. You need vacuum full table_name to basically rebuild it to return space... Or just work with current size, day after day (or even year after year ) the size will drop a bit if you keep on updating, deleting...


ORA-02270: no matching unique or primary key for this column-list I getting an error report of:

Error report -
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Can I know why?

parent table

    CREATE TABLE STUDENTINFO
    (
      Student_ID VARCHAR2 (10) PRIMARY KEY,
      Full_Name VARCHAR2 (50) NOT NULL,
      Contact_Number NUMBER (15)NOT NULL,
      Address VARCHAR2 (50) NOT NULL,
      Nationality VARCHAR2 (15) NOT NULL,
      IC_PassportNo VARCHAR2 (15) NOT NULL,
      Programme VARCHAR (75) NOT NULL,
      Email_Address VARCHAR2 (50) NOT NULL REFERENCES                    USERNAMEPASSWORD(Username),
      Parents_Number NUMBER (15)NOT NULL,
      Fingerprint_Template clob
    );

child table

    create table bit_2015_sep_cit4114_fyp_G_
    (
      Student_ID VARCHAR2 (10) PRIMARY KEY REFERENCES STUDENTINFO(Student_ID),
      Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name),
      Nationality VARCHAR2 (15) NOT NULL REFERENCES STUDENTINFO(Nationality),
      Fingerprint_Template CLOB NOT NULL REFERENCES        STUDENTINFO(Fingerprint_Template),
     "23/10/2015" VARCHAR2 (15) not null,
   );

I keep nationality and fingerprint as duplicate because it will be time consuming in verifying based on all information stored in studentinfo, therefore by breaking into individual class will be easier and faster. like for the table STUDENTINFO consist 1 million record, in table bit_2015_sep_cit4114_fyp_G_ will only have 40 records. I keep the nationality column because I have 1 more column in my table structure which is visa renewal which calculated based on the value of nationality in the table.

Fullname etc. are not declared as unique in the parent table, and therefore you can't reference them from the child table.

Why do you need to duplicate these columns in the child table?

EDIT:

Locking at you child table you say that:

create table bit_2015_sep_cit4114_fyp_G_
( ...
, Full_Name VARCHAR2 (50) NOT NULL
      REFERENCES STUDENTINFO(Full_Name)

What this means is that there must be a unique row in STUDENTINFO with this fullname. I.e. you need to declare that table:

CREATE TABLE STUDENTINFO
( ...
, Full_Name VARCHAR2 (50) NOT NULL
     UNIQUE,

I doubt that full_name is unique which means you cant declare a foreign key against this column.

You seem to assume that there will be performance problems later on and therefore you de-normalize your database. IMO this is a big mistake, start with a normalized database and de-normalize only when there is reason to do so.

If you insist on de-normalizing you can use a trick like:

CREATE TABLE STUDENTINFO
( Student_ID VARCHAR2 (10) PRIMARY KEY
, Full_Name VARCHAR2 (50) NOT NULL
, Contact_Number NUMBER (15)NOT NULL
, Address VARCHAR2 (50) NOT NULL
, Nationality VARCHAR2 (15) NOT NULL
...
,    constraint AK1_STUDENTINFO unique (Student_ID, Full_Name, Nationality)

Since Student_ID is unique it follows that Student_ID, Full_Name, Nationality must also be unique. I excluded Fingerprint_Template since I doubt that this can be used in foreign keys (not sure though, if it can you can add it)

create table bit_2015_sep_cit4114_fyp_G_
( Student_ID VARCHAR2 (10) PRIMARY KEY
, Full_Name VARCHAR2 (50) NOT NULL
, Nationality VARCHAR2 (15) NOT NULL
...
,    constraint fk_studentinfo foreign key (Student_ID, Full_Name, Nationality)
                references studentinfo (Student_ID, Full_Name, Nationality)

);

But as mentioned, start with a normalized design and see if that works


SQL Server cluster not available after failure of the "main" server I aksed this question on stackoverflow, but was asked to repost here so here it is:

I have a database that I'm setting up in a clustered environment. It'll be two servers with a shared storage. ServerA is the server where I setup the database, and ServerB is a node in the SQLServer cluster.

If I forcibly turn off ServerB everything keeps on working as expected, but if I turn off ServerA the instance is not available anymore.

Windows cluster is still alive (I can remote desktop onto the cluster via shared name), but the SQL is not visible, and cluster manager reports the resource as offline.

This kinda defeats the purpose of the cluster and I need to solve it, but it's not really in my area of expertise, and I have no idea where to start.

Thanks in advance.

EDIT (Additional info asked in comments): Windows was installed I added clustering feature for DTC and SQL Server. As for access, IP addresses of the machines are *.101 for main; *.102 for second server; *.99 for access point SQL server instances and *.111 for cluster manager. I can access 99 from both machines but can't access database at 101 or 102 from either.

EDIT #2 -  I tried increasing the failover threshold (to 50 in 24 hours, for out testing purposes), but the server still doesn't wake up after cutting off the network for server1. The cluster comes back in about 20 sec or so, but SQL Server stays down. In the logs I have serveral errors and one Critical level message that is probably causing the fail. Message is:

  The Cluster service is shutting down because quorum was lost. This
  could be due to the loss of network connectivity between some or all
  nodes in the cluster, or a failover of the witness disk.  Run the
  Validate a Configuration wizard to check your network configuration.
  If the condition persists, check for hardware or software errors
  related to the network adapter. Also check for failures in any other
  network components to which the node is connected such as hubs,
  switches, or bridges.

Without looking at the cluster logs or any other form of error reporting, all I can do is guess here.

But my initial thoughts are that you may have hit the failover threshold.  By default, this is going to be set to a maximum of n - 1 failures (where n is the number of nodes) over a period of 6 hours.  Yes, that's a long time, and especially in a 2-node cluster that isn't very many failures (only equates to one failure).  This threshold is set to prevent the Ping-Pong effect of cluster groups.

In production, this is probably a good thing.  But in testing/development/non-prod it is pretty common to run into this initially perplexing problem, as you may be trying to consecutively failover.  It is worth noting that these parameters are 100% configurable.  All you need to do is go into the properties of the cluster group, and in the "failover" tab you will have the option to change these two parameters (Maximum failures in the specified period and Period (hours).  Here is what this looks like in the Failover Cluster Manager:

Note: in my screenshot, the threshold is set to 2 because I have a 3-node cluster.

Likewise, this can be seen with PowerShell (accessing the FailoverClusters module).

# you may need to set your cluster group name to whatever it is named
# in your environment
#
Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)" |
    Select-Object Name, FailoverThreshold, FailoverPeriod


SQL Table Design Schema - To Normalize or Not? I am considering a table schema design for a project.
The design involves a Many-to-Many Relationship.

The 2 related tables are:

a) Class

b) Subclass

A Class can have many Subclasses, and a Subclass can have many Classes.

Here is my Normalized design:

Is it best to always do this design? The relationship will have 20 rows max. Is it then better to create a single denormalized table as it will then be much easier to update and delete data?

In the Normalized design I would have to create a View to join the tables to display data.
And also to create sprocs or triggers to update the base tables.

The denormalized design involves updating/deleting/selecting from a single table.

Denormalized design:

Is it always best practice to use normalized design?

This looks like a school project correct?  I think the confusion is coming in because your example is very simple (perfectly appropriate for a class/training environment).  The design you have is considered best practice.  The reason it might seem simpler to go with the denormalized design is because you have so little data/so few columns.

If you add a dozen columns each to the Class and Subclass tables it starts to look worse.  Then if you add a million rows it gets even worse. When you design tables you can't always know in advance how big they will get and generally they will be wider than in your example.

Another reason why it's best practice is because you are logically separating your data.  What if you need to tie/use subclass without the class table?  Or class has an additional purpose.  If they are all in a single table you can't break them up like this.

Think of the same structure using School & ClassName.  The School table could be used for any number of other purposes (to tie employees to for example).  Same with the ClassName (list of required classes for a degree) table and even the join table (students taking the class at a given school).  But if you have a single wide table you wouldn't be able to use them that way.


Get tablespace name from dump file I have an Oracle dump file which I need to import but do not know what is its tablespace name. How can I get it from the dump file? I know that it is possible to get many information using impdp MASTER_ONLY flag but can't see there any information about dump's tablespace.

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql


MySQL c++ connector: what is the correct way to use SetBlob() to set multiple blob data in a query? I am using the MySQL c++ connector version 5.x
The table creation statement is like:

CREATE TABLE files_table (fid INT NOT NULL AUTO_INCREMENT, idx TINYBLOB, head TINYBLOB, size BLOB, PRIMARY KEY(fid));

I could insert record manually

mysql> select * from files_table order by fid asc;
+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
+-----+------+------+------------+

But when I use the connector to add the next value

class StreamBufferData : public std::streambuf
{
public:
    StreamBufferData(const char *in_data, size_t in_size)
    {
        setg(in_data, in_data, in_data + in_size);
    }
};

enum{QUEry_SIZE=256;}
char ins[QUERY_SIZE];
memset(ins, 0, QUERY_SIZE);

strcpy(ins, "INSERT INTO files_table (idx, head, size) VALUES (?,?,?)");

try
{
    std::unique_ptr<sql::PreparedStatement> statement(ptr_connection->prepareStatement(ins));

    char test_data[2] = "0";
    StreamBufferData buffer0(test_data, 2);
    std::istream test_s0(&buffer0);
    statement->setBlob(1, &test_s0);

    strcpy(test_data, "1");
    StreamBufferData buffer1(test_data, 2);
    std::istream test_s1(&buffer1);
    statement->setBlob(2, &test_s1);

    strcpy(test_data, "2");
    StreamBufferData buffer2(test_data, 2);
    std::istream test_s2(&buffer2);
    statement->setBlob(3, &test_s2);

    statement->executeUpdate();
}
catch(sql::SQLException &e)
{
    std::cout << e.what() << ‘\n’;
    return;
}

The result is :

+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
|   2 | 2    | 2    | 2          |
+-----+------+------+------------+

Only the last value is correctly inserted into the table. My question is: what is the proper way to user msql::PreparedStatement::setBlob() to set multiple blobs in a query?

[environment]
Ubuntu 16.04.2
MySQL 5.7
MySQL connector version: 7.1.1.7
boost 1.58.0
g++ 5.4.0

Thanks for your help

Rong

The different std::istream objects point to the same buffer which allocated by test_data. I thought the istream object will copy the value in the char array. The fact is, it is not.

It also worths to point out that if the std::istream object is destroyed before the statement->executeUpdate() executed. The last stream object will be filled into the first column of the table, which seems to be an undefined behavior on my machine (Ubuntu 16.04.3 LTS). It looks like:

+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
|   2 | 2    |      |            |
+-----+------+------+------------+

So, if the table has strange results like the previous table, check the code to make sure the std::stream, the StreamBufferData and the pointer pointing to the buffer are still valid until statement->executeUpdate() executed.


Why does psql open two tcp connections to PostgreSQL? If I for instance place a iproute, like this

sudo iptables -I INPUT -p tcp --syn -m conntrack --ctstate NEW --ctstatus CONFIRMED --dport 5433 -j LOG

I can see that there are two entries every time I try to connect to my database.

[598519.458578] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC=192.168.0.101 DST=192.168.0.101 LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=49795 DF PROTO=TCP SPT=58554 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0
[598520.875428] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC=192.168.0.101 DST=192.168.0.101 LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=65392 DF PROTO=TCP SPT=58560 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0

Why does psql need two connections to PostgreSQL Server on two separate ports (58554 and 58560)? That is to say, I get two messages like above when I run this once..

psql -d test -h 192.168.0.101 -p 5433

This double connection depends on whether a password has to be submitted by the client (according to pg_hba.conf) and, if yes, whether the -W option is passed to psql.

In fact avoiding that second connection attempt is the only raison d'être of this option. According to psql manpage:

  -W
  --password

  Force psql to prompt for a password before connecting to a database.

  This option is never essential, since psql will automatically prompt for a password if the server demands password authentication.
  However, psql will waste a connection attempt finding out that the
  server wants a password. In some cases it is worth typing -W to avoid
  the extra connection attempt.

  Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial
  connection attempt.

I think that with this log from iptables, you're just noticing the mentioned "wasted connection attempt".
Note the time elapsed between the two connections:
[598519.458578] and [598520.875428]. Presumably this is the time it took you to enter the password: about 1.42 seconds.

If you try the same test with -W it should ask the password before attempting the connection and make only one connection.


SSMS 2014 (and 2012) CTRL+TAB requires pressing enter Sometime last week my CTRL+TAB behavior changed.  I used to hit CTRL+TAB, release, then I would be in my previous document tab.  Now I hit CTRL+TAB, release, and the nav window is still open requiring me to hit enter.

I have tried restarting ssms and rebooting several times.  This is an issue in both SSMS 2012 and 2014.  It is not an issue in Visual Studio (2010 and 2013).

In all VS 2010/2013 and SSMS 2012/2014 the hotkey Ctrl+Tab (Global) is set to Window.NextDocumentWindowNav.  Though I have tried modifying it then putting it back.

CTRL+F6 works the same as it always has.

Is there a way to get the CTRL+TAB behavior back?

To fix this issue: Tools \ Options \ Keyboard.  Bring up the command in the "Show commands containing:" list.  Then in the "Press shortcut keys:" text box press CTRL+Tab and click Assign.  This will re-assign the command back to itself, but fixed the "sticky" window.  Just had the same issue. Hope this helps.


Check if the trigger was fired by INSERT or DELETE I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet.
How to check if the trigger was fired by an INSERT or DELETE?
Example
CREATE TRIGGER [dbo].[TestTrigger]
On [dbo].[Table1]
AFTER INSERT, DELETE
AS
    --if save
    --do some work

    --else if delete
    --do some work other work

I want to do some work if it's an insert and some other work if it is a delete.
Please provide a code snippet for the same.

As long as the trigger is only for insert/delete:

IF EXISTS (SELECT 1 FROM inserted)
   -- I am an insert
ELSE
   -- I am a delete

And in fact if you are doing things based on a join to inserted/deleted, it can be even simpler:

-- do something for an insert
-- this will only actually do anything when it's an insert
INSERT dbo.somewhereElse SELECT whatever, 'insert'
  FROM dbo.sourceTable AS s
  INNER JOIN inserted AS i ON s.id = i.id;

-- do something for a delete
-- this will only actually do anything when it's a delete
INSERT dbo.somewhereElse SELECT whatever, 'delete'
  FROM dbo.sourceTable AS s
  INNER JOIN deleted AS d ON s.id = d.id;

If you add the update action to the trigger, the logic gets more complex, of course:

IF EXISTS (SELECT 1 FROM inserted)
BEGIN
  IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    -- I am an update
  END
  ELSE
  BEGIN
    -- I am an insert
  END
END
ELSE
BEGIN
  -- I am a delete
END


Rowcount issue in sql server I'm trying to get the rowcount for each table by using the query below:

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
where   [Tables].name   like '%ixf_%'
     or [Tables].name   like '%itx_%'
     or [Tables].name   like '%wk_%'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
ORDER BY [TotalRowCount] desc ,[TableName];

The problem cames up when I run it in a particular enviroment, i'm getting no rows, but when I tried in another server, I'm able to retrieve rows.

I have this query also to match the collate in case of any collation issue:

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default = b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

And no rows are returned either.

Some things to check (for future readers, since we now know what happened here), off the cuff:

Case sensitivity: on a case sensitive instance, the metadata needs to be an exact match; TableName <> TABLENAME <> tablename.
Collation: in some cases you may have names in your objects that were created with a different collation than you're using (and in general it is just a good practice to always explicitly define parameters involving metadata as nvarchar and prefix all string literals with N, since metadata is always stored as nvarchar).
Correct database/server: on the new server, when there are no results, it could mean that you've left the database context set to master, or tempdb, or your default database, or the database of the most recent query window before this one. Can't hurt to double-check all connection string settings... several times the solution ends up being the person is connected to the right database, but on the QA or staging server.
Correct schema: when you use a name like TableName with certain functions, procedures, or DMVs, it may look in the wrong schema or even return the wrong object from a different schema. You may also not have correct permissions on the right schema. Always explicitly specify the schema (even when it's dbo) and, if that doesn't help, get someone with sysadmin to try your queries.
Permissions: for many metadata queries, you need more than SELECT rights, you also need VIEW DEFINITION. Again, have someone with sysadmin try your queries and see if they get different results. If they do, you're on the right track!


how to find which user performed DML operations in a table in oracle? how to find which user performed DML operations in a table in oracle? Need a query to find out from DB?

The answer is auditing.

Monitoring Database Activity with Auditing

Traditional auditing:

audit all on owner.mytable by access;

Unified auditing:

create audit policy mypolicy actions all on owner.mytable;
audit policy mypolicy;

If audit_trail is set to DB or DB,EXTENDED, results can be queried from DBA_AUDIT_TRAIL in case of traditional auditing.
With Unified auditing, results can be queried from UNIFIED_AUDIT_TRAIL.


MySql Multi Master Active Active Mutli Write I am establishing Multi Master Replication [All mater can write to their database], Initially I started with two server. After establishing connection and sync data on both servers. There are some questions which are bothering me like

If there is connection lost between two server and at that time both updates the same row or create a row with same primary. There will be collision between them and sync will break.
Can we implement same things for Multiple Master configuration?
Is there any monitoring and conflict resolution tools which can I use?

1 & 2)
You should look at [auto_increment_increment] and auto_increment_offset variables.
This prevents the two masters from creating the same Primary Key (assuming you are using Auto Increment Values).

3)
Not Natively, for this you would need to look at moving to one of the Galera Cluster variants.

As it stands I wouldn't recommend an Active Active environment because you will almost certainly run into problems when the network drops out / replication stops on one or both sides.


Combining retail sales and payment OLAP cubes I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions:

List down all products that were paid with certain type of payment (which products are bought with voucher type X, etc)
Create a breakdown of frequency of payment type used to pay for product X (how many times cash is used to pay for product X, how many times debit, credit, voucher, etc)

There are 3 main transaction tables: SALESHEADERS, SALESDETAILS, and SALESPAYMENTS.

SALESHEADERS contains sales date, cashier, POS code, etc.
SALESDETAILS contains header code, product code, quantity, unit price, gross, discount, etc.
SALESPAYMENTS contains header code, payment type code, payment amount, etc.

One transaction can contain many products. One transaction can be paid with multiple types of payment. There is no connections between detail and payment except through header (sales ID).

Currently I have created 2 cubes, one sales cube and one payment cube.

Sales cube is created by joining the header and detail. Payment cube is created by joining the header and payment.

Both cubes are performing well, but they can't answer the questions at the top of my post.

You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.


Non-deterministic aggregated result Why is my query non-deterministic?

I have query that do the following:

select sum(float1*float2*coalesce(float3,1)) from table

When I run this query I get a deterministic result, but it seems not be the case when the query is run many times.

The last integer in the result differ between different runs.

All columns are (float,null)

Should a float truncation be the same for all runs?

Multiplying approximate values will multiply errors: sometimes they will cancel but usually they will exaggerate

float is also accurate to 15 significant figures at most: as you multiply and SUM you can quickly hit this and lose accuracy.

I guess that the changes in results will depend on how what order the data is accessed (changes with eg new rows, row overflow, page splits etc) so the series of calculations involved will change order each with their own rounding.

Edit: had a thought.

Each core/socket will do float calculations differently.

Try these to see if we can make this deterministic (note this isn't the same as "correct")

Add MAXDOP 1 to the query and see how many different results you get. I'm expecting no more then the number of cores or sockets. This reduces the number of permutations with parallelism

Then try processor affinity to always use the same core for all queries to force the query to run on one core/cpu

To fix it, use decimal or bigint.


ODBC Data Source SQL Server Connection - Login Failed For User I have moved my database from an SQL 2005 to a server with SQL 2008.

I am now trying to create an ODBC Data Source.

I am using "With SQL Server authentication using a login ID and password entered by the user" and have entered my Login and password.  The Login is visible in SQL Server Management Studio under Security-> Logins.

The login also contains the exact properties of the same login in my old server.  When I hit next after entering the username and password I get:

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myUser'.

After reading this: http://support.microsoft.com/kb/555332 I realized that the server properties were set to "Windows Authentication Mode" only, but even after changing to "SQL Server and Windows Authentication Mode" I am still having problems connecting.  I can connect with my Windows account though.

My answer... From my comments:

The issue was that the server was set to "Windows Authentication Mode" only. To fix this I

Right click the server - > Properties
Click "Security" in the left side of the "Server Properties" dialog
Changed server Authentication to "SQL Server and Windows Authentication mode"
Clicked "OK"
Restarted Associated services. At first I forgot to restart the services, so I was still getting the error, but now I am able to connect without an issue. This was very helpful: http://support.microsoft.com/kb/555332


How to resolve "the specified account already exists" error during SQL Server 2008 R2 installation I recently had a problem while installing Visual Studio 2012 and SQL Server 2008 R2 on a new development machine. My installation sequence was Visual Studio 2012 then install SQL Server 2008 R2.

An error occurred during the middle of SQL Server installation:

  The specified account already exists.

I have spent my day trying to fix this problem.

First, search the installation log. In my instance it was under the following path:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\*.*

In that log I found the following entries:

2015-08-07 11:16:50 Slp: Sco: Attempting to open registry subkey
2015-08-07 11:16:50 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\VisualStudio\9.0
2015-08-07 11:16:50 Slp: Sco: Attempting to get registry value InstallDir
2015-08-07 11:16:50 Slp: Target package: "D:\shared\~~~~~\sqlncli.msi"
2015-08-07 11:16:51 Slp: MSI Error: 1316 The specified account already exists.

I then located the file sqlncli.msi and opened a command prompt to execute this command:

msiexec /i "d:\[Your sqlncli.msi path]

I then uninstalled the SQL Server Native Client and ran the installation for SQL Server again, success.


Aggressive Autovacuum on PostgreSQL I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:

autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
autovacuum_vacuum_cost_limit = 10000 #Max value
autovacuum_vacuum_threshold = 50 #Default value
autovacuum_vacuum_scale_factor = 0.2 #Default value

I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane!

Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load.

Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load?

Update

Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.

Eelke is almost certainly correct that your locking is blocking autovacuum.  Autovacuum is designed to give way to user activity, deliberately.  If those tables are locked, autovacuum cannot vacuum them.

For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it.  Note that making autovacuum more aggressive is unlikely to solve your problem, however.  Also note that the default autovacuum settings are based on running over 200 test runs using DBT2 seeking an optimal combination of settings, so the defaults should be assumed to be good unless you have a solid reason to think otherwise, or unless your database is significantly outside the mainstream for OLTP databases (e.g. a tiny database which gets 10K updates per second, or a 3TB data warehouse).

First, turn on logging so you can check up on whether autovacuum is doing what you think it is:

log_autovacuum_min_duration = 0

Then let's make more autovac workers and have them check tables more often:

autovacuum_max_workers = 6
autovacuum_naptime = 15s

Let's lower the thresholds for auto-vacuum and auto-analyze to trigger sooner:

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05

Then let's make autovacuum less interruptable, so it completes faster, but at the cost of having a greater impact on concurrent user activity:

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

There's your full program for generically aggressive autovacuum, which might be apppropriate for a small database getting a very high rate of updates, but might have too great of an impact on concurrent user activity.

Also, note that autovacuum parameters can be adjusted per table, which is almost always a better answer for needing to adjust autovacuum's behavior.

Again, though, it's unlikely to address your real problem.


What known bugs still exist in SQL Server 2005 after SP4 CU4? I'm assuming CU4 will be the last update for SQL 2005.  We intend to stay on 2005 for awhile.  Does anyone know (or perhaps a list is maintained) which bugs STILL exist on 2005?  I assume these would mainly be bugs that have been fixed in 2008 and 2012, but still exist in 2005, even after applying SP4 CU4.

Seems like we should track this if we are staying on 2005...

Microsoft doesn't really keep a public list of open bugs. The ones that are the most important (e.g. anything involving security) are kept private for obvious reasons - they actually aren't published until they are fixed (and even then they often remain private). Not that there have been many security issues with SQL Server in the past 12 years, but by necessity, we don't know what we don't know.

And to be honest keeping such a list out in the open would be like BMW keeping a bulletin board with all the problems that still exist on their 2006 M3. It's kind of like a dirty laundry list, and other than satisfying customers who refuse to upgrade to more modern (and arguably "more fixed") versions, what would it do for them? :-)

Now, you could go out to http://connect.microsoft.com/sql/ and peruse the list of bugs opened against SQL Server 2005 that are still open. But that wouldn't be the whole story, since many bugs were opened against 2005, then closed as fixed when the fix was actually implemented in a more modern version. Never mind the large number that were closed as by design or not reproducible but that still affect you to this day.

You could also go to the knowledge base http://support.microsoft.com/kb and try to find all of the issues listed there that haven't been updated to include the text "this issue has been fixed in [such and such update]." This would also be a Herculean task that I would not wish against my worst enemies.

In the end, if you are not affected by bugs you do not know about, they may as well not exist, right? And if you are continuing active development against SQL Server 2005 and you come across an issue, it is highly, highly unlikely that you will be the first person to come across it. So if you come here for assistance you will stand a very good chance at getting the guidance you need - either a workaround to get around the bug by coding the query a different way, or a trace flag, query hint, or a completely different approach.

I know this isn't the answer you're looking for, but I'm quite afraid you're not going to get the answer you're looking for. This answer (and this site, to be honest) is quite possibly the next best thing. When you have a specific issue, bring it here, and we will help.


Migrating to a new (upgraded) SQL server We have a single SQL server with about 60 databases running on Win2012r2 with SQL 2014.

I am in the process of planning a hardware refresh and plan to migrate to WIN2016 with SQL 2017, I understand the basics of migrating to a new server (copy files over, re-attach, test etc).

My question is, we have specific restricted logins setup for each database.  The logins are windows ad groups and have specific permissions for their allowed databases. In addition to this we have about 20 SQL logins with the same setup against their databases.

My question is, how can i migrate these logins and databases while maintaining the permissions for specific databases?

I would use Copy-DbaLogin from the dbatools powershell module.
This command migrates the logins from server A to server B.
From their website the example:
Copy-DbaLogin -Source sqlserver2014a -Destination sqlcluster -Force

Copies the logins from sqlserver2014a to sqlcluster
and  overwrites them if they exist by using the -Force parameter
What does it migrate?

Migrates logins with SIDs, passwords, defaultdb, server roles &
securables, database permissions & securables, login attributes
(enforce password policy, expiration, etc.)

Same source as the one mentioned above


Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777).

When i tried to open the file mysql.sock, its giving me an error saying the file may be corrupt.

Please help me to log on to the mysql server.

First: Make sure that the owner of /var/lib/mysql and its files and subdirectories is mysql user of mysql group

to do that run the following command:

chown mysql:mysql -R /var/lib/mysql/*

Second: change write permissions to rwxr-xr-x (755) for mysql directory, and all its files and subdirectories, by running the following command:

chmod 755 -R /var/lib/mysql/*

now start mysql service

service mysql start


How to permanently delete a row in oracle forever I want to delete a record from a table and no one should ever be able to recover the deleted data.
I have done delete operation but the record is still in the undo segments. And one can do flashback and retrive that row.

There is likely a more streamlined set of steps, but here is a start.

Delete the data out of any materialized views or home grown
replication methods using triggers or the like.
Delete the data out of any database audit tables.
Change the database to noarchivelog.
Create a new undo tablespace.
Switch log files until all are archived.
Shutdown the database.
Turn off and purge any storage or virtual machine level snapshot capability.
Delete all trace files that may contain the data.
Delete all backups sets including archive log backups that could contain the data.
Delete all OS audit logs that could contain the data.
Visit any computer that may have the data cached or displayed and destroy it there.
Visit any other storage devices the data may have been copied to and purge those.
Find any hard copies of the data and destroy those.
If someone memorized the data, memory erasure is beyond the scope of this site.


How to backup to local drive and network drive simultaneously? I can backup to a local drive and mirror:

BACKUP DATABASE [xxx]
TO  DISK = N'XXXX.bak'
MIRROR TO DISK =N'Network path \XXX.bak'
WITH INIT

--directly to network drive
BACKUP DATABASE [xxx]
TO  DISK = N'Network path \XXX.bak'
WITH INIT

The mirror option is not available in Standard Edition (SQL Server 2012), so what's the best approach to resolve the issue?

As the documentation shows, MIRROR TO is only available in the Enterprise editions of SQL Server 2005+.

If your end goal is only to have a redundant backup for safekeeping in a different location from the local disk, and they don't have to be the same backup, then you could just do them in sequence in a single batch.

BACKUP DATABASE [MGEC_STAGE] TO DISK = N'XXXX.bak' WITH INIT;
BACKUP DATABASE [MGEC_STAGE] TO DISK = N'Network path \XXX.bak' WITH INIT;

If the backups have to be identical, then you will just have to perform the backup to the local drive and copy it, using any of (among other options)

xp_cmdshell and the xcopy/copy command
Windows Task Scheduler sqlcmd command line and xcopy/copy, assuming you (i) are performing the backup using a schedule (e.g. SQL Agent) and (ii) know how long it will take, timing the copy to occur soon after
SSIS package, along the lines of the Windows Task Scheduler


Why do you have to execute a full backup after a new database for trans backup jobs to run Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). Wednesday morning I received SQL agent email noticed stating the backup jobs failed specifically for the new DB. After reviewing the logs I read "The Job was invoked...There is no DB to backup," before the Senior DBA told me to do a manual full backup and then it will run. Why is this?

do a manual full backup and then it will run. Why is this?

This is because any database you create with full recovery model is always in pseudo simple recovery mode until you take a full database backup and this is where a log backup chain can be establish successfully.

You can easily check if a database is in pseudo simple recovery mode using DBCC DBINFO () WITH TABLERESULTS** --> check for field dbi_dbbackupLSN with value of 0:0:0 (0x00000000:00000000:0000). This means that the log chain has not been established.

Paul Randal has a TSQL script and Edwin Sarmiento  has a PowerShell script that will tell you if a database is in pseudo simple recovery or not.

You can use the scripts from above links to first check if the database is in pseudo simple recovery and if the database is then execute a full backup else run diffs or log backups.

** DBCC DBINFO is undocumented.


Data Factory keeps getting "untrusted connection" error when logging into on prem SQL Server via IR on VM We have:

A local SQL Server database
A VM with the Integration Runtime installed on it
A Data Factory sharing this IR, working (according to the ADF interface)
Logins stored in Key Vault for SQL Server auth from data factory to log into the SQL Server via the IR

The connection string properties of the connection appear to be what I want, note encryption is set to off and trustservercertificate=true:

The IR reports as connected and working fine:

I have tested the SQL Server auth on the VM with the IR installed via the IR's Diagnostics tab. Works OK:

From Data Factory, I test using the same login parameters on the linked service connected to the IR, which works:

Note we use a lot of parameters to define the connections, these work fine as well.
I set up a dataset, using the same properties, test connection works:

Now I set up an easy copy activity using the same parameters, using the same Dataset and the same linked service:

I also preview the data I want, which works, bringing data back from the server as expected.
This makes no sense to me, I have tested using exactly the same parameters in each step, but a simple copy activity fails when all the other connection tests have succeeded. Anyone have any idea?
NOTE: I have tried setting the Linked Service connection parameters: Encryption = True which sometimes means the connection goes through OK. Again, this makes little sense.

OK, I fixed this by completely uninstalling then re-installing the Integration Runtime on the VM we are using as a gateway box.  This indicates that it can become corrupt, but the corruption is not visible to the user. We could still use the gateway for testing connections, but not for data transfer.  A careful look through of the logs didn't give any clue to the error that had caused the corruption.
The version I reinstalled was exactly the same as what was there previously, so it didn't appear to be an issue with the version (5.2.7695.1).


Move very large DB from single to multple drives with not a lot of space I have to restore a SQL database that is just over 5 TB; it is going from a single drive that is  7 TB, to another server that has split drives C:\ 4 TB and D:\ 3 TB and also has E:\Bak which is only  0.5 TB

Can this be done Without adding space?

If i split the full bak into  parts can I copy and restore one at a time?

There are multiple data files and only one log

Yes you can achieve that based on the size of the data files and log. You can do a restore with a move option to split the locations of the database files (mdf/ndf and ldf) to the mentioned drives based on the size of the files and available space on the drives.
Sample script will perform this task:

RESTORE DATABASE [BigDatabase7TB] FROM disk = 'C:\backup.bak'
WITH NORECOVERY, STATS=10,
    MOVE 'BigDatabase7TB_Data' --logical file name of primary db
        TO 'C:\MyData\BigDatabase7TB.mdf', --desired data file
    MOVE 'BigDatabase7TB_Data1' --logical file name of primary db
        TO 'D:\MyData\BigDatabase7TB1.mdf', --desired data file
    MOVE 'BigDatabase7TB_Data2' --logical file name of primary db
        TO 'E:\MyData\BigDatabase7TB2.mdf', --desired data file
    MOVE 'BigDatabase7TB_Log'
        TO 'F:\MyLog\BigDatabase7TB.ldf'
GO

NOTE: You will face issues if any of the database file sizes is bigger than available space on any of the drives. So you probably want to pay attention to the sizes of the files first and match that to the drives based of available space.


query regarding combining an update and an insert query into a single query in mysql i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data.

I can use a select to get the old data, an insert to history and finally an update to change data.

can i have all these in a single query in mysql without using stored procedures, triggers, etc.. like using locks etc.. if so give me a small sample.

To do this without the risk of blocking another user trying to update the same profile at the same time, you need to lock the row in t1 first, then use a transaction (as Rolando points out in the comments to your question) :

start transaction;
select id from t1 where id=10 for update;
insert into t2 select * from t1 where id=10;
update t1 set id = 11 where id=10;
commit;


Adding a distributor for availability groups According to this MSDN article, I need to add a distributor:

Configure Replication for AlwaysOn Availability Groups (SQL Server)

I'm confused by this particular statement:

  The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of.

I have a three node cluster.  The first two nodes are configured as synchronous replicas.  The third node is intended to be read-only, asynchronous.

This is the first time I've come across this requirement and I'm worried my current setup doesn't cover this critical piece.

Can I set up any of these three nodes as the distributor?  Which node would be the better choice or does that matter?

The reason for my question is because we have a vendor application that is not able to access the database when we failover to the second node.  Below is the error we are receiving.

  Description: Error running query in SpDbOdbc State=37000
  Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]The target database, 'Mirage', is participating in an availability group and is currently not accessible for queries.
  Either data movement is suspended or the availability replica is not enabled for read access.
  To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

  Server: \
  Database: Mirage
  Date: 2014-11-11 15:06:45
  Module: .exe
  Function: hGetErrCode
  Error code: DATABASE_ACCESS

  Error code general description: Could not access the item in the database.

Also getting this message when trying to login through the application.

  Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]Failed to update database "Mirage" because the database is read-only.

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group.

Why?

Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the (remote/scaled out) Distributor.


Order by alphabet and then by numbers I have records like

 A5
 A4
 Z1
 B2
 C7
 C1A
 C11A
 B1
 B4

I want them to be sorted in this manner

A4
A5
B1
B2
B4
C1
C11A
C7
Z1

using the ORDER BY clause.

I want them to be sorted by alphabets and then by numeric values.

For your request:

  sorted by alphabets and then by numeric values

I assume (deriving from your sample data) you want to ORDER BY:

The first letter, treated as text.
The first number (consecutive digits), treated as integer.
The whole string to break remaining ties, treated as text. May or may not be needed.

SELECT *
FROM   tbl
ORDER  BY left(col, 1)  -- 1st letter as text
     , substring(fest, '\d+')::int NULLS FIRST  -- first number in string as int
     , col  -- whole columns as cheap tiebreaker

SQL Fiddle.

More details:

Alphanumeric sorting with PostgreSQL
Humanized or natural number sorting of mixed word-and-number strings


Mysql 5.5.45 crashing after refreshing web page rapidly(Too Many Connection warning) I am using ec2 aws instance for my wordpress webiste.after every 5 to 10 minute and randomly my website going down i tried lots of things like increasing buffer memory ,memory limit but that not working for me so any one know how do i resolve this issue bellow snpiet is the error log

Mysqld log :- http://pastebin.com/8Gp0V5g3

error log:- http://pastebin.com/cpEHHUwm

160201 14:44:06 [Warning] /usr/libexec/mysql55/mysqld: Forcing close of thread 104  user: 'wordpress2'

160201 14:44:06 [Warning] /usr/libexec/mysql55/mysqld: Forcing close of thread 73  user: 'wordpress2'

160201 14:44:06  InnoDB: Starting shutdown...
160201 14:44:07  InnoDB: Shutdown completed; log sequence number 2756685
160201 14:44:07 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

160201 14:44:07 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160201 14:44:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160201 14:44:08 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.46) starting as process 14406 ...
160201 14:44:08 [Note] Plugin 'FEDERATED' is disabled.
160201 14:44:08 InnoDB: The InnoDB memory heap is disabled
160201 14:44:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160201 14:44:08 InnoDB: Compressed tables use zlib 1.2.8
160201 14:44:08 InnoDB: Using Linux native AIO
160201 14:44:08 InnoDB: Initializing buffer pool, size = 128.0M
160201 14:44:08 InnoDB: Completed initialization of buffer pool
160201 14:44:08 InnoDB: highest supported file format is Barracuda.
160201 14:44:08  InnoDB: Waiting for the background threads to start
160201 14:44:09 InnoDB: 5.5.46 started; log sequence number 2756685
160201 14:44:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
160201 14:44:09 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
160201 14:44:09 [Note] Server socket created on IP: '0.0.0.0'.
160201 14:44:09 [Note] Event Scheduler: Loaded 0 events
160201 14:44:09 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.46'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

show full table wp_options as bellow:-

my.cnf file setting in /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#max_connections = 1000
#wait_timeout = 120
#interactive_timeout = 120
#max_allowed_packet = 64M
#innodb_buffer_pool_size = 800M
#innodb_buffer_pool_size = 384M
#max_user_connections = 500
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

error after refreshing the page rapidly.

User privileges wordpress user as bellow:-

Connection's:-

If that snapshot really says you are doing it, do not mix mysql_* interface calls with mysqli_* calls.  That could cause mysterious problems.

Do not use mysql_* at all.

You could try raising max_connections to 400.  But that is just a bandaid.


index and small set of possible values I have a string column that I want to be part of a multi-column index in a large table in MySQL. There can be only a few different values in this column. If I knew these values in advance, I could make this column integer and a foreign key to another small table that lists all possible values. But I don't know them in advance.

Does it make sense to index on a string column: i.e. does MySQL somehow avoid repetition of the index column value for each row when storing internally?

There are 3 choices.  VARCHAR is one of them; probably the 'worst'.

You suggested a second one -- an extra table.

The third is:  ENUM('one_option', 'another', ...).  It will take only 1 byte (like TINYINT).  You can directly use the strings that you are currently using with VARCHAR.  If you ever need to add another option, ALTER TABLE will be 'instant' if you add the new option on the end of the list.

It is useless to index low-cardinality columns; the optimizer will decide that a table scan is faster and ignore the index.  However, a 'composite' index that includes such a column is often useful.


RAM/memory based databases I am looking for a RAM/memory based database, and these two seem to be interesting:

SQLite
Oracle Berkeley DB

Do you have any other suggestions?

I need more than one executable to be able to access the DB concurrently.

Thank you.

More details:

I want to use a database as a high level communication channel among executables, instead of using the file system or shared memory.

File system pros: persistent storage (I do not care about it in this project).
File system cons: slow; hardware stress (both for hard disk and SSD).

Shared memory pros: speed.
Shared memory cons: data size; non persistent storage; low level access.

RAM Database pros: speed; high level, query-like access.
RAM Database cons: data size; non persistent storage (not a problem in my case).

I see two competing/conflicting requirements:

  RAM/memory based database

and

  more than one executable to be able to access the DB concurrently

They are competing/conflicting because modern operating systems normally enforce strict segregation for RAM allocated to specific processes (there are exceptions, but in the main this holds).

One option here is to copy your database to a RAM disk. That would allow separate apps to treat the database as if it were a file. The downside here is that the contents of a RAM disk won't survive a reboot of the host machine, and so you need a very robust system to keep your database saved to a real hard disk as well.

But really, if you want multiple apps sharing the same db, you're much better off using a database server. Enforcing safe, concurrent access is one of two main reasons databases exist as distinct entities from flat file stores (the other is efficient joins).


When normalising, if you have entities with the same primary key, does it mean it should be the same entity? Crrently I have this check in data where each check in has check-in details, activities and total extra items charge(extra items are required for some activities).

In UNF, I separated them into 3 repeating groups:

CHECKIN( (customer_id, customer_name, check_in_d&t, main_tour_guide),

(activity_code, activity_name, prescribing_tour_guide, completing_tour_guide, activity_d&t,activity_cost),

(item_code, description, quantity, total_item_charge, quantity, total_extra_item_charges))

If I group total_extra_item_charges under item, I will get 3NF:

TOTALITEMCHARGES( customer_id, activity_d&t, total_extra_item_charges)

ACTIVITYSCHEDULE( customer_id, activity_d&t , activity_code, prescribing_tour_guide, completing_tour_guide)

Both of these share the same primary key, so should I group total_extra_item_charges under activity schedule instead since 1 activity only has 1 total extra charge?But something seems off?

*d&t means date and time

Place total_extra_item_charges into the same table as the activities. You are unnecessarily duplicating customer_id and activity_d&t for a single column that you indicate will not have more than one row. Try to avoid over-normalizing.

I would also recommend a primary key on a unique identity column (e.g. activity_schedule_id). Based on your bolding of customer_id and activity_d&t, it appears you're using a composite primary key. A datetime column is not typically suitable as a primary key and is going to add significant physical overhead. I would instead add a nonclustered index and foreign key on customer_id with an include on activity_d&t.

Depending on the transactional volume of this database, you may also want to split the date and time portions of activity_d&t for quicker join lookups. E.g. activity_date and activity_time. Be mindful of the precision level you use for your time - do you need seconds and milliseconds? If you just need to be precise to the minute, be sure you aren't storing this data as you can save a significant amount of space.


SSRS 2016 Datasource - SQL Server 2000 database Looking to upgrade SSRS from 2008R2 to 2016.

Can SQL Server 2000 databases be used as data source in SSRS 2016? And are there any other known issues that could come up with the migration?

I just created a data source from SSRS 2016 to a 2000 database so yes it is possible. However, as the commenters above stated it isn't supported.


Is it possible (or good practice) to register data upon Deletion of a record? What would be a better solution? The problem is the following.

A company has one or more representatives (a person who speaks on behalf of the company).

When a representative is deleted, it's desired to keep it in an list called "Last representative". So if the company has no representatives, one can look at that list and see what were the past representatives.

Is this possible? Or, is this a good solution/practice? What would be a better solution?

Thanks

A common practice is to have a status column that indicates whether or not the representative is active. Deleting data that you may wish to analyze in the future isn't a good idea. Instead, you could do something like this:

representativeID      status     statusDate
-------------------------------------------
1                     inactive   2014-03-20
2                     inactive   2014-04-02
3                     active     2014-04-10

The statusDate column could be updated by a trigger. It would give you the date of the last status change. In order to look at past representatives, you would simply write a query that filtered the records by inactive statuses.

/** MySQL example - 10 most recent inactive represenatives **/
select representativeID
from representatives
where status = 'inactive'
order by statusDate desc
limit 0,10


Getting rows in left table only if not present in the right table I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join :

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel FROM newparsed
LEFT OUTER JOIN   advlabel ON ((`advlabel`.`seqid` = `newparsed`.`seqid`) AND (`advlabel`.`wordindex` = `newparsed`.`wordindex`))
WHERE sublabel = NULL

So is the above query correct sublabel is a column from the right side table advlabel  ?

both tables have primarykey(seqid,wordindex)

Here is an example :

SELECT
    a.col1
    , a.col2
    , a.col3
FROM
   table1 a
   LEFT JOIN table2 b
       ON
           (a.pkcol = b.pkcol)
WHERE
   b.pkcol IS NULL

So in your case, it will most likely be :

SELECT
    n.seqid
    ,n.wordindex
    ,n.label
    ,n.arg1
    ,n.arg2
    ,n.sublabel
FROM
    newparsed n
        LEFT JOIN   advlabel a
            ON
                ((a.`seqid` = n.`seqid`)
            AND
                (a.`wordindex` = n.`wordindex`))
WHERE
   a.`seqid` IS NULL

You just have to use LEFT JOIN and select any field (I suggest a primary key) in the right table that is NULL


Foreign Keys delete data I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable FKs to do that, i fear they will become untrusted and the parent tables will be in conflict.

I read about "ON CASCADE DELETE" but i think that only works for children tables.

Example table:

I had one where there was only one FK and i used the following query:

DELETE

FROM    guide_category

WHERE

id_guide NOT IN

        (
        SELECT  id_guide
        FROM    guide
        )

This could work but i don't its feasible with many FKs.

The data importance is actually zero (test DB) i just want small DB and all tables to be trusted for better performance.

Am i missing an easy solution to this problem? or maybe even seeing a problem that isn't there.

There's a lot going on in your question, so I'm going to break down my response into sections.

  I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable FKs to do that, i fear they will become untrusted and the parent tables will be in conflict.

You do NOT need to disable Foreign Keys in order to delete data from tables that have Referential Integrity defined; you just need to delete data from the child table(s) BEFORE you delete referenced data from the parent table.  When you disable the Foreign Key before issuing the DELETEs and then delete data only from a parent table, you will most certainly run the risk of having orphaned records.  Trying to re-enable the FKey constraint would also throw an error in this scenario.  This MSSQL Tips article does a decent enough job walking through why this is important, so I won't rehash it here.

  I read about "ON CASCADE DELETE" but i think that only works for children tables.

The cascaded deleted would identify if the table you're currently trying to delete data from is a parent table and issue appropriate deletes from underlying child tables prior to removing the records from the parent table.  All of this activity is nested within an implicit transaction, so depending on how much child-level data references said parent records, you may find yourself running a long-running transaction that generates a lot of blocking.  There are a number of issues with cascaded deletes (and updates), but taking this approach is a personal preference.  I think David Spillet's answer to this dba.se question does a good job of touching some of the risks/rewards with cascaded statements.

  Am i missing an easy solution to this problem?

Yes, if this is a test environment and you don't care about the data, the fastest approach would be what I would consider the nuclear option, as follows:

Drop all Referential Integrity constraints
Truncate all tables (this is why we disable the check constraints)
Recreate all Referential Integrity constraints
Insert any default data back into the tables of your choosing

The following script will accomplish this without much effort (or any checking).  Be careful with this; it is indiscriminate and will wipe all user data from your database without a second thought:

DECLARE @objectName NVARCHAR(1024), @fkeyName NVARCHAR(256), @refName NVARCHAR(1024), @colSelf NVARCHAR(MAX), @colReference NVARCHAR(MAX), @dSQL NVARCHAR(MAX)

-- Walk through the tables dropping RI
DECLARE fkeyDefinitions CURSOR STATIC
FOR
    -- Based on query lifted from RJB's SO Post: https://stackoverflow.com/a/36818102/944748
    SELECT  '[' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + OBJECT_NAME(t.object_id) + ']' AS objectName
        , fk.name
        , '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + ']' AS refName
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c
                    INNER JOIN sys.foreign_key_columns AS fkc
                        ON fkc.parent_column_id = c.column_id
                       AND fkc.parent_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_self
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c
                    INNER JOIN sys.foreign_key_columns AS fkc
                        ON fkc.referenced_column_id = c.column_id
                       AND fkc.referenced_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_reference
    FROM    sys.tables t
        LEFT JOIN sys.foreign_keys fk
            ON t.object_id = fk.parent_object_id
    WHERE   t.type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

-- go through the fkey cursor and drop all RI
OPEN fkeyDefinitions
FETCH NEXT FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT [' + @fkeyName + ']'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

-- go through new cursor truncating all the tables
DECLARE truncUserTables CURSOR
FOR
    SELECT  '[' + OBJECT_SCHEMA_NAME(object_id) + '].[' + OBJECT_NAME(object_id) + ']' AS objectName
    FROM    sys.tables
    WHERE   type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

OPEN truncUserTables
FETCH NEXT FROM truncUserTables
INTO    @objectName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'TRUNCATE TABLE ' + @objectName
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM truncUserTables
    INTO    @objectName
END

CLOSE truncUserTables
DEALLOCATE truncUserTables

-- go through the fkey cursor a second time re-creating RI
FETCH FIRST FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT [' + @fkeyName + '] FOREIGN KEY (' + @colSelf + ') REFERENCES ' + @refName + '(' + @colReference + ')'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

CLOSE fkeyDefinitions
DEALLOCATE fkeyDefinitions

I even setup a dbfiddle.uk demo if you want to see it in action.


How does one scale SQL Server 2008 or 2012? How does one scale SQL Server 2008 (or 2012)? At its basic, I understand there are two options:

Scale up:

If CPU bound, I can clearly see going from 1 CPU core to 2 to 4. Or if RAM usage rockets, just adding more RAM. Does SQL Server 2008/2012 actually pick up the slack and scale up that way assuming NO application level changes? To minimize speculation, lets assume I'm not doing something dumb like burning CPU cycles, doing cross joins etc.

Scale out:

It's not very clear how scaling out would work. I mean if I added another SQL server right next to my first one, how does the query know which server to run on? Is there some load balancer at the front (and does it come with the SQL Server software?) ? Does it entail application level changes for scaling out to work? Or do I have to shard the data and have custom code that calls up the correct database server depending on the data sharding key?

Would appreciate input from more experienced folks.

SQL Server doesn't scale out as such. It scales up.

There are 3 areas to do this, subject to edition limitations

CPU cores
RAM
Storage

And of course, use a higher edition eg Enterprise

SQL Server doesn't shard and any such solution (you can research MySQL sharding solutions) adds complexity and overhead to a system.

Scaling up one server (+ standby nodes/mirror) is usually quite straightforward with RAM, SSDs, more disk volumes to spread IO, separate drives for tempdb and logs etc

Also, if you find SQL Server is CPU bound then it's usually poor design and/or indexes and/or poorly written queries unless you have a massive load.


can mysql cli exit with error if no records returned? I would like to execute a sql command in bash, and do something based on whether or not there was a result.

e.g.

$ LOOKUP=123; mysql -e \
    'SELECT id FROM table WHERE id='$LOOKUP dbname \
    && echo "Row $LOOKUP exists"
    || echo "Row $LOOKUP does not exists"

I can think of bodges (e.g. testing for ZLS) but wondered if there was a clean way?

Try changing the query to force a value

Instead of

SELECT id FROM table WHERE id=${LOOKUP}

you can use

SELECT COUNT(1) FROM table WHERE id=${LOOKUP}

Here is bash script to use that new query

LOOKUP=123
SQLSTMT="SELECT COUNT(1) FROM table WHERE id=${LOOKUP}"
RCOUNT=`mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}"`
if [ ${RCOUNT} -eq 0 ]
then
    echo "Row ${LOOKUP} exists"
else
    echo "Row ${LOOKUP} does not exist"
fi

Here is an alternative

LOOKUP=123
SQLSTMT="SELECT CONCAT('Row ',rcount,' ',IF(rcount=0,'Exists','Does Not Exist')) FROM (SELECT COUNT(1) rcount FROM rolando WHERE id=${LOOKUP}) A"
mysql -u... -p... -AN -Ddbname -e"${SQLSTMT}" > /tmp/answer.txt
cat /tmp/answer.txt

Give it a Try !!!


Generate List of Missing Relationships I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from CLUSER with a similar, but not identical workgroup. The columns for these tables are as follows:

CREATE TABLE [dbo].[CLUSER]
  (
     [LOGONID]   [VARCHAR](15) NOT NULL,
     [WORKGROUP] [VARCHAR](30) NOT NULL,
     PRIMARY KEY CLUSTERED ( [LOGONID] ASC ) WITH (...other settings...,
       FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

CREATE TABLE [dbo].[CLUSERRX]
  (
     [LOGONID] [VARCHAR](15) NOT NULL,
     [RXUSER]  [VARCHAR](10) NOT NULL,
     CONSTRAINT [USERRX_LOGONID] PRIMARY KEY CLUSTERED
       ( [LOGONID] ASC, [RXUSER] ASC ) WITH (...other settings...,
     FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

I am able to generate a list of every CLUSERRX.LOGONID, CLUSER.WORKGROUP for that LOGONID, CLUSERRX.RXUSER, and CLUSER.WORKGROUP for the RXUSER using the following query:

SELECT u.LOGONID,
       u.WORKGROUP as 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP as 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
         ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
         ON p.LOGONID = r.RXUSER

What I need to do is take the data in CLUSER and find what relationships are missing in CLUSERRX.

Example data:

CLUSER
|-----------|------------|
|  LOGONID  |  WORKGROUP |
|-----------|------------|
| JCURCIO   | 001 USER   |
| TUSER     | 001 SUP    |
| ATEST     | 001 SUP    |
| MPAGE     | 001 User   |
|-----------|------------|

CLUSERRX
|-----------|------------|
|  LOGONID  |  RXUSER    |
|-----------|------------|
| JCURCIO   | TUSER      |
| JCURCIO   | ATEST      |
|-----------|------------|

Since there is no record for MPAGE in CLUSERRX for TUSER or ATEST that is incorrect and I need to display that.

I feel like I need to use something like:

SELECT u.LOGONID,
       u.WORKGROUP AS 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP AS 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
              ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
              ON p.LOGONID = r.RXUSER
WHERE  ( u.LOGONID NOT IN (SELECT u.LOGONID,
                                  u.WORKGROUP AS 'USER WORKGROUP',
                                  r.RXUSER,
                                  p.WORKGROUP AS 'PROVIDER WORKGROUP'
                           FROM   CLUSERRX r
                                  LEFT JOIN CLUSER u
                                         ON u.LOGONID = r.LOGONID
                                  LEFT JOIN cluser p
                                         ON p.LOGONID = r.RXUSER)
         AND p.WORKGROUP NOT IN (SELECT u.LOGONID,
                                        u.WORKGROUP AS 'USER WORKGROUP',
                                        r.RXUSER,
                                        p.WORKGROUP AS 'PROVIDER WORKGROUP'
                                 FROM   CLUSERRX r
                                        LEFT JOIN CLUSER u
                                               ON u.LOGONID = r.LOGONID
                                        LEFT JOIN cluser p
                                               ON p.LOGONID = r.RXUSER) )

But I have no idea how to format it properly to get the information I need. Can anyone provide any insight on what I may be missing, or if I am even in the right direction?

You have to define what you mean with "similar workgroups" and alter the relevant line but this is what I understand from your wording.

It will show all combinations of users from different workgroups (and similar in an arbitrary way, matching the first 3 characters of group name) that do not appear (in either order) in the cluserrx table:

; WITH UserCombinations AS
    ( SELECT r.logonid AS logonid,
             u.workgroup AS User_workgroup,
             p.logonid AS rxuser
             p.workgroup AS Provider_workgroup
      FROM cluser AS u
        JOIN cluser AS p
          ON  u.workgroup < p.workgroup                     -- not identical workgroups
          AND LEFT(u.workgroup, 3) = LEFT(p.workgroup, 3)   -- but similar
    )
SELECT
    uc.logonid,
    uc.User_workgroup,
    uc.rxuser
    uc.Provider_workgroup
FROM UserCombinations AS uc
WHERE NOT EXISTS
      ( SELECT *
        FROM cluserrx AS rx
        WHERE rx.logonid = uc.logonid  AND  rx.rxuser = uc.rxuser
           OR rx.logonid = uc.rxuser  AND  rx.rxuser = uc.logonid
      ) ;


Selecting only when specific values exist in another select query A bit of a noob question, but would appreciate the help.

I want to use the result of one select statement in a different one in a specific way.

The first select statemnent:

SELECT DISTINCT id FROM customers WHERE country = 'Neverland';

The result will be a one-column table with distinct IDs.
Now I want to select all these IDs from a different table.

SELECT * FROM shipments WHERE [the id is contained within the first select statement]

Thanks!

The easy answer would be to combine both queries, directly, using subqueries (IN). So, assuming the column on shipments is also called id (substitute it with the real name, if different):

SELECT *
FROM shipments
WHERE id IN ( SELECT DISTINCT id
              FROM customers
              WHERE country = 'Neverland')

It may be nice to learn a bit about JOINs, while those may seem more complicated at first, they may have in some cases a better query plan or logically less complex once you learn about those:

SELECT *
FROM shipments
JOIN customers
ON shipments.id = customers.id
WHERE shipments.country = 'Neverland'

(again, you may need to adapt it to the particular column names, and cardinality relation- show no duplicate shipments, etc.).


Find 13th min value in table group by a column I am trying to replicate Small function in Excel which is Small(Array,K) gives Kth smallest element from stream of data.

I want to find 13th Min value for perticular Column values. in my table I have 2 columns A and B for each A column row value I have lets say 20 B values. I am trying to find 13th Smallest value for Each A value from column B

I am using following query to get min from group by clause

select A,min(B) from test group by Level_Name

I want to find 13th smallest value and not the smallest value.

I added tried a new query as

with a as(
select A,B,ROW_NUMBER() over (order by A,B) as rownum
 from test
group by Level_Name,pl
)
select A,B from a
where rownum=13
group by A,B

It is returning only 13th number for 1 of the A value but there are multiple rows in A.


How do I know if my PostgreSQL is up do date with DST rules? Recently (2019-04-25) brazilian government ended the use of daylight savings time all around the country.

In my current project I rely on PostgreSQL to do timezone conversions and analytics based on time from different time zones and dates.

As far as I am concerned PostgreSQL implements daylight savings and timezones through Olson database.

1) Is PostgreSQL (version 11, for instance) up to date with the new no daylight saving time rule in Brazil?

2) If it is not updated, how can I fix it? Is there any default PostgreSQL patch calendar/schedule in that sense?

Yes, there is a schedule. It's documented here: https://www.postgresql.org/support/versioning/

  if need be, security fixes that are released at least once every three months in what we call a "minor release". For more information on the minor release schedule, you can view the minor release roadmap

And the minor release roadmap currently (as of 2019-07-08) shows:

  The target date for these releases are, unless otherwise stated, the second Thursday of February, May, August, and November. The current schedule for upcoming releases is:

     * August 8th, 2019
     * November 7th, 2019
     * February 13th, 2020
     * May 14th, 2020

Every minor release will include updated DST rules if necessary.

The last minor release for Postgres 11 was 11.4 on 2019-06-20. I would have assumed that it included the new rules for Brazilian DST, but I can't find any mentioning of that in the release notes as there was e.g. for the 11.3 and 11.2 releases.

So most probably, 11.5 (scheduled for August 8th) will include that.

If you want an authoritative answer from the devs, you should ask that on the Postgres mailing list

Postgres can be compiled in a way that it uses the operating system's time zone data. You can check if your Postgres version was compiled with the --with-system-tzdata option by running pg_config


Inserting all csv files into a table in MySQL I am looking for a way to insert all my csv files into MySQL without having to write several LOAD DATA INFILE statements.

I have many csv files I need to insert and the files themselves are very large.

I have tried *.csv, but this does not work.

See these previous answers:

https://stackoverflow.com/questions/8538995/how-to-import-multiple-csv-files-into-a-mysql-database

https://stackoverflow.com/questions/6552042/mysql-loading-multiple-files-into-a-table

Essentially, no.  But it's easy to script this to get a similar result (e.g. one command to import multiple files using a loop).


EXCEPTION logic is not working in Oracle PL/SQL function These are the tables I have created and inserted some values:

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR(30) NOT NULL
 );

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
 FIRST_NAME VARCHAR(20) NOT NULL,
 LAST_NAME VARCHAR(25) NOT NULL,
 EMAIL VARCHAR(25) NOT NULL,
 PHONE_NUMBER VARCHAR(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 JOB_ID NUMBER NOT NULL,
 SALARY DECIMAL NOT NULL,
 DEPARTMENT_ID NUMBER NOT NULL,
 CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
 );

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(3,'Accounting');

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (1,'Tony','Starc','starc@gmail.com','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (2,'Bruce','Wayne','bruce@gmail.com','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (3,'Larry','Ellison','larry@gmail.com','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (4,'Steve','Jobs','steve@gmail.com','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (5,'Remy','Lebeau','remy@gmail.com','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (6,'Clark','Kent','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

Then I have created a function to display number of employees working in a specific department:

CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912 (dept_name VARCHAR)
RETURN VARCHAR
IS
no_of_employees NUMBER;
BEGIN
SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;
DBMS_OUTPUT.PUT_LINE ('No of employees');
RETURN no_of_employees;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('IT'));
END;

DBMS OUTPUT:
No of employees
3

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Sales'));
END;

DBMS OUTPUT:
No of employees
3

As you can see from the code above the function is successfully displaying number of employees working in a specific department when I'm entering valid department name.
However, the problem that I'm facing is when I'm entering an invalid department name (that doesn't exist in department table), it is supposed show the error message 'Department Not Available' as I have set [EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE ('Department Not Available');]

Instead I'm getting this message when I'm entering invalid department name:

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Medicine'));
END;

DBMS OUTPUT:
No of employees
0

Where could I possibly go wrong ?

You are trying to find an error where an error can not exist. When you are doing a count it will always return a valid result even if the value that you are looking for does not exist in the table.

What you need to do is two separate counts and do a check to see what the return value is.

SELECT COUNT(*) INTO no_of_departments
FROM    DEPARTMENT
WHERE   DEPARTMENT_NAME = dept_name;

IF no_of_departments = 0 THEN
    * ERROR LOGIC HERE *
END IF;

IF no_of_departments > 1 THEN
    * ERROR LOGIC HERE *
END IF;

SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;

IF no_of_employees = 0 THEN
    * ERROR LOGIC HERE *
END IF;


Would elasticsearch or RavenDB be better for fueling a statistics engine/random forest? (Note: this question exists on StackOverflow as well but I thought it might have a better reception here.  If it proves this is the better place, I'll close/ask to migrate/link to this.  Also, if it doesn't really belong here, I'd be happy to delete it.)

I've been looking at the following NoSQL databases for the next phase of my project:

elasticsearch
RavenDB

elasticsearch positions itself as primarily serving advanced search scenarios while RavenDB positions itself as a document-oriented-database.

Primarily, the document will be around videos.  Each has a natural id.  That will be the key of the document.

Around that, I add other content in fields which will not necessarily be scalar or flat, as the information will come from a number of different sources with different structures.

For example, there will be content from the video provider's Atom feeds, blog posts that have the video embedded in it, and other pieces of data from a data warehouse project.

There is no set structure across all of the items (each of them will be very domain-specific, actually), the only thing that will relate them is the natural key of the video mentioned above.

That said, once I have this information in one of the above solutions, I'll want to do a number of things with it:

Cull it to help populate variables in a random forest in order to make classifications about the videos
Provide general search on the videos (general free-text, not based on the results of the random forest) through a web-based front end (ASP.NET MVC if you must know)

There are some requirements:

I will more than likely be in a ASP.NET shared web hosting environment.  This means I'll have one machine, and won't have access to set up a service.  Something embeddable will be very helpful.
The ASP.NET environment will be hosted in IIS, so the embeddable aspect will have to survive app-domain recycling.
I'll want to create new indexes based on the results of the statistical analysis which I can easily fascet which will help with the search on the site.
Support for autocomplete functionality (I know this isn't an "out-of-the box" request, but being able to get to that point is important).
Rich synonym support (there's a number of them in the type of videos I'm indexing content around)

I'm also open to services, such as Truffler, although I do have concerns about the cost (and in Truffler's case, a little concerned about latency between the data centers, because the requests will come from the web host on the West coast, or from a back-end process on the East coast).

Additionally, I don't feel that one solution needs to fit all the requirements.  I'm more than fine with having one serve one purpose and having another serve another purpose.  Granted, migrations suck, but migrating between these two document stores is a little easier (and I don't expect them to use the same document structure, necessarily).

Ravendb embedds quite nicely into a .net application and also allows you to create full text (embedded) lucene.net indexes. Given your constraints on the hosting elasticsearch won't be a viable option since you'll need it to run as a service alongside of your MVC application.

Lucene.net does not support facets out of the box but ravendb comes to the rescue here too:
http://ravendb.net/documentation/faceted-search

Ravendb also allows you to control your lucene.net analyzers quite nicely:
http://ravendb.net/documentation/how-indexes-work

Disclosure: I'm the author of the elasticsearch .net client NEST so if anyone would try to sell you Elasticsearch it be me :)


Installation of Oracle 11gR2 in Oracle Linux 7 Installation of Oracle 11gR2 is failed with below error, on Oracle Linux 7 OS.

> INFO: Building Client Shared Libraries INFO: Building Client Shared
> Libraries INFO: The output of this make operation is also available
> at: '/u01/app/oracle/product/11.2.0.4/db_1/install/make.log' INFO:
>
> INFO: Start output from spawned process: INFO:
> ---------------------------------- INFO:
>
> INFO: /u01/app/oracle/product/11.2.0.4/db_1/bin/genclntsh
>
> INFO: /bin/ld: cannot find -lstdc++_nonshared
>
> INFO: collect2: error: ld returned 1 exit status
>
> INFO: genclntsh: Failed to link libclntsh.so.11.1
>
> INFO: make: *** [client_sharedlib] Error 1
>
> INFO: End output from spawned process. INFO:
> ---------------------------------- INFO: Exception thrown from action: make Exception Name: MakefileException Exception String: Error in
> invoking target 'client_sharedlib' of makefile
> '/u01/app/oracle/product/11.2.0.4/db_1/rdbms/lib/ins_rdbms.mk'. See
> '/u01/app/oraInventory/logs/installActions2017-11-09_12-52-50AM.log'
> for details. Exception Severity: 1

Let me know that i need to try reinstall it or any work around can be done.

This is caused by the missing libstdc++ package.

Linking using g++ fails searching for -lstdc++

In your previous question, on your screenshot, you have multiple missing packages, including libstdc++.


Restarting SQL Server Agent on a cluster installation I've just enabled the mail profile in SQL Server Agent and I need to restart the service for it to take affect but the Agent is running as part of a SQL Server cluster and I don't want the cluster manager to think there is a problem and fail-over all of the services to the other node.

What is the correct way to restart the SQL Server Agent on a SQL Server instance that is clustered?

Set the resource/service for SQL Agent as offline via the failover cluster manager, and then back online.


Track access to database How can I see who has accessed the database, viewed the tables or executed SELECT, for SQL Server 2008 R2?

It sounds like what you're looking for is an audit.  You can achieve this through the use of SQL Server Audit.  With this tool, you will be able to audit Server and Database level actions and access.

To find out more information about how to use this tailored to your implementation, take a look at this BOL reference on SQL Server Audit.

This is available in Enterprise and Datacenter editions.


ORACLE error while dbstart command I'm trying to start up Oracle DB with startup command.
But error occurred while startup.

dbstart[80]: test: argument expected
Failed to auto-start Oracle Net Listene using /bin/tnslsnr
touch: $ORACLE_HOME/startup.log cannot create
chmod: $ORACLE_HOME/startup.log: No such file or directory
Processing Database instance "$ORACLE_SID": log file $ORACLE_HOME/startup.log
No such file or directory
dbstart[350]: $ORACLE_HOME/startup.log: cannot create

How can I fix it?  Oracle Database 10g Release 10.2.0.1.0 - 64bit Production is running under AIX.

I have faced the similar issue. The reason, why it has happened was the invalid entry in the /etc/oratab.

As dbstart script suggests, to configure, update /etc/oratab with instances that need to be started up. The entries are of the form:

$ORACLE_SID:$ORACLE_HOME:<N|Y|W>:

An example entry:

main:/usr/lib/oracle/emagent_10g:Y


How do I change Postgresql to be listening on port 5432? I've upgraded Postgresql from 9.3 to 9.6 on my machine (linux mint rafaella- so ubuntu 14.04)

As the previous version was listening on 5432 the new version is listening on 5433, but I want to change that to 5432 so that the previous configuration (rails, phppgadmin etc) work with the new postgres server.

I have changed the port in postgresql.conf from 5433 to 5432 and restarted postgres, but this didn't work:

$ sudo netstat -nltp |grep 5432
$ sudo netstat -nltp | grep 5433
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      25467/postgres

I also tried setting the environment variable and restarting postgres:

PGPORT=5432; export PGPORT

again, still listening on 5433 and nothing on 5432.

trying to connect gives me:

$ sudo -u postgres psql postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What am I doing wrong?

Output of pg_lsclusters:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

contents of pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

ypercube and deszo were able to get this user's environment up and running (per the chat transcript).

The key seemed to be running:

sudo pg_ctlcluster 9.6 main restart

but not running it as the postgres user.

See the chat log for more details.