mariadb won't let me access from "remote" I have one Linux computer(CentOS7.4) as server, one windows computer. I have setup the mariadb server. On windows, I use mysql-workbench as client.

However, I can access the server via 'tcp/ip over ssh', but when I connect using 'tcp/ip' I get 10060 error.

By the way, I can ping 192.168.1.11, it seems the network is ok. Therefore, what's wrong with my setting?

three things to check

the user and host:

and 3. the settings file in /etc/my.cnf:

server.cnf

mysqld

bind-adress and skip-networking

Four things to check:

Check that the DB user allows for connecting from the remote client. A user has a both a 'user' and a 'host' component. SELECT user, host FROM mysql.user; will show your users including their hosts, i.e. the clients they allow. You can use the wildcard '%' to allow connecting from any client, or a specific IP address or hostname if you prefer that.
Make sure that the skip-networking variable is not enabled in the configuration files.
Make sure bind-address is either not enabled or pointing to a specific IP address you want to allow in the configuration files.

Once you have updated the configuration files, restart MariaDB.

Check the MariaDB documentation on configuring for remote client access for details.

Make sure the firewall on CentOS isn't blocking access:

sudo firewall-cmd --zone=public --permanent --add-service=mysql

sudo systemctl restart firewalld


how to add attachment(text file) to database mail? I have scenario

Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates

I can send a mail to client that job is successfully completed - now i need to send him a text file as a attachment

Is there any way to send attachment through GUI (SQL Server Job setting)

I cann't run the script

EXEC sp_send_dbmail

I googled for this scenario but no information from GUI END - i could find from only with scripts

I was looking of for the same issue myself, and gave up. It seems that attachments can only be sent using TSQL, such as this

EXEC sp_send_dbmail
  @profile_name='MailProfile',
  @recipients='me@gmail.com',
  @subject='TestEmail',
  @body='I'm sending you this document...',
  @file_attachments='D:\TEST\file.doc'


Confused about why a particular index is being used I have a table (with 170M rows) that looks as follows:

CREATE TABLE [dbo].[Panel]
(
    [SubId] [varchar](15) NOT NULL,
    [LineageId] [int] NULL,
    [Buck] [varchar](20) NULL,
    [Lot] [varchar](20) NULL,
    [GlassType] [varchar](20) NULL,
    [ETA] [varchar](200) NULL,
 CONSTRAINT [PK_Panel] PRIMARY KEY CLUSTERED
(
    [SubId] ASC
)

99% of the queries against this table reference SubId either in the Where clause or join. One of our DBAs told me he could make all those queries and joins perform better by creating the following index:

CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId])

When he told me this, I thought he was nuts. But I just checked index usage since this index was created and found the following:

PK_Panel (232,394 seeks / 2,133 scans)
IX-Panel-SubID-I-LineageID (25,528 seeks / 3644 scans)

I was a bit shocked to see this. Under what circumstances would this new index every get used? Why would SQL Server ever select it?

Or maybe a better question would be, why would SQL Server select the new index to do a seek, instead of the clustered index? Approximately 25K times, it thought seeking on the new index was a better choice.

In case this helps, the LineageId essentially indicates where a panel was created and there are ~35 distinct values it could contain.

Creating an index on the column involved in the clustering key may seem a little strange.  One asks why create another index, when one already exists?
The clustered index is the table.  That is to say your clustered index for this table is similar to an index like:
CREATE INDEX IX_Panel
ON dbo.Panel(SubId ASC)
INCLUDE (
    LineageId
    , Buck
    , Lot
    , GlassType
    , ETA
);

Pretty clearly, this is not the same as an non-clustered index on the clustering key, which in your case only "includes" the LineageId column:
CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId]);

As a test, I created a mock-up of your table, then insert over 700,000 rows into it:
INSERT INTO dbo.Panel(LineageId, Buck, Lot, GlassType, ETA)
SELECT (ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) % 35)
    , SUBSTRING(o2.name, 1, 15)
    , 'lot'
    , 'GlassType'
    , o3.name + o2.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

I then ran the following query to see stats about both indexes:
SELECT o.name
    , ps.index_id
    , ps.index_type_desc
    , ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Panel'),-1,0, NULL) ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id;

The results of the query above are:

Clearly, the non-clustered index is smaller than the clustered index.  In this case about 1/5th the size.  Being a smaller index means the query optimizer will choose to use it when the index fulfills the requirements of the query, in one way or another.
For instance,
SELECT SubId
FROM dbo.Panel;

will only need to read 1,304 pages from disk by scanning the non-clustered index, instead of having to read 7,105 pages to scan the clustered-index.

On the other hand, for a query that references columns that are not included in the non-clustered index, SQL Server will likely use the clustered index since it fulfills the requirements of the query.
Take for instance, the following query, where you might intuitively think the query optimizer might pick the non-clustered index since it can use that to fulfill the WHERE clause, however the simple act of having to look up the Buck column means it is faster to simply seek the clustered index for the values in the WHERE, then return the 3 columns in the SELECT clause:
SELECT SubId
    , LineageId
    , Buck
FROM dbo.Panel
WHERE LineageId = 6
    AND SubId >= 27
    AND SubId <= 42;

This is a bit of a simplification, I would highly recommend looking at Brent Ozar's Index pages


Available space differs between sys.database_files and database properties dialog in SQL Server Management Studio I am looking at this for a few minutes now and could not find a thread mentioning this.

I wanted to query currently used space and available space from a database on SQL Server.
The numbers I query should match the numbers I see, when I open the properties dialog on a database in SQL Server Management Studio, like this:

So I browsed a bit and found that I can query against sys.database_files like so (from here):

SELECT
        DB_NAME() AS DbName,
        name AS FileName,
        size/128.0 AS CurrentSizeMB,
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
    FROM sys.database_files;

This gets me this result:

DbName       FileName         CurrentSizeMB   FreeSpaceMB
databasename databasename     17338.000000    16795.312500
databasename databasename_log   351.000000      331.234375

Trying to recognize my numbers from the previous dialog:

OK, adding 17338and 351 gets me to 17689 which is exactly the size the dialog lists
But for available space I cannot find it, 16795.312500 is off by almost half a MB

What can I do to get exactly the same number the properties dialog shows for Space Available?

Did I use the query/formulae in a wrong way or is this simply not possible at this point due to some rounding (converting pages/extents to MB...)?

Tracing the query used by ssms

One way to do this is by taking the profiler's 'tuning' template and then tracing your login

This query pops up

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

The query gets a DbSize and  SpaceUsed column from some DMV's.

The DbSize column

A straightforward query, get the size where the file type

= 0 = Rows
= 2 = FILESTREAM
= 4 = Full-text

This query gets all file sizes except the log size.

I'm assuming this is added later as it is added to the total size in the properties pane.

SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize]

The SpaceUsed column

A bit bigger, first get the sum of total pages from the  sys.allocation_units DMV

...SUM(a.total_pages) + ...
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
...

And then add any filestream data to this number (if none then add 0 ):

...(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id ...

The total query

Running the total query gives me:

DbSize  SpaceUsed
2557952 886791

Both the DBSize and SpaceUsed column is the size in 8KB pages.

  What can I do to get exactly the same number the properties dialog
  shows for Space Available?

If I transform the query to be divided by /128.0 and subtract SpaceUsed from DBSize like your query

SELECT
(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) )/ 128.0 -
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 ))/ 128.0 AS [FreeSpace]
FROM
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id

I can replicate the properties view of the space available:

FreeSpace
13055,945313

Still need to apply rounding with 2 digits after the comma

where your query gives me a lower amount on my database

FreeSpaceMB
13055.687500

Trying to find a why

Assumptions

We established that the DBSize column is the same for both queries, the difference should be between the total_pages column from the sys.allocation_units dmv and the FILEPROPERTY function

FILEPROPERTY(name, 'SpaceUsed') AS INT).

on the FILEPROPERTY documentation:

  Number of pages allocated in the file

Source

Where total_pages =

  Total number of pages allocated or reserved by this allocation unit.

Source

Guess

We are getting the sum of all these allocation units. My guess would be that the difference is based on the file having more overhead pages not directly in the allocation units dmv, such as File header, PFS, GAM, SGAM, DCM, BCM.

More on these here

Using sp_spaceused and @updateusage to update the space usage info (DBCC UPDATEUSAGE)

exec sp_spaceused

The sp_spaceused procedure gives the same result as the SSMS Properties window and the sys.allocation_units query.

unallocated space
13055.95 MB

But when running sp_spaceused with @updateusage = 'true'

  Indicates DBCC UPDATEUSAGE should be run to update space usage
  information. When objname is not specified, the statement is run on
  the whole database;
  Source

EXEC sys.sp_spaceused  @updateusage = 'true';

The values are updated:

unallocated space
13056.07 MB

The changed SSMS query that uses sys.allocation_units also shows this updated value:

FreeSpace
13056,070313

While the FILEPROPERTY query did not change!

SELECT         size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
    FROM sys.database_files;

FreeSpaceMB
13055.687500


Index conditional aggregate query on PostgreSQL How does one optimize a conditional aggregate query on PostgreSQL?

A example of such query would be:

SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL';

Indexing (PlayerId, status) would help the filter, any tricks or ideas to optimize the aggregation post filtration?

What would be ideal would be no external caching mechanism involved, and a save mechanism to keep a running SUM every time "games" table is updated – similar to how a cache works but a more reliable one.

You'll have to create a special table to contain the sum.

Then create triggers on the original table that add values that are inserted, subtract values that are deleted, do both for an update and set the value to zero on truncate.

You can see a small example of that for the count aggregate in my blog post. Different from that example you will have to have a summation table with one sum per "PlayerId" and status.


How To Secure Sql-Server Database From Intended Manipulation by IT mans or Database Admins or System Admins This question rises from a dispute between "Financial Department" and "IT Department" in a Production Company.

This company has it's own IT Department witch provides Financial Software tools and all other ERP-Like tools for this company. Because the IT Department itself is part of this company actually the salary of software developers and database admin is calculated and paid through the software and databases designed and maintained by themselves. so the financial department is feared of database manipulation by the system admins. also they don't want to use other software tools and want to remain in the current software and database solutions.

