texts: How to reduce table partition timing for existing table in SQL Server

texts:
Repost from: https://stackoverflow.com/questions/48169487/how-to-reduce-table-partition-timing-for-existing-table-in-sql-server

I have a problem with table partition, I am working in a very big database, It is around 2 terabyte. So we planned to split the large tables into different partitions at last year. On that time we were divided a table into two partitons. One partition was located in primary file group and another one was located in secondary file group named as PartitionFG1. So we got lots of benefits from this activity like index maintenance and performance.

And then we have planned to create another one partition for the same table for this upcoming year records. So we have created a new secondary file group and we have altered the partition scheme to use new secondary file group named as PartitionFG2. So we have alter the partition function to split the range, here is the problem. Here the new range value split and moves the data into new partition but not into the new file group. For example table’s max PK value is 1000, so I used split range value as 1000. So it should not be taken any time to move data to new partition, because new partition will contains 0 records only.

Now I am going describe the scenario by sequence.

Table Creation

CREATE TABLE Tbl_3rdParatition(PK NUMERIC(18,0) identity(1,1),Line Varchar(100))

CREATE CLUSTERED INDEX CidxTbl_3rdParatition ON Tbl_3rdParatition (pk ASC)

Insert record into table

DECLARE @I INT DECLARE @CNT INT SET @I=1 SET @CNT = 1000

WHILE (@I< = @CNT)
BEGIN
  INSERT INTO Tbl_3rdParatition (Line) VALUES ('Primary')
  SET @I=@I+1
END

Now we have inserted 1000 records into the table. We can verify the table’s row and partition by below query.

SELECT p.partition_number, fg.name, p.rows
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Tbl_3rdParatition')

So it ensures primary file group contains all records of the table. Now I am going to split the table into 2 partitions.

CREATE PARTITION FUNCTION PF_Tbl_3rdParatition(NUMERIC(18,0)) AS RANGE LEFT
FOR VALUES(500);

CREATE PARTITION SCHEME PS_Tbl_3rdParatition AS PARTITION PF_Tbl_3rdParatition TO ([PRIMARY],[SWPPartitionFG1])

CREATE UNIQUE CLUSTERED INDEX CidxTbl_3rdParatition ON dbo.Tbl_3rdParatition(PK) WITH(DROP_EXISTING = ON)ON PS_Tbl_3rdParatition(PK) ;

Now it is divided into 2 partitions. It took some time to move 500 records to SWPPartitionFG1.

Now I am going to create a new partition on another file group.

ALTER PARTITION SCHEME PS_Tbl_3rdParatition
NEXT USED [SWPPartitionFG2]

ALTER PARTITION FUNCTION PF_Tbl_3rdParatition()
SPLIT RANGE (1000)

ALTER INDEX [CidxTbl_3rdParatition] ON [dbo].[Tbl_3rdParatition ] REBUILD  WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1)

As of above query, 3rd partition should have 0 records. That is correct. But 3rd partition should be stored in SWPPartitionFG2 correct? But second partition’s data fully moved into SWPPartitionFG2. And the 3rd partition is allocated in SWPPartitionFG1 it is wrong!. So it takes too much of time to transfer the data from FG1 to FG2.

I desired to store the 3rd partition data in third file group (SWPPartitionFG2). Then only it will not take large time in partitioning process. For this reason we want lots of time to create a new partition. Our client will not give that much of down time for us.

Our Actual table size is 300 GB. 2nd partitions SWPPartitionFG1 File group contains 200 GB of data. So it requires 2:30 hrs time to move the data From FG1 to FG2. Please help me reduce the time in this activity.


texts: Service Broker Connection handshake failed. There is no compatible encryption algorithm

texts:
My target server is not receiving the message and the following error shows in the target servers error logs. Service Broker login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'

I am using service broker transport security - certificate based authentication.
My master db keys and certs are set, permissions are granted to broker_endpoint.
My user db keys and certs are set, permissions are granted to services.

My Endpoints on each server are configured for certificates, have permission to the master db cert, and all my databases are authenticated by the same user.

I am also receiving an error in Sql Trace, on target server: Certificate Not Found.

The problem here is that the service that it is documenting on the same row, in sql trace, is for a different service broker configured on the source server that I didn't reference in my conversations.

When I view sys.certificates, on both servers,  all certs are tied to the proper issuer_name.

Any help is appreciated.

Thank you


texts: Pivot table with many columns, a bad practice?

texts:
I am constructing a pivot table with many columns (300) and I wonder if I this is a bad practice.

Here are some details why I need to contruct this table.

A (very) simplified presentation of my database :

I have a company with diverse glass products, windows for example. Each product is referenced by and ID and has (many) attributes.

Products

ID | Attributes | Value
1  | width      | 10
1  | height     | 15
1  | refraction | 1
1  | thickness  | 0.5
1  | rating     | 10
...

Now, I would like to add some (computed) attibutes

For example a new attribute surface should be defined by surface=width*height. To do this, I create a pivot table from the Products, from which I create the computed attributes such as width*height AS surface.

With this method, I can make a query depending on the surface.

Is this a bad practice to do so, since the number of columns can be very high (300) in my case ? If there is another way to do this kind of operation, you can let me know.

By the way, this exceeds the maximal number of columns of Access...


texts: MySQL importing csv file by Data import wizard

texts:
I tried importing a CSV file in MySQL via the data import wizard.
My CSV file has 70,000 records but only 61 are imported.
I tried checking the CSV file, recreating the file from source, and changing the location of the CSV file, but nothing works.
I even reinstalled MySQL version 8.0.25.
Please suggest a solution or any other way of importing.


texts: TransactionLog not getting truncated in AlwaysOn Cluster

texts:
Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing.
Only one database in the availability group, is refusing to truncate it's log file and rest of the databases in the AG are truncating upon tlog backup.
One of the readonly (applicationintent) secondary replica was rebooted yesterday and this was the only new activity that happened on the AG cluster.
Three node AG cluster. SQL Server 2016 SP1
I tried these steps

15 min Tlog backups working properly. Tlog backups are done on the secondary replica.
select name , log_reuse_wait_desc from sys.databases  --> NOTHING
dbcc opentran  --> No active open transactions.
select database_id, log_send_queue_size, log_send_rate,redo_queue_size,redo_rate from sys.dm_hadr_database_replica_states  --> redo_queue_size is 0 and log_send_queue_size is 0
Verified that data movement from primary to both secondary databases is working fine and realtime
There is no blocking on on any of the nodes in AG cluster.
There are no active long running transactions on any of the nodes in AG cluster.

I'd really appreciate any input. Thanks!


texts:
Aurora MySQL 5.6.10a

I can see these events in the console:

February 20, 2018 at 4:27:24 PM UTC+11  <server>-production DB instance restarted
February 20, 2018 at 4:26:52 PM UTC+11  <server>-production Error restarting mysql: Engine bootstrap failed with no mysqld process running...
February 20, 2018 at 4:25:31 PM UTC+11  <server>-production Recovery of the DB instance is complete.
February 20, 2018 at 4:24:27 PM UTC+11  <server>-production Error restarting mysql: Could not verify and start mysqld.
February 20, 2018 at 4:19:36 PM UTC+11  <server>-production Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.

The only errors I found in the logs are the following, but I have no idea what they are telling me:

2018-02-20 05:25:11 9281 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
2018-02-20 05:25:28 9816 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 9816 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:25:39 10408 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 10408 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:25:51 10896 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 10896 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:26:07 11455 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 11455 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:26:24 12005 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 12005 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:26:35 12466 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 12466 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:26:53 13170 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 13170 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
2018-02-20 05:27:05 13644 [Warning] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
/etc/rds/oscar-start-cmd: line 39: 13644 Killed /rdsdbbin/oscar/bin/mysqld --core-file --innodb_shared_buffer_pool_uses_huge_pages='1' "$@"
----------------------- END OF LOG ----------------------
Displaying ~ 1000 lines of error/mysql-error-running.log.2018-02-20.05
Refresh LogClose

It came back up again automatically after ~15mins, but it would be nice to know what actually went wrong. CPU and memory graphs look fine.


texts: how to resolve ORA-04042: procedure, function, package, or package body does not exist and ORA-00942: table or view does not exist

texts:
I am trying to grant EXECUTE and READ privilege on two tables to a user ktest1.
grant EXECUTE on SYS.KIR_DOKUMENT to ktest2;
grant READ on SYS.KIR_DOKUMENT to ktest2;

when i give grant execute i get below error
grant EXECUTE on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-04042: procedure, function, package, or package body does not exist
04042. 00000 -  "procedure, function, package, or package body does not exist"

when i give grant read i get below error
grant READ on SYS.KIR_DOKUMENT to ktest2
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:

But i checked the status of the table in dba_objects and they are valid.
the type here is directory.

Kindly advice.


texts: How do I export data from Oracle 11.1 to 10.1?

texts:
I have a 11.1 oracle server and I need to export a dump file for a client who has a 10.1 server.

While reading the documentation, I figured out I needed the 10.1 Export Utility. I searched the Oracle site and I couln't find it.

How can I get this utility?
Thanks


texts: log send queue size and redo queue size in AG

texts:
I am trying to figure out the way to monitor these 2 events

log send queue size - I can see this in perfmon

Redo queue size - I can see in dmv but not in perfmon counter

Is there any way of using perfmon so that I can calculate the redo queue size even thought the counter is not available in perfmon?
Also, I see these events are logged in the Windows event viewer when they are part of database mirroring. But now using AG, how is it possible to log these 2 values in Windows event viewer for a certain range exceeding?
Edit
By alert I mean do we have something in AG as shown here as a message seen from db mirroring?


