In Oracle 12c trying to create table with columns greater than 4000 New to Oracle and SQL.
I have already followed normal procedure to allow for this size:

startup upgrade
alter system set MAX_STRING_SIZE ='EXTENDED' scope=spfile;
@%ORACLE_HOME%\RDBMS\ADMIN\utl32k.sql
@%ORACLE_HOME%\RDBMS\ADMIN\utlrp.sql
shutdown immediate
startup;

No error messages during sql scripts or startup.
Tried to create table column in user but still getting error below:

SQL> CONN oracle
<password>
SQL> CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
max_string_size              string     EXTENDED

Please assist me in finding my error.

@Balazs:  I have attempted your commands but receive errors:

$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 18 08:21:34 2019

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

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> CONN sys / as sysdba
Enter password:
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 5033164800 bytes
Fixed Size                  8803120 bytes
Variable Size            1040190672 bytes
Database Buffers         3976200192 bytes
Redo Buffers                7970816 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

SQL>

Not sure where I have gone wrong.

Those are not the correct steps to enable the extended data type. You have set max_string_size to EXTENDED in the spfile (scope=spfile) so it had no effect while running the scripts.

Try again.

Non-CDB:

Enabling the New Extended Data Type Capability

CONNNECT SYS / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
START $ORACLE_HOME/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;

Or:

MAX_STRING_SIZE


High Availability Error 35250 and “An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.) I am facing problem in configuring High Availability, this is my 30-40 time configuration of Always On Availability Groups with central subscriber replication topology and I have never been stuck like this with SQL Server error 35250. I searched a lot but I was unsuccessful in fixing it.

I got this error:

  An error occurred while receiving data:
  '10054 (An existing connection was forcibly closed by the remote host.)'.

I am using Windows Server 2012 R2 with two node server on SQL Server 2014 (64 bit).

Windows login account and SQL Server service account are both running under a domain account (abc\Administrator).

hadr_endpoint (port-5022) are started with window login permission.

My Always On endpoint ([Hadr_endpoint]) are not blocked by firewall because all firewalls are stopped.

  Msg 35250, Level 16, State 7, Line 1
  The connection to the primary replica is not active.  The command cannot be processed.

Note I am not installed any hotfixes.

I create a domain admin account (on DC) like domainname.in\Administrator and give the all permission to this user (under Member Of tab) and login with domainname\Administrator to the both server. Login in SQL Server with same domainname\Administrator, same service account domainname\Administrator. Note - I created user domainname.in\Administrator but login with domainname\Administrator without '.in'. Is it the issue?

Endpoint URL (please check with ".in" and service account without ".in" (is this the issue?)

My network team not using any kind of network capture and we stopped firewall. We are not using any security software. We are using cyberoam and we also pin all the three server into same switch.

CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)

Service Broker script:

