Oracle: Physically reorganizing rows of a huge table by a different key I have a huge table RECORD -- a few billion rows -- which contains data loaded continuously in a lot of batches. I can assign a primary key RECORD_ID from sequence, or use natural composite key BATCH_NUM + RECORD_NUM, which gives me a nice, unfragmented table where a batch with thousands of records only spans over several physical blocks, which is useful for next batch-aware step of processing.

But later I need to group together rows from arbitrary batches based on another field value, some RECORD_KEY. There are hundreds of millions of different RECORD_KEYs spread across rows of the table, therefore if I want to pick 10 rows with the same RECORD_KEY, it will almost certainly require retrieval of 10 physical blocks. Due to the table size this also cannot easily be cached so it is 10 physical reads (plus overhead on index traversal). This is obviously very slow.

Example:

Table RECORD

RECORD_ID is primary key from sequence
BATCH_NUM and RECORD_NUM are unique constraint (and could be a primary key)
RECORD_KEY is an indexed column

Sample data:

| RECORD_ID | BATCH_NUM | RECORD_NUM | RECORD_KEY
|         1 |         1 |          1 |  987654321
|         2 |         1 |          2 |  876543219
|         3 |         1 |          3 |  765432198
|         4 |         2 |          1 |  654321987
|         5 |         2 |          2 |  543219876
...
| 100000006 |   3000003 |          2 |  432198765
| 100000007 |   3000003 |          3 |  876543219
| 100000008 |   3000003 |          4 |  321987654
...
| 200000009 |   6000004 |          3 |  219876543
| 200000010 |   6000004 |          4 |  876543219
| 200000011 |   6000004 |          5 |  198765432
...

This SQL command will be fast because it will only have to retrieve one physical block:

select RECORD_ID from RECORD where BATCH_NUM = 1

This SQL command will be slow because it will have to retrieve three physical blocks -- one for each retrieved row:

select RECORD_ID from RECORD where RECORD_KEY = 876543219

EDIT:

The above is just an example. Typically I would have:

thousands of rows per BATCH_NUM
tens of rows per RECORD_KEY
thousands of rows per lookup, by BATCH_NUM of RECORD_KEY

SQLs look like this, both retrieving around 1000 rows:

select RECORD_ID from RECORD where BATCH_NUM = 123456

select RECORD_ID from RECORD where RECORD_KEY in (
  select COLUMN_VALUE from TABLE(batch_num_tbl) -- 100 values
)

The execution plans look both reasonable and similar -- using the respective index. However while the BATCH_NUM lookup takes under 1 second to execute, RECORD_KEY lookup takes around 20 seconds.

Options which I considered:

Partitioning: I could do of hash partitioning on RECORD_KEY. It would put the records a bit closer to each other, increasing the chance of them being in the same block, and enabling some partition-wise joins. Might help a bit but will not resolve the problem for me.
Index-organized table: Since it requires primary key, I would have to make primary key RECORD_KEY + RECORD_ID. Also the lookup on BATCH_NUM would then become slow.
Re-inserting the rows: A rather ugly solution, where I would regularly select rows ordered by RECORD_KEY, delete them and insert append them, effectively putting them next to each other.

Please note I am not any experienced DBA, just a developer stuck with this non-trivial DB problem.

Create an index on (record_key, record_id). This way oracle will most likely use the index only to answer your slow running query. You can add more columns to that index, so that you also get the needed content. Hopefully you don't need all columns.

Be careful about, what impact this index has on your load performance.


function hangs with null case operation I created a function that accepts a start and end date, with the end date being optional. I then wrote a CASE in the filter to use the start date if no end date is passed.

CASE WHEN @dateEnd IS NULL
    THEN @dateStart
    ELSE @dateEnd
END

When I call the function for the most recent month of the data:

SELECT * FROM theFunction ('2013-06-01', NULL)

... the query hangs. If I specify the end date:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

... the result is returned normally. I took the code out of the function and ran it fine inside a query window. I can't duplicate the issue the fiddle either. A query like:

SELECT * FROM theFunction ('2013-04-01', '2013-06-01')

... also works fine.

Is there anything in the query (below) that could be causing the function to hang when a NULL is passed for the end date?

SQL Fiddle

Execution plan for SELECT * FROM theFunction ('2013-06-01', '2013-06-01')
Estimated plan for SELECT * FROM theFunction ('2013-06-01', NULL)

Part of your initial query is as follows.

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart)

That section of the plan is shown below

Your revised query  BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) has this for the same join

The difference seems to be that ISNULL simplifies further and as a result you get more accurate cardinality statistics going into the next join. This is an inline table valued function and you are calling it with literal values so it can do something like.

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart)
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01')
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

And as there is an equi join predicate b.[Date] = a.d the plan also shows an equality predicate b.[Date] = '2013-06-01'. As a result the cardinality estimate of 28,393 rows is likely to be pretty accurate.

For the CASE/COALESCE version when @dateStart and @dateEnd are the same value then it simplifies OK to the same equality expression and gives the same plan but when @dateStart = '2013-06-01' and @dateEnd IS NULL it only goes as far as

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

which it also applies as an implied predicate on ColleagueList. The estimated number of rows this time is 79.8 rows.

The next join along is

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10))

colleagueTime is a 3,249,590 row table which is (again) apparently a heap with no useful indexes.

This discrepancy in estimates affects the join choice used. The ISNULL plan chooses a hash join that just scans the table once. The COALESCE plan chooses a nested loops join and estimates that it will still just need to scan the table once and be able to spool the result and replay it 78 times. i.e. it estimates that the correlated parameters will not change.

From the fact that the nested loops plan was still going after two hours this assumption of a single scan against colleagueTime seems likely to be highly inaccurate.

As for why the estimated number of rows between the two joins are so much lower I'm not sure without being able to see the statistics on the tables. The only way I managed to skew the estimated row counts that much in my testing was adding a load of NULL rows (this reduced the estimated row count even though the actual number of rows returned remained the same).

The estimated row count in the COALESCE plan with my test data was in the order of

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

Or in SQL

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList]

but this does not square with your comment that the column has no NULL values.


Insert script adds N before each varchar data I exported results from a query in order to import it into a table on another DB. I chose the xlsx output format and verified that the data had gotten written to the excel file. While trying to import it into the table, I chose to create an insert script so that I could go over the final data before importing it. On doing this, I noticed that all the varchar fields have a N character right before the opening quote, making it look like:

INSERT INTO ... ()
VALUES(N'...', 0.0, N'....')

Any ideas on why this might be happening and how I can avoid it?

Text literals with N (or n) in front of the opening quote indicate that the strings may contain symbols from a national character set, that is, they have the data type NCHAR or NVARCHAR. Either the target columns have these data types, or SQL Developers assumes that from the database NLS settings.

You can find more details in the manual.


Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work I'm trying to create a temp table using dynamic sql (MS SQL Server 13.0.5426).  This is a simplified example:

Using straight SQL works:

DROP TABLE #tmp_ts; -- will error, but ignored on the first execution
SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts;
SELECT * FROM #tmp_ts;

Creating it via dynamic SQL does not work.

DROP TABLE #tmp_ts; -- drop from previous test.
EXEC sp_executesql N'SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts';
SELECT * FROM #tmp_ts;

Output:

  (1 row affected)
  Msg 208, Level 16, State 0, Line 63
  Invalid object name '#tmp_ts'.

My workaround is to not use a temp table, but I'm curious if anybody knows why this doesn't work.

Thanks!

This has never worked.

If you want to insert into a static temp table dynamically, you can do this:

CREATE TABLE #t(id INT);

DECLARE @sql NVARCHAR(MAX) = N'SELECT 1 FROM sys.databases;';

INSERT #t ( id )
EXEC sys.sp_executesql @sql;

SELECT * FROM #t AS t;

If you need to build a temp table dynamically, see this Q&A:

Creating temporary table dynamically using condition


How to find Oracle's installed version and release from a program? I need to determine from a program what version of Oracle is installed in each of the Oracle Homes on a server.  As there may not be any databases created in the Home yet, I need to be able to do this outside of the database (i.e., without connecting to the database).  Also, it would be highly preferable to be able to do this from a remote program.

This is from a windows program running .Net (C#, if that matters).

I am currently reading remote registry keys (using this technique: https://stackoverflow.com/questions/1566547/how-to-read-remote-registry-keys), to find all of the Oracle Homes according to this method.  This works fine, however, I have looked around those keys and do not see any information on the exact version/release.

The name of an Oracle Home itself is of course 1) not a reliable indicator and 2) does not have the exact version/release (for instance "10.2.0.4.0").  Basically I am looking for a way to figure out what the Oracle Universal Installer tells you in the Installed Products button.

I should clarify, all of the Servers will be running Windows 2003-2008.

If the database homes were installed properly, the central inventory has a list about them.
The central inventory on Windows is located at C:\Program Files\Oracle\Inventory. On Linux/UNIX platforms, the location of the central inventory can be found in /etc/oraInst.loc. In the inventory, in ContentsXML\inventory.xml, there is a list of installed homes in XML format, e.g:

<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.1.0.2.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraGI12Home1" LOC="/opt/oracle/grid12102" TYPE="O" IDX="1"/>
<HOME NAME="OraDB12Home1" LOC="/opt/oracle/base/product/db12102ee" TYPE="O" IDX="2"/>
<HOME NAME="OraDb11g_home1" LOC="/opt/oracle/base/product/db11204ee" TYPE="O" IDX="3"/>
</HOME_LIST>
</INVENTORY>

You can parse this list, and find detailed information with opatch for example:

/opt/oracle/grid12102/OPatch/opatch lsinventory -oh /opt/oracle/grid12102 -details
/opt/oracle/base/product/db12102ee/OPatch/opatch lsinventory -oh /opt/oracle/base/product/db12102ee -details
/opt/oracle/base/product/db11204ee/OPatch/opatch lsinventory -oh /opt/oracle/base/product/db11204ee -details

