Restore of database failed .bak file SQL Server 2008 I have faced following error during restoring database:

  TITLE: Microsoft SQL Server Management Studio

  Restore failed for Server  (Microsoft.SqlServer.SmoExtended)

  ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 92382625792 additional free bytes, while only 42311290880 bytes are available. (Microsoft.SqlServer.Smo)

Database .bak file size is 697 MB and we have around 39 GB free space on server C drive.

Please anyone let me know, what should I do?

You need to check the actual size of the database files which you can check by running

RESTORE FILELIST ONLY FROM <backup_device>

This will give you information about the files contained in the backup and their sizes in bytes

You need the same space available on the server as the database file size, the backups can be compressed.

Now you need to free up more space on c: on the server but I would recommend that you add drives to your server and that you would look at storage best practies


Backup of database X on SQL Server 2008 on server A - restore to SQL Server 2012 database Y on server B I want to copy a SQL Server 2008 database to SQL Server 2012 with a different database name.

Took a backup of 'production' and copied it to the SQL Server 2012 machine.
Created a new SQL Server 2012 database with a 'test' name.
Tried to restore the .bak file but SQL Server says 'test' is busy

What is the correct way top do this?

Basically getting a snapshot of a production 2008 database into a test 2012 environment.

Thanks

Bob

Don't create the database on server B before restoring. Just restore it. It doesn't have to exist first in order to do that. This should work whether the database already exists or not (so perhaps you should consider using a better name than 'test'), just open a new query window:

USE master;
GO
RESTORE DATABASE test
  FROM DISK = 'E:\backup path\backup.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'data_file_name' TO 'D:\data file path\test.mdf',
    MOVE 'log_file_name'  TO 'L:\log file path\test.ldf';

But I have no idea where you are getting a message about "busy" - are you restoring using the UI? Stop doing that - the UI is notoriously bad for assisting with tasks like backup and restore. And make sure you don't have any query windows etc. with their context set to the existing test database. To kick people out so you can restore over it, you can use:

USE master;
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

To get the file names to place in the MOVE arguments, run this:

RESTORE FILELISTONLY FROM DISK = 'E:\backup path\backup.bak';


"ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it? Earlier I created a user :

SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.

but when I login as suhail and try to create a table I get an error saying ORA-01950: no privileges on tablespace 'SYSTEM' .  What error is it ? Why does it occur ?

SQL> connect suhail
Enter password:
Connected.
SQL> create table suhail_table(name char(10));
create table suhail_table(name char(10))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this:

sql> alter user scott quota 50m on system;

Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited.

However it is a bad practice to store user and application data in SYSTEM tablespace as it causes data fragmentation and thus degrades performance. So I would recommend you to create a new permanent tablespace:

sql> create smallfile tablespace users datafile '/u01/app/oracle/oradata/ORCL/users.dbf' size 10g;

And then set it as the default database permanent tablespace so that objects created by the users go into that default tablespace:

sql> alter database default tablespace users;


variable interval_minutes number;

exec :interval_minutes := 10;

select
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440,
  count(*)
from
  clients
group by
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440
order by
  1
;

Based on: Summarizing data over time - by time interval


Query hangs in preparing state and blocks others in waiting for table flush In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries:

UPDATE products
SET visible = 1
WHERE merchant_id = in_merchant_id
    AND source_code IN (
        SELECT source_code
        FROM feeds_processing_data
        WHERE processing_id = in_processing_id
        )

It basically updates a specific "window" on the products table, turning visibiliy on or off based on some other info. That table is central to the webapp, searches from the frontend are run on in (either via FULLTEXT or by code lookups), and background processes update its contents from time to time.
I usually approach these issues by checking the query plan and verifying that proper indexes are there.

What puzzles me, though, is that listing sessions from information_schema.PROCESSLIST shows that the above query sits in the preparing state, while the other blocked queries are Waiting for table flush, something I have little to no knowledge about.

This is one of those blocked queries:

SELECT id,
    [...]
FROM (
    SELECT (MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE)) AS rank,
        p.*
    FROM products p
    WHERE visible = 1
        AND MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE) LIMIT 400
    ) ranked
ORDER BY final_price

At a first glance, I see no problems with that UPDATE, this is its EXPLAIN plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY products    range   uk_merchant_id_source_code  uk_merchant_id_source_code  4   const   3861    Using where
2   DEPENDENT SUBQUERY  feeds_processing_data   ref fk_processing,source_code   fk_processing   4   const   2   Using where

Indexes on the involved tables follow.

SHOW INDEXES FROM products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
products    0   PRIMARY 1   id  A   409324  NULL    NULL        BTREE
products    0   uk_merchant_id_source_code  1   merchant_id A   126 NULL    NULL        BTREE
products    0   uk_merchant_id_source_code  2   source_code A   409324  NULL    NULL        BTREE
products    1   visible 1   visible A   2   NULL    NULL        BTREE
products    1   gtin    1   gtin    A   409324  NULL    NULL    YES BTREE
products    1   industry_code   1   industry_code   A   409324  NULL    NULL    YES BTREE
products    1   name_description_manufacturer_name  1   name    NULL    409324  NULL    NULL    YES FULLTEXT
products    1   name_description_manufacturer_name  2   description NULL    409324  NULL    NULL    YES FULLTEXT
products    1   name_description_manufacturer_name  3   manufacturer_name   NULL    409324  NULL    NULL    YES FULLTEXT

SHOW INDEXES FROM feeds_processing_data:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
feeds_processing_data   0   PRIMARY 1   id  A   10336188    NULL    NULL        BTREE
feeds_processing_data   1   fk_processing   1   processing_id   A   2981    NULL    NULL        BTREE
feeds_processing_data   1   source_code 1   source_code A   136002  NULL    NULL    YES BTREE
feeds_processing_data   1   rejected    1   rejected    A   1   NULL    NULL        BTREE

The first question I can't address is: why is it taking so much to complete the prepare phase?
Then, how could I further inspect the issue? What informations am I missing here?

As @jkavalik wrote you're executing the update with a dependent subquery which effectively runs it 3861 times making it highly ineffective even if the query itself runs fast.

An updated version of the query could be:

UPDATE products
JOIN feeds_processing_data ON
   (product.source_code = feeds_processing_data.source_code
    AND ?.processing_id = ?.in_processing_id)
SET products.visible = 1
WHERE merchant_id = in_merchant_id

You didn't attach the table schemas so I just assume this would work. You might need to fully qualify the field names (using tablename.fieldname). Also feeds_processing_data could benefit from a composite index covering the involved fields.


How find all the data loaded on previous day? Issue: Data Loading into development environment from production database

My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is

How can I get all the data loaded into Oracle database yesterday?
Is there a way in Oracle to get all data loaded into db on previous day, manage all dependencies and generate another dev snapshot with one day minimal data?

Oracle Data Pump Network-Mode Import may be one of possible solutions. You can invoke Data Pump Import in network mode using database link (NETWORK_LINK parameter) and specifying which version of data you want to import with FLASHBACK_SCN or FLASHBACK_TIME parameters.

First, create a database link to you production database on your development database:

CREATE DATABASE LINK prod_db
  CONNECT TO system
  IDENTIFIED BY "hackme"
  USING 'orcl';

Where orcl is the connect string you're using to connect to your production database. You can test if the link is working by querying some table in the production database:

SQL> select count(*) from hr.employees@prod_db;

  COUNT(*)
----------
       107

See Database Administrator's Guide for more info about creating database links.

Then to import data into development database from production database, you would run Data Pump Import like this:

[oracle@oca ~]$ impdp sys/hackme \
SCHEMAS=hr,oe \
CONTENT=ALL \
NETWORK_LINK=prod_db \
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')"

In this example, two schemas are imported, CONTENT specifies that both metadata and data should be imported (default), and FLASHBACK_TIME specifies the time of the snapshot you want to import.

Due to the restrictions of FLASHBACK_TIME:

  The FLASHBACK_TIME parameter pertains only to the Flashback Query
  capability of Oracle Database. It is not applicable to Flashback
  Database, Flashback Drop, or Flashback Data Archive.

You have to ensure that you have enough undo data on your source production database for the snapshot to be consistent, since Flashback Query depends entirely on undo.

Based on your requirements, you would need to guarantee undo retention to at least 24 hours to be able to get the previous versions of the data you're importing. You would also need to size you undo tablespace on the source database appropriately.

To guarantee the retention, connect to your source database and alter your current undo tablespace like this:

SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.

To specify the retention time (in seconds):

SQL> alter system set undo_retention=86400;
System altered.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS1

(86400 is 60 seconds * 60 minutes * 24 hours.)

Sizing the undo tablespace:

SQL> alter database datafile '/oradata/ocaexam/undotbs01.dbf' resize 10g;

You can determine what's the best size for your undo tablespace using Undo Advisor in Enterprise Manager.

If you would like to automate your import process, you could use PL/SQL and Oracle Scheduler as described here.


Get the Text of the currently running SQL? This is something I am just interested in knowing if it is possible.

Could I write an SQL query that could return the text of itself as a variable?

Where I might use this would be on things like reports that I might want to return a variable that saves the actual query that ran to generate the report, or as a debugging tool.

I have seen there is:

sys.dm_exec_text_query_plan

that would return the text of the query but can't see an easy way to always get the currently executing queries ID to link back to this table.

Has anyone ever written something to do something like this?

I am using SQL Server 2008, 2012 and 2014.