CREATE ENDPOINT [BrokerEndpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

I am not using RC4 algorithm for endpoint or service broker. I also increased session timeout time with 60 seconds for secondary replica. I also see the .dag file and it show "failure_condition_level 3". I know probably this is network issue but my network person unable to solve it.

Is there any user mapping issue in SQL Server? Any database is compulsory add to my SQL Server login account?

After too many tries and reading lots of blogs about SQL Server error 35250, I can't solve this error.

Finally, I tried IP address in Endpoint URL and I successfully configured Always On availability group. Problem in FQDN name.

To use an IP address in Endpoint URL: First check below services are started. If not, then start on all replicas and DC:

1. DNS Client
2. Function Discovery Resource Publication
3. SSDP Discover
4. UPnP Device Host

Above services help to discover all nodes in the network. Then check into network for your all node are shown over there. If not then follow these steps:
Go to Network and sharing center; click on change advanced sharing settings; and turn on network discovery.

TCP://10.10.10.530:5022


How to prevent deadlocks in many-to-many insert/update trigger function? I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point.

I have a tweet table that receives thousands of records per second. One of the columns is a PostgreSQL array[]::text[] type with zero-to-many urls in the array. It looks like {www.blah.com, www.blah2.com}.

What I'm tryin to accomplish from a trigger on the tweet table is to create an entry in a urls_starting table and then adding the tweet/url_starting relationship in a tweet_x_url_starting.

Side note: The url_starting table is linked to a url_ending table where the fully resolved url paths reside.

The problem I face is deadlocks and I don't know what else to try.
I went on an Erwin Brandstetter learning spree. (if you're out there man... THANK YOU! )

How to implement a many-to-many relationship in PostgreSQL?
Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
Postgres UPDATE … LIMIT 1 (skip locked help)

I tried adding ORDER BY's for deterministic, stable orders and FOR UPDATE SKIP LOCKED but am not sure I'm doing any of it correctly.

Here's the structure. Using PostgreSQL 10.5.

CREATE TABLE tweet(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    twitter_id text NOT NULL,
    created_at timestamp NOT NULL,
    content text NOT NULL,
    urls text[],
    CONSTRAINT tweet_pk PRIMARY KEY (id)
);

CREATE TABLE url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

CREATE TABLE tweet_x_url_starting(
    id_tweet integer NOT NULL,
    id_url_starting integer NOT NULL,
    CONSTRAINT tweet_x_url_starting_pk PRIMARY KEY (id_tweet,id_url_starting)

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT tweet_fk FOREIGN KEY (id_tweet)
REFERENCES tweet (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT url_starting_fk FOREIGN KEY (id_url_starting)
REFERENCES url_starting (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

Here's the tweet table trigger.

CREATE TRIGGER create_tweet_relationships
    AFTER INSERT OR UPDATE
    ON tweet
    FOR EACH ROW
    EXECUTE PROCEDURE create_tweet_relationships();

And finally, the function.

CREATE FUNCTION create_tweet_relationships ()
    RETURNS trigger
    LANGUAGE plpgsql
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 1
    AS $$
BEGIN
    IF (NEW.urls IS NOT NULL) AND cardinality(NEW.urls::TEXT[]) > 0 THEN
        WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
            ORDER BY 1
          ON CONFLICT (url) DO UPDATE
            SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
            ORDER BY 1, 2
            FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING;
    END IF;

    RETURN NULL;
END
$$;

I blindly threw the stuff I read about into the function without success.

The error looks like this.

deadlock detected
DETAIL:  Process 11281 waits for ShareLock on transaction 1317; blocked by process 11278.
Process 11278 waits for ShareLock on transaction 1316; blocked by process 11281.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (494,33) in relation "url_starting"
SQL statement "WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
      ORDER BY 1
          ON CONFLICT (url) DO UPDATE
              SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
    ORDER BY 1, 2
        FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

How can I stop the deadlocks? Thanks!

This might require careful examination, knowledge of the complete situation and more time than I can spend now. Or maybe I am missing something obvious. Whatever else might go wrong here, some things stand out:

Remove the FOR UPDATE SKIP LOCKED completely. It makes no sense where you use it. While selecting from the CTE, which holds rows with exclusive lock already, that makes no sense. It also would not make sense to skip any rows at this stage of the query.
COST 1 is misleading. The default is COST 100 and your trigger function is more in the realm of COST 5000. Leave the default or set it higher. Probably unrelated to the deadlock.
An AFTER trigger might be more susceptible to deadlocks than rewriting the whole workflow with a single query (with multiple data-modifying CTEs).
My shot in the dark: the deadlock is caused by the FK constraint trying to take a ShareLock on url_starting while concurent transactions try to modify the same row after having taken a similar ShareLock themselves and vice versa. A quick and dirty solution might be to drop the FK constraint tweet_x_url_starting if you can afford that. You could at least try that to verify it's part of the problem.

If you want to continue your learning spree - here is one more that seems relevant:

How to use RETURNING with ON CONFLICT in PostgreSQL?


Can I create a random number in an SSIS derived column I have a task where I am trying to assign a random PIN to an account. I have SQL code that will generate a random integer between 1000 and 9999. I was wondering if I could put that in a Derived Column task in SSIS and not do it in the source data?

Also, I can probably write a Script Task to accomplish this. At this point it's more an exercise to see if it's possible since I have alternatives.

You don't actually have to alter your source data, but you can use a customised SQL command in your OLE DB Datasource.  Basically change the Data access mode from Table or view to SQL command or even SQL command from variable and create your statement to include your PIN code, something like this:

SELECT *, 1 + ABS(CHECKSUM(NEWID())) % 9999 PIN
FROM dbo.ssisTest

Test results:

No need for Derived Column, Script Task etc.  Make sense?

Another approach would be to assign some kind of arbitrary row id to your source table and a similar id to your unique account ids table and join on that, something like this:

SELECT *
FROM
    (
    SELECT
        ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rowId,
        *
    FROM dbo.ssisTest
    ) x
    INNER JOIN dbo.uniqueAccounts a ON x.rowId = a.rowId

Similar to the above approach, you can hold this SQL in your OLE DB Source without affecting your actual source data.  In order for this to work you must a) persist your unique accounts and give them an arbitrary row id and b) there must be more unique accounts than there are original records, eg generate a few million up front.


One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database? This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before.
I'm using One-Directional Transactional Replication and I have ServerA - the Publisher, and ServerB the Subscriber setup. I'm only replicating about 20 tables or so total.
In ServerA, Table1 has a couple of nonclustered indexes on it (including the primary key itself), and a separate clustered index. When the snapshot is generated to create Table1 on ServerB, all of it's indexes (even the clustered index) are created as nonclustered on the subscriber database in ServerB.
I even went so far as to take the following steps:

Removed Table1 from the Publication's Articles
Dropped Table1 from the subscriber database
Re-initialized and generated a new snapshot
Then re-added Table1 with the Article Properties set to false for "Copy nonclustered indexes"
Then re-initialized and generated a new snapshot again

And it still recreated Table1 on the subscriber database with it's clustered index as a nonclustered index. It did not copy any of its actual nonclustered indexes except the primary key (which again is defined as a nonclustered index in the publisher's database).
Here's some screenshots to show it (sorry for all the obfuscation):
Table1 in Publisher vs Subscriber Database:

Publisher Table1's sys.Indexes:
[
Subscriber Table1's sys.Indexes:

Publication's Article Properties for Table1:

What the heck is going on?

maybe it's a bug in the SSMS; try to scritp the @schema_option and verity the proper value based on the list here:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-ver15


How to extract fields of anonymous row type in PostgreSQL In PostgreSQL I can create an anonymous row or composite type
postgres=# SELECT ROW(1,'Hello',false) as r;
      r
-------------
 (1,Hello,f)
(1 row)

But how can I get the fields out again?  None of the following work
postgres=# SELECT (r).* from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered

postgres=# SELECT (r).column0 from (SELECT ROW(1,'Hello',false) as r) as T;
ERROR:  record type has not been registered

New in v13

Allow ROW expressions to have their members extracted with suffix notation (Tom Lane)
For example, (ROW(4, 5.0)).f1 now returns 4.

select (ROW(1,'Hello',false)).f1

Note there are some gotchas. You may need to explicitly cast type
select (ROW(1,'Hello',false)).f2

ERROR:  failed to find conversion function from unknown to text

select (ROW(1,'Hello'::text,false)).f2

See db<>fiddle for more
The relevant commit in source appears to be 8b7a0f1d11 with a discussion at https://www.postgresql.org/message-id/flat/10872.1572202006%40sss.pgh.pa.us
I don't know at this time of any official documentation, but I'll shout out Jack@TA for showing me the new trick.


Postgres query to select all records that have at least one matching array value from another array Rough table structure:
id                      uuid
fields                  jsonb
created_at              timestamptz
related_content_ids     _uuid
updated_at              timestamptz

I'm trying to write a query that will find all records in this table that have at least one value from a specified array in the related_content_ids column.
Something like this works for matching all values in the array, but I just need at least one to match:
SELECT * FROM content WHERE related_content_ids @> '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'

"At least one match" can be done using the "overlaps" operator &&
select *
from content
where related_content_ids && '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'


Changing sa password I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same.

Login into the SQL Server Management Studio, Select Database Engine, \SBSmonitoring, Windows Authentication.
Go to Object Explorer--Security folder--Logins folder.
Right click on SA account and select the Properties option.
In General Page, change the SA password and confirm it.
In Status Page, change Login to Enabled. Click OK to save the change.
Restart the SQL Server and all its services.

Any idea ?

use this query it will change sa password

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword'
GO


MySQL Replication - Introduce new Slave to replication I've recently taken on a sys admin position managing a group of 20 (or so) servers.  One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL replication farm.

Basically the replication is set up like so:

MS -> SL1 -> SL2 (backup)
|                     SL3 (reporting)
SB2                   SL4 (loadbalanced web slave)
                      SL5 (loadbalanced web slave)
                      SL6 (loadbalanced web slave)
                      SL7 (loadbalanced web slave)

But basically its a Master with two read slaves, (one purely for backup), one for the main read slave (master in waiting, if you will), with 6 slaves behind it, used for an additional backup, 4 load balanced web read slaves and a server used for reporting.

I've done much reading online, and assumed adding a new slave to the environment (from SL1), would be to:

Log onto SL2:

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
(copy mysql directory to new server)
upon completion, UNLOCK TABLES;, and then START SLAVE; (all fine up until this point, SL2 comes back online and catches up)
made sure db structure is correct on new server and master.info points at the right place (pointing correctly at SL1)
Start MySQL on the new server, checked that Slave SQL and I/O is running (yes, this is fine)

However, after a period, I get duplicate key insert errors from the binary log:

110122 17:01:25 [ERROR] Slave SQL: Error 'Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY'' on query. Default database: 'thelm_soft'. Query: 'INSERT INTO  thm_member_views  (   member_view_ts, logged_in_members, non_members ) VALUES ( now(), '27037', '132834' )', Error_code: 1062
110122 17:01:25 [Warning] Slave: Duplicate entry '2011-01-22 17:00:01' for key 'PRIMARY' Error_code: 1062

Is this an issue with the fact now() is being in a query, which common sense tells me would cause issues if the inserts on that table were frequent enough??!?!  Or have I got the process with replication wrong, i.e starting at an incorrect log position?

Is this an issue with the fact now()
  is being in a query, which common
  sense tells me would cause issues if
  the inserts on that table were
  frequent enough??!?!

Yes, I believe this is the cause of your errors. Your method for introducing the new slave seems to be correct. It is quite strange in my opinion to define a table with a DATETIME field as the Primary Key. As you've quite rightly pointed out, the slave gets the replicated queries from the master and they will use the now() keyword in the queries, which will grab the timestamp from the local server.

Really, the table should be defined with some other data type for the PK (such as INT or BIGINT) which can be guaranteed to be unique, unlike a timestamp inserted with now().


Configuring 2017 SQL Server SSISDB Server-wide Default Logging Level does not seem to work Using SSMS, I have changed the SSIS Catalog Logging Level from Basic to Performance to a Custom Logging Level in a 2017 SQL Server Instance and then I run the report: "All Executions".  It doesn’t seem to matter what Logging Level I am at, the amount of data/detail that is included in the SSIS Catalog Report remains the same.  I would think the amount of detail would change with each Logging Level.
If the SSIS Catalog Reports do not change when the Logging Level changes, then how do I see the changes to the Logging Levels?  Otherwise, if the SSIS Catalog Reports are suppose change, can someone please point me in the correct path to get the SSIS Catalog Reports to change?

As BCM indicates, that default will apply to new executions. Your job in SQL Agent likely has the earlier default level baked into the execution and so it's overriding the system default. In the Advanced properties, you'd switch it to the new value.
When you manually run a job, this is the syntax of the steps it takes (agent job does the same behind the scenes)
DECLARE @execution_id bigint;

EXEC SSISDB.catalog.create_execution
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'FolderName'
,   @project_name = N'ProjectName'
,   @use32bitruntime = False
,   @reference_id = NULL;

SELECT
    @execution_id;

-- 0 = None
-- 1 = Basic
-- 2 = Performance
-- 3 = Verbose
-- 4 = Runtime Lineage
-- 100 = Custom
DECLARE @var0 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

If you use the custom/ 100 logging value, then you also need to specify what custom logging level you want to use. Assuming I created a log level called "New Custom Logging Level", I'd add set this parameter before invoking start_execution
DECLARE @logLevelName SQL_VARIANT = N'New Custom Logging Level'
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
,   @object_type = 50
,   @parameter_name = N'CUSTOMIZED_LOGGING_LEVEL'
,   @parameter_value = @logLevelName;

MSDN set_execution_parameter_value
Tim Mitchell has a good article on setting the execution parameter values


Replicating a remote MySQL database to MS SQL Server 2008 I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.

Is this possible? Can anyone outline the steps required in order to achieve this?

Thanks.

Personally I'd pull into MS SQL method vs. the push from MySQL method.
Why? Well Windows has 32bit and 64bit MySQL ODBC drivers ready to go and setting up a linked server is trivial. I have plenty of MySQL servers linked to from MS SQL. Also, connecting to MS SQL from linux/unix is not always great and you're ususally not able to use all features. FreeTDS has limitations; you may hit them sooner rather than later so why not just skip it. This all assumes you're running MySQL on *nix. If not, it gets a little closer to 50/50 but I'd still choose pulling from MS SQL as it sounds like it's not the "live" database thus putting the load on it for any ETL or processing is more ideal.
The GoldenGate solution sounds interesting, but I'm sure it's not free.

Considering I've setup this sort of scenario with both MySQL and Oracle databases replicating to MS SQL I'll provide some tips that have worked best for me:

If you can, do your best to determine how you can ensure you're only bringing across delta changes. Merge commands can help with this. Truncating a table and then inserting it all in again bloats your log, uses network bandwidth, and just generally wastes time.
If dealing with lots of data be sure to break the transactions up so as not to require a massive log file. Use explicit commits or checkpoints when you've reached a step you know you won't need to roll back from.
If the MSSQL db will be for reporting only, do ETL work there so as not to impact the MySQL server. Use a staging database or schema + filegroup to make things easy.
Break the data importing into steps. This makes it easy to re-start the import where it failed and/or troubleshoot. An all or nothing approach becomes annoying quickly.
Use variables wherever possible to assist the remote db with query plans & index use. Also pay attention to what sort of transaction isolation you're in on the host box and what impact the "replication" queries will have. You don't want to block writers on a live db if you're just pulling data over for reporting or sandbox use.

Hope the tips help!


Restore deleted data from a Table which occurred daily I am not sure whether this is restore data scenario is possible.
Scenario:

I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in code),the data was getting deleted the same day whenever a new record which was not related that process comes into the table.So there is no specific time that this delete happens.
We identified this after a week(yesterday),and i added a delete trigger for that table and stores the data into another table.

The issue is how can i restore past week's deleted data.
I have transaction log backups which was taken every one hour.
Without knowing the specific time the data got deleted,how can i do point in time recovery.
Any restore strategy.?

It would be an arduous process, but you might be able to use RESTORE WITH STANDBY to a different instance.  After each log is restored, you can read the data in the database.  There is no guarantee the rows you are looking for haven't been added and deleted within the same transaction log backup.

RESTORE Statements

  STANDBY =standby_file_name Specifies a standby file that allows the
  recovery effects to be undone. The STANDBY option is allowed for
  offline restore (including partial restore). The option is disallowed
  for online restore. Attempting to specify the STANDBY option for an
  online restore operation causes the restore operation to fail. STANDBY
  is also not allowed when a database upgrade is necessary.

  The standby file is used to keep a "copy-on-write" pre-image for pages
  modified during the undo pass of a RESTORE WITH STANDBY. The standby
  file allows a database to be brought up for read-only access between
  transaction log restores and can be used with either warm standby
  server situations or special recovery situations in which it is useful
  to inspect the database between log restores.

On a side note (in reference to your statement about hourly transaction log backups), this situation is one of the reasons I like to Back Up Transaction Logs Every Minute. Yes, Really. for my critical databases.

Here is an example of using WITH STANDBY.

/* Create our test database.  You may need to change the file paths for your environment. */
CREATE DATABASE [StandbyTest] ON  PRIMARY
( NAME = N'StandbyTest', FILENAME = N'C:\StandbyTest\StandbyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'StandbyTest_log', FILENAME = N'C:\StandbyTest\StandbyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StandbyTest] SET RECOVERY FULL
GO
USE [StandbyTest]
GO

/* Create some dummy data */
CREATE TABLE dbo.Customers (CustomerID INT, CustomerName VARCHAR(50))
GO
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (1, 'Bill Gates')

INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (2, 'Steve Ballmer')

GO

/* Do a full backup of the database */
BACKUP DATABASE [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.bak'
WITH FORMAT, INIT,
NAME = N'StandbyTest-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add some more dummy data */
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (3, 'Donald Farmer')

/* Do a transaction log backup */
BACKUP LOG [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FORMAT, INIT,
NAME = N'StandbyTest-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Pretend we're a developer */
DROP TABLE dbo.Customers
GO

/* DISASTER!  We need to go back in time! */
USE master
GO
RESTORE DATABASE [StandbyTest]

 FROM  DISK = N'c:\StandbyTest\StandbyTest.bak'
 WITH  FILE = 1,
 REPLACE,
 STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
/* Notice that we only got 2 customers, because we haven't restored the t-log yet. */

RESTORE LOG [StandbyTest]

 FROM  DISK = N'C:\StandbyTest\StandbyTest.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers


Using a keywords table with multiple other tables Using MySQL

I need help determining how to model a Keywords table to three different tables (Products, Certifications, and Exams) - I have included my model which is using what I refer to as intermediary tables (what would they be called), but im not sure that is correct. Can someone tell me if this is correct, or show me an example of how to do it correctly?  Thank You!

Your design is correct. Tables with many to many relationship needs to have an interface table. Interfaces table contains foreign keys to the main table, it must have a primary key. Good luck


ExclusiveLock on related table due to update? Recently ran into the following:

Consider the tables created by this:

create table table_a (
        id  bigserial not null,
        last_update timestamp,
        primary key (id)
);

create table table_b (
        id  bigserial not null,
        primary key (id)
);

alter table table_b add column a_id int8;
alter table table_b add constraint fk_c foreign key (a_id) references table_a;

And then, the following query(which takes a bit longer than the example shown here might have you suspect):

update table_a set last_update = now() where id in(<list of ids>);

I observed that this update query created an ExclusiveLock on table_b.

That seems really weird, as table_b isn't being used by the query at all. Or so I think.

What's going on here? Does where id in create this lock implicitly?

Either way, how can I avoid this? Where does my understanding fail?

Also, the query that showed me this lock being taken on table_b:

SELECT a.datname,
      c.relname,
      l.transactionid,
      l.mode,
      l.GRANTED,
      a.usename,
      a.current_query,
      a.query_start,
      age(now(), a.query_start) AS "age",
      a.procpid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.procpid
JOIN pg_class c ON c.oid = l.relation
WHERE pid <> pg_backend_pid()
ORDER BY a.query_start;

There is no way to fix this with PostgreSQL 9.1

Up until 9.2 (including) any referencing table was locked  during an update.

This was fixed in the PostgreSQL 9.3 (also unsupported by now)

Quote from the release notes:

  Prevent non-key-field row updates from blocking foreign key checks


How to evolve MySQL schema while maintaining integrity As I understand it, when you run a MySQL DDL statement, it will often lock the table, and then unlock it. It will even unlock it even if you had manually locked it. Also, schema modifications cannot be grouped by a transaction.

Therefore, if you have a migration which removes some foreign key constraints, alters the related tables, and then replaces the constraints with new ones, you could have a problem. If another database client has inserted an invalid value while the foreign key constraints were removed, the new foreign key constraints may not be possible to apply even if the migration SQL was correctly written. That's just one example, but there are probably other ways that this problem could manifest.

How do you deal with this in MySQL? I am using version 5.6.

You could probably use the online schema change feature of ALTER TABLE in MySQL 5.6
Please note the MySQL 5.6 Documentation for ALTER TABLE

Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Section 14.11, “InnoDB and Online DDL” for information about online DDL for InnoDB tables.

You would run the ALTER TABLE like this:
ALTER TABLE mytable ALGORITHM-INPLACE ... ;

What this will do is create a shadow copy of the table being changed. All reads and writes can go on with the original table. Additional changes made to the table is placed in the shadow copy. Everything is merged and presented at the end.
CAVEAT #1
The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.
CAVEAT #2
Again, from the MySQL 5.6 Documentation for ALTER TABLE

As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary.

Changing Columns with older the DATETIME format will not work. You would have to use other means to make the new table.


can I convert an integer to base-2 without a function Here's a bit of a challenge.  I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek.  I may not be using the proper terminology here, but this is an integer from which one can derive the days of the week an SSRS subscription is set up to run.  Each day is assigned a number as follows:

Sunday: 1
Monday: 2
Tuesday: 4
Wednesday: 8
Thursday: 16
Friday: 32
Saturday: 64

The sum of the days the subscription is set to run is the number in this column.  Thus subscriptions that run Monday through Friday have a value in this column of 62.

The end result I want is a view that derives T/F flags for each day based on this number so that I have a column for each day.  The method I am currently exploring to get me there is converting this integer to base-2 so then I can convert that to a varchar and parse out the days.  In this example, the result would be 111110.

The final twist - I do not have the ability to create functions or stored procedures in this database, so my strong preference is to solve this within a SELECT statement...

(If push comes to shove, I will move the raw data and use a function in a separate database - and have found a number of those online.)

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for).  It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable


Drives vs. Mount Points? The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, I think, because he has no experience with them).

Based on the results of numerous internet searches, I can't find any (post-SQL Server 2000) reason to not use mount points.

Is anyone aware of Windows OS limitations regarding this topic?

I've been hearing the claim "the OS doesn't recognize mount points" a
lot lately. (Untrue, based on my research into the versions of Windows Server we use).

Is there any evidence- or experience-based reason NOT to use mount points with SQL Server?

Assume that running out of drive letters is not an issue for us.

It's my understanding that mount points are incredibly useful for segregating workloads.

Can anyone confirm or refute my understanding that mount points actually segregate/isolate workloads of the different types of data and log files (system database files, user database files, tempDB) more efficiently than one drive each for data files, log files, and tempdb?

It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer.

To determine the configuration of the mount points, see Locating Mount Points Using PowerShell by Boe Prox.

SQL Server has no problem with mount points. These are defined at the OS level and SQL Server "doesn't know1" they're not the same volume as the drive they appear to be mounted in.

Some Monitoring tools might give you bad information based on that last sentence, however.

For example if you have three mount points like

C:\SQLData\SQL_Data where all your .MDB files are stored
C:\SQLData\SQL_Logs where all your .LDF files are stored
C:\SQLData\SQL_Backups where all of your .BAK and .TRN backup files are stored

Then SQL Server will work without any issues. But if you run some sort of tool that warns you when disks are low in space, it may check the C: and not the mounted volumes below it, so you may not know when those mount points are low on disk space. Also, various "best practices" queries will throw false warnings telling you that you shouldn't have your data, logs, and backups all on the same disk because SQL Server thinks they are on the same disk. Those are false flags, and can be ignored.

But you'd basically want to set up some additional steps in your server monitoring to make sure the C: drive has enough space and that each mount point does, too.

The times I've used mount points in SQL Server, that's been the only issue I've run into: SQL Server system health reports that give false data about free space available, and false errors saying you shouldn't have all of your data on the same drive. Since you know those are false errors, they are easy enough to ignore.

1 SQL Server has data that makes it aware of the mount point, but from a practical use viewpoint, there's no difference in behavior. It "just works," trusting the OS to handle the specifics. Just as it trusts the OS to handle the specifics for iSCSI LUNs that are connected as local drives.


Email Column Constraint to Contain '@' character How do you create an inline constraint for a Email Address Field that requires there be an "@" character? Pretty much I want there to be a restriction to the field so that the data entered must have the "@" within it. I'm using Oracle SQL Developer 12c.

Try this:
CREATE TABLE myTable ( email VARCHAR2(254) );
ALTER TABLE myTable ADD CONSTRAINT chk_email_format CHECK ( REGEXP_LIKE(EMAIL, '.{1,64}@.{1,255}'));

INSERT INTO myTable (email) VALUES ('abc abc.com')
Error report -
SQL Error: ORA-02290: check constraint (FLASHCARD.CHK_EMAIL_FORMAT) violated

254 total characters is from Errata ID: 1690 with 64 before the @ and up to 255 after it.
This simple regexp, email address must have a @, will alert you to attempts to insert wildly wrong data (perhaps helping catch a bug or two).  However, building a complete RFC 5322 validator is a difficult task.  For details take a look here:
https://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address


What is meant by Orphan Lock and Orphan Sessions? I have came across this term Orphan locks and Orphan sessions and I really have no idea what that is. I have tried to search it on Google and I still haven't got any useful info/results regarding what it mean.

All I get till now is that orphan locks are some kind of session locks which results in deadlocks after executing query or queries. (please correct me if I get it wrong)

Can someone please explain this a bit ? What are Orphan Locks ? Are they the same as Orphan sessions ? How do they form and what they have to do with sessions ?

p.s: I am still newbie to this so I apologise if it sounds all too basic.

As the name suggests orphaned session is like a child that nobody knows whom it belongs to and in session terms, nobody know a specific session whom it belongs to, hence the name orphaned session.
Orphan session examples:

database table lock forever because client and server connection is broken
A user session that the IdP (identity providet) cannot determine what client it belongs to, this can happen with SSO when the user log out from SP (service provided) (example any website that uses SSO) and the IdP that provides the SSO (example login with facebook/google). If the user logs out of the SP but not the IdP as well, then the IdP would have all those orphan sessions that they don't know whom they belong to and are just hanging there.


Splitting metadata inside Postgresql I have metadata in a postgresql table that I need to split. So far I have been attempting to use

Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | etc...);

Out of a table formatted like so:

QGC 10''
QGC 10''
QGC 10''
QGC 10''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 10''

I need only to extract the integers and would like to preserve the acronyms if possible. I have attempted to use the split substring with regex function but haven't found much success, although this is primarily due to my lack of understanding of how to pass substrings inside of queries when dealing with a table. If someone could explain the appropriate syntax in order to perform this task they would help me greatly. Thanks in advance.

Thanks Martin, You are right - I placed this in the wrong section, thanks for your input. Also, you are correct in assuming that the quotes are holdovers from the database and I have corrected them to more accurately represent the data. How would I go about having the topic moved as not to darken the door of gis.stackexchange?

After looking at split_part() from PostgreSQL String Functions, and assuming that column is the name of the "compound attribute" that is of the form "acronym codenum", then I'd say you should try something like this

SELECT
  split_part (column, ' ', 1) AS acronym,
  split_part (column, ' ', 2) AS codenum
FROM table


InnoDB query duration inconsistent I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 updates will take 20ms, and the 21st update will suddenly take 200ms. I've set up an incredibly basic test:

CREATE TABLE `test` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I insert a single row into the table, and then I have a C# console program that does a series of updates:

for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   MySqlCommand cmd = new MySqlCommand();
   cmd.Connection = conn;
   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}