Now the Question is:

How is it possible to monitor an Sql-server database to prevent any intended manipulation by database admin? the solution must be in a way that insures the system works fine but any manipulation by system admin will be traceable for financial department and system admin will not be able to erase his/her manipulation traces.

of course any simplified and tutorial based approach suggestion will be appreciated.

Sure, this is what compliance auditing solutions like IBM Guardium and Imperva do. They're appliances that sit in between your SQL Server and the rest of the network, and they capture everything that happens without affecting end user activities.

They're expensive - think six figures and up for the full implementation, and it's not going to be discreet.

If you were looking for something free or cheap, you can try using the built-in auditing features in SQL Server, or software products like Idera Compliance Manager, but be aware that sysadmins can disable those tools (because they're likely gonna be the ones installing them anyway.)


Selecting Multiple Rows in One Query with Multiple Conditions I can select multiple rows with one condition by using something like:

SELECT `Col`, `Col2` FROM `Table` WHERE `Col3` IN (?, ?, ?, ?, ?);
# This selects 5 rows

How can this be done if there are multiple conditions (all integer equals operations)?

There are three conditions that the query needs to check against and all three of these make up the composite primary key.

A single query will select from 10 to 100 rows (though most of the time it'll be only 10)- it has to be fast in terms of performance. This is why using multiple queries isn't a good idea.

The CREATE TABLE statement is:

CREATE TABLE `Table Name` (
  `X` smallint(6) unsigned NOT NULL,
  `Y` smallint(6) unsigned NOT NULL,
  `Z` smallint(6) unsigned NOT NULL,
  `Data` varchar(2048) NOT NULL DEFAULT '',
  PRIMARY KEY (`X`,`Y`,`Z`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With multiple queries, it could be done like this:

SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
# This selects 3 rows but I don't want to make 3 calls to the database server for that

You have 2 basic syntactical options for doing this in one query and 2 options on whether to send the values in the query or load them first in a table:

the normal AND / OR (parentheses are redundant here but it's good to use them with OR, just in case the WHERE gets more complicated):

WHERE (  ( x = ? AND y = ? AND z = ? )
      OR ( x = ? AND y = ? AND z = ? )
      ...
      OR ( x = ? AND y = ? AND z = ? )
      )

compact IN with "row constructor":

WHERE (x,y,z) IN ((?,?,?), (?,?,?), ...)

load the triplets in a (temporary) table and JOIN:

CREATE (TEMPORARY) TABLE tmp_table
--- ;

INSERT INTO tmp_table (x,y,z)
VALUES (?,?,?), (?,?,?), ... ;

SELECT t.*
FROM my_table AS t
  JOIN tmp_table AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;

or:

  ON  (t.x, t.y, t.z) = (tmp.x, tmp.y, tmp.z)

The first 2 options are equivalent but they may differ in efficiency, depending on version. This syntax of IN with tuples (row constructors) does not use indexes most effectively in older versions. In 5.7 the optimizer identifies the two syntaxes as equivalent (see MySQL docs: Row Constructor Expression Optimization). Test!

The other choice of using a table may be better when you want to query for a number of parameters / triplets. You can also index the (temp) table. Test!

So, the basic advice is to test in your version/configuration/setup, with the tables having sizes similar to their predicted sizes, and with various number of parameters.

Two more ways that might be worth testing too:

The simple UNION ALL. Since we just want to run multiple identical queries where only the parameters differ. One disadvantage is that the query gets really long and clumsy-looking if the basic query is complex and you have many triplets to check:

SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
---
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) ;

Using a derived table (with UNION ALL) in the JOIN variation. This may use an optimization (that was added in 5.5 or 5.6) that can materialize and index a derived table:

SELECT t.*
FROM my_table AS t
  JOIN
      ( SELECT ? AS x, ? AS y, ? AS z  UNION ALL
        SELECT ?, ?, ?  UNION ALL
        ---
        SELECT ?, ?, ?
      )
    AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;


How to select from two tables where the rows may be or may not be equal and group it if they are equal I have two mysql tables of images ojm_pages_articles_photos, ojm_images_optimized but I want to select rows that are common to them and also rows that are NOT common to them. In case common rows are found, they should be grouped but in case they are not, the single rows should be selected. The problem in my code is that it only selects and group records that are found both in the two tables but i want records to also be selected when they are found only in table ojm_pages_articles_photos and not in the other one.

SELECT DISTINCT `ojm_pages_articles_photos`.`id_pages_articles_photos`,
                `ojm_pages_articles_photos`.`url_articles_photo`,
                `ojm_pages_articles_photos`.`id_ojm_peoples` AS `id_ojm_peoples_uploader`
FROM    `ojm_pages_articles_photos`,
        `ojm_images_optimized`
WHERE `ojm_pages_articles_photos`.`url_articles_photo` LIKE '%jpeg%'
  AND `ojm_pages_articles_photos`.`url_articles_photo`=`ojm_images_optimized`.`url_image_original`
GROUP BY    `ojm_images_optimized`.`url_image_optimized`
ORDER BY    `ojm_pages_articles_photos`.`id_pages_articles_photos` DESC,
            `ojm_images_optimized`.`optimized_is_smaller`='Oui'

I used AND  (ojm_pages_articles_photos.url_articles_photo=ojm_images_optimized.url_image_original) GROUP BY ojm_images_optimized.url_image_optimized

to select where the file is in both tables and group it but there sometimes where the files are only in table ojm_pages_articles_photos but not in the other.

How to select from those two tables where the rows may be or may not be equal and group it if they are equal ?

FULL JOIN is not implemented in MySQL.

Possible realization (model):

SELECT filename,
       CASE SUM(table_from) WHEN 1 THEN 'In first table'
                            WHEN 2 THEN 'In second table'
                            ELSE        'In both tables'
                            END AS where_it_is
FROM ( SELECT filename, 1 AS table_from
       FROM table1
       UNION DISTINCT
       SELECT filename, 2
       FROM table2 ) AS combined_data_array
GROUP BY filename

If the field in question is unique in each separate table then use UNION ALL instead of UNION DISTINCT.


Is there a PostgreSQL proxy running queries on several servers at once? Let's assume we had PostgreSQL version X.Y and decided to give a test-try to version Z.W. Probably, there are some changes between X.Y and Z.W that could cause a little bit of trouble :-)

It would be preferable to run Z.W in parallel with X.Y, checking whether it goes well and using only X.Y's replies for a while. Later on, having Z.W's DB "ok" we could use it as primary, discarding X.Y optionally.

Question: Is there any PostgreSQL proxy that can run queries on several servers at once?

Have you checked pgpool already? It has query replication as a feature (look for 'parallel query').


For Oracle, we mount the instance to the database or mount the database to the instance? The following text is extract from oracle documentation.

  Start the instance, and mount and open
  the database. This can be done in
  unrestricted mode, allowing access to
  all users, or in restricted mode,
  allowing access for database
  administrators only.

  ....

  To mount a database to a previously
  started, but not opened instance, use
  the SQL statement ALTER DATABASE with
  the MOUNT clause as follows:

  ALTER DATABASE MOUNT;

You can read it directly from http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/start.htm

The first part gives me an impression that we are mounting the instance to the database. But the To mount a database to a previously started, but not opened instance part gives me an impression that we are mounting database to an instance.

Which one understanding is right? Anyone can help to explain it please?

Thanks

We are mounting a database to an instance. See the concepts guide:

  The instance mounts a database to
  associate the database with that
  instance.

One way I've seen it explained (probably here or on SO) is that the instance is the memory structures and processes, and the database is the data on disk. The instance has to have a database associated with it to be able to do anything useful.


If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then? We have an issue with MySQL InnoDB performance.
We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts processing them, it changes this status field to 1, then in case of an exception, the status goes to 2 or in case of successful processing, the status is set to 3.
The records remain in this table for three days then they are deleted. This way, the record count grows to around 1.7M and remains there.

If I run this:

describe SELECT * FROM temptable where status=0

It shows around 900k examined records, and the query itself returns 0 matching rows in about 5 seconds. There is an index on the status field. If I drop this index and recreate it, then the examined rows value goes down to 1 and starts to grow again, as new records are inserted.

If I query records with the STATUS value of 2 (Exception state), and none of these records existed ever, then the examined row count stays at 1 and the query returns instantly.

SELECT * FROM temptable where status=0
0 row(s) returned   5.538 sec / 0.000 sec
SELECT * FROM temptable where status=2
0 row(s) returned   0.000 sec / 0.000 sec

You can reproduce the same behaviour by creating a table like this:

CREATE TABLE temptable (
    someprimary int(11) NOT NULL AUTO_INCREMENT,
    status int(11) NOT NULL,
    PRIMARY KEY (someprimary),
    KEY IND_STATUS (status)
) ENGINE=InnoDB;

Fill it with around 100000 zero values, then update some zeros to ones:

update temptable set STATUS=1 where mod(someprimary,11)=0

Set the remaining zeros to 3:

update temptable set STATUS=3 where STATUS=0

Now there are some STATUS=1 records, many STATUS=3 records and none with the value 0 or 2.  You can now run the aforementioned selects and see for yourself if the query where we search for 2 returns instantly and the search for 0 requires some noticeable time to complete.
Is there any way to solve this issue and get the results instantly if there are only between 0-10 records matching the query and there is an index on this field?

Update:

SELECT * FROM temptable where status=0;
Empty set (1.56 sec)

describe SELECT * FROM temptable where status=0;
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows   | Extra       |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const | 144651 | Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

SELECT * FROM temptable where status=2;
Empty set (0.00 sec)
describe SELECT * FROM temptable where status=2;
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | temptable | ref  | IND_STATUS    | IND_STATUS | 4       | const |    1 | Using index |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

(total record count is now 289303)
SELECT count(1) db, STATUS FROM temptable group by STATUS;
+--------+--------+
| db     | STATUS |
+--------+--------+
|  25808 |      1 |
| 262864 |      3 |
+--------+--------+
2 rows in set (1.55 sec)

Thank you!

DESCRIBE, alias EXPLAIN, estimates the number of rows.  In your case (frequent changing of status and deletion), the number are very likely to be very far off.  Do not trust it.

EXPLAIN gets its information from "statistics" about the distribution of the data in the table.  The statistics are updated at certain times.  Version 5.7 does the update at quite different times than 5.5.  (What version are you running?)

You have INDEX(status)?  Perhaps you should not; it is adding to the problem and not helping the solution in a consistent way.  How often are the status changes?  The DELETEs?  There may be workarounds.

The Optimizer will look at the statistics for status and incorrectly decided that status=0 was too common for using the INDEX, so it did a table scan instead.  On the other hand, the stats said that status=2 was rare, so it did use the index and 'instantly' discovered zero rows.

Alas, running an EXPLAIN, even immediately after running the equivalent SELECT does not necessarily give you the explain plan for that select.  (I suspect this explains the mysterious results you got.  I have almost never seen such happen.)

Your test case is not quite correct -- You have only 2 columns, the secondary key and the PRIMARY KEY, so you got Using index;  This is unlikely to happen in the 'real' table -- don't you have other fields?

Please try one more thing to help get to the root of this.  Rerun the tests with

FLUSH STATUS;
SELECT ... ;    -- (returning emptyset)
SHOW SESSION STATUS LIKE 'Handler%';

This will show whether it actually did an index (or table) scan, and was not just a caching problem, as hinted by @jkavalik .


Upgrading from 10.2g to 11g by incrementals I know it's possible to generate a full rman backup in a 10.2g database, move it to another server containing 11g, restore it there and finally do an upgrade.

Since I have to do this to a large database and there are several things involved (apps, servers, network, etc), it could take me a few days and not only one night.

Could I make a full rman backup in 10.2g, restore it in 11g, upgrade, and in the next days go making incremental rman backups in 10g and restoring it in 11g, until we decide to shutdown 10g and use 11g in production. Something like a standby database, but in this case current database would be 10g and standby database would be 11g.

Once you upgrade the destination database to 11.2, you won't be able to apply archived logs (or incremental backups) from a 10.2 database.  So no, this won't work.

It is, on the other hand, often possible to use an actual standby database to perform a rolling upgrade.  You can create the 11.2 database as a logical standby of the 10.2 database, let the SQL apply process keep the databases in sync, and then switch over to the 11.2 database when you are ready to migrate.  In a similar vein, you could also use Streams between the 10.2 database and the 11.2 database to keep the data synchronized and then switch over when you're ready to migrate.


Is it possible to redirect queries to another server? I want to set up a server that redirects queries. It would receive incoming SQL Server traffic, redirect to the actual SQL Server, and return results back to the client.

Is this possible? What are my options?

First Things that come to my mind:

1 Linked Servers
You can use a linked Server and forward all requests to it, but then the calling Code or Programm must use prepared procedures or views that use the linked Server.

2 SQL alias, but I guess this is not what you are looking for.

But I wonder why would you do that? I can't See advantages.

Think about license Costs. They may double if you Do this kind of scenario in a production Environment.

Monitoring and tracing : administrative effort increases and again Costs.

And Security. It's Trick to configure linked Server connection to reuse the calling Logins to make sure you dont Open All Gates to anyone who can access the "Proxy".


Delete From sys.views With Wildcards I can run a delete statement on my server, but anytime I add a where clause to my delete statement it throws an error of:

  Msg 10054, Level 20, State 0, Line 0
  A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This syntax throws the error ->

DELETE
FROM sys.views
WHERE sys.views.name LIKE '%DoNotNeed_%'

What am I setting up incorrectly in order to delete all views that are prefaced with Do Not Need?

An alternative dynamic SQL approach (which also includes schema, often important!):

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N',' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id]
WHERE v.[name] LIKE N'%DoNotNeed[_]%';

SET @sql = N'DROP VIEW ' + STUFF(@sql, 1, 1, N'') + N';';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

This will produce a command like the following, which is legal (you don't need a DROP VIEW command for every view):

DROP VIEW [dbo].[view1], [dbo].[view2], [dbo].[view3], ...;

When you believe it is correct (and note that PRINT might not output the entire command if it exceeds 8k), uncomment the EXEC.

Note that whatever solution you use, you might run into difficulties dropping certain views (for example, they could be referencing by objects that use WITH SCHEMABINDING). In which case you may want instead to use something a little more elaborate:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
BEGIN TRY
  DROP VIEW ' + name + N';
  PRINT N''   Dropped ' + name + N'!'';
END TRY
BEGIN CATCH
  PRINT N''Could not drop ' + name + N'!'';
  PRINT ERROR_MESSAGE();
END CATCH'
FROM
(
  SELECT name = QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
   FROM sys.schemas AS s
   INNER JOIN sys.views AS v
   ON s.[schema_id] = v.[schema_id]
   WHERE v.[name] LIKE N'%DoNotNeed[_]%'
) AS x;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Parts of this solution can be complicated if you have views with ' in their names. Don't do that.


Effectively handle 10-100 millions row table of unrelated data What are the common approaches to boost read/write performance of table with up to 100 millions of rows?

Table has columnSEGMENT_ID INT NOT NULL, where each segment has about 100.000-1.000.000 rows. Writes - all rows for SEGMENT_ID are inserted at once, no updates for SEGMENT_ID afterwards. Reads - are pretty often, I need good performance for SELECT * FROM table WERE SEGMENT_ID = ?.

The most obvious approach is creating new table for SEGMENT_ID dynamically, but dynamic tables means hacks with ORM or even native SQL query-framework. In other words you finish with code that smells.

You can also use sharding, right? Does database create new tables under the hood?

I can cluster the table by SEGMENT_ID. But will my inserts be clustered if I insert all segment-related data at once?

Also Postgres propose to use partitioning to handle very big tables.

Maybe there is some kind of magical index which will help me to avoid creating new tables dynamically or configuring sharding?

Any other options?

Using a simple BRIN index

TIAS.

Here is a table exactly as you described, worst case 100 million rows with 1 million rows per SEGMENT_ID

explain analyze
CREATE TABLE foo AS
  SELECT (x::int%100)::int AS SEGMENT_ID
  FROM generate_series(1,100e6) AS gs(x);

                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series gs  (cost=0.00..15.00 rows=1000 width=32) (actual time=21740.904..57589.405 rows=100000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 96685.350 ms
(3 rows)

That means we created the table in 1.5 mins. Here we're adding an index.

CREATE INDEX ON foo
  USING brin (SEGMENT_ID);
VACUUM ANALYZE foo;

Then we add another million rows. SEGMENT_ID = 142

explain analyze
INSERT INTO foo(SEGMENT_ID)
  SELECT 142
  FROM generate_series(1,1e6) AS gs(x);

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Insert on foo  (cost=0.00..10.00 rows=1000 width=0) (actual time=1489.958..1489.958 rows=0 loops=1)
   ->  Function Scan on generate_series gs  (cost=0.00..10.00 rows=1000 width=0) (actual time=174.690..286.331 rows=1000000 loops=1)
 Planning time: 0.043 ms
 Execution time: 1499.529 ms
(4 rows)

Adding a million rows took 1.5 seconds.. Now we select,

explain analyze
SELECT *
  FROM foo
  WHERE SEGMENT_ID=142;

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=52.00..56.01 rows=1 width=4) (actual time=4.401..140.874 rows=1000000 loops=1)
   Recheck Cond: (segment_id = 142)
   Rows Removed by Index Recheck: 24832
   Heap Blocks: lossy=4535
   ->  Bitmap Index Scan on foo_segment_id_idx  (cost=0.00..52.00 rows=1 width=0) (actual time=1.504..1.504 rows=46080 loops=1)
         Index Cond: (segment_id = 142)
 Planning time: 0.059 ms
 Execution time: 176.902 ms
(8 rows)

Selecting a million rows took 176 ms.

That's on a 5 year old x230 with a "Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz" and a single SSD. You can pick one up for a few hundred bucks and install Xubuntu it. Not exactly hard science either. I'm compiling Angular apps in the background.


Why SQL does not use the indexed view? I have the following version of SQL Server:

  Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
  Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

I created the following view

CREATE VIEW [dbo].[vwGroupsOfficesDependencies]
WITH SCHEMABINDING
AS
SELECT        GC.IdGroup, COUNT_BIG(*) AS countBig, OD.IdOffice
FROM            dbo.Group AS GC INNER JOIN
                         dbo.GroupDependencies AS GD ON GC.IdGroup = GD.IdGroup INNER JOIN
                         dbo.OfficeDependencies AS OD ON OD.IdDependency = GD.IdDependency INNER JOIN
                         dbo.Dependencies AS D ON D.IdDependency = GD.IdDependency
WHERE        (D.Active = 1)
GROUP BY GC.IdGroup, OD.IdOffice

Then I created the clustered index

CREATE UNIQUE CLUSTERED INDEX [IX_vwGroupsOfficesDependencies_IdOficeIdGroup] ON [dbo].[vwGroupsOfficesDependencies]
(
    [IdGroup] ASC,
    [IdOffice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

But when I run the query

SELECT IdGroup, IdOffice FROM dbo.[vwGroupsOfficesDependencies]

I see in the execution plan that it does not use the indexed view, it performs the joins with the tables inside the view. But if I include the hint WITH(NOEXPAND) it uses the indexed view.
I read that I must include WITH(NOEXPAND)  for non Enterprise version, but in my case it is not necesary. Why it is not using the indexex view?

Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins.

It may later chose to match part(s) of, or the whole plan, back to one or more indexed views later on in the optimization process.

There are two common reasons that the original expansion isn't reversed later:

The plan tree has been changed by prior optimizer activity in such a way that the indexed view no longer matches (or at least, can no longer be matched by the tools available to the optimizer).
The optimizer finds a good enough (low estimated cost) plan before it goes looking for indexed view substitutes.

There are other reasons to prefer NOEXPAND hints. For example, statistics are only automatically created on indexed views when that hint is present. See my article Another Reason to Use NOEXPAND hints in Enterprise Edition for more.


STRING_SPLIT and ordered results Can you assume that the result of the string_split function returns elements in order? In short, is the following code correct?

DECLARE @tags NVARCHAR(400) = 'clothing,road,touring,bike'
DECLARE @FIRST nvarchar(100);

SELECT TOP 1 @FIRST = value
FROM STRING_SPLIT(@tags, ',');

TOP 1 without ORDER BY could not be guaranteed to return the same results even if the output of STRING_SPLIT() were guaranteed (which it most definitely is not). While in real-world usage you may find it hard to come up with a counter-example where the data is not returned in order, this is a terrible kind of thing to do. Trusting that something always works because you've never seen it break is like assuming that if you put a deer sign on the highway that is the only place you'll ever see a deer.
However, let's look at another way we could solve this. Why don't we locate the position of each individual string in the overall list:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',';

SELECT value, CHARINDEX(@c + value + @c, @c + @tags + @c)
  -- we surround the value and the string with leading/trailing ,
  -- so that cloth isn't a false positive for clothing
  FROM STRING_SPLIT(@tags, ',') AS t;

Output here is:

Do you think we could use this to determine the first element in the list? Of course! Let's try again:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @first nvarchar(100),
  @c nchar(1) = N',';

;WITH t AS
(
  SELECT value, idx = CHARINDEX(@c + value + @c, @c + @tags + @c)
  FROM STRING_SPLIT(@tags, ',')
)
SELECT TOP (1) @first = value FROM t ORDER BY idx;

PRINT @first;

Output:
clothing

You could use the same logic to find the last element, just change the ORDER BY idx to ORDER BY idx DESC. In fact, you could use this logic to return the nth string in the list:
DECLARE
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',',
  @nth tinyint;

SET @nth = 3;

;WITH t AS
(
  SELECT value, idx = ROW_NUMBER() OVER
    (ORDER BY CHARINDEX(@c + value + @c, @c + @tags + @c))
  FROM STRING_SPLIT(@tags, ',')
)
SELECT * FROM t WHERE idx = @nth;

Result:
touring

As a disclaimer, if you have duplicates, that's going to mess things up, because the index value will always represent the first appearance of that value in the string. You can switch from ROW_NUMBER() to DENSE_RANK() but that won't solve for all cases. You can de-dupe the string first (I talk about some edge cases here).


Is it possible to determine the default Collation and server version of a database without a full restore? I work for a company who regularly receives large SQL database backups from clients for support purposes. We support multiple SQL versions and collations, but currently have to manually confirm our client's SQL versions before restoring to the correct SQL instance. I am wondering if there is a way to automate this process.

I am looking to:

Find the SQL Server version a database was backed up from (we get 2008 R2 as well as 2014 and 2017)
Find the database's default Collation

Without requiring a full restore to a potentially incorrect SQL instance, which takes a significant amount of time due to their size. (maybe a piecemeal restore?)

You can get some info by inspecting a backup file using the RESTORE HEADERONLY command. The linked documentation explains what all of the result set fields are and mean, but the ones you are looking for should be:

[SoftwareVersionMajor]

8 = SQL Server 2000
9 = SQL Server 2005
10 = SQL Server 2008 or 2008 R2 (see [SoftwareVersionMinor] for distinction)
11 = SQL Server 2012
12 = SQL Server 2014
13 = SQL Server 2016
14 = SQL Server 2017
15 = SQL Server 2019

If you might be getting SQL Server 2008 R2 backups, then you will also need [SoftwareVersionMinor] as this value should be "50" for that version, else "00" for all others (so far)
[Collation]

For example:

RESTORE HEADERONLY FROM DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'

That will return a result set. If you are executing this from app code, it should be fairly easy to grab those specific fields. If you are executing this from T-SQL, and you need to act on it programmatically, then you will need to:

create a local temporary table with all possible result set fields from that command
execute the RESTORE HEADERONLY command within an INSERT INTO ... EXEC(...) construct:

INSERT INTO #TempBackupInfo
  EXEC(N'RESTORE HEADERONLY FROM DISK =
  N''C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\master.bak'';');

select the fields in question from that local temp table


MS SQL Server 2008 R2 network backup Can MS SQL 2008 R2 backup database to a file on different network location...I've been unable to set network path for backup to other machine on local network via tasks-backup-destination.add location...

I believe you can. We backup to a network location although we have the location mapped to a drive.

It seems you can use a UNC as well though according to this answer http://social.msdn.microsoft.com/Forums/sqlserver/en-US/965cd9c4-f4d5-4492-a202-f09b78d89ed1/how-to-backup-a-database-to-a-network-drive?forum=sqltools.

Have a look at the answer as there's some good considerations on here like if the sql service login has access to the network location.


How to limit select output from sqlplus? I stop using SQL Developer as found more convenient to use SQL Plus inside Emacs SQLi mode.

SQL Developer on selects with large amount of rows show only few rows. But SQL Plus tend to dump all rows that match conditions. I can put ROWNUM <= N but I often forget to do this.

Is there any option for SQL Plus to limit it output from select statement? I will put it to /opt/instantclient/glogin.sql in order to apply to each sesstion...

I don't think you can. The only thing I know that sort of does that (but is slightly annoying IMO) is the PAUSE option which waits for you to press enter between pages. The annoying part is that it pauses before the first row... You can Ctrl C to interrupt when you've seen enough.

SQL> set pause on
SQL> set pages 10 pause 'press enter to continue'
SQL> select object_name from dba_objects;
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
CON$
UNDO$
C_COBJ#
I_OBJ#
PROXY_ROLE_DATA$
press enter to continue

OBJECT_NAME
--------------------------------------------------------------------------------
I_IND1
I_CDEF2
I_OBJ5
I_PROXY_ROLE_DATA$_1
FILE$
UET$
I_FILE#_BLOCK#
press enter to continue

I_FILE1
ERROR:
ORA-01013: user requested cancel of current operation

15 rows selected.


SQL Query Store qds_async_queue I've recently inherited a SQL Server 2017 instance with performance issues and I've been able to remediate the low hanging fruit. However, one substantial wait still remains.

The Query Store - qds_async_queue seems to be significantly high. But I'm unable to find any meaningful documentation or insight. Microsoft's online description has been at 'TDB' since SQL 2016.

Does anyone have any insight or helpful information about this wait?

Please note that the official documentation is the official source of information.
I took a brief look and this shouldn't be taken as an extreme look from every possible angle.

The wait type is benign as it's a permanent task that which is persisting QDS data in the background. The wait shows up and is incurred while the instance is up and running, it will have more incurred wait if there are items to persist and lower if there are less items to persist.

So, overall, unless there are other issues going on, this wait type can be ignored.


How to make dataguard switchover seamlessly In my Dataguard configuration on a 12c environment, when I perform a switchover from primary (foo) to standby (bar) and then try to sqlplus in with user@foo, I receive the "ORA-011033: ORACLE initialization or shutdown in progress" error instead of being able to connect to the new primary(bar).

Now, I know that this is because the connection is still pointing to the foo address instead of the bar address, and according to many articles, you need to create a new "virtual" service to handle the different connections, as well as a trigger to stop/start this service after a rolechange.

I did this and with the service_names parameter set to this new "virtual" service, a switchover will work and can also switch back but when trying to sqlplus with user@foo it throws ORA-011033. The trigger is useless and the service continues to run on both foo and bar because it seems that having that parameter set overrides whatever the trigger tries to do.

Now if I don't define that parameter, my switchover works fine, and the triggers do their job, and when I sqlplus user@foo, it will correctly redirect me to the bar address. However, when I try to switchover back to foo, I receive the following in the DG alert log:

Redo transport problem detected: redo transport to database foo has the following error:
ORA-16047: DGID mismatch between destination setting and target database
08/05/2016 16:01:02
Initialization of connection failed.
Expected destination db_unique_name is foo.
Database actually reached is bar
Failed to send message to site foo. Error code is ORA-16642.
Data Guard Broker Status Summary:
Type                        Name                             Severity  Status
  Configuration               foo_dg                         Warning  ORA-16607
  Primary Database            bar                            Error  ORA-16778
  Physical Standby Database   foo                              Error  ORA-16642

ON FOO:

  log_archive_dest_2='service="bar"','ASYNC NOAFFIRM delay=0 optional
  compression=disable max_failure=0 max_connections=1 reopen=300
  db_unique_name="bar"
  net_timeout=30','valid_for=(online_logfile,all_roles)'
  fal_client='foo' fal_server='bar'

ON BAR:

  log_archive_dest_2='service="foo", ASYNC NOAFFIRM delay=0 optional
  compression=disable max_failure=0 max_connections=1 reopen=300
  db_unique_name="foo" valid_for=(online_logfile,all_roles)'
  fal_client='bar' fal_server='foo'

Entries in LDAP:
foo:

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=foo_home)(PORT=15940))(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=virtual_foo)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))))

bar:

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=bar)))

