Set mongodb's `maxIndexBuildMemoryUsageMegabytes` setting in conf file In the mongodb documentation for the configuration options it says that I can specify how much RAM index building will consume:

  maxIndexBuildMemoryUsageMegabytes:   Limits the amount of memory
  that simultaneous foreground index builds on one collection may
  consume for the duration of the builds.

This setting is set after starting the server by executing an administrative command called  setParameter.
This is done like this:

mongo --eval "db.adminCommand({setParameter: 1, maxIndexBuildMemoryUsageMegabytes: 700})"

I would like to specify this amount when the server starts up by putting it in the Configuration File Options. The documentation does not seem to include this option.

How can I specify this setting from the mongod.conf file?

The different approaches for configuring setParameter options (via runtime admin command, config file setting, or command-line option) are listed in the Synopsis section at the top of the MongoDB Server Parameters page you referenced.

You can use setParameter key/value pairs in the YAML configuration file.

For example:

setParameter:
    maxIndexBuildMemoryUsageMegabytes: 700

To confirm this setting has taken effect via the mongo shell, use either of:

// Check the parameter values parsed from config file or command-line options
db.serverCmdLineOpts().parsed.setParameter

// Check the current setting of the maxIndexBuildMemoryUsageMegabytes parameter
db.adminCommand({getParameter: 1, maxIndexBuildMemoryUsageMegabytes: 1})


To check who has updated my database I have server database MySQL which is connected to different network(or ip address)
i want to find out which user has updated what and when ?

can log file help me in this if so how ?

please can anyone update me in this?

You should activate the general log. Normally, the general is a text file. I have posts that show you how to make the general log a MyISAM table. Then, you could query the general log by datetime using SQL.

Here are my posts how to setup and configure the general log as a MyISAM table

Jun 30, 2011 : How do I output MySQL logs to syslog?
Jan 08, 2012 : How to enable MySQL general log?
Feb 11, 2012 : MySQL general log
Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
Jun 23, 2013 : Analyse MySQL General Query Log in Real-time?
Apr 28, 2014 : MySQL (on Windows) general log too large to rotate properly?

Once you configure it, just run queries like this to see commands run today

SELECT user_host,argument
FROM mysql.general_log
WHERE event_time >= DATE(NOW()) + INTERVAL 0 SECOND;


Incorrect Syntax Near `=` - SQL Expression I'm trying to include a conditional check in a WHERE statement.But i keep getting Invalid expression at = 3
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND CLM.myval = 3
          AND
          CASE APEL.numval
          WHEN 1 THEN  CLM.value =  3 END

Basically i want to check CLM.VALUE=3 when APEL.numval=1 otherwise this AND Condition should not be executed.

One way to do this , if I understood it right :
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND
          (
             (CLM.myval = 3 and APEL.numval = 1)
           or
             (APEL.numval <> 1 )
          )

or try to split in 2 :
SELECT ...
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND CLM.myval = 3
          AND APEL.numval = 1

UNION  ALL  /* or UNION */

SELECT ...
WHERE APCCL.data = @coolval
          AND CCMF.Status_ID = 1
          AND APCCL.Status_ID = 1
          AND APEL.numval <> 1

Here are some sample testing:
declare @coolVal int = 1;

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
      data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND
    (
        (myval = 3 and numval = 1)
        or
        (numval <> 1)
    )

or with UNION
declare @coolVal int = 1;

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
          data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND myval = 3
          and numval = 1

UNION ALL

SELECT *
FROM
(
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 3 as myVal, 2 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 1 as numVal UNION ALL
SELECT 1 as Data, 1 as Status_ID , 4 as myVal, 2 as numVal
)a
WHERE
          data = @coolVal
          AND Status_ID = 1
          AND Status_ID = 1
          AND numval <> 1

and the result set :
Data    Status_ID   myVal   numVal
1       1          3        1
1       1          3        2
1       1          4        2

dbfiddle
some interesting info here


SQL Server 2008 - How to schedule a copy of view to another database? I am a SharePoint admin and need to copy the log data in database to another temp database for analysis. The data is stored in a view ([WSS_Logging].[dbo].[RequestUsage]). There is a column [LogTime] on the view and any row older than 14 days will be discarded.

I would like to setup a daily schedule job to copy all records one day before to a temp database. I estimate there will be 1GB data daily. May I have your suggestion the best way to achieve this? If possible, please introduce method to check against the copying have not lost any row during the copy, too.

SQL Server is not my professional area. Any help is appreciated. Thanks.

Moving 1GB is not a trivial operation, though not a scary one either.  A simple INSERT .. SELECT would have too much impact on the running server.  Batching so only n-thousand rows are moved at a time would be better.  'n' can be tweaked to the idiosycracies of your particular system.  Better still would be a dedicated ETL package.  You'll need a SQL Server developer to help you with that, most likely.  If it is important that you never miss a row be sure to build reconciliation into whatever solution you adopt.

Be careful using a datetime to determine whether a row should be copied or not.  Times have to rounded to the resolution of the column holding them.  It is therefore possible that a new row is rounded down and missed by the next extraction.  It sounds like you will be copying data that is 13 days old so will not be at risk.


postgres_fdw and postgres version upgrade Case explained

I'm facing difficulties concerning performance of queries using a Postgres 9.5.4 database set up on a different server connecting via Foreign Data Wrapper to another instance of Postgres 9.5.4.

I've already tried enabling use_remote_estimate setting via ALTER SERVER but this seems not to fix it as much as I thought it would.

Regular queries with joins tend to take >3X longer when issued via FDW database.

I've investigated data transfers, gave more RAM and CPU to utilize on FDW but with no success. Anyway it seems like it's not even using that much resources.

Possible improvement

I've read about join and aggregate pushdowns for FDW introduced in Postgres 9.6 which seems great and should improve response time, but I'm unsure whether or not it's enough to just upgrade the FDW Postgres server. Do I need to upgrade the main server I'm connecting to as well?

Example

Simple example of a query that I think could benefit a lot from upgrading Postgres to 9.6 would be:

db=# EXPLAIN VERBOSE select max(add_date) from mytable;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate  (cost=2084783.99..2084784.00 rows=1 width=8)
   Output: max(add_date)
   ->  Foreign Scan on public.mytable  (cost=100.00..1963010.07 rows=48709569 width=8)
         Output: id, id_a, id_b, id_c, allowed, mod_date, add_date, idv
         Remote SQL: SELECT add_date FROM public.mytable

There is no index, and won't be, on production, but it takes around 10s to complete on master server (50M rows), while on FDW server it goes as high as 250s, which I believe is due to the fact that remote SQL executed is sending all the data to the server in order to allow it to aggregate.

Back to question

Again, to benefit from what 9.6 has to offer do I only need to upgrade Postgres instance on server that has the FDW Server and foreign tables, or also the master server?

Join and aggregate pushdowns are supposed to go live on Postgres 10, not Postgres 9.6 which is why remote queries are not being sent the way I expected it to.


Stored procedure arguments get immediately truncated I have a stored procedure with a single SELECT statement inside:

DROP PROCEDURE `pos_neg_test`//
CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP)
    BEGIN
        SELECT `title_id`, `total`, `pos`, `neg` FROM
        (SELECT
            COUNT(message_id) AS total ,
            title_id
        FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts)
        GROUP BY title_id
        ) total
    LEFT OUTER JOIN
        (SELECT
            COUNT(message_id) AS pos ,
            title_id FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment > 0
        GROUP BY title_id
        ) pos
    USING (title_id)
    LEFT OUTER JOIN
        (SELECT
            COUNT(message_id) AS neg ,
            title_id FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment < 0
        GROUP BY title_id
        ) neg
    USING (title_id);
END;

When I call this procedure from the mysql CLI, I got an empty result:

mysql> CALL pos_neg_test(1348142600, 1348143200);
Empty set (0.24 sec)

Query OK, 0 rows affected, 2 warnings (0.24 sec)

SHOW WARNINGS tells me, that my input argument got truncated:

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'start_ts' at row 1 |
| Warning | 1265 | Data truncated for column 'end_ts' at row 1   |
+---------+------+-----------------------------------------------+
0 rows in set (0.00 sec)

What did I do wrong?

MySQL timestamp is not a Unix timestamp.  It has the form '2012-02-02 12:12:12'.  I think it is worth to mention that it differs from datetime in that while timestamps minimum and maximum values correspond to that of a Unix timestamp, datetime has a broader range.

See the docs here:
http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The easiest solution is changing arguments to integers.  The other way is of course producing valid timestamps in the calling application and feed those to your stored proc, but in this case you will have to modify the proc as well (by removing the FROM_UNIXTIME conversions).  (Thanks for DTest for the suggestion.)


Issue with forming correct relationship between subtype and another table in ERD The focus is on the Cashier table and the Cash Accountability table. The rule is that only Cashiers have Cash Accountability and a Cashier ideally only cashes out once per day. Before I changed the ER diagram to what it is now, the CashID was a foreign key in the Cashier table, but there was nothing in the Cash Accountability table to identify which cashier the data belonged to. So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

I am unsure of how to get the relationship between the two tables correct and I would appreciate some guidance in the right direction.

Screenshot of the ERD

I actually think your diagram is good for your rule. The only thing is that with the relationship line connecting "Cash Accountability" and "Cashier", I would remove the circle on the Cashier side, to indicate that a "Cash Accountability" has to have exactly one Cashier.

  So as it is now, the relationship states that a cashier cashes out many times in a day and I want to express that a cashier only cashes out once per day.

The relationship is simply saying that a cashier can cash out more than once (not necessarily more than once per day), which is technically correct.

You could add a unique constraint to the implementation of your data model between "Cash Accountability"'s EmpID and Date fields in order to enforce that a Cashier cannot cash out more than once a day. Here's how this would be done in MySQL: https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql

