OFFSET and LIMIT on complex query I am doing a pretty complex query in Postgres 9.3.4:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  100
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

See here for more details on my Postgres db, and settings, etc.

Which is great, unless I try to do some pagination.
To see what I mean. When I do a count, with no limit set:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   ) p
ORDER  BY p.external_created_at DESC;

I get (107 rows), which is the correct number of results.

If I want to paginate, I'd use OFFSET on the subquery. If the first page has 100 posts, the second page should have 7 posts.

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  100
   OFFSET 100
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

but instead it returns (0 rows).

If I do the pagination on the outer query:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   ) p
ORDER  BY p.external_created_at DESC
LIMIT  100
OFFSET 100;

I get (7 rows) which is correct, but then the query is insanely slow (see this question)

Is there a way to paginate this query properly, not slow it down, and get the correct results back?

For just the two pages, a compromise could be:

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
              , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
     , LATERAL (
   SELECT *
   FROM   posts
   WHERE  source_id = s.source_id
   AND    deleted_at IS NULL
   ORDER  BY external_created_at DESC
   LIMIT  200   -- 100 + 100 (outer limit + offset) per source_id
   ) p
ORDER  BY p.external_created_at DESC
OFFSET 100
LIMIT  100;

Which will be considerably slower than the first 100 rows, but still much faster than your original query in your previous question.

For just 107 rows total in your example, performance will be almost identical.

Performance decreases with every later page, obviously. OFFSET is a pain for performance, generally. But it becomes a PITA when combined with your difficult query.

I would consider a materialized view with pre-calculated row numbers or the more complex (but also more potent) solution here:

Can spatial index help a "range - order by - limit" query

Also related:

Improve performance for order by with columns from many tables


Named Pipes Clarifications Currently,SQL Services are using TCP IP protocol. Named pipes protocol is disabled. I am facing issues while running my application status as below :

An error has occurred while establishing a connection to the server.
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

My Question is ,Will there be any issue if I enable Named Pipes now because I restarted the Named Pipes and restarted SQL Service and issue seems to be resolved. But I am confused why we are getting Named Pipes issue when TCP IP is enabled. Does SQL Server need both TCP IP and Named Pipes to be enabled ?

Thank you

It's because,

When a client tries to make a connection, it uses the protocols order by priority defined by SQL Server configuration manager. A low value means a high priority. If you configured TCP/IP as first priority and SQL Server is not able to resolve Name to IP it raise the error you posted. But when you disable TCP/IP, then because TCP/IP with first priority is disabled client uses Named Pipe (with second priority) to connect to SQL Server. And get a successful connection.

Here, you can set the priority of protocols by moving it UP or DOWN.

Thanks


Howto use Windows Authentication with SQL Server 2008 Express on a workgroup network? I have two computers running SQL Server 2008 Express: c01 and c02, I setup both for remote connection using windows authentication. Worked fine for c02 but not for c01.

This is the error message I'm getting:

  Cannot connect to ACAMP001\SQLEXPRESS.

  ADDITIONAL INFORMATION:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

  For help, click: http://go.microsoft.com/fwlink?>ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

I don't know if I'm missing something, here is what I did:

Enabled TCP/IP protocol for client from Sql Server Configuration Manager.
Modified Windows firewall exceptions for respective ports.
Started the Sql Browser service as a local service
Added Windows user to this group: "SQLServerMSSQLUser$c01$SQLEXPRESS"
From Management Studio, I added "SQLServerMSSQLUser$c01$SQLEXPRESS" to SQLEXPRESS instance's logins under security folder, and I granted sysadmin permissions to it.
Restarted c01\SQLEXPRESS
Restarted Sql Browser service.
There is no domain here. It's only a workgroup.

Please any help is appreciated, Thank you.

Whatever username you are using on the "remote" machine must be set up on the "server" machine.

So, if you are logged-in to "remote" as "John," there must be a "John" user on the machine that is hosting SQL Server 2008 Express, and unless you are going to provide different credentials, it should have the same password, too.


SSAS Cube for tracking changes in parent child relationship over time I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change:

which object belongs to which
attributes of each object.

Here is my schema:

fact.Edge:
    the_date date
    parent_id int
    child_id int

fact.Vertex:
    the_date date
    id int
    attribute1 int
    attribute2 int
    ...
    attributen int

dim.attribute{1...n}:
   id int
   value1 nvarchar(64)
   value2 nvarchar(64)
   ...
   valuem nvarchar(64)

These tables get new data once daily. If nothing changes, then there are two copies of the exact same data in the two fact tables with sequential dates.

I would like to know if it is possible to define a parent child hierarchy in SSAS based on the fact.Edge table referencing itself (via child_id->parent_id) but also only when the_date = the_date.

I am new to SSAS, but it seems only one attribute can be the parent attribute. Are there any workarounds?

Additionally, is it possible to treat the vertex table as two "fact" related dimensions -- ie parent_vertex and child_vertex? Or else do I need to include edges with either a null parent_id or null child_id and choose the other to have the only vertex reference?

If my questions don't quite make sense (likely due to my limited SSAS experience), is there an example cube definition that demonstrates best practices for this case?

I'd appreciate any insights you might have!

Parent-child relationship in SSAS isn't straight forward as we might wish. From my experience in SSAS 2008 linking them both together wasn't simple at all.
In those type of cases the most recommended solution would be to flatten the relationship with a (SQL) view in which you link the parent to the child with the date attribute. That view becomes your dimension and on which you check the changes over time.
you can find more data about this in here:
http://www.kimballgroup.com/2007/10/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/
and here:
http://www.kimballgroup.com/2012/05/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/
Alternatively:
If you need both the Parent id and the Child in the fact table (because you need to query on both) then in that case I would go on a different direction and include both id's in my fact table. This way you can monitor changes across time as you would for any other dimension.


You want to use a PIVOT and ROW_NUMBER function in order to accomplish your objective.  See the example below.

The Setup

DECLARE @Seats AS TABLE
    (
    RollNo VARCHAR(10) NOT NULL
    , Seat CHAR(3) NOT NULL
    );

INSERT INTO @Seats
        ( RollNo, Seat )
VALUES  ('A1234','GN')
        ,('A1233','GN')
        ,('A1235','GNF')
        ,('A1238','GN')
        ,('A1233','GNF')
        ,('A1231','GN')
        ,('A1232','GNP');

The Query

SELECT GN, GNF, GNP
FROM (
    SELECT RollNo
        , Seat
        , ROW_NUMBER() OVER (PARTITION BY Seat ORDER BY RollNo) AS GroupID
    FROM @Seats) AS T PIVOT (MAX(RollNo) FOR Seat IN (GN, GNF, GNP)
    ) AS pt


import TSV file into MySQL table with SET datatype column Is it possible to use LOAD DATA INFILE to load data into a MySQL/MariaDB table, where one column uses the SET datatype? My columns are separated by tab, my set members by comma.

If so, how do I specify the separator for the individual set elements?

The answer appears to be yes.

Set members are accepted if they are separated by comma, however there should be no space between the comma and the next set element.

Since commas are used to separate set members, columns must be separated by a different character. I have used tabs, and this works well.

Performance is greatly improved (roughly by a factor of 10-20), compared to joining with another table holding the set values.


Why does updating a large VARBINARY break replication (MSSQL_REPL21100)? We have replication set up on a VARBINARY(MAX) column that stores image data (yes, yes, I know - don't blame me, I didn't design it). Recently, we started inserting larger images (around 200kb) and every time it breaks the replication with the following message:

  The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\110\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)

If I re-initialise the subscription with a new snapshot, everything syncs up fine (including the VARBINARY) and the error goes away. If I then update the VARBINARY column the replication breaks again and we go back to step 1.

The first thing I checked was that the accounts that run the 'SQL Server' and 'SQL Server Agent' services both have full access to the directory - they did.

Next I made sure the server was configured to replicate large VARBINARYs:

-- Set the maximum replication size to unlimited.
EXEC sp_configure 'max text repl size', -1;
GO
RECONFIGURE;
GO

This didn't help (I think it was already set to unlimited). What is going on here and how do I fix it?

After much frustration and fruitless googling, I finally figured out the problem. It was not that the accounts for the 'SQL Server' and 'SQL Server Agent' services did not have access to the directory. The problem was that the account for the 'Log Reader Agent' did not have access. Should have been obvious from the error message, but I just didn't get it...

On the publication server, run this:

USE DatabaseName
EXEC sp_helplogreader_agent

The job_login column will tell you which account runs the Log Reader Agent. Grant that account read/write access to the directory and everything works fine.


Wait Stats on Microsoft Azure test On a particular test we're doing with Microsoft Azure, we are seeing a wait_category called: PAGE LATCH (non-I/O) that we're unfamiliar with.

In our test, CaptureSeconds = 1595 and for PAGE LATCH (non-I/O) it reports:

wait_time_ms = 126666216

wait_time_ms_per_sec = 79364

max_waiting_tasks = 13086110

average_wait_time_ms = 9

max_Wait_time_ms = 2526

Can someone tell me what PAGE LATCH (non-I/O) wait category is and whether this is indicating a problem of some sort?

Thanks,

RW

In SQL Server a Latch used to provide brief, exclusive access to a database page.  When multiple sessions need to modify the same page, all but one will enter a "PAGE LATCH (non-I/O) wait".  Your wait stats seem to indicate that all your sessions are lining up to modify a single page.

There are couple of different scenarios where Latch contention can become an issue.  The most common are when all a large number of sessions need to write to the same "hot" page, typically at the end of a clustered index, and latch contention in Tempdb for metadata pages when a large number of sessions create and drop temp tables or table variables.  See  Diagnosing and Resolving Latch Contention on SQL Server for details.

But in the context of a synthetic load test, this Latch contention may represent a defect in the test workload design that is preventing the sessions from running concurrently and accessing all of the server resources.