A Windows example would be:

C:\oracle\base\product\12.1.0\dbhome_1\OPatch\opatch lsinventory -oh C:\oracle\base\product\12.1.0\dbhome_1 -details

The above needs to be run on the server of course.

This is the best-case scenario, with proper installations. Worst-case scenario is, homes use separate inventories, or use no inventory at all, and you have to search all filesystems on the server for possible Oracle installations.


IMPDP Failing with ORA-00001: unique constraint in oracle When I try to import a dump which contains data from several tables, the import fails with ORA-00001: unique constraint as there are several entries which are matching in the destination database, but there are several other values in the same table which are not getting imported due to unique constraint error.  I need those values.

Can some one help me out in solving this . I cannot use truncate / replace.

I have used this command:

impdp system/oracle@LIQ727DO directory=DATA_PUMP_DIR dumpfile=EXPDP_ACM_TABLES_LIQ72.DMP
  logfile=IMPDP_ACM_TABLES_LIQ72.log remap_schema=LIQ728:LIQ727 TABLE_EXISTS_ACTION=append

impdp ... data_options=skip_constraint_errors

Above works only for non-deferred constraints though.

link


Disabling SKIP_CORRUPT after using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS Recently I was involved in recovering data after several blocks in production DB got corrupted.

Disclaimer: I'm by no means a competent DBA, but rather a PL/SQL developer with quite a bit (I guess) of experience, so maybe I'm missing something quite obvious here. Sorry for that.

DBMS_REPAIR package provides the procedure SKIP_CORRUPT_BLOCKS which is pretty much self-explanatory (allows accessing table despite it having corrupted blocks).

After it is used you can check DBA_TABLES.SKIP_CORRUPT column (which becomes ENABLED instead of being DISABLED by default).

Now that the dust has settled, all the data has been recovered and, hopefully, lessons has been learnt, I can't seem to find a way to switch it back without recreating the table (if some day another block in this segment gets corrupted, I'd rather have the queries fail than silently skip rows from corrupted blocks).

Is there a way to disable SKIP_CORRUPT without recreating the table?

You can disable it with the same procedure.

begin
  dbms_repair.skip_corrupt_blocks
  (
    schema_name => '...',
    object_name => '...',
    object_type => dbms_repair.table_object,
    flags=> dbms_repair.noskip_flag
  );
end;
/

dbms_repair.skip_flag enables, dbms_repair.noskip_flag disables this behaviour.


XQuery on Extended Events joining with dm_exec_query_plan not working I have this XQuery for my extended event session which works fine but when I try joining the plan_handle to dm_exec_query_plan to get the query plan, it says

  Msg 257, Level 16, State 3, Line 1 Implicit conversion from data type
  varchar to varbinary is not allowed. Use the CONVERT function to run
  this query.

If I convert to varbinary, this is the error I receive.

  "Msg 569, Level 16, State 5, Line 1 The handle that was passed to
  dm_exec_query_plan was invalid. "

If I manually pass the handle into the DMF, it works just fine. This is how it looks like in varchar format (after adding the 0x)

  0x06000700f9971028f01033ae0300000001000000000000000000000000000000000000000000000000000000

SELECT
        q.Time
        ,q.[CPU (ms)]
        ,q.[Duration (s)]
        ,q.[SQL Statement]
        ,q.plan_handle
FROM (
SELECT
        data.value(     '(/event[@name="sql_statement_completed"]/@timestamp)[1]', 'DATETIME') AS [Time]
        ,data.value(        '(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)]
        ,CONVERT(float, data.value('(/event/data[@name="duration"]/value)[1]', 'BIGINT')) / 1000000
        AS [Duration (s)]
        ,data.value(
        '(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement]
        --,data.value('(/event/action[@name=''plan_handle'']/value)[1]', 'varchar(4000)')       AS [PlanHandle]
        ,'0x' + CAST(data.value('(/event/action[@name="plan_handle"]/value)[1]', 'varchar(4000)') AS varchar(4000)) AS plan_handle
    FROM (
        SELECT
            CONVERT(xml, event_data) AS data
        FROM sys.fn_xe_file_target_read_file
        ('H:\SQLskills\EE_ExpensiveQueries*.xel', 'H:\SQLskills\EE_ExpensiveQueries*.xem', NULL, NULL)
    ) entries
    )q
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)

This is still a string, regardless of how it looks to you when you copy the output:

'0x' + CAST(...) AS varchar(4000)) AS plan_handle

Try using a style parameter on the CONVERT, so that the string isn't converted to a binary representation of the string, but rather converted to the string's literal binary value:

CONVERT(VARBINARY(32), '0x' + CAST(...) AS varchar(4000), 1) AS plan_handle


Imposing LIMIT on UNION We are working with some legacy code that is using a sub-optimally normalized DB.  The problem that we are trying to solve is best described with an illustrative example.
hi_priority and lo_priority are two tables that have the same schema, as defined below:
CREATE TABLE hi_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);

CREATE TABLE lo_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);