I'm not sure if there's a "correct" way to display this unique constraint in an ER Diagram, but I suppose you could just add a comment.


Cycle or something similar in PL/SQL Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via

alter user %username% account unlock

and instead of typing the same line multiple times for each user I believe some cycle construction can be used but don't know how to write it in PL/SQL. Can somebody advice on this?

set serveroutput on size unlimited
for i in (select 'alter user '||username||' account unlock' cmd
            from dba_users
           where account_status = 'LOCKED'
          )
loop
   dbms_output.put_line (i.cmd);
 --  execute immediate i.cmd;
end loop;
end;
/

This works. Be careful, I have seen ppl doing similar things without testing, effectively destroying databases, especially when using patterns and manipulating database objects with the results. If the output is OK, activate the execute immediate.


Can I get notification when event occur? SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:

While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?

Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.

Below is the script that will help you with Deadlock detection and email in real time :

It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.

use dba_db ---- change HERE use find and replace as per your database name !!
go
/********************************************************************
Job Name    :   Monitoring - Deadlock Detector
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled
*********************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Deadlock]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
Print ' *** Table Deadlock Table Already Exists... moving Further *** '
ELSE
create table Deadlock
(
RecordId int identity (1,1) primary key not null,
AlertTime datetime not null,
DeadlockGraph xml,
Notified int not null constraint [DF_deadlock_flag] default (0)
)
go
create index deadlock_idx on Deadlock  (AlertTime) with fillfactor = 100
go
USE [msdb]
GO
-- No need to enable deadlock trace flags
--dbcc traceon (1204,1222,-1)
--go
-- enable replace runtime tokens for sql agent to respond to WMI alets
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
-- create the job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Monitoring - Deadlock Detector',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Job Name :   Monitoring - Deadlock Detector
Created by  :   Kin for dba.stackexchange.com
Version     :   V1.0
Funtionality:   1. Real time Deadlock detection using WMI
                2. Send email out when deadlock occurs along with
                   Deadlock Graph attached.
                3. Logs all the deadlocks in a central Table
                   dba_db.dbo.Deadlock
                4. Does not require trace flags 1204 and 1222 enabled',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert Deadlock info',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'INSERT INTO Deadlock (

AlertTime,

DeadlockGraph

)

VALUES (

GETDATE(),

''$(ESCAPE_NONE(WMI(TextData)))''

)',
        @database_name=N'dba_db',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Send Email with Deadlock Graph]    Script Date: 10/01/2010 12:01:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email with Deadlock Graph',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'if exists (select 1 from dba_db.dbo.Deadlock where notified = 0 )
begin
declare @tableHTML nvarchar(max)
set @tableHTML =N''
Deadlock Has occured on ''+@@servername+'' .. Please Investigate Immediately
''
set @tableHTML = @tableHTML+ N''
'' +
           N'''' +
            N''
RecordId	'' +
            N''AlertTime	'' +
            N''DeadlockGraph	'' +

            N''
'' +
           ISNULL(CAST ( (
                            select  td = '''',
                                    td = RecordId,'''',
                                    td = AlertTime,'''',
                                    td = DeadlockGraph,''''

                     from dba_db.dbo.Deadlock where notified = 0

    FOR XML PATH(''tr''), TYPE

            ) AS NVARCHAR(MAX) ),'''') +
            N'''' +
            N''
'' ;
-- bcp out as .xdl file. This is the deadlock graph that will be emailed. Note that it will be overwritten everytime !!
exec master..xp_cmdshell ''BCP.exe "SELECT  [Deadlockgraph].query(''''/TextData/deadlock-list'''') FROM dba_db.dbo.Deadlock where Notified = 0" queryout "d:\logs\deadlock.xdl" -c -q -T -Slocalhost''; ---- change localhost ..with the servername\instance or servername !!
-- send email out with the graph attached
declare @subject1 varchar(50)
set @subject1 = ''Deadlock Has Occured on ''+@@servername
EXEC msdb.dbo.sp_send_dbmail
            @profile_name = ''DBMAIL PROFILE'',             ---- change HERE db mail profile !!
            @recipients=''DBAcompanyGroup@companyName.com'', ---- change HERE  email group!!
            @subject = @subject1,
            @body = @tableHTML,
            @body_format = ''HTML'',
            @file_attachments = ''D:\logs\Deadlock.xdl''; ---- change HERE  deadlock graph location!!
end
go
-- update the Deadlock table so that when the job runs it wont send out previous alert
update dba_db.dbo.Deadlock
set Notified = 1 where notified = 0
--SELECT * FROM dba_db.dbo.Deadlock',
        @database_name=N'dba_db',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

-- create an WMI alert to respond to deadlocks
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Deadlock')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Deadlock'
GO
DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'
EXEC msdb.dbo.sp_add_alert @name=N'Respond to Deadlock',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Monitoring - Deadlock Detector' ;
GO


Temporarily stop Log shipping How can I temporarily stop log shipping --> Do the task --> Start the Log shipping ? Is it possible ?

Yes simple way to temporarily stop Logshipping is to disable all the jobs related to Logshipping, since LS is basically backup of transaction logs and restore you have control over it. You need to disable Backup, copy and restore jobs. Please also make sure you disable LS_Alert jobs because they would fire and unnecessarily give you false alerts.

Caution:

If you stop the jobs for longer duration your database recovery stands exposed so be careful with that.

You also should keep in mind that the transaction log backup which would be done after you start the job might be big in size and it would take time to copy it and restore it. If the size is too big you might get some out of sync alerts so be ready for it.


Error Code : 1248 Every derived table must have its own alias in MySQL i have problem here,
when im trying to select with query below, i got error message

  Error Code : 1248 Every derived table must have its own alias

SELECT B.BRANCH_CODE, B.BRANCH_NAME, C.COMPANY_CODE, C.NAME, TSK.DATE_CREATE,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Payment'
    ELSE 'Payroll'
  END AS TRX_METHOD_E,
  CASE TB.BULK_TYPE
    WHEN 1 THEN 'Bulk Transfer'
    WHEN 2 THEN 'Bulk Pembayaran Tagihan'
    ELSE 'Pembayaran Gaji'
  END AS TRX_METHOD_I,
  TB.TOTAL_RECORD,
  TB.ACCOUNT_NO,
  TSK.TRX_TOTAL,
  TC.TOTAL_CHARGE,
  DATE(TSK.DATE_TRX) AS DATE_TRX,
  TB.REF_ID,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'All Success'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'All Failed'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Partial Success (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Pending Execution'
  END AS TRX_STATUS_E,
  CASE
    WHEN TSK.TRX_COUNT_SUCCESS = TSK.TRX_COUNT_ALL THEN 'Berhasil Semua'
    WHEN TSK.TRX_COUNT_FAIL = TSK.TRX_COUNT_ALL THEN 'Gagal Semua'
    WHEN TSK.TRX_COUNT_SUCCESS > 0 AND TSK.TRX_COUNT_FAIL > 0 THEN 'Berhasil Sebagian (' || TSK.TRX_COUNT_SUCCESS || '/' || TSK.TRX_COUNT_ALL || ')'
    ELSE 'Tunggu Eksekusi'
  END AS TRX_STATUS_I
FROM CB_TASK_BULKS TB
  JOIN CB_TASKS TSK ON TSK.REF_ID = TB.REF_ID
  JOIN CB_COMPANIES C ON C.COMPANY_ID = TSK.COMPANY_ID
  JOIN CB_BRANCHES B ON B.BRANCH_CODE = C.BRANCH_CODE,
(
  SELECT REF_ID, SUM(CHARGE) AS TOTAL_CHARGE
  FROM
  (
    SELECT XTB.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS XT
    JOIN CB_TASK_BULK_DETAILS XTBD ON XTBD.BULK_DETAIL_ID = XT.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB ON XTB.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB2.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_TRANSFERS_DOM XTD
    JOIN CB_TASK_BULK_DETAILS XTBD2 ON XTBD2.BULK_DETAIL_ID = XTD.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB2 ON XTB2.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID

    UNION

    SELECT XTB3.REF_ID, SUM(CHARGE) AS CHARGE
    FROM CB_PAYMENTS XP
    JOIN CB_TASK_BULK_DETAILS XTBD3 ON XTBD3.BULK_DETAIL_ID = XP.BULK_DETAIL_ID
    JOIN CB_TASK_BULKS XTB3 ON XTB3.REF_ID = XTBD.REF_ID
    GROUP BY XTB.REF_ID
  )
  GROUP BY REF_ID
) TC
WHERE TC.REF_ID = TSK.REF_ID
AND (TSK.TRX_COUNT_SUCCESS > 0 OR TSK.TRX_COUNT_FAIL > 0);

can somebody help me to find my problem here,
i think i've given all tables with his own "name" like TB for CB_BULKS_DATA (example)

You need to mention an alias for all your inline views / derived tables in MySQL, even when they are nested. In your code you are using a derived table inside another derived table but you seem to have forgotten to mention an alias for your inner derived table. Put an alias before the 4th line from the bottom which is GROUP BY REF_ID. Put an alias before this group by clause and you will be fine.


Partitioning in SQL Server 2008 I want to partition some of our tables in a SQL Server 2008 database.  The partition key will be date based.

I have several questions:

Is there a rule of thumb regarding the recommended number of rows per partition?
Is there any special problem creating a partition function with different intervals?
I mean for example, that the data from 2008, 2009, 2010 will be based on yearly partitions, and from 2011 and on it will be on a monthly based partitions.
Is it recommended to create a different partition function/scheme for each of the tables (they will be identical except their name) or can I create one monthly and one yearly partition function/scheme and assign the tables to them? What are the consequences for each option?
We have around 15 tables that we are considering partitioning. We want to keep all the data, hence only a split mechanism is needed. I thought to create a management table that will contain all the information on the different partition tables and to create one procedure that will run on all the partition functions and will split them. Another option is to create a different procedure for each of the partitioned tables. I prefer the first option because of its manageability, but I don't know if it will cause issues/contentions.

For what you want to do, I would recommend the following (which is pretty much what you were thinking).

1> Create history tables for the historic data you have - keep the schemas as similar as possible. Split up by some logical grouping (such as year/month) based on how they are going to be queried (say you need to report with in month/year as well as all). Do not worry about the table size of the splits unless they are getting into the TB size range (your dbms should handle it) just make sure that they are appropriately indexed for the queries that need to be run.  You should consider putting these onto a different disk to the active data if performance is an issue.

2> Create a routine to move data from the active table to the relevant historic table. Run this periodically. As a practice rebuild the indexes on the table that has had the data removed from it, and maybe update the table statistics. Easiest way to do this is to write a sql script.

3> Consider the reporting you want to do. If you want to only have to deal with 1 table when writing queries, create a view that joins the archived tables together. Create indexes on all the tables to suite the view. This way if you want all the data, select from the view. If you want data from a specific year/month, query that table. The view will look something like:

create view view_all_data as
select "Jan12" as month,a.* from data_Jan12 a
union
select "Feb12" as month,b.* from data_Feb12 b
....

I am assuming here that the system is not a highly used transactional system and that you have windows of low usage to run the analysis queries. If you need to maintain high levels of performance, you may like to do the above in a separate database (separate hardware) and port across the new data that you get from backups.


MySQL replication still failing after new import I had an issue with my MySQL replication from Master to Slave. An App we had by mistake wrote to the Slave and deleted some data as well, causing errors during the replication (delete errors, primary key not found errors etc.- error codes 1032, 1062 etc.).

Skipping all the errors didn't work as it seemed the data was beyond repair and once all the errors from multiple tables were skipped the replication continued failing as above. To this end we exported the data from the Master using MySQL Workbench (after following all the steps of stopping, granting permissions, flushing privileges, locking the master and then exporting the data through MySQL Workbench and unlocking the databases).

https://www.lexiconn.com/blog/2014/04/how-to-set-up-selective-master-slave-replication-in-mysql/ (example guide)

The .sql files were ported to the Slave, Slave was stopped, changed the MASTER to MASTER_HOST and imported all the data through the MySQL Workbench on the Slave. The database is rather large (30 odd Gigs) so this process takes over 24 hours. Once the Slave is then Started, again the errors appear (Duplication errors, cannot delete errors, Primary Key errors etc.).

Initially we used mysqldump to extract the data and MySQL to to re-import the data but this failed on the MySql importing complaining of errors in the SQL files (which are too large to open in any notepad and investigate). So we went for the WorkBench as an export and import (which seems to both function without any errors).

I cannot find any research online that deals with something like this, a failure of replication after clean export and import or where to go from here. The .sql files created by the WorkBench do all have Drop Tables commands before create tables, so surely the tables are deleted and created fresh so should not have any Primary Key concerns etc.

Does anyone have any guidance for this as any test we do has to run for 24 hours before we see the failure.

Much appreciated.

Take a full dump again on the master node with below command
 shell> mysqldump --single-transaction --master-data=2 --routines --triggers your_database_name > file.sql

--master-data=2 parameter will provide master's binary log position and file in file.sql
Restore the dump from master node in Slave node
login to slave and execute below
mysql> STOP SLAVE; RESET SLAVE ALL;
mysql> CHANGE MASTER TO MASTER_HOST='Master Node IP',
       MASTER_USER='replication_user',
       MASTER_PASSWORD='replication_user_password',
       MASTER_LOG_FILE='binary_log_file',
       MASTER_LOG_POS=binary_log_file_position;


pgAdmin4 Hide Alerts pgAdmin4 has introduced 'alerts' or 'toasts' in the UI that display the status of certain tasks.

There seems to be no way to dismiss these alerts, which obscure other parts of the UI, without first clicking on the 'Click here for details' link.  This opens yet another window, which you then can close to dismiss the alert.

I've searched the docs for any mentioned of 'alert', 'notification', 'toast', etc with no luck.  I would like to know if there is a way to hide multiple alerts at once, or change their default behavior, for example to get them to disappear after a few seconds (like an Android 'toast').

I am happy to report (in case it helps anyone else) that v2 of pgAdmin 4 addresses this issue by providing a close widget in each toast that comes up.


WHERE statement on an inner join with GROUP BY I need some assistance with the below. Essentially I am grabbing all lines that match certain parameters (matching itemid, custaccount, qtyordered). I need to also add a where statement to my inner join, i.e. where b.salesstatus in '3','4'. The issue I have is that I can't select salesstatus in my inner join unless I am also grouping by salesstatus? How can I add a where statement for the inner join that will be acceptable?

SELECT
 ST.DATAAREAID
,ST.CREATEDDATETIME
,ST.SALESID
,ST.SALESNAME
,SL.ITEMID
,SL.CUSTACCOUNT
,SL.QTYORDERED
,SL.SALESQTY
,SL.LINEAMOUNT
,SL.CUSTOMERREF
,ST.SALESPOOLID
,ST.RETURNITEMNUM
,CASE WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '1'   THEN 'Confirmed' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '2'     THEN 'Invoiced' WHEN CONVERT(nvarchar(12), SL.SAB_PICKINGSTATUS) = '0'   THEN 'OpenOrder' ELSE 'UNKNOWN' END AS PickStatus
,CASE WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '1' THEN 'OpenOrder'   WHEN CONVERT(nvarchar(12), SL.SALESSTATUS) = '3' THEN 'Invoiced' WHEN CONVERT  (nvarchar(12), SL.SALESSTATUS) = '4' THEN 'Cancelled' ELSE 'UNKNOWN' END AS   LineStatus
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
ON SL.SALESID = ST.SALESID
AND SL.DATAAREAID = ST.DATAAREAID
INNER JOIN dbo.INVENTTABLE ON SL.ITEMID = dbo.INVENTTABLE.ITEMID
AND     ST.DATAAREAID = dbo.INVENTTABLE.DATAAREAID
INNER JOIN (
  SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
  INNER JOIN dbo.SALESTABLE AS STABLE
ON LINE.SALESID = STABLE.SALESID
AND LINE.DATAAREAID = STABLE.DATAAREAID
GROUP BY
 LINE.CUSTACCOUNT
,LINE.QTYORDERED
,LINE.ITEMID
HAVING COUNT(*) > 1
) AS B
ON B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID
**strong text**WHERE (SL.CREATEDDATETIME >= '2019-01-01 00:00:00')  AND (SL.SALESSTATUS != '4') AND (SL.CUSTACCOUNT NOT IN (N'2ALD217')) AND (SL.ITEMID NOT IN (N'15MEGA', N'15TACK', N'10MEGA', N'10TACK')) AND (NOT (dbo.INVENTTABLE.SAB_INVENTCLASSGROUPID IN (N'CATBRO', N'CHARGES', N'KIPLREG', N'MERCH', N'POS', N'SAMPLES', N'SWAFLD'))) AND (SL.QTYORDERED > 1) AND (SL.CUSTOMERREF NOT IN (N'')) AND (ST.SALESPOOLID NOT IN (N'POSE', N'DISP', N'INTC'))

Can you just put LINE.SALESSTATUS in (3,4) in inner part of subquery like below:

 SELECT
  LINE.ITEMID
  ,LINE.CUSTACCOUNT
  ,LINE.QTYORDERED
FROM dbo.SALESLINE AS LINE
INNER JOIN dbo.SALESTABLE AS STABLE ON LINE.SALESID = STABLE.SALESID AND LINE.DATAAREAID = STABLE.DATAAREAID
where  LINE.SALESSTATUS in (3,4)
GROUP BY LINE.CUSTACCOUNT,LINE.QTYORDERED,LINE.ITEMID
HAVING COUNT(*) > 1

may be it helps you to get filtered record sets inside the inner section.


Sampling in PostgreSQL I am looking for possible ways of random sampling in PostgreSQL. I found a couple of methods to do that with different advantages and disadvantages. The naive way to do that is:

select * from Table_Name
order by random()
limit 10;

Another faster method is:

select * from Table_Name
WHERE random() <= 0.01
order by random()
limit 10;

(Although that 0.01 depends on the table size and the sample size; this is just an example.)

In both of these queries a random number is generated for each row and sorted based on those random generated numbers. Then in the sorted numbers the first 10 are selected as the final result, so I think these should be sampling without replacement.

Now what I want to do is to somehow turn this sampling methods into sampling with replacement. How is that possible? Or is there any other random sampling method with replacement in PostgreSQL?

I have to say that I do have an idea how this might be possible but I don't know how to implement it in Postgres. Here is my idea:

If instead of generating one random value we generate S random values where S is the sample size, then order all of the random generated values, it will be sampling with replacement. (I don't know if I am right.)
At this point I don't mind about the performance of the query.

Corrective

If instead of generating one random value we generate S random values
where S is the sample size, then order all of the random generated
values, it will be sampling with replacement. (I don't know if I am right.)

You are wrong in two points.

Your original query does not generate "one random value". random() is a volatile function that is called for every row. Then all rows are sorted by the result, that's why this is hugely inefficient for big tables.
Accordingly, the approach "if we generate S random values ..." is going nowhere. You have to "granulate" random rows to matching "row numbers" - either existing IDs or surrogate numbers. That's what I am going to demonstrate.

I know you said:

At this point I don't mind about the performance of the query.

So @ypercube's answer is correct as usual. But my heart is bleeding. There are much faster ways.
Assuming the worst case that you don't know anything about the table.
Attach a sequential number to each row in arbitrary order, then you can select random rows reliably, multiple times if you want. The table is scanned once, which is still expensive for huge tables and small samples, but much better than scanning n times:
WITH t AS (SELECT *, row_number() OVER () AS rn FROM tbl)
SELECT * FROM (
    SELECT trunc(random() * (SELECT max(rn) FROM t))::int + 1 AS rn
    FROM   generate_series(1, 10) g
    ) r
JOIN   t USING (rn);

Each row has the same chance to be selected any number of times.
If you have an ID column with few or no gaps there are much faster options for big tables:

Best way to select random rows PostgreSQL


Where can I find some guidance on index strategies? Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.

As a general rule, which fields should be indexed?
Which fields should not be indexed?
What are the rules for using indexes while striking a balance between too many and not enough indexes in order achieve performance improvements, not degradation?

Short

The "too many indexes" rule is a bit misleading I think.

Long

Given that the average database is around 98% reads (or higher) reads need to be optimised. An INSERT is a read if there is a unique index, for example. Or the WHERE on an update. I once read that even a write intensive database is still 85% reads.

What you do have is poor quality indexing. Examples:

wide clustered indexes (SQL Server especially)
non-monotonic clustered indexed
overlapping indexes (eg cold, cole and cold, cole, colf)
many single column indexes (also overlapping with more useful indexes) that are useless for your queries
no INCLUDEs, not covering (eg all single column indexes)
...

Note it's quite typical to have indexes several times bigger than your actual data even in OLTP systems.

Generally, I'd start with the

clustered index (usually PK)
unique indexes (not constraints, these can't be covering)
foreign key columns

Then I'd look at:

common queries and see what I need. A query running every seconds needs tuning. The report at Sunday 4am can wait.
with SQL Server, the weighted missing index DMVs

Saying that, I have broken these rules for some systems after seeing how things panned out (10 billion rows later) to tune a system. But I'd never consider not indexing unless I could demonstrate why I'm doing so.


How to delete control file in oracle 11g I am practicing the rman backup in oracle 11g.and how to dalete the control file. Please help.

rm -rf \oracle\app\oracle\oradata\xe\control.dbf

it did not work at hosting server.

RMAN is a oracle utility to backup, restore & recovery of database.

The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)

Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.

Note: Be sure you have installed oracle 11g successfully in your environment.
I had asked to you about O.S environment. I didn't get any clue from you. So as a example i am writing the all RMAN backup steps in Windows O.S environment.

First open the command prompt and try to connect sql server as per your environment credential. for example

Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.

C:\> set Oracle_sid=orcl
c:\> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 26 10:17:22 2017

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

Connected to an idle instance.

Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?

SQL> select log_mode from v$database;

LOG_MODE

NOARCHIVELOG
Database is in NOARCHIVELOG mode.

Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step # 4: Startup the database in mount state.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.

Step # 5: Configure database in ARCHIVELOG mode.

SQL> alter database archivelog;
Database altered.

Step # 6: Alter database to open state.

SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

Step # 7: Ensure ARCHIVELOG destination.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     330
Next log sequence to archive   332
Current log sequence           332

Step # 8: Ensure the flash/fast recovery area location.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\app\xyz\flash_recove
                                                 ry_area
db_recovery_file_dest_size           big integer 3852M

Note : Here i did 'xyz' in C:\app\xxx\flash_recovery_area. due to security reason.

Step # 9: Connect to RMAN prompt with target database.

C:\>rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 30 12:32:57 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1464491423)

Step # 10 : To show the all configuration of your RMAN through this command.

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\xyz\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

Note : -  As per this statement  'CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';' I hope you have created the folder '/backup/rman' in your c: drive.

Typically we’ll use “BACKUP AS BACKUPSET” to backup a database. So, to take a full backup of the database without the archive logs, do the following.

RMAN> BACKUP AS BACKUPSET DATABASE

RMAN> backup as backupset database;

Starting backup at 30-JUL-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\xyz\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00001 name=C:\APP\xyz\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00003 name=C:\APP\xyz\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=C:\APP\xyz\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APP\xyz\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=C:\BACKUP\RMAN\FULL_11SAL7BV_33_1 tag=TAG20170730T125212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=C:\BACKUP\RMAN\FULL_12SAL7G7_34_1 tag=TAG20170730T125212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-JUL-17

For further your ref Here and Here


How to normalize Time Sheet data? I am having a terrible time finding an appropriate way to store this data in a database.

The current excel (simple) version looks something like this:

http://i.stack.imgur.com/BEZOw.png

Staff and Client also have additional data associated with them, so it makes sense to use a foreign key relationship there. However, I am unsure how to store the hours data. Obviously you could make a column for each Time Period, however this seems like a very bloated approach.

I then thought that each "cell" that contains hours data can be represented like this:

http://i.stack.imgur.com/4VTXQ.png

However, this seems like the Staff and client key values are stored too many times. I have never designed a proper database before and would greatly appreciate suggestions.

*Sorry for not directly linking images, I do not have enough rep on this sub-site

You want to have something like this:

This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)


Adding data in a bridge table automatically Basically the concept is that a customer can place orders from multiple addresses and an address can have multiple customers, like different members of the same family.

CREATE TABLE Customer (
  cust_id    NUMBER PRIMARY KEY,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE Address (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE Customer_Address (
  cust_id    NUMBER REFERENCES Customer ( cust_id ),
  Address_id NUMBER REFERENCES Address ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

Now I want to automate the process of entry of ids into the Customer_Address table i.e. When a record is added into the customer and address table, the ids are automatically added into the customer_address table.

I have tried this by creating a view where I inserted the ids of customer and address tables and then using the instead of insert trigger to populate the junction table but it did not work.

I created the view and trigger:

CREATE VW_CUSTADD AS
SELECT CUST_ID, ADDRESS_ID FROM CUSTOMER, ADDRESS;

CREATE OR REPLACE TRIGGER TG_CustAdd
INSTEAD OF INSERT ON VW_CustAdd
FOR EACH ROW
ENABLE
BEGIN
INSERT INTO CUSTOMER_ADDRESS VALUES (:NEW.CUST_ID,:NEW.ADDRESS_ID);
END;

The data is getting inserted into the view but not the junction table (Customer_Address). Have I made any mistake?

When contemplating the problem that your are describing, I thought that a VIEW and a TRIGGER won't give you the solution you are after.  (My current view is: a VIEW should be used for viewing or for creating "virtual tables", and TRIGGERs are best avoided ... but that's just me.)  Instead, I have written some example code for a PROCEDURE that could do the job.  I have changed the table names slightly (I have used the prefix "se" for "stackexchange"), but have kept the column names and the constraints:

-- DDL code
CREATE TABLE SECustomer (
  cust_id    number primary key,
  cust_name  VARCHAR2(100),
  cust_phone VARCHAR2(20)
);

CREATE TABLE SEAddress (
  address_id NUMBER PRIMARY KEY,
  address    VARCHAR2(500),
  area       VARCHAR2(100)
);

CREATE TABLE SECustAdd (
  cust_id    NUMBER REFERENCES SECustomer ( cust_id ),
  Address_id NUMBER REFERENCES SEAddress ( address_id ),
  PRIMARY KEY ( cust_id, address_id )
);

For the procedure's logic, I have more or less coded what you have specified in your comment (27 Apr 2017, 1221 hours):

  "The first thing added is the phone no. of the customer. Then we check
  if the phone no. already exists in the customer table we use the id
  corresponding to that phone no. else we create a new id and add
  customer details and address details."

I have sprinkled some comments into the code, so that it is easier to see what's going on.  Also, there are quite a few dbms_output.put_line() statements.  These should be removed if you really use the procedure in a "serious" setting.

create or replace procedure addnewclient (
  phone VARCHAR2
, customername VARCHAR2
, anaddress VARCHAR2
)
as
  phonecount number := 0 ;
  newcustid number := 0 ;    -- only needed if the customerid is NOT generated
  maxcustid number := 0 ;    -- latest/highest customer_id using a particular phone number
  newaddressid number := 0 ; -- only if addressid is NOT generated
  existingaddr number := 0 ;
  errorcode number := 0 ;
  errormsg varchar2(128) := '' ;
begin
  -- does the phone number exist?
  select count(cust_phone) into phonecount from secustomer
  where phone = cust_phone ;

  -- if ids are NOT "generated always as identity"
  select max(cust_id) + 1 into newcustid from secustomer ;

  if phonecount > 0 then        -- phone number exists, use an existing address
     dbms_output.put_line('phone number ' || phone || ' EXISTS in customer table') ;
     -- Use the newest customerid (in customer table)
     select max(cust_id) into maxcustid from secustomer where cust_phone = phone ;
     insert into secustomer (cust_id, cust_phone) values (newcustid, phone);
     dbms_output.put_line('  INSERT (Customer) -> new customer id, existing phone number ') ;

     -- Find the address id for the newest customerid (in intersection)
     select address_id into existingaddr from secustadd where cust_id = maxcustid ;
     insert into secustadd values (newcustid, existingaddr) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, existing address id') ;

  elsif phonecount = 0 then     -- we need to record a new address
     dbms_output.put_line('phone number ' || phone || ' does NOT exist in customer table') ;
     -- Generate a new id (only needed if customer_id is NOT generated!)
     select max(address_id) + 1 into newaddressid from seaddress ;

     insert into secustomer (cust_id, cust_phone) values (newcustid, phone) ;
     dbms_output.put_line('  INSERT (Customer) -> new customer id, new phone number ') ;
     insert into seaddress (address_id, address) values (newaddressid, anaddress) ;
     dbms_output.put_line('  INSERT (Address) -> new address id, a new address ') ;
     insert into secustadd values (newcustid, newaddressid) ;
     dbms_output.put_line('  INSERT (CustAddr) -> new customer id, new address id ') ;

  end if;
exception
  when others then
    errorcode := SQLCODE;
    errormsg := SUBSTR(SQLERRM, 1, 128);
    dbms_output.put_line('ERROR: ' || errorcode || '  ' || errormsg ) ;
end;

I have assumed that you are NOT using auto-ids (...generated always as identity).  For testing, I suggest that you INSERT just one line of test data into each table - for starters -, and then call the procedure from an anonymous block (several times).

-- insert some test data
begin
  insert into secustomer (cust_id, cust_phone)  values (1, '123456789') ;
  insert into seaddress (address_id) values (50) ;
  insert into secustadd values (1, 50) ;
end;

-- testing:

begin
  -- phone number exists
  addnewclient('123456789', 'first_new', 'newaddress1') ;
end;

begin
  -- phone number does not exist
  addnewclient('333444555', 'second_new', 'newaddress2') ;
end;

I'm sure this will need some more tweaks (exception handling etc), but it should get you started.
Best of luck!


The certificate, asymmetric key, or private key file is not valid or does not exist I'm trying to perform a restore of a database from a different server onto my localhost server .That database is encrypted (the bak file ) ,so I have to decrypt it ,that's fine I have all the cert files and the password ,but for some reason I'm still getting the following error :

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

This is the code I'm executing:

      CREATE CERTIFICATE [Certificate1]
FROM FILE = 'C:\Location of the certs'
WITH PRIVATE KEY (
    FILE = 'C:\Location of the certs' ,
    DECRYPTION BY PASSWORD = 'password'
);

PS. I'm no expert at backups and restores .

Here are the steps that I had to take to restore the encrypted database onto a different server(localhost).

1.Create the certificates :

CREATE CERTIFICATE [CertficateName]
FROM FILE = 'C:\FolderName\NameOfCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\FolderName\NameOfCertKey.key' ,
    DECRYPTION BY PASSWORD = 'YourPassword'
);

2.

USE Master ;
Open Master Key Decryption by password = 'YourPassword'
Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'YourPassword';
    GO

3.Restore Master Key

Use master
    restore master key
    FROM FILE = 'C:\FolderName\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'YourPassword'
    ENCRYPTION BY PASSWORD = 'YourPassword'

4.
This is the last step you ,be careful at this stage as it took me a while to get that each command needs to be run separately :

Alter Database [DatabaseName]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

Next run it separatly

USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

Next command run separatly

RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Folder\FULL\NameoftheBakFilethat ourAreRestoring.BAK'
WITH Replace , STATS = 5

Do not forget to set back to Multi-User mode the Dabase.


Number of rows summed up by grouping them, each group has a different number of rows Every 4 rows need to be summed up and displayed against that group but every group has different number of rows, at the end of the group for the odd rows sum is different.

For example, in the group if just one row is there then it will be value = X + last 3 rows value from the previous group similarly if 2 rows remaining  for first row in the group will be X + last 3 rows value from the previous group for the 2nd row it will be from the previous group X + previous row from the group and  last 2 rows value from the previous group at the end it should be total 4 rows.

I have tried this query doesn't seem to be working for some reason:

            declare @temptable table(RetailerID int, retailer nvarchar(50),category nvarchar(50),Weekid int,value bigint)
            Insert into @temptable values(34,'R1','a',1,10)
            insert into @temptable values(34,'R1','a',2,10)
            insert into @temptable values(34,'R1','a',3,4)
            insert into @temptable values(34,'R1','a',4,5)
            insert into @temptable values(34,'R1','b',1,2)
            insert into @temptable values(34,'R1','b',2,4)
            insert into @temptable values(34,'R1','b',3,5)
            insert into @temptable values(34,'R1','b',4,3)
            insert into @temptable values(34,'R1','b',5,1)
            insert into @temptable values(34,'R1','c',1,35)
            insert into @temptable values(34,'R1','c',2,68)
            insert into @temptable values(34,'R1','c',3,43)
            insert into @temptable values(34,'R1','c',4,4)
            insert into @temptable values(34,'R1','c',5,5)

            select retailer,category,Weekid,isnull(sum(value),0)    from @temptable td
            where Weekid  between 1 and 12 and RetailerID=34
            group by retailer,category,Weekid  order by retailer,category,Weekid;
            with cte as
            ( select retailer,category,Weekid,value,
                    Rangkgrp = (row_number() over (order by category) - 1) / 4
            from @temptable
            )
            select retailer,category,Weekid,value,
                sumofvalue=sum(value) over (partition by Rangkgrp),Rangkgrp
            from cte
            order by category,Weekid

Result my query produces which is not what I am looking for. The last row in category b is adding up values from category C ,this is not what I want it has to be from Category b and in category C last two rows are incorrect Category C with Week4 should be 150 and with Week5 should be 120.

How 13 and 120 are calculated: It's Category b Week 2 + 3 + 4 +5 values which is (4+5+3+1) and for category C its again the same thing Week 2 + 3 + 4 +5 values which is (68+43+4+5)=120 and if there was another row in Category b/c it should be Week 3+4+5+6 and so on there can be gap in the weeks but that doesn't matter it should just pick up the next week in the group but under same category.

Let me try to explain again for category a we have 4 rows which is perfectly fine so the value is W1+2+3+4 = sum(10+10+4+5)=29 so against category I am adding one more column and that 29 will be displayed against all the weeks for category a similarly for category b and c I am adding up w1+2+3+4 =sum(2+4+5+3)=14 will be displayed against category b for first 4 rows now I am left with W5 for category b so W5 is calculated as current week(W5) + 3 Previous week)  and that value should be displayed against Weeks 5 in category b.

In the same category there are many other weeks ex : first partition has 88 weeks it's just calculating the values for first 4 rows then it starts adding up current - 3 previous weeks it shouldn't work that way ..it should be like 22 groups of 4 rows for each group value should be the sum of 4 rows where as if its 78 rows in the partition then 19 groups of 4 rows + 3 rows where it should perform for each row Current weeks + 3 preceding weeks and here I am not sure each partition might have odd or even number of weeks.

You seem to need rolling sums over windows of 4 rows - so there need to be some correction for the first 3 rows of each group inside every partition:

with cte as
(   select
        RetailerID, Category, WeekId, value,
        row_number() over
            (partition by RetailerID, Category
             order by WeekId)
            as rn,
        count(*) over
            (partition by RetailerID, Category)
            as tc,  -- total count per partition
        sum(value) over
            (partition by RetailerID, Category
             order by WeekId
             rows between 3 preceding
                      and current row)
            as sv
    from temptable
    where Weekid between 1 and 12
      and RetailerID = 34
)
select
    RetailerID, Category, WeekId, value,
    case when rn <= floor(tc / 4) * 4
        then case (rn % 4)
                 when 1 then lead(sv, 3) over w
                 when 2 then lead(sv, 2) over w
                 when 3 then lead(sv, 1) over w
                 else sv
             end
        else sv
    end as sum_value
from
    cte
window w as
    (partition by RetailerID, Category order by WeekId) ;

Test at dbfiddle.uk.

Works in MySQL 8 and SQL Server (2012+).


Deadlock occur in a delete insert scenario <deadlock-list>
  <deadlock victim="process14012e08">
    <process-list>
      <process id="process14012e08" taskpriority="0" logused="148"
               waitresource="PAGE: 28:1:762341" waittime="6583"
               ownerId="54542271281" transactionname="DELETE"
               lasttranstarted="2013-06-22T01:04:20.450" XDES="0x64de49950"
               lockMode="S" schedulerid="26" kpid="13336" status="suspended" spid="1239"
               sbid="0" ecid="0" priority="0" trancount="2"
               lastbatchstarted="2013-06-22T01:00:19.120"
               lastbatchcompleted="2013-06-22T01:00:19.117" hostpid="15675"
               isolationlevel="read committed (2)" xactid="54542271281" currentdb="28"
               lockTimeout="4294967295" clientoption1="671350816" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="3" stmtstart="134" stmtend="332"
                 sqlhandle="0x02000000e5eb1a38144fa5c164b3c876cc2c8da059bf3bb1">
            delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          </frame>
        </executionStack>
        <inputbuf>
          set rowcount 10000
          while 1 = 1
          begin delete from grid_col where grid_col_id not in (select grid_col_id from grid)
          if @@rowcount = 0
          break
          end
          set rowcount 0
        </inputbuf>
      </process>
      <process id="process10031288" taskpriority="0" logused="912"
               waitresource="OBJECT: 28:731149650:16 " waittime="40270"
               ownerId="54542276360" transactionname="insertRows"
               lasttranstarted="2013-06-22T01:04:23.753" XDES="0x1641ef9400"
               lockMode="IS" schedulerid="17" kpid="29104" status="suspended" spid="351"
               sbid="0" ecid="0" priority="0" trancount="2"
               lastbatchstarted="2013-06-22T01:04:23.753"
               lastbatchcompleted="2013-06-22T01:04:23.660"
               isolationlevel="read committed (2)" xactid="54542276360"
               lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="check_grid_col" line="1" stmtstart="226" stmtend="376"
                 sqlhandle="0x03001c00fe2706376157a70026a000000000000000000000">
            SELECT @retval = COUNT(*) from grid_col where grid_col_id = @grid_col
          </frame>
          <frame procname="adhoc" line="1" stmtstart="34376" stmtend="35270"
                 sqlhandle="0x02000000434dff26c45f91c6fdb640bbbf3a4a276f6ac605">
            INSERT INTO grid (grid_id,grid_col_id,value) VALUES ( @P2 , @grid_col_id,@P3)
          </frame>
        </executionStack>
        <inputbuf>
          (@P0 bigint,@P1 int,@P2 char(300))
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <pagelock fileid="1" pageid="762341" dbid="28" objectname="grid"
                id="lock1cb9187500" mode="IX" associatedObjectId="72057594070695936">
        <owner-list>
          <owner id="process10031288" mode="IX" />
        </owner-list>
        <waiter-list>
          <waiter id="process14012e08" mode="S" requestType="wait" />
        </waiter-list>
      </pagelock>
      <objectlock lockPartition="16" objid="731149650" subresource="FULL"
                  dbid="28" objectname="grid_col" id="lock12e6c78f00" mode="X"
                  associatedObjectId="731149650">
        <owner-list>
          <owner id="process14012e08" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process10031288" mode="IS" requestType="wait" />
        </waiter-list>
      </objectlock>
    </resource-list>
  </deadlock>
</deadlock-list>

An example of how it can happen:

Two processes, each one has a shared lock on the same page, then an exclusive lock is needed by each process. Each process can't exclusively lock the page because of the shared row locks.

So, a deadlock happens on the same page.

More examples here from @AlexK


Any way to estimate the required space for a SQL dump file? I want to import a Wikipedia XML dump file into MySQL. The uncompressed XML dump file is 65GB. Does this mean I should free up almost 64GB in the MySQL data path? Is there any way to estimate the required space?

Here is the sql schema:https://git.wikimedia.org/blob/mediawiki%2Fcore.git/HEAD/maintenance%2Ftables.sql

I have dealt with questions like this in both directions

Mar 20, 2013 : How can I determine the size of a dump file prior to dumping?
May 21, 2012 : How large will a MySQL database be relative to the dump file?

Your question is a little different because you did not give anything about the target MySQL Instance (what Storage Engine will be used, which columns are TEXT/BLOB, what indexing is needed for your data, etc.)
IMHO the most straightforward way I can think of is the following
METHOD #1
Write some Perl/Python script to sum the length of all XML tag delimiters. Subtract the sum you get from the length of the file. That would essentially be the size of a CSV file less delimiters. This would be the bytes of the raw data.
METHOD #2
Do METHOD #1 and also take the count of all XML tag delimiters and add that count to the result of METHOD #1. That would like adding commas back into a CSV file. This make be closer   in size to an actual CSV should you print the output to such a file.


How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6 I want to upgrade my MySQL 5.5.40 server to 5.6.28. But as per the documentation we need to alter the tables having columns which are using date or datetime or timestamp data types as storage requirement for these data types changed and to allow fractional parts for these temporal data types.

https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

ALTER TABLE tbl_name FORCE;

But how can we confirm that storage is altered. Is there any query or something to check that table columns are updated. What are the consequences if we don't alter the table and just done an in place upgrade from 5.5 to 5.6?

Without the upgrade, you cannot use fractions of a second.

Old data structure was 4 bytes for TIMESTAMP, 8 bytes for DATETIME.
New structure is 5 bytes for each, depending on how many decimal places of time you pick.
If you later allow for fractional seconds, the space is up to 8 bytes.


Oracle 10.2.0.1.0 listener process stopped When I am checking my servers I see that there is an error about listener process stopped.
I logged in and checked the status of listenere. please see the below status;

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
      Solaris Error: 146: Connection refused
        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
          TNS-12541: TNS:no listener
            TNS-12560: TNS:protocol adapter error
              TNS-00511: No listener
                Solaris Error: 2: No such file or directory>

I am thinking of just running the start command but my concern is why is this issue arised ?

There is probably not enough logged at the default level to give you any real insight to root cause, so the best thing to do is restart the listener at a higher logging level. Enter

LSNRCTL> trace admin
LSNRCTL> save_config
LSNRCTL> start

Then if it crashes again, you'll know why. You can also set the variables trc_directory, trc_file and trc_level to customize this behavior.

Also your HOST=0.0.0.0 looks odd to me... Shouldn't that be the IP address of your host? What do you have in LISTENER.ORA?


Matching single column against multiple values without self-joining table in MySQL We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:

user_id     question_id     answer_value
Sally        1               Pooch
Sally        2               Peach
John         1               Pooch
John         2               Duke

and we want to find users who answer 'Pooch' for question 1 and 'Peach' for question 2, the following SQL will (obviously) not worK:

select user_id
from answers
where question_id=1
  and answer_value = 'Pooch'
  and question_id=2
  and answer_value='Peach'

My first thought was to self-join the table for each answer we are looking for:

select a.user_id
from answers a, answers b
where a.user_id = b.user_id
  and a.question_id=1
  and a.answer_value = 'Pooch'
  and b.question_id=2
  and b.answer_value='Peach'

This works, but since we allow an arbitrary number of search filters, we need to find something much more efficient. My next solution was something like this:

select user_id, count(question_id)
from answers
where (
       (question_id=2 and answer_value = 'Peach')
    or (question_id=1 and answer_value = 'Pooch')
      )
group by user_id
having count(question_id)>1

However, we want users to be able to take the same questionnaire twice, so they could potentially have two answers to question 1 in the answers table.

So, now I'm at a loss. What's the best way to approach this? Thanks!

I have found a clever way to do this query without a self join.

I ran these commands in MySQL 5.5.8 for Windows and got the following results:

use test
DROP TABLE IF EXISTS answers;
CREATE TABLE answers (user_id VARCHAR(10),question_id INT,answer_value VARCHAR(20));
INSERT INTO answers VALUES
('Sally',1,'Pouch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duke');
INSERT INTO answers VALUES
('Sally',1,'Pooch'),
('Sally',2,'Peach'),
('John',1,'Pooch'),
('John',2,'Duck');

SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id;

+---------+-------------+---------------+
| user_id | question_id | given_answers |
+---------+-------------+---------------+
| John    |           1 | Pooch         |
| John    |           2 | Duke,Duck     |
| Sally   |           1 | Pouch,Pooch   |
| Sally   |           2 | Peach         |
+---------+-------------+---------------+

This display reveals that John gave two different answers to question 2 and Sally gave two different answers to question 1.

To catch which questions were answered differently by all users, just place the above query in a subquery and check for a comma in the list of given answers to get the count of distinct answers as follows:

SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A;

I got this:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           1 | Pooch         |                 1 |
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
| Sally   |           2 | Peach         |                 1 |
+---------+-------------+---------------+-------------------+

Now just filter out rows where multianswer_count = 1 using another subquery:

SELECT * FROM (SELECT user_id,question_id,given_answers,
(LENGTH(given_answers) - LENGTH(REPLACE(given_answers,',','')))+1 multianswer_count
FROM (SELECT user_id,question_id,GROUP_CONCAT(DISTINCT answer_value) given_answers
FROM answers GROUP BY user_id,question_id) A) AA WHERE multianswer_count > 1;

This is what I got:

+---------+-------------+---------------+-------------------+
| user_id | question_id | given_answers | multianswer_count |
+---------+-------------+---------------+-------------------+
| John    |           2 | Duke,Duck     |                 2 |
| Sally   |           1 | Pouch,Pooch   |                 2 |
+---------+-------------+---------------+-------------------+

Essentially, I performed three table scans: 1 on the main table, 2 on the small subqueries. NO JOINS !!!

Give it a Try !!!


How to find the counts of word occurrences in a column? I have a column containing text in postgresql. Now I want to find, for a given input word, the counts of occurrences of the word in my text column.
So I have
  date_col   |  text_col
-------------+-----------------------
 2021-04-02  | This is a test.
-------------+---------------------
 2021-03-30  | A test is a test.
-------------+---------------------
 2021-03-30  | How to test?
-------------+---------------------
 2021-04-01  | One more test

and I want this result for the word 'test':
 count | num_occurrences
-------+-----------------
   3   |  1
-------+-----------------
   1   |  2

meaning 3 times there was excactly one occurrence of "test". Once there were two occurrences of "test" in the same row.
Later, I want to be able to query a given period with the same query.
My initial take was to create a new table with a row for every word like so:
  date_col  |  word
------------+------------------
 2021-04-02 |  This
------------+------------------
 2021-04-02 |  is
------------+------------------
 2021-04-02 |  a

and do some counting and grouping. But is there a better way?

You need to first split each string into multiple words and count the number of times each word occurs inside the string. Splitting can be done using regexp_split_to_table() which yields one row per word:
select w.word, w.num_occurrences
from the_table t
  cross join lateral (
     select word, count(*) as num_occurrences
     from regexp_split_to_table(lower(t.text_col), '[\s[:punct:]]+') as x(word)
     where word <> ''
     group by word
  ) w

This returns the following given your sample data:
word | num_occurrences
-----+----------------
test |               1
a    |               1
is   |               1
this |               1
test |               2
a    |               2
is   |               1
test |               1
how  |               1
to   |               1
test |               1
more |               1
one  |               1

This can be grouped by num_occurences to get the result you want:
select count(*), num_occurrences
from (
  select w.word, w.num_occurrences
  from the_table t
    cross join lateral (
       select word, count(*) as num_occurrences
       from regexp_split_to_table(lower(t.text_col), '[\s[:punct:]]+') as x(word)
       where word <> ''
       group by word
    ) w
  where w.word = 'test'
) t
group by num_occurrences
order by 1 desc

And the result of that is:
count | num_occurrences
------+----------------
    3 |               1
    1 |               2


SQL Server statement takes forever while running instantly in Oracle Please help me interpreting this statement and plan:

https://www.brentozar.com/pastetheplan/?id=Bysy6YtEV

We migrated from Oracle to SQL Server and we have some quite strange behaviours. It might be related to a problem during migration.

I found it hard to interpret the execution plans. Both environments should have the same structure and indexes. Statistics should be up to date.
Settings in SQL Server:

Create Auto Statistics Enabled
optimize for Ad Hoc Queries = true
Snapshot Isolation enabled
Max Parallel = 4
Threshold 50

DB is 600 Gb in size, 16 cores, 160 Gb memory.

The query:

SELECT
    COUNT ( t_01.rsecondary_objectu ) AS selectExpr
FROM
    PIMANRELATION t_01 ,
    PITEM t_02 ,
    PITEMREVISION t_03
WHERE
    (   (   (   ( t_01.rprimary_objectu = t_02.puid )
            OR  ( t_01.rprimary_objectu = t_03.puid )
            )
       AND
            ( t_01.rrelation_typeu = 'w8INy241VJFL2B' )
        )
   AND  t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    )

Oracle

We found out that the issue is / was related and depend on the data. If I select a different item to copy in the GUI (it's actually a copy thing and any how the app does these statements) it works instant. The query then once working fine looks a little different: https://www.brentozar.com/pastetheplan/?id=SJo-h2c44

SELECT  COUNT ( t_01.rsecondary_objectu ) AS selectExpr FROM PIMANRELATION t_01 , PITEM t_02 , PITEMREVISION t_03 WHERE ( ( ( ( t_01.rprimary_objectu = t_02.puid ) OR ( t_01.rprimary_objectu = t_03.puid ) )  AND ( t_01.rrelation_typeu = 'w8INy241VJFL2B'  ) ) AND t_01.rsecondary_objectu IN  ('wBHZpD0uVJFL2B' , 'V2PlBGlAVJFL2B' , 'lnHlBGlAVJFL2B'  ) )

SQl Server seems to completely struggle with the one I mentioned before (running infinite) while providing the second one almost instantly. That's so crazy weird. Like a buggy product.

I don't have influence on the statements as they are produced by the app.

CE 110 / 70 results in the same plan. Even if disable all custom indexes and only keep the ones suggested by the app it behaves the same. All primary keys are clustered indexes. But maybe something messed during our migration. But it's quite strange. Most stuff runs fine, but the query at topic is extreme. I let it run für 45 minutes in SQL Server. It simply does not complete.

An other example. Our Prod Environment gets unuable once the highest rated index is created:

Index:
CREATE INDEX EN_PIPRELEASESTATUS_1 ON [TCEUP01].[dbo].[PRELEASESTATUS] ([pname], [pdate_released]) INCLUDE ([puid])

Results in this query:
https://pastebin.com/Ax3qTUjd
===>Took 137.142 seconds

our Test Environment, which should be the same, behaves differently:
https://pastebin.com/0PTZTJpr
===>Took 3.884 seconds

the problematic plan looks like this:
https://www.brentozar.com/pastetheplan/?id=Skvy0qRNE

and it seems it can run like infinite.

The Oracle optimizer uses OR Expansion to improve efficiency of the query. Quoting from the documentation:

  In OR expansion, the optimizer transforms a query with a WHERE clause
  containing OR operators into a query that uses the UNION ALL operator.

  The database can perform OR expansion for various reasons. For
  example, it may enable more efficient access paths or alternative join
  methods that avoid Cartesian products.

You can think of the new query as being written like this:

SELECT
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEMREVISION t_03 ON t_01.rprimary_objectu = t_03.puid
        CROSS JOIN PITEM t_02
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
)
+
(
    SELECT
        COUNT ( t_01.rsecondary_objectu ) AS selectExpr
    FROM
        PIMANRELATION t_01
        INNER JOIN PITEM t_02 ON t_01.rprimary_objectu = t_02.puid
        CROSS JOIN PITEMREVISION t_03
    WHERE
    t_01.rrelation_typeu = 'w8INy241VJFL2B'
    AND t_01.rsecondary_objectu = '2yLJkWqiVJFL2B'
    AND LNNVL(t_01.rprimary_objectu = t_03.puid)
)
from dual;

Now there is an equality condition for both parts of the query, so Oracle can use indexes to perform an efficient nested loop join for both. It still needs to do a cross join for both parts of the query, but the size of the intermediate result sets is significantly reduced compared to doing two cross joins in the same query. For example, if PIMANRELATION has 1 relevant row and PITEMREVISION and PITEM both have a million rows then you get a trillion rows if you cross join them together. However, if you split the queries up then you only end up with a million rows for both.

The SQL Server query optimizer has a rule that can transform OR into UNION ALL: SelToIdxStrategy. There's no documentation for this and the only reference I could find is this answer. However, that rule will not be applied in this context. Instead, you get two cross joins which can only be implemented through nested loop joins. For each relevant row in PIMANRELATION, SQL Server will cross join to all rows in PITEM, then cross join to all rows in PITEMREVISION, and will finally filter out rows after that. You can easily end up with trillions of rows getting filtered down.

I have bad news for you. If you truly cannot change any part of the query text and you need that query to perform well then SQL Server probably isn't the right platform for your application. Databases have different strengths and weaknesses and you may need to change your queries to accommodate those differences.


Securing LOAD DATA LOCAL I need to periodically import significant number of rows from a CSV file. I'd like to be able to open up the least amount of access to the user (automation script) for this job.

Is it possible to limit LOAD DATA LOCAL to a specific table and user? If not, I'm thinking of creating a "staging database", load-data-local into it then INSERT-SELECT across two databases under the same MySQL instance.

Any other suggestions?

Create stored procedure which performs LOAD DATA INFILE.
Specify its DEFINER attribute as a user who have rights to import data (root@localhost, for example).
Specify SQL SECURITY DEFINER attribute.
Grant the user to execute this procedure.

Result: when user calls this SP it is executed with the rights of its definer - it is enough to import data. Even if the user called SP have no rights to this table at all (I'm not talking about the FILE right).

You can check any additional conditions in the procedure (current username, hostname, day of week, etc.) and perform import or not dependent by them.

Filename, tablename, additional data can be transferred into SP as parameters and/or via user-defined variables (which existence and value can be additional checkpoint).


Why columns in views are always nullable? I'm using postgreSQL. Why the fields in the views always are nullable? I get that in some situation computed columns can be NULL, but in the below snippet I only "forward" those data.
I noticed that MySQL behaves like I'd have, and I don't need special feature from psql, so I think I'll switch to MySQL.
But I really wonder WHY postgreSQL doesn't behave "normally"?
It is quite annoying when you are working with system like SQLx in Rust bridging SQL and Rust types since you cannot rely on this tool when you refers to views in your Rust code.
postgres=# CREATE TABLE a( field INT NOT NULL);
CREATE TABLE
postgres=# CREATE VIEW b AS SELECT field FROM a;
CREATE VIEW

postgres=# SELECT column_name,is_nullable, data_type
FROM information_schema.columns
WHERE table_name ='a';
 column_name | is_nullable | data_type
-------------+-------------+-----------
 field       | NO          | integer
(1 row)

postgres=# SELECT column_name,is_nullable, data_type
FROM information_schema.columns
WHERE table_name ='b';
 column_name | is_nullable | data_type
-------------+-------------+-----------
 field       | YES         | integer
(1 row)

It sounds like this is just the way it is in PostgreSQL (which I understand is a sucky answer). It seems like under certain conditions for certain versions of PostgreSQL, a view's column may be attributed as non-nullable. This StackOverflow answer gives the example where if the field is a primary key of the base table, in a vanilla view, it is showing as non-nullable in the view's schema.
I suppose PostgreSQL has some strict requirements on what it assumes can truly be non-nullable in a view definition.


What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction? I'm trying to understand the performance differences between something like

INSERT INTO Person (id, name) VALUES
(1, "Kevin"),
(2, "John"),
(3, "Jane"),
...

and

BEGIN TRANSACTION;

INSERT INTO Person (id, name) VALUES (1, "Kevin");
INSERT INTO Person (id, name) VALUES (2, "John");
INSERT INTO Person (id, name) VALUES (3, "Jane");
...

END TRANSACTION;

I'm aware that during a transaction indexes are temporarily built, but I'm not quite sure otherwise. I also don't know the other performance differences between the two.

Using a transaction eliminates the most expensive operation, the transaction log flush, but other per-statement operations can still make the multi-row insert more efficient than single-row inserts.

In SQL Server a multi-row insert may modify indexes more efficiently, and insert rows more efficiently, as the locking and latching necessary for the operation taken on a statement-by-statement, rather than a row-by-row basis.  And there is some overhead for running each statement, as TSQL is (by default) an interpreted language.

Also referential integrity constraints are checked and triggers fired after each statement, not at the end of a transaction.  So there could even be a scenario where inserting multiple rows in a single statement behaves differently than using multiple statements.  EG

use tempdb
go

drop table if exists t
create table t(id int primary key, tid int not null)
insert into t(id,tid) values (1,2),(2,1)


What happens when SQL Server is in Single User Mode? If I put a database in single user mode, would I have to worry about locks?  Or will SQL Server basically perform (with exception to my open transactions) like the isolation level is read uncommitted?

We have a heavy ETL process that was written and sped up by utilizing query hints such as TABLOCK and NOLOCK depending on the data.  I was wondering if putting the database into single user mode during these processes would be beneficial.

So in single_user mode - you would be quite unlikely to have locking problems in that database. It is what it sounds like - single user - and it doesn't mean Single Username - it means one user. So it's used when you as a DBA want to do something that can't be done with others users in. Maybe you are trying to do a repair option of a checkdb. Maybe you are trying to change some object metadata and don't have a better way to kick other users out. Etc.

But it really isn't an isolation level "thing" it's really an "access thing" - if you want to eliminate or avoid locking - there are a lot of things to look at such as better performing queries, snapshot isolation levels, improvements to code, etc.


Do Constraints move from one database to another? We have a development database, a test database, and production.  The only way for data to enter one of these three systems is via an SSIS package, which means that for the last month of development, production has been stable, with no additions made.  We keep, as far as data is concerned, the three systems in sync.  However; our data modeler made suggestions that I rolled into development on the existing structure of our tables.  I rolled this into test, with the new data we will be using going forward.  We'd like to roll these tables from Test directly into production.  What I'm wondering is if the constraints that I've set up in Test will go with the table or will I have to set them up in Production?

Yes, in SQL Server constraints are defined within each database within the table definition. So if you physically promote a database to a higher environment through backup and restore, the constraints will remain within the database. If you script out objects, the default behaviour will be for the constraints to be scripted out with the table definition.

Microsoft's TechNet has a decent explanation:
https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx


How to read the MySQL Audit.log file in a SQL table? I tried to read the Audit.log file of the MySQL Enterprise using a table or other more friendly format and easy to access and understand, but the xml format with which it is written has an irregular structure:

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-17T12:53:23 UTC</TIMESTAMP>
   <RECORD_ID>1_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Audit</NAME>
   <SERVER_ID>1</SERVER_ID>
   <VERSION>1</VERSION>
   <STARTUP_OPTIONS>./mysqld --basedir=/MYSQL_HOME/mysql</STARTUP_OPTIONS>
   <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>
   <MYSQL_VERSION>5.6.38-enterprise-commercial-advanced-log</MYSQL_VERSION>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>2_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Connect</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>nbryan</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>connect</COMMAND_CLASS>
   <PRIV_USER>nbryan</PRIV_USER>
   <PROXY_USER/>
   <DB/>
</AUDIT_RECORD>

<AUDIT_RECORD>
   <TIMESTAMP>2019-02-19T02:22:23 UTC</TIMESTAMP>
   <RECORD_ID>3_2019-02-17T12:53:23</RECORD_ID>
   <NAME>Query</NAME>
   <CONNECTION_ID>460432</CONNECTION_ID>
   <STATUS>0</STATUS>
   <STATUS_CODE>0</STATUS_CODE>
   <USER>flopez[nbryan] @  [172.17.3.150]</USER>
   <OS_LOGIN/>
   <HOST/>
   <IP>172.17.3.150</IP>
   <COMMAND_CLASS>set_option</COMMAND_CLASS>
   <SQLTEXT>/*!40101 set @@session.wait_timeout=28800 */</SQLTEXT>