I'm not sure what I'm missing or misunderstanding. Can anyone shed any light as to what I might be doing wrong? Can provide more info if needed.

Starting with 11.2, you do not need to create the trigger, you can define a role based service with Grid Infrastructure.

foo:

srvctl add service -db foo -service virtual_foo -role primary
srvctl start service -db foo -service virtual_foo

bar:

srvctl add service -db bar -service virtual_foo -role primary
srvctl start service -db bar -service virtual_foo

If you do not use Grid Infrastructure, well, just use the trigger.

Your virtual service was misconfigured, that is why you received ORA-01033. You should not update the value of service_names manually. After you did that and performed the switchover, virtual_foo service still ran on foo. Your client tried the first address in the lists, found that the listener was alive, and a service existed with the requested name, so it tried to log in. But the standby database was in MOUNT state, so the client could not log in --> ORA-01033.

For the switchover back to foo part: do not use the same TNS entries for clients and Data Guard configuration. Clients do not need to know which site they connect to, but Data Guard Broker and database background processes need to connect exactly to foo or bar, a virtual service does not provide that functionality. Seperate the TNS entries needed for Data Guard configuration and the one used by clients.

Usually I configure DG with the below naming and TNS entries.

If the db_name is foo, then:

primary: db_unique_name = foo_site1
standby: db_unique_name = foo_site2