texts:
I installed mongodb-10gen version 2.4.6 in Debian. This package does not include mongosniff.

How can I install a pre-built version of mongosniff in Debian ?


texts: RDS MySQL instance is unavailable, probably during binlog rotation

texts:
One of my customers uses a few EC2 linuxes with PHP application, connected to a standalone MySQL RDS.
He reported application outage. During my investigation  I found that this outage happened immediately after  some RDS operation with mysql binlogs, probably log rotation.
Is there some known issue?

Update: Many thanks to Rolando and Maxime. I have a few years of MySQL experience; but I'm pretty new @ AWS. So my question in fact: "is there some RDS-specific issue with MySQL binlog processing which causes to DB outage?"


texts:
If I have a query that returns a query_plan, for instance like this:

    SELECT TOP 1000 st.TEXT
    ,cp.size_in_bytes
    ,cp.plan_handle
    ,QP.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
WHERE cp.objtype = N'Adhoc'
    AND cp.usecounts = 1

Then I can click on a query_plan and hover over the left most icon, where tip-text will list the Estimated Subtree Cost.

Is there a way of getting that Estimated Subtree Cost out as a separate column for my query?

I understand that the number is unit-less, and refers to a particular developers PC some 20 years ago. Even so, I think it might tell me how long the query should take if statistics are not too far out.

I've tried really hard to Google for this info, but even dba.stackexchange.com comes up empty.


texts: Script DB Level Permission Before Refresh to Non Prod Environment

texts:
I am looking for a script that I can use to script out all database level permissions.

Script users \ permissions
Restore DB From Prod to Non Prod
Drop Prod Users and Run script to apply original permissions

There are a lot of scripts out there. But i am unsure which one to use. Would be great if someone can point me in the right direction.


texts: SQL Server: what additional permissions for EXECUTE for implicit result codes?

texts:
Tested on: 2014+SP2+CU7, 2016+SP1+CU5, 2016+SP2

Short description: User with EXECUTE permission still gets

  Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

when successfully EXECuting the stored procedure (example below).

We are finally moving application access from db_owner to a more limited role. Everything has been fine with the user as a member of db_owner. We crated a new role db_webAppAccess and granted SELECT, UPDATE, DELETE, and EXECUTE. The problem occurs when the user executes most if not all of our stored procedures. The procedure runs, but at the very end fails with

  Msg 15151, Level 16, State 1, Procedure sp_fakeProcedureName, Line 14 [Batch Start Line 2]
  Cannot find the object 'sp_fakeProcedureName', because it does not exist or you do not have permission.

We know it executes because when performed in SSMS the expected data is returned. Also, if we cope the contents of the stored procedure and put it into SSMS it executes without error.

Now to the confusing part: if we add an explicit return value, the error disappears. Ex add return(0) as the line of the SP below. Sadly, we have a few hundred stored procedures that would need to be examined and set to have explicit return values in all cases.

I am at an absolute loss on exactly what is different in executing a stored procedure when there is and is not an explicitly defined return code. Is there some additional grant I can give to allow access to the result codes?

Thank you for any help you can provide!

CREATE PROCEDURE [dbo].[sp_fakeProcedureName]
AS
BEGIN
  SET NOCOUNT ON;

  SELECT    fakeTablePKIdentity, column2, column3, column4
  FROM fakeTable
  ORDER BY column2
END

GRANT EXEC ON [sp_fakeProcedureName] TO PUBLIC
GO

Here is the procedure I used to setup the role and user.

USE [fakeDatabse]
GO

CREATE USER [usr_fakeUser] FOR LOGIN [DOMAIN\fakeUser] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE ROLE [db_fakeRole]
GRANT CONNECT TO [db_fakeRole] AS [dbo]
GRANT DELETE TO [db_fakeRole] AS [dbo]
GRANT EXECUTE TO [db_fakeRole] AS[dbo]
GRANT INSERT TO [db_fakeRole] AS [dbo]
GRANT SELECT TO [db_fakeRole] AS [dbo]
GRANT UPDATE TO [db_fakeRole] AS [dbo]

ALTER ROLE [db_fakeRole] ADD MEMBER [usr_fakeUser]
GO


texts: Importing Oracle database from dump where source db filesystem has changed

texts:
I'm migrating a database from one server to other. In the migration process  we changed the filesystem structure from :

/appli2/oradata05/HISTO/
/appli2/oradata06/HISTO/
/appli2/oraredolog01/HISTO/
/appli2/oraarch/HISTO/

to

/appli2/oracle/HISTO/oradata05
/appli2/oracle/HISTO/oradata06/HISTO/
/appli2/oracle/HISTO/oraredolog01/HISTO/
/appli2oracle/HISTO/oraarch/HISTO/

And i'm getting error according to this changes:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/******** DIRECTORY=MIG11G DUMPFILE=20131125_full_HISTO.expdp LOGFILE=impdp_dump_HISTO_r7.log parallel=4
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDO" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"TOOLS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "TAB_ANC" DATAFILE '/appli2/oradata05/HISTO/HISTO_tab_anc_01.dbf' SIZE 1048576000,'/appli2/oradata05/HISTO/HISTO_tab_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf'
ORA-27040: file create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
Failing sql is:
CREATE TABLESPACE "IDX_ANC" DATAFILE '/appli2/oradata06/HISTO/HISTO_idx_anc_01.dbf' SIZE 1048576000,'/appli2/oradata06/HISTO/HISTO_idx_anc_02.dbf' SIZE 2146435072 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM

Is it possible to import only de data in the current filesystem structure ?
I saw the remap command but i never used it, someone could put an example to use it if it was the answer to the problem.

remap_tablespace:source_tablespace_1:user,source_tablespace_2:user


texts: Copy PostgreSQL data from one PC to another

texts:
I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to the other system? I have a copy of all the table schemas.
I have no idea on how to proceed. I am using PostgreSQL.


texts: SQL Agent AD account gets permission denied for ODBC connections in SSIS packages

texts: We have an AD account specifically created for the agent service and when running SSIS packages that connect to databases we get permission denied even though we have granted that account access to the database.  My hunch is that it is something to do with how our security group created the service account in AD.  My question is has anyone experienced this and might know if there are certain settings in AD that could prevent an account from authenticating properly with SQL Server (using Windows authentication)?  If I change the agent service to use another AD account it works and at the SQL Server level they both are granted access to the database with the same permissions.


texts: Don't rebuild indexes < 1000 pages - then why bother having them?

texts:
Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM).

If I'm not rebuilding them, then they'll become heavily fragmented.  In that case is there any point in having them at all?  I'm imagining a phone book where every single entry is in the wrong place - it would be useless!

For clarification I'm mainly talking about non-clustered indexes, though I'd be interested to know whether or not the answer would be different for clustered indexes.

The answer to this question suggests that indexes can help to avoid blocking issues behind row locks, which would be a reason why the indexes may still be useful if it is correct and applies to SQL Server.


texts: Select rows based on values from 1 column being the same?

texts:
An example of my database table

film id | scene id | value id
-----------------------------
   0    |     0    |     7
   0    |     1    |     1
   0    |     1    |     0
   1    |     2    |     8
   1    |     2    |     3
   1    |     3    |     1
   2    |     4    |     3
   2    |     4    |     7
   2    |     5    |     2

I want to select rows where the value id is equal to 8 or 3 only if they are both in a row with the same scene id.

My desired result would be:

film id | scene id | value id
-----------------------------
   1    |     2    |     8
   1    |     2    |     3

I tried grouping by count of scene id but it would output this:

film id | scene id | value id
-----------------------------
   1    |     2    |     8
   1    |     2    |     3
   2    |     4    |     3

where the last row was included because there were multiple values of scene id.


texts: Ambiguity in Auto Create\Update Statistics

texts:
If I set 'Auto Update Statistics' to 'False' and 'Auto Update Statistics Asynchronously' to 'True'

What the DB will do?

Also - If I set 'Auto Create Statistics' to 'True', It will be 'Async'?


texts: Meaning of "The database X needs to be upgraded" in ClearTrace

texts: I'm trying out ClearTrace from http://www.scalesql.com/cleartrace/download.html , but it isn't very user friendly from my first impression.  I have a database in SQL Server 2008 that I'm adding in the ClearTrace - Options dialogue, but it says "The database mydatabase needs to be upgraded.  Would you like to upgrade it?".  If I click No, I can't continue.  What does it mean to upgrade the database in this context?  The support forums don't seem to answer this.


texts: How do I eliminate a second seq scan over a table when deriving a new table?


texts: Ola Hallengren Scripts and Maintenance Plan Editor - How to execute sequentially on completion?

texts:
I'm using Ola Hallengren's scripts to perform maintenance on my databases. In the Maintenance Plan Designer I am trying to setup a plan that works step by step, but no matter what I try everything gets executed at one time.

My first and second attempts, left column in the photo, was to use "execute job" and call each job in the order I want.  The first constraint was "success" but all jobs execute at once.  So I tried "completion" next.  Same result.  I then constructed the same tree using execute t-sql instead of execute job using this code:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO

One again I got the same result, everything executes at the same time.  I even tried adding a @return_value for executing sp_start_job but that did nothing either.  Finally I tried one script like this:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - SYSTEM_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='IndexOptimize - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - SYSTEM_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - LOG';
GO

And it also executes everything at once.  Besides scheduling them one by one in the maintenance plan editor at different times, is there any way I can make them execute one after another.  I'm not concerned if they succeed or not, I just want one to run then the next instead of everything at once.