</AUDIT_RECORD>

As you can see, the same tags are not always generated, so I have not been able to format it to a table from the SQL Server (there is a lot of information on how to read xml formats using sql). I wanted to know if there is another way, maybe from the same MySQL, although it is very difficult to find something of this manager in relation to other solutions.

Any help or idea would be very helpful.

Thank you.

In this page you find information about Audit.log:

https://mysqlserverteam.com/audit-logs-json-format-logging/

Audit Log File Reading
Audit Log can store log files as the XML or JSON format, depending on the specified output format. When the JSON logging is enabled, we can read events using the provided audit_log_read() UDF.

  {
  "timestamp": "2018-05-24 23:15:07",
  "id": 0,
  "class": "connection",
  "event": "connect",
  "connection_id": 12,
  "account": { "user": "user",
               "host": "localhost" },
  "login": { "user": "user",
             "os": "",
             "ip": "::1",
             "proxy": "" },
  "connection_data": { "connection_type": "tcp/ip",
                       "status": 0,
                       "db": "bank_db" } }


How do I enable full text search on MySql 8? I'm using MySql 8.  I would like to speed up searches on my table of the form
select * FROM dirctory_coop where name like '%mystr%';

and someone mentioned using Full text searches -- https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html.  However, I'm having an issue with this.  Below is my table ...
mysql> desc directory_coop;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(250) | NO   |     | NULL    |                |
| enabled  | tinyint(1)   | NO   |     | NULL    |                |
| phone_id | int          | YES  | MUL | NULL    |                |
| email_id | int          | YES  | MUL | NULL    |                |
| web_site | longtext     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