(or something similar, that identifies the site)

TNS:

foo_site1: TNS entry pointing to the host at site1, with service_name
defined as foo_site1 - used by DG configuration
foo_site2: TNS entry pointing to the host at site2, with service_name defined as foo_site2 - used by DG configuration
foo : TNS entry pointing to both sites, with service_name defined as foo (and that is defined as role based service) - used by clients

Finally, this part in your connection string:

(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))

Unless you have RAC databases with multiple instances per site, and foo_home and bar_home are actually SCAN names, this is unnecessary. These parameters are used to configure Transparent Application Failover (session failover between RAC instances).

By the way, this is far from a really seamless switchover, all that was achieved by this, was that clients can use a single connection string to connect to the database after a role transition.

Client sessions will fail and need to reconnect manually, that could take minutes. Or you need to stop your applications and have planned downtime for this.

Having a really seamless switchover requires far more work (like configuring ONS, FAN). More details at:

Client Failover Best Practices for Highly Available Oracle Databases

Fast Application Notification


How to change "tmpdir" on a running MySQL instance Is there a way to change the tmpdir on a MySQL server without restarting it?
The issue is that I use a tmpfs mounted at /var/lib/mysqltmp but sometimes it's overflowing and causes issues. I don't want to restart the server, because that means around 2-3 hours of service degradation.