This is the output I see from the program:

MySQL version : 5.5.17
Duration: 36
Duration: 30
Duration: 16
Duration: 26
Duration: 152
Duration: 47
Duration: 71
Duration: 77
Duration: 46
Duration: 28
Duration: 21
Duration: 25
Duration: 17

If I run "SHOW profile FOR QUERY" with the 152ms time, both the "Updating" and "query end" values are abnormally high.

If I switch the table to MyISAM, the query duration is perfect, but I don't want table-locking. Does anyone have a guess as to what is making InnoDB act this way?

ASPECT #1 : InnoDB

What immediately catches my eye is the fact that the table has no explicit PRIMARY KEY. This means that an internal rowID Clustered Index (gen_clust_index) is being used.

With each UPDATE command, you may encounter moments when the row is actually locked in conjunction with the Clustered Index. Consequently, the data page gets locked.

Here is a question: Is it possible for the PRIMARY KEY (or rowID) of two different rows to deadlock? Surprisingly, the answer is Yes. Rather than explain why, I will refer you to three questions asked by one person: RedBlueThing.

I managed to answer his three questions about deadlocking pages:

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

It is possible that two or more rows occupy the same data page. In fact, the rowID-based PRIMARY KEY coexists with the data on the same data page. While crisscrossing rows, perhaps some miscellaneous garbage collection (data structures within the InnoDB Buffer Pool that are released and reallocated) may be happening.