SSRS error - A scope is required for all aggregates in the page header or footer which reference fields I had the following textbox expression in my SSRS report and it was working fine.

  = First(Fields!FirstName.Value) +" " + First(Fields!LastName.Value) + ", MD"

When I moved the textbox to the report header area I started getting the "scope required" error message

I did some research and saw that I needed to specify the dataset. Once I included the dataset (PPR) below the error went away.

  = First(Fields!FirstName.Value,"PPR") +" " + First(Fields!LastName.Value, "PPR") + ", MD"

SSRS is now giving the same error but for another textbox/expression (below) that was working fine until I moved it to the header.

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,1))) / sum(CINT(right(fields!Points.value,1))))*100) & "%"

I tried the following 2 modifications to include the scope (PPR) in the expression but I am still getting the same error. Is this just a syntax error on my part or am I trying to do something that can't be done in the header?

didn't work

="Interim Score:   " & CINT((sum(CINT(left((fields!Points.value,"PPR"),1))) / sum(CINT(right((fields!Points.value,"PPR"),1))))*100) & "%"

didn't work

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,"PPR",1))) / sum(CINT(right(fields!Points.value,"PPR",1))))*100) & "%"

Below is the full error message I am getting:

  The Value expression for the text box ‘Textbox6’ references a field in
  an aggregate expression without a scope.  A scope is required for all
  aggregates in the page header or footer which reference fields.

I only have 1 dataset (PPR) in my report.

I was reading up on the below page but I can't seem to pinpoint what I am doing wrong. Any suggestions would be greatly appreciated.

https://technet.microsoft.com/en-us/library/dd220421%28v=sql.110%29.aspx

The reason I was getting the error message is because I was supplying the scope (dataset) in the wrong place.

The scope needs to be supplied as the second argument of the aggregate function. I was incorrrectly supplying the scope as part of the left function (which is not an aggregate function) instead of the SUM function.

Below is what the working expression looks like. The only difference from my original syntax is the location of the scope/dataset name (PPR) in the expression.

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,1)),"PPR") / sum(CINT(right(fields!Points.value,1)),"PPR"))*100) & "%"


MySQL Explain has different row count than slow query log I have this entry in slow query log:

# User@Host: user[host] @  [ip]
# Thread_id: 1514428  Schema: db  Last_errno: 0  Killed: 0
# Query_time: 2.795454  Lock_time: 0.000116  Rows_sent: 15  Rows_examined: 65207  Rows_affected: 0  Rows_read: 65207
# Bytsent: 26618
SET timestamp=1407511874;

select off.*,translated_title,translated_description
from ephpb2b_products off  USE INDEX(id_viewed)
  INNER JOIN ephpb2b_members mem
    ON  off.uid = mem.id
  Left Join ephpb2b_product_language_new pol
    ON  off.id = pol.offer_id
    and pol.language='en'
where off.approved=1
order by off.viewed
LIMIT 15;

When I explain this query, it's absolutely fine.

mysql> explain select off.*,translated_title,translated_description from ephpb2b_products off  USE INDEX(id_viewed)  INNER JOIN ephpb2b_members mem ON off.uid = mem.id Left Join ephpb2b_product_language_new pol ON off.id = pol.offer_id and pol.language='en' where off.approved=1 order by off.viewed  LIMIT 15;

+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key         | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | off   | index  | NULL                    | id_viewed   | 4       | NULL                      |    3 | Using where |
|  1 | SIMPLE      | mem   | eq_ref | PRIMARY                 | PRIMARY     | 4       | db.off.uid |    1 | Using index |
|  1 | SIMPLE      | pol   | ref    | offer_id,id_language | offer_id | 5       | db.off.id  |    4 |             |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
3 rows in set (0.17 sec)

How do I optimize this query? Why does explain show 3 rows and slow query log says it examined 65207 rows.

In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics.

When you run explain, the rows column will tell you, for each table access, how many rows will be examined by using the intended filter. There are two ways to calculate that: either an index dive (that usually should give you exact results) or by using approximate statistics that each engine stores independently -up to 5.6- for each table. While the first method is preferred when it can be used (simple filters on a single indexed column), in many cases, only an approximation could be used -otherwise, the query optimiser would take as much time as the query execution itself.

In any case, the rows are the calculated rows to be read (not to be returned) per table access. Even if it were exact (and many times has differences with several order of magnitudes, but still is good enough for the optimiser), it does not predict the real number of rows accessed throughout a join. For example, if you join table A (reading exactly X rows) and table B (reading exactly Y rows), in the order A -> B, the real number of rows read will be: X + # of rows returned by A (<=X) multiplied by Y, as standard mysql only supports nested loop joins.

The slow log, like the handler statistics, or other profiling mechanisms tell you the real number of rows processed and sent, because those statistics are gathered after the execution, thus exact.

Regarding your particular case, EXPLAIN is to blame because it shows that only 3 rows would be scanned for the first access, when in reality it may be doing a full index scan (as it is using the key only for sorting), which later gets multiplied for each join performed. Do not trust explain. You can use:

FLUSH STATUS;
-- Execute your query here
SHOW STATUS like 'Hand%';

To check the actual number and kind (PK access, ref, index scan, table scan) of row operations. I would use it to test each table access individually.

For more specific help, we would need the table structure of each table and the approximate selectivity of each filter condition.


How can I get the maxium number of concurrent connections to MySQL over a specific time interval? I have a monitoring app that checks MySQL's Threads_connected  and Max_used_connections variables every X seconds.

Neither of these variables tells me what I want to know: The highest number of concurrent connections since the last check (X seconds ago).

In the past, I've used FLUSH STATUS to reset Max_used_connections some time after the critical level is reached, but this method isn't ideal for a number of reasons, and according to the docs:

  This is something you should use only when debugging a query.

Before I head down the path of a complicated analysis of the query log, is there anything I'm overlooking?

The only thing I can think of is finding some what (maybe through a cron job) to record the 'Threads_connected' values every minute, and then calculate the max of these values.

I'm not sure if you can do exactly what you are asking for without resetting the 'Max_used_connections' before your monitoring period.


Reaching SQL Server AlwaysOn HA listener from the Internet I've successfully created a SQL Server AlwaysOn High Availability Group between two servers and everything seems to be working great internally. Each replica is on a separate physical location, behind different firewalls and public IPs, as well as private IPs/subnets. Right now they are able to communicate directly with each other through a site-to-site VPN.

The application that I use to access the database is able to connect to the listener's DNS. Both Failover Cluster nodes and SQL replicas have private IPs so as long as I run the application within my network, I'm OK.

The issue that I have now is that I need the application to be able from anywhere in the world. Basically, I need to be able to connect to the listener from the Internet without having to VPN in and still be able to take advantage of the HA//Failover capabilities.

How can this be accomplished? The only documentation that seems to be posted online relates to Azure which doesn't apply to my setup.

Thank you!

It's generally considered a really bad idea to make your database accessible from the internet.

Making the database accessible from the public internet is a HUGE security risk. It's easy to port-scan and find publicly accessible databases. Even if you run on a non-standard port, it's relatively trivial to discover the SQL Server instance that is internet-facing. Your database would be available to anyone to exploit. It's likely that you'd be victim of every zero-day exploit, in addition to brute force attacks. Eventually, your data will be stolen or destroyed.

Take a look at shodan.io to see how easy it is to discover databases on the public internet. You will essentially have a big sign saying "HACK ME" hanging on your IP address.

Ideally, you would have a web service that the application would interact with. That public web service would be the only thing that connects to the (non-public, firewalled) database. Doing this would require re-architecting your application to use the web service, rather than a direct client-server database connection. If you can't re-architect the application, then VPN is the only secure solution.


Hyperthreading & MySQL InnoDB Thread Concurrency Performance I have a dedicated DB Server with the following specs"

Ubuntu 12.04 LTS Server
Intel(R) Xeon(R) CPU X5670

@ 2.93GHz
12 Cores

MySQL 5.5.29
64 GB RAM
RAID10

We are currently doing Hyperthreading so we have 24 logical cores. Currently running between 2k-4k transactions/sec.

I have set the following :

innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24

This is strictly OLTP load (70% reads). Queries are relatively fast (milliseconds). Query cache is ON but not used too much. Temp disk tables are not created often. The InnoDB Buffer Pool is set to 48GB which covers almost the whole data set (60 GB data+index).

Transactional load will double to 4k-8k transactions/sec. Currently CPU load is around 1000% so I anticipate that this will be the bottleneck.

How can I better tune my CPU threading and how MySQL uses these threads? Let me know if anyone has done any benchmarks on innodb_thread_concurrency.

I would appreciate any suggestions.

UPDATE

After updating from innodb_thread_concurrency = 48 to:

innodb_thread_concurrency = 0

On a production system during peak times (4-5k transactions/sec) to see the behavior this is what I observed:

Load average increase
mysqld process CPU usage went from 400% - 1000% to 400% - 1400%
About 4-10 CPUs are idle while the other 14-20 CPUs are around 70%-90%
%wa is 0% - 0.1% for each CPU so I/O is not the problem here. (From linux top output)

It seems like setting innodb_thread_concurrency to a non-zero value throttles your CPUs so that they don't go overboard. I set it back to innodb_thread_concurrency=48 and CPU usage decreased.

I would appreciate any thoughts on these results.

By default, innodb_thread_concurrency is 0. That's actually the best setting. It means infinite concurrency. It allows the InnoDB storage engine to decide the best number of concurrency tickets to launch and address. Setting it to a nonzero value actually can throttle InnoDB or throttle the OS if not set properly.

I have written many posts in the DBA StackExchange about this setting:

May 26, 2011 : About single threaded versus multithreaded databases performance
Aug 16, 2011 : Why do MySQL threads often show "freeing items" status when the query cache is disabled?
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Aug 03, 2012 : Slow insert performance in mysql innodb table for solr indexing