In each table there is a 1:1 mapping between the name and col2.
The idea is to potentially SELECT name, col1, col2 from both tables but limit the total number of distinct names (let's say 7 for our illustrative example), subject to the following constraints:

Preference is to be given to entries from hi_priority table over entries from lo_priority tables.
For example, if there are 15 distinct names in hi_priority table,
we will select all rows with 7 names from hi_priority table,
ordered by col2.
If there are 5 distinct names in hi_priority
table, we select all rows with those 5 names and on top of that we
select all rows with 2 distinct names from lo_priority table,
ordered by col2
If there are some entries in both the
hi_priority table and the lo_priority table, we select entries
only from the hi_priority table

Let us assume that the tables are populated as follows:
INSERT INTO hi_priority(name, col1, col2) VALUES('john', 'kl7y5tis4yh4', 1), ('john', 'tiid6iywq02k', 1),
('john', 'detawgsxz615', 1), ('amy', 'i6u4hhc1trjk', 2), ('amy', 'wdpt0t5vtmbt', 2),
('amy', '87z5wgdfztwl', 2), ('amy', 'oj98jjdpb5yv', 2), ('steve', 'hllaazonflf0', 3),
('steve', '0h22y66kq3ow', 3), ('steve', 'o04ksti7di60', 3), ('steve', '3qlylbzqfr51', 3),
('steve', 'wmisshywtb12', 3), ('bob', 'ku8wpf7d6ta9', 3), ('bob', '6t7mn60g0g18', 3),
('bob', 'qv0s7ho3jku6', 3), ('bob', '5qgz7dznzwvn', 3), ('bob', 'mm76nhimm6fu', 3),
('bob', 'k7nlpksc55t2', 3), ('oliver', 'gvf9kjewpj7h', 4), ('oliver', '24w5s30w5te6', 4);

INSERT INTO lo_priority(name, col1, col2) VALUES('doug', 'j205tzrsfmax', 11), ('doug', '0w7rcazbh6es', 11),
('doug', '6xswf8frsjaw', 11), ('john', 'iw7d14vtysz2', 2), ('john', '6lg667dygaz1', 2),
('john', '83uk5dcobpu5', 2), ('john', 'tl8cpzatv0n9', 2), ('mike', '2dsarwozpmci', 3),
('mike', 'hinn6w03wdib', 3), ('mike', '4sxbgyacmjob', 3), ('mike', 'm4q13ln9gctj', 3),
('mike', 'pnip9c8cejo9', 3), ('steve', 'faff9p9v96x4', 4), ('steve', 'd5mxqpd3k8zi', 4),
('martha', 'bxggn5t6d2xn', 8), ('martha', 't05mi47i4n6l', 8), ('martha', 'p30wmw2o6nty', 8),
('martha', 'wip6efajt9yv', 8);

The expected output is:
+--------+------------+------+
| name   | col1       | col2 |
+--------+------------+------+
| john   | kl7y5tis4y |    1 |
| john   | tiid6iywq0 |    1 |
| john   | detawgsxz6 |    1 |
| amy    | oj98jjdpb5 |    2 |
| amy    | 87z5wgdfzt |    2 |
| amy    | wdpt0t5vtm |    2 |
| amy    | i6u4hhc1tr |    2 |
| bob    | k7nlpksc55 |    3 |
| bob    | mm76nhimm6 |    3 |
| bob    | 5qgz7dznzw |    3 |
| bob    | qv0s7ho3jk |    3 |
| bob    | 6t7mn60g0g |    3 |
| bob    | ku8wpf7d6t |    3 |
| steve  | wmisshywtb |    3 |
| steve  | 3qlylbzqfr |    3 |
| steve  | o04ksti7di |    3 |
| steve  | 0h22y66kq3 |    3 |
| steve  | hllaazonfl |    3 |
| mike   | 4sxbgyacmj |    3 |
| mike   | pnip9c8cej |    3 |
| mike   | m4q13ln9gc |    3 |
| mike   | hinn6w03wd |    3 |
| mike   | 2dsarwozpm |    3 |
| oliver | gvf9kjewpj |    4 |
| oliver | 24w5s30w5t |    4 |
| martha | bxggn5t6d2 |    8 |
| martha | t05mi47i4n |    8 |
| martha | p30wmw2o6n |    8 |
| martha | wip6efajt9 |    8 |
+--------+------------+------+

Amy, Bob and Oliver are only present in hi_priority table and will be selected.
Mike and Martha are only present in lo_priority table and will be selected.
John and Steve are present in both hi_priority table and lo_priority table but will be selected only from hi_priority table

WITH
cte_hi AS ( SELECT DISTINCT name, hi_priority.col2, 1 priority
            FROM hi_priority
            ORDER BY col2, name LIMIT 7 ),
cte_lo AS ( SELECT DISTINCT lo_priority.name, lo_priority.col2, 2 priority
            FROM lo_priority
            LEFT JOIN cte_hi USING (name)
            WHERE cte_hi.name IS NULL
            ORDER BY col2, name LIMIT 7 ),
cte_total AS ( SELECT * FROM cte_hi
               UNION ALL
               SELECT * FROM cte_lo
               ORDER BY priority, col2 LIMIT 7 )
SELECT hi_priority.*
FROM cte_total
JOIN hi_priority USING (name)
WHERE priority = 1
UNION ALL
SELECT lo_priority.*
FROM cte_total
JOIN lo_priority USING (name)
WHERE priority = 2
ORDER BY col2, name, id

fiddle


Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object? According to MS-DOCS about System information schema views, the schema columns definition have a warning note that says:

  ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a
  object is to query the sys.objects catalog view.

Why can't INFORMATION_SCHEMA views be used to determine the schema of an object?

Is this information erroneous?

Yes, the statement there is inaccurate. I talk about this in this blog post (and complained about it in this ancient Connect item), where I implore people to use the sys catalog views over INFORMATION_SCHEMA:

  The warning really should state that the INFORMATION_SCHEMA views are incomplete; the wording above implies that they might be incorrect, which in itself is incorrect.

In 2011, they said:

  Hi Aaron,
  That particular wording has been in the topic since 2005. But, I see your point. We'll revist the wording and try to make it more clear.

Now that we can submit pull requests for suggested changes to the documentation, it might be time for someone to make them pay attention to it again. 13 years is long enough.


Improve the poor performance of the sys.dm_db_stats_properties DMV We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL column statistics on the partitioned tables.

When running sys.dm_db_stats_properties on these databases, the performance of this table function is extremely poor. We are looking at approximately 1 second per ROW - i.e per 'run' of the this table function.

Here is an example of the query plan generated by a simple query with the CROSS APPLYsyntax used to execute this table function against 1605 stats-table combinations.

There is nothing very helpful here - the performance is clearly poor from the DMV.

My current theory is that due to the nature of the statistics objects in the database, the query against the OPENROWSET internal table is poorly optimized (possibly the TOP 1, and this is what is causing the slowdown.

CREATE FUNCTION sys.dm_db_stats_properties (@object_id int, @stats_id int)
RETURNS TABLE
AS
RETURN SELECT TOP 1 -- The first row in the TVF will be the root; avoid scanning entire TVF to find any additional rows.
    object_id, -- Columns now explicit since underlying tvf has additional columns we don't want to expose for backwards compat
    stats_id,
    last_updated,
    rows,
    rows_sampled,
    steps,
    unfiltered_rows,
    modification_counter,
    persisted_sample_percent
FROM OPENROWSET(TABLE DM_DB_STATS_PROPERTIES, @object_id, @stats_id)

However, given that sys.dm_db_stats_properties is a DMV and therefore immutable, we can't change the way it queries the internal tables or anything like that, as far as I know.

The objective here is to obtain the values for the columns rows, rows_sampled, unfiltered_rows, modification_counter, last_updated as obtained from sys.dm_db_stats_properties in a way that doesn't take 3 hours per database! It does not matter if we use a different DMV, so long as the source of the information is not less accurate.

We have tried reorganizing all the system tables to see if that has any effect with statements such as ALTER INDEX ALL ON [Database].sys.[table to reorganize] REORGANIZE. However, no performance increase was observed.

The we are running the following version of SQL server:
Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) on Windows Server 2016.The compatibility level is 140

You can get everything except modification_counter from DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
WITH STAT_HEADER;

You'd need to capture its output into a temporary table using something like:

CREATE TABLE #StatHeader
(
    [Name] sysname NULL,
    Updated datetime NULL,
    [Rows] bigint NULL,
    [Rows Sampled] bigint NULL,
    Steps integer NULL,
    Density float NULL,
    [Average key length] integer NULL,
    [String Index] varchar(3) NULL,
    [Filter Expression] nvarchar(max) NULL,
    [Unfiltered Rows] bigint NULL,
    [Persisted Sample Percent] integer NULL
);

INSERT #StatHeader
EXECUTE('DBCC SHOW_STATISTICS (table, stat) WITH STAT_HEADER;');

A substitute modification counter is provided by rowmodctr on sys.sysindexes.

This is a workaround, and what we would have done before the new DMV was provided. It's not ideal, and you should report the unexpectedly slow performance you see to Microsoft.


Is MySql's LAST_INSERT_ID() function guaranteed to be correct? When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row.

As many Microsoft SQL Server devs and admins no doubt are aware the equivalent functionality in SQL Server (SCOPE_IDENTITY and @@IDENTITY) hasn't been without its problems.

I know the MySQL docs state:

  The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENTvalue generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

(source)

and even go so far as to say:

  Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid.

(source)

Are there any known risks or scenarios that may cause LAST_INSERT_ID() not to return the correct value?

I'm using MySQL 5.5 on CentOS 5.5 x64 and Fedora 16 x64 and the InnoDB engine.

A couple caveats I'd like to point out when using LAST_INSERT_ID:

I know you mentioned single-row inserts. But when doing multiple-row inserts, LAST_INSERT_ID() will return the value of the first row inserted (not the last).
If the insert failed, LAST_INSERT_ID() would be undefined. The same is true for automatic rollbacks of transactions (due to errors).
If you do an insert in a transaction that succeeds, and you still issue a ROLLBACK, LAST_INSERT_ID() would be left as it was prior to the rollback.
There are a couple caveats when using AUTO_INCREMENT and LAST_INSERT_ID in statement-based replication. The first being when used in a trigger or function. The second being the less-common scenario where your auto_increment column is part of a composite primary key and is not the first column in the key.


What happens when we call pg_dump while users are still using the system? We have a web application with PostgreSQL as our database and use celery background tasks for somewhat complex task. At the very beginning of this complex task we take a backup of the database using pg_dump.

I was wondering about the state of this sql dump if the users are still using the system. Could it cause any inconsistencies ?

Also, I would like to know if pg_dump is the best choice for taking backups in this case. Of course as the database grows the backup takes a lot of time which slows the overall process.

Thanks.

I was wondering about the state of this sql dump if the users are still using the system

pg_dump relies on PostgreSQL's MVCC support to ask PostgreSQL to take a snapshot of the database state at a given point in time.

Changes made by concurrent transactions after the point where pg_dump gets its snapshot do not affect pg_dump, whether they're committed or not.

There are a few things that aren't necessarily consistent, like sequences, which continue to increase while pg_dump is running. But applications have to be prepared for gaps in sequences anyway, so this isn't considered to be a problem.

  Also, I would like to know if pg_dump is the best choice for taking backups in this case

No, you're way better off using WAL archiving and PITR for your purpose. Look into PgBarman and read the docs on continuous archiving.

You should still take logical dumps for disaster recovery, but it need not be an all-the-time thing.


Table not shown up on Linked Server I have a machine which have 2 SQLServer instances

WINXP-PRO\SQLSERVER2000 (SQL Server 2000)
WINXP-PRO\SQLEXPRESS (SQL Server 2005 Express Edition).

In \SQLEXPRESS, I have database wavinet2 and wavinet2_mapic. In \SQLSERVER2000, I create linked server to [IP_ADDRESS]\SQLEXPRESS, the linked server is created.

All tables have shown up except for database wavinet_mapic.

I provide the remote login using sa. I try to select and it cannot found the table.

Any hint what is causing this problem?

My suspicion is that the table in question uses a feature that is only available in SQL Server 2005 and above and so that is why you cannot see it in your 2000 instance. This is similar to what happens when you try to open a SQL 2005 or above instance from Enterprise Manager - except you get a more descriptive error. If you desperately need to see this table in SQL 2000 remove the offending feature (if at all possible) from the table (it might just be a data type), if you cannot remove the feature then have a think about whether it is possible to have the link the opposite way around?

I hope this helps you.


Incomplete mysqldump I'm trying to run mysqldump to create a database snapshot, and I'm finding it will randomly stop midway, without reporting any error. My database is relatively small (about 100MB) and is using InnoDB.

I'm running it like:

mysqldump --force --single-transaction --quick --user myuser --password=mypass -h mydatabasehost mydb > /tmp/snapshot.sql

Checking the exit code reports 0.

My version is: mysqldump  Ver 10.13 Distrib 5.1.52, for redhat-linux-gnu (i386)

I've seen some similar posts and even an official bug report, but neither solutions seem to apply.

How to I get mysqldump to take a complete database snapshot?

EDIT: My database currently resides on Amazon's RDS.

It may have been a problem with max_allowed_packet not being set high enough on both the client (i.e. mysqldump) and the server (i.e. Amazon RDS). I set this to 500M on both and that seems to have fixed the problem.

Since InnoDB's information schema tables only give row count estimates, it's hard to tell if my snapshot truly includes everything from RDS. All the tables are there, but the row counts differ. I'll update with a more definitive answer when I have some time to script a more thorough analysis.


Update only the oldest entry in a table with foreign key criteria I'm working on a forum website.

What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts".

"Threads"

"Posts"

I'm not really sure how to update only the oldest entry in "Posts" where the foreign key in "Posts" match the Id in Threads.

I have never really tried advanced UPDATE statements with subqueries, and it isn't one of my strongest skills yet.

This is what i have tried (which updated ALL rows in "Posts"-table):

    UPDATE Threads
    SET Title = @ThreadTitle
    WHERE Id = @Id;

    UPDATE Posts
    SET Content = @Content
    WHERE
        (SELECT TOP 1 FkThreadId FROM Posts
         WHERE FkThreadId = @Id
         ORDER BY CreationTime ASC)= @Id

Also is it possible to update joined table instead of using 2 statements, or is it just better to write 2 seperate statements?

Version:

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

Yes, your query will update all rows in posts. You have to include a condition that restricts the update to one row. One way to do this:

UPDATE Posts
SET Content = @Content
WHERE Id =
    (SELECT TOP 1 Id
     FROM Posts
     WHERE FkThreadId = @Id
     ORDER BY CreationTime ASC) ;

In SQL-Server, you can't update two tables in one statement, so you have to use two statements. You can put them inside a transaction though, so they are treated as an atom, they either both succeed or fail.


How do I get the current unix timestamp from PostgreSQL? Unix timestamp is the number of seconds since midnight UTC January 1, 1970.

How do I get the correct unix timestamp from PostgreSQL?

When comparing to currenttimestamp.com and timestamp.1e5b.de I don't get the expected time from PostgreSQL:

This returns the correct timestamp:

SELECT extract(epoch from now());

While this doesn't:

SELECT extract(epoch from now() at time zone 'utc');

I live in time zone UTC +02. What is the correct way to get the current unix timestamp from PostgreSQL?

This returns the correct time and time zone:

SELECT now();
              now
-------------------------------
 2011-05-18 10:34:10.820464+02

Another comparison:

select now(),
extract(epoch from now()),
extract(epoch from now() at time zone 'utc');
              now              |    date_part     |    date_part
-------------------------------+------------------+------------------
 2011-05-18 10:38:16.439332+02 | 1305707896.43933 | 1305700696.43933
(1 row)

Unix timestamp from the web sites:
1305707967

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

DBFiddle

This is because:

  PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication


Would there be a reason not to make primary keys unsigned? I am going through a database creation script created by people with more experience than me, and I noticed that the primary keys were set to  signed.

So like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

instead of like this,

CREATE TABLE `exampleTable`
(
`pk` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
`content` TEXT
) ENGINE = INNODB
ROW_FORMAT = COMPACT;

It seems to me that by setting the primary key to be unsigned one has doubled the possible number of values, am I missing something where it would actually be better to use a signed int for the primary key?

Reasons you may be given include:

Suspicion that the unsigned type may not be portable, even among other non-portable code and even if no one's contemplating porting
Fear that since it deviates from the default it has the potential to distract, while not being strictly necessary, leading potentially to programmers' waste of time or errors
Fear someone may omit unsigned when copying it to other structures (ETL) and not realize the error until eventually the numbers do get over the other half of the range; that may take years and blow out much later
Unexpressed, vague fears that something might break, down the line, whether when not handled properly (in one's own codebase), or even if handled properly, if all the rules that make it work aren't clear (in the extreme, this may include fear of an error in the database engine itself)
Challenge from seniors, managers, or just people who like to challenge, who are in a position to stop things from happening by challenging them. This group might adduce it's not in the coding conventions to use unsigned, and insist you produce a very powerful reason if you wish to add it to the coding conventions. It won't be easy to prove your reason is strong when you're the only one against others who say otherwise and surrounded by a third group who just don't care.

Or, if you advocate to use the negative half of signed numbers:

Dislike of negative numbers / confusion
Adduce formatting problems. 'What if these needed to be printed? Would they be printed properly?'
Pose a valid question: What happens as the ID assign process has exhausted the negatives and hits 0? Will ID=0 be correctly handled by the application and downstream processes like any other ID's, or will they think it's special?

If you promote things that aren't the norm in a group without building a sound reputation first, you risk people attacking your initiatives and your good reputation will be harder to build and you might even get a bad reputation, even if you have done nothing wrong, or said or caused no difficulty, or been a factor or co-factor of anything gone wrong.

If you contest this, the powers that be will attack you also, and keep you in check, they'll get the 'your attitude' word out and you'll be down in no time ('opinion based' is such another key word they may use when referring to you or your contributions).

The problem isn't negative numbers or unsigned, it's group-thinking, and enforcement of 'normal' by people who set the rules, whoever they are and whether they truly know or not. Of this the database world is full, just as any other technical field where lots of males compete for preeminence and power.

You see, if this was merely a technical problem, it'd be easily solved. I'm not saying easily shared, but at least easily solved. It'd just require your skill, attention, carefulness, thoroughness.

Unfortunately as things depart from the average and even if they are 'better' in a sense, they become a human 'problem'. Now to solve it you need acceptance, patience, negotiation, persuasion, and a tad lots more. Things that are even harder to fix than technical problems.

Departures from the norm can be very good at times, but at times they can shake the ground and test the individual differences and assumptions and presumptions and the group's cohesion and their beliefs and culture and become factors on where the group is going, and its members. The dynamics between the technical and the human are complex, often we won't be able to say what's preferable, short term, long term; we won't even understand it all or have the same opinion.

A little technical issue, recipe, option or preference can be just nothing, or it can become the tip of the iceberg (for a majestic sail, or a ship wreck), or like stroking a genie lamp. We're all a box of surprises and you don't know what's behind the door until you press that button.


MySQL large number of opened tables -- we're noticing performance hits We have 3 database servers, containing 200, 200 and 146 databases respectively. Each database contains about 250 tables. These definitions are the same for 99% of the databases - dependent on the version of software each customer is running on. We've been running this since over a week, and performance was great. It's dropping now, and we're not sure where to look.

Output from the first SQL:

> SHOW GLOBAL STATUS LIKE 'open_%'
>   -> +--------------------------+----------+
    -> | Variable_name            | Value    |
    -> +--------------------------+----------+
    -> | Open_files               | 1        |
    -> | Open_streams             | 0        |
    -> | Open_table_definitions   | 633      |
    -> | Open_tables              | 400      |
    -> | Opened_files             | 5078508  |
    -> | Opened_table_definitions | 1905982  |
    -> | Opened_tables            | 8910560  |
    -> +--------------------------+----------+

show variables output: https://gist.github.com/Ma-ve/453c80de33cb55064bd1dcb142fa83c4

Server has 16 GB of RAM, 8 vCPU. Any other specs can be requested.

edit: Full output from SHOW STATUS; as of a few minutes ago: https://gist.github.com/Ma-ve/ecc8ac4e67965d5da4f5de0a58436263#file-status-log-L3

Full output from SHOW VARIABLES; as of a few minutes ago: https://gist.github.com/Ma-ve/d0ba67695ef6d5b1a2020648250ded89

Server details:

16 GB RAM, a DigitalOcean Droplet (regular droplet)
120 GB disk
Output du -sh /var/lib/mysql: 19G
Output uptime: 11:26:46 up 270 days, 23:37,  3 users,  load average: 1.20, 1.39, 1.49
vm.swappiness = 0

Bit of history behind the query cache: we had it disabled when we had only 15 clients on a server, and performance was godawful. Enabling it (as it was enabled on our old server) instantly solved a lot of performance issues.

Opened_tables (etc) is useless without dividing by Uptime.  I recommend no more than 2/sec.  Perhaps 1/sec for Opened_table_definitions/Uptime.  Those are under the control of these:

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1500  |
| table_open_cache           | 1555  |
| table_open_cache_instances | 4     |
+----------------------------+-------+

And rethink the wisdom of having so many databases and so many tables.

open_files_limit and the OS limit are also involved.

Table_open_cache_hits and Table_open_cache_misses and Table_open_cache_overflows are useful to watch.  Up to 1 miss or overflow/sec might be a good goal.

When increasing cache(s), be sure not to use up so much RAM that swapping occurs.  If swapping does occur, the quick fix is to decrease innodb_buffer_pool_size a little.

If you would like more analysis, please provide the info requested here .

Tuning Analysis

Observations:

Version: 5.7.20-0ubuntu0.16.04.1-log
16 GB of RAM
Uptime = 21d 01:09:23
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Really bad:  query_cache_size should not be more than about 50M.  2GB leads to a lot of overhead in purging entries.  Consider turning off the QC.

Since MyISAM is not used much, decrease key_buffer_size to 50M.

open_file_limit is only 1024.  This is probably because Ubuntu is restricting
it to 1024 -- see ulimit, and raise it.  After that, increase table_open_cache to
at least 5K, and a few others.

innodb_buffer_pool_size (10G) is probably at a good size.  I deduce that you have less than tha much data.

innodb_log_file_size is quite high for the amount of activity you have.
But it is not important to change it.

Why is SHOW CREATE TABLE (and similar things) being run so many times?

Why so many ALTER TABLEs?

It seems that a lot of SELECTs are inefficient.  See this for what to do next.

LOCK TABLES and UNLOCK TABLES should rarely, if ever, be used with InnoDB.  What is going on?

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (512M - 1.2 * 32 * 1024) / 16384M = 3.1% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 32 * 1024 / 512M = 0.01% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( Opened_tables ) = 10,163,367 / 1818563 = 5.6 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 2,479,464 / 1818563 = 1.4 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 10,109,150 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 10,163,360 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 132,895 / 655280 = 20.3% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size to bigger than necessary?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 10,751,949,312 / (1818563 / 3600) / 2 / 256M = 0.0396 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,818,563 / 60 * 256M / 10751949312 = 756 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( (Com_show_create_table + Com_show_fields) / Questions ) = (1137006 + 1592424) / 249138897 = 1.1% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( query_cache_size ) = 2048M -- Size of QC
-- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (2048M - 1143770840) / 560588 / 8192 = 0.219 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Created_tmp_disk_tables ) = 2,468,770 / 1818563 = 1.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 2,468,770 / (2468770 + 4016418) = 38.1% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Com_rollback / Com_commit ) = 2,444 / 8577 = 28.5% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 5,519,376 / 1818563 = 3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 5,519,376 / 21974063 = 25.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_alter_table ) = 85,985 / 1818563 = 0.047 /sec
-- Why so many ALTERs?