ASPECT #2 : Your C# Code

Inside the loop, you are also doing garbage collection and reallocation for the DB Connector. That needs to be reorganized, perhaps placing it outside the loop.

SUGGESTION

Try setting up the test using the transaction paradigm with BeginExecuteNonQuery()

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.BeginExecuteNonQuery();
for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}
cmd.EndExecuteNonQuery();

Give it a Try !!!


How to implement Sql Server 2016 mobile publisher on existing Report Server Currently working on sql server 2014 (SSRS 2014) on window server 2012 R2.

Requirement is to publish Sql Server mobile publisher report on report manager on SSRS 2014.

I installed Sql Server 2016 mobile publisher and developed some report but how to publish it on ssrs 2014 (Is it possible ?).

My problem is how to implement and use Sql Server 2016 mobile report on my current scenario.
Is this possible ? or upgrade to sql server 2016.

SQL Server Mobile Reports (nee Datazen) is only available on SSRS 2016.


After insert trigger only using single inserted virtual table row I have a trigger that updates multiple columns in a table for rows inserted into another table.

A record inserted into the fuelTransactionInfo column, updates a column in fuelTransactions, based on the "info code", finding the correct row using fuelTransactionId. I'm just denormalizing some data for easier reporting.

The rows can be inserted into the fuelTransactionInfo column with multiple values. E.g.,

INSERT INTO fuelTransactionInfo
(...cols)
VALUES
(...values),
(...values),
(...values);

My issue is that only the first set of values is being used in the trigger. I understand that triggers are fired on the statement, not the rows, and I believe the trigger code accounts for that. However, it's simply not working.

Here is my trigger code:

ALTER TRIGGER [dbo].[fuelTransactionInfoDev_DenormalizeFuelTransactions]
   ON  [ERPDev].[dbo].[fuelTransactionInfo]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE t SET
        t.employeeNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'DRID' THEN
                inserted.value
            ELSE t.employeeNumber
        END,
        t.unitNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'UNIT' THEN
                inserted.value
            ELSE t.unitNumber
        END,
        t.contractId = CASE
            WHEN fuelInfoCodes.efsCode = 'CNTN' THEN
                (SELECT c.id
                 FROM pinContract p
                 JOIN contracts c
                    ON c.contractNumber = p.ContractNumber
                 WHERE pin = inserted.value)
            ELSE t.contractId
        END,
        t.contract = CASE
            WHEN fuelInfoCodes.efsCode = 'CNTN' THEN
                (SELECT c.contractNumber
                 FROM pinContract p
                 JOIN contracts c
                    ON c.contractNumber = p.ContractNumber
                 WHERE pin = inserted.value)
            ELSE t.contract
        END,
        t.driverName = CASE
            WHEN fuelInfoCodes.efsCode = 'NAME' THEN
                inserted.value
            ELSE t.driverName
        END,
        t.subFleet = CASE
            WHEN fuelInfoCodes.efsCode = 'SSUB' THEN
                inserted.value
            ELSE t.subFleet
        END
    FROM fuelTransactions t
    JOIN inserted
        ON inserted.fuelTransactionid = t.id
    JOIN fuelInfoCodes
        ON fuelInfoCodes.id = inserted.fuelInfoCodeId;
END

I've done a little bit of testing by adding the following statement to the trigger:

SELECT * FROM INSERTED INTO insertedTest

I see all the expected rows in the insertedTest table. I've tested the UPDATE statement by converting it into an equivalent select (based on the insertedTest table, instead of "inserted"), and all the joins are good.

Only the column in the 'UNIT' case is actually updated in fuelTransactions, which happens to be the first row in every single insert firing the trigger.

What am I doing wrong? Let me know if you need more info.

The reason it's not working is that your join causes each row to be updated multiple times, but each update sees only the original values, not the cumulative values of previous updates. So your updates are in effect cancelling the previous updates.
(Or maybe SQL only allows one row update per statement. Either way the result is the same, with different resource use.)

Here's a quick and poorly formatted repro:

declare @insertedexample table (id INT, codeid int, value varchar(10))