texts: pt-online-schema-change's triggers fail with "DELETE command denied" on insert

texts:
We recently tried pt-online-schema-change to add a column to a table. It worked mostly as expected but one thing puzzles me: in case the account we use to do the migration doesn't have the DELETE permission, our application (which keeps running simultaneously) gets errors that say "DELETE command denied to user 'pt'@'localhost' for table '_xxx_new'" when the app is performing an insert on the xxx table.

My understanding is that the triggers are to do inserts to the destitation _xxx_new table when a new record is inserted in the source xxx table. How come it may fail with not having a DELETE permission??

Percona docs are pretty generic on this and no googling helped, so will be thankful for any ideas!


texts: How to group by week even when the count is 0

texts:
My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:

My current output:

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344

The result above is missing many weeks (1,2,3 and 15,16,17 etc.) How do i get to show those with 0 count?

TEST EXAMPLE:

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');

;with ResultsTable as
(

SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A

WHERE YEAR(A.DATE) = '2019' AND

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE < '2019-01-01')
GROUP BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber < (
        SELECT datename(week, '2019-12-31')
        )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber


texts: Will TDE encryption protect my SQL database from hacker in this scenario

texts: I'm a bit understand that TDE encryption protect the database from being stolen and read by a hacker. However, I don't understand if it can help me in this scenario: If a hacker got into my server and he got the Administrator privilege. Now he want to access the database to stolen information. He won't need to copy the entire database file which being encrypted. But he can reset the SQL SA password or read the connection string from a website .config file , and use tool like SQL Server Management Studio to access the database from that user and password, finally he use few SQL query to view the data or export it to disk. Is that possible?


texts: Will stopping my query rollback all my queries

texts:
I have started a Query like the following in SQL Mgt studio:

Currently running sp_who I can see the Query is running for TBCompany_503
My question is that if I Kill the process for TBCompany_503 will it rollback the changes for TBCompany_500 - TBCompany_502 ?
Reason I want to stop it, and run them separately.


texts: How to install olaptrain sample schema on oracle 12c?

texts:
I need to install the sample schema available from the Oracle website:

http://download.oracle.com/otndocs/products/warehouse/olap/olaptrain_install.zip

I am trying to run the commands as given here:

http://www.oracle.com/technetwork/database/enterprise-edition/readme-098894.html

But I get this error:

ORA-01537: cannot add file 'dm.dbf' - file already part of database

Given below is the copy of the screen:

SQL> ALTER SESSION SET CONTAINER = pdborcl;

Session altered.

SQL> @install_olaptrain.sql

Specify file system directory containing this installation program.

Directory: C:\Users\Ashish\Desktop\olaptrain_install

Specify a password for the OLAPTRAIN user.
Password:

Begin installation
... creating tablespaces
  declare
*
ERROR at line 1:
ORA-01537: cannot add file 'dm.dbf' - file already part of database
ORA-06512: at line 31

How can I resolve this error?


texts: Why should I save both markdown and the converted HTML in my database?

texts:
First of all, forgive me if this is the wrong place to ask this or if this has been answered somewhere and I just can't seem to find it.

So you have a scenario where you have an article/news web app and your authors use markdown to generate content.

With regards to deciding database schema, everywhere you search, the recommended method is to save both the raw markdown text and the converted HTML in your database, but my question is WHY?

Internet: You never want to parse markdown every time you have to display the content.

Me: Yes, so parse the markdown and store the HTML into the database directly, so you don't have that performance hit when serving up the articles/content since you want speed here. Good, that's fine.

Internet: It makes it easier to edit the raw markdown later, so saving the markdown to the database is recommended.

Me: It sounds good in theory, but what happens when you have a very large database with tens of thousands of rows? You basically inflate the size of your database just to prevent parsing some HTML back to markdown when you edit the article/content? How often do you edit these articles? Not that much since most of the hits are just reads to display the content to your end users.

So my question is, where is the benefit of storing both the markdown AND the converted HTML in the database? The "later-edit" argument seems moot to me when just to save a minor conversion before editing, you're basically inflating your database size by needlessly storing the raw markdown in addition to the HTML. Your articles table can theoretically double in physical size just because of this. What's the point then?

It just sounds like pre-optimization that leads to a larger detriment later on when your infrastructure is forced to scale because of the duplicity of the markdown/HTML content. A conversion from HTML to markdown seems much more realistic to me, not like the authors/editors are going to notice the extra few milliseconds that the converter takes when they try to edit something.


texts: CSS-like dynamic properties cascading configuration table?

texts:
Have an idea in mind, not interested in inventing an existing wheel, but can't find info on the subject so I'm turning to the community.
My goal is to define a "dynamic properties cascading configuration table", which means the following:
I would like to maintain a metadata table with dynamic properties for a certain database table entities.
There are many properties and they may or may not exist for the entity. That is why they should be kept on a separate table.
Moreover, I would like to have configuration levels for that metadata table, e.g.: SYSTEM, TOP_LEVEL, MID_LEVEL, etc.
Let's say we have an online store system where we have brands, each having customers, each having shops.
We'd like to define a system-wide default property of a logo image URL.
This property may be different on the brand leve, customer level or the individual shop level.
The design for this table would be something like:
  id          // unique row id
  configType  // SYSTEM, BRAND, CUSTOMER, SHOP
  entityId    // ID of a user, a post, etc.
  key
  value
  valueType   // string, number, etc. - will be used for validations and parsing

Possible rows would look something like:
+----+------------+----------+----------+-----------------+-----------+
| id | configType | entityId |   key    |      value      | valueType |
+----+------------+----------+----------+-----------------+-----------+
|  1 | SYSTEM     | SYSTEM   | logo-img | sys-logo.jpg    | string    |
|  2 | BRAND      | brnd2    | logo-img | brnd2-logo.jpg  | string    |
|  3 | CUSTOMER   | cust81   | logo-img | cust81-logo.jpg | string    |
|  4 | SHOP       | shp76    | logo-img | shp76-logo.jpg  | string    |
+----+------------+----------+----------+-----------------+-----------+

So when searching for the logo image of shop shp76, which its parent is cust81, which its parent is brnd2, we would like to get the most nested level of configuration possible. shp76-logo.jpg in the above case.
If SHOP and BRAND config types wouldn't exist for this configuration, we would still prefer the CUSTOMER level value (cust81-logo.jpg) over the SYSTEM default.
+----+------------+----------+----------+-----------------+-----------+
| id | configType | entityId |   key    |      value      | valueType |
+----+------------+----------+----------+-----------------+-----------+
|  1 | SYSTEM     | SYSTEM   | logo-img | sys-logo.jpg    | string    |
|  2 | CUSTOMER   | cust81   | logo-img | cust81-logo.jpg | string    |
+----+------------+----------+----------+-----------------+-----------+

A postgres query would look something like:
  SELECT "value"
  FROM "ConfigTable"
  WHERE "key" = 'logo-img'
    AND "configType" IN ('SYSTEM','BRAND','CUSTOMER','SHOP')
    AND "entityId" IN ('SYSTEM','brnd2','cust81','shp76')
  ORDER BY (CASE status
            WHEN 'SYSTEM' THEN 1
            WHEN 'BRAND' THEN 2
            WHEN 'CUSTOMER' THEN 3
            WHEN 'SHOP' THEN 4
            END) DESC
  LIMIT 1

The config types we query are dynamic on a per-case basis. So if we'd like to query the value for the CUSTOMER level, we'll be able to use a different set of config types (e.g. without the SHOP type in this case)
This will allow us creating and maintaining different levels of configuration nesting for totally different configuration types as well (e.g. SYSTEM, WEEKLY, DAILY, HOURLY)

Issues with this solution:

The valueType and key do not change between rows, and are both duplicated.
The thought behind this is that this is already a pretty complex subject so introducing another id;key;type table would be another level of complexity.
How to maintain the data? (manually? write a configuration tool?)
How to query for multiple keys at the same time?
Is it possible to achieve with a pure SQL query or would code be required?
Performance
Should results be cached somewhere else (e.g. Redis)?
Is this a known subject which already has academy-level solutions?


texts: How to set up last access (timestamptz) in table 1( basic info table) when working with many other tables having foreign key from table 1?

texts:
Let's say the database have 5 tables for sake of simplicity (there're many more in reality). Table 1 has the basic information of a user which is not changed so often. However, the other tables are accessed very often. All these other tables have the user-id as the foreign key. I need to make a column lastAccess in table 1 such that whenever the user works with any of the table, user's lastAccess in table 1 is updated.

I'm working with [python-socketio] (https://github.com/miguelgrinberg/python-socketio) server connected to PostgreSQL database using python-psycopg2 connector.

It seems like a task which every person working with databases should know. I am new to working with RDBMS and exactly find a way to do it.
A general direction, if a solution is not possible, will also be really appreciated.


texts: Granting a SQL Server database only user execute on xp_instance_regread fails

texts:
So I'm not finding an answer to my specific issue.

SQL Server 2012 database
SSMS 17 admin tool

Log on as a database (not domain) user foo.

Receive sys.xp_instance_regread no permission errors.

In past versions of SSMS you get the errors, but you can do your work on your database.

I've seen this before, the user needs execute permission on the stored procedure. Shoot I even have a script I use all the time for domain users

Grant Execute ON [sys].[xp_instance_regread] TO [domain\user];

So I modify it:

Grant Execute ON [sys].[xp_instance_regread] TO [foo];

but get this error:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the user 'foo', because it does not exist or you do not have permission.

Now the user exists and my account has sysadmin access.

Any ideas on how to fix this?