( back_log / max_connections ) = 92 / 214 = 43.0%

( Connections ) = 10,172,085 / 1818563 = 5.6 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 100 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- 0 is good for Windows
0 is inefficient for non-Windows; 10 is probably fine

( thread_cache_size / max_connections ) = 100 / 214 = 46.7%

Abnormally large:

Com_create_db = 0.35 /HR
Com_create_table = 85 /HR
Com_create_trigger = 19 /HR
Com_create_user = 0.34 /HR
Com_drop_table = 85 /HR
Com_drop_user = 0.014 /HR
Com_grant = 0.34 /HR
Com_release_savepoint = 3.6 /HR
Com_rollback_to_savepoint = 0.25 /sec
Com_savepoint = 3.6 /HR
Com_show_create_db = 3.6 /HR
Com_show_create_func = 0.96 /HR
Com_show_create_table = 0.63 /sec
Com_show_create_trigger = 0.054 /sec
Com_show_events = 3.6 /HR
Com_show_fields = 0.88 /sec
Com_show_function_status = 3.6 /HR
Com_show_privileges = 0.002 /HR
Com_show_procedure_status = 3.6 /HR
Com_show_table_status = 0.25 /sec
Com_show_triggers = 0.25 /sec
Com_unlock_tables = 89 /HR
Handler_savepoint = 3.6 /HR
Handler_savepoint_rollback = 0.25 /sec
Performance_schema_digest_lost = 2.56e+7
Qcache_free_blocks = 342,300
Qcache_free_memory = 1.14e+9
Qcache_total_blocks = 1.49e+6
Select_full_range_join = 55,471
Threads_cached = 93
eq_range_index_dive_limit = 200
group_concat_max_len = 1.0e+6
innodb_purge_threads = 4
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210
performance_schema_max_statement_classes = 193
query_cache_limit = 1.05e+8