SQL Server - Analyzing Multiple Stored Procedure Statistics I'm trying to analyze 1000+ stored procedures and I need to find out the:

Elapsed Time in Sec (AVG based on # of iterations)
CPU (AVG based on # of iterations)
Logical Reads

At first I'm using SQL Query Stress, but it will take me so much time to test-run each stored procedure and analyzing test parameters.

So my question is, is there an easier way to get what I needed?

If these are in a live database, then you can use the DMV sys.dm_exec_procedure_stats to pull information for all the Stored Procedures that have been run and cached on the instance.

A basic query to pull all procedures ordered by the Average Duration may look something like this:

USE <DATABASE>;
GO

SELECT
  o.name,
  deps.total_elapsed_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgDurationInS,
  deps.total_worker_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgCPUInS,
  deps.total_logical_reads/CAST(deps.execution_count AS DECIMAL(10,2)) AS AvgLogicalReads,
  deps.cached_time
FROM sys.dm_exec_procedure_stats AS deps
INNER JOIN sys.objects AS o
  ON o.object_id = deps.object_id
WHERE deps.database_id = DB_ID()
ORDER BY AvgDurationInS DESC;

However, this doesn't show you all the queries within the Stored Procedure and break down each one by the same metrics. You can do that using sys.dm_exec_query_stats


Handle 4 tables and to show 4 columns as result i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns:

I want to show this 4 rows, each from a different table.

EDIT: Each company belongs to a Sector. And because of how the data it's submited and handle it inside the database, i can't have everything in only 2 tables. Every table has different uses.

So far, I have tried UNION/UNION ALL:

SELECT Companies.Symbol, CompanyData.strenght FROM Companies
INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anything'
UNION ALL
SELECT Sectors.SectorName, SectorTrend.Trend FROM
SectorTrend
INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID

I only get 2 columns: Symbol and Strenght

Several JOINS nesting the queries:

SELECT SectorTrend.Trend
FROM (
    SELECT Sectors.SectorName
    FROM (
        SELECT Companies.Symbol, CompanyData.strenght
        INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anyvalue'
         )
    INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
    WHERE Sectors.SectorID = 'anynumber'
     )
    INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

I get some sintax error on line 5 that can't figure out how to fix.

Using plain JOIN queries:

SELECT Companies.Symbol, Sectors.SectorName, CompanyData.strenght ,
SectorsTrend.Trend FROM Companies
INNER JOIN Companies ON Companies.SectorID = Sectors.SectorID
INNER JOIN CompanyData ON CompanyData.CompanyID = Companies.CompanyID
INNER JOIN SectorTrend ON SectorsTrend.SectorID = Sectors.SectorID

I get Error: Table/alias Companies is not unique. I have been playing switching the query order without any luck.

Is there anything that i'm  missing or can you point me out another way to handle this query?
Both queries handled separately works fine.
Should i handle it separately, store the results in any variable and then create a view to display the result?

Thanks in advance.

UPDATE #1
Following @Kondybas suggestions i rewrited the query, and got empty result.
It was an multi column index problem and when i executed EXPLAIN SELECT... there were NULLS keys and NULL possible keys, so I added indexes in several columns with: CREATE INDEX _indexname_ ON _tablename_ (_column1_, _column2_); and now the query it's running faster.

But managed to get results only if i remove the last JOIN: JOIN SectorTrend AS st ON st.SectorID = s.SectorID and without using any WHERE condition.
So it seems the problem it's that last JOIN.

Final query is (changed and included some columns names):

EXPLAIN SELECT c.Symbol , s.SectorName , cprs.strenght , ssbpi.ColumnType FROM Companies AS c JOIN Sectors AS s ON s.SectorID = c.SectorID JOIN CompanyPriceRS AS cprs ON cprs.CompanyID = c.CompanyID JOIN SubSectorsBPIsData AS ssbpi ON ssbpi.SectorID = s.SectorID WHERE cprs.strenght = 'strong' AND c.SectorID = '16'

And EXPLAIN SELECT... result:

First I'll explain the mistakes/errors.

1) UNION provides the vertical merging not horizontal.

+---+---+         +---+---+     +---+---+
| A | B |         | A | B |     | 1 | 2 |
+---+---+         +---+---+     | 3 | 4 |              +---+---+---+---+
| 1 | 2 |  UNION  | 6 | 7 |  =  | 6 | 7 |          not | 1 | 2 | 6 | 7 |
| 3 | 4 |         | 8 | 9 |     | 8 | 9 |              | 3 | 4 | 8 | 9 |
+---+---+         +---+---+     +---+---+              +---+---+---+---+

Also UNION requires both merged tables have columns of the same
names/types/order. That is why you get only two columns while the columns and rows from the second table are dissapeared.

2) That "some syntax error" was the requirement that each tablesource used by query should have the name. When you have used the subquery you should give it an alias:

SELECT SectorTrend.Trend
  FROM ( SELECT Sectors.SectorName
           FROM ( SELECT Companies.Symbol, CompanyData.strenght
                   --- some missed clause should be here
                   INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
                   WHERE CompanyData.strenght = 'anyvalue'
                ) AS aaa --- that's what I mean
          INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
          WHERE Sectors.SectorID = 'anynumber'
      ) AS bbb --- that's what I mean
 INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

Anyway that query is too complicated and wrong to be used.

3) Multiple JOINs
Your mistake is that you have joined the table Companies twice (instead of Sectors) and engine can't tell one instance from another. The good practice is to assign an unique alias to each table in the query:

SELECT c.Symbol
     , s.SectorName
     , cd.strenght
     , st.Trend
  FROM Companies   AS c
  JOIN Sectors     AS s  ON s.SectorID   = c.SectorID
  JOIN CompanyData AS cd ON cd.CompanyID = c.CompanyID
  JOIN SectorTrend AS st ON st.SectorID  = s.SectorID

I've replaced the INNER JOINs by JOINs because (at least in the mysql dialect) they are exactly the same with ON clause. And this query is my choice for the task.


Query to calculate database growth I have the following query that outputs some good information from MDW data collectors regarding database space usage. I am wondering with the following columns (db size, reserved space, unused space, unallocated space, collection date) how can i properly calculate database growth trend? I am trying to keep whether a db shrinks or grows into consideration so I cannot just take "max size" and go from that. Here is the full query so far...

DECLARE @ServerName VARCHAR(MAX);
DECLARE @SelectedDatabaseName VARCHAR(MAX);
DECLARE @snapshot_id INT;

SELECT TOP 1
        @snapshot_id = snapshot_id
FROM    ( SELECT DISTINCT TOP 100
                    d.snapshot_id
          FROM      snapshots.disk_usage d ,
                    core.snapshots ss
--WHERE ss.instance_name = @ServerName
--AND ss.snapshot_id = d.snapshot_id
ORDER BY            d.snapshot_id DESC
        ) AS q
ORDER BY snapshot_id ASC;