texts: select records by a particular month wise in sql

texts:
I want to get the records for a month of January'2017 and here is the one row sample of my huge table

Table:

CUSTTRIPID  TPTRIPID    CUSTOMERID  ENTRY_TOLLTXNID EXIT_TOLLTXNID  ENTRYTRIPDATETIME   EXITTRIPDATETIME    TRIPIDENTMETHOD TRIPCHARGETYPE  ENTRYLANEID ENTRYPLAZAID    EXITLANEID  EXITPLAZAID VEHICLENUMBER   VEHICLESTATE    VEHICLECLASS    VEHICLEID   TAGREFID    TOLLAMOUNT  FEEAMOUNTS  DISCOUNTSAMOUNT OUTSTANDINGAMOUNT   TRIPSTAGEID TRIPSTATUSID    TRIPSTATUSDATE  POSTEDDATE  PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID   PLAZA
39038181    34040337    20272679    35126165    35126165    2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P   NULL    NULL    NULL    NULL    KA03D8435   KA  VC10    272614  34161FA8203289720249F4C0    30.00   0.00    0.00    30.00   3   1   2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST    2017-05-04 08:32:11.130 TRIPPOST    0   BangaloreNelamangalaTollPlaza

I have applied this query to get the Date part because the requirement was to retrieve CUSTOMERID more than 2 records with time (of a month).

Query:

SELECT   TOP (10) [CUSTOMERID],
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS]
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING   COUNT(*) > 2;

Result:

CUSTOMERID  DATEPART    TIMEPART
20010438    12/16/2016  22:16:55
20015278    1/28/2017   5:18:20
20108092    2/10/2017   0:37:09
20018833    1/27/2017   4:53:32
20199820    1/27/2017   5:34:24
20124122    2/1/2017    2:47:01
20240710    3/21/2017   13:02:18
20143691    3/21/2017   8:08:03
20074212    12/15/2016  7:28:56
20114518    12/18/2016  6:11:31
20009754    12/17/2016  20:38:40
20072106    3/17/2017   19:37:20
20092287    1/28/2017   10:54:06
20055028    1/27/2017   4:36:51
20003676    4/23/2017   1:34:11

Now how to get the result for a month, assume for January 2017 ?


texts: How do I create a oracle database patch list overview?

texts:
I need to create an list for Oracle database patch versions for a recurring audit on patch states. It will check whether the current patch state of Oracle database meets the required patch state of a CERT policy. The list will be used by a licenses management and monitoring system that tracks the Oracle database versions by querying a view.
Example:

SQL> select * from patch_history;
DIFF :    77 DAYS ACTION=APPLY VERSION=12.1.0.1 DATE=20151030 ID= 11 COMMENTS=WinBundle 12.1.0.1.11 PORT=IBMPC/WIN_NT64-9.1.0

The string: “WinBundle 12.1.0.1.11” has to match with the patch description in the list/overview.
The current solution is to manually search for the required patch states on Oracle support website. Is there a way to manage such a list in better way? The list has to contain the Oracle database version, the OS: Windows Server, Linux, AIX and the required patch version and the dates. View link as example. The format of such a list is not relevant.

Oracle patch overview list example


texts:
I am trying to enable sql server and windows authentication mode by logging into SSMS but when I change to sql server and windows authentication mode and hit OK, I get this pop up enter image description here
I was following this quide here Can't change authentication mode from Windows to both SQL Server and Windows

I tried to grant permissions to my user id but I get a syntax error on the backslash \ between the domain and my id
USE master GO
GRANT EXECUTE ON xp_instance_regwrite TO domain\user


texts: Transactional Replication hangs when huge transactions flow

texts:
We are running push transactional replication in our production environment ( SQL server 2014 (x64) Default configuration). It hangs when thousands of live transactions flows in and this continues for hours and eventually days. So as to come out we generally stop the replication. I am not sure what causes it and also don't know how to troubleshoot this issue.
Since I am new to replication, can anybody help me out with the steps to troubleshoot the issue?


texts: MySQL Event Help - Update column value for each row every 15 minutes

texts:
Okay so if I have a login database:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    4
 2 |  ""   |  ""  |    2

Attempts marks the number of attempts made at logging in, per user. So user 1 has attempted to log in 4 times and user 2 has attempted to log in 2 times.

I was wondering if there was an event that I could add to my login database, which would reset all user attempts to 0? So after the reset, the table would look like this:

id | email | pass | attempts
----------------------------
 1 |  ""   |  ""  |    0
 2 |  ""   |  ""  |    0

Thanks !


texts: AWS RDS responds with (Got an error reading communication packets). Works locally

texts:
I've been on this issue for a couple of days now, I will cover things I've tried at the end. So I'm running a NodeJS server from a GitHub repo using netlify-lamda. I am connecting to an Amazon RDS (MySQL). So the strangest thing is this works locally however when I bring it over to Netlify I get an error.

I'm sending the HTTP requests using Postman.

My db.js file:

var mysql = require("mysql");
var db;
require("dotenv").config();

function connectDatabase(){
    if(!db){
        db = mysql.createPool({
            connectionLimit: process.env.CONN_LIMIT,
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASS,
            database: process.env.DB_NAME,
            port: process.env.DB_PORT
        });
    }
    return db;
}

module.exports = connectDatabase();

To query the DB I do the following:

router.post('/login',(req,res) => {
    var username = req.headers.username;
    var password = req.headers.password;
    var sql = `SELECT password FROM admin WHERE username= ?`;
    db.query(sql,[username],function(err,results,query){
        if(err){
            res.status(401);
            res.send("Unauthorized-sql err"); // This is where the error occurs.
        }
        else if(!err){
            if(crypt.decrypt(results[0].password).match(password)){
                res.status(200);
                res.send("Authorized");
            }else{
                res.status(401);
                res.send("Unauthorized");
            }
        }
    });
});

RDS Error Log:

2020-04-03T14:11:31.874911Z 22 [Note] Aborted connection 22 to db: 'db' user: 'admin' host: 'xxx.xxx.xx.xx' (Got an error reading communication packets)
2020-04-03T14:11:31.877847Z 29 [Note] Aborted connection 29 to db: 'db' user: 'admin' host: 'xxx.xxx.xx.xx' (Got an error reading communication packets)
----------------------- END OF LOG ----------------------

NodeJS Error Log

6:17:18 PM: 2020-04-03T17:17:18.120Z    1d561b89-f8a1-4e78-9d7c-61dac84ea37f    INFO    Error: connect ECONNREFUSED 127.0.0.1:3306    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1137:16)

at Protocol._enqueue (/var/task/admin.js:29255:48)
at Protocol.handshake (/var/task/admin.js:29162:23)
at PoolConnection.connect (/var/task/admin.js:9041:18)
at Pool.getConnection (/var/task/admin.js:27212:16)
at Pool.query (/var/task/admin.js:27366:8)
at /var/task/admin.js:27556:6
at Layer.handle [as handle_request] (/var/task/admin.js:7030:5)
at next (/var/task/admin.js:6641:13)
at Route.dispatch (/var/task/admin.js:6616:3)
at Layer.handle [as handle_request] (/var/task/admin.js:7030:5)
{
errno: 'ECONNREFUSED',
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 3306,
fatal: true
}

Things I've tried:

Changing the PORT.
Within the DB instance I'm allowing all connections from any IP address.
Setting a different PORT for the NodeJS instance.
I've now switched to using a MySQL pooling technique, the error is still there.
Preventing the minification/encoding of the code (for the DB instance in particular as that's an issue with the mysql npm package)

Sidenote: It's my first time working with AWS RDS so it's highly likely that the error is something very very stupid.


texts: How to calculate the sums of two columns, when that two columns brought by join queries?

texts:
I ran two queries as shown bellow;

query 1: queried with JobRequiredProducts table
and Product table to list the jobId 1 required all product and unit prices

SELECT  j.jobId,
        j.productId,
        p.productUnitPrice
FROM    JobRequiredProducts J
JOIN    Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
ORDER BY j.jobId

query 2: queried with JobRequiredLabours table
and Labour table to list the jobId 1 required labours and worked hours

SELECT  j.jobId,
        j.labourId,
        j.hoursOfWork
FROM    JobRequiredLabours J
JOIN    Labour L
  ON    l.labourId = j.labourId
WHERE   j.jobId = 1
ORDER BY j.jobId

And I got bellowing results:

Query result:

 jobId | productId | productUnitPrice
__________________________
   1   |     4     | 1500
   1   |     5     | 800
   1   |     6     | 1200

 jobId | labourId | hoursOfWork
______________________________
   1   |     1     | 6
   1   |     2     | 8

Now I want to calculate the total job cost by total (productUnitPrices) + (hoursOfWork * 30). And display the total cost separately. Please help me to do it. Note: 30 added to multiply because of hourly rate is 30


texts: Is it possible to execute "begin transaction" when connected to MS SQL server with Oracle's SQL Developer?

texts:
I am using Oracle's SQL Developer to execute SQL statements on an MS SQL Server instance. Apparently, SQL Developer recognizes specific Oracle related keywords such as begin. Thus, when I try to execute begin transaction, SQL Developer will give me the error Incorrect syntax near the keyword 'begin'..

So, is there something I can tweak in order to be able to execute such a statement?


texts: Does using LIMIT improve the performance and is it noticeable?

texts:
I want to understand the following.
Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by.
Letting aside any optimizations to the query itself e.g. indexes etc.
Is there any significant performance benefit using LIMIT? I assume that all the query (and results) must be processed before LIMIT is applied, so using a LIMIT to retrieve a subset of the results, does this offer any significant/noticable improvement?


texts: Insert multiple values into stored procedure

texts:
I have 2 System Stored Procedures.

1) exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID=000000