Another possible exit from this situation is to unmount the tmpfs, but trying it causes this error:

 [root@db2 /]# umount /var/lib/mysqltmp/
 umount: /var/lib/mysqltmp: device is busy.
    (In some cases useful info about processes that use
     the device is found by lsof(8) or fuser(1))

Seems like there is no easy and unobtrusive way to do it. However there is a way to increase the size of the tmpfs.

Change the size in /etc/fstab accordingly
Issue a mount -o remount and it will be resized without a need to release file handle locks.


SOA + Fusion + Weblogic + Oracle 11g - STUCK threads This is sort of a last-ditch effort.  We have Oracle SOA on Fusion, 11.1.1.5.0 (+ patches on a separate server) running, and nearing the point where we're taking it into production.  I've posted on OTN and talked to numerous folks at Oracle about this problem, but no one can seem to help me.

When under production-level load (simulating about 1 request every 30 seconds) Weblogic encounters STUCK threads and SOA simply stops processing composite instances.  The composite that fails changes from time to time, and the logs indicate that deadlocked thread is trying to load a WSDL or referenced schema file in MDS.  We have several shared schema and WSDL files across many composites on multiple partitions.  Each SOA partition has its own MDS repository.  The end result is that the Weblogic server needs to be restarted before processing can proceed.  There's nothing logged in the audit trail and nothing blatantly evident in the server's diagnostics logs.  I upped the logging to the most verbose level, and can't personally see a pattern.  I have since given Oracle the best possible logs and thread dumps available.

In the audit trail, I can click into the faulting mediator/composite for details, and the call graph shows that it calls the related service, but there's no link, and viewing the invoked composite in EM does not show an entry related to that invocation.

Is this a familiar symptom to anyone?

Should be addressed by patch 12573835.  Hopefully fixed for good in 12.x.

Edit - patch 12573835 is moderated & password-protected on support.oracle.com.  Its recommended only for BPM customers, which we are not, so no support has been given.  We have applied the January SOA critical patch and are still seeing the issue under load ("load" meaning 2 requests per minute, not much of a load but whatever, we can disagree).  So, there is no acceptable solution at the moment.


How to set constraints on UDT in SQL Server 2005 - 2012 I'm creating my UDT:

CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL

As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that columns defined from this type contain only specific characters?