SELECT  database_name ,
        ss.instance_name ,
        CONVERT (DATETIME, SWITCHOFFSET(CAST (d.collection_time AS DATETIMEOFFSET),
                                        '+00:00')) AS collection_time ,
        d.snapshot_id ,
        ( ( CONVERT(DEC(15, 2), d.dbsize) + CONVERT(DEC(15, 2), d.logsize) )
          * 8192 / 1048576.0 ) AS 'database_size_mb' ,
        'reserved_mb' = ( d.reservedpages * 8192 / 1048576.0 ) ,
        'data_mb' = CONVERT(DEC(15, 2), d.pages) * 8192 / 1048576.0 ,
        'index_mb' = ( d.usedpages - d.pages ) * 8192 / 1048576.0 ,
        'unused_mb' = ( ( CONVERT(DEC(15, 2), d.reservedpages)
                          - CONVERT(DEC(15, 2), d.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'unallocated_space_mb' = ( CASE WHEN d.dbsize >= d.reservedpages
                                        THEN ( CONVERT (DEC(15, 2), d.dbsize)
                                               - CONVERT (DEC(15, 2), d.reservedpages) )
                                             * 8192 / 1048576.0
                                        ELSE 0
                                   END )
FROM    snapshots.disk_usage d ,
        core.snapshots ss
WHERE   --database_name =@SelectedDatabaseName
/*AND*/ d.snapshot_id >= @snapshot_id
--AND ss.instance_name = @ServerName
        AND d.snapshot_id = ss.snapshot_id
ORDER BY d.database_name ASC ,
        collection_time;

Hopefully at the end of it I will be able to see in a year from now how much every instance, database and server grows per year.

Method 1 : Comes with a cost

You can use a third party software which will do everything for you in terms of gathering the data and presenting the reports for database growth and predicts the same depending upon the gathered data as explained here

Method 2: Create table, run the stored proc using SQL agent job and scheduled accordingly to gather the data in the created table and then query it over a period of time to see what has been collected and predicts with all you're calculations handy:

You can use this as explained here

Method 3: Little more on Manual side but works good, is using the default trace. Use the default trace to capture the growth events on data and log for that database , depending upon how frequent or how long the default trace file stays there and does not get rolled over:

Method 4 My personal favorite and the one i have been using:

This excellent article from Chad Miller Database capacity planning which uses T-SQL and Powershell along with SSRS reporting to give you the desired database growth over a period of time.


Suitable NoSQL DB design for a fleet of IoT devices This is my use case:

10,000 IoT devices
Each IoT device sends its environmental data (temperature, pressure, etc.) every 30 seconds
Data of each device will be kept for 24 hours

The main requirement is to retrieve the latest data of a specific device (from device_id).
Retrieve of the data will be step-wise. Meaning, at one time, only 100 records will be read. Then if the user wants, he will request the read the previous 100 records, and so on.

Also, it is possible that multiple users try to retrieve data of multiple devices at the same time.

My concerns are:

How to structure the DB (I am specifically looking at AWS's DynamoDB)
Creating a table for each device would be elegant, however seems creating 10,000 tables is not recommended
If I put all the 10,000 devices in one huge table, reads from this would be very inefficient.

Could someone advice on what would be the best design strategy for this case?

Thank you.

Why NoSQL?

Any RDBMS on modern hardware can easily support your requirements.

Basic Setup

This is meant to be RDBMS agnostic.  You'll want to tweak it to the RDBMS you choose.

create table iot_devices (
  device_id      int identity primary key,
  gis_location   rdbms dependent, -- yes, databases can support longitude/latitude information.
  serial#        varchar(20),
  other_data     what-ever-you-need
);

create table iot_data (
  device_id   int not null references iot_devices (device_id),
  date_time   date  not null,
  temp        number(4,1), -- make sure COMMENT if the units are Celsius, Fahrenheit, or Kelvin
  pH          number(3,1) check ( pH between -14.0 and 14.0)
  -- other data
);

Retrieval

Now, your primary SELECT statement is this:

select *
from iot_data a
where a.device_id = ?
order by a.device_id, a.date_time desc
-- syntax for limit/paging clause is RDBMS specific
;

The question mark is a place holder so that you can BIND a value.  It also prevent SQL Injections.
Need multiple devices?  Look into Array Binding.

From experience, this index should help speed up that specific query:

create index iot_data_ix1 on iot_data (device_id, date_time desc);

Removing Expired Data

Removing "Old data" will cause headaches due to the amount of data that needs to be removed.  With PARTITIONING, you can DROP large segments of data very quickly.

I suggest a range partition on the interval of 1 day and drop the 3rd day's partition every day.
Some RDBMS will need the PARTITION created ahead of time; others can create the PARTITION on-the-fly.

Final

After you have settled on a setup, there are three thing you must do:

Benchmark
Benchmark
Benchmark

With out it, you won't know what the system's limit really is and you won't know if a change to the system has improved/deteriorated the design.


sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options' I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file.

This is the script i am trying to solve it with:

DECLARE @advanced_options int
DECLARE @xp_cmdshell int

--CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT @advanced_options = value_in_use
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell'

select @advanced_options as advOptions, @xp_cmdshell as cmdShell

exec sp_configure 'show advanced options', 1;
Reconfigure;

EXEC sp_configure 'xp_CMDShell', 1;
Reconfigure;

DECLARE @CMDSQL VARCHAR(1000)
SET @CMDSQL = 'F:\Adress\FillDBWithMSTabFiles'--Keep in mind, path needs to be correct.
EXEC master..xp_CMDShell @CMDSQL

EXEC sp_configure 'xp_CMDShell', @xp_cmdshell;
Reconfigure;

exec sp_configure 'show advanced options', @advanced_options;
Reconfigure;

select @advanced_options as advOptions, @xp_cmdshell as cmdShell

Results in error:

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 8
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

Error is at below select statements. If you do ALT+F1 on sys.confiugrations you'll notice that value_in_use column is of type sql_variant and you are trying to assign it to int variables. To fix this, use below statements in your script and it should work fine.

SELECT @advanced_options = cast(value_in_use as int)
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = cast(value_in_use  as int)
FROM sys.configurations
WHERE name = 'xp_cmdshell'


How to use select (query)alias on WHERE clause? i'm having a problem on my query, this is the working query and

    SELECT res_ID,res_fName,res_mName,res_lName,rs.suffix,rms.marital_Name,rg.gender_Name,rr.religion_Name,ro.occupation_Name,ros.occuStat_Name,res_Date_Record,rc.country_citizenship,res_Bday,TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) AS age,
(case
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1) then 'Maternal and Newborn'
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=12) then 'Babies'
when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=24) then 'Toddlers'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=2 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=4) then 'Preschoolers'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=5 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=8) then 'School Age Children'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=9 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=12) then 'Tweens '
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=19) then 'Teenager'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=20 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=35) then 'Young Adult'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=36 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=55) then 'Middle-Aged Adults'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=56 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=100) then 'Senior'
   end) Age_Stage
FROM resident_detail rd
LEFT JOIN ref_suffixname rs ON rd.suffix_ID = rs.suffix_ID
LEFT JOIN ref_gender rg ON rd.gender_ID = rg.gender_ID
LEFT JOIN ref_marital_status rms ON rms.marital_ID = rd.marital_ID
LEFT JOIN ref_religion rr ON rr.religion_ID = rd.religion_ID
LEFT JOIN ref_occupation ro ON ro.occupation_ID = rd.occupation_ID
LEFT JOIN ref_occupation_status ros ON ros.occuStat_ID = rd.occuStat_ID
LEFT JOIN ref_country rc ON rc.country_ID = rd.country_ID

this is the Result:

i want is use the alias "Age_Stage" column on WHERE statement,
on the image above Age_Stage has their specific value, i just want is to filter those Age_Stage by value that i want.
but when i add on the query
 WHERE Age_Stage = "Young Adult" on the of query the error is result is

  Unknown column 'Age_Stage' in 'where clause'

Renaming takes place after WHERE. You can either repeat the CASE statement:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
WHERE (case ... end) = 'Young Adult'

or nest the statement:

SELECT ...
FROM (
    SELECT res_ID,res_fName,res ...
       (case ... end) as Age_Stage
    FROM resident_detail rd
    LEFT JOIN ...
) AS T
WHERE Age_Stage = 'Young Adult'

Or you can try add it in an HAVING clause. As noted by ypercubeᵀᴹ in a comment, MySQL deviates from the standard by evaluate HAVING before renaming ( ... as Age_Stage). This is a trick that appears to be quite commonly used for MySQL in situations like this:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
HAVING Age_Stage = 'Young Adult'


Rolling changes to production databases We're currently using Clustering to do fail over for our SQL environment.

There are two servers that use the same shared drive.  The SQL database is on a shared drive.  This provides OS and hardware failover, however there is only a single copy of the database.

What we need is two copies of our database.  We need this to allow us to roll changes into production safely.  The deploy process would be to have one the web server point at one SQL Server, while we failover the other server to do the deployment.  Then we would point reverse this configuration to update the first SQL Server.

Does anyone know how to use clustering to do that?

General rules for deployment would have kinks worked out when deploying to a TEST, QA or DEV system so there are no issues with the PROD deployment.  If you are already running a cluster you can add an instance to the cluster at no licensing cost and use that as your TEST environment to iron out the deployment process.  If the deployment to TEST goes south then you simply restore the database from the PROD backup, change the scripts and try again.


How to delete rows from the mid of the table without any where condition? I have a table testing with two columns

id: auto_increment
name: varchar(20)

I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records from MySQL table (InnoDB engine). How can I achieve this?

This table I have created is just for testing purposes. My actual table contains billions of records. One way I thought as:

delete from testing where id in (select group_concat(id) from testing limit 1, 2);

I think this would be quite slow in case of my actual table.

Can anyone provide any better solution?

What you are asking for can lead to unpredictable results in certain situations.
What kind of situations?

Running this on a Master and the Slave receives the same SQL. The problem I see is that rows of data in a Master may not be physically written in the same order on the Slave.
Triggers will encounter issue (or really create issues) with this type of DELETE query

I am sure there are other situations, but these came to mind first.
I addressed these before

Feb 22, 2011 : Problem with MySQL subquery
Jul 30, 2012 : Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

SUGGESTION
If you look back at the original question (Problem with MySQL subquery), the OP suggested getting the key for the row you want deleted. Then issue the delete using that key. It's the safest way.
YOUR ACTUAL QUESTION
Doing fancy DELETEs, even if possible, could produce unwantd results on Slave, on a Master, or even a Standalone DB Server when not using WHERE.
In the end, you must use WHERE for the safest and most expected results.


Can mirroring be used to upgrade SSRS? Can you use mirroring to upgrade SSRS?

For instance, I've upgraded from SQL 2005 to SQL 2014 with mirroring.

SSRS is running on the same box as my database server.  I'm going from SQL 2012 (11.0.5343) to 2014 SP1.

I was hoping to get info from the community to upgrade in the easiest way with any possible caveats.

Assuming native mode here...

The main thing you are migrating when doing the upgrade to SQL Server 2014 is the reports, subscriptions, and then your data connections. I can't think of a reason why you would need to put this database in replication just for an upgrade. Also, while it probably could be done (and work) it is not a supported method of upgrade from Microsoft to put the SSRS databases in mirroring configuration. So proceed at your own risk.

Unless you are adding/modifying reports or subscriptions often I would not see why you could not just do a quick migration according to documentation. Perform your testing of the new version with your reports, and then at cut-over time just do a backup/restore one last time.


Scale vs. Precision - Why this definition? From the Microsoft documentation concerning T-SQL / SQL Server:

  Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

To the naive non-expert (=me) this is always a bit confusing. I would have expected it the other way round.

How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?
How can I think of the number of decimals in terms of scale (how 'big' something is)?

Just to be clear: This is not a rant disguised as a question, and more than mere curiosity. I am convinced that there is a good reason for this terminology, and I think I can learn about databases by understanding the underlying thinking here. (If this question is considered too basic for here, I understand.)

Precision and Scale are based on the mathematical concept of Significant Figures.  You're not alone in that the current definition/usage for these terms is confusing.

  How does the total number of digits relate to the everyday concept of precision (how 'exact' something is measured)?

First, you must remember that everything in computers is represented in binary; however not all numbers are easily represented using binary syntax, specifically when it comes to certain fractions.  One such fraction, ⅓, is actually impossible to represent in a computer.  Even with the largest amount of memory possible, a computer will never be able to accurately represent ⅓ in binary form.  However, it can get more precise (e.g. close) to ⅓ with the more bits (e.g. and therefore decimal places) that are thrown at it.