The first proc retrieves a column called "localUpdateID" with 30 000 rows.
In the second proc you have specify the retrieved row from the first proc at "@localUpdateID=000000"

Copying each one of the 30 000 rows and pasting it into the second proc will take forever.

I have tried:

1) exec spDeleteUpdate @localUpdateID = exec spGetObsoleteUpdatesToCleanup

2) exec spDeleteUpdate @localUpdateID IN ('00000', '00001')


texts:
I am trying to run this against all the DB's on my server and not just on "TEST_1".  Is there a simple way to do this?  Ideally the select would also return the DB name.

use TEST_1
go
declare @DataSetID int

set @DataSetID = 1;

declare @Linkdate datetime

set @Linkdate = '8/31/2015';

select distinct I.InterfaceName
    ,case
        when B.brokerCode is null
            then 'Not Populated'
        else B.BrokerCode
        end
    ,case
        when B.brokerDescription is null
            then 'Not Populated'
        else B.BrokerCode
        end
from transactions T
inner join interfaceoriginations I on I.OriginationID = t.OriginationID
left join brokers B on B.BrokerID = t.BrokerID
where linkdate = @linkdate
order by 1
    ,2


texts: Changing Default Values of Percona Audit Log

texts:
Have I missed Something here? I am installing the Percona Audit Log Plugin (MySQL 5.5).

I can install it fine using:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

I can then see all the default variables with:

SHOW GLOBAL VARIABLES LIKE 'audit%';

Looking at the documentation it tells me that for the most part they are not Dynamic.

So how do I set them to what I need at the time I install the plugin?
From what I can tell the only option is to add them to my my.cnf file and restart the Database.

That's fine for a Test System, but I can't go round restarting my LIVE databases, just to install a plugin, surely.


texts: SQL Server - Slow Database for no apparent reason

texts:
I have a DB Server running SQL Server 2008 R2 SP2 on 24 physical Cores and 32GB of RAM. The database files are on a SSD Raid1 and the TempDB on a SAS 15k (150GB DB)

I assume that the server has more than enough resources and the queries are running slow.

On my test environment the queries run quick on a low resources server, and in production they are getting stuck, lasting for 100.. 200 seconds for apparent no reason.

My indexes are not fragmented, my CPU activity do not pass 25%, my SSD activity are 5% max, are there any ideas to help me solve this problem?


texts: The media family on device incorrectly formed for read only file group restore

texts:
Yes the databases (source and destination) are the same version.  I have checked 3 times.  Both are "Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64)"
The database itself restores ok without error.  Only when I try the read-only file group restore does it give the error.   I have redone the back up and attempted the restore again just in case I had something wrong with the backup.
I have done this numerous times with ss 2016 and even when upgrading from ss2016 to ss2019.  But now I am testing the restores it just looks broken to me.  Almost like this is a  bug


texts: Insert values from a record variable into a table

texts:
I am developing a user-defined function that takes two arguments:

create or replace function gesio(
    events_table_in regclass,
    events_table_out regclass)
returns void as $$ ... $$

events_table_in and events_table_out have exactly the same schema.

Simply explained, I loop through the records of events_table_in, manipulate the records and want to append (insert) the manipulated records into events_table_out in the following fashion:

OPEN recCurs FOR execute
format('SELECT * FROM %s order by session_id, event_time', event_table_in);

LOOP
    FETCH recCurs into rec;
    if not found then
      exit;
    end if;

    -- 1. do something with rec

    -- 2. insert the rec into events_table_out

end loop;

How can I save the rec into events_table_out?


texts: MySQL Workbench SSH to Google Compute Fails with "Authentication error opening SSH tunnel" - but Putty works

texts:
I am trying to connect to MySQL server hosted on google cloud compute server. I believe the issue is not MySQL authentication related, but rather SSH. MySQL workbench seems to be unable to connect on SSH with "Authentication error opening SSH tunnel".

I am able to open a putty SSH connection to the server using the same IP, user and private key on the same computer.

Connection is configured as:

SSH Hostname: [ip of my google cloud server]
SSH Username: [username used in putty ssh]
SSH Key File: [same ssh-rsa ppk file I use in putty]
MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306 Username: [mysql user that works from putty]
Password: [mysql password that works from putty]

wb.log:

09:43:57 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
09:43:57 [INF][     SSH tunnel]: Opening SSH tunnel to 104.197.xxx.xxx
09:43:57 [ERR][sshtunnel.py:notify_exception_error:235]: Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py", line 303, in _connect_ssh
    look_for_keys=has_key, allow_agent=has_key, timeout=SSH_CONNECTION_TIMEOUT)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 367, in connect
    look_for_keys, gss_auth, gss_kex, gss_deleg_creds, gss_host)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 584, in _auth
    raise saved_exception
AuthenticationException: Authentication failed.
09:43:58 [ERR][     SSH tunnel]: Authentication error opening SSH tunnel: Authentication error. Please check that your username and password are correct and try again.
Details (Original exception message):
Authentication failed, please check credentials.
Please refer to logs for details
09:43:59 [INF][      WBContext]: Connection to go-xv01 cancelled by user: Tunnel connection cancelled


texts: RMAN list archivelogs that are needed for to recover specified backup

texts:
I've taken backup of datafiles in 31.12.2013. It completed datafile backups successfully, but archivelog backups failed.

Now, I want to determine what archivelogs should I backup to be sure that my database will be recoverable after restoring 31.12.2013 backup of datafiles.


texts: How to resolve ORA-01031: insufficient privileges error on Windows installation?

texts:
I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database using:

C:\>sqlplus / as sysdba

I get a following error:

  ORA-01031: insufficient privileges

This worked before the client installation. My account is on the ORA_DBA group. My account is also in the Administrator group. I don't have ORACLE_SID set in my environment variables list. Does it have to be? This worked before the client installation, and I did not have that variable at that time either.

UAC is on at the lowest level, and I always run cmd as admin.

I have an sqlnet.ora file like this in the folder:

C:\app\myaccount\product\11.2.0\dbhome_1\NETWORK\ADMIN

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydb.com)
    )
  )

listener.ora

# listener.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\myaccount\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\myaccount\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\myaccount

From the windows registry (using regedit), from the path Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ORACLE I can find two keys:

KEY_OraClient11g_home1
KEY_OraDb11g_home1

And under those keys I can find ORACLE_HOME variables. So it seem that the client installation generated also ORACLE_HOME so that I have now two ORACLE_HOMEs. I don't know if this has something to do with this problem?

How to resolve this error on my Windows machine?


texts: How do you authenticate the database connection when using Reporting Services Forms authentication?

texts:
I'm looking into using Forms authenticaiton for SSRS. I got it working using Microsoft's sample found here: http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008!Security%20Extension%20Sample

However, I'm wondering what authentication to use to the SQL Server data source. When I use Windows Authentication I get this error:

An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'ActualYears'. (rsDataSetExecutionError) Cannot impersonate user for data source ' Data source for shared dataset'. (rsErrorImpersonatingUser) This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)

When I use SQL Server Authentication it prompts me for the credentials when I run a report, even though my data source has the user and password specified in the connection string. So when I run the built-in Report Manager I have to log in for forms authentication. Then when I run a report I have to log in again for SQL authentication.

If using Forms authentication do you have to use SQL Server Authentication or can Windows Authentication somehow work? Also, is there a way to get SQL Server Authentication to not prompt you for the credentials, but to use those already supplied in the connection string?


texts: Questions around migrating to a live replicated database from an existing log shipping database in SQL Server 2016 SP1

texts:
I have two large databases, one on my production server named S1 and the other on my reporting server named S2.

S1 is the live database used for our customer orders and accounting, and S2 is the reporting database, which is a one-way, read-only copy of S1.

Every 2 hours, S2 is taken offline, then a log shipping job runs on S1 and updates the last 2 hours of transactions into S2 on the reporting server. It works, but isn't quite up to date enough any more for my users' needs, and having it go offline every 2 hours and interrupt any queries running at that point is less than ideal, so they end up running queries on S1 and causing untold problems.

I need to set up simple replication such that S2 is updated as soon as there are any changes in S1. I've never done this before so I'm quite nervous.

I can set up a new database on my reporting server, there's plenty of capacity, so I'm in the process of setting up a live read-only transactional replication named S3. When I'm happy it's working and matches S2, I just want to do a straight swap and start using S3 for all my reports etc.

Is it as simple as just detaching S2, then renaming S3 to S2?

Provided the tables and indexes etc were all identical, Would all my users' queries just work at that point?

Instead of doing this, is it a better idea to reconfigure the existing DB from a log shipping model to a transactional model, or is that painful?


texts: How do I limit Oracle to use specific ports?

texts:
I've built a new Oracle 9iR2 instance on a Windows 2003 32-bit cluster. Of course, it's version 9i for reasons out of my control.  Several application servers connect to this instance, and each opens several DEDICATED connections that they keep open for their work.

This instance is in a locked-down environment, so my network guys want to seal off as many ports as possible. To do that, I need to tell them precisely what ports or port ranges the instance needs open.

I've skimmed the Listener documentation, and I can see that I can change the default Listener port of 1521. However, every connection these application servers open against the database appears to get its own TCP port.

Is there any way to limit the range of ports these servers will use to connect to the database?


texts: Get streak count and streak type from win-loss-tie data

texts:
I made a SQL Fiddle for this question if that makes things easier for anyone.