There is no way that a query can output its own text, that I am aware of, but you can certainly capture it using sys.dm_exec_requests.

You can try this:

-- Query to Run
SELECT 100 / 10

-- Query Text Capture
DECLARE @Query NVARCHAR( MAX )
SET @Query = ( SELECT text /*, query_plan*/
    FROM sys.dm_exec_requests AS er
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
        CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
    WHERE session_id = @@SPID );
SELECT @Query

Or inside SP:

CREATE PROCEDURE Test
AS
    SET NOCOUNT ON

    SELECT 100 / 10

    DECLARE @Query NVARCHAR( MAX )
    SET @Query = ( SELECT text /*, query_plan*/
        FROM sys.dm_exec_requests AS er
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS et
            CROSS APPLY sys.dm_exec_text_query_plan( er.plan_handle, DEFAULT, DEFAULT ) AS ep
        WHERE session_id = @@SPID );

    SELECT @Query
GO
EXEC Test

I think you would be better served by SQL Trace though.

For a procedure, you can use sp_helptext and equivalents to get its text. I don't think you would need to capture SP text at the point execution as SPs don't get redefined often. Capturing SP name should be enough.


how to check if a procedure is running, and what permissions are required? I have a process run by a domain account MY_LOGIN that needs to execute a procedure THE_PROCEDURE but only when another specific procedure, called PROCEDURE_TO_TEST is not running.

All this is within one specific database called MY_DATABASE

How can I do for MY_LOGIN to check if the procedure PROCEDURE_TO_TEST is running ?

What permissions should I grant to MY_LOGIN so that it can do this check?

How can I test if this is working?

I create my PROCEDURE_TO_TEST in a very simple way for testing purposes:

    USE MY_DATABASE
    GO

    CREATE PROCEDURE DBO.PROCEDURE_TO_TEST
        AS BEGIN

        SELECT 'BEGIN'
        WAITFOR DELAY '00:00:15'
        SELECT 'AFTER'

        END
GO

I execute the stored procedure PROCEDURE_TO_TEST and on a separate window I
use the script below to check if it is running. (if it shows up, then it is running)

declare @object int
SELECT @object=OBJECT_ID('DBO.PROCEDURE_TO_TEST')
SELECT @OBJECT

    Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

I grant the required permissions to MY_USER:

use master
go
GRANT VIEW SERVER STATE TO MY_LOGIN
GO
use MY_DATABASE
go
GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN
GO

Because I use OBJECT_ID (Transact-SQL)

  A user can only view the metadata of securables that the user owns or
  on which the user has been granted permission. This means that
  metadata-emitting, built-in functions such as OBJECT_ID may return
  NULL if the user does not have any permission on the object. For more
  information, see Metadata Visibility Configuration.

And sys.dm_exec_sql_text (Transact-SQL)

  Permissions Requires VIEW SERVER STATE permission on the server.

Now for testing purposes I will run the procedure PROCEDURE_TO_TEST and on a different window I will check if it is running, IMPERSONATING MY_LOGIN.

to do this I use:

EXECUTE AS LOGIN='MY_LOGIN'

AND

--===================================================================================
-- check who is the user running the show, and the original login
-- this is to test the 'execute as login' below
--===================================================================================

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
                         CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))
SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

And when I have done the tests, and found out the user can run the script then I come back to be me:

REVERT

now I wrap the script into a procedure that I create called isSPRunning that receives as parameter @ProcedureName and check if @ProcedureName is running:

USE MY_DATABASE
GO

GO
CREATE procedure issprunning
@ProcedureName VARCHAR(108)
AS
/*
=======================================================================
Script  :   procedure issprunning

Desc    :

  Take as parameter a procedure name and returns 1 if it is running
   and 0 if it is not running or the procedure has not been found
   or the user does not have required permissions.

Required Permissions:

   The required permissions to make use of this procedure are:

   1) procedure to run this procedure
   2) VIEW SERVER state
   3) view definition on the @ProcedureName procedure

   the granting of permissions:
   (please replace the username and database name accordingly)

    use master
    go
    GRANT VIEW SERVER STATE TO MY_LOGIN
    GO
    use MY_DATABASE
    go
    GRANT VIEW DEFINITION ON [PROCEDURE_TO_TEST] TO MY_LOGIN
    GO
    GRANT EXECUTE ON isSpRunning TO [MY_LOGIN]
    GO

Usage   :

         DECLARE @I INT
         EXEC @I = issprunning 'PROCEDURE_TO_TEST'
         SELECT @I

Links   :

=======================================================================
History
Date             Action        User                Desc
-----------------------------------------------------------------------
03-Mar-2017     created       Marcelo Miorelli

=======================================================================
*/
BEGIN
SET NOCOUNT ON

declare @object int
SELECT @object=OBJECT_ID(@ProcedureName)
--SELECT @OBJECT

RETURN CASE WHEN
EXISTS(

                Select *
                from        (

                                SELECT *
                                FROM sys.dm_exec_requests
                                where sql_handle is not null

                ) r
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
                where t.objectid = @object

) THEN 1 ELSE 0 END

END
GO
GRANT EXECUTE ON dbo.issprunning to [MY_LOGIN] as dbo
GO

In order to make it easier, so that I can pass any procedure name that exists on MY_DATABASE I have extended a the permissions granted, but consider the implications of this in your own environment.

    use MY_DATABASE
    go
    GRANT VIEW DEFINITION TO [MY_LOGIN]
    GO


Challenge with modelling a basic membership table I am facing some challenges modeling a table for mapping users and their chatroom. I found two ways to get around the specific challenge I am facing, but with performance concerns. I have added a simplified version of actual scenario below-

Table: membership

Columns:

user_id         int
user_name       varchar
user_role       int
chatroom_id     int
chatroom_name   varchar

constraints:

user_id                     unique: an user can only be in one chatroom
user_name+chatroom_id       composite unique: one chatroom can have only one user with same name

When an user leaves, or kicked from a chatroom, I simply delete the row from membership.

Problem: I want to store the membership record for historical reasons, either on same table or a separate one.

Approach 1:
Keep a column 'status'. values: active, left, kicked
Problem with approach 1: Cannot maintain either of the unique constraints (when the same user joins that same or some other chatroom)

-

Approach 2:
Delete the row and use a trigger to arcive the record to different table
Problem with approach 2: Cannot include the information as to why the record was deleted, as trigger cannot determine that, nor there is any way to pass that info to trigger

-

Approach 3:

INSERT INTO archive (..., reason) VALUES(SELECT from membership where ..)
DELETE FROM membership

within a transaction, no trigger
Problem with approach 3: Works. But performance concerns- requires three queries, insert and delete performance hits for index, requires separate table.

-

Approach 4:
using two new columns- 'status' and 'uuid'.
uuid will be null when status=active, and when status is updated to any other value, uuid will be populated with a GUID.
Each of two constraints will be changed to be composites including the uuid column:

user_id + state + uuid
user_name + chatroom_id + state + uuid

Problem with approach 4: Works. But performance concerns- too many composite unique indexes. Maybe int instead of varchar (GUID) for uuid would improve performance a bit, but that would require further tricks.

Should I normalize the table further? Or any basic design flaw or lack of database design knowledge that led me to this complexity?

If not, which one is the better approach for this case?

Approach 5:

is_in_room_now TINYINT DEFAULT NULL, -- always either 1 or NULL
UNIQUE KEY (user_name,chatroom_id,is_in_room_now)

Nulls are not equal to each other, so you can have infinitely many rows with "the same" (user_name, chatroom_id, NULL) without violating the unique constraint... but you can only set is_in_room_now to 1 for exactly one (user_name, chatroom_id, 1) row.

When the user is removed from the room for whatever reason, set the value to null (not 0).


MySql - Rows to Dynamic Columns I have the following sql query:

 SELECT gp.group_name as `Group Name`,
   CONCAT(lc.first_name, ' ', lc.last_name) as `Client Names`
FROM `group` gp
INNER JOIN cbo ON (gp.cbo_cbo_id = cbo.cbo_id)
INNER JOIN credit_officer_has_cbo cocbo ON (cbo.cbo_id = cocbo.cbo_cbo_id)
INNER JOIN credit_officers co ON (cocbo.credit_officers_id_credit_officer = co.id_credit_officer)
INNER JOIN user usr ON (co.user_id = usr.id)
INNER JOIN branches br ON (co.branches_br_id = br.br_id)
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id)
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)

To Generate the following output

  +-----------+-------------+
  | Group Name| Client Name |
  +-----------+------+------+
  |  Group A  | Joan        |
  |  Group A  | Patrick     |
  |  Group A  | Smith       |
  |  Group B  | Samuel      |
  |  Group B  | Elise       |
  |  Group C  | Marco       |
  +-----------+-------------+

But i need to arrange the 'Client name as rows' as below.

+----------+-------------+-------------+-------------+
|Group Name|   Member 1  |   Member 2  |    Member 2 |
+----------+-------------+-------------+-------------+
|Group A   |   Joan      |   Patrick   |    Smith    |
+----------+-------------+-------------+-------------+
|Group B   |   Samuel    |   Elise     |             |
+----------+-------------+-------------+-------------+
|Group C   |   Marco     |             |             |
+----------+-------------+-------------+-------------+

As you can see the 'Client Name' becomes individual columns. The number of 'Member' columns is unpredictable. I couldn't find a break through to solve this query. Any help would be much appreciated. Thanks in advance!