Precision can therefore be thought of as how exact a number you want to represent.  The more digits the more precise that number becomes.

  How can I think of the number of decimals in terms of scale (how 'big' something is)?

Since Precision is a measure of how exact the number you wish to represent is and we know we're limited in how much we can store, you have to sacrifice precision for scale.  So basically the smaller the scale the larger the final value can become.

Scale may best be explained with how we look at maps.  A map of a state or province shows a much larger scale of area with less detail, whereas a map of a city shows a smaller area at greater detail.

Scale can therefore be thought of as the amount of detail.  The more detail we want to convey comes with the cost of showing less actual stuff.

I don't know if that helps you at all, but hopefully that helps provide some context behind the definitions of these terms.


PostgreSQL - Execution plan - Actual time meaning I have a question regarding the meaning of actual time in an execution plan.

I read the following things but they seem to be contradictory:

The first value is the time to retrieve the first line and the seconds value is the time to retrieve the last line.
When I want to know which operation takes a long time, I take the second value of actual time of the outer operation and the second value of actual time of the inner operation.

Example:

EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1;

QUERY PLAN
-> Sort(cost=172682.84..175182.84 rows=1000000 width=37)(actual time=612.793..729.135 rows=1000000 loops=1)
   Sort Key: c1
   Sort Method: external sort  Disk: 45952kB
   -> Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37)(actual time=0.017..111.068 rows=1000000 loops=1)
Total runtime: 772.685 ms

According to the point 1, the sort operations takes (729 - 612) 117ms.

According to the point 2, the sort operation takes 618 ms.

Can anybody explain to me what I'm missing here?

Your assumption 1) is wrong.

The first time is the time it takes to initialize the step. The second time is the total time the step took (including child steps).

So the total time for the sort step is 729ms - 111ms of that were spent retrieving the data through a Seq Scan.


Database Backups of 2GB+ I have a forum which is growing way faster than I could imagine, and my site has been a target for DDoS attacks, NTP attacks, and a bunch of hacking attempts. I am starting to worry that there may be a compromise soon with the way these attacks are going about.

The forum database grows an average of 200MBs a day worth of forum topics, posts, and data. I am backing up my system once a week currently to make sure that I won't lose too much data if something is to happen.

Please Note: The backups are transferred to my file storage server after they have been created. I am keeping two backups of the data. One at the beginning of the month and one in the middle of the month, and the final one at the end of the month for the life-time of the site. The others are deleted after three months.

Am I making sure my data is protected enough in case I have to use these backups, or should I keep more copies throughout the lifespan of the site?

Some important factors to consider for backup are:

how annoyed the forum users would be if X days of activity disappeared
the probability of that happening
how long will it take to recover the data if it's backed up
how much does backup interrupt the normal operation of the site

As for, "Am I making sure my data is protected enough ...?", I'll ask a question back. What Recovery Point Objective (RPO) and Recovery Time Objective (RTO) are you aiming for?

Your current backup schedule has a recovery point of

for the current month, to the nearest weekly backup
for the last three months, to the nearest 1/2 monthly backup
for the "life-time of the site", to the nearest monthly backup

I'll suggest that you add daily backups to your rotation. If space is a concern, look into differential backups that only backup the data created that day. If space is a concern and RTO is not of paramount importance, you can reduce the size of backup files by compressing them. Backups of a DB containing primarily text might compress well.

By the way, if your forum keeps growing at an average of 200 MB/day (and growth is not accelerating), that's 6 GB/month. If you keep doing full backups on the schedule you outlined and backups are more-or-less the same size as the forum DB and they aren't compressed, by the end of the year you will have:

January monthly backup, 2 GB
February monthly backup, 8 GB
March monthly backup, 14 GB
April monthly backup, 20 GB
...
September monthly backup, 50 GB

September 1/2 monthly backup, 53 GB

October monthly backup, 56 GB

October 1/2 monthly backup, 59 GB

November monthly backup, 62 GB

November 1/2 monthly backup, 65 GB

December monthly backup, 68 GB

Week 1 backup, 69.5 GB
Week 2 (December 1/2 monthly) backup, 71 GB
Week 3 backup, 72.5 GB
Week 4 backup, 74 GB

approximately 1 TB of stored backups. And backups will be taking 20-40 times longer than they are now. An interesting followup question would be, "Are you doing cold or hot backups?"


Create stored procedure with dynamic number of columns + column names I am relatively new to postgresql (I have version 12), and am having some trouble creating my first stored procedure. The real trouble is that I need it to be dynamic in some way, and thus I am having trouble finding previous threads that have asked this question.

Say I have a table with the following information:

Table nominal_dollars:

year       GDP        Dividends
2000      100              20
2001      110              30
2002      120              40

In the above table, the numbers are not adjusted for inflation. A common procedure I want to do is create a new table, in which the numbers are adjusted for inflation. This will mean I need to join the above table with a new table, the consumer price inflation (CPI), which has the following form

Table cpi:

year       cpi_adjustment
2000      1
2001      2
2002      3

This will allow the creation of a new table, in which all of the numbers are adjusted for inflation, i.e. the original numbers are multiplied by the cpi adjustment:

Table nominal_dollars_adj:

year       GDP        Dividends
-----------------------------------------
2000      100              20
2001      220              60
2002      360              120

Where GDP  for the new table equals GDP from the original table * cpi_adjustment, and the same for dividends.

Now, I want to do this CPI adjustment procedure for many different tables --- which may have different numbers of columns.

So I want to create a stored procedure that takes as an input a table (or table name), and inside this function, I want to create a new table with the same exact names and number of columns as the original table, but with all of the values adjusted for inflation as above.

I can select all of the columns names I need to create the new table from the command

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'nominal_dollars'
ORDER BY ordinal_position;

I can then loop through these column names using the following loop

   FOR temprow IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'test' ORDER BY ordinal_position;
    LOOP
        execute 'UPDATE test SET temprow = temprow / 2'
    END LOOP;

But I'm having trouble putting it together in a stored procedure that will actually work.

I have the following snippet of code which should work for MySQL but does not work for Postgres. Any advice on how to get it to work?

CREATE PROCEDURE [dbo].[Results_Inflation_Adjusted]
@TableName    VARCHAR(50)
AS
BEGIN
DECLARE @sql VARCHAR(5000);

SET @sql = 'SELECT ';

SELECT @sql +=  CASE ColName WHEN 'ID' THEN  @TableName + '.' + ColName WHEN 'Year' THEN  @TableName + '.' + ColName  ELSE @TableName + '.' + ColName + ' * cpi_adjustment' END + ' As ' + ColName + ','

FROM
(SELECT COLUMN_NAME AS Colname
    FROM INFORMATION_SCHEMA. COLUMNS
    WHERE TABLE_NAME = @TableName
    --ORDER BY ORDINAL_POSITION
) temp

SET @sql = LEFT(@sql,DATALENGTH(@sql)-1)  --remove last comma
SET  @sql = @sql + ' From ' + @TableName + ' inner join CPI on ' + @TableName + '.year = ' + ' CPI.year' ;
EXECUTE(@sql)
END

When passing a table name as a parameter, you can create a clone of this table in a plpgsql function or procedure with simple code like this:

CREATE FUNCTION example(tblname text) RETURNS void AS
$$
 begin
   execute format('CREATE TABLE %I (LIKE %I)', tblname || '_adj', tblname);
 end
$$ LANGUAGE plpgsql;

See Executing Dynamic Commands in plpgsql documentation for more.

To adjust the values in columns like GDP or Dividends, you may automate this with a dynamic UPDATE similarly to the CREATE TABLE, if the columns that need the updates exist under identical names in all these tables (but that seems unlikely except if the schema was designed up-front with this constraint in mind).


What are the performance implications of creating an index in MySQL? The MySQL reference guide doesn't (from what I see) detail the implications of creating an index; by this I mean the performance implications and any locks it may take on the table or columns against which is building the index.

If anyone could give me an idea as to what the implications are, it would be appreciated; what would be appreciated even more is a link to some documentation where I can answer this question for myself!

Regards,

Chris :D

Since the creation of an index is DDL, it must always incur a lock on all moving parts of the table. How does the locking work ?

MyISAM

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/mydb/mytable.MYD (MyISAM Data)
/var/lib/mysql/mydb/mytable.MYI (MyISAM Indexes)

By means of a full table lock, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation.

InnoDB

For the table mydb.mytable whose datadir is /var/lib/mysql, there are file handles on the following files:

innodb_file_per_table disabled

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/ibdata1 (Data and Index Pages for mytable)

innodb_file_per_table enabled

/var/lib/mysql/mydb/mytable.frm (Table Definition)
/var/lib/mysql/mydb/mytable.ibd (InnoDB Data and Index Pages)

By means of a full table lock as well as the Clustered Index, all INSERTs, UPDATEs, and DELETEs are held in abeyance until the completion of the index creation. There are also too many moving parts to the lifecycle

Can I setup a temp table for index creation?

One could attempt to use an external table as follows:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD INDEX (some_column);
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;
DROP TABLE mydb.mytable_old;

This does not work under two conditions

CONDITION #1 : If there are any INSERTs, UPDATEs, or DELETEs during the first 3 steps, the RENAME steps will have the incoming data disappear.
CONDITION #2 : If the table has foreign key constraints, CREATE TABLE mydb.mytable_new LIKE mydb.mytable; will not create those constraints in the temp table because

a table with foreign key constraints can only have one parent at a time
two tables cannot have identical constraints with the same parent

While there is a tool that can bypass these limitations and allow INSERTs, UPDATEs, or DELETEs (called pt-online-schema-change), the simplest approach for a creating an index is to just bite the bullet and let mysqld lock the table.


Representing multiple attributes for students I have a data structure that has group of students.
Each student has an array of attributes - that is, he has an array of strings that represent attributes

How do I plan my data structure? Have a table of students and another table of classes in each grade has a pointer to his students