I tried the below statements to update my column definition but am getting the following errors ...
mysql> alter table directory_coop modify column name FULLTEXT;
ERROR 1064 (42000): 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 'FULLTEXT' at line 1
mysql> alter table directory_coop modify name FULLTEXT;
ERROR 1064 (42000): 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 'FULLTEXT' at line 1

What else do I need to do to enable the performance optimizations that full text search supposedly provides?

You have to add or create an INDEx like
 ALTER TABLE directory_coop ADD FULLTEXT idx_name(name);

For more information read the manual ALTER TABLE


MariaDB - Slow GROUP BY on 55 Million row table I have a table with ~55 million rows, which holds user music listening history. I want to generate a report for the last quarter detailing how many times songs have been played per user. The query boils down to:

select userId, trackId, count(*)
from events
-- about 9.379.254 rows in this time range
where occurredAt > '2020-01-01 00:00:00' and occurredAt < '2020-03-01 00:00:00'
group by userId, trackId;

After having above query run for more than two hours I decided to abort the execution. I tried various indexing options, but haven't made any significant progress in terms of performance.

My best attempt made use of the table cluster index. Which had a runtime of 45 minutes:

select userId, trackId, count(*)
from events
where id between
    (select id from events where occurredAt > '2020-01-01 00:00:00' limit 1)