Maybe GROUP_CONCAT would be sufficient for your purposes? Instead of giving individual columns for each member in a group, it would give a single column with comma-separated lists of members. In your case I think it would be something like:

SELECT gp.group_name as `Group Name`,
   GROUP_CONCAT(CONCAT(lc.first_name, ' ', lc.last_name)) as `Client Names`
FROM `group` gp
...
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id)
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)
GROUP BY gp.group_name

Which should give something like:

+----------+----------------------+
|Group Name|   Client Names       |
+----------+----------------------+
|Group A   |   Joan,Patrick,Smith |
+----------+-------------+--------+
|Group B   |   Samuel,Elise       |
+----------+----------------------+
|Group C   |   Marco              |
+----------+----------------------+


ALTER TABLE online by keeping data as BLOBs Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source

  Additionally, it's crucial to be able to change the schema: either to
  use a database where ALTER TABLE can be done online, work around the
  database's limitations and build a way to non-destructively perform an
  ALTER TABLE, or use extreme de-normalization (store the value as a
  serialized blob along with a version that can be used to infer the
  schema in use at that time). This is probably important for any kind
  of a real-time database driven  application, but crucial for social
  networks where new features (e.g., new profile fields) are added all
  the time.

That's not what this is saying. Few systems can change a schema without taking the system offline -- even modern databases typically require exclusive table locks and table-rewriting to remove a column. This presents a problem in some workloads. Think about a contact book where you can add a field, and a delete a field. If each of those operations changed the schema of the data by issuing an ALTER TABLE, you'd have absolutely chaos. That's not how schema is supposed to work.

So you have two traditional options,

Schemaless data,
Entity–attribute–value model (which is frequently both schema-less and type-less, and slower.)

Schemaless Data

Storing the column as a serialized blob may sound bad, but that's only because DBAs are usually not capable of that level of engineering and there could be dragons there. It's just out of their pay grade to implement binary types. That's exactly what happens though with advanced types under the hood. Take for instance, in PostgreSQL,

JSONB
hstore

These types are in fact binary blobs under the hood. The database just provides operators and stringification for them. In PostgreSQL they're TOASTABLE binary types.

Before JSONB, and before hstore, we used to achieve the same thing with Storable::nfreeze. This is what the author is calling extreme demoralization. You serialize the version of the object (metadata), and the object itself and dump the whole thing into the database.

DBA's write in a declarative language (SQL) and model and query relational data. That's entirely different than the skillset required to create something like JSONB. We're a different community from Stackoverflow because we have a different skill set. How many databases have hstore, or jsonb -- or even SQL Arrays? These are hard problems. Getting it right is a niche skill for a DBA.


Datetime2 rounding issue We are working on a multi-database application (read and write to multiple databases). The datamodel is identical on the databases.

We are inserting a timestamp (12 fraction digits) value in a datetime2(6) column in MSSQL however MSSQL rounds the value making it different to other databases where the extra fraction digits are ignored.

Example:

DECLARE @t TABLE(x DATETIME2(6))
INSERT @t SELECT '2017-03-28 14:00:59.4106489'
SELECT x FROM @t

Result: 2017-03-28 14:00:59.410649
Expected: 2017-03-28 14:00:59.410648

DB2 provides the expected result by throwing away the 7th fraction digit.

How can we make MSSQL not round the datetime2 value ?

EDIT

The application writes a java.sql.Timestamp object with 12 fraction digits to DB2 and MSSQL. In DB2 the column is a TIMESTAMP(6) and in MSSQL a DATETIME2(6). DB2 truncates from 12 fraction digits down to 6. MSSQL rounds down to 6.

I don't think there is a way to make SQL Server default to truncating incoming values instead of rounding.

You can however control the values in your Java application, before they are sent to the databases.

According to Java 8 docs, java.sql.Timestamp has getNanos() and setNanos() methods you can use (pseudocode, my Java is rusty):

ts = ...                  // the Timestamp object

micros = ts.getNanos() / 1000 ;   // extract and truncate to microseconds
ts.setNanos( micros * 1000 ) ;    // set the truncated value back

insert(..., ts, ...) ;    // INSERT


Cannot connect using tnsnames.ora but can connect by substituting its details I'm setting up a minimal Oracle Instant Client on a non-Oracle Linux host from zip files (as per https://docs.oracle.com/database/121/SQPUG/apd.htm#SQPUG157) and have installed

instantclient-basiclite-linux.x64-12.1.0.2.0.zip
instantclient-sqlplus-linux.x64-12.1.0.2.0.zip

(note: no tnsping is available).

I've added the following environment variables:

ORACLE_SID=prod; export ORACLE_SID
ORACLE_HOME=/local/share/bin/oracle_client/instantclient_12_1; export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/sqlplus; export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME:${LD_LIBRARY_PATH}; export LD_LIBRARY_PATH
TNS_ADMIN=/local/share/oracle/network; export TNS_ADMIN

The $TNS_ADMIN/tnsnames.ora file is provided by an Oracle host (I cannot change it), is readable from this non-Oracle host, and contains no syntax errors (it is in use by Oracle hosts).  The specific entry looks much like this:

PROD.DOMAIN =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = prod.domain)(PORT = 1234))
  )
 (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = prod)
 )
)

That said, I cannot connect on the command line using the preferred method for us:

$SQLPLUS user@prod
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 14 14:45:22 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

suggesting that tnsnames is not being found or parsed or... ? I believe I have everything in place as per "Connecting to a Database with SQL*Plus Instant Client" in the referenced document at the top.

Note that I can connect if I use the host name itself:

$SQLPLUS user@\"//prod.domain:1235/prod\"

or the details from tnsnames itself (proving good syntax):

$SQLPLUS user@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=prod.domain)(PORT=1234)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod)))"

Any ideas?

My current workaround is the third method using Perl to parse tnsnames into a set of one-liners and grep for the instance I want but it'd be nice to know why this isn't working and how to fix it.

Thanks.

Thank you to Wernfried Domscheit for pointing me to sqlnet.ora.  This file was not referenced in the documentation I was using so I had no idea it existed.  After visiting an Oracle host and finding the file in $ORACLE_HOME/network/admin, I copied it to $TNS_ADMIN dir and minimized it through testing to the one setting (no others were needed) Wernfried referenced:

# this setting = my local domain
NAMES.DEFAULT_DOMAIN = DOMAIN

and my preferred connection method of

$SQLPLUS user@prod

succeeded.  I will speak with the Oracle DBAs about this file and it's proper inclusion in this shared dir.


left joining and combining the results where they are not null I'm given a MySQL database with tables like this:

show create table c;
CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  -- and other columns
  PRIMARY KEY (`id`)
)
show create table c_r;
CREATE TABLE `c_r` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL,
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)
show create table c_s;
CREATE TABLE `c_s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)

Now, I want, for each date, the sum of the something_numeric values from c_r and the sum of the something_numeric values from c_s. If one of those is null, I still want the other. My output should have a row per date.

I don't see a way to get that. If I join twice on c.id, I get only such dates as exist in both c_r and c_s; so I need to left join twice instead. But then how do I make sure to get one row per date altogether, whether that date exists in only one table, only the other, or both?

Without an actual query attempt from you, some thing are difficult to understand.

From what you've said, you're looking to group by date only, not on any information from c. That being the case, it's not clear that c needs to be in the query at all.

The following query should give you the totals you want, for all dates found in either c_r or c_s:

SELECT c_date
      ,COALESCE(SUM(r_amount),0) as r_amount
      ,COALESCE(SUM(s_amount),0) as s_amount
  FROM (
        SELECT c_date, 0 as r_amount, something_numeric as s_amount
          FROM c_s
        UNION ALL
        SELECT c_date, something_numeric as r_amount, 0 as s_amount
          FROM c_r
       ) sq
 GROUP BY c_date
;

If you need to limit rows by some value in c, INNER JOIN each of the tables in the subquery to c separately, with identical WHERE clauses. You could alternately include the values from c in the subquery's SELECT list, and filter in the main query, but that's probably less efficient.


Determine the source of user permissions in a database I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment.

How can I track down what security group allowed such a behavior? I have the AD login used, as well as the target database.

I could go the long way around, looking into every single AD group and sub-group this user is a member of, and check for each one if it grants some kind of security in my SQL server. But it is going to take hours. And that would only cover one user out of a dozen.

I would like to track permissions (other than READ or VIEW DEFINITION), and their AD members. Either selecting an AD user and look through what they have access to, or the other way around, selecting a database and getting everyone that has access to to it (and how).

I can handle a PowerShell solution too if someone has some code to share (PS newbie).

Following is a partial solution that will return permissions assigned to objects in a given database to groups that a user is a member of either directly or indirectly.  The script loops through the groups the user is a member of, and gets all of the groups those groups are members of recursively to build a list that is used for a WHERE IN predicate.

The complexity of a full solution lies in the multiple ways permissions can be gained (through direct assignment to any number of groups, membership in roles, etc.)  For example, if you add an AD group to a custom role, then you have to check if that role has permissions.  Also this only shows assignments, not effective permissions, so if there are DENYs you have to take those into account to determine effective permissions.

You'll need the Active Directory PowerShell module installed for this, as well as the SQL Server client module.

$Username = "USERNAME"
$DomainName = "CONTOSO\"
$SqlServer = "SqlServerName"
$Database = "DatabaseName"

Function Get-ADGroupMemberOfRecursive{
Param([String]$Group)
    Begin{
        $Results += "'$DomainName" + $Group + "', "
    }
    Process{
            ForEach($Object in (get-adgroup $Group -properties memberof).memberof | get-adgroup | select name){
                Get-ADGroupMemberOfRecursive $Object.name
            }
    }
    End{
        $Results | Select -Unique | out-string
    }
}

ForEach($Object in (get-aduser $Username -properties memberof).memberof | get-adgroup){
    [string]$GroupList += Get-ADGroupMemberOfRecursive $Object.name
    }

$GroupList = $GroupList.substring(0, $GroupList.length - 4)

$Query = "SELECT `
        dp.class_desc `
       ,dp.permission_name `
       ,dp.state_desc `
       ,ObjectName = OBJECT_NAME(major_id) `
       ,GranteeName = grantee.name `
       ,GrantorName = grantor.name `
    FROM `
        sys.database_permissions dp `
        JOIN sys.database_principals grantee `
        ON dp.grantee_principal_id = grantee.principal_id `
        JOIN sys.database_principals grantor `
        ON dp.grantor_principal_id = grantor.principal_id `
    WHERE grantee.name IN (" + $GroupList + ")"