I need to save all to database - on android device.

Or something else ?

I would have a Student Table which is a parent table. These represent single line attributes, which can all fit in this one table.
If you need any data, where a student may have multiple attributes (example signing up for many classes, or different address types), to avoid multiple rows and data redundancy, I would create child tables.

Parent Table:

  *create table dbo.Student
  (
      StudentId int identity(1,1) primary key,
      FirstName varchar(55) not null,
      LastName varchar(55) not null
  )*

Child Tables:

  *create table dbo.CourseCatalog
  (
      CourseCatalogId int identity(1,1) primary key,
      ClassName varchar(55) not null,
      CourseStartDate date not null,
      CouseEndDate date not null
  )*

  *create table dbo.StudentEnrollment
  (
      StudentEnrollmentId int identity(1,1) not null primary key,
      CourseCatalogId int not null foreign key references CourseCatalog(CourseCatalogId),
      StudentId int not null foreign key references Student(StudentId),
  )*

  *create table dbo.AddressType
  (
      AddressTypeId int identity(1,1) not null primary key,
      AddressType varchar(55) not null    -- Example 1 is Home, 2 is Campus Address
  )*

  *create table dbo.StudentAddress
  (
      StudentAddressId int not null primary key,
      StudentId int not null foreign key references Student(StudentId),
      AddressType int not null foreign key references AddressType(AddressTypeId)
  )*

by the way, can someone edit to properly remove asterisks, also feel free to add to this as needed


Low Cardinality of Index performing better than High Cardinality? For testing purposes I have 2 Tables TableA and TableB in an mysql-InnoDB, both with the same columns

id bigint not null auto_increment,
depot smallint not null,
custno bigint not null,
range_from bigint not null,
range_to bigint not null,
depot2 smallint not null,
primary key (id)

and the same data (~5M rows). Now TableA has the index

unique index (range_from, range_to, custno, depot)

and TableB has the index

unique index (depot, range_from, range_to, custno)

The cardinality of "depot" is very low (~100 different values), the cardinality of "custno" is medium, and the cardinality of range_from and range_to is very high.

The Select

select sql_no_cache custno,depot,range_from,range_to
from TableB
where $value between range_from
                 and range_to
  and depot=$other;

takes about .8 seconds.
Explain says:

+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table     | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | TableB    | range | range_from    | range_from | 8       | NULL | 1992762 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+

Now the same select on TableA takes .03 sec and here is what explain says:

+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows   | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | TableA   | range | depot         | depot | 10      | NULL | 107736 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+

Even though the Index starts with a low cardinality column the select outperforms the other index by a factor of at least 20.
Can someone please explain this to me like i am 4 years old? Every article I read so far advocated for using the high cardinality values first in indices.

Mysql version is 5.5.32 btw.

The between condition is short form for:

 ... range_from <= $value
 and $value <= range_to

In this context range_from and range_to have an effective cardinality of 2, in range or out of range.

Given your indexes the range_from needs to be searched for all values up to and including $value.  If value is close to the end the possible values, the number of records that need to be checked is quite high.  If you are looking for recent records, this is will be close to all the records.

Using the index with the depot column first, far fewer records match the range_from value.  If you have 100 depots, you have 1% of the records to check compared to not using it.  Depots with few records will be faster than those with more records.  Your results on table A will vary depending on the value of $other.

In either case, the query could be completed within the index.  Without depot in the index, the query optimizer might decide to scan the table rather than the index.  Some SQL dialects allow a hint to the optimizer indicating the number of rows the query will match.  This can influence the resulting query plan.

While you consider the cardinality low, from an optimization standpoint, it is quite high it reduces the number of index rows to be scanned by a factor of 100.

While the cardinality of the from_range may be high from a values standpoint. When running a range search it is of little value in searching for records.  For high values (which may be the most common case), it will match close to 100% of the records.  Its high cardinality, is of more use when running a range check of the form:

where from_range between $low and $high

The index may be more useful if the range columns are ordered range_to, range_from.


Correct composite indexing indexing order I have MySQL (MariaDB) database. I have a table 'sensors' ,which collects data from IoT devices.
Each device may have 4-6 parameters that it records, like temperature, humidity, air quality, etc. Each device sends a measurement once every minute.
There are 10-15 such devices. Each device has its deviceid.
Six columns:
`autoid`(INT,AUTOICREMENT)
`deviceid`(varchar)
`pname`(varchar)         /* name of parameter like temperature,humidty */
`pcode`(INT)             /* code for each parameter like for temperature its 11,humidty its 12 etc */
`datavalue`(double)      /* value of parameter */
`rectime`(INT)           /* UNIX timestamp */

Here is sample of table data:

autoid
deviceid
pname
pcode
datavalue
rectime

1
sdbjs4b
temp
11
30.54
1621702300

2
sdbjs4b
hum
12
104
1621702300

3
sdbjs4b
gas
13
768
1621702300

4
vsf5bjs
temp
11
31.45
1621702300

5
vsf5bjs
volt
15
5.10
1621702300

There are almost 4-5 million rows in the sensors table.
My query requirements: I have to get data for some arbitrary time values for each day for each device and parameter.
Here is query that is used:
SELECT * from sensors where deviceid =? AND  pcode = ? AND rectime =?

This is guaranteed to give me only one result. The problem is I need to run this query inside nested loops, worst case 500 times. Why I need looping?. I need to create a report between two dates for each device, parameter, and for a given set of time slots between two dates. I have to loop for values of time slots.
I have a composite index on (deviceid,rectime,pcode).
What is the difference if I change this index to (rectime,deviceid,pcode)?
In general, will column order matter in composite indexing if my query uses all indexed columns in the where clause?

I think your approach with nested loops is suboptimal.
Why can't you do something like:
select * from sensors where deviceid =? AND  pcode = ? AND rectime between ? and ?

This would return the whole dataset and you  could process it locally.
Selecting 500 or even more rows in one correct select is better then 500 single row selects.
In this case I would change the the clustered index to
(deviceid,pcode,rectime)

you can still keep the autoId primary key, just make it nonclustered.
In addition, the way your select works, it has to do key lookup afterwards to get one single extra row. MySQL AFAIK doesn't support include on indexes, but even then, it would be a de facto reordered copy of the table so clustered index makes sense.
As for if the order matters.... Well, yes and no. Given your datatypes and size of table, the difference would be minimal.


How do I copy the structure and contents of some tables from one database to another? How do I copy the structure and contents of the tables below in SQL Server Management Studio from database SNO_TEST to SNO_TMP (on the same server)?

The names of the tables are ADMIN_ErrorLog, AdminMissingFiles and SNO_QAErrorCategory