I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data (like 'W2' if the team has won their last 2 matchups, or 'L1' if they lost their last matchup after winning the previous matchup - or 'T1' if they tied their most recent matchup).

Here is my basic schema:

CREATE TABLE FantasyTeams (
  team_id BIGINT NOT NULL
)

CREATE TABLE FantasyMatches(
    match_id BIGINT NOT NULL,
    home_fantasy_team_id BIGINT NOT NULL,
    away_fantasy_team_id BIGINT NOT NULL,
    fantasy_season_id BIGINT NOT NULL,
    fantasy_league_id BIGINT NOT NULL,
    fantasy_week_id BIGINT NOT NULL,
    winning_team_id BIGINT NULL
)

A value of NULL in the winning_team_id column indicates a tie for that match.

Here's a sample DML statement with some sample data for 6 teams and 3 weeks worth of matchups:

INSERT INTO FantasyTeams
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6

INSERT INTO FantasyMatches
SELECT 1, 2, 1, 2, 4, 44, 2
UNION
SELECT 2, 5, 4, 2, 4, 44, 5
UNION
SELECT 3, 6, 3, 2, 4, 44, 3
UNION
SELECT 4, 2, 4, 2, 4, 45, 2
UNION
SELECT 5, 3, 1, 2, 4, 45, 3
UNION
SELECT 6, 6, 5, 2, 4, 45, 6
UNION
SELECT 7, 2, 6, 2, 4, 46, 2
UNION
SELECT 8, 3, 5, 2, 4, 46, 3
UNION
SELECT 9, 4, 1, 2, 4, 46, NULL

GO

Here is an example of the desired output (based on the DML above) that I'm having trouble even beginning to figure out how to derive:

| TEAM_ID | STEAK_TYPE | STREAK_COUNT |
|---------|------------|--------------|
|       1 |          T |            1 |
|       2 |          W |            3 |
|       3 |          W |            3 |
|       4 |          T |            1 |
|       5 |          L |            2 |
|       6 |          L |            1 |

I've tried various methods using subqueries and CTE's but I can't put it together. I'd like to avoid using a cursor as I could have a large dataset to run this against in the future. I feel like there might be a way involving table variables that join this data to itself somehow but I'm still working on it.

Additional Info: There could be a varying number of teams (any even number between 6 and 10) and the total matchups will increase by 1 for each team every week. Any ideas on how I should do this?


texts: Designing a database for a weekly time schedule - speed vs space

texts:
I need to "store" a weekly time schedule as compactly as possible.  The schedules are in 15-minute increments.  In a 24 hour period that would give me 96 time slots.  Midnight is 00, 0600 is 24, 1800 is 72 and 2345 is 95.

I need to store 7 days of information per row SO...  Lookup speed vs space-wise, is it more efficient to create a row with 14 SMALLINT columns (start time and end time for each day) or 1 column concatenating padded numbers?  (IE. 2472247224722472247224722472)

There could be millions of rows, so I want to try to reuse common schedules.  This means when someone creates a schedule, I want to look up the schedules that have already been created to find a match and use the match's ID if I can find one.  If not, I'll create a new row.  Would using 14 AND statements on smallints be faster than a CHAR column lookup?  Is there an even better way to do this?


texts:
I see a lot of these kind of queries running  on my primary server where all the replica set members keep checking if the Primary is Primary by running isMaster:

query: { ismaster: true }  ntoreturn: -1 ntoskip: 0

10.117.241.104:27017 <<-- 10.117.241.104:38837 480 bytes id:16f386 1504134 - 1602

reply n:1 cursorId: 0

This is causing a connection bottleneck as my primary has 28000+ connections, which keeps increasing at the rate of 8-10 per second, and takes the primary down.

How can I handle this?


texts: How to set Parent field according to another hierarchy field

texts:
I need some help to set a parent field according to hierarchy field like this:

declare @Chart table([ChartId] int NOT NULL identity(1,1), [ParentId] int, [Number] varchar(5),
    [Serial] varchar(5), [AccountName] varchar(200), CurrencyId int, cType char(1),
    [TotalDebit] decimal(18,2), [TotalCredit] decimal(18,2));

insert into @Chart
select null,  '1    ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '10   ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '101  ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', null, 'CAPITAL & LONG TERM LIABILITIES', 1, 'T', 0, 0 union all
select null, '1013 ', '00001', 'ShareHolder 1', 1, 'R', 0, 0 union all
select null, '1013 ', '00002', 'ShareHolder 2', 1, 'R', 0, 0 union all
select null, '1013 ', '00003', 'ShareHolder 3', 1, 'R', 0, 0 union all
select null, '4     ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '40   ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '401  ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', null, 'SUPPLIERS', 1, 'T', 0, 0 union all
select null, '4011 ', '00101', 'Supplier 1', 1, 'R', 0, 0 union all
select null, '4011 ', '00102', 'Supplier 1', 2, 'R', 0, 0 union all
select null, '4011 ', '00103', 'Supplier 2', 1, 'R', 0, 0 union all
select null, '4011 ', '00104', 'Supplier 3', 2, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 1, 'R', 0, 0 union all
select null, '4011 ', '00105', 'Supplier 3', 3, 'R', 0, 0 union all
select null, '41   ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '411  ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', null, 'CUSTOMERS', 1, 'T', 0, 0 union all
select null, '4111 ', '00101', 'Customer 1', 1, 'R', 0, 0 union all
select null, '4111 ', '00102', 'Customer 2', 1, 'R', 0, 0 union all
select null, '4111 ', '00103', 'Customer 2', 2, 'R', 0, 0 union all
select null, '4111 ', '00104', 'Customer 3', 2, 'R', 0, 0 union all
select null, '4111 ', '00105', 'Customer 4', 2, 'R', 0, 0 union all
select null, '4111 ', '00106', 'Customer 4', 3, 'R', 0, 0 union all
select null, '4111 ', '00107', 'Customer 5', 3, 'R', 0, 0;

Select * from @Chart;

What i wish is to update ParentId column as Parent and Id as keyfield and this according to Number Column like: left(number, 1) should be 1 and left(number, 2) should be linked to its parent which is 1... left(number, 3) should be related to its parent ...
the output should look like:

I would be highly appreciate any help.

Thank you


texts: How to structure different types of a model in MySQL?

texts:
I am building an application where users can apply for small business loans. There are 4 types of loans (A,B,C,D). There are 10 commons questions no matter which type they choose, but then based on which type of loan they want, there are 3-4 more questions specific for that type.

What is the best way to represent this in the database?

Thanks in advanced!


texts: In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

texts:
In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns?

I am aware of the fact that PRIMARY KEY and CLUSTERED INDEX key are separate concepts and that we can create PRIMARY KEY without CLUSTERED INDEX on it (see below).

ALTER TABLE dbo.Sample
ADD CONSTRAINT PK_Sample_SeqGUID_Col1 PRIMARY KEY NONCLUSTERED (SeqGUID_Col1)

But my question is to see if it is possible to create PRIMARY KEY on a table without a CLUSTERED or NONCLUSTERED index on it.


texts: efficient primary key that does not expose business internals

texts:
I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks.
Keys are short, efficient, but are auto incremented in a predictable way.

If a user creates 2 tasks and is given a key that auto increments for both of them, than he can calculate how many tasks happened in that time interval, giving him unwanted insight.

To combat that, I thought about using UUIDs in place of auto_incremented key. But this has it's drawbacks too. Its 32 chars long when dashes are stripped, and as far as I understand has a significant performance hit. The table in question won't have more than a million rows so I am not sure how this applies.

The last part of UUID is MAC address of a node. I will be using only 1 machine, so that part can be stripped of, making it 20 chars.

My currently favourite option is to use something like SELECT FLOOR(rand() * 10000000000);  and than check if that number already exists.

Should I consider any other method of storing non predicatable primary keys? Which option do you recommend?


texts: Relational DB which can do inserts at billions of rows with multiple indexed columns?

texts:
I am looking for advice on a database to potentially replace SQLite for my application.
To briefly describe my DB requirements:

needs to support billions of rows and the most queried table has multiple indexed columns (this is currently causing slow imports with SQLite at 15m rows)
inserts done manually weekly, but should be done quickly i.e not having to go to sleep and wake up before i can query the DB with the freshly imported 100k-1m rows
almost no update queries

The only problem i have so far is the speed of importing new data sets into my DB, which i only need to do infrequently and manually. It has gotten very slow if not impossible to import data with the current amount of records (15 million) due to the indexing. This leaves me two options for SQLIte inserts:

Spread the data across thousands of SQLite tables - added complexity and i believe this would all need to be custom coded in my app

Drop indexes -> insert new data -> re-create indexes - also quite slow

Could PostgreSQL or another relational DB offer fast inserts with multiple indexed columns up to 100 million + rows?


texts: MySQL - speed up execution of ALTER TABLE

texts:
I have a MySQL DB with innoDB tables. the DB size is about 30GB. I am using ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; to change all the tables characters set and it takes forever!!
is there a way to speed up the process?


texts: Query optimization with extreme statistic


texts: General strategy for updating a full text search index

texts:
I am using a SQL Server 2008 R2 DB with FTS enabled on it. I applied an FTS index on a table. The table is frequently updated (I mean the CUD operations). Keeping this in mind, I came up with a strategy for updating the above index:

I run an update population of the index when any delete occurs on that table
I run an update population of the index once an hour to track the newly inserted texts

I would be grateful for any constructive criticism of my ideas. Should any additional details be necessary, I will provide them.

Thanks in advance


texts: CONVERT to datetime fails on Dynamic SQL - "Conversion failed when converting date and/or time from character string"