invoke-sqlcmd -serverinstance $SqlServer -Database $Database -Query $Query | ft


Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables? These are my tables:

CREATE TABLE uregister(
u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);

CREATE TABLE dregister(
d_id GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);

CREATE TABLE question(
q_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);

Can an email field refer to two different PRIMARY KEY (or UNIQUE) fields at a time? Under this schema, only one user or one domain can ask a given question?

You can do it in both SQL Server (see fiddle here) and IBM DB2 (fiddle):

I adapted your table create syntax slightly because it fails on SQL Server 2019 on the fiddle otherwise (I imagine it would fail on all versions of SQL Server). Your syntax works on IBM DB2.

The first table:

I had to change (SQL Server) the IDENTITY column slightly from u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, to u_id INT IDENTITY NOT NULL, otherwise the statement fails.

CREATE TABLE uregister(
u_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) UNIQUE NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);

And I had to change (both servers) the line u_mail VARCHAR(150) NOT NULL, to u_mail VARCHAR(150) UNIQUE NOT NULL, otherwise the following error occurs on trying to create the child table. This is the case with all other servers AFAIK - you have to have a child's parent field be either UNIQUE or a PRIMARY KEY (on its own, and not just part of a PK (if it's only part of a PK, it is not guaranteed to be unique in that table!).

Msg 1776 Level 16 State 0 Line 1
There are no primary or candidate keys in the referenced table 'uregister' that match the referencing column list in the foreign key 'FK__question__email__286302EC'.
Msg 1750 Level 16 State 1 Line 1
Could not create constraint or index. See previous errors.

If the PK or UNIQUE constraint(s) are not present for that field in the IBM DB2 fiddle, this is the error:

[IBM][CLI Driver][DB2/LINUXX8664] SQL0573N  A column list specified in the references
clause of constraint "EMAIL..." does not identify a unique constraint of the parent
table or nickname "FIDDLE_UHGEYTFVKRUDCQDSXLQL.UREGISTER".  SQLSTATE=42890 SQLCODE=-573

Same thing for the dregister table!

CREATE TABLE dregister(
d_id INT IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) UNIQUE NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);

Then, the two foreign keys are created without problem (as per the fiddle).

CREATE TABLE question(
q_id INT IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);


Is it possible to log MySQL variable changes? I'm using Percona MySQL server 5.7 and after a while my max_allowed_packet change to 1024 bytes and I don't know who or what application changes it.

I've set it in config file and after restart it sets fine:
[mysqld]
max_allowed_packet=100M

But after a while (few days later), my application throws error while inserting long text, it changed to 1024 bytes. I check it with:
SHOW variable LIKE 'max_allowed_packet'

Is it possible to log only variable changes to know who changed it when?

You can log changes that are made on-the-fly using the audit plugin.  You can get that from here https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html - variable changes are logged as SET commands.

The max_allowed_packet variable is set on both the client and the server - so in the [client] section and the [mysqld] section of the ini files (my.cnf for linux or my.ini for windows).

The variable may also be getting set in your application API, so you would need to check and change that too.

Finally there is an observation in the book High Performance MySQL that max_allowed_packet also controls the maximum size of a user-defined variable. These can be truncated or set to NULL if that max value is exceeded.


how to remove 6 months old data automatically from MYSQL innodb table I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older than 6 months. I know I can do it using CRON jobs but I want to use MYSQL way to delete older data automatically. I read about PARTITION also and My question is Can I do it using PARTITION or I will have to find some alternative way ?

The Event Scheduler allows to execute regular events according to a schedule. There is detailed example in my post on the Stack Overflow, you just need to change time interval value from 24 hours to 6 months.

Firstly, make sure the Event Scheduler is enabled. To enable it use

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time and delete old records. For example

CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  DELETE FROM MyTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW
  INSERT INTO LogTable (MainID, Created)
  VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table rows that was created before specific time and delete corresponding records.

delimiter |
CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 6 MONTH;
    DELETE FROM MainTable
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable
    WHERE LogTable.Created < MaxTime;
  END |
delimiter ;


Can't find source code for building mysql I'm trying to follow the instructions for building MySQL from source. They guide me to a link where I can download the latest GA. I end up at the downloads page, where I can download mysql-5.6.19-osx10.7-x86_64.tar.gz for my mac, and then untar and unzip.

All good so far. But when I try to cmake . per the instructions, I get an error saying that it can't find CMakeLists.txt. Poking around looking for that, I've discovered that the download doesn't come with any actual source code except .h files. In fact, the download looks like an already-built setup with binaries such as mysqld already there.

I am probably missing something obvious, but I can't figure out where I'm supposed to get the full source code and the makefile, if not from the links provided under the title, "Installing MySQL Using a Standard Source Distribution". Anyone have an idea?

Funny - I'm just doing this myself now (assuming Linux (as per Alex Kuzminsky - I use Xubuntu - no heavy GUI desktop).

Get the source (all source downloads here (select from dropdown), Linux source here).

Run (depends on your Linux distro)

sudo apt-get install cmake
sudo apt-get install bison flex
sudo apt-get install libaio1
sudo apt-get install g++ (Ooops!)
sudo apt-get install libncurses5-dev

This was all I needed to do after a fresh (Oracle Virtual Box) install of Xubuntu 14.04 LTS.

Then, what I do is follow the instructions here.

Create a directory "sandbox" beside mysql-5.6.16.

cmake ../mysql-5.6.19
make
make package