(I would have assumed there was already a question on this, but I couldn't find a general one for SQL Server, just narrowly tailored ones, or ones for other platforms.)

The method I chose would probably depend on how big the tables are, and also whether there are any other objects associated with the tables (like indexes, triggers, constraints, etc.). But here are a few methods that might work:

Use the "Import/Export Wizard".

This is probably the simplest. Right click the source database and go to Tasks > Export Data... I'm not going to walk through all the steps of the wizard here, but you can select the source and destination databases, then choose which tables you want to copy. Here's a page that walks through a simple example, although they are using it for importing data from Excel.

After copying the data, add any necessary indexes/constraints/etc.

Do a SELECT INTO

With very small tables, I'll just do a very quick:

SELECT *
INTO myDestinationDB.dbo.tablename
FROM mySourceDB.dbo.tablename

Replacing with your database names and table names, of course.

This creates the destination tables on the fly. You'll need to add indexes/constraints yourself afterwards. Also be aware that this won't always work for some limited situations like identity columns.

Script out the table definition, then do an INSERT INTO

Right-click the table in the source database, and choose "Script Table as > Create To > New Query Editor Window"

Then change the USE databasename at the top to your destination database, and run the CREATE statement to create the table. Now is your chance to also add indexes or constraints, if necessary. Then to copy the data, do something like:

INSERT INTO myDestinationDB.dbo.tablename
SELECT * FROM mySourceDB.dbo.tablename

If these tables have millions and millions of rows, definitely use the 1st method above, or use an advanced technique like a custom SSIS package (not covered in this answer), otherwise you might fill up your transaction log.

Hope this leads you in the right direction. Good luck.


Do SET Statements happen in order? Since I could not find it out here, do set statements happen in order scripted? Below is query I found. Would I get the same result every time it ran?

DECLARE @Num VARCHAR(10) = '10';
DECLARE @Cleaned VARCHAR(10);
SET @Cleaned = SUBSTRING(@Num, PATINDEX('%[^0]%', @Num+'.'), LEN(@Num));
SET @Cleaned = CASE
                   WHEN LEN(@Cleaned) > 3
                   THEN @Cleaned
                   ELSE RIGHT('00000'+@Cleaned, 3)
               END;
-- to be set up
SELECT @Num AS [before]
     , @Cleaned AS [After]

Yes, you will get the same results in every case. There are a few exceptions where SQL Server doesn't seem to process the code in the order the code is written, and a few cases where there are no guarantees at all. Some are intentional and some are unintuitive.

GOTO

GOTO b2;

b1:
PRINT 'first';
GOTO b3;

b2:
PRINT 'second';
GOTO b1;

b3:
PRINT 'done';

Results:

second
first
done

This might not be expected simply because these labels can get lost in more complex code, can be hit only in certain conditions, etc.

Deferred Name Resolution

CREATE PROCEDURE dbo.floob AS
  SELECT * FROM dbo.ThisTableDoesNotExist;

This is kind of related in that it allows you to create an object at compile time that references an object that will cause the procedure to fail when you call it. I mention it only because it allows you to create this procedure before you create the table that the procedure will ultimately depend on.

SQLCMD mode

There is a mode in SSMS that allows you to intersperse SQLCMD and T-SQL. SSMS will make a first pass at the code and process all the SQLCMD bits, and then make a second pass where it will process the T-SQL. Let's say you have a table with an identity column, and a file that includes an insert into this table. With the following script run in SQLCMD mode, the filename.sql query will get executed on the first pass, and the insert of Bob will happen on the second pass. So the IDENTITY values will be generated in the opposite order:

:CONNECT instance
INSERT dbo.table(name) VALUES('Bob');
:r filename.sql

Compile Time Name Checking

IF 1=1
  CREATE TABLE #foo1(x int);
ELSE
  CREATE TABLE #foo1(y int);

Result:

  Msg 2714, Level 16, State 1, Line 4
  There is already an object named '#foo1' in the database.

The parser has gone ahead to line 4 and prevented you from creating two objects of the same name, even though that would never have been possible at runtime due to the IF conditional never being able to evaluate as true (line 2 was never executed).

Order of execution of a single statement

Depending on the version and compatibility level of SQL Server, you may see various cases where clauses are not evaluated in the order you expect. E.g.:

CREATE TABLE #x(y varchar(32));

INSERT #x(y) VALUES('foo'),(4);

SELECT y FROM #x
  WHERE ISNUMERIC(y) = 1
    AND y < 5;

Result:

  Msg 245, Level 16, State 1, Line 5
  Conversion failed when converting the varchar value 'foo' to data type int.

SQL Server tried to evaluate the y < 5 predicate before the ISNUMERIC() result could filter out ineligible rows. People often first try to use a CTE or correlated subquery, but that often fails because you still can't control the order in which these things are evaluated. Typical workarounds include using TRY_CONVERT() or a CASE expression (example here, and more on CASE in a minute), while more elaborate and less desirable approaches exist (such as dumping the filtered rows to a #temp table first).

CASE + AGG

While in most cases a CASE expression is the only surefire way to guarantee order of evaluation (misconceptions abound, though), even that can fail in some scenarios, one of which was discussed here at great length:

Example that fails, even though the ELSE should never be reached:

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

Result:

  Msg 8134, Level 16, State 1
  Divide by zero error encountered.

JOIN order

For completeness I want to make sure it is clear that if your query says...

SELECT ... FROM t1
  INNER JOIN t2
  INNER JOIN t3

...SQL Server is free to perform those joins in whatever order is most efficient, unless you use OPTION (FORCE ORDER) or any other hint that implicitly forces order. So you may see join strategies that optimize a join between t1 and t3, and then join those results to t2.

Surely, there are others...

...those are just a few off the cuff.


Is it possible to duplicate a standby database on the fly? Can I duplicate a standby database on the fly?
Currently, I'm getting this error:

RMAN-03002: failure of Duplicate Db command at 01/08/2021 01:55:58
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 3
was not restored from a sufficiently old backup

When I run:
DUPLICATE TARGET DATABASE TO clone_db FROM ACTIVE DATABASE;

Any idea?

Step 2: Mount or Open the Source Database Before beginning RMAN
duplication, mount or open the source database it if it is not already
mounted or open. If the source database is open, then archiving must
be enabled. If the source database is not open, and if it is not a
standby database, then it must have been shut down consistently.

Duplicating a Database

To create a standby database with the DUPLICATE command you must
connect as target to the primary database and specify the FOR STANDBY
option. You cannot connect to a standby database and create an
additional standby database.
Basic Concepts of Standby Creation with RMAN


How to get max value of a column from one table with its matching record in second table in PostgreSQL? I have two tables as follows:

create table two(grade_id int, edu varchar(20),sortby int);
alter table two add constraint pk_one primary key(grade_id);
create table one(id int, name varchar(20),grade int);
alter table one add constraint fk_two_one foreign key(grade) references two(grade_id);

Now I insert some records in both tables:

insert into two values(1,'High School',1);
insert into two values(2,'Bachelor',3);
insert into two values(3,'College',2);
insert into two values(4,'Masters',4);
insert into two values(10,'PHD',5);

insert into one values (1,'Ahmad',10);
insert into one values (1,'Ahmad',3);
insert into one values (1,'Ahmad',4);
insert into one values (2,'Ghani',1);
insert into one values (2,'Ghani',3);
insert into one values (2,'Ghani',2);
insert into one values (3,'Fahim',4);
insert into one values (3,'Fahim',1);
insert into one values (3,'Fahim',3);
insert into one values (3,'Fahim',2);

Now I want to select id,name from table one with its edu and sortby columns from table two that has max value in sortby column.
So my desired output would be:

id   +    name   +   education  +   sortby
1    |    Ahmad  |      PHD     |     5
2    |    Ghani  |      Bachelor|     3
3    |    Fahim  |      Masters |     4

Anybody please help.

In Postgres the most efficient way is to do this using distinct on ():

select distinct on (o.id) o.id, o.name, t.edu, t.sortby
from one o
  join two t on o.grade = t.grade_id
order by o.id, t.sortby desc


Multiple IPs are not getting bind in mongodb conf file I am trying to bind multiple IP to mongodb config file, one is 0.0.0.0 and other is 127.0.0.1. I did the following change in configuration file

  /etc/mongod.conf

# network interfaces
net:
  port: 27017
  bindIp: "0.0.0.0,127.0.0.1"

Than I reloaded the configuration by the following command

sudo mongod --config /etc/mongod.conf

Than checked if the server is listening on localhost as well or not by the following command.

netstat -tlpn

It shows the output only for 0.0.0.0

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:27017           0.0.0.0:*               LISTEN      -

Why is it not listening to localhost as well?

had the same issue and the thing is everywhere it's written that you have to bind them like this

bindIp : 127.0.0.1,192.168.0.50

but that doesn't work.

The MongoDB version 3.2.x have the following configuration and its working for me.

    bindIp : [127.0.0.1,192.168.0.50]

    example :

# network interfaces
net:
      port: 27017
      bindIp : [127.0.0.1,10.12.2.1,10.12.65.1]


How to set open_files_limit on mariadb I encountered a strange behavior and I would like to understand better what is happening.
I set in my.cnf the parameter open_files_limit = 32000 in the [mysql] section.
I restarted MariaDB. I have two processes mysqld_safe and mysql.

Result of ps:

root     10439     1  0 11:27 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe
--datadir=/var/lib/mysql/data
--pid-file=/var/lib/mysql/data/rcentdb02.pid

mysql    10880 10439  0 11:27 pts/1    00:01:14 /usr/sbin/mysqld --basedir=/usr
--datadir=/var/lib/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql
--log-error=/var/lib mysql/data/rcentdb02.err --open-files-limit=32000
--pid-file=/var/lib/mysql/data/rcentdb02.pid --socket=/var/lib/mysql/mysql.sock
--port=3306

Result of cat /proc/$(pgrep mysqld_safe$)/limits | grep "Max open":

Max open files            32000                32000                files

Result of cat /proc/$(pgrep mysqld$)/limits | grep "Max open":

Max open files            90162                90162                files

My question is: why mysql_safe have the good parameter for open_files_limit but not mysqld process?

Thanks.

The result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 90162.
I tried to remove the open_files_limit from my.cnf but it does not work.

I have always this value 90162 which seem to be setup nowhere ...

Any idea?

I setup my.cnf to have:

[mysqld]
open_files_limit = 32000
[mysqld_safe]
open_files_limit = 32000

I stop mysql and start mysql.
Result of SHOW GLOBAL VARIABLES LIKE 'open_files_limit'; => 90162

I changed the value of open_files_limit to 100000 and now the result of query SHOW GLOBAL VARIABLES LIKE 'open_files_limit' is 100000. So now my question is : I understand that mysql may autoadjust this parameter but how he can choose a value (90162) higher than the system limit ... ?

With root account:

ulimit -Sn => 1024
ulimit -Hn => 4096

With mysql account (I changed nologin to bash for my test):

ulimit -Sn => 32000
ulimit -Hn => 64000

You may have to login to MariaDB and run
SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

and see if this is 32000 or 90162. mysqld may be capping it.
It is entirely possible that open_files_limit is getting autoadjusted. The Documentation says Default:5000, with possible adjustment and Maximum:platform dependent.
Here is how mysqld_safe and mysqld interact:

mysqld_safe will pass values to mysqld
mysqld may override values passed from mysqld_safe

According to MySQL Documentation on mysqld_safe:

Many of the options to mysqld_safe are the same as the options to mysqld. See Section 5.1.4, “Server Command Options”.
Options unknown to mysqld_safe are passed to mysqld if they are specified on the command line, but ignored if they are specified in the [mysqld_safe] group of an option file. See Section 4.2.6, “Using Option Files”.

What you may need to do is remove open_files_limit from my.cnf and let mysqld_safe assign the value it knows to mysqld.
Then, restart mysqld.
UPDATE 2018-02-28 08:24 EST
You may need to add [mysqld_safe] header in my.cnf and put open_files_limit=32000, like this:
[mysqld_safe]
open_files_limit=32000

However, don't restart mysqld. This may not properly terminate mysqld_safe because it contains an indefinite loop to check the return code of mysqld when it terminates. There is a certain return value that lets mysqld_safe know to relaunch mysqld. This means that mysqld_safe does not terminate when it handle a mysqld restart.
It is better to do the restart in two steps
service mysql stop && service mysql start

Doing service mysql stop will terminate mysqld and then exit the indefinite loop in mysqld_safe, terminating mysqld_safe. Then, launching service mysql start will launch a fresh mysqld_safe process. Give it a try and let us know.
If you cannot edit my.cnf, then specify the option on the command-line:
service mysql stop && service mysql start --open-files-limit=32000


Oracle's left join and where clauses errors CREATE TABLE "ATABLE1"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

CREATE TABLE "ATABLE2"
  (
    "COLUMN1" VARCHAR2(20 BYTE),
    "COLUMN2" VARCHAR2(20 BYTE)
  );

Insert into ATABLE1 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE1 (COLUMN1,COLUMN2) values ('B','2');

Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A',null);
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','1');
Insert into ATABLE2 (COLUMN1,COLUMN2) values ('A','2');

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    GROUP BY ATABLE1.column1;

Result

COLUMN1              COUNT(ATABLE2.COLUMN1)
-------------------- ----------------------
A                    3
B                    0

This works as expected. The thing is that I always want all rows from ATABLE1 to be shown and also apply some restrictions.

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2 on ATABLE1.column1 = atable2.column1
    where atable2.column2 = '1'
    GROUP BY ATABLE1.column1;

COLUMN1              COUNT(ATABLE2.COLUMN1)
-------------------- ----------------------
A                    1

Why are not all columns from ATABLE1 being shown even with left join? How can I make them appear?

Thanks a lot in advance.

When you add WHERE filters to the optional/outer table, then you change the query into an INNER JOIN. You need to add the condition into the join, or derived table, or CTE.

select ATABLE1.column1, count(ATABLE2.column1)
    from ATABLE1 Left OUTER JOIN ATABLE2
         on ATABLE1.column1 = atable2.column1 AND atable2.column2 = '1'
    GROUP BY ATABLE1.column1;


Can I use xtrabackup to import a database without altering others? Context:

Each time a demo branch is built, I want to download a copy of the prod database and setup it onto the demo server. This way under the MySQL I have X database corresponding to X demos.

I achieved it using the mysql_import but it takes 3 ~ 5 minutes when importing the DB. So to speed up I wanna use Xtrabackup.

Problem:

The problem is that during the full backup, xtrabackup copies all the necessary files and also the ib_logfilefiles.

So let's say I want to import the database into database named demo_1874.

I do a full backup of the database, then I run --prepare twice on the same server, and I compress the result.
Download on the other server, uncompress all the backup files
Paste them under /var/lib/mysql which will override the other files (ib_logfile)
Rename the folder original_db to demo_1874

Question

By copying files like ib_logfile, will it break all the other databases I have on this MySQL server (which I don't want this to happen) ?
What are the other options using Xtrabackup to import a database among others without altering the others ?

What you need is partial backups.
innodb_file_per_table=ON is a prerequisite to make it work.

In your case procedure would be like following:

Take a partial backup from the production server

innobackupex --databases="mydatabase" /path/to/backup

On the destination server prepare the backup copy for export:

innobackupex --apply-log --export /path/to/backup

For each table repeat these steps:

create a new table with the same structure (get it from the production server with mysqldump -t production or SHOW CREATE TABLE t)

demo_1874> CREATE TABLE t (...) ENGINE=InnoDB;

Discard tablespace that InnoDB has

demo_1874> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; created.

Copy t.* files from partial backup to /var/lib/mysql/demo_1874, chown mysql:mysql them, and import the tablespace from the partial backup

demo_1874> ALTER TABLE t IMPORT TABLESPACE;

Consult http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html for more details.


Microsoft Master Data Services (MDS) Migration | 2017 to 2019 We're in the process of upgrading our MDS instance, from 2017 to 2019, by means of a migration.  I can't find any good documentation online on how to do this in an efficient way.  Has anyone performed these steps and have some guidance to share?

Thanks in advance.

We ended up getting this to work by backing up the 2016 MDS database and restoring over the MDS database on the 2019 server.  You have to open Master Data Services Configuration Manager after the restore, select the database and choose Upgrade Database (you may get an error about 'clr strict security' which we had to disable).


Array of strings when updating a field Unintentionally discovered that following query works in PostgreSQL:

UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1;

I'm passing an array of strings to field and the result is a string of '("string1","string2")', not an array as expected, at least then using php extension pgsql.

How this happens and that does ('string1', 'string2') means in PostgreSQL? I don't think this works in other SQL RDBMSes.

I'm passing an array of strings ...

You are not passing an array, at least not a Postgres array type.
An array literal would look like this:

'{string1, string2}'

You can type it explicitly:

'{string1, string2}'::text[]

The same can be achieved with an ARRAY constructor in most places:

ARRAY['string1', 'string2']

This is constructing a row (of type "anonymous record"):

('string1', 'string2')

It's short syntax for a ROW constructor:

ROW('string1', 'string2')

And can, in similar fashion be provided as string literal (row literal) as well:

'(string1,string2)'

Each of those anonymous records can be cast to a well know row type:

'(string1,string2)'::my_row_type

In the UPDATE statement Postgres coerces the value to the data type of the target column or raises an exception if that cannot be done. If sometable.somefield is data type text, the text representation of the above row is saved, which is  '("string1","string2")'. Double quotes are only added where necessary to keep the syntax unambiguous.

Related:

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?
PostgreSQL: Issue with passing array to procedure


Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present? It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key constraint to the corresponding column in the second table.
It seems that in this scenario, the OUTER JOIN could be translated into an equivalent INNER JOIN, because each row in the first table is:

guaranteed to have a value in the column (NOT NULL constraint), and
guaranteed to have a matching row in the second table (trusted foreign key constraint).

For example, consider the following tables:
 CREATE TABLE dbo.tbl_fk
 (
    fk_val CHAR(1) NOT NULL PRIMARY KEY CLUSTERED,
    junk VARCHAR(100) NOT NULL
 );

 CREATE TABLE dbo.tbl_main
 (
    id INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1),
    fk_val CHAR(1) NOT NULL FOREIGN KEY REFERENCES dbo.tbl_fk(fk_val)
 );

In the following query, why does the optimizer not convert the LEFT OUTER JOIN to an INNER JOIN in the execution plan?
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val;

By adding a predicate to explicitly remove NULL values from the second table, however, the optimizer converts the LEFT OUTER JOIN into an INNER JOIN in the query plan, as expected:
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL;

Undocumented trace flags show that a rule is applied to change the OUTER JOIN to an INNER JOIN in the query with the predicate:
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8621);