Currently, the only way to truly centralize/encapsulate this kind of logic (which is what I assume you're trying to do) is to use a CLR type. The good news it that this solution works with 2005+.

One alternative is to centralize the validation logic into a UDF, and then create individual check constraints that reference the UDF. If you're not using CLR already, this may be the best solution.

CREATE TYPE is very limited: when referencing a system type, it's just a type alias and nothing more. It would be nice to, for example, declare a check constraint (or something similar) that's associated with the type itself, but this is currently not implemented.


Adding unsigned 256 bit integers in PostgreSQL I was wondering if there would be any way to do the following in PostgreSQL:

UPDATE cryptotable SET work = work + 'some big hexadecimal number'

where work is an unsigned 256 bit number. Right now my column is a character varying(64) column (hexadecimal representation) but I would be happy to switch to another data type if it lets me do the operation above.

If it's not possible with vanilla PostgreSQL, are there extensions that could help me?

(I also posted this to pgsql-hackers so check out the thread there for more ideas.)

Using pg-bignum

The pg-bignum library does what you want. I just patched it to accept hex input.

Download the library. Run

make
sudo make install

Now, run

CREATE EXTENSION bignum;

Now you can take a 256 bit number like this

59 51 a1 20 2e 65 62 26 ab f3 af e6 93 16 db 1c
3b 37 24 47 75 32 72 95 79 e9 d7 fa 38 12 c0 0e

And feed it to bn_hex_in(),

SELECT bn_in_hex('5951a1202e656226abf3afe69316db1c3b3724477532729579e9d7fa3812c00e');
                                   bn_in_hex
-------------------------------------------------------------------------------
 40400070184923956455012790534029088594440775740385060328118871212830923866126
(1 row)

You can confirm that with Wolfram Alpha

Size advantages

Numeric is pretty big, from the docs

  Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

The above example in numeric is 46 bytes (you can check with ::text::numeric), as a bignum it's 36.


What does it mean that sp_who2 is not documented? I am reading a book about SQL Server administration where I encountered this sentence

  sp_who2 is undocumented stored procedure which gives you more verbose information about each processes (in comparison with sp_who).

What does it mean that sp_who2 is undocumented? Is it deprecated or is the use of it not recommended?

Thanks for your time.

It is "not documented" means that it is not part of any officially published API so there is no implicit or explicit promise that it will work in any given way, keep working at all, or not just vanish at any time (perhaps a security hole is found relating to it so MS just decide to disable it, temporarily or permanently, because fixing it quickly isn't practical).

It is unlikely that this will happen, it is likely that it will stay around in its current form, but there are absolutely no guarantees of this so if you choose to rely on it you do so at your own risk.

Also as the behaviour of the call is not defined in any official documentation MS have no obligation to fix any bugs in it in a timely manner or at all (as there is no official documentation that describes its behaviour any behaviour it exhibits could be considered correct).

On a related note: all the information it outputs is available from documented system tables and views (you can script out its code from master to see what it does) so you could write your own that collects even more information to output - for years I had my own "sp_who3" that did just that. These day's I use Adam Machanic's sp_whoisactive instead.


Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app.

I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (ed. fixed code after answer below)

do $$
declare
target text;
begin
    for target in
        select quote_ident(column_name)
        from information_schema.columns
        where table_name = 'cds' and data_type = 'character varying'
    loop
        RAISE NOTICE 'Calling trim(%)', target;
        execute 'update cds set '
            || quote_ident(target)
            || ' = trim('
            || quote_ident(target)
            || ')';
    end loop;
end
$$;

The select gives my a nice list of all the column names in my table and I was hoping to for-loop through all of those:

gakera=# select column_name from information_schema.columns
         where table_name = 'cds' and data_type = 'character varying';

 column_name
-------------
 userID
 junk1
 dates
 times
 junk2
 junk3
 servID
 junk4
 junk5

Sidetrack

Not the question, but just extra confusing.

The fun starts at \i ~/path/test.sql with this error:

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  function quote_ident(record) does not exist
LINE 2:    || quote_ident(target)
              ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

What's going on with quote_ident? I'm gonna try a sanity check, I copied this from http://www.postgresql.org/docs/9.3/static/functions-string.html

gakera=# select quote_ident('test');
 quote_ident
-------------
 test

Ok, insanity check then:

gakera=# select quote_indeed('test');
ERROR:  function quote_indeed(unknown) does not exist
LINE 1: select quote_indeed('test');
               ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

Ugh, whatever, I'll just remove the quote_identfunction from the offending lines (it didn't complain about the select quote_ident part so I keep that. Now I have:

    execute 'update cds set '
        || target
        || ' = trim('
        || target
        || ')';

Question

Here is where things start to get interesting. when I run this like before I get:

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  syntax error at or near "trim"
LINE 1: update cds set (anum) = trim((anum))
                                 ^
QUERY:  update cds set (anum) = trim((anum))
CONTEXT:  PL/pgSQL function inline_code_block line 11 at EXECUTE statement

Why is it adding () around anum? How can I get rid of them? The EXECUTE statement should read update cds set anum = trim(anum)

@klin already addressed the bug you had been asking about. I am going to address your general approach.

Your DO statement would run a separate UPDATE for every column - which is almost n times as expensive as updating n columns at once.
Due to PostgreSQL's MVCC model this also produces n dead tuples in the table, leading to excessive table bloat and a lot of work for VACUUM.

What's worse, it updates every row n times even if nothing changes at all.

I suggest a much more efficient approach:

Update every row once.
Only update if anything actually changes.

CREATE OR REPLACE FUNCTION f_trim_cols(IN _tbl regclass, OUT row_ct integer
                                                       , OUT col_ct integer)
  RETURNS record AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT format('
      UPDATE %s SET (%2$s) = (trim(%3$s))
      WHERE (%2$s) IS DISTINCT FROM (trim(%3$s))' -- test if anything changes
                , _tbl
                , string_agg (quote_ident(attname), ', ')
                , string_agg (quote_ident(attname), '), trim(')
                )
        , count(*)::int  -- count columns
   INTO   _sql, col_ct
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    atttypid IN ('text'::regtype, 'varchar'::regtype) -- basic string types
   AND    NOT attisdropped
   AND    attnum > 0;

   -- RAISE NOTICE '%', _sql;  --  debug
   EXECUTE _sql;  GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_trim_cols(regclass) IS 'Trim all columns of table _tbl ($1).
Return count of applicable columns (col_ct) and rows actually changed (row_ct) .
Call:
   SELECT * FROM f_trim_cols($$city$$)   --';

Produces SQL code like:

 UPDATE city SET (city, country) = (trim(city), trim(country))
 WHERE (city, country) IS DISTINCT FROM (trim(city), trim(country));

I also target text as well as varchar columns.
Everything is sanitized and safe against SQL injection.
More explanation, links and a fiddle in this related answer on SO:

Update multiple columns that start with a specific string


Correct way to stop SQL Server (through automation) Question

Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands?
If not, is there a recommended way to start & stop SQL's services through automation?

Additional Info

We're in the process of creating scripts to bring down our systems cleanly before maintenance windows, then to bring them back up afterwards.
By cleanly I mean it stops the application's services, then stops the database services (i.e. in dependency order), setting all to DISABLED, so that the infrastructure team can apply windows updates / perform any work, safely rebooting servers as required, before running the scripts to bring the system back up in the correct (dependency aware) order.

Our DBA mentioned that the correct way to stop services is through SQL's Configuration Manager; implying that that's different to just using services.msc (or for automation, powershell's stop-service / command line's sc \\someServer stop someService.

The Note at the top of this article implies something similar: https://technet.microsoft.com/en-us/library/ms175516(v=sql.105).aspx
This article points out that SQL Server Configuration Manager has some advantages for some functions: https://msdn.microsoft.com/en-us/library/ms174212.aspx

However I've not been able to find anything explicitly saying it's incorrect to start/stop services through the regular means, or what extra protection/functionality SQL Configuration Manager gives with regards to simply starting & stopping services.

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager...

Configuration manager is also used to control instances, etc etc but the function of starting and stopping a service is the same no matter which method you choose.

Some people have their own ways of doing things, for example to quit an application, you can do File > Exit. Or you can do ALT+F4, they are both the same function...

Please view this link below for additional information and helpful details. This covers all of the different methods (SQL configuration mgr, SSMS, Commandline etc)

https://msdn.microsoft.com/en-us/library/hh403394.aspx

UPDATE regarding Failover Cluster Instance...

If the instance is clustered, use of any of the above suggestions does not apply and should not be used. For a FCI you need to utilize Cluster Manager or the FailoverClusters PowerShell module commands.


how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data I need to find the marks in ‘Math’, of ‘English’ topper for each class in MySQL for student result data.

Output schema: class, roll_no, math_marks

(Here expected result image not exact answer)

my table is as,

CREATE TABLE resultdata
(
  class TINYINT UNSIGNED NOT NULL,
  roll_number TINYINT UNSIGNED NOT NULL,
  subjects VARCHAR (15) NOT NULL,
  marks TINYINT UNSIGNED NOT NULL
);

Populate it:

INSERT INTO resultdata(class,roll_number,subjects,marks) VALUES
(8, 1, 'math', 98),
(8, 1,'english', 88),
(8, 1,'science', 96),
(8, 1,'computer', 94),
(8, 2,'math', 78),
(8, 2,'english', 89),
(8, 2,'science', 87),
(8, 2,'computer', 99),
(9, 1,'math', 90),
(9, 1,'english', 85),
(9, 1,'science', 85),
(9, 1,'computer', 85),
(9, 2,'math', 95),
(9, 2,'english', 30),
(9, 2,'science', 40),
(9, 2,'computer', 80),
(10, 1,'math', 85),
(10, 1,'english', 90),
(10, 1,'science', 65),
(10, 1,'computer', 45),
(10, 2,'math', 50),
(10, 2,'english', 95),
(10, 2,'science', 65),
(10, 2,'computer', 85),
(10, 3,'math', 51),
(10, 3,'english', 61),
(10, 3,'science', 71),
(10, 3,'computer', 81);

This question intrigued me - the requirement to have the mark for Mathematics for the class (regardless of role) which (who?) obtained the best mark in English.

I decided to go one better and obtain the results for all subjects for the best in class in English - fiddle available here.

The SQL:

SELECT t1.class, t1.roll_number,
       t1.sci_mark,
       t1.comp_mark,
       t1.math_mark,
       t1.eng_mark
FROM
(
  SELECT
    class,
    roll_number,
    SUM(CASE WHEN subjects = 'math'
      THEN marks ELSE 0 END) AS math_mark,
    SUM(CASE WHEN subjects = 'english'
      THEN marks ELSE 0 END) AS eng_mark,
    SUM(CASE WHEN subjects = 'science'
      THEN marks ELSE 0 END) AS sci_mark,
    SUM(CASE WHEN subjects = 'computer'
      THEN marks ELSE 0 END) AS comp_mark
  FROM resultdata
  GROUP BY class, roll_number
) AS t1
JOIN
(
  SELECT class, MAX(marks) AS maxmark
  FROM resultdata
  WHERE subjects = 'english'
  GROUP BY class
) AS t2
ON
  t1.class = t2.class AND
  t1.eng_mark = t2.maxmark
ORDER BY class, eng_mark DESC;

Result:

class   roll_number sci_mark    comp_mark   math_mark   eng_mark
    8             2       87           99          78         89
    9             1       85           85          90         85
   10             2       65           85          50         95

A glance at the other solution here shows that it matches on the Mathematics score for the highest English mark. Inspection shows that it works for the two other subjects (Science and Computers) also. As pointed out here MySQL does not have a PIVOT function, but I got this technique from that site.


All queries on the server have slowed down, cpu is normal, plenty of free memory but SQL is using very little The other day I logged onto our server to see SQL was using 100% cpu and the server was slowed to a crawl. I decided to stop SQL and restart it. However, it refused to stop properly and stopped responding. After playing around with it eventually I got it restarted and running again.

However, all queries against it are now going 10-30 times slower, and in some cases affect the performance of the server.

I've been googling for solutions but I can't see anything addressing a global issue with SQL running slow. The CPU usage isn't abnormal and there is plenty of free ram - in fact, SQL is using only about 300mb whereas previously it used to be around 20gb, which may be part of the issue.

I created a brand new database and when I attempt to populate it, it goes about 30 times slower than it used to, and the server becomes very unresponsive.

Could it be something simple that needs resetting or a repair that needs doing after it broke before?

When you're trying to find out why a SQL Server is slow, start by checking these things:

In Task Manager, is the CPU consumer actually sqlserver.exe, or something else? If it's something else, troubleshoot that first.
If it's actually sqlserver.exe, check what queries are running right now. The easiest way is with Adam Machanic's excellent sp_WhoIsActive. Here's how to run it.
Check the server's wait stats. My favorite way to do this is with sp_AskBrent, my stored proc that takes a 5-second sample of wait stats plus checks some diagnostic queries.

For more detail, check out these resources:

Our First Responder Kit - a PDF checklist of things to look at
The free book Troubleshooting SQL Server - I like this one because it's a problem/solution approach. You don't have to read the whole thing to get value out of it - just put it on your virtual shelf and when you've got a CPU problem, turn to the CPU chapter.


alter public database link to private in oracle I have a database link in my database which is created as public as below.

CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO usernane USING 'tag';

I have a requirement to convert it to a PRIVATE database link.
Is there any way to ALTER it without recreating it?

Many thanks.

No.  You'll need to create a new private database link.  Public database links (and synonyms) are owned by a different user than private database links (and synonyms) and you can't alter an object to change the owner.


Implications of adding a trigger on production database I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day.

When I try to add a trigger to this table, SQL Server takes too much time - until I stop the execution or SQL Server throws a deadlock error.

I need to run this trigger on production. How can I add a trigger with minimal impact on running the production database?

The trigger is just inserting a record in a audit table 100% similar to https://www.mssqltips.com/sqlservertip/5909/sql-server-trigger-example/

To create a trigger on a table a Sch-M lock must be taken on that table. SQL Server allows a single schema modification lock (Sch-M) on any locked object, so a transaction which creates the trigger must wait to acquire a Sch-M lock on the target object.

While a process is waiting to acquire a Sch-M lock it is blocking other processes which can try to begin using the table.

You should try to create the trigger during your maintenance window when the table is free from other locks.

The Sch-M lock is Evil - this is on indexes rebuilding but lock mechanics is the same.


Change Order in Pivot Table I write some pivot query in oracle but I have some problem with order data :

   Select * FROM
(
SELECT
    ve_nazwisko PH,
    odbiorca,
    CONCAT(CONCAT(nazwa1,' '),nazwa2) FIRMA,
    segment2017,
    segment2018,
    symbol_kraju,
    nazwa_kraju,
    netto,
    zysk,
    CONCAT(CONCAT(miesiac,' '),ROK) Data
FROM
    v_eksport_arkusz_4_6
    WHERE ROK >= '2017'
)
PIVOT
(
SUM(netto) as netto,
SUM(zysk) as zysk
FOR Data
IN('01 Sty 2017','02 Lut 2017','03 Mar 2017','04 Kwi 2017','05 Maj 2017','06 Cze 2017','07 Lip 2017','08 Sie 2017','09 Wrz 2017','10 Paź 2017','11 Lis 2017','12 Gru 2017',
        '01 Sty 2018','02 Lut 2018','03 Mar 2018','04 Kwi 2018','05 Maj 2018','06 Cze 2018','07 Lip 2018','08 Sie 2018','09 Wrz 2018','10 Paź 2018','11 Lis 2018','12 Gru 2018',
        '01 Sty 2019','02 Lut 2019','03 Mar 2019','04 Kwi 2019','05 Maj 2019','06 Cze 2019','07 Lip 2019','08 Sie 2019','09 Wrz 2019','10 Paź 2019','11 Lis 2019','12 Gru 2019'
))

And displaying data they are not something I expected.

Now displaing this:

January 2017 - Netto,January 2017 Zysk,February 2017 Netto,February 2017 Zysk and something like this to 2019

I want display pivoting data in the following order :
- January 2017 Netto,February 2017 Netto,March 2018 Netto and to last 2017 months next January 2017 Zysk ,February 2017 Zysk,March 2018 Zysk and to last 2017

And this data loop to 2019

Somthing like in pivot table in Excel :

If you want to retrieve columns in a specific order, then select columns in that specific order.

drop table pivot_test;
create table pivot_test
(
  c0 number,
  c1 varchar2(30 char),
  c2 number,
  c3 number
);

insert into pivot_test values (0, 'A', 1, 5);
insert into pivot_test values (0, 'B', 2, 5);
insert into pivot_test values (0, 'C', 3, 5);
insert into pivot_test values (0, 'D', 4, 5);
commit;

select * from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d));

        C0    A_NETTO     A_ZYSK    B_NETTO     B_ZYSK    C_NETTO     C_ZYSK    D_NETTO     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          5          2          5          3          5          4          5

select c0, a_netto, b_netto, c_netto, d_netto, a_zysk, b_zysk, c_zysk, d_zysk from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d));

        C0    A_NETTO    B_NETTO    C_NETTO    D_NETTO     A_ZYSK     B_ZYSK     C_ZYSK     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          2          3          4          5          5          5          5