Abnormal string values:

innodb_force_load_corrupted = OFF
innodb_undo_directory = ./
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN


I get the error "You are attempting to execute a query in which the selected expression is no part of a statistical function." in MS Access 2010? Please note: my copy of Access is in Dutch, so any English parts are translated from Dutch.

I'm currently working on a database in Access using SQL, with the exercise being:

Create a query that will list all students who have more than €25 of penalty.

I created this query:

SELECT L.LLNR, U.LLNR, L.VOORNAAM, L.TUSSENVOEGSEL, L.ACHTERNAAM, SUM(U.BOETE)

FROM
    LEERLINGEN L
    INNER JOIN UITLENINGEN U ON L.LLNR = U.LLNR

WHERE (((U.BOETE)>25));

When I click Execute, I get the following error (translated from Dutch)

  You are attempting to execute a query in which the selected expression LLNR is no part of a statistical function.

What should I do?

Translations: |
Leerlingen = students |
Boete = penalty |

When you have an aggregate function, you need a GROUP BY statement.  In your case, it would be

SELECT
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    SUM(UITLENINGEN.BOETE)
FROM
    LEERLINGEN
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement.  In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns.  That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause.  Then it would look like this:

SELECT
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    SUM(UITLENINGEN.BOETE)
FROM
    LEERLINGEN
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    UITLENINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM
HAVING
    SUM(UITLENINGEN.BOETE)>25;

Think of HAVING as a WHERE for your groups.  You filter rows using WHERE, and you filter groups using HAVING.


SQL Availability Group and SSRS, SSIS and SSAS I am looking at a new SQL 2016 installation that will consist on two nodes (located in the same data center):

Node 1: Production DBs

Node 2: Production DBs (replica)

I am thinking where to install my SSIS / SSRS / SSAS.  If they don't have high availability requirement, then does it make sense to install them on node 2?  The only draw back I see is the potential network congestion.

So end result is

Node 1: DB Engine, Production DBs

Node 2: DB Engine, Production DBs (replica), SSIS, SSAS, SSRS, all supporting DB (not HA)

I am thinking where to install my SSIS / SSRS / SSAS.

If it were me, and I was the one having to administrate the servers, I'd load these each on their own individual server with their own individual resources. Virtual servers are great for this, and if you aren't sure about the load then it is very easy to adjust resources.

When I see servers that have everything loaded on them and all running - I know there's going to be a problem, it's a matter of when and not if.

  If they don't have high availability requirement, then does it make sense to install them on node 2?

If you're into that sort of thing, then sure by all means. If you're limited by some other reason, then I would fight for more of whatever resource you are low on... disk, servers, memory, licensing, etc., as 15k for a new server is generally less money than 1,000 work hours wasted by everyone troubleshooting issues.

  The only draw back I see is the potential network congestion.

Here are a few more for the list:

None of the services know about each other
This can severely impact your FCI RTO
"Phantom" performance issues
Non-Scalable setup
Overly complicated troubleshooting (as a result of #3)

Don't get me wrong, in a pinch this can absolutely be done and sometimes when the servers are beefy enough and the load is small enough and not so important that a few hiccups here and there will be noticed then it can work.

For example, a runaway/poorly written/large data SSIS package is running but it's slow - what do you troubleshoot? Is there also a large parallel query taking up CPU in SQL Server, maybe the SSRS report is running a large report and rendering, at the same time SSAS is rebuilding a cube. How do you show that one is impacting the other?

With all of the services running on the same server, it's extremely hard to pinpoint individual items... it can be done, with the proper amount of monitoring and troubleshooting. However, as stated before, I'd rather have each isolated so that when this inevitably occurs, I can get to the root of the issue much faster and with greater accuracy.

  My understanding the active node will be the one taking most of the load so that's why I think to install the other stuff on the passive

I wouldn't continue thinking this way as you're setting yourself up for more issues. Instead, think of AGs and FCIs as "running anywhere, I don't really care" and your designs will be much more robust with less overall issues. Assuming that a single server will always hold a service on it in a WSFC is a bad assumption. Assume it will be on any of the possible owners at any given point in time.


Is the double dollar sign ($$) quotation method in SQL standard? In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,

SELECT $f$ Hi $f$;

Is this quoting mechanism a part of any SQL standard, or is it postgres specific?

NO  its postgresql specific many programing languages offer a way to write text that avoids having to be escaped

consider

Select $$";"'\e\r\n$$

vs

select '\";\"\'\\e\\r\\n'

Also keep in mind Postgtesql from version 9.1 by default no longer allows passing escape string in a query,  it has to have a leading E

select E'\r\n'

to be escaped now
see standard conforming string


Learning Oracle, does it matter if I install 10g or 11g? I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g.

Is 11g backwards compatible?

As in, just for learning, will I be able to not use the added 11g features?

Are you really trying to use Windows 8?  No version of Oracle is supported on Windows 8 yet and, if history is any guide, I wouldn't expect any of them to work without patches that aren't available yet.  Is Windows 8 even in public beta yet?  I'd hate to try to take a class using an early beta operating system.

Oracle 11.2 is supported on Windows 7 (which seems like a more realistic Windows version).  If you have Windows 7, that's realistically the version you need to install.

What, exactly, does the class cover?  If you are learning how to do data modeling and to write basic SQL and PL/SQL, you should be fine using 11g since everything will be backwards compatible.  If, on the other hand, your instructor is teaching about database administration or is making heavy use of the Oracle Enterprise Manager (OEM) GUI, there are going to be a lot of visual differences that you'd have to compensate for across versions.  You can almost certainly get the same information from the 11g OEM GUI, it just may be in a slightly different place.


Dynamically change column names in query in PostgreSQL There is a table with a bunch of columns. I need to capture the column name and dynamically generate queries based on a given column.
Table definition:
id int,
at_11 charcater varying(50),
at_12 charcater varying(50),
at_13 charcater varying(50),
at_14 charcater varying(50)

And the sample rows in the table are:
1,0,0,0,0
2,0,'Low',0,0
3,'High',0,0,0

CREATE OR REPLACE FUNCTION return_at_query(p_column character varying)
 RETURNS TABLE(at_id character varying, v_id numeric)
 LANGUAGE plpgsql
AS $function$
declare
    v_cnt int;
begin
       RETURN QUERY EXECUTE
       format('SELECT $1, id
               FROM tab1 WHERE $1 <> 0'
            )
   USING p_column;
end;$function$;

When I call the function by passing in a column name value 'at_11', I expect to see the results as 'High',3. But I am getting the whole list of rows instead. How can I accomplish this?

I've changed v_id from numeric to int, because in your sample data id is integer.
Them IMHO you don't need to use USING, you can simply format your sentence and execute it.
Finally you should escape single quotes around last 0.
CREATE OR REPLACE FUNCTION return_at_query(p_column text)
 RETURNS TABLE(at_id character varying, v_id int)
 LANGUAGE plpgsql
AS $function$
declare
    ret text;
begin
       RETURN QUERY EXECUTE
       FORMAT('SELECT %s, id FROM tab1 WHERE %s <> ''0''', p_column, p_column);
end;
$function$;

select return_at_query('at_11')

| return_at_query |
| :-------------- |
| (High,3)        |

db<>fiddle here


Disabling logging on certain tables I'm using SQL Server 2005.  I have two tables that contain aggregate information.  The information is constantly being updated, generating almost 5GB of log data a day.  (That's larger than the entire database!)  I'd like to disable logging on these tables, as rolling back is not really necessary.  I would however like to keep logging on the other tables in the database.

Is it possible to disable logging on certain tables within the database?  If not, can I place the two tables in the same schema, then disable logging on the schema?  Is the only option to move the two tables to a separate database, and disable logging there?

Update:  I guess I'll explain why I really don't need to log the activity on these tables.