INSERT INTO @insertedexample VALUES
(1,1,'newvalue1'),
(1,2,'newvalue2')

declare @targetexample table (id INT, codeid1value varchar(10), codeid2value varchar(10))

INSERT INTO @targetexample VALUES
(1, 'origvalue1', 'origvalue2')

SELECT * FROM @targetexample

UPDATE t
SET
t.codeid1value = CASE WHEN i.codeid=1 THEN i.value ELSE t.codeid1value END,
t.codeid2value = CASE WHEN i.codeid=2 THEN i.value ELSE t.codeid2value END
FROM @targetexample t
JOIN @insertedexample i on i.id = t.id

SELECT * FROM @targetexample

The only way I can think of at the moment to handle this is by building a dynamic query.

Aaron's suggestion of a view would certainly be better, provided users will only be filtering by fuelTransactions.id or the data will remain small enough to handle filtering after pivoting. (Edit: I later realised that it might also be possible to create a view, or a set of views, or certainly a function, that would allow efficient additional filtering, dependent of course on supporting indexing in the base table.)

UPDATE:

Actually you probably don't need a dynamic query, since the columns in the fuelTransactions table are fixed as are the efsCode codes you're interested in.

You might be able to use a pivot of one form or another, which incorporates the current values for the columns not being updated, even if you have to use a bunch of ugly subqueries.
I might have time to experiment with it, but can't promise.


Always-On secondary Replica's Read Committed Snapshot Isolation When you use Readable Secondary Replicas in an SQL Server Availability Group, SQL Server will also use internally Read Committed Snapshot Isolation. When fail-over occurs to a readable secondary replica does the secondary database isolation mode change back to READ COMMITTED as the primary or does it stay Read Committed Snapshot Isolation internally? Finally, if the database isolation mode stayed the same; would it be fair to say if a database supports an Always-On Readable Secondary Replica, then it also has to support RCSI as well?

Yes, when failover occurs the secondary changes back to read committed if that's how the DB is configured on the primary.
The developers are the only ones that can tell you if your application supports RCSI.  It probably does, but if they designed it such that it depends on blocking to ensure things are done in the correct order, then it doesn't.  Many third-party applications do not document whether it is supported when it really is, so step one is to contact the vendor support and ask and go from there.


Best DB structure for large CSVs? I have previously posted on stackoverflow, however, I have been directed here.

I'm getting to the stage where I need to analyse lots of flat CSVs:

Read-in around 500GB of CSVs (e.g. daily data split by month)
Group the data (e.g. by month or year)
Output the aggregated data as a small CSV

I wasn't sure whether sqlite was the right package for this (adding CSVs to it appears to take a long time and as in my link above after a certain size I can't access the database) and whether I should consider something else like PostgreSQL or Microsoft SQL Server?

I'm looking to invest in a hardware/software platform for this (e.g. SSD, RAID, Microsoft SQL Server) and was hoping for some information on where to begin.

Particularly, if PostgreSQL is a possibility - is there a similar way to quickly import CSVs like here:

https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Edit (08/10/15):

I'm testing out uploading the data into a PostgreSQL database and am averaging 16 minutes per 10GB CSV. My issue is that some of my columns are very big so I would have to change them from bigint to varchar, etc. The data has 38 columns and around 50 mill rows per file so figuring out which categorical variables are strings or integers is a real pain.

At the moment I am using:
cur.copy_expert(sql="COPY %s FROM stdin DELIMITERS '~' CSV;", file=f), with my data-type mainly being varchar. I did this because the CSV is a bit messy and sometimes what I think is an integer turns out be alphanumeric and I have to re-upload.

Would it be much slower to import as a Pandas data-frame (so that panda takes care of column-type for me) and use that with sqlachemy to insert into Postgres? I'm guessing if PANDAs is a bit slower it will make up for it because the column types will be optimised (since it will decide on that for me).

E.g. something like this:

import pandas as pd
df = pd.read_csv('mypath.csv')

from sqlalchemy import create_engine
engine = create_engine('postgresql://username:password@localhost:5432/dbname')

df.to_sql("table", engine)

My main goal is to optimise this for the group-by command.

Second Edit:

Ahh, I guess best thing is to upload one CSV file using PANDAS and then copy the table structure into a new table which is then filled with the faster COPY command?

Frankly, I probably wouldn't use a database in the first place.  It doesn't sound like you have any need for ACID, which is one the main things which databases provide and which doesn't come cheap in terms of performance.  Nor does it sound like you need complicated indexes.

What I would do (and have done, an awful lot) is just loop over the files with Perl or Python and use their built-in hash table features to generate your aggregates.  Since you have specified that the output is small, you should have little problem holding the intermediate aggregation state in memory for the duration.


SQL Server and SSPI handshake failed error Recently I faced an issue where users from one domain were unable to login to SQL Server and getting this error:

  Error: 17806, Severity: 20, State: 2.
  SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxx]

  Error: 18452, Severity: 14, State: 1.
  Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxxxxx]

We have two domains let's say A and B. My SQL Server is running in domain B. In a normal scenario, both domain users are able to connect. But few days ago, all the users from domain A were unable to connect to SQL Server running in domain B. However domain B users were able to connect it successfully. Users approached SQL Server DBA team to look into the issue.

When I checked, even I was unable to connect to SQL Server remotely using SSMS and using credentials of domain A. However when I tried to RDP the server using domain A's credential it was working fine and there after remote access to SQL server also started working fine. So it looked to me like when I RDPed the server something got refreshed and my login remotely started working fine. I was sure that the actual issue was something related to AD and kerberos authentication but unable to prove so.

I researched this issue on web and found two solutions to fix it. One was to fix malfunctioning DC and second was to restart the server where SQL Server was running. I went the 2nd way as windows admins were not accepting that it is an DC issue. I checked everything from SQL end nothing got changed recently. Also found that this issue started happening after below event was logged on server where SQL Server was running:

  Log Name: System
  Source: NETLOGON
  Date: xxxxxxxx
  Event ID: 5719 Task
  Category: None
  Level: Error
  Keywords: Classic
  User: N/A
  Computer: xxxxxxxxxx
  Description: This computer was not able to set up a secure
  session with a domain controller in domain xxxxxx due to the
  following: There are currently no logon servers available to service
  the logon request. This may lead to authentication problems. Make sure
  that this computer is connected to the network. If the problem
  persists, please contact your domain administrator.

  ADDITIONAL INFO If this computer is a domain controller for the
  specified domain, it sets up the secure session to the primary domain
  controller emulator in the specified domain. Otherwise, this computer
  sets up the secure session to any domain controller in the specified
  domain.

My questions are:

How to prove that this is a domain controller issue?
How reboot of server fixes this issue?

Thanks for your help.

Why does a reboot of the server sometimes fix this issue?

I think the OS has an issue talking with the DC (for whatever reason(s) you determine) and when this happens, it just gets out of sync somehow with DC, AD, etc. and a quick fix is to reboot the SQL Server OS when this occurs and there is no network communication with the DC at the time of the reboot, and all comes up fine with everything back in sync at that point. To accurately answer this, you probably need to find the reason first.

Proving that [the] Domain Controller(s) is/are or is/are not the issue

1. Troubleshoot & Consider

  I'm not suggesting you make a bunch of changes before trying to find
  and fix the specific problem beforehand but there are many factors to
  consider I would think. It's just something you have to troubleshoot
  one step at a time unless there's something obvious causing the issue.

2. Access Perspective

  In my setup, I'm the domain admin as well as the DBA so I have access
  to everything and all servers (two domains with two-way trusts) to
  troubleshoot at all levels whereas you may be a bit more restricted
  with what you can review, etc. on each server in the loop of the
  issue.

3. No Simple Oversight

  I usually start with the simple items first that don't require changes
  and see if there are any obvious issues (e.g. event viewers on all
  servers, DNS testing on all servers, review all server configurations,
  etc.).

4. No Telling Logic

  This could be an issue at the OS level of the SQL Server for the
  Windows version it is on. This could also be an issue with the NIC of
  the SQL Server or a mis-configuration of TCP/IP or DNS settings on the
  SQL Server or any of your DCs.

5. Collect & Update

  Gather information on or ensure the SQL Server is fully updated with
  Windows Updates, and ensure server firmware is up-to-date if
  applicable with BIOS version as well as any hardware device firmware
  updates. The domain admins could also do the same on the DCs but use
  caution with making a bunch of changes and do so one step at a time.

6. Best Practices

  I'm not sure how your AD and DCs are configured topology wise, but
  best practices should be followed as best as possible and a DC should
  be close to the network where both domains are physically rather than
  going across a slower network pipe or routers, etc. which adds another
  level where there could be issues or configurations to review.

  The DCs should have their TCP/IP settings configured so that their DNS
  settings point to other DNS servers or follow best practices otherwise
  as per Microsoft for the DCs configurations applicable in your
  environment.

7. Request Access or Configuration Disclosure Assistance

  You'll need to do basic troubleshooting to find the issues to resolve,
  ask questions, see what all could be updated and patched or changed,
  and get your domain admins to help you troubleshoot or disclose domain
  configurations to you if you're not sure how to see if everything is
  configured as best it could be for optimum performance and per best
  practices.