and
    (select id from events where occurredAt > '2020-02-31 00:00:00' limit 1)
group by userId, trackId

Some indexes I've tried:

(occurredAt, userId, trackId)
(userId, trackId)

Using a monthly summary table

I'm planning to introduce a summary table to further improve the performance. Based on the data I believe this will result in a 6x reduction in row size. This of course is a nice optimisation, but I expect more meaningful optimisations can be made elsewhere first.

I'm hoping to find some good suggestions from this community on what can be done to improve the query performance.

The schema:

CREATE TABLE `events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `deviceId` varchar(64) DEFAULT NULL,
  `trackId` varchar(32) NOT NULL,
  `playlistId` varchar(32) DEFAULT NULL,
  `bitrate` varchar(32) DEFAULT NULL,
  `duration` int(11) unsigned NOT NULL DEFAULT 0,
  `playTime` int(11) unsigned NOT NULL DEFAULT 0,
  `wasSkipped` tinyint(1) NOT NULL DEFAULT 0,
  `occurredAt` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `events_userid_index` (`userId`),
  KEY `events_trackid_index` (`trackId`),
  KEY `events_playlistid_index` (`playlistId`),
  KEY `events_wasskipped_index` (`wasSkipped`),
  KEY `events_occurredat_index` (`occurredAt`),
  KEY `user_track` (`userId`,`trackId`)
) ENGINE=InnoDB AUTO_INCREMENT=55364153 DEFAULT CHARSET=latin1;

Some bugs:  Each time range leaves out one midnight.  Your first example is 2 months minus one second; nowhere near one "quarter"  The second is one quarter minutes one day minus one second.

As for the attempts you have made in indexing, this one is probably optimal:  (occurredAt, userId, trackId).  Rationale:

The first column completely handles the filtering for the WHERE.
The rest of the columns make the index "covering", so only the index will be used.

Note:  There could be caching issues that confuse the results of timing tests.

The reason the query with an id range may have seemed faster could be caching.  I would not trust it to be faster.

This may work better:  INDEX(userId, tracked, occurredAt)  I say "may" because it depends on a messy optimization that your version may not include.  Note that it could use the first two columns to handle the GROUP BY, thereby avoiding a sort.  And it is "covering".  But it depends on being able to hop through the index.  Suggest getting EXPLAIN FORMAT=JSON SELECT ..., which might provide enough clues.  Or Optimizer Trace.

A big Yes to a summary table.  Depending on the distribution of the data, I might add rows to it each night, then tally up the rows to get the quarterly total.  (This would let you get monthly or weekly tallies, too.  And they would be equally fast.)

A Rule of Thumb when designing a Summary Table:  Aim for one-tenth as many rows in the summary table as in the 'Fact' table.  (If the 'averge' user listens to only one track per day, daily subtotal would be useless.)

More on summary tables:  http://mysql.rjweb.org/doc.php/summarytables

Another issue...  What is the setting of innodb_buffer_pool_size; how much RAM do you have; and how big is the table?  (I would guess 5GB for "Data_length".)  If you have a small machine, we need to discuss shrinking the datatypes, normalization, and tuning.


In mongodb, drop collections, but still in db.stats() ? In db.stats(), fileSize is greater, greater than storageSize I used db.coll.drop() to drop two collections in my MongoDB database. There are only system.indexes in my database now. But the output of db.stats() is:

mongos> db.stats()
{
"raw" : {
    "rs0/sever01:27017,server02:27017,server03:27020" : {
        "db" : "mydb",
        "collections" : 2,
        "objects" : 1,
        "avgObjSize" : 48,
        "dataSize" : 48,
        "storageSize" : 49152,
        "numExtents" : 3,
        "indexes" : 0,
        "indexSize" : 0,
        "fileSize" : 4226809856,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
            "major" : 4,
            "minor" : 5
        },
        "extentFreeList" : {
            "num" : 128,
            "totalSize" : 2680152064
        },
        "ok" : 1
    }
},
"objects" : 1,
"avgObjSize" : 48,
"dataSize" : 48,
"storageSize" : 49152,
"numExtents" : 3,
"indexes" : 0,
"indexSize" : 0,
"fileSize" : 4226809856,
"extentFreeList" : {
    "num" : 128,
    "totalSize" : 2680152064
},
"ok" : 1
}

Why is the collections number still 2? Why is fileSize is so large?

And when I run show dbs, the output shows that my database is about 4 GB.

I am not sure whether my issue is the same as the issue of dropping database here Removing Database from sharded cluster in Mongo DB.

Number of collection is equal 2:  The 2 collections are the system.indexes system.users. Execute the "show collections" command under your database to confirm.
Filesize: MongoDB allocates "physical files" or files on disk per database. For example eventsdb.0, eventsdb.1 ... Collections are stored with a distributed way within these files. Dropping a database will delete all these files and return the disk space to the OS. Dropping a collection marks the pages within the files as dead but doesn't reduce the filesize. You might have heard that as fragmentation. To reclaim disk space you need to execute the repairDatabase (http://docs.mongodb.org/manual/reference/command/repairDatabase/#dbcmd.repairDatabase) which i don't recommend because uses the global lock. Instead if you running on a 3 member replica set which is recommended, just stop one SECONDARY, delete the data directory and start the SECONDARY again. The initial sync will remove fragmentation.
Show dbs: Show dbs warps the size from filesize, so the number you see is correct.