The two tables are filled with GPS data, so they get quite large.  The first table is capturing raw locations from six Android tables in the field.  New data from each of the tablets comes in every 5-10 seconds.  That information is then aggregated as locationA, locationB, travelTime.  The goal is to ultimately have the shortest travel times between all locations, based on actual driving data.  The data is only for a small city, and only accurate to four decimal places, so it is manageable.  However, as new raw data comes in, there are slower travel times that need to be updated, and new ones that need to be inserted.

Once the raw data is aggregated, it is purged.  We're not going backwards to longer travel times, so that is why rolling back does not matter so much in these tables.

Is it possible to disable logging on certain tables within the database?
  Can I place the two tables in the same schema, then disable logging on the schema?
  Is the only option to move the two tables to a separate database, and disable logging there?

Logging of user operations cannot be disabled.

There is a class of operations called minimally-logged operations that only allows a transaction to be rolled back (as opposed to also being able to roll forward). However, it doesn't sound like what you're doing would meet the criteria for that to apply, nor do I think this would solve the problem anyway.

The SQL Server solution would be to land the staging tables in a new database in SIMPLE recovery, separate from the aggregation results database, which I assume is in FULL recovery. While this wouldn't decrease the volume of logging, it would eliminate the need to back up the log generated by the data loads. (See my blog post here about logging, and some of the factors involved in choosing a database's recovery model.)

Separating the tables into another database would provide the flexibility to land the log file on a faster I/O subsystem, probably a small array of locally-attached solid-state drives.

Also, consider that SQL Server alone may not be the best solution to solve the problem. There are other RDBMS solutions that offer the ability to completely disable logging for certain tables. The data could be staged and aggregated in another system, and the results merged into the existing SQL Server database, which is well-protected by full logging and backups.


How to get SSIS to stop waiting/processing at task above time limit? So I have a couple SSIS packages that are run sequentially, scheduled through SQL Server Agent. Right now, I'm getting some errors where certain input data steps just wait indefinitely-- i.e access database locked exclusively by another user or other issues. I'm still in the process of figuring out why it will just wait at some of these steps.
However, is there any way for me to check how long I've been at a certain step, and error if it's been too long? I.e no individual step should take longer than 15 minutes as the absolute max, so I would like to have something like:
If step_processing_time > 15 minutes:
    Send error email message, fail package

Is this possible to do in SSIS? I tried googling but couldn't really find anything from what I searched.

You can set timeouts on tasks; I don't know if you can on steps.


How can you stop MySQL slave from replicating changes to the 'mysql' database? I have my slave set to not replicate the 'mysql' database as described in this SHOW SLAVE STATUS\G;

           Slave_IO_State: Waiting for master to send event
              Master_Host: 127.0.0.1
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 1660
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 478
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      **Replicate_Ignore_DB: mysql**
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1660
          Relay_Log_Space: 633
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0

Now, if I go to the MASTER server and issue a GRANT and FLUSH PRIVILEGES:

GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
FLUSH PRIVILEGES;

I then go back to the SLAVE server and issue:

SHOW GRANTS FOR `foo`@`localhost`;

and receive the response:

+-------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How can I stop the slave from replicating changes the mysql database? I figured 'replicate_ignore_db' would have sufficed.

Alright, after a few more hours of investigation, I think I figured it out. Adding my answer in case this is of use to others.

According to the docs on replicate-ignore-db:

  Statement-based replication.  Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

Of course, statement-based replication is the default and what I was using. So I made the attempt to change the format by restarting the master with binlog_format=row to see what would happen. No dice. GRANTS and REVOKES still were replicated.

Further investigation into the docs on replication changes on the mysql table revealed

  Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms).

Gah! Ok, so I checked the binlog using mysqlbinlog and my GRANT statement was not issueing a USE mysql database call (why should it?). So replicate-ignore-db could not in good conscience ignore the statement.

My solution was to cut the changes to the mysql table out of the binary log completely by adding binlog-ignore-db=mysql to my.cnf and restart the server. Worked like a charm.


Getting the "Column name or number of supplied values does not match table definition" when I use CTEs in an IF statement inside a stored procedure I have a Stored Procedure that has 3 parameters. The stored procedure looks like this:
ALTER PROC [dbo].[Trans_sp_test] (@Load bit = '',@ShowMsg bit = '', @File bit = '')
AS

SET NOCOUNT ON
DECLARE @transmsg varchar (80)

IF @Load = 1
BEGIN

;with cte1 as (

 select top 100 percent
    field1,
    field2,
    field3
 from dbo.table1

 order by field1
), cte2 as (
  select top 100 percent
     field4,
     field5
  from dbo.table2

  order by field4
)

select distinct
 _cte1.*,
 _cte2.*
from cte1 _cte1
inner join cte2 _cte2 on _cte2.field4 = _cte1.field1

END

The stored procedure compiles just fine: however, when I try to execute it using the parameter @Load= 1, for example, (exec Trans_sp_test @Load = 1), I get an error:
"Column name or number of supplied values does not match table definition".
I have verified the columns multiple times and ensured that I have the correct number of columns, but I am still getting the error. At this point, I'm not sure what am I missing, or if there's an error in my code. I appreciate all your help.

This error message is generally associated with an insert, which you don't have in your sample code.  Assuming this is representative of your proc, I would guess you are using an INSERT INTO ... EXEC with the proc.  Since that take the first results set, it would mean that this supplants another result set that is returned when @Load = 0.
Under that assumption, since one works, and the other does not, it would also mean those have different columns.  If it was your intent to have this result set replace the other, then the solution is to make the columns of the conditional query match those of the second.
Otherwise, your code is not representative of the problem you are encountering.


Why is the default width for a MySQL signed MEDIUMINT 9 instead of 8? If I create a signed MEDIUMINT column without a width specifier, MySQL automatically adds a width of 9. However, the range of a signed MEDIUMINT is -8388608 to 8388607, which only needs 8 display characters max including the sign.

I tested all the other *INT types without a width and the default width MySQL assigned matched the worst case maximum number of characters needed to output the datatype. Why does a signed MEDIUMINT default to +1 character?