***** Rule applied: A LOJ B -> A JN B

These examples were tested using SQL Server 2014 and 2019 using a number of different compatibility levels and also using both the legacy and "new" cardinality estimator.  The behavior appeared to be the same in all cases.
Forrest McDaniel pointed out that (as of 2010), Sybase appeared to have such a transformation built into their product (see Example 1): http://dcx.sybase.com/1200/en/dbusage/queryopt-sectb-5356466.html

I'm not sure there's a "good reason" why the query transformation doesn't happen, other than it hasn't been implemented.
You can see a potential benefit in queries like
SELECT m.fk_val, count(f.junk)
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
group by m.fk_val

Which can have the aggregate pushed before the join if it's an INNER JOIN.
SELECT m.fk_val, count(f.junk)
FROM dbo.tbl_main AS m
INNER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
group by m.fk_val

But in the case of the conversion of OUTER JOIN + Predicate on outer column, the outer column predicate is either eliminated entirely, or can be pushed to before the join.
EG, with
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.fk_val IS NOT NULL

This query is logically equivalent to the simpler query
SELECT m.fk_val, f.junk
FROM dbo.tbl_main AS m
INNER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val

which has one less predicate.
And if you try to push the outer table predicate before the join without changing it to an INNER JOIN you would still have to apply the predicate after the join.
For something like
SELECT m.fk_val, m.Id
FROM dbo.tbl_main AS m
LEFT OUTER JOIN dbo.tbl_fk AS f
    ON m.fk_val = f.fk_val
WHERE f.junk = 'a';

If you apply the f.junk = 'a' predicate before the join, you'll filter out some of the rows before the JOIN.  But then the inner rows corresponding to outer rows with f.junk <> 'a' would survive the  OUTER JOIN, and you'd have to apply f.junk = 'a' again after the join.


Clustered vs Non Clustered Indexes for a Primary Key I have a large table with around 19 million records in it.

The primary key is a uniqueidentifier column called ID and there is a nonclustered index on this column.  An application generates these GUID values.

The table currently has no clustered index on it.

I am considering changing from a nonclustered index to a clustered index, since the table currently has no clustered index. I am wondering what are the pros and cons of doing this or if I should?

Leave things as is
Find another column to create the clustered index on
Change this ID column over from a non clustered index to a clustered index.

We are getting about 50K inserts daily, with no deletes and no updates. Let me know if you need any additional details.

The table has 24 columns total and 5 nonclustered indexes without a clustered index:

Index 1- PK on ID (uniqueidentifier)
Index 2- TimeStamp (datetime)
Index 3- Hash Column (nvarchar(64))
Index 4- Covering index 4 columns
Index 5- Covering index 6 columns

I inherited the database from someone else. The application uses this GUID as a primary key with relationships to matching foreign keys in respective child tables so the queries use the uniqueidentifier id's in joins.

How do I determine if my GUID is generated sequentially? They don't appear similar. Some examples are as follows ordered by createddate:

24A689A0-831E-4670-9204-766256C58E43
ED001C69-11BA-430D-ACF9-3C3CADF3B400
768E5497-4F48-4C65-87D9-E9C27066708E

Doing a little research, it seems like a uniqueidentifier column is not a good candidate for a clustered index, so I'm wondering if I need to:

Look to a few columns to create a composite clustered index on these columns or
Live without a clustered index
Something else?

Let me know if I can provide any additional details. If anyone can give me some guidance as to what are some considerations for making this decision, I would appreciate that greatly.

Unless you switch to sequential GUID generation, you don't want the GUID as a clustered index key.  So your choices are

1) Make the ID column the clustered index key.  This will optimize joins and lookups on ID, but it sounds like those are rare.

2) For SQL 2016+ replace the Heap with a Clustered Columnstore index.  This compresses the data and optimizes table scans, perhaps to the extent that you can drop some of your other indexes.

3) Pick your most used non-clustered index and make that the Clustered index.

4) Leave it as a Heap.

In any case, the "child tables" that use the GUID as a foreign key might benefit from having that foreign key column as the leading column in their clustered primary key.