What exactly is an unstructured data and why to use Non-relational DBMS for that? I read some posts online about Unstructured data like this one.
It says

Unstructured data is information that is not arranged according to a pre-set data model or schema, and therefore cannot be stored in a traditional relational database or RDBMS.

But RDBMS allow BLOB storage. So we can store unstructured data in traditional RDBMS.
The examples that they have given for unstructured data are Email, Text files, Media files etc. First of all, how are all these types unstructured? And even if they are, how can a Non-relational DBMS like MongoDB store/retrieve it better than RDBMS' BLOB storage?
A common example for unstructured data that I found almost everywhere is storing products' information in a e-commerce company like Amazon. Every product has different attributes and so in a NoSQL DBMS like MongoDB we can have a collection called Products in which we will have all the products' information. But we can store this data using RDBMS by creating a separate table for each product. This will probably a much cleaner way to store the data. Then why to use a non-relational DBMS for that?
So my questions are:

What exactly is an unstructured data in the context of databases?
How is a non relational DBMS like MongoDB better for unstructured
data than a RDBMS?
Can you give a real life example of unstructured data?

Thank you!

Unfortunately the answer to when a NoSQL database system is better than a RDBMS is a little subjective which is why it's highly debated. A lot of misinformation has caused the incorrect connotation that a NoSQL database is faster than a RDBMS, particularly with big data, which isn't necessarily true. This connotation is based on the origination story of NoSQL databases being used by companies who do have big data who were trying to solve a scale problem actually, but not because of scaling performance rather one of flexibility to allow them the ability as developers / infrastructure administrators to scale faster. (In my opinion, with the availability of modern Cloud services and decoupled services like Azure and AWS, that problem is mostly solved in an alternative way now.) But to this day, those same companies, such as Facebook still leverage a RDBMS in addition to a NoSQL database system, for likely similar amounts of data, because it's not a question of database performance, rather of flexibility and using the right tool for the right job dependent on use case.
At a high level, as previously mentioned, NoSQL databases have the advantage of flexibility at the trade off of consistency, which is a trade-off between the ACID Database Principals for eventual consistency. This flexibility allows a NoSQL database to store unstructured, semi-structured, and highly variable structured data; and it allows that database to be distributed easily when horizontally scaling across a multi-node database cluster. Because of the flexibility with the schema of the data it can store, it doesn't have to worry about schema changes in one node vs another node in a multi-node cluster. Additionally, because of its ability to be eventually consistent, it generally doesn't have to worry about having 100% of the same data across every node in the cluster.
Because of its flexibility for scaling horizontally, one can loosely argue (at a high level) that this is where the performance benefit comes from when using NoSQL over a traditional RDBMS. But the other tradeoffs are your ability to transform and query the data become limited when you're dealing with a NoSQL database due to it's lack of a consistent schema. I want to be objective, so I will credit MongoDB with offering a multitude of ways to query the data which I believe are also a little more procedurally programmatic by design, so may be a little user-friendly to a developer who is not very experienced with traditional relational logic. But there's only so much one can do when guaranteed eventual consistency at best as opposed to the actual guaranteed consistency of a RDBMS.
As far as actual use-cases for a NoSQL database, it depends heavily on the schema of the data (or lack thereof) and the developer's specific use cases and types of querying they'll need to support. Some of the examples you've read are potential use cases, such as an Email for example. Emails are semi-structured in schema, as far as I would define them. They have a few consistent fields like the From, SentDateTime, ReceivedDateTime, and Subject but they all have highly variable fields like the To, CC, BCC and the Body. So a NoSQL database could make sense in the scenario where the type of querying you'll ever do has predicates dependent only on the From and/or SentDateTime field. Those would be your keys in your Emails table, and the Body of the message and the To field will be the rest of your entity (maybe in a JSON format) that's stored in the table. (Note the term table is used loosely here for conceptualization.)
Now of course you're saying to yourself "but you can accomplish just the same in a RDBMS" - which is true, but again the flexibility of NoSQL with its eventual consistency allows you to then take the above example table and shard it across multiple nodes more easily when horizontally scaling. So while even some of the main features of a NoSQL database are available in a traditional RDBMS (in a sense I almost see NoSQL being a subset of RDBMS from a features perspective) it's flexibility and the rules it has to follow differ enough from a traditional RDBMS, allowing it a place in the world we live in.
That being said, over time a lot has changed, even NoSQL systems in their original sense have changed a lot and adapted to the progression of the database world (or the ones who haven't die off). The lines between NoSQL and RDBMS continue to blur (for the better) as both grow to accommodate the things they're missing from one or the other. For example, ACID compliant NoSQL databases now exist, and RDBMS databases with much easier horizontal scaling capabilities exist as well.


What is the DPT_ENTRY_LOCK wait type? We have a SQL Server 2016 Availability Group with 3 servers.  Twice in the past 5 days we have had performance issues.  8a is our primary server, and 8b and 8c are the secondaries.

We started having issues right before 8PM.  We saw timeouts as well as 502 Bad Gateway errors.  On 8b and 8c we see a huge spike in DPT_ENTRY_LOCK waits as well an increase in LCK_M_IS, LCK_M_S, and HADR_SYNC_COMMIT on the primary at the same time.  Unfortunately we don't know which server of the 3 was timing out because all 3 sit behind a load balancer for read-only connections.

We are currently trying to track down what was locking stuff up but can't figure out what the DPT_ENTRY_LOCK type is on the secondaries.  When SQL Skills doesn't have the answer I start to worry.

DPT_ENTRY_LOCK waits are caused when the workload is constantly modifying the same pages (so constant updates would cause this to show up). Practically nothing has been written around this subject so trying to get definitive answers (along with supporting evidence) is more than a challenge, as such I'll give you what I've seen from experience on my side. You would probably need to open a CSS call at this point for something more definitive.

When you have a large number of transaction on your primary that have to be flushed to the secondary, and you have a high number of reads on a secondary that access the same tables, you end up with this lock, or a DIRTY_PAGE_TABLE_LOCK wait.

These happen when there is a lot of work going on, meaning that there are a large number of dirty pages that need to be flushed, expect to see very short wait times on this usually. You wouldn't expect this thanks to RCSI on the secondary, but it happens, and I've only seen it in the case where there are a large number of writes on the primary and read calls on the secondary.

As for a way around this, you could try using indirect checkpoints (if not already enabled) which uses sorted, partitioned lists to track dirty pages rather than the random ordering used by prior versions - see SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

There is a possibility that this could cause unforeseen performance issues on your primary, so you should test it at load prior to implementing on a production system. And there is no guarantee that this will work. I'm actually about to try this very thing for some systems that I'm experiencing the same issues with.

You might expect to see PARALLEL_REDO_TRAN_TURN waits as well, which are caused by page splits.


mysql general_log only shows a hex number instead of SQL? When I enable sql logging to table like this:

set global log_output = 'table'
set global general_log = 'on'

Do some queries then look at the log table like this:

select * from mysql.general_log
order by event_time desc

I dont see my sql, I see hex numbers like this in the argument field or blank ('') and no SQL at all:

0x53484F5720435245415445205441424C4520606D7973716C602E6067656E6572616C5F6C6F6760

What is going wrong?

Use this query to get the argument as text:

select a.*, convert(a.argument using utf8) from general_log a;


ERROR Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) We are facing the issue in replication that we are not getting any error in Show slave status\G output rather facing the following issue in Error Log File

[ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

Data for some tables is not being populated from Master Server and Slave does not update it self from the master.

We are using windows 2008 R2 and on Master Version is 5.6.19 and on Slave it is 5.6.26

Things looked so far are as below:
1. Change the master heartbeat value but it didn't work
2.increased the slave_net_timeout, Increased max_allowed_packet on master   and slave
3.Rebuilding Slave
4.Slave user permissions are also correct
5.Ping and telnet from both server also works and  windows firewall is off

Any help is much appreciated

Your network connectivity from slave to master was lost due t some reason and after the
Try the following:
STOP SLAVE;
START SLAVE;


How to sync two tables which are on different databases and servers using SSIS? I am relatively new to SQL Server's tools. I am on the following situation:

On several stored procedures on a database D1 on server S1, I need to use data that is stored on a database D2 on server S2, on a table that is being constantly updated.

As far as I've read, I can use SQL Server Integration Services to automate this task and sync two tables between two different servers/databases.

What I want to do is to have a "mirror" table from S2/D2 on S1/D1. So my stored procedures could use that table transparently.

Could you give me some any guidance? Thanks so much.

It's not clear what you're try to do.

It might be that you want a read-only copy for reporting; in which case you should probably consider log shipping, db mirroring or the like.

If you just want to be able to query data from another server, linked servers are an option. The queries would always return fresh data, but there's a performance hit to consider.

SSIS is the correct tool, if you want periodic secondary copies updated batch-style.

Service Broker is almost certainly far too complex for your scenario, but addresses asynchronous cases.


xp_cmdshell only sends first character of string I am trying to use xp_cmdshell to launch a python script with a list as its input but for some reason its only passing the first character of the string to command line. The result I get from the code below is "'D' is not recognized as an internal or external command, operable program or batch file.". If I add the START command before the path it just replaces 'D' with 'S'. This particular row I'm trying to test with concatenates to 241 characters so I'm not bumping up against the varchar limits for xp_cmdshell. Using MSSQL 2008R2 on Windows Server 2008R2.

DECLARE
    @inspection varchar

SELECT
    @inspection = 'D:/Python27/ArcGISx6410.1/python.exe D:/GIS-DATA/Scripts/whydrant_update_01.py [''' + COALESCE(FACILITYID, '') + ''',''' + COALESCE(MAKER, '') + ''',''' + COALESCE(HYDRCOND, '') + ''',''' + COALESCE(BASECOL, '') + ''',''' + COALESCE(CAPCOL, '') + ''',''' + COALESCE(STEAMSIZE, '') + ''',''' + COALESCE(DISSIZE, '') + ''',''' + COALESCE(CAST(FLOW AS varchar), '') + ''',''' + COALESCE(CAST(PRESSURE AS varchar), '') + ''',''' + COALESCE(VALVELOC, '') + ''',''' + COALESCE(ACTIVE, '') + ''',''' + COALESCE(OPERABLE, '') + ''',''' + COALESCE(OPERHARD, '') + ''',''' + COALESCE(CORRODED, '') + ''',''' + COALESCE(CLEARING, '') + ''',''' + COALESCE(PAINTING, '') + ''',''' + COALESCE(MARKER, '') + ''',''' + COALESCE(NOTE, '') + ''',''' + COALESCE(CAST(created_date AS varchar), '') + ''',''' + COALESCE(USER_, '') + ''']'
FROM sde.sde.a1288
WHERE FACILITYID = '2294'

EXEC xp_cmdshell @inspection

The problem is with your variable declaration varchar.

The command_string is   varchar(8000) or nvarchar(4000)

so define your variable along with proper length  to avoid string truncation

  DECLARE
      @inspection varchar

should be

  DECLARE
      @inspection varchar(8000)

couple of things as well (since I dont know Python).

Make sure you print or select @inspection and see that you can run the produced command successfully.
Use a variable for success or failure and raise an error message when the command fails.

e.g.

declare @result int
DECLARE @inspection varchar(8000)
exec @result = master..xp_cmdshell @inspection
if @result <> 0
begin
    print 'Python script errored out !!'
    RAISERROR ('Python script errored out', 16, 1)
end


What's the best way to update several (200k)rows in a database? I've got a database that I need to update a column in some rows. I've got the data from my logs and I wrote php script that processes each log and extracts the data in to a file with an update for each record that needs it.

I broke it down in to each month but running this is taking forever. Is there a better way to do it?

The file is basically a .sql file filled with 205k rows of

update table set ended_at = '2019-01-31 23:59:30' where uuid = '304ad6ea-b949-42f5-96a0-a7a550e6126d';

Is there a better way?

I would suggest that the schema is poorly designed if you have 205K rows with the identical uuid.

Furthermore, if you need all of them to have the same ended_at value, then there should be some other table that has, for example, one row with uuid and ended_at.  The JOIN to get the data during a SELECT will be more than compensated for by having removed ended_at from the big table.

Perhaps I am being overly critical.  If so, please provide some more insight into what the columns mean, and where the uuids come from, etc.

The best way to update "many" rows in a "huge" table is to walk through the table by the PRIMARY KEY, and update a thousand rows at a time.  More details on chunking:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


How to set SSRS parameter Default to uncheck specific options? In a multi-select Parameter for SSRS (2008), I want the default state of an input parameter to be such that one option is unchecked while all others are checked. Like in the image that follows:

Is there any way in SSRS that this can be accomplished? I guess there is no way to refer to a "checked" state so this can be handled?

In the parameter preference pane, there's one tab for available, and one tab for default. You can pick completely different details for each set, including different datasets, one with the available options, and one selecting just the ones you want set as defaults.


How can I create a fresh copy of a large database on SQL Server? I have a very large database (400GB) that is now corrupted. I tried repairing it with the repair_allow_data_loss option, but that did not fix the problem so my next option is to restore to a previous version. What I want to do first it to create a new fresh copy of the database. So a database with all the tables/views/procedures/permissions like the existing one and then restore to it an older backup.

How can I create this fresh copy?

Taking into account a 400GB database, its upto you to choose any of the below routes :

Method 1 :  Backup and Restore

Depending on your hardware and the amount of activity going on, it will be slower than Method 2 - BCP OUT / BCP IN

Below is the script that will help you :

/************************************************************************************************************************************************
Author      :   KIN SHAH
Purpose     :   Restore database on the same server with different Name
DATE        :   06-03-2013

Note        :   1. Change as per your environment by replacing the bits marked by "--- CHANGE HERE !!"
                2. If you are using SQL 2008 and up depending on Enterprise or Standard Edition, you can add "COMPRESSION" to the backup command as well.
                3. As a cleanup task, this script requires xp_cmdshell to delete the backup file on the server. I have commented that out for now.
************************************************************************************************************************************************/
declare @dbname NVARCHAR(MAX)

set @dbname = 'test1' --- CHANGE HERE !!

if object_id('tempdb..#temp1') is not null
            begin
            drop table #temp1
            END
if object_id('tempdb..#temp2') is not null
            begin
            drop table #temp2
            END
if object_id('tempdb..#temp3') is not null
            begin
            drop table #temp3
            END

 --backup path goes here
DECLARE @path NVARCHAR (MAX)
-- Here the path is hard-coded  as all the server has 'D:\2restore' folder. This can be made as input parameter also !
SET @path = 'C:\test' --- CHANGE HERE !!
 -- generates Archive database name
DECLARE @archivedbname NVARCHAR(MAX)
SET @archivedbname = @dbname+'_Archive'+'_'+DATENAME(MONTH, GETDATE())+'_'+ CAST(DATEPART(YEAR, GETDATE()) as VARCHAR(MAX))
--PRINT @archivedbname

-- check that the database to be archived is  there and is not a system database ...

IF  @dbname in (SELECT NAME FROM MASTER..sysdatabases WHERE DB_ID(NAME)>4)
    BEGIN
        select 'The database is correct. starting Archiving Process .....'
            BEGIN TRY
                        select * INTO #temp1
                            FROM MASTER.sys.master_files
                            WHERE database_id = cast(DB_ID(@dbname) AS NVARCHAR(MAX))

                    -- now get the logical and physical names of the database to be archived
                    -- type 0 = data
                    CREATE TABLE #temp2 (ldata NVARCHAR(MAX), pdata NVARCHAR(max))
                    DECLARE @ldata NVARCHAR(MAX)
                    DECLARE @pdata NVARCHAR(MAX)
                    SELECT  @ldata = 'select [name],[physical_name] from #temp1 where  type = 0 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                    INSERT INTO #temp2
                    EXEC (@ldata)

                    SELECT @ldata = ldata FROM #temp2
                    --PRINT @ldata
                        SELECT @pdata = pdata FROM #temp2
                        SELECT @pdata = left(@pdata, len(left(@pdata,LEN(@pdata)-4))-len(@ldata))+@archivedbname+'.mdf'
                        --PRINT @pdata
                        -- type 1 = log
                        CREATE TABLE #temp3 (llog NVARCHAR(MAX), plog NVARCHAR(max))
                        DECLARE @llog NVARCHAR(MAX)
                        DECLARE @plog NVARCHAR(MAX)
                        SELECT  @llog = 'select [name],[physical_name] from #temp1 where  type = 1 and database_id ='+ cast(DB_ID(@dbname) AS NVARCHAR(MAX))
                        INSERT INTO #temp3
                        EXEC (@llog)
                        SELECT @llog = llog FROM #temp3
                        --PRINT @llog
                        SELECT @plog = plog FROM #temp3
                        SELECT @plog = left(@plog, LEN(left(@plog,LEN(@plog)-4))-LEN(@llog))+@archivedbname+'_log.ldf'
                        --PRINT @plog

                        -- now we will take backup of the database that is specified ....
                        select 'Taking backup of database ' + @dbname

                        DECLARE @sql NVARCHAR (MAX)
                        -- use compression using Redgate backup
                        select @sql = 'backup database '+@dbname+' to disk ='''+@path+ '\'+ @dbname+'_FULL_'+convert(varchar(10),getdate(),112)+'.bak'+''' with init, stats = 10'

                        --print @sql
                        exec(@sql)

                        select 'The backup is done for ' + @dbname
            END TRY
            BEGIN CATCH

                    SELECT
                        ERROR_NUMBER() AS ErrorNumber,
                        ERROR_SEVERITY() AS ErrorSeverity,
                        ERROR_STATE() AS ErrorState,
                        ERROR_PROCEDURE() AS ErrorProcedure,
                        ERROR_LINE() AS ErrorLine,
                        ERROR_MESSAGE() AS ErrorMessage

            END CATCH

                -- now restore the database as archive database
        BEGIN TRY
         select 'Starting restore part for ' + @archivedbname

             SELECT @sql = 'restore database '+@archivedbname + ' from disk = ''' + @path +'\'+ @dbname+'_FULL_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'+''' with recovery, stats = 10 '+','+ ' move '''+@ldata+''' '+'to ' +''''+ @pdata +''''+','+ ' move '''+@llog+''' '+ ' to '+ ''''+ @plog +''''
                --print (@sql)
                exec (@sql)

                select 'Restore is done sucessfully ! And the new database name is '+@archivedbname + '!!'

        END TRY
        BEGIN CATCH

                SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_PROCEDURE() AS ErrorProcedure,
                    ERROR_LINE() AS ErrorLine,
                    ERROR_MESSAGE() AS ErrorMessage
        END CATCH

select 'Database is restored as Archive, so now lets do some clean up task ...'
-- delete the backup file
select @sql= 'exec master.dbo.xp_cmdshell ''Del '+@path+'\'+ @dbname+'_full_'+ CONVERT(VARCHAR(8), GETDATE(), 112)+'.bak'''

print (@sql)
--EXEC (@sql) --- CHANGE HERE !! (if you want to delete the backup file after the restore is done !!

    END

ELSE
    BEGIN
            select ' The database is INCORRECT ! Check if the database exists or is not a system database'
    END
GO

Method 2 - BCP OUT / BCP IN

Script out the database SCHEMA_ONLY and recreate an empty database on the same server with a different Name.

Use BCP OUT and BULK INSERT to insert data. Below script will help you with that :

    /************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or same server but different database ***************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe  --- CHANGE HERE !!
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format --- CHANGE HERE !!
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema
order by schema_name(schema_id)

--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in --- CHANGE HERE !!
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
        +  QUOTENAME(name)
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path --- CHANGE HERE !!
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','')
        +'.dat''
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',
        TABLOCK
        )'  + char(10)
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go'
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema
order by schema_name(schema_id)


Foreign keys update automation I'm on SQL Server 2014 and I need to clean a table from duplicates. Problem is: if a duplicate has references in other tables, that references should be updated to reference the chosen non-duplicate entity.

E.g., say a have a Person table:

+------+-------+-------+------------------------+
|  id  | fname | lname | mail                   |
+------+-------+-------+------------------------+
| 1111 | John  | Smith | john.smith@example.com |
| 2222 | J.    | Smith | john.smith@example.com |
| 3333 | john  | smith | john.smith@example.com |
| 4444 | Smith | John  | john.smith@example.com |
+------+-------+-------+------------------------+

I need to delete 2222, 3333, 4444 and replace references around the database to these with a reference to 1111. After this operation, a unique index on mail will be created.

My approach would be gathering all the distinct mail and building a  id => [ ids ] map (e.g. 1111 => [ 2222, 3333, 4444]) and with a scripting language like Perl or PHP update all the tables which may have references to the duplicates and setting the correct id.

Since there are thousands of users and hundreds of tables with relations to them, I wonder if operations like this could be done directly in SQL Server function, with something like:

DELETE FROM [Person] WHERE [id] IN (2222, 3333, 4444) UPDATE REFERENCES WITH 1111

I think this will do it
I changed this answer as I may have read the question incorrectly
Look at edit history if you are looking for something different

as for the fk

with CTE as
(  select id, mail
        , row_number() over (partition by mail order by id) as rn
   from table
)
update fk1
   set fk1.fkID = cte1.ID
  from fk as fk1
  join cte as CTE2
    on CTE2.ID = fk1.fkID
   and CTE2.rn > 1
  join cte as CTE1
    on CTE1.mail = CTE2.mail
   and CTE1.rn = 1;

run above for addition fk

delete *
from
(  select id, mail
        , row_number() over (partition by mail order by id) as rn
   from table
) as t2
where rn > 1;


Stopping cascading logical replication Given two primary PG12 servers, A and B, let's setup logical replication from A to B.
A --> B
Both servers can and do receive writes, and don't use sequences or unique constraints so there is no conflict.
Is it possible to set up replication from B to A, without replicating the data replicated from A?
Writes from A:
A --> B -x-> A
Writes from B:
B --> A -x-> B
End result is both databases have the same data set.

No, that is not possible. Replication cannot be cyclical.
You seem to be looking for a multi-master solution for PostgreSQL, but there are no open source solutions for that.
If I get that right that you have no unique or primary key constraints on the table, logical replication will not do what you want if the table contains duplicate rows.


Oracle SQL query - select or rows without all ids in other tables I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table).

example of insert to tables:

INSERT INTO CUSTOMER (ID, SECOND_NAME, FIRST_NAME, MIDDLE_NAME, BIRTHDATE, PASSPORT, PHONE_NUMBER, ID_DISCOUNT, ADDRESS) VALUES (141, 'w', 'w', 'w', '15-APR-89', '521522221', '(847) 256-4120', null, 'new 4');
INSERT INTO ACCOUNT (ID, ID_CUSTOMER, DATE_OPEN, ID_DEPARTMENT) VALUES (121, 140, TO_DATE('2019-11-12 23:41:12', 'YYYY-MM-DD HH24:MI:SS'), 4);
INSERT INTO OPERATION (ID, ID_CUSTOMER, DATE_OPERATION, TYPE, AMOUNT_TRANSFER, ID_ACCOUNT_SENDER, ID_ACCOUNT_RECEIVER) VALUES (131, 5, TO_DATE('2019-11-12 23:31:56', 'YYYY-MM-DD HH24:MI:SS'), 22, 15, 9, 8);

some queries that I tried:
all accounts without operations, but I don't know how select all customers that only contain only such accounts or no accounts even.

SELECT A1.ID, C1.ID
FROM ACCOUNT A1
  JOIN CUSTOMER C1
    ON C1.ID = A1.ID_CUSTOMER
WHERE (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1))
ORDER BY A1.ID;

I try following query, but it give me incorrect result:

SELECT C1.ID
FROM CUSTOMER C1
  FULL JOIN ACCOUNT A1
    ON A1.ID_CUSTOMER = C1.ID
WHERE A1.ID NOT IN (SELECT A1.ID
FROM ACCOUNT A1
WHERE A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT  O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1))
ORDER BY A1.ID;

first approach for customers without account

DELETE FROM CUSTOMER WHERE
ID NOT IN (SELECT CUSTOMER_ID FROM ACCOUNT);

the inner select gives you the list of customer_id's in the accounts table - the remaining data-sets in the customers table can be deleted after your first sentence.

based on that you build a similar select for the case without operations

DELETE FROM CUSTOMER WHERE
(
  (ID NOT IN (SELECT CUSTOMER_ID FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_sender FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_receiver FROM OPERATION) )
);

both where conditions can be combined using OR

of course if you first want to run a select to review what is going to be deleted, you can replace "DELETE" with "SELECT *" in the 2 queries I posted


Proper use of FULLTEXT index for a column storing unique hash value We have an InnoDB table called user, where we have a column called token (varchar(128)) where a unique md5-like value that represents whether a user is authenticated or not, is stored. This column can be filled or empty/NULL, depending on whether the user has authenticated yet or not.

Whenever an API is being called to make a change to other data, we always check whether the token sent with the API is valid or not. The query would be something like:

select *
from `user`
where token ='96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0'

As you can imagine, this query runs into seconds, because it is a query on a varchar field. Now I'm trying to fix the query speed.

I tried adding a FULLTEXT on the token field, but it won't help in the = query. So I'm trying to use MATCH() AGAINST() query as follows:

select *
from `user`
where MATCH(token) AGAINST('96e1e3c72ce665bc7aeb93a357801fa3296e500b1aed8016e5342e744990cd0094c98857ae5f4b0a73cc6dfebd11a8491a5ba8448e6ddff8a631eb8213c254d0')

But this query does not return any result. When I check use EXPLAIN, I get:

id   1
select_type  SIMPLE
table    user
type fulltext
possible_keys    token
key  token
key_len  0
ref  NULL
rows 1
Extra    Using where

Here, the key_len being 0, and rows saying 1, even though no results were returned, are suspicious to me.

Now I'm thinking even FULLTEXT isn't the solution - it doesn't seem to be working for completely unique values with no common words - not usual text.

Can you help me understand:

Why FULLTEXT index with MATCH() AGAINST() is not bringing any results?
What is the best way to index the token field and reduce query time?

No, I "can't imagine".  That is, unless you did not have INDEX(token), which is the index that would fast.  Don't use FULLTEXT in this situation.

To discuss further, please provide SHOW CREATE TABLE and EXPLAIN SELECT for the case of token = '...'.


Mechanics of partitioning Imagine the following hypothetical database structure.  Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).

I want to partition my SQL Server 2008 database by CountryID.  As you can see, CountryID is only in the first 2 tables (e.g. amCountries and amOrders).  Given that (as far as I understand) that partitioning is done at the table level, do I need to add CountryID to the remaining tables to be able to partition them?  Or is there some type of cascade available in SQL Server that will allow me to skip adding CountryID to everything?

Partitioning is only at the table level and is for managing partitions of that table.  Typically the advantage of partitioning is in swapping in and out data and for getting additional control of the granularity of storage and backup.  To some extent it can also help with performance if there are shared partition columns in a join, but that probably shouldn't be a reason to partition.

I understand your thinking about the child tables, but if you want that data also partitioned by country (for some of the above reasons), you would have to add a column because the partition function is restricted.

What is your thinking about why you want to partition in the first place?

http://msdn.microsoft.com/en-us/library/ms190787


Improve performance on write-only table? I am using MySQL 5.5 with innoDB. The basis of my server is Netty, JDBC and BoneCP.

I have a log table that contains user inputs(HTTP header, request body etc). This table will only be read very rarely for reasons like security and data recovery. Therefore the read performance is not something we care about.

There are five columns in this table.

Name       | Type
--------------------------------------------------
userNumber | medium integer
logTime    | timestamp
header     | varchar(100)
body       | varchar(200)

What are some tips that will improve the insert performance?

I just read about blackhole engine in mysql today. This could fit your requirement I believe, if you have a slave set-up. You can dump all the data into your master but will get saved only in slave. The load on your master server should be minimal.


Can I grant access to a MySQL user for a single partition of a table? Is there a way, in MySQL, to grant access to a single partition of a table to a specific user? I see no way in the doc for that, but maybe I'm missing it...

Here's a test case:

CREATE TABLE testing (
    id INT UNSIGNED NOT NULL
)
PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);
INSERT INTO testing (id) VALUES (2),(20),(200),(2000),(20000),(200000);
SELECT * FROM testing;

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT USAGE ON *.* TO 'foo'@'localhost';
GRANT SELECT ON my_test.testing TO 'foo'@'localhost';
FLUSH PRIVILEGES;

-- Now use foo@localhost: you shouldn't see 2k, 20k and 200k values
SELECT * FROM testing;

What would the proper GRANT syntax be?

I'm not entirely sure you can do what you are after.

I would have thought a better option would be to create a view that only shows the data you want:

CREATE VIEW view_testing AS SELECT id FROM testing WHERE id <= 1000;

Then give your user only permission to access that view:

CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT SELECT ON my_test.view_testing TO 'foo'@'localhost';

(n.b. the user creating the view needs access to the underlying table)


Couldn't restore database and couldn't change Single user mode to Multi user mode I am unable to restore a database.

The database is stuck in single user mode.  I cannot change to multi-user mode.

I was restoring the SQL Server database from Prod to Dev as I have done many times without issue.

I run the following scripts:

--1
use master

go

ALTER DATABASE [Acct] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Go

The database changed to single user mode.

--2
RESTORE DATABASE [Acct] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\ProdAcct.bak'
WITH  FILE = 1,  MOVE N'ProdAcct' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct.mdf',
 MOVE N'ProdAcct_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Acct_log.ldf',
 NOUNLOAD,  REPLACE,  STATS = 10
GO

Error: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

--3 I run the following script to see the connection

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--4 kill the connection
Kill 85
Kill 51

--5
exec sp_dboption 'Acct', 'single user', 'FALSE'
go

and I've tried:

ALTER DATABASE [Acct] SET MULTI_USER
Go

Both commands fail.

--6
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'Acct'
go

--7 connection 51 was  accessing
kill 51

But I wasn't able to kill it.

At this point I had to call the application owners and asked them to stop the applications they were running. After they stopped their app, I was able to restore the database.

Question:

What kind of mistake did I make to run into this kind of problem?
Is there a way I am able to restore without asking people to stop their application whenever I want to refresh the Dev database?

Here is what I do to ensure that I get the ONE (1) connection for SINGLE_USER.

USE MyDB;  -- Use the database you want to make SINGLE_USER
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE ----; /* Or whatever you need to do. */
GO
ALTER DATABASE MyDB SET MULTI_USER;
GO

The trick is to be in the database already when you switch to SINGLE_USER mode.  This way, you are the SINGLE_USER and nobody else can get in until you return the database to MULTI_USER.

NOTE: If you exit the database while in SINGLE_USER mode someone else can take over the database.  This can also cause you problems.


Why does InnoDB store all databases in one file? It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default).

I understand that InnoDB will map the location of data in the file by individual index files for tables, but I do not understand why it mixes all data in one file. And more importantly, why mix the data of all databases on the server?

An interesting feature of MyISAM is that one can copy/paste a database folder to another machine and then use the database (without a dump).

The architecture of InnoDB demands the use of four basic types of info pages

Table Data Pages
Table Index Pages
Table MetaData
MVCC Data (to support Transaction Isolation and ACID Compliance)

Rollback Segments
Undo Space
Double Write Buffer (background writing to prevent reliance on OS   caching)
Insert Buffer (managing changes to non-unique secondary indexes)

See the Pictorial Representation of ibdata1
By default, innodb_file_per_table is disabled. This causes all four info page types to land a single file called ibdata1. Many people try to spread out the data by making multiple ibdata files. This could lead to fragmentation of data and index pages.
This is why I often recommend cleaning up the InnoDB infrastructure, using the default ibdata1 file and nothing more.
Copying is very dangerous because of the infrastructure under which InnoDB works. There are two basic infrastructures

innodb_file_per_table disabled
innodb_file_per_table enabled

InnoDB (innodb_file_per_table disabled)
With innodb_file_per_table disabled, all these types of InnoDB info live within ibdata1. The only manifestation of any InnoDB table outside of ibdata1 is the .frm file of the InnoDB table. Copying all InnoDB data at once requires copying all of /var/lib/mysql.
Copying an individual InnoDB table is totally impossible. You must MySQL dump to extract a dump of the table as a logical representation of the data and its corresponding index definitions. You would then load that dump to another database on the same server or another server.
InnoDB (innodb_file_per_table enabled)
With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table db1.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

/var/lib/mysql/db1/mytable.frm
/var/lib/mysql/db1/mytable.ibd

System Tablespace ibdata1
All the metadata for db1.mytable still resides in ibdata1 and there is absolutely no way around that. Redo logs and MVCC data also still live with ibdata1.
When it comes to table fragmentation, here is what happens to ibdata1:

innodb_file_per_table enabled: you can shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable;. This results in /var/lib/mysql/db1/mytable.ibd being physically smaller with no fragmentation.
innodb_file_per_table disabled: you cannot shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable; because it resides with ibdata1. Running either command actually, make the table contiguous and faster to read and write to. Unfortunately, that occurs at the end of ibdata1. This makes ibdata1 grow rapidly. This is fully addressed in my InnoDB Cleanup Post.

WARNING (or DANGER as the Robot would say in Lost in Space)
If you are thinking of just copying the .frm and .ibd file, you are in line for the world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metadata of the ibdata1 file.
I wrote two posts in DBA StackExchange about this tablespace id concept

Table compression in InnoDB? (under the heading 'Restoring Databases')
How to Recover an InnoDB table whose files were moved around

Here is an excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.
For InnoDB, you only need to something this to move
CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

to make a copy of an InnoDB table.
If you are migrating it to another DB server, use mysqldump.
With regard to mixing all InnoDB tables from all databases, I can actually see the wisdom in doing so. At my employer's DB/Web hosting company, I have one MySQL Client that has a table in one database whose constraints are mapped to another table in another database within the same MySQL instance. With one common metadata repository, it makes transactional support and MVCC operability possible across multiple databases.


MongoDB show duplicate key is null? MongoDB version 3.6

I got the duplicate key message when I executed this. What's the actual problem?

mongos> db.setup.findAndModify({
... query : {"_id" : ObjectId("5b3c7abbea95705803084cad")},
... update : {$set : { "test" : "xxxx" }},
... upsert : true
... })
2018-07-06T10:13:22.749+0000 E QUERY    [thread1] Error: findAndModifyFailed failed: {
    "ok" : 0,
    "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",
    "code" : 11000,
    "codeName" : "DuplicateKey",
    "$clusterTime" : {
        "clusterTime" : Timestamp(1530872002, 603),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : NumberLong(0)
        }
    },
    "operationTime" : Timestamp(1530872002, 602)
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCollection.prototype.findAndModify@src/mongo/shell/collection.js:724:1
@(shell):1:1
mongos>

This is the expected case if you have defined a unique index which is non-sparse: documents missing a value for the field with the unique index will have an indexed value of null. This means that only a single document in the collection can be missing the unique field.

The duplicate key message includes the index name and key violation:

  "errmsg" : "E11000 duplicate key error collection: testdb.setup index: name dup key: { : null }",

In your example, the collection setup in database testdb has a unique index on the name field. The attempted upsert failed because the name field was missing and there was already a document in this collection with a missing or null value for name.

If you want to use a unique index without requiring the field to be present you have a few  options:

Drop and recreate the unique index with the sparse property:

db.setup.createIndex(
    {name: 1},
    {unique:true, sparse:true}
)

Drop and recreate the unique index using a partial filter expression (which allows further criteria if needed):

db.setup.createIndex(
   { name: 1},
   { unique:true, partialFilterExpression: {name: {$exists:true }}}
)


How to enforce a constraint over multiple tables? I have the following five tables:

PRODUCT
FEATURE
PROVIDER
PROVIDER_PRODUCT
PROVIDER_FEATURE

The logic is as follows:

A generic PRODUCT can have one or more FEATUREs. There are different PROVIDERs who are delivering the same product, but with different details, and the same FEATUREs, also with different details.

The problem is that with this ERD I can create a PRODUCT p1 with FEATURE f1 and a PRODUCT p2 with FEATURE f2. Then I create PROVIDER pv1. Now I can create a PROVIDER_PRODUCT pvp1 with PROVIDER pv1 and PRODUCT p1. But when I create a PROVIDER_FEATURE pvp1 I should only be allowed to add FEATURE f1, as this is the FEATURE that is linked to p1 via the PROVIDER_PRODUCT table. However I can also add a FEATURE f2.

How can I create a constraint that prevents the user from entering a PROVIDER_FEATURE that is part of a FEATURE that is part of a PRODUCT but that is not the PRODUCT on the PROVIDER_PRODUCT that is on that PROVIDER_FEATURE? Do I need to solve that in a stored procedure, or is there a more elegant way to enforce this?

+-------------------+       +--------------------+
|                   |       |                    |
| PRODUCT           +-----> | FEATURE            |
|                   |       |                    |
+---------+---------+       +----------+---------+
          |                            |
          |                            |
          v                            v

+-------------------+       +--------------------+
|                   |       |                    |
| PROVIDER_PRODUCT  +-----> | PROVIDER_FEATURE   |
|                   |       |                    |
+-------------------+       +--------------------+

          ^
          |
          |
+---------+---------+
|                   |
| PROVIDER          |
|                   |
+-------------------+

This can easily be done by using cascading keys that overlap.  Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK):

In this example, the PK to Provider Product includes the Product Number provided, and the PK to Feature includes the Feature Number supported for that Product.  The Product Number in Provider_Feature is a FK back to both the Provider Product and the Feature.  The FK constraints thus prevent inserting a Provider Feature for a Product Feature combination not already instantiated as a Product Feature possibility.

This solution assumes that a feature is identified by a Product - that is a feature cannot exist outside the context of a product.  That assumption is likely valid as features, in and of them selves, have no meaning with the context of a product.  However, if you do want to instantiate features that can included on multiple products, then you simply add a Feature table and make the current Feature table a Product_Feature table.

Often database designers assume each table must have its own "identifier."  Nothing could be further from the truth.  Please see Fabian Pascal's excellent blog post on this topic.  Adding a surrogate Key to each table means the natural business rule you are trying to enforce can no longer be enforced declaratively via PK and FK constraints, just as you have found.  If you must have a SK for some reason your only option is to enforce the constraint procedurally using a trigger. This option is much more problematic.  First, you have to write, debug, test and maintain the trigger.  The book Applied Mathematics for Database Professionals provides excellent detail on how difficult it can turn out to be to express constraints using procedural logic in a way that performs adequately.  Secondly, triggers in and of them selves can sometimes be performance inhibitors as they execute serially.

My recommendation is to use the natural declarative approach.


How to implement a low resource privileged user on Oracle 10g using Resource Manager? In my database I have users and their respective grants using the least amount of privileges principle.

How do I start taking advantage of resource manager to prevent users from hogging all the resources? Can the process of implementing the resource manager be summed up in some short precise steps? I am not looking for a detailed process but instead like a bullet list of steps you have to complete to have successfully implemented this feature.

This is fairly easy.

create resource consumer groups
create a resource manager plan using the created groups
somehow arrange that sessions are mapped to a resource consumer group
in the resource directives specify if a request should be estimated first or not
make sure users have switch privileges to the required resource consumer groups

Point three is a bit vague, there are many ways to have a session map itself to a resource consumer group. One is by user definition, an other is by client machine name, yet an other is by module ....

If estimates are in place and the estimates guesses that more resources could be needed than allowed, the action is greeted with an error message explaining so.

It does need some careful design and mutual agreements between the admins and the users. Oracle Resource Management is very powerful.

Check the docs. Managing Resources with Oracle Database Resource Manager


how to set up sql server alerts based on a schedule basis? We would like to stop getting sql server alerts from dev servers during the weekend. We have alerts for severity 016 to 025.

Probably the easiest way I can think of is to set up an agent job to disable/enable the agent on a schedule.

https://docs.microsoft.com/en-us/sql/ssms/agent/disable-or-reactivate-an-alert?view=sql-server-2017

https://www.mssqltips.com/sqlservertip/4848/getting-started-with-sql-server-agent--part-1/

-- changes the enabled setting of Test Alert to 0
USE msdb ;
GO

EXEC dbo.sp_update_alert
    @name = N'Test Alert',
    @enabled = 0 ;
GO

You can generate the required code for all alerts that fire a notification by running this:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

This will generate the required code to re-enable those alerts:

USE msdb;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE '
' END + N'EXEC dbo.sp_update_alert @name = N''' + sa.name + ''', @enabled = 0;'
FROM dbo.sysalerts sa
WHERE sa.has_notification = 1
ORDER BY sa.name DESC;
PRINT @cmd;

I'd add the generated "disable" code to an agent job that runs on Friday evening, and the "enable" code into a job that runs on Monday morning.


Postgres data integration Consider a scenario where I have 3 standalone running Postgres engines
that are installed on three separate machines.

The data on this machine is inserted, updated or removed each month by employees. Changes made to the database must be sent and integrated into a master engine by the means of a usb drive (all the work is done offline).

One way is to always copy all the data from the 3 machines each month and then copy them to the master machine. Is there a more practical solution or technology to that on Postgres?

Try the dblink. You find the description in http://www.postgresql.org/docs/current/static/contrib-dblink-function.html and http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html. Also a question about dblink was asked on https://stackoverflow.com/questions/3862648/how-to-use-install-dblink-in-postgresql.


SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling.

I have execute sql tasks to initialise, commit and rollback the transaction, however despite having set "RetainSameConnection" to true, I am still getting the below error:

  [Execute SQL Task] Error: Executing the query "ROLLBACK;" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It turns out, I had done something really stupid.

My SSIS package, as it turns out was perfect, I had named the transactions and set "RetainSameConnection" to true. Begin/commit/rollback commands were all specified correctly.

My problem was that one of the stored procedures called as part of the SSIS package had a unnamed transaction in it, causing SQL Server to commit everything once the commit in that stored procedure was called.

Resolution was to modify the stored procedure to name the transaction, so that SQL Server treats that transaction as a nested transaction (In theory, I would have thought the second "begin transaction" statement would do this, but for some reason this was not happening).


Decomposition of a relation to 2NF then to 3NF I'm currently studying for my exams and the trouble I am having is how to decompose a relation R with given functional dependencies into 2NF then 3NF.

For example for the following R and functional dependencies:

R = {A, B, C, D, E, F, G, H, I, J}

Functional dependencies F = ( {A, B} -> {C}, {A} -> {D, E}, {B} -> {F}, {F}
-> {G, H}, {D} -> {I,J} }

I know firstly you have to work out the closure to find the key for R, which I have done and the key is {A,B} and now this is where I get stuck. My text book doesn't give any examples on how one can solve this except for definitions of 2NF and 3NF.

Example on how I can do this will be greatly appreciated.

2NF: Remove Partial Dependencies

R = {A, B, C, D, E, F, G, H, I, J} includes partial dependencies.

D and E depend only on A, F depends only on B, G, H, I and J don't depend on the key (directly) at all.

  R0 = {A, B, C}

  R1 = {A, D, E, I, J}

  R2 = {B, F, G, H}

R0, R1, and R2 contain no partial dependencies (or repeating groups) so they are 2NF.  However R1 and R2 are still an issue, because they contain transitive dependencies.

3NF: Remove Transitive Dependencies

I and J depend on D, not on the key of R1.  Therefore you need to further normalize R1 as follows:

  R1 = {A, D, E, I, J}

  R1a = {A, D, E}

  R1b = {D, I, J}

Similarly, G and H depend only on F so R2 must be decomposed as follows:

  R2 = {B, F, G, H}

  R2a = {B, F}

  R2b = {F, G, H}

Now all of your remaining relations (R0, R1a, R1b, R2a, R2b) are devoid of repeating groups, partial dependencies and transitive dependencies.  That means your relations are in 3NF.

When you are looking at an relation that hasn't been normalized and a series of dependencies, you can often normalize by inspection just by recognizing what your primary keys are going to be.  Any attribute or combination of attributes that functionally determine other attributes are going to end up as primary keys.  Once you've got your primary keys defined, you just need to figure out which non-key attributes go with each key.  This is obvious from the statement of what your functional dependencies are.


If a DW fact table is not uniquely identified by all dimensions, what problems? This is a bit of a thought problem I've been wrestling with.  I have a visceral antipathy to the notion of duplicate combinations of dimension values in a fact table.  I've read a lot about the existence of problems when the combination of dimensions in a fact table does not form a unique key.  However, I want to understand the exact types of analysis failures that can arise.

Note that I am going to stipulate, in advance, that the hypothesized ugly fact table has data that is all at the same grain.  Let's say that all sales are reported uniquely, but the finest granularity for the time of the sale is a day.  Obviously there will be transactions sharing the same combination of dimension values.  So this approach does not summarize the transactions for each day, the way good practice would ordinarily dictate.

I think simple DW queries with standard aggregation would still be correct.  By "simple," I mean that only a single fact table is referenced in the query.  In the usual form of aggregating/analyzing measures, I think the queries would produce correct results.

One failure case would arise from trying to select unique fact rows by combining all the dimensions. I believe these sorts of queries are practically unknown; I see little use for them, unless the user wants to really drill down to the finest level on all dimensions.  Am I correct in thinking this?

The only predictable and common failure case I can see arises from cross-fact queries.  Here the extra cardinality would presumably multiply any measures used in any fact table.

With my students (and in my corporate jobs), I often get asked "what bad will happen if I don't follow this rule?"  Right now I worry I don't have all the answer.

Thanks in advance for your thoughts and your answers.

I don't think you will run into a lot of problems when querying your data warehouse except for the one you already stated (cross fact queries) although if you are aware of how your design is done and only aggregate over dimensions I think you should still be able to query that. You couldn't join on the dimension keys in the fact table but you could still aggregate.

If you only care about aggregations on a daily granularity there is no need to have a dimension with a finer granularity in my opinion.

Where you might run into problems is if you try to do incremental loads or update fact records in your ETL instead of doing a full load but there are ways around that.

You could use a fact table surrogate key as Kimball describes.
You could use the technique described by Vincent Rainardi
You could include "supporting" columns in your fact table that you never report on but support your ETL strategies (in your case timestamp maybe)


Immutably convert enum to string The normal way to convert an enum value to a string in PostgreSQL is to cast it (with ::text) or to simply rely on implicit conversion when, say, concatenating it to another string:
testdb=# CREATE TYPE color AS ENUM ('red', 'green', 'blue');
CREATE TYPE
testdb=# CREATE TABLE object (id serial, name text, color color);
CREATE TABLE
testdb=# INSERT INTO object (name, color) VALUES ('table', 'red'), ('chair', 'green');
INSERT 0 2
testdb=# SELECT name || color FROM object;
  ?column?
------------
 tablered
 chairgreen
(2 rows)

However, there's a catch: such conversions seem not to be considered immutable by Postgres. As a consequence, you get an error if you try to use them in a place where an immutable expression is expected, such as in the definition of a GENERATED column:
testdb=# ALTER TABLE object ADD COLUMN namecolor text GENERATED ALWAYS AS (name || color) STORED;
ERROR:  generation expression is not immutable
testdb=# ALTER TABLE object ADD COLUMN colorastext text GENERATED ALWAYS AS (color::text) STORED;
ERROR:  generation expression is not immutable

This seems unintuitive, and is inconvenient, since I'd like to be able to concatenate enum values onto strings in a computed column.
Thus, I have two questions:

Why does Postgres consider converting an enum to a string to not be immutable? Is this behaviour a bug?
Is there a workaround that will let me persuade Postgres such a conversion is immutable (and are there any risks I should be wary of)?

It is possible to rename an enum entry, so the cast is not IMMUTABLE.

If you are sure that you will never rename enum values, write your own conversion function that uses a cast internally and label the function IMMUTABLE. Then PostgreSQL will trust that label, and it is up to you to fulfill the promise.


Query Last Value in Fixed Length Array With Empty Values I have a table that generally looks like:

Name varchar       | Location varchar[]
---------------------------------------------
Lemoney    | {Home,Work,Home,Stack Exchange,,,,,,}
Other Dude | {Home,,,,,,,,,}
Busy Dude  | {Home,Work,Work,Work,Home,Work,,,,}

I have been trying to write a query that will return the Last Value for each Name like:

Lemony     | Stack Exchange
Other Dude | Home
Busy Dude  | Work

I haven't been able to figure out how to return that yet. Does anyone have any ideas?

SELECT distinct Name, loc
FROM (
  SELECT  *, last_value(Location) over (partition by Name) as loc
    FROM (SELECT Name, unnest(Location) as Location FROM table)  x) y;

Should answer your question


Breaking point for SQL server database based on connections from app to SQL server We have been asked to confirm on some capacity test for one of our SQL server.

Application team is building their new app servers with better infrastructure to connect to SQL server.

Currently with X app servers connecting to our SQL server with over 500 Gb of RAM and 80 logical processors Numa enabled. Below is the current load

Total sessions:

used and not sleeping - on avg 100;
used and sleeping - 5000;
not used and sleeping around 500.

Above shows 40K batch requests / sec followed by approx 10k transactions per sec during the peak day. The described load consumes between 70-80 percent of CPU through peak time on SQL Server.

How can I calculate the max load in terms of additional app servers
this SQL Server can handle without causing any outages or breaking
like slow SQL Server causing issues?
Does it matter if volume remains same and just more user
connections are made or that's never going to be the case?
How can I calculate the max capacity of user connections this
SQl Server can handle without breaking apart?

You can use a load testing tool which supports the protocol which your application servers use for the database communication (OLE DB, ODBC, JDBC, etc.) in order to mimic the increased amount of application servers talking to the database instance, this way you will be able to collect metrics and correlate increasing load with the increasing query execution times, eventual deadlocks and so on.

Just make sure to configure the load testing tool to use the same connection pattern: connection pool settings, transaction isolation level, etc. so your load test would accurately represent the network footprint of the real application.

Check out The Real Secret to Building a Database Test Plan With JMeter article for more information on the concept of databases load testing using a performance testing tool, in the above case Apache JMeter


How to conditionally raise an error in MySQL without stored procedure I need to conditionally raise an error, but I can only use a simple statement and no stored procedure.

I'd like to do something like this:

select case when foo = "bar" then 1 else SIGNAL SQLSTATE 'ERROR' end;

Unfortunately SIGNAL is only usable in triggers and procedures and I have to use this within an existing application that only allows me to enter statements, but not procedures. (I only have one long line and no way to set a DELIMITER etc.)

Is there any other way to conditionally cause a runtime error ?

Retrieve column from a table with multiple rows in the else part

select case when foo = "bar" then 1 else (select table_name from information_schema.tables) end;

As an example, let's use @foo instead of foo

mysql> select case when @foo = "bar" then 1 else (select table_name from information_schema.tables) end;
ERROR 1242 (21000): Subquery returns more than 1 row

Give it a Try !!


TSQL - Intermediate results returned by User Defined Functions (UDF) and used within Inline Table Valued Function (iTVF) I have recently run into this issue and wanted to know whether my understanding was correct. I am by DNA a backend developer (not DBA) but has regular interaction with SQL. I created two identical functions (from result data viewpoints) and I am putting them in order of creation scripts:

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE
RETURN SELECT dbo.usernames(@user_id))
END
GO

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE
AS
RETURN
(SELECT name = SELECT dbo.usernames(@user_id))
GO

-- dbo.usernames is a simple scalar function which is defined after both the functions above

This is how I create all the functions for my DB:

I drop all the functions from sysobjects
I recreate all the functions again.
"the usernames() function is created after both of the above".

When I try to execute all the function creation script - it seems that my iTVF creation fails because it reports that usernames() function isn't there (which is expected because the objects are dropped, as I mentioned above).

Msg 4121, Level 16, State 1, Server XXXXXXXXX, Procedure get_names_itvf, Line 5
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.usernames", or the name is ambiguous.

But for the UDF function it doesn't run into this error.

Is this because how the inline TVF sees the internal results? Or is it how the dependencies are resolved for UDF and iTVF?

I am not sure why this is happening so it would be good to get some guidance/suggestion.

KR,

When you`re creating a scalar function you define the return type

CREATE FUNCTION dbo.get_names_udf (@user_id nvarchar(24))
RETURNS NVARCHAR(500)
AS
BEGIN
RETURN (SELECT dbo.usernames(@user_id))
END
GO

Which was NVARCHAR(500) in this case and SQL server does not need to check the underlying structure of referenced functions/tables as long as knows the return type.

It will fail to execute tho, if usernames function (in this case) would return something other than NVARCHAR(500), which is expected

On the other hand

CREATE FUNCTION dbo.get_names_itvf (@user_id nvarchar(24))
RETURNS TABLE
AS
RETURN
(SELECT dbo.usernames(@user_id) as username)
GO

As specified ,returns table therefore SQL server checks the underlying structure of the table because it needs to know what kind of table is begin returned.
How many columns it has,if any of the columns are without names(in aggregate cases) and inform you about it. You can test this by running the query above without referencing the column name( 'as user name' ) in case username table does not have a specified column name.

Note:
I had to tweak this two functions a little bit, to make them work.

Hope this clarifies what is going under the hood for you


Difference between Generate Script option at database level and backup and restore option When doing that generate scripts on the database level what difference does it do from doing a backup and restore on the database? It seems to me that they both provide the same purpose. What would be best to use between the two? Is one better than the other?
I am migrating database from one domain to another and both are different domain but have sql server 2012

With backup you have all data. Of course you can generate script for data, but for a DB with 1-2 GB the script will be very big and will execute long time.

The best way in my point of view is detach/attach method. It will preserve the current state of the DB. Of course you can make backup before that. In that case you also have to take into account downtime for transferring database between servers.


Oracle Ref Partitioning: Deadlock due to child table row migration My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.

ORDERS table is LIST partitioned on STATUS column and all other tables are referenced partitioned with ORDERID as a foreign key.

At peak load, when order status is changed and ORDERS table row is moved from one partition to another, Oracle performs row migration for all the child tables referenced partitioned by ORDERS table. Due to many tables that depend on ORDERS table, large number of row movements happen causing a deadlock in one of the child table.

My question is, how to resolve a deadlock caused in the ORACLE's internal row migration step?

Here is an example setup:

ORDERS table:

CREATE TABLE ORDERS (
       orderID   NUMBER PRIMARY KEY,
       description    VARCHAR2(30),
       status   VARCHAR2(30))
  PARTITION BY LIST (status) (
       PARTITION A VALUES ('COMPLETED'),
       PARTITION B VALUES ('ACTIVE'),
       PARTITION C VALUES ('SUBMITTED'))

Child table: PLAN

CREATE TABLE PLAN (
       planID   NUMBER PRIMARY KEY,
       orderID    NUMBER,
       description   VARCHAR2(30),
       CONSTRAINT FKC64393AD1EC7235 FOREIGN KEY (orderID)
       REFERENCES ORDERS (orderID) ON DELETE CASCADE
 )
 PARTITION BY REFERENCE (FKC64393AD1EC7235);

There are many more child table where ORDERS is there parent table.

Under heavy load, when ORDER status is changed which causes row movement between partition, following deadlock error is printed in the log
ORA-00060: deadlock detected while waiting for resource

In the Oracle trace log, I see following SQL causing deadlock

update /*+ opt_param('_and_pruning_enabled', 'false') */ "TEST"."PLAN" partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) move to partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) set "ORDERID" = "ORDERID" where "ORDERID" = :1

Now this SQL is internally generated by Oracle to perform row migration for child PLAN table.

To resolve the issue I tried following changes:

I have confirmed that OrderID column (Foreign Key column) in the PLAN table has index on it.
Tried increasing PCTFREE parameter on the table.

But havnt got success yet. How do I handle deadlock for this scenario?

------------------------- UPDATE -------------------------

As per suggestions suggested by Wernfried and Gandolf989, I verified if all my foreign keys have indexes on them by running query given in the Gandolf989 answer.

Result was "No Rows Found". So it means, all the indexes seems to be in place. But while analyzing I realized, if I check an explain plan for a simple query like below, I see FULL table scan on the PLAN table even after having an index on ORDERID column.

 UPDATE PLAN SET DESCRIPTION = 'ABC' WHERE orderid= '234';

Following is the index statement originally run on this table.

 CREATE INDEX IDX_PLAN_ORDERID ON  PLAN(ORDERID);

So for some reason, ORACLE is not taking Index into consideration while running update query on PLAN table. Am I missing something?

That could be caused by unindexed foreign keys.
Tom Kyte has a query that you can use to find them.
It could be as simple as adding a few indexes
on the child tables.

Unindexed Foreign Keys


Buffer Pool Extension There were very good articles in MSDN and all over, but none explained about the BPE size or max size setting or growth.

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);

This is going to add the file of 2GB, but what I understand was this is the minimum size of buffer pool file which it will be starting with, and this will be the max memory size setting on sql server?

and is there a way i can  set how large the BPE file can grown... even  though i plan the SSD drive to be 1:4 ratio ( 1 being maxmemory setting in sql).. Could some one please clarify.

According to the official documentation:

  The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend a ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.

If you have, say, 64 GB of physical RAM, your Max Server Memory setting would be 54272 MB (53 GB), which means your BPE should be a maximum of 848 GB (53 * the ratio of 16) or less, with 424 GB or 212 GB recommended for optimal size.

When you configure the BPE size with ALTER SERVER CONFIGURATION, that is the size of the cache, and is how large it will be. It will not grow unless you change it.

So make sure you pick the right size to begin with, and make sure your SSD is fast enough.

In summary (from the comments):

The maximum file size of BPE should be set as a part of the initial file creation, and this file size should be in a 1:4 ratio of Max Server Memory to Buffer Pool Extension, as per Microsoft's recommendation.
In practice, the BPE size is approximately the size of your flash storage, less the size of existing TempDB on that drive. In other words, you would use the leftover space on your PCIe/NVMe flash disk after sizing TempDB.


How do I determine the cause of a very small table responding to queries slowly? I have a table that stores less than 3 registers, but the queries on it are very slow.

This table has only a single Primary Key with 0% fragmentation.

> DBCC SHOWCONTIG scanning 'MYTABLE' table... TABLE level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7930.0
- Avg. Page Density (full).....................: 2.03%

If multiple threads are issuing the delete/update/select statements concurrently, then blocking is the most likely explanation.
You could use SQL Profiler and filter the trace on TextData Like '%MYTABLE%' to find all the statements accessing the table.

https://stackoverflow.com/questions/4076516/how-to-profile-for-one-table-in-sql-server

However use caution with SQL Profiler in a production environment, it can impact overall server performance.


How do I sort the results of a recursive query in an expanded tree-like fashion? Let's assume you have a nodes tables like this:

CREATE TABLE nodes
(
    node serial PRIMARY KEY,
    parent integer NULL REFERENCES nodes(node),
    ts timestamp NOT NULL DEFAULT now()
);

It represents a standard node-like tree structure with root nodes at the top and several child nodes dangling from root nodes or other child nodes.

Let us insert a couple of example values:

INSERT INTO nodes (parent)
VALUES (NULL), (NULL), (NULL), (NULL), (1), (1), (1), (1), (6), (1)
     , (6), (9), (6), (6), (3), (3), (3), (15);

Now I want to retrieve the first 10 root nodes and all their children up to a depth of 4:

WITH RECURSIVE node_rec AS
(
    (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10)

    UNION ALL

    SELECT depth + 1, n.*
    FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node)
    WHERE depth < 4
)
SELECT * FROM node_rec;

This works great and gives me the following result:

 depth | node | parent
-------+------+--------
     1 |  1   |
     1 |  2   |
     1 |  3   |
     1 |  4   |
     2 |  5   |  1
     2 |  6   |  1
     2 |  7   |  1
     2 |  8   |  1
     2 | 10   |  1
     2 | 15   |  3
     2 | 16   |  3
     2 | 17   |  3
     3 |  9   |  6
     3 | 11   |  6
     3 | 13   |  6
     3 | 14   |  6
     3 | 18   | 15
     4 | 12   |  9

As you might have noticed, there's no ORDER BY clause, so the order is not defined. The order you see here is from root nodes to deeper nodes.

How would I order the results as they would appear in an expanded tree view, as you can see from the example picture below?

I basically want the child nodes to be placed right after their corresponding parent node. If two or more child nodes have the same parent node, I want them to be sorted by their timestamp. Based on the example above, here's the desired output order that I'm trying to achieve:

 depth | node | parent | ts
-------+------+--------+---------
     1 |  1   |        | 2014-01-01 00:00:00
     2 |  5   |     1  | 2014-01-01 00:10:00
     2 |  6   |     1  | 2014-01-01 00:20:00
     3 |  9   |     6  | 2014-01-01 00:25:00
     4 |  12  |     9  | 2014-01-01 00:27:00
     3 |  11  |     6  | 2014-01-01 00:26:00
     3 |  13  |     6  | 2014-01-01 00:30:00
     3 |  14  |     6  | 2014-01-01 00:36:00
     2 |  7   |     1  | 2014-01-01 00:21:00
     2 |  8   |     1  | 2014-01-01 00:22:00
     2 |  10  |     1  | 2014-01-01 00:23:00
     1 |  2   |        | 2014-01-01 00:08:00
     1 |  3   |        | 2014-01-01 00:09:00
     2 |  15  |     3  | 2014-01-01 10:00:00
     3 |  18  |     15 | 2014-01-01 11:05:00
     2 |  16  |     3  | 2014-01-01 11:00:00
     2 |  17  |     3  | 2014-01-01 12:00:00
     1 |  4   |        | 2014-01-01 00:10:00

An array representing the path from the root up to the leaf should achieve the desired sort order:

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.node, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)
SELECT *
FROM   node_rec
ORDER  BY path;

  If two or more child nodes have the same parent node, I want them to
  be sorted by their timestamp.

Shift the path by one towards the root and order by that column additionally:

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.parent, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)
SELECT *
FROM   node_rec
ORDER  BY path, ts;


Oracle Procedure to Enable/Disable all constraints I have this nice piece of code:

begin
for i in
(
    select constraint_name, table_name
    from user_constraints
    where constraint_type ='R'
    and status = 'ENABLED'
) LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

I'm using if very often for my migration so it would be even better to create a procedure out of if. The procedure should accept one boolean argument (should it enable or disable constraints), but frankly I had never wrote a procedure before. Any help would be appreciated.

I recommend you put the code in an AUTHID CURRENT_USER package.  You can certainly pass a Boolean, but I think a Varchar2 makes the usage clear.  You should unit test this code, add documentation, formalize the error handling, include instrumentation, and probably modify the reporting method.

CREATE OR REPLACE PACKAGE Maint AUTHID CURRENT_USER IS

Procedure ToggleConstraints (iNewStatus In Varchar2);

END;
/

CREATE OR REPLACE PACKAGE BODY Maint IS

Procedure ToggleConstraints (iNewStatus In Varchar2) Is
Begin
   If (UPPER(iNewStatus) NOT IN ('ENABLED','DISABLED')) Then
      Raise_Application_Error(-20001
         , 'Constraints can only be toggled to ENABLED OR DISABLED.');
   End If;

   For vConstraint In
   (
       SELECT 'alter table ' || table_name
         || DECODE(UPPER(iNewStatus), 'DISABLED',' disable',' enable')
         || ' constraint ' || constraint_name As Statement
       FROM user_constraints
       WHERE constraint_type = 'R'
       AND status = DECODE(UPPER(iNewStatus),'DISABLED','ENABLED','DISABLED')
   ) Loop
      DBMS_Output.Put_Line(vConstraint.Statement);
      execute immediate vConstraint.Statement;
   End loop;
End;

END;
/

Set serveroutput on size 1000000 format wrapped
EXECUTE Maint.ToggleConstraints(iNewStatus=>'ENABLED');


Maintenance in MYSQL when innodb_file_per_table disabled I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.

But what if I need to perform index maintenance on InnoDB tables with ANALYZE command, it will grow the single tablespace also?

What other alternatives are there to increase performance or doing some maintenance in Innodb engine, when using a single tablespace and without grow out of control the single ibdata1.

Regards.
Matthew

ANALYZE TABLE will read index pages for a table, compute statistics, and store the results in INFORMATION_SCHEMA.STATISTICS. No writes to ibdata1 whatsoever.

Notwithstanding, anything DDL-related such as

ALTER TABLE ... ADD INDEX ...
ALTER TABLE ... DROP INDEX ...
ALTER TABLE ... MODIFY COLUMN ...
OPTIMIZE TABLE ...

performed against an InnoDB table or its indexes with innodb_file_per_table disabled will make ibdata1 mercilessly grow.

There are two things you could try to minimally control (or at least monitor) ibdata1's growth

ALTERNATIVE #1 : Place a limit on ibdata1 on creation

Perhaps create a large ibdata1

ibdata1:innodb_data_file_path = ibdata1:100G

or set to large initial size and a larger max filesize

ibdata1:innodb_data_file_path = ibdata1:50G:autoextend:max:100G

ALTERNATIVE #2 : Use a Large Raw Data Device

According to MySQL 5.0 Certification Study Guide, Page 428

  Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

  Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

  After changing the configuration, restart the server.

CAVEAT

With either alternate you stop worrying about growth until there is no more room. Otherwise, you must eventually deal with any imposed limits on ibdata1's size.

Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?


Why are some tables in PostgreSQL available from all databases in my server? I found out that in my PostgreSQL server, there are 3 tables that I can call from any database. When I use \d command in psql I see those tables no matter to which database I'm connected.
I didn't even know it is possible for tables to be accessible from all databases.

What is this feature in PostgreSQL and how should I hide them from other databases?

This quote from the manual should help to lift the fog. From "Managing Databases" - "Overview":

  A database is a named collection of SQL objects (“database objects”).
  Generally, every database object (tables, functions, etc.) belongs to
  one and only one database. (However there are a few system catalogs,
  for example pg_database, that belong to a whole cluster and are
  accessible from each database within the cluster.) More accurately, a
  database is a collection of schemas and the schemas contain the
  tables, functions, etc. So the full hierarchy is: server, database,
  schema, table (or some other kind of object, such as a function).

Bold emphasis mine.

So there are some tables that are visible in every database of the same cluster. But only a few system catalogs like pg_database. Most other system catalogs, also present in each database, are separate instances, sharing the same name.

Nothing keeps you from creating tables of the same name in distinct databases or distinct schemas within the same database. If you have created tables (or other objects) in the template1 database of your cluster, one copy is added to every new database automatically. All of these are distinct tables regardless.

Finally, "server" is a fuzzy term. Might refer to a single DB cluster or to a system with several PostgreSQL DB clusters (listening to distinct ports) ... Use accurate terminology to avoid confusion.


Deploying Oracle ADW Changes from development environment to production I'm currently running an Oracle Autonomous Data Warehouse (Oracle's cloud-based database offering) on version 19c. I have a development database on the Always Free Tier and my production database is a paid instance (with backups etc). I will likely also be making a staging instance soon.
When making changes to the database schema we have been making changes to my database schema as follows:

Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
Test the development environment manually
Change the schema on the production environment using identical SQL statements
Test the production environment to make sure nothing is broken

I am aware that this is not the way it should be done, we need to implement some CI/CD to streamline these changes but we've only recently come out of our piloting phase where it was a rush to get the product working etc, I'm very keen to firm up our new systems.
How I'm imagining it'd go (changes in bold):

Change schema on the development environment using SQL statements (CREATE, ALTER, etc)
Test the development environment with automated tests
Apply some tool to apply the same changes to my production system
Test the production environment to make sure nothing is broken

I have seen that tools exist for other SQL database tools, such as MySQL, PostgreSQL etc but from my searches on Oracle-specific tools, I'm not sure if where to find a tool that can propagate the schema changes from our development database to a production database.
If you know of any tools that I can look at to do this functionality then I'd appreciate a pointer on where to look!
I'm aware this is similar to other questions, the ones I've looked at are:

Altering db schema in production environment
Upgrade production database during software development lifecycle

Both of these questions were asked several years ago and so I'm not sure if new things have come up recently.

Key Infrastructure Pieces

Code Repository/Version Control System
Separate (and licensed) Dev/Test/Prod environments

You seem to be missing the Test environment.
Test Environment
I highly recommend using a Test environment.  One advantage is: You can use the actual Schema's name irregardless of environment.
One way to use the Test environment:

Clone Prod to Test

usually a clone of a pluggable database

apply Change scripts
perform Unit Tests

Automatic Test Scripts
There is a PL/SQL based Unit Testing package on GitHub ( utPLSQL ).
But, if you want an Oracle product, SQL Developer has Unit Testing capabilities.
You'll most likely need to create the scripts that interface with your automation software.
Automatic Schema Changes
Your second link mentioned LiquiBase.  This is now integrated into SQL Developer. Demo for Dev Schema to Prod Schema is available here.
You will use the new command line interface (SQLcl) for automation, not SQL Plus.
Another alternative (for new tables) is the SQL Developer Cart feature. It can also work well for creating the INSERT scripts for the small Dimension Tables.
Code Review
One thing you may want to incorporate into your process is a way to automate parts of the Code Review process.  I believe the key words to search in Google/Bing are "code sniffer".
SQL Developer has a custom highlighting feature that I use to detect "bad code".
Example

TO_DATE() without a format.
commit inside of a loop.
when others then null ( other forum ).

The review process is manual for me.  But, the custom highlighting helps the human code reviewer.
TL;DR
You need to look at SQL Developer's set of features.


2013. Lost connection to MySQL server during query error I have a table with close to 20 million rows.

In phpMyAdmin, I attempt to add a new column:

ALTER TABLE `myTable` ADD `newColumn` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';

After about 60 secs (every time), I get the error.

I've had my server guy increase both net_read_timeout and net_write_timeout from 60-600 seconds, but the error still occurs after 60 secs.

Just wanted to see if I'm going in the right direction here, or should be chasing something else?

The error log from just prior to the crash says (nothing before this for a long time):

150209 05:03:39 mysqld_safe Number of processes running now: 0
150209 05:03:39 mysqld_safe mysqld restarted
2015-02-09 05:03:40 0 [Warning] option 'interactive_timeout': unsigned value 9999999999 adjusted to 31536000
2015-02-09 05:03:40 0 [Warning] option 'wait_timeout': unsigned value 9999999999 adjusted to 31536000
2015-02-09 05:03:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-09 05:03:40 6744 [Note] Plugin 'FEDERATED' is disabled.
2015-02-09 05:03:40 6744 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-02-09 05:03:40 6744 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-09 05:03:40 6744 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2015-02-09 05:03:40 6744 [Note] InnoDB: Memory barrier is not used
2015-02-09 05:03:40 6744 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-02-09 05:03:40 6744 [Note] InnoDB: Using Linux native AIO
2015-02-09 05:03:40 6744 [Note] InnoDB: Not using CPU crc32 instructions
2015-02-09 05:03:40 6744 [Note] InnoDB: Initializing buffer pool, size = 54.0M
2015-02-09 05:03:40 6744 [Note] InnoDB: Completed initialization of buffer pool
2015-02-09 05:03:40 6744 [Note] InnoDB: Highest supported file format is Barracuda.
2015-02-09 05:03:40 6744 [Note] InnoDB: The log sequence numbers 108217366409 and 108217366409 in ibdata files do not match the log sequence number 108487328890 in the ib_logfiles!
2015-02-09 05:03:40 6744 [Note] InnoDB: Database was not shutdown normally!
2015-02-09 05:03:40 6744 [Note] InnoDB: Starting crash recovery.
2015-02-09 05:03:40 6744 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-02-09 05:03:41 6744 [Note] InnoDB: Restoring possible half-written data pages
2015-02-09 05:03:41 6744 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 394549, file name sbdb-bin.000108
2015-02-09 05:03:42 6744 [Note] InnoDB: 128 rollback segment(s) are active.
2015-02-09 05:03:42 6744 [Note] InnoDB: Waiting for purge to start
2015-02-09 05:03:42 6744 [Note] InnoDB: 5.6.22 started; log sequence number 108487328890
2015-02-09 05:03:42 6744 [Note] Recovering after a crash using sbdb-bin
2015-02-09 05:03:42 6744 [Note] Starting crash recovery...
2015-02-09 05:03:42 6744 [Note] Crash recovery finished.
2015-02-09 05:03:43 6744 [Note] Server hostname (bind-address): '*'; port: 3306
2015-02-09 05:03:43 6744 [Note] IPv6 is available.
2015-02-09 05:03:43 6744 [Note]   - '::' resolves to '::';
2015-02-09 05:03:43 6744 [Note] Server socket created on IP: '::'.
2015-02-09 05:03:43 6744 [Note] Event Scheduler: Loaded 0 events
2015-02-09 05:03:43 6744 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.22-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Thanks for your time and help.

Check your memory usage or, more probably, your disk usage (is there enough free space during the process?). Altering a table may require either a large amount of memory or a copy on disk of your table. Changing the alter algorithm from INPLACE to COPY can be even faster in your particular case.

You may also be hitting the innodb_online_alter_log_max_size limit, although in that case, only the query should fail, not the entire server. It is possible that the crash may be happening due to the ROLLBACK, and not the operation itself, though.

Finally, some application configurations or hosting servers cancels a query/http request that is taking too long, I recommend you to execute the same query on the command line client for testing purposes.


mysql trigger AFTER INSERT if condition syntax error #1064 - You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near '' at line 7

DELIMITER //
  CREATE TRIGGER after_tableA_insert AFTER INSERT ON tableA
  FOR EACH ROW
  BEGIN
     IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN
       INSERT into error
       SET triedTime = NEW.changedat;
  END //
DELIMITER ;

Based on your own answer, the issue appears to have been with this line:

IF(TIMESTAMPDIFF(HOUR, `triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

The problem is the `triedTime` reference. According to the manual:

  Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger.

That is to say, without either NEW or OLD you cannot access columns of the affected rows. So, in the above shown line you probably want to use the NEW keyword:

IF (TIMESTAMPDIFF(HOUR, NEW.`triedTime`, CURRENT_TIMESTAMP()) < 60000) THEN

Your own method of looking the value up in the table works as well, only it means an extra table hit, which may affect performance.


Oracle not available offline I recently faced a weird problem where I cannot use/log into any Oracle user nor even System account as well as not being able to access my database.
I have a Windows 10 64 bits Home edition, and I did multiple new installation of both Oracle XE 11g release 2 and Oracle XE 18c.
When I installed the XE 11g R2, and when trying to connect to SQLplus using the command line I get the following error

So searched for this error and I found that it is probably related to the database as it is not being mounted correctly everytime:
I did the following:

Now I get the ORA-00119 & ORA00130 ERRORS, and I honeslty don't know where is the problem especially because all of these problems do not occur when I'm using an Internet Connexion using WIFI at home.
If anyone got any idea on how I can fix this please share!
Here are the following settings I currently have for Oracle:

LSNRCTL status:

the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listner.ora

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )
      (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521))
      )
    )

  DEFAULT_SERVICE_LISTENER = (XE)

**the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora **

  XE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = XE)
      )
    )

  EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
        (SID = PLSExtProc)
        (PRESENTATION = RO)
      )
    )

The services that are running for oracle :

My envirement variables:

sqlplus / as sysdba
create pfile from spfile;
exit

Now edit C:\oraclexe\app\oracle\product\11.2.0\server\database\initXE.ora. Remove the line with local_listener, you use the default 1521 port on your local machine, you do not need that line. Once done:

sqlplus / as sysdba
create spfile from pfile;
startup

The problem is that your hostname is ASUS-ALEXLUTINNOIR, but only the first 15 characters was put in the local_listener parameter (ASUS-ALEXLUTINN).  The database can't resolve that hostname when starting up. In such a simple configuration you do not need the local_listener set, just leave it blank (remove), so the database will register itself to the listener running on the default port (1521).


Do higher normalized forms always meet the criteria of lower ones? For example, a database that is 3NF is always 1NF and 2NF; does this hold for 4, 5, and 6?

1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF ⊂ 5NF ⊂ 6NF