https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Hmmm... Sounds like a bug.  But since that number is not used for anything (assuming you don't have ZEROFILL), then it is no big deal.

You could, if you like, file a bug report at bugs.mysql.com .


Process to map a transaction table to a status table I have a table, TRANSACTIONS, in SQL Server 2012 with ~100000 rows that has the following columns:

TRANSACTION_ID
TRANSACTION_DATE
PERSON_ID
FROM_STATUS_LK
TO_STATUS_LK
FROM_GROUP_ID
TO_GROUP_ID
ORGANIZATION_LK
TRANSACTION_TYPE_LK

I want to map each record to a table, PERSONS, with the following columns:

PERSON_ID
SEQNO
STATUS_LK
GROUP_ID
FROM_DATE
TO_DATE
ORGANIZATION_LK

The new table will have SEQNO's that reset after each TRANSACTION_DATE. For example, if N transactions occurred on October, 20th 2014, then there would be N records in the child table with SEQNO's from 1-N for that date.

What SQL constructs could I use to achieve this?

The problem with a sequence is that you have to specify the top end of the range and that's unpredictable in your scenario.  But you could track it on your own without having to worry about caching. Be sure to index and check as you see fit.

CODE:

--CREATE THE JOIN TABLE
IF OBJECT_ID ('dbo.PERSONS_TRANSACTION','U') IS NULL
    CREATE TABLE PERSONS_TRANSACTION
    (
          SEQUENCE_DATE    DATE
        , SEQNO            INT
        , PERSON_ID        INT
        , TRANSACTION_ID   INT
    );
GO

--CREATE A PROC TO PERFORM INSERTS
IF OBJECT_ID ('dbo.usp_Insert_PERSONS_TRANSACTION','P') IS NULL
    EXEC sp_executesql N'CREATE PROC dbo.usp_Insert_PERSONS_TRANSACTION AS SELECT 1 STUB;';
GO

ALTER PROC dbo.usp_Insert_PERSONS_TRANSACTION  (@personID INT,@transactionID INT)
AS
    DECLARE
          @today  DATE
        , @maxSeq INT;

    --GET THE DATE
    SET @today = CONVERT(DATE,GETDATE());
    --PRINT @today

    --GET THE LAST SEQ NUMBER (NOT A TRUE SEQUENCE)
    SELECT @maxSeq = MAX(SEQNO)
    FROM PERSONS_TRANSACTION
    WHERE SEQUENCE_DATE = @today;
    --PRINT @maxSeq

    --IF THERE IS NO SEQENCE NUMBER IT HASN'T RESET YET, SO THE SEQUENCE NUMBER IS 1, INSERT
    IF @maxSeq IS NULL
    BEGIN
        INSERT PERSONS_TRANSACTION
        VALUES (@today,1,@personID,@transactionID);
    END
    --IF THERE IS A SEQUENCE NUMBER, ADD 1 AND INSERT
    ELSE
    BEGIN
        SET @maxSeq +=1;
        INSERT PERSONS_TRANSACTION
        VALUES (@today,@maxSeq,@personID,@transactionID);
    END;
GO

--ADD SOME TEST RECORDS
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=1,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=2,@personID=2;

--MOVE THE DATE BACK A DAY TO TEST RESET
UPDATE PERSONS_TRANSACTION
    SET SEQUENCE_DATE = '2017-09-24';

--ADD SOME MORE RECORDS
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=1;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=3,@personID=2;
EXEC dbo.usp_Insert_PERSONS_TRANSACTION @transactionID=4,@personID=2;

SELECT * FROM PERSONS_TRANSACTION;

--DROP TABLE PERSONS_TRANSACTION;
--DROP PROC usp_Insert_PERSONS_TRANSACTION;


View columns elimination I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one.

Here is my view:

CREATE VIEW Schema1.Object1
AS
  SELECT Object2.Column1  AS Column2,
         Object2.Column3  AS Column4,
         Object3.Column5,
         Object3.Column6,
         Object4.Column1  AS Column7,
         Object5.Column1  AS Column8,
         Object5.Column9  AS Column10,
         Object5.Column11 AS Column12,
         Object5.Column13 AS Column14,
         Object5.Column15 AS Column16,
         Object5.Column17 AS Column18,
         Object5.Column19 AS Column20,
         Object5.Column21 AS Column22,
         Object5.Column23 AS Column24,
         CASE
           WHEN Object2.Column25 >= Object5.Column25
             THEN Object2.Column25
           ELSE Object5.Column25
         END              AS Column25,
         Object2.Column26
  FROM   Schema1.Object6 AS Object2
         CROSS JOIN Schema2.Object7 AS Object4
         JOIN Schema1.Object8 AS Object3
           ON Object3.Column5 = Object2.Column5
              AND Object3.Column7 = Object4.Column1
         LEFT JOIN Schema1.Object9 AS Object5
           ON Object5.Column4 = Object2.Column3
         JOIN Schema3.Object10 AS Object11
           ON Object5.Column26 = Object11.Column27

Now, I just want to do a query like:

SELECT Column2
FROM Schema1.Object1

...but the estimated plan includes all columns from a view even though the query doesn't return them.

Why has this happened? Can I avoid this?

Here is a link for query plan: https://1drv.ms/u/s!AhdjYi359YDTgYF6sLo8fBsj5H6Ilg

also available at https://www.brentozar.com/pastetheplan/?id=S1MhIB3Ff

Each node in the plan only projects the columns minimally needed to satisfy the query correctly. You can see this by looking at the Output List of each operator. For example, the final join only lists one column. SQL Server is very good at removing unneeded projections.

I think perhaps you are expecting one or more of the joins to be eliminated. This is more tricky, because the optimizer has to be careful to not change the meaning of the query. There are four reasons to keep a join as Rob Farley describes in JOIN simplification in SQL Server:

Extra columns. The join is needed to provide columns needed by the query, either because the column appears in the final result, or it is needed for some intermediate step (like filtering, or a different join).
Row duplication. A join can increase the number of rows matched. For example a single row in table A might join with two rows in table B, so the result would contain two copies of the information in the table A row.
Row elimination. An inner join can eliminate rows from table A that do not join with any row in table B.
Added NULLs. A right or full join can introduce new NULLs where a row in table A does not match a row in table B.

SQL Server is quite good (though not perfect) at removing unnecessary joins where it is safe to do so. A join can only be removed if the optimizer has a guarantee that none of the four join effects above will affect the result.

In your case, it is likely that item #2 and #3 above applies. You may be able to make the view more simplifiable by using left joins instead of inner joins, and perhaps adding a DISTINCT to your outer query. See Rob's article for examples.


Sql Server 2012 Extended Events for Selective Xml Indexing not showing results I created a Extended Event to monitor and tune a Selective Xml Index

CREATE EVENT SESSION [SelectiveXmlIndexing] ON SERVER
ADD EVENT sqlserver.selective_xml_index_no_compatible_sql_type,
ADD EVENT sqlserver.selective_xml_index_no_compatible_xsd_types,
ADD EVENT sqlserver.selective_xml_index_path_not_indexed,
ADD EVENT sqlserver.selective_xml_index_path_not_supported
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

In SSMS when I view live data and then execute the xml queries and views, no results are being returned.

All i see is:

Is there another step I need to acomplish before seeing the results?

I followed:

http://www.mssqltips.com/sqlservertip/2731/managing-sql-server-extended-events-in-management-studio/

http://www.sqlskills.com/blogs/bobb/choosing-paths-for-selective-xml-index-part-2-using-the-sxi-xevents/

http://www.sqlskills.com/blogs/jonathan/sql-server-2012-extended-events-update-3-viewing-target-data/

Edit,

Adding requested Query and Index

Query

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

SELECT

    x.XmlId

    ,a.value('@id', 'int') As ColumnA
    ,b.value('.', 'NVARCHAR(25)') ColumnB

        From
    dbo.XmlTable As X

    Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
    Cross Apply a.nodes('tst:Forms') bb(b)

And Index for the root element and attribute

CREATE SELECTIVE XML INDEX [xsi_XmlTable] ON [dbo].[XmlTable]
(
    [XmlColumn]
)
WITH XMLNAMESPACES
(
DEFAULT 'http://tempuri.org/tst.xsd'
)

FOR
(
[1] = '/Root/Stuff' as XQUERY 'node()',
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO

I ultimately want to index the element and the text() of the Forms Element, but the below executes, but is not getting indexed for paths 3 and 4.

[1] = '/Root/Stuff' as XQUERY 'node()',
[2] = '/Root/Stuff/@id' as SQL [int] SINGLETON ,
[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms/text()' as SQL [nvarchar](25) SINGLETON

Edit: The below will not work either because I have the same path included and receive the error

Paths with same path expression indexed with selective XML index 'xsi_XmlTable' should have SINGLETON option either specified for all of them or for none of them.

So I am trying to test which will work better if I remove Singleton

[3] = '/Root/Forms' as XQUERY 'node()',
[4] = '/Root/Forms' as SQL [nvarchar](25) SINGLETON

Edit, was able to return some results after removing the join from the below query.  I am not sure why the join was not allowing it to return results, but just running the straight xquery I was able to return the results but path 3 and 4 i still not getting indexed.

WITH XMLNAMESPACES ( 'http://tempuri.org/tst.xsd' as tst )

    SELECT

         x.XmlId
        ,c.Content

        ,a.value('@id', 'int') As ColumnA
        ,b.value('.', 'NVARCHAR(25)') ColumnB

            From
        dbo.XmlTable As X

        Cross Apply x.XmlContent.nodes('tst:Root/tst:Stuff') aa(a)
        Cross Apply a.nodes('tst:Forms') bb(b)

        Inner Join TableC c on c.id = a.value('@id', 'int')

Here is a simple Xml Sample

<tst:Root xmlns:tst="http://tempuri.org/tst.xsd" SchemaVersion="0.1">
  <tst:Stuff id="1">
    <tst:Forms>Sample1</tst:Forms>
  </tst:Stuff>
</tst:Root>

Not sure what you are seeing and not seeing in the event log but it works as expected for me (version 11.0.3000).

The mistake you did with your extra path expressions is that you forgot Stuff in the path.

Testing this with the extended events and looking at the execution plan for operator Table Valued Function XML Reader ... (there should be none), I found that one extra path is enough for your queries to make it use only the index to fetch the data [4] = '/Root/Stuff/Forms' as SQL nvarchar(25). You can have [3] = '/Root/Stuff/Forms' as XQUERY 'node()' in there as well and perhaps it will make a difference or not depending on what your XML look like.

Note 1: You have mixed up the names of the columns in the index and your query. That might contribute to the problems you have with extended event.

Note 2: The events for missing selective XML indexes is generated when the query is compiled. If the query plan is used from the cache you will not see any events.


Call to Function in Oracle says it's not declared When I call the next stored-function in oracle:

CREATE OR REPLACE FUNCTION SOLICITAR_DISPONIBILIDAD (parqueo IN NUMBER, semestre IN NUMBER,
año IN NUMBER, jornada IN NUMBER)
RETURN INTEGER IS
cupo_disponible INTEGER := 0;
cupo_tomado INTEGER := 0;
BEGIN
SELECT capacidad
  INTO cupo_disponible
  FROM estacionamientos
  WHERE numero = parqueo;
SELECT COUNT(*)
  INTO cupo_tomado
  FROM asignacion_parqueos ap
  WHERE ap.par_año = año
    AND ap.par_ciclo = semestre
    AND ap.par_numero = parqueo
    AND ap.par_jornada = jornada;
RETURN cupo_disponible - cupo_tomado;
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END SOLICITAR_DISPONIBILIDAD;

It says 'SOLICITAR_DISPONIBILIDAD' is not a procedure or is undefined. Does anyone knows why?

I call it like this:EXECUTE SOLICITAR_DISPONIBILIDAD(1,0,2014,0);

A function has to return something (in your case an INTEGER) somewhere.

Try SELECT SOLICITAR_DISPONIBILIDAD(1,0,2014,0) FROM DUAL


Unable to Connect LocalDB Server in Navicat Premium I've installed LocalDb server on my PC.

I am trying to create a connection to the LocalDB server in Navicat Premium.

I am unable to get the correct Host Name/IP Address for the LocalDB server.

I noticed by default LocalDB server always creates an instance named MSSQLLocalDB when started.   I've searched the internet but couldn't find the exact solution for my problem in Navicat premium software.

Here is the error I am getting:

I've installed only LocalDB server. Is it not a SQL Server native client? I don't want to install both LocalDB server and MS SQL Server Express Edition as I have no need of SQL Server Express Edition.

Installing SQL Server Native Client says:

  Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2016. There is no SQL Server 2016 Native Client.

I am using the recent SQL Server 2016 LocalDB. I believe the download links are for SQL Server 2012. I am using SQL Server 2016, which does not need a native client.

After trying out some more I was able to land on a new error as shown in the second image file below. I am sure that the instance name is correct as you can see in the image:

Is Navicat installed on the same computer as LocalDB? LocalDB doesn't allow for remote connections.

Assuming that Navicat is installed on the same computer: is Navicat a service or desktop application? LocalDB runs, by default, as a private user process that only your Login can see (no problem for a desktop application that you are running). If you want others (such as a service) to be able to access your LocalDB instance, you need to share it using the sqllocaldb.exe command line utility.

Also #1: (localhost)\MSSQLLocalDB is not a valid syntax. The reason for specifying (localdb)\InstanceName is that the (localdb)\ portion of it is a special syntax that the underlying connection library understands and hands-off to the LocalDB library (it is special because it can auto start up that instance of LocalDB simply by trying to connect to it using that syntax).

Also #2: SQL Server Native Client is a connection library (ODBC is another type of connection library), and it is being used by Navicat to connect to SQL Server. If Navicat is saying that you need to install SQL Server Native Client, then you need to download it from Microsoft. And yes, the last version of it was for SQL Server 2012. But if Navicat requires it, then you kinda need it.

Now, it appears that Navicat Premium is a desktop app so you likely don't need to worry about sharing the instance, unless there are other users logged onto your computer that you want to be able to access this instance. But for now, I will assume that it is only you using it. Keeping in mind the fact that LocalDB isn't a service and so not always running, and that it can be auto-started by attempting to connect to (localdb)\something, it should be understood that it takes several seconds for the LocalDB instance to start. The amount of time it takes is longer than the default connection timeout, at least in SQL Server Management Studio (SSMS). So, when initially connecting, it is expected to get the timeout error, so immediately try connecting again. If you wait too long (several minutes) to attempt connecting again, you will get the timeout again as the instance will auto-shutdown after several minutes of no connections.

So, again, assuming that both LocalDB and Navicat are running on the same computer AND by the same Windows Login, try connecting to (localdb)\MSSQLLocalDB. When you get the connection timeout error, immediately try connecting again. If it doesn't work, then something else is wrong.


Promotion of PostgreSQL standby and application reconnection Background:

I work for a small company.  We have been using PostgreSQL on Heroku as one of our core technologies.  While, in general, we've been happy with Heroku, the company has made the decision that our monthly bill with them is getting prohibitively expensive.  As a result, the engineering team has decided to port everything over to DigitalOcean.  Since I'm the most experienced database developer on the team, I've been given the task of figuring out "what to do about the databases".  To be clear, I've got almost 20 years of writing SQL statements and stored procedures and 4 years with PostgreSQL, but I'm definitely not a DBA (And frankly, I'm scared to death that I'm going to screw things up).  I've spent every night for a week reading blogs and watching videos on configuration strategies for "high availability".  What I've decided to do is create a master, with 2 hot standbys.  For each service/product we have, the architecture is basically the same:

1:n webserver(s)
1:n jobserver(s)
1:n reporting server(s)

Web and job(s) will be pointed to master, and the reporting server(s) will be pointed to 1 of the hot standbys.

note: We are using Anisble for Cloud/IT automation.

So, the idea is that if the master fails, the most up-to-date standby gets promoted to Master. Obviously, that means that the other hot standby must be switched to the new master, and the all the servers (web, job, reporting) must be reconfigured and reconnected.

Questions

I've read a lot about how to do this with something like repmgr.  But, nothing on how all of the other servers know about this change and are reconfigured.  Is there a simple way this is done?  With Anisible, we could have a playbook that connects and changes environment vars and restarts the services, but it's hard for me to believe this is the way everyone does it.  It feels like there is a piece missing for me.  It feels like you'd just need/want some kind of a proxy in the middle and just reconfigure it.  Is this where people use something like PgBouncer or PgPool?

Any help will be greatly appreciated.

Relevant technologies:

PostgreSQL 9.3 (but, could move up to 9.4 if there is a reason)
DigitalOcean
Ubuntu 14.04
Python 2.7
Ansible

To be truly highly available you will need some logic built into the application to handle this.  You can do this sort of thing with pg_pool, but what if pg_pool breaks?

I think there are many ways you could work this one, but the way I would do it would be:

Master DB (lets call it A), and two slaves (lets call them B and C).  B pulls changes from A and C pulls changes from B in a cascading replication setup.  This means if A fails and is a gonner promote B, no need to change C as its already a slave of B, when A is back you make it a slave of C and so on.

You will need to make changes to your application so that it has some intelligence in it regarding the state of the database.  This would be typically done by having a layer that manages the connections, and it runs a test query (like "SELECT current_timestamp") if the query passes all is fine and it gives the connection to your application to use.  Each application will need to know a list of servers to use in which order so the one that writes will use A first then B.  So in the event the primary fails it tries the secondary (and potentially the third and so on), until it gets one where the test query works.

With this in place if C fails its only read only, the applications will just fail over to B or A and start using C if you get it back.  If B fails, same deal though you might want to do something with C pointing it to A.  If A fails then the system will go read only (but will still work for all read queries).  There will then need to be some human intervention to say "yep A is gone", promote B (which will only take seconds), when B is promoted the writers will go though the connection layer, it will test A and fail test B and pass to the application will get a connection to B which is now a master so writes will be allowed.

I appreciate that this is still not a full HA solution (as there is still a manual step, but its a very simple and quick one), but I think we are going to have to wait for PostgreSQL 9.5 for that.

As I say I dont think there is one dead cert right answer to this, but this is how I would have done it.

Hope it helps


Multiple increments in table Let's say I have a table which stores different bills.

There are multiple departments using this database and every department  needs to use their own incremental numbering for the bills.

I do not want to use multiple tables, so I have an issue what the best method for this issue is.

I have come up with an suggestion by myself, but I think this is not a good solution. Namely keep a column DepartmentBillId and keep track of the numbering in PHP (this will be the easiest way though, but I have multiple users so this could actually cause conflicts).

I hope somebody can guide me in the best direction!

For me the way to go is that you create a pre-insert trigger that will set the BILL_ID for that department when no BILL_ID was given. To get the next BILL_ID you could use a table that holds the last BILL_ID per department. An other way is to get the MAX(BILL_ID) for that department and add 1 to it. When you work with multiple users then the first solution is better and faster. The primary key for the BILLS table would be DEPARTMENT_ID, BILL_ID and perhaps even YEAR if you need to start at 1 when the new year starts.


pg_upgrade unrecognized configuration parameter "unix_socket_directory" I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this command as the postgres user

$ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.3/data/ -j 2 -u postgres

The error in the log

  command: "/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1
  waiting for server to start....FATAL:  unrecognized configuration parameter "unix_socket_directory"
  .... stopped waiting
  pg_ctl: could not start server

As pointed by a_horse in the comments that parameter was replaced by unix_socket_directories (plural) in 9.3. But the server version being started is the old one 9.2:

$ /bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.2.4

Any ideas?

I hacked the problem by running (as root):
mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
     /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run pg_upgrade as intended, then undo the hack:
    mv -f /usr/bin/pg_ctl{-orig,}

The problem is that pg_upgrade executes the program pg_ctrl with arguments that specify files in the old "unix_socket_directory" rather than the new "unix_socket_directories" (note the second is plural). This hack renames the original /usr/bin/pg_ctl to /usr/bin/pg_ctl-orig, and then creates a shell script in its place that simply calls the original pg_ctl program, passing all arguments with any strings "unix_socket_directory" changed to "unix_socket_directories".
In bash, one can change a portion of a string, say from bar to baz in a variable $foo, by using ${foo/bar/baz} (note this does not change the variable, but rather returns the variable's modified contents). Arrays can also be used with ${x/y/z} to retrieve an array with all of its contents replaced, all at once. The variable $@ is an array that contains all arguments passed to the program/script/function, so the new pg_ctl script executes the old one with all arguments changed from the old directory name to the new one.


MySQL on Windows Refuses to Start as a Service I am trying to install MySQL 5.7.33 onto a windows 2012 server. A process I have done many many times.
I have copied the Zip file onto the server and expanded it into G:/mysql57/ (the same zip file I used to install on other servers previously without issues).
I place a my.ini file into this directory. Then I initialize the database:
G:/mysql57/bin/mysqld.exe --initialize --basedir=G:/mysql57/ --datadir=G:/mysql57/data/
So far so good. The error log is created along with temporary password. I then install the service:
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file="G:/mysql57/my.ini"
I then try to start the service, which results in:
Error 1053: The service did not respond in a timely fashion
It also annoyingly doesn't add anything to the error log.
If I try and run mysqld manually however it works fine, no errors
G:/mysql57/bin/mysqld.exe --defaults-file=G:/mysql57/my.ini
I can log in, I change my password etc.
Yet starting the service fails, every time.
I should point out that there is another MySQL database on there in D:/mysql (MySQL 5.5) which is using port 3306 and runs fine. This new one is using Port 3307.
Again a process I have done many times over.
I have checked the Windows Event Viewer, nothing.
I have tried the my.ini file from the old database.
I have tried a minimal my.ini file (port, basedir and datadir only).
Still nothing.
Is there anything else I have missed, as I've now lost most of a day on something that was supposed to be done by lunchtime.

It seems it all came down to the " " which it didn't like.
So I changed:
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file="G:/mysql57/my.ini"
to
G:/mysql57/bin/mysqld.exe --install MySQL57 --defaults-file=G:/mysql57/my.ini
and it started up straight away. I can only assume this is something to do with Windows Server 2012, as I repeated the whole process (with " ") on my Windows 10 PC, and it worked fine.


How to Install postgresql-client-11 on Ubuntu 18.04 I need to use the pg_basebackup/pg_dump program on an Ubuntu 18.04 system to connect to a remote PostgreSQL 11.6 server.

However, the current system only contains pg_basebackup 10.10 and the remote database is using PostgreSQL 11.6. Running pg_basebackup give the error

  pg_basebackup: incompatible server version 11.6

Tried to install the correct version of pg_basebackup using the command:

apt install postgresql-client-11

but the apt package is not found. Only postgresql-client, postgresql-client-10 and postgresql-client-common are available the system's apt repos.

Question: How can we install the correct version of pg_basebackup without having to copy it over from the remote database server, and without installing PostgreSQL 11.6 on this machine?

Install it from PostgreSQL's own apt repository, adapted from these instructions.

sudo apt-get install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt install postgresql-client-11
pg_basebackup -V

  pg_basebackup (PostgreSQL) 11.7 (Ubuntu 11.7-1.pgdg18.04+1)