texts:
I've spent a few hours with this and still can't get it to work. I'm trying to get the path to the most recent file that matches a pattern for a dynamic SQL query where I'm loading a CSV file into a temp table for a stored procedure. The original SQL that I built the dynamic query on works perfectly fine (yes, I know CONCAT isn't in this one, but it shouldn't make a difference):

DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
            INSERT INTO @FILES EXECUTE xp_cmdshell 'dir "C:\Inventory Feeds"'
            SELECT [FileName]
            FROM (SELECT ID
                        ,CAST(LEFT([FileInfo],18) AS datetime) AS 'Created'
                        ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS 'FileName'
                        ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS 'RowNum'
                    FROM @FILES
                    WHERE [FileInfo] LIKE @FILE) AS FileInfo
            WHERE FileInfo.RowNum = 1

But when I try to run it here I get the "Conversion failed when converting date and/or time from character string" error:

DECLARE @DIR varchar(MAX)
SET @DIR = 'C:\Inventory Feeds\';
DECLARE @FILE varchar(MAX)
SET @FILE = '%InventoryFeedResults%.csv';

DECLARE @FINDFEED nvarchar(MAX), @PARAM nvarchar(MAX), @PATH nvarchar(MAX)
    SELECT @FINDFEED = 'DECLARE @FILES TABLE (ID int IDENTITY, [FileInfo] varchar(MAX))
                INSERT INTO @FILES EXECUTE xp_cmdshell ''dir "'+ @DIR + '" /b''
                SELECT @ADD = CONCAT(''' + @DIR + ''',[FileName])
                            FROM (SELECT ID
                            ,CAST(LEFT([FileInfo],18) AS datetime) AS ''Created''
                            ,RIGHT([FileInfo],CHARINDEX(CHAR(32), REVERSE([FileInfo]),1)-1) AS ''FileName''
                            ,ROW_NUMBER() OVER(ORDER BY CONVERT(datetime,LEFT([FileInfo],18)) DESC) AS ''RowNum''
                        FROM @FILES
                        WHERE [FileInfo] LIKE ''' + @FILE + ''') AS FileInfo
                        WHERE FileInfo.RowNum = 1'
    SELECT @PARAM = '@ADD nvarchar(500) OUTPUT'
    EXECUTE sp_executesql @FINDFEED, @PARAM, @ADD = @PATH OUTPUT

    SELECT @PATH

The dates are nvarchars in this format, EX:  '2019-02-15 10:29:00.000'

The filenames are formatted with a report name and a 3 digit number, EX: InventoryFeedResults924.csv

The full output for FileInfo is:

02/15/2019  10:29 AM           405,609 InventoryFeedResults924.csv

This needs to work on SQL Server 12.

I've tried switching between CAST and CONVERT and a few different style numbers in CONVERT, but I'm honestly not sure why it would work one place and not the other. I just need to know what magical incantation that will let me convert this string to a datetime in a dynamic query so I can pull the most recent file if it's possible. Or maybe I could add an option to xp_cmdshell to format the timestamp to make it work? I'm at a loss, help me please, Internet!


texts: Is it ever a good idea to denormalize for integrity?

texts:
I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.

These problems can take a variety of forms - they might be multiple choice (What's 2 + 2? A: 2. B: 3. C: 4), or they might require the user to calculate an answer of their own, in which case I need to constrain their input to be something like '440' or '1/2' or '.333'. Some of the problems might prompt the user to type in an essay. And, of course, I may need to add more types of problems later.

The tables I'm envisioning, in a simplified form, are something like this:

CREATE TABLE problems
(
  problem_id serial NOT NULL PRIMARY KEY,
  problem_type text NOT NULL, -- Refers to a lookup table
  answer_letter text, -- If not null, refers to the correct answer in the answers table below.
  response text -- If not null, represents a correct answer to be input, like '0.4'
);

CREATE TABLE answers
(
  problem_id integer, -- Foreign key
  answer_letter text,
  content text,

  CONSTRAINT answers_pkey PRIMARY KEY (problem_id, answer_letter)
)

CREATE TABLE questions
(
  user_id integer,
  created_at timestamptz,
  problem_id integer, -- Foreign key
  answer_letter text,
  response text,

  CONSTRAINT questions_pkey PRIMARY KEY (user_id, created_at)
);

So, the problems table would have a variety of constraints to ensure that:

When problem_type is 'multiple_choice', answer_letter must not be null and response must be null.
When problem_type is 'user_input', answer_letter must be null and response must not be null. Response must also consist of only a few characters.
When problem_type is 'essay', both answer_letter and response must be null, since I can't really have a correct answer for an essay question.

This is clean enough, and constrains the problems table just fine. I might use an enum instead of a lookup table for problem_type, since all of its possible values will already be baked into the schema anyway.

My difficulty is, how to constrain the questions table? Its constraints will be very similar (I don't want an answer_letter supplied for a question that references an essay problem, and so on). I can think of a few options:

Create a unique index on problems (problem_id, problem_type), add a problem_type field to questions and include it in the reference to problems, then use it in check constraints in a way similar to problems. This is the way I'm leaning right now, since it seems cleanest overall, but then I'm denormalizing to achieve the proper constraints, which feels wrong to me.
Create three problem tables, one for each type, and constrain them separately. Do the same with three question tables. This feels like the pure relational way to me, which is typically what I'd like to go for, but it also feels way too complex. I don't want to have to deal with unioning three tables (or more, later on) to get a user's question history.
Go with #2, but use Postgres' inheritance support to try to keep the logic simple. But since you can't point a foreign key at a hierarchy of tables, again it's not a very clean solution, it's a solution that would again need to be hacked around.
Use triggers to ensure that questions data fits the corresponding problem. Maybe it's because I don't have much experience with triggers, but I'm concerned that cramming that sort of imperative logic into the DB will eventually become unmanageable.
Forget the question constraints, handle it in app logic, and hope for the best. You can't constrain everything all the time. Of course, I don't really like this idea either.

I feel like there's a problem with my approach to modeling that's leading me to these difficulties, especially since I'm running into a couple of cases very similar to this elsewhere in my schema (this one was just the easiest to describe). Maybe it's just a domain that's difficult to model, but I feel like there must be a better way, and I'm just not normalizing correctly.

Help? Thanks!


texts: CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

texts:
@@Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a Geography datatype?

DECLARE @xPolygon NVARCHAR(MAX)
     = '53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670'

CREATE TABLE #xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO #xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

The Second Compute Scalar operator in the actual execution plan for Expr1005 shows this

Scalar Operator(CONVERT_IMPLICIT(geography,[geography]::STPolyFromText((N'POLYGON(( '+[@xPolygon])+N'))',(4326)),0))

There is no such CONVERT_IMPLICIT if I change to using a user table instead of a temp table.

CREATE TABLE dbo.xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO dbo.xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

Here's the actual execution plan

If I try the same test on a SQL 2014 database on the same machine, I don't get a convert_implicit whether I use a temp table or a user table.

Why the difference on 2008R2? and how do I get rid of the Convert_Implicit when using a temp table on 2008R2?


texts:
I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL).

It's for online radio. A listener connects to the stream, and a row is created. Once they stop listening, Stop is updated to the current time.

Currently, it's about 80K rows per day.

I'd like to process the sessions once a day by determining the max amount of listeners for each minute, and push that data into a different table: Timestamp, ListenerCount (UNSIGNED SMALL INT).

I'm not super concerned with speed, just with working out how I can process this data within MySQL. I'm assuming I use a stored procedure (not much experience there), loop over each minute between the MIN(Start) and Max(Stop), and do a COUNT() between x-minute and x-minute:59.

Does this sound viable? Is there a better way?

Thanks for your time.


texts: How to restore a specific table from a big mysqldump file?

texts:
From a very big backup file (created by mysqldump) I need to bring back only one (or a few) specific table(s), without loading all tables.
Are there any fast alternatives?


texts: DENY EXECUTE on one schema object hides other objects in same schema

texts:
I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give them the right to execute the SP, so I used

DENY EXECUTE ON OBJECT::XTR.LoadData TO [AD\Users]

when now one of my users connects to the database ALL stored procedures are not visible anymore, the same goes for scalar valued functions (table valued functions they still see).

Did I get a concept totally wrong or why is denying execution on a single object having such effects on all other objects in the same schema ?


texts: Database model with Users, Resources, Permissions

texts:
I am fairly new to the DB design. I need to handle what a user can do on a specific resource.

One of the main queries I need to drive is:

  Get all the cars a user has write permission to.

I think if I only had the one resource it would be easier, but I will have plenty of resources and so I am trying to design something via a ResourceType model, so that I don't have to create a lot tables as the number of resources grow.

I saw this question: Discretionary Access Control (DAC) model

For what I can tell that is the closest thing to what I am trying to accomplish because it differentiates between roles approach and discretionary. I do not have roles and won't have roles for now. When a user creates a resource (like Car, Location, etc...) the server assigns the "owner" permission for that user. The owner can in turn grant permissions to other users, like read, write...

I have created the following tables:

    CREATE TABLE ResourceType
    (
       Id (PK)
       Name
       resourceId
       ...
    )

    CREATE TABLE Car
    (
       Id (PK)
       model
       resourceTypeId (FK to car ResourceType)
       ...
    )

    CREATE TABLE User
    (
       Id (PK)
       Name
       ...
    )

    CREATE TABLE Permission
    (
       Id (PK)
       canRead
       canWrite
       ...
    )

then I created a junction table:

    CREATE TABLE ResourceTypeToUserPermission
    (
       Id (PK)
       permissionId (FK to Permission)
       resourceTypeId (FK to ResourceType)
       userId (FK to User)
    )

I was hoping to use that junction table to get what I needed with inner joins and where clauses on userId = x...

I am obviously not doing this right and I am wondering how I can modify things correctly so that for any resource I will have in the DB  I will be able to get all resources of a given type with a certain type of permission for a given userId.

Any help would be much appreciated.


texts: Efficiently Filter Large Set With Disjunctions

texts:
Let's say I have a single table

CREATE TABLE Ticket (
    TicketId int NOT NULL,
    InsertDateTime datetime NOT NULL,
    SiteId int NOT NULL,
    StatusId tinyint NOT NULL,
    AssignedId int NULL,
    ReportedById int NOT NULL,
    CategoryId int NULL
);

In this example TicketId is the Primary Key.

I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed:

The query will always perform a range filter on an
InsertDateTime
The query will always ORDER BY InsertDateTime DESC
The query will page results

The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example:

SELECT
    TicketId
FROM (
    SELECT
        TicketId,
        ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum
    FROM Ticket
    WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01'
      AND StatusId IN (1,2,3)
      AND (CategoryId IN (10,11) OR CategoryId IS NULL)
    ) _
WHERE RowNum BETWEEN 1 AND 100;

Now assume the table has 100,000,000 rows.

The best I can come up with is a covering index that includes each of the "optional" columns:

CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime DESC
) INCLUDE (
    SiteId, StatusId, AssignedId, ReportedById, CategoryId
);