Fast backup & restore of MySQL database to revert recent changes I have a decently big MySQL DB (InnoDB) with size about 400 MB. Thus dumping the whole DB with mysqldump and restoring it back takes forever (more than 15 minutes, which is unacceptable).

For debugging purposes I need to run my code against a certain "version" of the DB and then revert all changes back (if any) and try again (possibly multiple times). In order to reproduce a bug, I need to always start from the same version of DB.

Is there any way to manually instruct the DB to create a point-in-time snapshot and then restore it back on demand? This should ideally take just a couple of seconds (I imagine some copy-on-write method would be used).

I'm looking for a solution that is independent from the end application. Imagine a web application that you need to run your robot tests on (which includes multiple request/response cycles). Moreover, if we treat the application as possibly buggy we can't trust it to reliably manage transaction rollbacks either.

I just tried to backup /var/lib/mysql with rsync. Cannot even start MySQL after restore, probably there's some file left somewhere that breaks the integrity. Anyway, I was looking for a more higher level solution, resorting to FS-level backups only as a last hope.

Copying the data directory, as you mentioned, is one way to accomplish what you need. Here are the steps, and if you face any error, please post it.

Shut MySQL server down; or in a terminal flush tables with lock FLUSH TABLES WITH READ LOCK [Important: Keep the terminal open]
Copy the data directory: cp, or rsync
Restart the server if you shut it down; or go back to the terminal and unlock the tables: UNLOCK TABLES
Make multiple copies of the copy you already made
Chnage the owner of the newly created directories to mysql: chown mysql:mysql dir_name (I think it is chown -r, so it runs recursively)

So far, you have multiple copies of the data at the point of time that you need.

In order to "restore" a copy, you can do one of the following:

Change the value of datadir in my.cfg (or my.ini) to a location of one of the data copies, and restart the server; OR
Shut down the server and copy the contents of a copy of the data back to the original data directory, and start the server again.


Hmmm.... this is an interesting one. The Oracle optimizer is a bit of a black box as far as mere mortals like myself are concerned... Jonathan Lewis wrote a 536 page book on the topic and that's just the **fundamentals* (and it ain't bedtime reading!).

Two questions.

a) Did you flush the caches before/after each query - maybe the first affects the second - i.e. the optimizer figures out after the first query that it's not worth using an index?

b) What happens if you bump up those numbers in 'M' and 'D'? Does there come a point where it switches back to an INDEX scan? I'm guessing that such a point will come (but see point a)

Just a couple of thoughts - I don't have an Oracle system to hand.


Modify all array entries in all records of a table at once I have a schema that involves an array of timestamps. Due to an import problem all the timestamps are off by one hour (time zone issue). I'd like to update all the timestamps in all the records with one UPDATE statement.

You can update all the array entries like this:

UPDATE ambient_properties ap
SET recording_time =
    (
        SELECT ARRAY(
            SELECT unnest(ap.recording_time) - interval '1 hours'
        )
    );

The same strategy should also work for values of other types.

SQLFiddle


how I use multiple SUM with multiple LEFT JOINS SELECT
`members`.`id` AS `aid` ,
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `amount` ELSE 0 END) AS `deduction_amount` ,
SUM(`portion`) AS `total_portion`
FROM `members`
LEFT JOIN `agents_data` ON `members`.`id` = `agents_data`.`agent_id`
LEFT JOIN `agents_deductions` ON `members`.`id` = `agents_deductions`.`agent_id`
LEFT JOIN `agents_commission` ON `members`.`id` = `agents_commission`.`agent_id`
GROUP BY `members`.`id` ORDER BY `members`.`id`

The total_portion results is not correct

how I use multiple SUM with multiple LEFT JOINS

UPDATE

I have 3 tables members, agents_data,  agents_deductions, agents_commission

I want to view all members and calculate agents_deductions.amount and agents_commission.portion

members
agents_deductions
agents_commission
I want to calculate agents_deductions.amount it should be 12000
also calculate agents_commission.portion it should be 166666.7
 for the members.id = 1

UPDATE

I tried

SELECT
(
SELECT *,
`frist_step`.`id` AS `aid`,
SUM(CASE WHEN `agents_deductions`.`loan_status` = 0 THEN `agents_deductions`.`amount` ELSE 0 END) AS `deduction_amount`
FROM `members` AS `frist_step`
LEFT JOIN `agents_deductions` ON `frist_step`.`id` = `agents_deductions`.`agent_id`
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `frist_step`.`id` ORDER BY `frist_step`.`id`
),
(
SELECT *,
`step_two`.`id` AS `aid2` ,
SUM(`agents_commission`.`portion`) AS `total_portion`
FROM `members` AS `step_two`
LEFT JOIN `agents_commission` ON `step_two`.`id` = `agents_commission`.`agent_id`
WHERE `membership` != 'l_client' AND `membership` != 'l_super_client' AND `branches` LIKE '%l_contact%' AND 1
GROUP BY `step_two`.`id` ORDER BY `step_two`.`id`
),
(
SELECT * FROM `frist_step`
LEFT JOIN `step_two` ON `frist_step`.`aid` = `step_two`.`aid2`
GROUP BY `frist_step`.`aid` ORDER BY `frist_step`.`id`
)

the results Error Code: 1146. Table 'frist_step' doesn't exist

Try

SELECT members.id AS aid,
       ad.deduction_amount,
       ac.total_portion
FROM members
LEFT JOIN ( SELECT agents_deductions.agent_id, SUM(agents_deductions.amount) deduction_amount
            FROM agents_deductions
            WHERE agents_deductions.loan_status = 0
            GROUP BY agents_deductions.agent_id
          ) ad ON members.id = ad.agent_id
LEFT JOIN ( SELECT agents_commission.agent_id, SUM(agents_commission.portion) total_portion
            FROM agents_commission
            WHERE agents_commission.loan_status = 0
            GROUP BY agents_commission.agent_id
          ) ac ON members.id = ac.agent_id
ORDER BY members.id

PS. I do not understand why the agents_data table is included in the query data source. None of its field is used in a query (except join condition) and it cannot affect to the query result because of left join (except unwanted multiplying).


Rate-limiting query execution in a batch process I have a set of queries in my queries.sql file which I am feeding to psql for execution. But I don't want them to run instantly one after the other: I would like to set some sort of delay - I was looking at \watch and query buffer but I am not so sure how to proceed. I would prefer not to write PG_SLEEP after each of my queries.

\watch executes the query buffer (one statement). It doesn't loop through different statements.

\set title "EVAN SAYS HAI"
SELECT 'HELLO WORLD' AS "Evan Greets the Plebs";
\watch

Now move that window over to your fifth monitor and when the boss comes by pretend like you're waiting for it to change.

If you want to put a delay in between query executions, make a spool as @Craig Ringer said in the comments

  Write a Python or Perl script with your list of queries and have it loop over them, sleeping after each execution. – Craig Ringer Mar 5 at 11:18

You can do it one line or whatever too..

echo -e "SELECT 1;\nSELECT 2;\nSELECT 3;" |
  perl -pe'$|=1; sleep 1;' |
  psql -d foo;


Create range from column I have a list of positions:

pos
123
100024
1025
5426

I want to define a range of these positions

E.g.

100-1000

1001-2500

etc...

So when i do a Select of how many positions there are in that range counts the number of elements in the range.

I've tried with:

SELECT pos , generate_seres(max(pos)::int, min(pos)::int, 1000::int)
as pos_int
FROM varianti
group by pos
ORDER BY pos;

But i don't think that is correct...

Is there a way?

If the first range is allowed to start with 0, you can do something like this:

with ranges (range) as (
  select int4range(i, i + 1000, '[)')
  from generate_series(0, (select max(pos) from varianti), 1000) as i
)
select range,
       count(v.pos) as range_count
from ranges l
  left join varianti v
         on v.pos <@ l.range
group by range
order by range;

For this data:

insert into varianti (pos)
values
(123), (150), (170),
(1025), (1050), (1051), (1052), (1053), (1054), (1054),
(2054), (2051),
(3001), (3002), (3003), (3004), (4004),
(5426);

This returns:

range       | range_count
------------+------------
[0,1000)    |           3
[1000,2000) |           7
[2000,3000) |           2
[3000,4000) |           4
[4000,5000) |           1
[5000,6000) |           1


How to select top 10 records from each category I have a table with fields

Hour,
PathId,
Duration,
Event,
CellId,
Channel

I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to display the top 10 records (each PathId) for each CellId.

Sample Table

Sample Output

Use row_number() in a derived table. Partition by CellID and use the order by as per your specification. In the main query you filter on rn to get the top 10 rows per category.

select T.CellID,
       T.PathID,
       T.Duration
from (
     select T.CellID,
            T.PathID,
            T.Duration,
            row_number() over(partition by T.CellID order by T.Duration desc) as rn
     from dbo.YourTable as T
     ) as T
where T.rn <= 10;


Always On - Does the read replica need the same SQL license as the primary? We have a SQL Server 2014 Enterprise Edition (30 cores) production database. We're using Always ON to provision a read only replica. Does the read replica instance also need to be Enterprise Edition with support for 30 cores?

Enterprise Edition is required on the readable replica, but it doesn't have to have the same hardware. You could go with a server with less cores and thus need less licenses.


Can you get multiple values from a table into one row? I'm extremely green at database design and have been given a project to complete as part of an assignment.

My question is, in regards to values from a table, in this instance, it relates to health club facilities.

Let's say there are multiple facilities that a health club could have such as 24-hours gym, swimming pool, sauna, kids playroom, outdoor training park, cycle studio. My plan would be to store these and have a sequential ID number relating to each.

If I store this information in one table, can multiple ID numbers be referenced in a single row in another?

for example, if the health club "Kingsway" has 4 of those facilities, can I show in the row relating the "Kingsway" have under the facilities column ID numbers 1,2,5,6?

Cheers