then install the mysql-5.6.19-linux_64.tar.gz package (or whatever it's called)

Works nicely for those of use who want to control things and even run several MySQL servers on the same machine using different ports for example.


Session close unexpectedly destroying temporary table I am using Temporary table to delete multiple row at the same time in the database. Every time I try to use temporary table on a specific Sql-Server-2016 it fails.

I did try to reproduce the issue on a different instance and it is not happening.

The trace from the server showing the issue shows that just after successfully creating the Temporary Table the session is closed. The same session is then used to try to insert data in the Temp table but it failed since the Temp table only last as long as the session that created it. The Connection to the database is not pooled (and it says so in the traces too).

I compared the configuration between a fresh installation and the server showing the issue and all the configuration from sys.configurations are the same.

Here are the traces I collected. Every Audit Login have the same parameters command so I will only put put it once.

Audit Login
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SQL:BatchStarting   CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
SQL:BatchCompleted  CREATE TABLE ##TempA368AEA69A2F4E949CE8DC668FD7F86F (...)
Audit Logout

Audit Login
Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchStarting   INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F
VALUES(...)

Exception   Invalid object name '##TempA368AEA69A2F4E949CE8DC668FD7F86F'
SQL:BatchCompleted  INSERT INTO ##TempA368AEA69A2F4E949CE8DC668FD7F86F
VALUES(...)
Audit Logout

My question, is there any reason a SQL-Instance closes a session without the application disposing it/timeout

As noted in the comments, if your session disconnects for any reason, you're going to have problems with even a global temp table disappearing.

Here's a neat workaround: create real user tables in TempDB. This works perfectly fine:

USE tempdb;
GO
CREATE TABLE dbo.persistent(ID INT);
GO

And then you can access that table from any session (via tempdb.dbo.persistent.)

I'm not saying this is a good idea - it's a bad one in the same way that global temp tables are a bad idea, like contention across multiple sessions, having problems when you have an AG failover or a service restart, or filling up your TempDB drives - but as long as you're trying to use global temp tables, this would be a workaround.


SQL Server Monitoring Tools Can anyone recommend a good SQL server Monitoring Tool?
One which will provide info on, server space usage, CPU usage , expensive queries run etc.

I've voted to close this, because it's essentially a poll question, and there isn't a possible "right answer." You should be evaluating the vendors' offerings to see which product best suits your requirements and budget. Nobody can tell you which product will do that best.

The major vendors you want to investigate are:

SQL Sentry
Quest FogLight / SpotLight
Confio Ignite
RedGate SQL Monitor
Microsoft System Center Operations Manager
Idera Diagnostic Manager

They all have evaluation versions so you can test them out. In addition to feature sets you'll want to baseline your workloads and check the additional overhead each monitoring solution adds - no monitoring is 100% free.


Mysql Innodb cluster does not show mysql server instances in Mysql shell I followed docker innodb cluster manual method to create an innodb cluster. Now in mysql server instance, by running select * from performance_schema.replication_group_members, I can see the group contains 3 nodes:

However, mysql shell gives only 1 node in the cluster:

Why they are not identical?  I think the replication group is the same thing as an innodb cluster in this example.

The unofficial docker innodb cluster manual does not use MySQL Shell to set up and configure the MySQL InnoDB cluster.

If you want/need to use the MySQL Shell to manage your InnoDB cluster then you must "adopt" the running Group Replication group. To do so, use the adoptFromGr option of dba.createCluster():

dba.createCluster("testcluster", {adoptFromGr: true})

For more information, please consult the official userguide:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

Cheers,

Miguel


Bandwidth comparison between log shipping and transactional replication Which technique uses more network bandwidth:

Log shipping
Transactional Replication

Can some one share any benchmarks for the same ?

What would be the Memory and I/O impact on the Primary server when we use any one of the technique ?

Thanks,

Piyush Patel

It depends on your workload.  As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour.  Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour.  Then setup Transactional Replication and use Peformance Monitor to log the network counters for 1 hour.  The network bandwidth that Log Shipping and Transactional Replication consumes is the difference between the collections and the baseline.  The same can be done for memory and I/O.


Data warehouse: using day number in Epoch in a date dimension In Chapter 2 Retail Sales of Ralph Kimball's The Data Warehouse Toolkit, the author describes a date dimension with three fields which appear totally alien to me:

Date Key (PK)
Date
Full Date Description
Day of Week
**Day Number in Epoch**
**Week Number in Epoch**
**Month Number in Epoch**
Day Number in Calendar Month
Day Number in Calendar Year
...

I understand that an Epoch is the "instance in time chosen as a particular era" and that the current standard epoch for J2000.0 starts on January 1, 2000. But I don't see how these numbers would be helpful in a date dimension or a cube used for getting information about retail sales.

Are numbers in Epoch typically used in Date Dimensions?
What Epoch would be used for a modern day business?
How would a user slice data based on Epoch and why?

Epoch is also a synonym for a date or time. Occasionally used in database models as an alternative to the ubiquitous 'timestamp' as a column name, the two are essentially interchangeable.

Edit: Looking at your list with fresh eyes (and without getting the book from the cupboard it's hiding in) I expect an epoch in this instance would be a period such as a financial quarter. For example, if a the quarter ran from 1st April to 30th June, 'Day number in Epoch' for 1st May would be 32.


postgresql multicolumn partitioning Postgres 11
I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.
The reason is that:

I want to prune months that are not longer updated.
I need all the data to be partitioned by list of ids for further joins with and other partitioned table listings, that is partitined by the same list of keys.

The documentation says:

Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing.

Thus, if I get it right, this means that my taks can not be done with Declarative partitioning, but probably can be done using Inheritance.
So, I try to reproduce the given example with my modification
CREATE TABLE measurement (
                city_id         int not null,
                logdate         date not null,
                peaktemp        int,
                unitsales       int
            ) PARTITION BY RANGE (logdate); # this already does not accept mixed types of partitioning

than I try to create a partition with my mixed rules of partitining:
CREATE TABLE measurement_y2006m02 (
                CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01'
                        AND paektamp >0 AND peaktemp <=10)
                      ) INHERITS (measurement);

this gives:
cannot inherit from partitioned table "measurement"

Thank you!

A partition can again be a partitioned table, so using subpartitions you can partition a table in two different ways:
CREATE TABLE calendar (
   id bigint NOT NULL,
   day date NOT NULL
) PARTITION BY RANGE (day);

CREATE TABLE calendar_2020 PARTITION OF calendar
   FOR VALUES FROM ('2020-01-01') TO ('2021-01-01') PARTITION BY LIST (id);

CREATE TABLE calendar_2020_a
   PARTITION OF calendar_2020 FOR VALUES IN (1, 2, 42);

Make sure to test carefully if you get the desired performance benefits; you probably have to turn on enable_partitionwise_join.
Also make sure that you use a recent PostgreSQL version (≥ x12) and that you don't end up with thousands of partitions.


Does The Stored Procedure sp_removedbreplication Remove Rowguids In merge replication if I run sp_removedbreplication will that remove rowguids or not?

Will it take into account the preserve_rowguidcol values in the sysmergearticles table?

Try this:

UPDATE dbo.sysmergearticles SET preserve_rowguidcol = 1 WHERE type = 10 and name = 'MyTable'

It will preserve the rowguid column for the article.


ODP Connect String with Oracle Wallet I'm trying to use ODP.NET in PowerShell to access an Oracle DB. I want to use my existing Oracle Wallet for the connection credentials but I find no hints about how to specify the connect string with a Wallet. I've tried a few variations but I'm just not getting it correctly specified.
SQLPlus would use /@
What is it for ODP.NET?
Searching for the solution, I found that ODP needs to be configured to find the wallet. I specified the following as app.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="4.121.2.0">
      <settings>
        <setting name="WALLET_LOCATION" value="(SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = c:\Users\xeaa2ll\Oracle\Wallet\)))"/>
        <setting name="SQLNET.WALLET_OVERRIDE" value="true"/>
        <setting name="TNS_ADMIN" value="c:\Users\xeaa2ll\ora\product\11.2.0\client_1\network\admin\"/>
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

The code I'm testing is
$configPath = "app.config"
[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $configPath)

Add-Type -Path "c:\Users\xeaa2ll\ora\product\12.1.0\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$tns = 'OODBAP01'
$uid = 'xeaa2ll'
$pw  = 'xx'
$query = "select 1 from dual"
$connectionString = 'Data Source=/@oodbap01.bnymellon.net:1524:' + $tns  # fails
$connectionString = 'Data Source=/@' + $tns                              # fails
$connectionString = 'Data Source=' + $tns                                # fails
$connectionString = 'User Id = ' + $uid + '; Password = ' + $pw + '; Data Source=' + $tns
try {
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.open()
}
catch
{
    Write-Error ("Can't open connection: {0}`n{1}" -f `
    $connection.ConnectionString, $_.Exception.ToString())
}
finally {
    $command=$connection.CreateCommand()
    $command.CommandText=$query
    $reader=$command.ExecuteReader()
    while ($reader.Read()) {
        $val = $reader.GetValue(0)
        write-host $val
    }
}
if ($connection.State -eq 'Open') { $connection.close() }

This works for me:

"User Id=/;Data Source=DBNAME"

Please note that many people (including myself) are having issues getting an Oracle Wallet to work with the 12c managed driver.  You may need to enable SSL client authentication.  See the following thread on the Oracle Community site for details.

https://community.oracle.com/thread/2590420

As an aside, I'd recommend not overriding the app.config for powershell.exe, and instead using the machine.config.  If you don't see the entries in there to edit, run the configure.bat script found in ORACLE_HOME\odp.net\managed\(x64|x86)\

ise ([System.Configuration.ConfigurationManager]::OpenMachineConfiguration().FilePath)

And then in your Powershell script, you can also load the driver without having to know the path to the DLL:

[void][reflection.assembly]::Load("Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342")


Is the order of records in a VALUES list well-defined (postgres)? Postgres 9.4 introduced the WITH ORDINALITY clause which adds an ordinality column to a table-like expression, somewhat similar to ROW_NUMBER() OVER (). Apparently, this is used to provide ranks for set-returning functions like unnest(<array>). My question is, is the order of rows defined for VALUES lists? I would like very much for this to be the case:
 SELECT *, ROW_NUMBER() OVER () FROM (VALUES ('a'), ('b'), ('c')) xs(a);
 a | row_number
---+------------
 a |          1
 b |          2
 c |          3

EDIT: Fixed to use ROW_NUMBER instead of WITH ORDINALITY (which is syntactically incompatible with a values list).

VALUES is treated like SELECT. The SELECT documentation says:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Your version of Postgres happens to return the three rows in the order you have written them. While unlikely, this could change in the future.


How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database

local_SID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
    (CONNECT_DATA = (SID = remote_SID))
  )

and then use that in sqlplus

sqlplus user/pass@local_SID

However, in my circumstances modifying the local tnsnames is not possible. Is it possible to connect to a remote database just by using sqlplus argument without having to change tnsnames? Something like

sqlplus user/pass@remote_SID@hostname.network ;( I know, this one is not valid)

sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))

Maybe, and this might be dependant on the command line environment you're using, you need to quote the string, something like

 sqlplus "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"

or

 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'


Recover Wrong encoded string values in MySQL database I have a database in which I store the alt and title values of the images. Unfortunately the program which inserts these values hasn't been connecting to the MySQL database with a UTF-8 link and the data it has inserted to the database is not in Unicode. I have fixed that but I was wondering if there is some way to retrieve the original values from the database and convert them to Unicod

A possible solution:

Dump your table data with mysqldump or any other favorite tool
Fix encoding in dump file using one of the utilities (there are plenty out there)
Load data back


How should deletions be handled in the database? I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are actually deleting the record in question and storing the changes in a separate audit table, or not deleting the record and using a boolean "deleted" column to mark it as deleted. The latter solution would require additional application logic to ignore the "deleted" records under normal circumstances, but would make it much easier to implement recovering the records on the application side.

Yeah, I would definitely go for the second option, but I would add one more field a date field.

So you add :

delete       boolean
delete_date  timestamp

It would let you give a time for the undelete action.

If time is less than an hour one can undelete.

To really delete the entry deleted just create a stored procedure that will clean every entry with delete set to true and time greater than one hour and put it as a cron tab that runs every 24hours

The hour is just an example.


Does relational design discourage splitting tables unnecessarily? It's been a long time since I studied relational design, but I had a vague memory that it encourages not splitting a table unnecessarily. For instance, given the functional dependencies
K -> A
K -> B
K -> C

my assumption was that the "best" schema is just {KABC} and not something like {KAB, KC} or even {KA, KB, KC}. At least in practice that is how I've seen database designers implement the table.
However, a quick refresher on Wikipedia indicates that the normalization formalism

doesn't make any statement in the direction of obtaining a "minimal schema",
6NF would even require {KA, KB, KC}. Since 6NF implies the other normal forms, it implies that it is even impossible for them to make such a minimal requirement.

I'm a bit confused that I got this wrong all the time. Does the notion of "obtaining a minimal number of tables" really play no role in formal relational design, and it is just common practice?

The “Normal Forms”  are narrowly defined in terms of eliminating redundant data and “update anomalies”.  Whether fixing other schema design problems counts as “Normalization” can be debated, but in general parlance Normalization just means ensuring the database complies with some Normal Form.


why the “size” field of a collection stat is larger than “storageSize” or totalSize of a collection in mongodb. “count” : 1552400 "count" : 1552400, "size" : 1571028800.0, "avgObjSize" : 1012, "storageSize" : 229552128,

from my understanding of the above stats, "avgObjSize" gives the size of a document in the collection and "size" gives the sum size of all the documents(no index size included) in the collection. but when i computed the total size of a collection 264282112 bytes. this is less than "size" 1571028800 in bytes. storage engine i am using here is wiredTiger and block compresses "snappy " is enabled.

can anyone please explain when the document stored to disk, does it store as compressed or does it store with the size of "avgObjSize" (in my case 1012 bytes).

As per Mongodb documentation:

  The total size in memory of all records in a collection. This value
  does not include the record header, which is 16 bytes per record, but
  does include the record’s padding. Additionally size does not include
  the size of any indexes associated with the collection, which the
  totalIndexSize field reports.

You have :

The number of objects or documents in this collection = 1552400
The average size of an object in the collection (plus any
padding)=1012

1552400*1012=1589657600

Output shows 1571028800 which is very close to what is expected.

How do you get 264282112?

Documents are compressed when stored in disk but storageSize shows allocated space.

  The total amount of storage allocated to this collection for document
  storage. The scale argument affects this value.

  storageSize does not include index size.


SQL Server: migrate one table to discrete DB server SQL Server 2012 Standard

We have one table that is highly transactional (tons of inserts, no updates), that contains a BLOB object of 5kb-300kb per row. The whole table is about 31GB in size (table and indexes), and has 450K rows. There is a primary key (it is a clustered table), and there is a record creation timestamp col.

We want to move table to its own db server. We have already updated the application to reference this table via its own ADO connection string. Yes, we can take the app offline during a maint window to do this.

What is the best strategy to migrate a single table to another DB server?

Options we have considered:
 - Log shipping , but this replicates whole db... if we could log ship one table, we would do it for sure, we like log shipping
 -  Transactional replication... we do NOT like transactional replication because it is such a monster.

One thought is to bring the app down briefly, and do a tsql command to

insert into <new db server.db.table> select from <source db server and table>

Can that work? Can we do a tsql command across discrete db servers?
What other method we should consider?

If it is one time, then you should use bcp out and bulk insert .. much better and faster than the linked server approach that you are thinking, since in the linked server approach if you run the query on the source server then you are pushing the data from source server to destination server which is an expensive operation. Alternatively, you can use SSIS as well.

If you need to keep the data in the new server sync with the old one, then your only option is to use transactional replication.

Also, refer to my answer on : Which one is more efficient: select from linked server or insert into linked server?


Is there a Storage-Engine/DB that logs all changes and allows selective rollback of specific changes? Is there a storage-engine or database that naturally support this use-case:

1) Key-Value storage. Small Fixed Length Key, and value within [1KB, 64KB]
2) Access is read-mostly
3) Latency more important than throughput.
4) Use discs for values; not a memory-database, but keys could be kept in RAM.
5) Transactions (that involve several values) are NOT required
6) It should be possible to roll-back to a point-in-time (say, 1 week ago max).
7) All changes are attributed to some specific entity (a user)
8) It should be possible to roll-back changes done by a specific group of users
   within a certain time interval.
9) Rollback happens rarely, so transaction logs should not be kept in RAM.
10) Online backups are possible.
11) I should be directly or indirectly accessible from a JVM.

While I know several engines / DB that generally fit, point 7 and 8 are more problematic.

What would fit this use case best?

[EDIT] This is my first "independent"/private project and I don't have much money to invest for software. Also I'm not sure yet if I will charge for it, or open-source it when it is done, so I would prefer a free storage, or at least one with a free version. Here a clarification of some questions raised in the first answer.

3) I'm sold to SSD already.
4) I said "use disc" simply because the data will not all fit in the RAM I can
   afford. Also I suspect I will have a lot of "cold" data, that gets created once
   and never accessed again, so it would seem a waste to keep it all in RAM, as
   would be the case with VoltDB, for example.
7) Adding explicitly the UserID is not problem in most database. I just thought that
   some storage system might have some kind of "special" ID that makes rollback
   easier.
8) This is where the real problem is. Writing a traditional "undo/rollback" by hand
   is time-consuming and error-prone. I know I can do all the rest with most DB.
   What I'm looking for is one that implements the undo/rollback for me, given some
   transaction ID or similar. And most importantly, without undoing other
   independent transactions that happened before or after.
9) I think redis does that; not sure.
10) I was (implicitly) thinking about a free one, where it's not always a given.

Here's the problem that I'm struggling with, and that I think you will find when attempting to find this type of system. (Particularly when it goes to requirements 7 & 8.)

Let's say that you have User A who changes a value from 1 to 2.  Then User B changes it from 2 to 26.  If you attempt to rollback User A's value, what do you roll it back to?  Do you change it back to 1 or do you leave it at 26?  Do you do some strange business logic and make it 25?

There are several possible scenarios, but ultimately, there is no generic way of handling this type of data.  If you don't roll back, then you fail to satisfy Rule 8.  If you do roll back, then you fail to satisfy Rule 7 (allowing ONLY changes from a certain user to roll back).  If you do some crazy math rules, then each rule will have to be very specific to the column that modify.

I don't think that there are any databases that can handle this on a general basis.  It is possible to roll back on a point-in-time basis, but that completely breaks Rule 7 (allowing you to roll back changes from a certain group).

Outside of a custom-written, specific use application, I don't see how it's possible to implement 7 & 8 on a generic database system.


How to fine-tune Postgres for IO usage? I have a Postgres database I am hosting in a quad core server with 8 GB of RAM.

At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When I do "top", most of my cores have very high IO wait % - 50% - 70%.

I have a feeling I need to fine-tune some parameters in my Postgres. But which ones would effect IO usage the most? Would adding more cores help in reducing IO usage?

The single best thing you can do to reduce disk I/O load is to ensure that your database fits comfortably in RAM after allocating RAM to everything else running on the system.  If this is the case, your disk I/O will be primarily WAL flushes on commit, which is mostly sequential I/O.

The second thing you can do is address the shared_buffers and effective_cache_size settings.  If the system is exclusively running PostgreSQL I like to set shared_buffers low, and effective_cache_size high and adjust from there.  If the system is running other programs too, shared_buffers should be set much higher because it reserves RAM exclusively for PostgreSQL.  Note that the OS disk cache is often faster than the PostgreSQL cache, but the Pg cache is more full-featured so there is a real balance.  Measure, test, repeat.

A third setting to be aware of is work_mem.  This setting should be adjusted only with care, because it is per operation.  If you set it too low, joins and sorts will write to disk.  If you set it too high, it will squeeze out memory that could be used for caching data.  Again, look for query performance patterns and move forward.


What is the difference between select from function and select function I am doing a test with generate_series function on Postgres v.9.6 on Ubuntu 16.04. So, I get a curiosity about loops (loops=100000) when calling that function as below.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT a FROM generate_series(1,10));
Aggregate  (cost=627262.00..627262.01 rows=1 width=8) (actual time=60.534..60.534 rows=1 loops=1)
  ->  Seq Scan on t5  (cost=0.00..627137.00 rows=50000 width=0) (actual time=0.048..50.267 rows=100000 loops=1)
        Filter: (SubPlan 1)
        SubPlan 1
          ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.000..0.000 rows=1 loops=100000)
Planning time: 0.161 ms
Execution time: 60.599 ms

However, If I change the query. The loop and result will change.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series(1,10) a);
Aggregate  (cost=1488.51..1488.52 rows=1 width=8) (actual time=0.050..0.050 rows=1 loops=1)
  ->  Nested Loop  (cost=17.80..1363.51 rows=50000 width=0) (actual time=0.025..0.046 rows=10 loops=1)
        ->  HashAggregate  (cost=17.51..19.51 rows=200 width=4) (actual time=0.012..0.014 rows=10 loops=1)
              Group Key: generate_series(1, 10)
              ->  Result  (cost=0.00..5.01 rows=1000 width=4) (actual time=0.004..0.007 rows=10 loops=1)
        ->  Index Only Scan using t5_pkey on t5  (cost=0.29..6.71 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=10)
              Index Cond: (id = (generate_series(1, 10)))
              Heap Fetches: 10
Planning time: 0.124 ms
Execution time: 0.086 ms

Here's script to create table

CREATE TABLE t5 ( id serial primary key, a int, b text);
INSERT INTO t5 (a,b) SELECT a, 'test_' || a FROM generate_series(1, 100000) a;

It's not the FROM that's causing the different query plans. It's the bad aliasing.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series(1,10));

And

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT generate_series FROM generate_series(1,10));

will return the same plan. As will this,

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT x FROM generate_series(1,10) AS gs(x));

However, this won't.

EXPLAIN ANALYZE
  SELECT count(*)
  FROM t5
  WHERE id in (SELECT a FROM generate_series(1,10));

Notice: you forget to alias the generate_series to a Now you're essentially running a correlated subquery. It's not just more work, but it's returning the entire table and not just 10 rows.


Which database is best for storing mouse movement data for analytic stuff? Problem

I want to track the user behavior on website. I am tracking the mouse movement and after tracking certain number of mouse movements, i am planning to send this data to my server, where i will code an API which stores this data into database.

The data will be like an array and each cell of array have this:

{x: some-value, y: some-value, some-other-properties:{}}

My solutions

Initially i was thinking to store this data into SQL database. Then i thought this data is more like a non-structured data, it will also grow rapidly with time as i have many websites in future and number of users will grow very fast,  i should store it in MongoDB (NOSQL database). The other option in my mind is influxDB which is used to store time-series data. Now, we see the mouse-movement is more like a time-series data. Hence i am just confused and need some expert opinion.

I want to store data efficiently. This is my main concern.

Which database is best for storing mouse movement data for analytic
  stuff?

As per MongoDB BOL Here

MongoDB Compass is the GUI for MongoDB. Visually explore your data. Run ad hoc queries in seconds. Interact with your data with full CRUD(Create,Read,Update,Delete) functionality. View and optimize your query performance. Available on Linux, Mac, or Windows. Compass empowers you to make smarter decisions about indexing, document validation, and more.

As with the help of MongoDB Compass you can

Visualize and explore
Insert, modify, and delete
Debug and optimize

Know your data with built-in schema visualization

MongoDB Compass analyzes your documents and displays rich structures within your collections through an intuitive GUI. It allows you to quickly visualize and explore your schema to understand the frequency, types and ranges of fields in your data set.

Get immediate insight into server status and query performance

Real-time server statistics let you view key server metrics and database operations. Drill down into database operations easily and understand your most active collections.

A better approach to CRUD makes it easier to interact with your data

Modify existing documents with greater confidence using the intuitive visual editor, or insert new documents and clone or delete existing ones in just a few clicks.

Understand performance issues with visual explain plans

Know how queries are running through an easy-to-understand GUI that helps you identify and resolve performance issues.

View utilization and manage your indexes

Understand the type and size of your indexes, their utilization and special properties. Add and remove indexes at the click of a button.

A simpler way to validate your data

Create and modify rules that validate your data using a simple point and click interface. CRUD support lets you fix data quality issues easily in individual documents.

Visualize, understand, and work with your geospatial data

Point and click to construct sophisticated queries, execute them with the push of a button and Compass will display your results both graphically and as sets of JSON documents.

As per your said database store structure , it's a format of {key : Value} pair. which is JSON format is fully supported

  {x: some-value, y: some-value, some-other-properties:{}}

For Your Further ref For Understanding the JSON Format


MySQL Replication Master push to Slave I have two servers that need to be synced. One is online and has a public IP address. The other is internal and has no public IP address - nor is there a way to get it a public IP so that's off the table.

I'd like to set up replication between the two servers through MySQL but as I understand it, the MySQL slave needs to be able to connect to the master (i.e. the master doesn't just simply push data out) and that's a problem since there's no public IP or interface to connect to.

Is there any way to replicate and simply tell the master to push data to the slave (which it can access)? Alternatively, is there some other solution? I have considered a mysqldump periodically but the data is going to grow very quickly resulting in too-large dump files.

Make sure that you are able to connect to Remote MySQL server (Online server) from a Slave server machine.
You can verify by using telnet command

# telnet <online server IP><space><MySQL port>

Now follow the replication procedures mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

For Database synchronization refer existing answer here:

https://stackoverflow.com/questions/7946540/how-do-i-sync-2-mysql-databases


MySQL shows previously deleted data We're running MySQL on Azure. We have three very small databases.

The main database is POI and it hold 11 static records. During the day a few records may be added and deleted again within 1-2 hours.

We typically have 5-10 mobile users who poll the POI-DB for all 11-15 records every 60 seconds.

Several times every day, we do get some corrupt data from our SQL query. We often see two records which were deleted from the database two months ago.
What can make such a simple database become unreliable?

Server: eu-cdbr-azure-north-d.cloudapp.net via TCP/IP
Server type: MySQL
Server version: 5.5.45-log - MySQL Community Server (GPL)
Protocol version: 10
PHP extension: mysqli
PHP version: 5.4.42

--edit

I've changed our database to InnoDB. I still get the ghost-data.
The ghost data is not visible, when I use phpMyAdmin to view the POI-DB.

I started to measure the database connection time and realized that they sometimes are 1 - 5 seconds, so I's very likely to have concurrent requests, when the connection request is stocked.

Do I need to do some special tricks to handle concurrency?
The long connection times are probably caused by reverse DNS lookup. We will disable that now.

"Transaction Isolation Modes" let you see data even after it has been deleted.  Some modes effectively "take a snapshot of all data", then let you see only that data until you COMMIT.

I doubt if that is what is causing what you see.  It would be very rare.  It would need longer timeouts than are reasonable.  Have you changed any from their defaults?

Note that I am also saying that you had to start a transaction and and not finish it for a long time; plus sometime in the middle a DELETE occurred from a another connection.

Look at the VARIABLES tx_isolation and autocommit and %timeout%.  Show them to us, plus sketch out the transactions you have.

Another answer:  You have a caching layer outside MySQL that is causing the problem.


MySQL: Show Unique key definition by name I get the following error:

ERROR 1062 (23000) at line 1901: Duplicate entry '3-3820-50-0' for key
 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID'

How can I find out which table and columns this is referring to?

This should do it:

select table_schema, table_name, column_name
from information_schema.key_column_usage
where constraint_name = 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID'
order by ordinal_position


PostgreSQL: Using schema and table names from other tables? Consider the situation where I have all my schema names in one table and all my table names in another table.

Is it possible to do something like in the following (pseudo-code)?

SELECT value FROM (SELECT schema_name FROM schemas).(SELECT table_name FROM tables)

Or do I have to break up the query into three SELECT's?

You need dynamic SQL - perhaps something like this:

create role stack;
create schema authorization stack;
set role stack;

create or replace function f(p_schema in text, p_table in text)
                           returns setof integer language plpgsql immutable as $$
begin
  return query execute 'select value from '||p_schema||'.'||p_table;
end;$$;

create table t1(value integer);
insert into t1(value) values (1);
insert into t1(value) values (2);

create table t2(value integer);
insert into t2(value) values (1);

create table schemas(schema_name text);
insert into schemas(schema_name) values ('stack');

create table tables(table_name text);
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');
insert into tables(table_name) values ('t1');
insert into tables(table_name) values ('t2');

select f(schema_name, table_name) from schemas cross join tables;
 f
---
 1
 2
 1
(3 rows)

I'm assuming that every table is present in every schema as the question implies


Oracle 11g: increasing buffer cache with AMM On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4 GB will half the physical reads, and we have 4 GB to give.

How must we do this? By increasing memory_target and letting Oracle grab more memory for the buffer cache? Or by explicitly setting a value for db_cache_size (which now is zero)?

You can set the value for db_cache_size, and the value you set for it will be considered the minimum value it can be with AMM enabled.


Leaf nodes for averages I have the following MDX tuple calculation for my KPI in Dashboard Designer:

AVG([Person].[Person].children,[Measures].[Hours])

This works perfectly when, for instance, I view it by Team name.

However, when I view it by the [Person] it's returning no values. Does AVG not work when you're looking directly at the leaf nodes or something? Or is there something else I'm doing wrong?

OK, so I found AN answer eventually, though I'd quite like a more elegant solution.

What I'm doing is performing an iif statement to check whether there are any [Person].[Person] children available in the current context. If there aren't, then assume we're looking at this by individual [Person].[Person] members and use currentmember instead.

The tuple formula looks like this:

AVG(iif(Count([Person].[Person].children, EXCLUDEEMPTY) = 0,
    [Person].[Person].currentmember, [Person].[Person].children),
    [Measures].[Hours])


PostgreSQL queries from external IP causing high CPU load. Have I been hacked? Since I few days I have noticed that on my Atlassian Software VM (running only Jira, BitBucket and Bamboo) PostgreSQL is causing very high load as seen in htop.

I then checked with

SELECT * FROM pg_stat_activity;

what is running on my system and found two weird entries below.

12405 | postgres    | 15526 |       10 | postgres   |                  | 114.86.94.107 |                 |       56033 | 2017-11-17 15:40:03.745147+00 | 2017-11-17 15:40:04.954341+00 | 2017-11-17 15:40:04.954341+00 | 2017-11-17 15:40:04.954343+00 |                 |            | active |             |      3202367 | select Fun020001 ('./x4000021328 &')
12405 | postgres    | 15539 |       10 | postgres   |                  | 114.86.94.107 |                 |       61953 | 2017-11-17 15:40:12.560015+00 | 2017-11-17 15:40:13.374722+00 | 2017-11-17 15:40:13.374722+00 | 2017-11-17 15:40:13.374724+00 |                 |            | active |             |      3202368 | select Fun020001 ('./ps3991599979 &')

The queries they're running seem weird to me

select Fun020001 ('./x4000021328 &')
select Fun020001 ('./ps3991599979 &')

I then found files named like the two above in

/var/lib/postgresql/9.6/main/

-rwxrwxrwx 1 postgres postgres    14480 Nov 17 15:39 ps3991599979
-rwxrwxrwx 1 postgres postgres   496464 Nov 17 15:40 x4000021328

Do I assume correctly that my machine has been hacked? For now I have disabled remote connections and deleted the binaries. I also turned off the machine. Is there anything else I can do to prevent further abuse of my VM?

After using clamscan to investigate further it found another backdoor installed.

/var/lib/postgresql/9.6/main/base/12405/2613: Win.Trojan.MSShellcode-6360729-0 FOUND

I also found out that PostgreSQL 9.6 has a lot of vulnerabilties, but apt-get wouldn't find a newer version than 9.6. To upgrade to Version 10 you'll need to add

 deb http://apt.postgresql.org/pub/repos/apt/ sid-pgdg-testing main 10

to your sources in order to upgrade via

 apt-get install postgresql-10


migrate over clause from mssql to mysql How to write the following code into mysql,

    SELECT SUM(COUNT(col1)) over (PARTITION by col2) as summation
    FROM TABLE
    GROUP BY col2

The exact equivalent is:

SELECT COUNT(col1) as summation
FROM TABLE
GROUP BY col2

The SUM does nothing as there is only one aggregate based row per each partition of the OVER clause therefore it can be simplified to a simple GROUP BY instead.


What is the 131072 in the call to lo_open() ByteA to BLOB conversions typically make a call to lo_open(),

CREATE OR REPLACE FUNCTION make_lo(bytea)
RETURNS oid AS $$
DECLARE
  loid oid;
  fd integer;
  bytes integer;
BEGIN
  loid := lo_creat(-1);
  fd := lo_open(loid, 131072); -- <<< --==HERE==--
  bytes := lowrite(fd, $1);
  IF (bytes != LENGTH($1)) THEN
    RAISE EXCEPTION 'Not all data copied to blob';
  END IF;
  PERFORM lo_close(fd);
  RETURN loid;
END;
$$ LANGUAGE plpgsql STRICT;

In the above what is

fd := lo_open(loid, 131072);

You can see others asking this same question here,

  The number 131072 is some flag I couldn't find what means, and 999999999 is the max read size, but I could read the value anyway.

This variable is called the "mode parameter" and it's the int representation of INV_READ (x'20000')

-- lo_open(lobjId oid, mode integer) returns integer
-- The mode parameter to lo_open uses two constants:
--   INV_READ  = 0x20000
--   INV_WRITE = 0x40000

You can see the integer representation like this,

SELECT CAST( x'20000' AS integer);
  int4
--------
 131072
(1 row)

Personally, I would rather use x'20000' which I find to be more explicit. You can see INV_READ mentioned in a comment in largeobject.source, and you can see it defined it in the code at libpq-fs.h.

In the event you ever see 262144 that's the numified representation of INV_WRITE (0x40000).


Why are queries parsed in such a way that disallows the use of column aliases in most clauses? While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.

The MSDN docs say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even a suggestion to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).

As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.

It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?

Summary

There's no logical reason it couldn't be done, but the benefit is small and there are some pitfalls that may not be immediately apparent.

Research Results

I did some research and found some good information. The following is a direct quote from a reliable primary source (that wishes to remain anonymous) at 2012-08-09 17:49 GMT:

  When SQL was first invented, it had no aliases in the SELECT clause.
  This was a serious shortcoming that was corrected when the language
  was standardized by ANSI in about 1986.

  The language was intended to be "non-procedural"--in other words, to
  describe the data that you want without specifying how to find it. So,
  as far as I know, there's no reason why an SQL implementation couldn't
  parse the whole query before processing it, and allow aliases to be
  defined anywhere and used everywhere. For example, I don't see any
  reason why the following query shouldn't be valid:

select name, salary + bonus as pay
from employee
where pay > 100000

  Although I think this is a reasonable query, some SQL-based systems
  may introduce restrictions on the use of aliases for some
  implementation-related reason. I'm not surprised to hear that SQL
  Server does this.

I am interested in further research into the SQL-86 standard and why modern DBMSes don't support alias reuse, but haven't had the time to get very far with it yet. For starters, I don't know where to get the documentation or how to find out who exactly made up the committee. Can anyone help out? I also would like to know more about the original Sybase product that SQL Server came from.

From this research and some further thought, I have come to suspect that using aliases in other clauses, while quite possible, simply has never been that high a priority for DBMS manufacturers compared to other language features. Since it is not that much of an obstacle, being easily worked around by the query writer, putting effort into it over other advancements is not optimal. Additionally, it would be proprietary as it is obviously not part of the SQL standard (though I'm waiting to find out more on that for sure) and thus would be a minor improvement, breaking SQL compatibility between DBMSes. By comparison, CROSS APPLY (which is really nothing more than a derived table allowing outer references) is a huge change, that while proprietary offers incredible expressive power not easily performed in other ways.

Problems With Using Aliases Everywhere

If you allow SELECT items to be put in the WHERE clause, you can not only explode the complexity of the query (and thus the complexity of finding a good execution plan) it is possible to come up with completely illogical stuff. Try:

SELECT X + 5 Y FROM MyTable WHERE Y = X

What if MyTable already has a column Y, which one is the WHERE clause referring to? The solution is to use a CTE or a derived table, which in most cases should cost no extra but achieves the same final end result. CTEs and derived tables at least enforce the resolution of ambiguity by allowing an alias to be used only once.

Also, not using aliases in the FROM clause makes eminent sense. You can't do this:

SELECT
   T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
FROM
   Table1 T
   INNER JOIN Table2 T2
      ON T2.ID = CalcID
   INNER JOIN Table3 T3
      ON T2.ID = T3.ID

That's a circular reference (in the sense that T2 is secretly referring to a value from T3, before that table has been presented in the JOIN list), and darn hard to see. How about this one:

INSERT dbo.FinalTransaction
SELECT
   newid() FinalTransactionGUID,
   'GUID is: ' + Convert(varchar(50), FinalTransactionGUID) TextGUID,
   T.*
FROM
   dbo.MyTable T

How much do you want to bet that the newid() function is going to be put into the execution plan twice, completely unexpectedly making the two columns show different values? What about when the above query is used N levels deep in CTEs or derived tables. I guarantee that the problem is worse than you can imagine. There are already serious inconsistency problems about when things are evaluated only once or at what point in a query plan, and Microsoft has said it will not fix some of them because they are expressing query algebra properly--if one gets unexpected results, break the query up into parts. Allowing chained references, detecting circular references through potentially very long such chains–these are quite tricky problems. Introduce parallelism and you've got a nightmare in the making.

Note: Using the alias in WHERE or GROUP BY isn't going to make a difference to the problems with functions like newid() or rand().

A SQL Server way to create reusable expressions

CROSS APPLY/OUTER APPLY is one way in SQL Server to create expressions that can be used anywhere else in the query (just not earlier in the FROM clause):

SELECT
   X.CalcID
FROM
   Table1 T
   INNER JOIN Table3 T3
      ON T.ID = T3.ID
   CROSS APPLY (
      SELECT
         T3.ID + (SELECT Min(Interval) FROM Intervals WHERE IntName = 'T') CalcID
   ) X
   INNER JOIN Table2 T2
      ON T2.ID = X.CalcID

This does two things:

Makes all expressions in the CROSS APPLY get a "namespace" (a table alias, here, X) and be unique within that namespace.
Makes it obvious everywhere not only that CalcID is coming from X, but also makes it obvious why you can't use anything from X when joining table T1 and T3, because X hasn't been introduced yet.

I'm actually quite fond of CROSS APPLY. It has become my faithful friend, and I use it all the time. Need a partial UNPIVOT (which would require a PIVOT/UNPIVOT or UNPIVOT/PIVOT using native syntax)? Done with CROSS APPLY. Need a calculated value that will be reused many times? Done. Need to rigidly enforce execution order for calls over a linked server? Done-with a screaming improvement in speed. Need only one type of row split to 2 rows or with extra conditions? Done.

So at the very least, in DBMS SQL Server 2005 and up, you have no further cause for complaint: CROSS APPLY is how you DRY in the way you are wanting.


Login to SQL Server 2k8 R2 SP1 via Active Directory Group I added an Active Directory group as a Windows Authentication Login to our SQL Server 2008 R2 database.

When a user who is a member of that group tries to connect to the server he is given a login failed error.  If I add his specific username as a login, he is then able to connect.

Should I be able to add an Active Directory group to allow users in that group to connect?  It would seem this would be preferred over individually managing specific users.

SELECT @@version;

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

It appears that because the security group was literally created moments before I added it as a Windows Auth Login that it hadn't synced yet with all the domain controllers.  It now appears to be functioning properly.

I apologize for the errant question.

Thanks,

Jeremie