This gives me a query plan as follows:

SELECT

Filter

Top

Sequence Project (Compute Scalar)

Segment

Index Seek

It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal.

Are there better strategies for implementing this kind of searching?

I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client.


texts: T-SQL CREATE VIEW returning 'There is already an object named x'

texts:
SQL Server 2014. I have a database on which I created a view called 'Sales-Analysis'. I also have a second database with the same schema as the first and I want to add the view to that. So in SSMS I performed 'Script view as CREATE to new Query Editor window'. I changed the first line of the script to reflect the name of the second database and ensured it was selected in the SSMS databases dropdown too. However when I execute I get:

"There is already an object named 'sales-analysis' in the database."

There isn't other than the one in the original database. No temporary tables involved. VS 2015 is closed.

It also doesn't work even if I take the original db offline, or if I save the script as an .SQL file and execute it with SQLCMD.

Same server. Refreshed views. Can successfully create a view with a '2' appended into the target database. It's just when the view name is the same as the original database. use [database] is already at the beginning of the script -'Script view' adds it. I changed it to the target database name. The view 100% does not exist in the target db. I checked via querying sys.views.


texts:
Thanks for reading this.   I’ve completely overthought this so I’m hoping to get some feedback.

I have a customer table (custID, statusID), a notes table (custID, noteID), an action table (custID, actionID, noteID)  and a status table (custID, actionID, noteID).

The notes table contains self-referential hierarchical data (like a conversation or a forum with a parent note).

Here are the scenarios:

Every customer has a status (like Approved) but they only have one current status.

A customer might have an action that requires a status update (i.e. credit review leading to an increase in credit)

Any user can record notes about a customer

An action will require one parent note.

Any status change will require one parent note.

Here’s where I’m stuck:  I’m thinking I’m getting myself into a data spiral where everything references something else in a random way.  I need to look up a customer and have all the notes available so that was the thinking with my initial design but something seems not right.

I appreciate any thoughts and feedback on this.


texts: How to convert date and/or time from character string as variable field

texts:
I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) if it is the case that not all databases have been backed up within the last 30 days but i get the error Conversion failed when converting date and/or time from character string.

;WITH CTE AS
(
SELECT
--sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date > DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO Server.Database.Schema.TableName
SELECT @@SERVERNAME  as serverName,
        --DatabaseName as DatabaseName,
        CASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime

FROM CTE
GROUP BY LastBackUpTime


texts: How does SQL Server determine precision / scale?

texts:
I'm running SQL Server 2012

SELECT
   0.15 * 30 / 360,
   0.15 / 360 * 30

Results:

 0.012500,
 0.012480

This one is even mor confusing to me:

DECLARE @N INT = 360
DECLARE @I DECIMAL(38,26) = 0.15 * 30 / 360
DECLARE @C DECIMAL(38,26) = 1000000

SELECT @C *  @I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 )
SELECT @C * (@I *  POWER(1 + @I, @N)  / ( POWER(1 + @I, @N) - 1 ) )

The first select gives me the correct result: 12644.44022
The second one truncates the result: 12644.00000


texts: Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

texts: I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?


texts: How to increase the maximum number of control files?

texts:
ORA-00208 number of control file names exceeds limit of num

  Cause:    An attempt was made to use more control files than Oracle
  supports. The limit is given in the message.

  num = 8.

I need to create 11 control files.


texts:
Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct results. But I dont know how to join them so I will get the desired result.

SELECT ... FROM (
SELECT users.name,
posts.id,
text_posts.text,
posts.location
posts.createdate

COUNT(*) AS total
FROM posts
INNER JOIN likes
    ON likes.posts_id = posts.id
INNER JOIN users
    ON photos.user_id = users.id
    INNER JOIN text_posts
            ON text_post.posts_id = posts.id
GROUP BY posts.id
ORDER BY total DESC ) as A
    join (select username, user_id, count(user_id)
            as totalfriends FROM follows
    INNER JOIN users ON users.id = follows.user_id
    group by user_id
    order by totalfriends desc )
                as B ON A.posts.id = B.posts.id
                    LIMIT 10;

  The result should be 10 posts but the user needs to have the most amount of friends and that those posts the users created have the most likes, and also display the name of the creator of that post, the content of the post, location, date of creation and the number of likes so basically the two subqueries put together.


texts: Document Store & Relational DB Hybrid. Exists? Possible?

texts:
Does anyone know of any product or open source project that attempts to strap together Mongo DB and MySQL for instance?

I hear a lot about how relational DBs being ACID compliant makes it nearly impossible to do sharding as easily as in Mongo and this is the achilles heel. The NoSQL fans are surely right. A technology must eventually parallelize or die.

No to NoSchema

However, schemaless DBs are not a sustainable solution to many problems. Schema changes are only hard because they force you to see what you're breaking. Not seeing doesn't mean you're not breaking.

Best of both?

What I need help understanding is why it seems accepted that you can't in theory have the advantages of both. Can't a relational DB be engineered to compromise with a wise strategy of eventual consistency? I understand why it's more difficult than with documents - a single fetch is composed of records stored physically all over the disk. But how about a document store which acts as a front for a relational DB?

Imagine

The DB has a built in ORM to map from relational to document collection and you interface with the document in your app. Essentially, the document collections are like materialised views with write-back capability. By default the relational DB part is ACID compliant and ensures that non-acidic documents are eventually consistent. Cross-record schema constraints may not be performant enough but most other schema controls could be.

When you query a large batch of documents, if a few are out of sync - because another document sharing the same denormalised data has just been written to - then they are silently refreshed live from the underlying DB by default. If blocking is an issue then you can choose to handle those dirty docs with a number of other looser consistency options.

Pros and pros

..and small cons

You don't get the document store advantage of fast writes but

Gets NoSQL advantage: massive and fast reads, easy sharding and no O-R impedance mismatch for front-end developers.

Gets SQL advantage: Organisations can stay nimble, able to work with their data from new angles not thought of initially. Schema helps keep production data from turning into a turgid mess of exceptions before its too late.

Gets hybrid advantage: Rows can be physically re-organised in the RDB to follow the patterns suggested by the most frequently accessed document collections.
When the load gets too high for the relational DB on one box this will make sharding more of a possibility (with a possible tradeoff against whole-system analytical queries but that's what you have an OLAP for).  The relational DB will also have much fewer reads than if it were directly user facing.

I've misunderstood something. I must have.

I don't imagine this is an easy bit of engineering but it seems within the powers of the those who have brought us the products we already have.

I'm not seeking general discussion here but I wouldn't be surprised if there's a big, concrete gotcha I'm missing in this account and I'd like to know what it is.


texts: How Can I Monitor SQL service with Zabbix?

texts:
We are implementing Zabbix, monitoring a lot of processes.

But I think it would be great to monitor the SQL service ( if it's running, stopped...)

We are now monitoring Ping, conections services and etc.

im new in this Area, But it's something I was asked to do.


texts: What is the best use of memory in MySQL?

texts: I have a MySql server with 2Gb of memory. What's the best configuration to use then with best results? How can I "distribute" this memory fine to MySql server?


texts: Oracle 12c export and import schema with different data tablespace and index tablespace

texts:
Suppose that I exported the schema objects from my Oracle 12c database using the original export utility as below:

exp userid=<export_schema_name>/<export_schema_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log

The export_schema_name uses different tablespaces for data and indexes, let's called data_tbs_1 and idx_tbs_1. All tables in the export_schema_name are created as below:

create table <table_name>
(
    -- columns definition
)
tablespace <data_tbs_1>
/
--
alter table <table_name>
add constraint <constraint_name> primary key(<key_name>)
using index tablespace <idx_tbs_1>

Now I want to import the exported dump file into a new schema that I created as below (I already created a new data tablespace data_tbs_2):

create user <import_schema_name> identified by <import_schema_password>
default tablespace <data_tbs_2>

The imporst command I am going to use is:

imp userid=SYSTEM/<import_system_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log fromuser=<export_schema_name> touser=<import_schema_name>

My questions are:

If I do this, where are the data and indexes in  stored? Are they still in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.
If the indexes of the  are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?