While it is possible to have a single row for Kingsway and a column for each facility it would be a poor design.

Better to have one table to list clubs. A second table holds the superset of all possible facilities across all clubs. A third table shows which facilities each club actually has. It will have a row for each (club, facility) pair e.g. (Kingsway, pool), (Kingsway, bicycles), (Kingsway, sauna) etc. If another club also had the same facilities there would be a further three rows in this table: (Club2, pool), (Club2, bicycles), (Club2, sauna).

How these tables become populated depends on the source of the data and the particular database design chosen. If the data comes from off-line sources, say brochures, there's no alternative but to type it in to each of the three tables. If the source is digital it could be loaded as-is into a disposable staging table. The appropriate parts are then copied to each of the three data tables. Finally the staging table can be dropped.

The final design could deploy either natural keys or surrogate keys. Using natural keys the third, linking table will appear exactly as shown above. The actual string "Kingsway" and the actual string "pool" will be in the linking table. Using surrogate keys the club and facility tables must be loaded first to creating the pairings between values in columns club_name and club_id, and between facility_name and facility_id. Then the linking table can be populated with the correct (club_id, facility_id) pairs. Likely this will need to join the staging table to the club and facility tables.


Filter one column by value in second column postgres I'm constructing a large table that is pulling values from across the database. Two of the fields I want to include in the new table are stored in a single column in another table. I want to split this field in the current table into two columns in my new table. Ideally, I would do this as part of a larger select statement but otherwise, I can use a CTE or temp table.

For example, the data is stored like this:

CREATE TABLE foo(id,title,type)
AS VALUES
    ( 1, 'Book Title 1',  'Book'    ),
    ( 2, 'Book Title 2',  'Book'    ),
    ( 1, 'Chapter 1',     'Chapter' ),
    ( 1, 'Chapter 2',     'Chapter' ),
    ( 2, 'Chapter 1',     'Chapter' );

And I'd want my new table to be like this:

|Book             | Chapter    |
--------------------------------
| Book Title 1    | Chapter 1  |
| Book Title 1    | Chapter 2  |
| Book Title 2    | Chapter 1  |

How do I write a SQL query that can perform this transformation?

Can this type of transformation be done as part of a larger SQL query or should I create the table and then join it to my other tables?

Or should I export the data and reshape it with R and the load it back into the DB?

I think you want a self join like this,

SELECT f1.title AS book, f2.title AS subtitle
FROM foo AS f1
JOIN foo AS f2
  USING (id)
WHERE f1.title <> f2.title
  AND f1.title LIKE 'Book%'
ORDER BY 1, 2;

    title     |   title
--------------+-----------
 Book Title 1 | Chapter 1
 Book Title 1 | Chapter 2
 Book Title 2 | Chapter 1
(3 rows)

If all subtitles are chapters, you can also do this

SELECT f1.title AS book, f2.title AS chapter
FROM foo AS f1
JOIN foo AS f2
  USING (id)
WHERE f1.title <> f2.title
  AND f1.title LIKE 'Book%'
  AND f2.title LIKE 'Chapter%'
ORDER BY 1, 2;


Creating an auditing strategy when a user has been defined in a table instead of in SQL To preface this post, I'm pretty new to database design, so please excuse me if there's already a simple or immediate answer to this post.

In my DB I have a table called t_employees which acts as a "user" table.  In other words, tables that keep track of things like "modifiedBy", "createdBy", etc. use this table.

The table looks like this:

CREATE TABLE t_employees (
    employeeId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    employeeExternalId VARCHAR(50) NOT NULL,
    email NVARCHAR(128) NOT NULL,
    passwordHash NVARCHAR(128),
    firstName NVARCHAR(60),
    lastName NVARCHAR(60),
    dateHired DATE,
    gender INT,
    DOB DATE,
    ...
    --And a bunch of other random fields that an employee can use
);

And this is where my question resides.

I'm trying to come up with an auditing solution that will capture "who modified this data" when an update, insert, or delete statement occurs in my application.  I've looked into threads like this where people can audit a table via triggers that keep track of the system user who's making the changes.  However, this approach doesn't work for me as I'm trying to keep track of the employeeId from the t_employees table who made the changes and I only have 1 system user in my database.

I've thought of creating an SP that I call before every update, insert, or delete statement that logs what data is being inserted, updated, or deleted along with the employeeId of the user who made the request into an audit table.  However, I feel like there must be a better solution to this as this seems kind of redundant.

Any help or advice would be very much appreciated.  Thank you.

Adding to what @DForck42 said- username is best left to the application layer. But since these tables don't have too many changes everyday,I would include a

     IsDeleted (bit)

column and implement a soft delete (1 or 0).


How can one optimize a query involving a sort and a LIKE? Here is the query:

EXPLAIN ANALYZE SELECT
    *
FROM
    "Users" AS "User"
WHERE
    "User"."name" LIKE 'garr%'
AND "User"."id" NOT IN (2449214)
AND "User"."hellbanned" IS NULL
AND "User"."hellbanPostsAfterDate" IS NULL
ORDER BY
    "followerCount" DESC NULLS LAST
LIMIT 3;

Which yields:

Limit  (cost=0.43..3662.92 rows=3 width=1711) (actual time=181.935..2158.898 rows=3 loops=1)
  ->  Index Scan using users_search_followercount_index on "Users" "User"  (cost=0.43..280791.25 rows=230 width=1711) (actual time=181.933..2158.891 rows=3 loops=1)
        Filter: (((name)::text ~~ 'garr%'::text) AND (id <> 2449214))
        Rows Removed by Filter: 29434
Total runtime: 2158.951 ms

The index used is the most optimal I could build:

CREATE INDEX users_search_followercount_index ON "Users" ("followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL;

I also tried these:

-- CREATE UNIQUE INDEX users_name_unique_index ON "Users" ("name");
-- CREATE INDEX users_followerCount_index ON "Users" ("followerCount" DESC NULLS LAST);
-- CREATE UNIQUE INDEX users_search_name_followercount_unique_index ON "Users" ("name", "followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- N/A
-- CREATE UNIQUE INDEX users_search_followercount_name_unique_index ON "Users" ("followerCount" DESC NULLS LAST, "name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422
-- CREATE INDEX users_name_unique_index ON "Users" ("name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422

But none speed it up.

Your index attempts are still futile, since the predicate

u."name" LIKE 'garr%'

is obviously the most selective one but cannot use a basic (default) b-tree index unless you are operating with the "C" locale (which is uncommon).

SELECT *
FROM   "Users"
WHERE  name LIKE 'garr%'
AND    id <> 2449214
AND    hellbanned IS NULL
AND    "hellbanPostsAfterDate" IS NULL
ORDER  BY "followerCount" DESC NULLS LAST
LIMIT  3;

There are several options (also depending on the missing table definition and other information), this index should work:

CREATE INDEX "Users_whatever_idx"
ON     "Users" (name text_pattern_ops, "followerCount" DESC NULLS LAST)
WHERE  hellbanned IS NULL
AND   "hellbanPostsAfterDate" IS NULL;

The conditions on hellbanned and "hellbanPostsAfterDate" are only useful if they exclude more than a few rows. Else, a complete index might be the better choice, since it can be used for more different queries.

Also, there is a chance the second column in the index "followerCount" won't help, since there are typically multiple different matches for the first and Postgres has to sort after all. Test a couple of typical queries with and without the second column in the index.

Details for indexes and pattern matching, including an explanation for the operator class text_pattern_ops:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Aside: My standing advise is to use unquoted, legal, lower-case identifiers exclusively in Postgres.


MySQL configure as ACI vs Redis i have an use case in my application where i need real time communication between users based on their position. Basically one user will create an "event" and it will be sent to near users. On the server side i'm planning use redis or mysql to manage events and participants. Based on questions like this one i could configure mysql with:

innodb_flush_log_at_trx_commit = 2

and it increases write performance. Do you think that properly configured MySQL could compete with Redis?, what i like of MySQL is that i can use an ORM in front of it. Or maybe i'm missing something about the difference between Redis and MySQL.

Thanks in advance.

There are too many differences to enumerate.  The choice between them is heavily based on your application, which you have not described.

That setting affects writes, not reads.

If you are not exceeding 100 queries per second on slow spinning drives, MySQL will do just fine.  Or, if it does not, then we should look at your indexes and queries.


Parse hyphenated word to unsigned integer When tested to_tsvector on 'someword-123' with english full text config, I got the following result:

SELECT to_tsvector('someword-123');

to_tsvector
tsvector
--------------------
"'-123':2 'someword':1"

However, I want to config to get the result like "'123':2 'someword':1" so when user search with 'someword & 123', it could return result.

Is there anyway to do this? Hope someone could help.

Update: For more information, my full text search is applied mostly for product name. For example, I have a product which name is "Intel Core i7-3820 Processor" and I want the result of to_tsvector() for this string could be like "'intel':1, 'core':2, 'i3':3, '3820':4, 'processor': 5" (currently, it's "'intel':1, 'core':2, 'i3':3, '-3820':4, 'processor': 5") so this product can be returned when user search with 'intel 3820'

Thank for your all.

I think it would be better to work around your problem: simply replace hyphens with spaces in the strings to be ts_vectored.  If necessary, you can store the unhyphenated values in a separate column and run your searches against it.


Oracle DB license for only non-production environments My company is a software vendor. We develop software that is later deployed on the customer's environment, on their own machines. Our software uses various DBMS, depending on what the customer has. We do it for PostgreSQL, MySQL and so on.

Currently we have two new customers, which are using Oracle DB in their own production environments. We are obliged, however, to provide an user acceptance environments, which are ought to be 1:1 to the customer's production environment.

Hence my question - do we need to buy an Oracle DB license for ourselves, provided we're never going to have a production environment with that product? In another words - since the customer has the Oracle DB license do we also need to buy one only for testing purposes?

Yes you do.  If you install the Oracle software you need to pay for it.  However numerous exceptions exist including

you can use Oracle Express 11g for free
your customer may have licencing provisions that apply to you

Either way, do not take anything from my answer or anyone else's answer as valid.  Oracle licencing  is complex and a final word can only come from them.  Contact  your representative and ask for clarification.