Important: This not to say there couldn't be a very simple reason this suddenly happened but if nothing has changed anywhere (i.e. OS on DCs or SQL Server, network configurations, AD or forest functional levels, hardware upgrades, Windows Updates, etc.) then you just have to troubleshoot for the reason why one step at a time.


Estimate average and median efficiently in Postgres? I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which contains lots of useful statistics (such as the histogram bins in view pg_stats) that describe a database. Is there a way to take advantage of the statistics in pg_catalog to estimate the average and median numbers over a numeric column in a Postgres table?

If an estimate is good enough, then statistical sampling is your friend. I'd probably use a sample size calculator to determine how many rows I need, then write some code to randomly insert that many keys into a table. A join, a function, and you're done.

If you've never done anything like this before, you'll probably want to do some background reading. When I had to do that stuff, I used a handbook from nist.gov.  (And you'll probably be surprised at how small a sample you need.)


Is it better to store calculated values or recalculate them on request? Possible Duplicate:
  Storing vs calculating aggregate values

At an old job, we had this database, it wasn't normalized rigth, some tables wheren't used anymore, it was patch after patch. In conclusion, it was a mess.

Once, while I was trying to fix it I saw that some fields where calculated on the fly and this made the db very slow performing simple select querys. Since then, I don't calculate any field anymore, I just store the data as is.

Is this recomendend? when is better to store data than calculate it?

Thanks guys.

It depends.

Storing derived values can speed up processing of later read requests as the values do not need to be computed again.

There is one key problem with denormalising your data in this way though: you have to ensure that it is not possible for the derived value to become out-of-date undetected. If your data is not in a sufficiently normal form inconsistencies can creep in and cause trouble. This means that everywhere that updates the data the value is derived from must also refresh the derived value or somehow mark it as needing to be updated before being relied upon. Updating the derived values via triggers may mitigate having to add this logic to many places in your code, but be aware that triggers have their own set of potential gotchas (unexpected locking issues, code in the business logic layer not expecting values that it doesn't explicitly reference to update, triggers can be disabled, and so on). Another solution is to discourage direct access to the tables at all and have all updates via an API based on stored procedures - those procedures can know to recompute the calculated values as needed and obviously can't be disabled.


a table with one column only? What is the use of a table with only one column?

TableName
----------
Table_PK

Say I have a table named Academic and the staff has a positionID but this positionID is:

tablePosition
----------------
PositionID

thats it...only one column

My best and most speculative guesses are:

A lookup table. Used as a control/pick list for an application or report

A dimension.  Used in a data warehouse.

A log table. Used for logging.

Left over junk fron an adhoc query, data migration, report filter etc.

Aaron was right... But I couldn't resist ; )

To me, the names imply that it's to distinguish between academic and non academic staff. But unless you can test the application exhaustively in order to reverse engineer it you may never know the true/complete purpose for it.


How to extend oracle database to a newly added hard disk I'm a begineer to Oracle database admin role. Recently I have added a new hard drive to extend the capacity of an oracle database which runs on Linux. How to extend the oracle database to utilize new hard disk storage. Please explain the steps in point form.

As beginner, I strongly recommend to start working with the classical way on filesystems.
Then you must be aware, where do you need more capacity within your database.
Ensure, that the new disk is available on OS. Then go ahead with following hints and steps:
(Very high level, simple and the classic way)

Users can create objects (Tables, Indexes, Views, Procedures, Sequence, ...)
Objects, which are storing any kind of data are segments, e.g. tables/indexes.
Each table is always stored within only one Tablespace.
Users can locate different tables/indexes etc. into different Tablespaces.
Tablespaces consist of one or more datafiles.
A DBA can add as many datafiles to a tablespace as he likes (ALTER TABLESPACE ADD DATAFILE).
Each new datafile enlarges the capacity of only this tablespace.

To answer your question, you need to know, which tablespace needs the additional space. Then you can add a new datafile with command above (there are tons of examples) and locate the datafile to the mountpoint of your new disk.
If you are new, you should start with a database on standard filesystems and play around with datafiles. Other options like ASM are not a good starting point for beginners, if you want to learn.


mysql innodb space x did not exist in memory Into my innodb log I got the errors below. How to fix?
What did it mean? Some tables are corrupted but not all.

InnoDB: space id 1753 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute,
InnoDB: space id 1777 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_eav_attribute,
InnoDB: space id 1626 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_int,
InnoDB: space id 1609 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_text,
InnoDB: space id 1610 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_varchar,
InnoDB: space id 1611 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_eav_attribute,
InnoDB: space id 1746 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_eav_attribute_website,
InnoDB: space id 1747 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_form_attribute,
InnoDB: space id 1754 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_label,
InnoDB: space id 1779 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_option,
InnoDB: space id 1780 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_option_value,
InnoDB: space id 1781 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_entity_attribute,
InnoDB: space id 1784 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_form_element,
InnoDB: space id 1792 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/googleshopping_attributes,
InnoDB: space id 1804 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/salesrule_product_attribute,
InnoDB: space id 1516 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/googlebase_attributes,
InnoDB: space id 1798 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_set,

These messages appear to occur when InnoDB hasn't cached a "tablespace object," for a table, which from what I can tell is an internal structure containing the information about the tablespace (.ibd) file for a table.  Under normal conditions all of these would be cached at server startup (and new ones added when tables are created).

So, this suggests you've got data dictionary issues (corruption in ibdata1?) but the message does not, itself, appear to be a fatal one.

The message comes from dict_load_table() inside innobase/dict/dict0load.c, in response to a FALSE returned from a call to fil_space_for_table_exists_in_mem() in innobase/include/fil0fil.h.

Comments there:

/*Returns TRUE if a matching tablespace exists in the InnoDB tablespace memory
cache. Note that if we have not done a crash recovery at the database startup,
there may be many tablespaces which are not yet in the memory cache.
@return TRUE if a matching tablespace exists in the memory cache */

This suggests that those messages, while not normal under typical operating conditions, are not entirely unexpected if your server is in the middle of InnoDB crash recovery.

The next thing that happens is that InnoDB tries to open the tablespace files, and since there are no messages in your log indicating that this next step isn't succeeding, it seems safe to conclude that the tablespace (ibd) files are at least accessible and the problem will be elsewhere.

All of this suggests that there will have been previous messages and later messages in the log that will give a better indication of the nature of the problem.


Why is MySQL is creating so many temporary tables on disk? Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows

Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were created on disk

table_open_cache = 125 tables
table_definition_cache = 256 tables
You have a total of 97 tables
You have 125 open tables.
Current table_cache hit rate is 3%

Earlier temp table was "of the 23725 temp tables 38% were created on disk" but I changed max_heap and tmp_table to 200m from 16m and it lowered to 30%..

Configuration:

engine myisam
group_concat_max_len = 32768
key_buffer_size = 3.7 GB,
thread_stack = 256k,
table_cache = 125
query_cache_limit = 1M
query_cache_size = 16M
join_buffer_size = 2.00 M
max_connections = 800

Another system with default configuration is showing "of 23725 temp tables, 1% were created on disk" with the same database.

I tried changing to default on the machine with this issue and it still shows "Of 580 temp tables, 16% were created on disk".

I am using Ubuntu 11.4 64 bit with 48 gb ram. Can any one suggest a solution?

Will changing the db engine from "myisam" to "memory" on tables using "group by" fix this? As explained here: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

mysqltuner rarely provides any useful information.  It uses mostly irrelevant statistics about "hit rates" and puts arbitrary limits on what is an acceptable number of widgets are acceptable.  If you are not facing a performance problem, then you don't actually need to solve any of the problems that it presents to you.  That being said, here's a little background information about temporary tables...

MySQL internally uses the MEMORY storage engine for creating implicit temporary tables.  On disk temporary tables use the MyISAM storage engine.

Temporary tables are created on disk when:

TEXT or BLOB fields are present (because MEMORY doesn't support these types)
the size of the resulting implicit temporary table exceeds the lesser of tmp_table_size or max_heap_table_size
If a column w/ more than 512 bytes is used with either a GROUP BY or UNION or ORDER BY

Read the MySQL Documentation on Internal Temporary Tables for more details.

What can you do about this?  Presuming that it actually represents a performance problem (rather than just bothering you intelluctually):

Avoid TEXT/BLOB fields and instead use appropriately sized VARCHAR or CHAR fields where possible.
If TEXT/BLOB are unavoidable, sequester them to separate tables with a foreign key relationship and JOIN only when you need them.
Treat large columns, more than 512 bytes as you would the above mentioned TEXT/BLOB fields.
Make sure your queries are returning only the result set you need (appropriately selective WHERE clauses, avoid SELECT *)
Avoid subqueries and replace them with joins, especially if they return a large result set
Last resort - raise both tmp_table_size and max_heap_table_size.  Don't do this unless you find that your queries cannot be optimized.

If you are concerned about your MySQL configuration and are not comfortable with the available settings yourself, you might want to check out the Percona Configuration Wizard as a starting point.

  Will changing the db engine from "myisam" to "memory" on tables using
  "group by" fix this? as explained here

No, it won't and it will make it such that your tables are never persisted to disk.  Don't do this.


Aggregation using CASE I have a table called country:

CountryName | Sales
A             200
B             100
C             300
D             500
E             400

Now I in my report I have hardcoded the parameter of region that the user will select, such as RegionEast, RegionWest etc.

My requirement is that if I select RegionWest then I should aggregate countries (A, C). If (B, D) then RegionEast. There is no explicit relationship of a region with a country in the database.

I was trying to write my query like this:

Case When Parameter ='RegionWest' Then Country in (A,C)
     when Parameter ='RegionEast' Then Country in (B,D)
 Else Country

But that is not working as expected. Can somebody help?

The CASE you wrote is for visualization (SELECT) or for filter data (WHERE).

if is the SELECT case, then can be (not sure if you want an SUM(Sales):

SELECT
  (CASE WHEN Parameter = 'RegionWest' THEN 'A,C'
        WHEN Parameter = 'RegionEast' THEN 'B,D'
  ELSE Country) AS CountryName, Sales
FROM yourTable

If is the WHERE case I need more of your SQL to understand your question.


Database User Securables Question Learning sql server over here and I'm trying to figure out how to see the securable permissions I have added via T-SQL , in SSMS

For example, I have a database user called "LoginUser1" in Database1 and I granted "grant create table, create view to LoginUser1".

I'm trying to see this permission in SSMS. I go to the properties for the user in this database but i'm not seeing anything. What am I missing?
Thanks!

Query presented by @S4V1N will do the trick. If you have to see it via GUI using SSMS you can select:

Database → Properties → Permissions → Select the user LoginUser1 from Users or roles: list.

Select Explicit, you will see this.

Select Effective, you will see this.


Is it possible to change between SELECT column based on an "IF" statement? and use INNER JOIN variables? I have a table of 'mapped_products':

  product_id | price

A table of 'product_id_to_name':

  product_id | product_name

(please don't ask me why, this is an already built web-app i am working on).

A table of 'non_mapped_products':

  product_id | product_name

and a table of 'orders_history':

  prodeuct_id | order_date

My task was to get a list of 'product_names' that were previously ordered (by checking in the 'orders_hisoty' table).
This is the query for this situation:

SELECT `non_mapped_products`.`product_name`
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

Now I noticed that I have the same products in my 'non_mapped_pruducts' within my 'mapped_products' table - same 'product_id', different 'product_name':
I want to use the "product_name" if exist on my 'mapped_products' table instead of the one in tne 'non_mapped_products' table - if they do not exist I want to use the current 'product_name' from within the 'non_mapped_products'

I'm not so sure how to write this kind of query, it seems like an if/case statement under the select with an inner join (?)

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` )
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

***** It's important not to use the orders_history table (it's a huge table, and indexed badly).

Using MySQL database.

SELECT

    CASE
        WHEN mapped_products.product_id = non_mapped_products.product_id
        THEN product_id_to_name.product_name
    ELSE
        non_mapped_products.product_name
    END
        AS product_name

FROM non_mapped_products

INNER JOIN mapped_products
    ON mapped_products.product_id = non_mapped_products.product_id
INNER JOIN product_id_to_name
    ON product_id_to_name.product_id = mapped_products.product_id

WHERE EXISTS ( SELECT 1  FROM orders_history WHERE orders_history.product_id = non_mapped_products.product_id)

returns :
duplicate product names only from the mapped_products table

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` )
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

can be re-written to:

SELECT COALESCE( product_id_to_name.product_name,  non_mapped_products.product_name )
FROM non_mapped_products
    WHERE EXISTS ( SELECT 1
                   FROM orders_history
                   WHERE orders_history.product_id = non_mapped_products.product_id)
LEFT JOIN product_id_to_name ON
    product_id_to_name.product_id = orders_history.product_id


Is the SQL Server Express memory and CPU limit per instance? If I have 8GB of RAM in a server and I run 4 instances of SQL Server Express, will the total memory limit used by SQL Server be 1GB or 4GB?

Would it be advisable to run multiple instances like this to enable each database to make better use of resources (assuming that the server has plenty of resources)?

If I have 8GB of RAM in a server and I run 4 instances of SQL Express,
  will the total memory limit used by SQL Server be 1GB or 4GB?

Each instance can use up to 1GB of memory for the buffer pool.  Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool.  In your case, the maximum memory used by the four instances for buffer pool would be 4GB.

To confirm, I started two instances of the SQL Server 2008 Express Database Engine, performed some activity to load up the (separate) buffer pools, and then looked at per-instance memory utilization in a number of ways, for example using DBCC MEMORYSTATUS or by counting the number of buffers using the sys.dm_os_buffer_descriptors DMV.

The physical memory usage numbers below were obtained using simultaneous queries against the system DMV sys.dm_os_process_memory on each instance of the database engine:

SELECT
    dopm.physical_memory_in_use_kb
FROM sys.dm_os_process_memory AS dopm;

Output:

╔═══════════╦═══════════╗
║ Instance1 ║ Instance2 ║
╠═══════════╬═══════════╣
║   1102872 ║   1059812 ║
╚═══════════╩═══════════╝

Each of these is slightly in excess of 1GB as total physical memory usage includes more than just buffer pool usage, as mentioned previously.

  Would it be advisable to run multiple instances like this to enable
  each database to make better use of resources (assuming that the
  server has plenty of resources)?

If the databases on each instance are functionally independent then it is at least workable to run multiple instances of Express in this way, though you would need to pay careful attention to configuration and maintenance may be made more complex.

You might be be better served using another edition, such as the fully-featured (and very cheap) Developer Edition, if the intended usage matches the licence).  You would need to say much more about the circumstances to get a clear answer on this point.


Pad an Integer with 0 mid string I have a table with a column that stores ID values (integers) that require a padding with 0s (zeroes) in the middle. I can pad left or right but, how do I pad in the middle?

For example:

4001 = 40001
4123 = 40123
4000 = 40000

So, any number 4XXX becomes 40XXX.

Comment interactions

Question:

  To clarify, once the values of the relevant column get the padding, are they meant to:

  be stored permanently or
  to be temporarily computed only (e.g., the padding serves display purposes only)?

Response:

They should be stored permanently.

If they are indeed INTEGERs, it should be simply

UPDATE
  yourtable
SET yourcolumn =
    FLOOR(yourcolumn/1000) * 10000 + MOD(yourcolumn, 1000)


Is one-to-one relationship normalized? Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table connected with a one-to-one relationship.

The advantage of the former is to avoid JOIN and have a quick access to all statistical data for the corresponding record.

The advantage of the latter is to keep the column tidy. The first column is read-intensive, and the second write-intensive. Of course, I think it has no significant effect on the performance, as I use InnoDB with row-level blocking.

In general I want to know if it is practical useful to separate different sets of data for a single record?

If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms.

To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as when you have subtypes with distinct predicates (columns).

The reasons you would use 1:1 relationships depend on your point of view.  DBAs tend to think of everything as being a performance decision.  Data modelers and programmers tend to think of these decisions as being design or model oriented.  In fact, there is a lot of overlap between these points of view.  It depends on what your perspectives and priorities are.  Here are some examples of motivations for 1:1 relationships:

You have some subset of columns that are very wide and you want to
segregate them physically in your storage for performance reasons.
You have some subset of columns that are not read or updated
frequently and you want to keep them apart from the frequently used
columns for performance reasons.
You have some columns that are optional in general but they are
mandatory when you know that the record is of a certain type.
You have some columns that logically belong together for a subtype
and you want to model them to fit well with your code's object model.
You have some columns that can only apply to some subtype(s) of an
entity super-type, and you want your schema to enforce the absence of
this data for other subtypes.
You have some columns that belong to an entity but you need to protect
these particular columns using more restrictive access rules (e.g. salary
on an employee table).

So you can see, sometimes the driver is performance, sometimes it is model purity, or just a desire to take full advantage of declarative schema rules.


The problem is you are joining unrelated tables.  If you don't join them, as in

SELECT NOW() AS 'Dorjan Masrussen',
        s.store_id,
        (select sum(p.amount)
           from payment p
           where p.rental_id in (
             select r.rental_id
               from rental r
               join inventory i
                 on i.inventory_id = r.inventory_id
               where i.store_id = s.store_id)) total_sales,
        (select count(*)
           from customer c
           where c.store_id = s.store_id) num_customers,
        (select count(*)
           from inventory i
           where i.store_id = s.store_id) count_inventory,
        (select count(distinct film_id)
           from inventory i
           where i.store_id = s.store_id) count_titles,
        (select sum(f.replacement_cost)
           from inventory i
           join film f on f.film_id = i.film_id
           where i.store_id = s.store_id) inventory_cost,
        (select count(*)
           from rental r
           join inventory i
             on i.inventory_id = r.inventory_id
           where i.store_id = s.store_id) num_rentals_out
  from store as s

you get:

+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| Dorjan Masrussen    | store_id | total_sales | num_customers | count_inventory | count_titles | inventory_cost | num_rentals_out |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+
| 2020-03-31 17:32:18 |        1 |    33679.79 |           326 |            2270 |          759 |       46205.30 |            7923 |
| 2020-03-31 17:32:18 |        2 |    33726.77 |           273 |            2311 |          762 |       46415.89 |            8121 |
+---------------------+----------+-------------+---------------+-----------------+--------------+----------------+-----------------+

Total sales and num_rentals_out differ from your correct output.  I wonder who is right.   The Sakila structure diagram, if I'm not mistaken, has it's crow's feet inverted.

The same result can be obtained, while querying inventory  only once, using a CTE, the Common Table Expression "WITH", as follows:

WITH idata as (
  select i.store_id,
         count(*) num,
         count(distinct i.film_id) num_films,
         sum(f.replacement_cost) repl_cost,
         sum((select count(*)
                from rental r
                where r.inventory_id = i.inventory_id)) num_rentals,
         sum((select sum(p.amount)
                from payment p
                where p.rental_id in (
                  select r.rental_id
                    from rental r
                    where r.inventory_id = i.inventory_id
                ))) sum_rentals
    from inventory i
    join film f on f.film_id = i.film_id
    group by i.store_id
)
SELECT NOW() AS 'Dorjan Masrussen',
        s.store_id,
        idata.sum_rentals total_sales,
        (select count(*)
           from customer c
           where c.store_id = s.store_id) num_customers,
        idata.num count_inventory,
        idata.num_films count_titles,
        idata.repl_cost inventory_cost,
        idata.num_rentals num_rentals_out
  from store as s
  join idata on idata.store_id = s.store_id

Querying the store table can also be dropped, since all we need is the store_id, which we also find in inventory.


MongoDB: replication headroom gone below 1 days (avg/sec) getting alert that "Replication Headroom has gone below 1 days(avg/sec)".
when checked OPS manager for replication lag, max lag I can see that is of 16 seconds. Oplog size has been set to 50gigs. also when checked rs.status(), there is no difference in time for "optimeDate" and "lastHeartbeat".
I'm not understanding how to resolve this alert. Also is there any way I can sync secondary and reduce replication lag to zero. Please suggest.
Concept explaining shrinking replication headroom/replication lags with its workaround welcomed.
Thanks!

"Replication headroom" means "how long time of changes your opLog can store". To get larger "replication headroom" you need to expand your opLog size at least one node, of course it is preferred to have same size opLog at every node.

Reason why this headroom has gone under one day, is that because you have more changes (opLog documents) nowadays, than before.


How to add multiple rows to a table that contains only a generated always identity column? This more of a Q/A question, perhaps someone is facing the same problem, so I'll ask a question and then answer it.

Say that we have a table defined as:

CREATE TABLE T (x BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY);

and need to generate one row for each row in another table. For one row we could use:

INSERT INTO T (x) VALUES (DEFAULT);

However:

INSERT INTO T (x) SELECT DEFAULT FROM SRCTABLE

does not work, and generates the following error:

SQL0206N  "DEFAULT" is not valid in the context where it is used.
SQLSTATE=42703

How do I add multiple rows to such table without resorting to a cursor?

One way is to use MERGE (best thing since sliced bread?).

MERGE INTO T x
USING (
    SELECT 1 FROM SRCTABLE
) y (dummy)
    ON 1=0 -- Always false
WHEN NOT MATCHED THEN
    INSERT (x) VALUES (DEFAULT);

This will add one row to T for every row in SRCTABLE.

It would be cool if we could grab the generated values with the use of transition tables, but it appears as if this does not work for MERGE (db2 10.5, have not checked 11 yet):

SELECT * FROM NEW TABLE (
    MERGE INTO T x
    USING (
        SELECT 1 FROM SRCTABLE
    ) y (dummy)
        ON 1=0 -- Always false
    WHEN NOT MATCHED THEN
        INSERT (x) VALUES (DEFAULT)
);

SQL0104N  An unexpected token "select * from new table (" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".
SQLSTATE=42601

Compare with:

SELECT * FROM NEW TABLE (
    INSERT INTO T (x)
    VALUES (DEFAULT)
);


This database has two foreign keys going to a single field, is it in 3NF? Firstly, here is a picture of the database diagram generated by SQL Power Architect.

I needed to create a user_id pair in order to match friend requests to users, but this has given me two foreign keys on the request table which link back to a single primary key on the user table. I stuck with this because PostgreSQL never flagged it as an issue.

As well as this, I have somehow ended up with a non-identifying relationship on the session to log tables.

Do I have a problem with the normalization of my database? Not all of the data depends on the tablename_id primary keys so do I need to remove them?

Normalization up to 3NF and BCNF is about FDs (functional dependencies) within a table. (We can also say that a database is in a certain normal form to mean that all of its tables are.) But FKs (foreign keys) are between tables, so they do not affect what normal forms a table is in.

(Some diagramming styles have lines that are FKs. Others have lines that are relationships represented by "association" tables. Some have both kinds of lines. Association tables have to be normalized too. You need to know what the relationship is in terms of the application.)

A FD (constraint) on a table says that when a subrow value for a (determining) column set only ever appears with the same subrow value for another (determined) column set or column. A column set that determines every attribute is a superkey. A CK (candidate key) is a superkey that contains no smaller superkey.

  A table is in 3NF if and only if, for each of its FDs X → A, at least one of the following conditions holds:
  - X contains A
  - X is a superkey
  - each attribute in A-X is contained in some CK

There is nothing wrong with having two FKs to the same table or superkey in a table:

  Just declare per what is true of your relationships/tables:

  a PK or UNIQUE NOT NULL declaration says that every subrow value in a column set is unique. Ie that the column set is a superkey. (A PK is
  just a distinguished UNIQUE NOT NULL.)
  a FK declaration says that a column list subrow value in referencing columns must also be in referenced superkey columns.

We can only guess at your FDs from the words you chose for table and column names. You need to tell us what they are before we can tell you what normal forms your tables satisfy. But your FKs and what they reference will have nothing to do with that.

PS Some of your tables seem to have one composite (multi-column) PK where the columns should really be two CKs, one simple (one column) and one composite.


Which one of these two contact management database designs is correct or best practice? In this first database design, column name starting from first_name to country exist in each table. The relationship of each table to table user_categories is ONE TO MANY.

In this second database design, there is no duplication of column name in each table. The relationship of table users and table shippers, suppliers, employees and customers are ONE TO ONE. Is the relationship correct?

The purpose of table shippers, suppliers, employees and customers is to store users based on its category in respective table just like the first design. I'm curious if this is a good practice since the columns of fourth tables is only its ID. Is there any column that I can add to those tables?

Could you please advice me if there are any better solutions?

In general. Recommended structure (assuming one user can fit into several categories):

users (user_id,
       name,
       -- etc.,
       primary key (user_id))

categories (cat_id,
            category_type, -- shipper, supplier, etc.
            name,
            -- etc.,
            primary key (cat_id))

users_to_categories (user_id,
                     cat_id,
                     -- etc.,
                     primary key (user_id, cat_id),
                     foreign key (user_id) references users (user_id),
                     foreign key (cat_id) references categories (cat_id))

If some user fits in more than one category there is more than one record in the last table with the same user_id and different cat_id.

  A user can only choose one category for example, supplier. If he wants to change to customer, he must delete his account first.

If so:

users (user_id,
       cat_id,
       name,
       -- etc.,
       primary key (user_id),
       foreign key (cat_id) references categories (cat_id))

categories (cat_id,
            category_type, -- shipper, supplier, etc., ENUM
            name,
            -- etc.,
            primary key (cat_id))


Migrating from a physical database to a cloud database What are the implications of moving a physical database created in Microsoft SQL server 2008R2, that makes use of advanced features like Full-Text Search on varchar(MAX) FILESTREAM BLOBS to SQL Azure?

What architectural changes are needed? What steps are involved in this transition? SQL Azure Migration Wizzard can't simply be the magic tool that automagicly makes the transition.
What are the compatibility issues that the migration tool can not fix and should be worked out "manually"?

Moving the data is pretty easy.  If you are using full text search that feature isn't available in SQL Azure yet.  Neither is FILESTREAM.  If you require full text search then Azure isn't for you.


Understanding multiple table join with aggregation I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2

CREATE TABLE main (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE link1 (
   id SERIAL PRIMARY KEY,
   main_id integer NOT NULL,
   CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id)
);

-- link2 is similar to link1

SQL Fiddle

Why does the query below give a product of counts (rather than sum) when the count is non-zero in both columns.

SELECT main.id, COUNT(link1.main_id) + COUNT(link2.main_id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
LEFT JOIN link2 ON main.id=link2.main_id
GROUP BY main.id

What you see is a "proxy cross join". Aggregate first, then join:

SELECT m.id, COALESCE(l1.ct, 0) + COALESCE(l2.ct, 0) AS total_ct
FROM   main m
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link1
   GROUP  BY main_id
   ) l1 ON l1.main_id = m.id
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link2
   GROUP  BY main_id
   ) l2 ON l2.main_id = m.id
ORDER BY m.id;

SQL Fiddle.

Do not multiply rows with multiple unqualified joins and count(DISTINCT ...) later to fix the mistake. It happens to work in this case since counting DISTINCT link1.id / link2.id coincides with the desired result, but it's needlessly expensive and error prone.

Detailed explanation and a couple of syntax variants in these related answers on SO:

Multiple array_agg() calls in a single query
Two SQL LEFT JOINS produce incorrect result