texts: Peer review database design for various reports

texts:
I'm currently trying to redesign a poorly thought out database for a peer review system that we use internally at my workplace. Employees can log on and submit a review about another employee who worked on the same team for a specified project.

I was initially tasked to just remove one of the questions (from 4 to 3) but they are stored as columns in a review table instead of as their own table. I'm attempting to redesign the database with possible additions of questions in mind, and here's what I've got so far:

---------------
| category    | // the questions you are asked to rate an employee by
---------------
| id (PK)     |
| name        |
| description |
---------------

--------------------
| score            | // the score you give to a category
--------------------
| id (PK)          |
| score            | // ENUM (1,2,3)
| category_id (FK) | // category.id
| review_id (FK)   | // review.id
--------------------

--------------------
| review           |
--------------------
| id (PK)          |
| date             |
| for_month        |
| reviewee_id (FK) | // employee.id
| reviewer_id (FK) | // employee.id
| project_id (FK)  | // project.id
| comments         |
--------------------

--------------
| department |
--------------
| id (PK)    |
| name       |
--------------

----------------------
| employee           |
----------------------
| id (PK)            |
| first_name         |
| last_name          |
| username           |
| department_id (FK) | // department.id
| start_date         |
| end_date           |
| is_admin           |
----------------------

------------
| project  |
------------
| id (PK)  |
| name     |
| end_date |
------------

--------------------
| project_employee | // maps employees to projects
--------------------
| project_id (FK)  | // project.id
| employee_id (FK) | // employee.id
--------------------

It seems like I'm going in the right direction, at least for storing reviews. However, retrieving data about reviews for managers feels overly complicated, involving lots of joins. I want to provide multiple views of the data, such as:

List of employees with year average scores
Reviews for individual employees
List of departments with year average scores
Reviews for individual departments
List of projects with average scores
Reviews for individual projects
Reviews by month

Things like showing average yearly scores for a department get fairly convoluted, since there is no direct link between scores and departments, or even reviews and departments.

Would it be overkill to add foreign keys to the score table for project, reviewer, reviewee, and department? That way I can very directly query the score table to get scores for departments. Or maybe I should keep what's in review and just add department? Or should I suck it up and keep writing long queries that join multiple tables from score to review to employee to department to get that data? Or am I just doing this wrong?

EDIT: After thinking about it some more, I'm leaning towards adding department_id to the review table, since that should give me everything I need to lookup reviews and grab scores. It also solves another unrelated issue, that of what to do when an employee changes departments, since the original reviews should still count towards the department they were in at the time. My question of if I'm doing this right in general still stands...


texts: create mongodb user to only read in replica set (not in master)

texts: We have a Mongodb with ReplicaSet and a readonly user. This user can read from both Master and Replica Set. Some of the developers might accidentally run heavy queries from Master with this readonly user. Can be restricted to readonly in ReplicaSet instance and not in Master?


texts:
I am using SQL*Plus and connected to the HR schema.

When I do SELECT * FROM tab; it gave me three column names displayed in different lines.

Is there a way to correct it?


texts:
I am monitoring MySQL server using phpMyAdmin [Status => Monitor] function. And it shows a number of useful graphs.

Which variables (or which mechanism) is phpMyAdmin using to pull this data?

ps. I was wondering if it is calling SHOW STATUS query?   But when I look through a bunch of variables resulting from SHOW STATUS I cannot spot anything related neither to CPU, memory or not even active connnections...


texts: SQL Server 2016: Temporal table performance vs. Triggers and CDC

texts:
It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended test on SQL Server 2016, so I thought I'd ask about it here.

Basically, the typical advantage with triggers is that they are easier to manage in stand-alone and clustered / alwaysOn environments, are real-time for being synchronized, and have access to session data such as the user ID.

CDC on the other hand while requiring a bit more management and being asynchronous, is much lighter, and thus performs far better. So if there's any doubt at all that the bottle necking caused by triggers could become a problem, CDC will basically be the superior solution. In terms hardware requirements, there's a negligible extra space requirement by CDC due to using logs and cdc audit tables for tracking the changes.

The question: How do temporal tables compare to the two above? In terms of speed, performance, storage space usage. WHEN should I use temporal tables instead of triggers or CDC? When should I not?

I understand anything as potentially complex as the business requirements and technical limitations behind DB auditing isn't going to have one easy answer, as it depends largely on the requirements and scope of the project. But anything to shed more light on the questions above would be appreciated. Thanks!


texts: Oracle DB union two select statements which return multiple rows

texts:
I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than requested number of rows", which occurs of course because I have more than 1 row being retunrned from the second select statement.
This is my stored procedure:
DECLARE

timer_before timestamp;
timer_after timestamp;
timer_duration number;

referencelistid number(20);
referencename varchar(20);
startdate timestamp;
enddate timestamp;
creationuser varchar(20);
creationdate timestamp;
changeuser varchar(20);
changedate timestamp;
username varchar(20);
changetime timestamp;

BEGIN
timer_before :=systimestamp;
--DBMS_OUTPUT.put_line('Start at: ' || timer_before);

SELECT
    REFERENCE_LIST_ID,
    REFERENCE_NAME,
    START_DATE,
    END_DATE,
    CREATION_USER,
    CREATION_DATE,
    CHANGE_USER,
    CHANGE_DATE,
    USERNAME,
    CHANGE_TIME
into
    referencelistid,
    referencename,
    startdate,
    enddate,
    creationuser,
    creationdate,
    changeuser,
    changedate,
    username,
    changetime
from
    tbrd_reference_lists
where
    reference_list_id = 0000
UNION SELECT
    REFERENCE_LIST_ID,
    REFERENCE_NAME,
    START_ DATE,
    END_DATE,
    CREATION_USER,
    CREATION_DATE,
    CHANGE_USER,
    CHANGE_DATE,
    USERNAME,
    CHANGE_TIME
from
    tbrd_reference_lists_h
where
    reference_list_id = 0000;

timer_after :=systimestamp;
--DBMS_OUTPUT.put_line('End at: ' || timer_after);

select
  extract(second from  timer_after-timer_before) into timer_duration
from
  dual;

DBMS_OUTPUT.put_line('Delete Duration: (s): ' || timer_duration);
DBMS_OUTPUT.put_line('Delete Duration: (ms) ' || timer_duration*1000);

END;

I tried to search for a solution, and pipeline functions look like an option. Is there anything simpler that I can use to achieve this
Thanks
Pulkit


texts: Merge replication gone wrong, and snapshot is timing out

texts:
I have a merge replication set up between one Publisher and 3 Subscribers, replicating a single database with around a 100 different tables of various sizes. It's "real-time" replication running continually 24x7, and it's worked fine for the last year or more: the volume of changes is small but constant throughout the day.

However, last night it all started to go wrong. Several tables in the database were "locked": applications couldn't access them. I did the usual reboot, and everything seemed fine, and I didn't think much of it. However, this morning the same thing was happening: vital tables were locked, and digging into sp_who2 showed that replmerg was chewing on something.

I tried to reinitialise the subscription; this seems to have made things worse: the replmerge log is telling me that the snapshot is out of date. When I try to run the snapshot agent - which I've done a few times in recent memory and it's never taken more than a couple of minutes - it gets 46% of the way through before telling getting stuck at:

[46%] Setting up the publication for filtered articles

... and eventually timing out. I've dug into the queries that are running when it's stuck, and it appears to be chewing on:

select @changes_in_tombstone = count(*)
  from dbo.MSmerge_tombstone with (rowlock)
  where generation = @g

I've looked at the MSMerge_tombstone table, and it's got 21 million rows in it (a 14 day retention time): I've no idea if this is good or bad.

So I don't know how to proceed: I really don't want to have to delete and recreate the publication - there are changes at the subscribers that I don't want to lose - but it appears I'm stuck in a Catch 22: I need to create a new snapshot in order to get the replication going again, but I can't create a snapshot as it times out.

Should I resign myself to recreating the publication, or is there a way to fix this?


texts: Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

texts:
If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right?
You can call a function inside a procedure
create or replace procedure P
    a_variable_name number;
begin
    a_variable := a_function('a parameter');
end;
/

And i imagine you'd have to pass the result of your procedure into the function. Like
if X procedure executed correctly, then do nothing
if Y error, do the Y exception handling

Is such a thing possible? If not, how do oracle users handle writing 10 different procedures that all use the same exception handling? Like, are there any tips and tricks besides just copying and pasting those exceptions and hoping you'll never have to change all 10 of them if you have to change one thing?


texts: MySQL - [Note] Got an error reading communication packets

texts:
I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs

2020-01-17T18:00:04.787586Z 1541 [Note] Got an error reading communication packets
2020-01-17T18:00:06.787610Z 1543 [Note] Got an error reading communication packets
2020-01-17T18:00:08.787548Z 1544 [Note] Got an error reading communication packets
2020-01-17T18:00:10.787586Z 1546 [Note] Got an error reading communication packets
2020-01-17T18:00:12.787575Z 1547 [Note] Got an error reading communication packets

I tried by changing below variables but no success

set @@global.max_connections = 400;
max_allowed_packet=256M
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M
SET GLOBAL thread_cache_size=50;

i tried to set SET GLOBAL log_warnings=3; for details logging but nothing is showing

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

Internet is full of tips about how to solve this error message, but the usual solutions are tweaking the various timeout and max_allowed_packet settings. However, changing these settings made no difference to me, the error kept happening infact now i shut all application and only MySql is running but still error is repeating. Although my services are not hampering but i have implemented Centralized logging and this log is flooding it.

I also run mysqltuner but its suggestion are for query cache only and tunning it has no effect

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

So if there is not client why MySQL is showing this message as no reading or writing happening??

Extra Details:

MySql Starting logs as they start popping as soon as mysql start
https://gist.github.com/imranrazakhan/d5ccca11c840b3229bf2cedb1a2deebb
SHOW GLOBAL STATUS
https://gist.github.com/imranrazakhan/5b0f06529916435e5df71aebdb8062c0
SHOW GLOBAL VARIABLES;
https://gist.github.com/imranrazakhan/2bf9cadd5d1ae0e350feeecd3a984cef
SHOW FULL PROCESSLIST;

mysqltuner output;
https://gist.github.com/imranrazakhan/71e4638b638364a939ac1410765188ee
ulimit -a;

https://gist.github.com/imranrazakhan/cadf78df1d09e4ad11676d51ec02839b
MySQL Conf
https://gist.github.com/imranrazakhan/5ec96905048758eb49cf7ac6140bdfff


texts: how to migrate data between two distincts tables

texts:
I have a database with millions of records in a table users. I need to collect this data, tranform to a new format (removing a field, for example) and save it to another database. I'm currently doing this with Ruby, but the performace is way too poor. I was thinking if maybe I could do that with SQL. I'm using Postgres.

I know the question sounds a little sparse. Ask me more info/data if needed...


texts: Database Normal Form (financial statement)

texts:
I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. Also, what about my proposed design(s) violate normal form?

Database (in NF3?)

TICKER TABLE

ticker |
wmt
...

YEAR TABLE

year |
2014
...

QUARTER TABLE

quarter |
q1
q2
q3
q4

YEAR_QUARTER TABLE

year    | quarter    |
year_fk | quarter_fk
2014_fk | q1_fk
...

TICKER_QUARTER TABLE (necessary because fiscal/calendar dates are not the same for all companies)

ticker    | quarter         | date
ticker_fk | year_quarter_fk | e.g. 2014-11-11
...

ITEM TABLE

item    |
revenue
cogs
...

Final Table Options

TABLE 1

ticker    | date                 | item       | value
ticker_fk | ticker_quarter_fk    | item_fk    | 100.00
wmt_fk    | ticker_quarter_fk    | cogs_fk    |  65.00
...

QUESTION: Does this Table 1 option properly adhere to normal form?

TABLE 2

ticker | date              | revenue | cogs  | ...
wmt_fk | ticker_quarter_fk | 100.00  | 65.00 | ...
...

I don't like this option because if I add a new item I need to append a new column.

Or

I scrap the item_fk reference table and have a table for EACH AND EVERY item, e.g.

TABLE REVENUE

ticker | date              | value
wmt_fk | ticker_quarter_fk | 100.00

TABLE 1...N (cogs, sg&a, ...N)

ticker | date              | value
wmt_fk | ticker_quarter_fk | n

I don't like this option because I have to create a new table for each item and I often have use for multiple items in a single query so this option would require a lot of joins.


texts: Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

texts:
I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server), Solomon Rutzky mentions the following reasons to prefer the SQL data types for strings:

  A primary difference between native common language runtime (CLR) data
  types and SQL Server data types is that the former do not allow for
  NULL values, while the latter provide full NULL semantics.

  ...

  Streaming values in can be achieved via SqlChars for N[VAR]CHAR,
  SqlBytes for [VAR]BINARY, and SqlXml.CreateReader() for XML...

  ...

  When using SqlString (not string or even SqlChars) you can access the
  CompareInfo, CultureInfo, LCID, and SqlCompareOptions properties...

I know that my input will never be NULL, I don't need to stream the values in, and I'll never check the collation properties. Could my case be an exception where it's better to use String instead of SqlString? If I do go with that approach, is there anything in particular that I should watch out for?

If it matters, I'm using SQL Server's default collation. Here's part of my source code, with s1 being the input parameter:

fixed (char* chptr = s1)
{
    char* cp = (char*)current;

    for (int i = 0; i < s1.Length; i++)
    {
        cp[i] = chptr[i];
    }
}


texts: Export as csv or xsl file from SQL Server 2008 express R2

texts:
I am basically LAMP developer got some assignment where SQL Server is required. I have installed SQL Server 2008 R2 Express and imported backup file in it.

Now, I want to export user table in excel or csv but not able to do that.

Can anyone tell me how can I export table?


texts: Prostrct create causes windows batch file to exit

texts:
I have a daily job to restore a database from last night's back up before doing other things to it and moving on. I'm trying to automate this with a script, but when I get to the following command the batch file exits - it won't run anything after this command:

prostrct create repldb rdb10.st -blocksize 8192 >> "%logfilename%" 2>&1

So if I put another command on the very next line such as echo boo! then nothing appears.

Can anyone advise on how I can get my batch to continue after prostrct create?


texts: How to remove a database server from Utility Explorer

texts: I use the Utility Explorer to monitor some of my databases but I forgot to remove one before retiring the server. Normally, you could just right click and select "Remove Managed Instance" but I can't do that since the server has been wiped from existence. Now it is stuck in my list without any data


texts: Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication

texts:
We created two new mysql servers running 5.5.  Our old server were 5.0.

The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues.

We have some long running select queries (40-50 sec), and when they are run replication breaks - we have to skip a couple of records to get replication restarted.  This is consistent and repeatable.  I suspect that some tables are getting updated on each server, but replication is paused trying to update a locked table - and when it is done, each server is trying to send data to the other and replication is broken.

I am seeing a lot of "waiting for table level lock" on select statements when this long query is running.

Is there a setting I've missed to prevent selects from locking tables? Is there some other cause, or any way to fix this?


texts: Unable to run job - Subquery returned more than 1 value

texts:
I'm trying to run the following job:

IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

But end up getting the error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression. [SQLSTATE 21000] (Error 512).  The step failed.

I have this working on another sql server but on this specific I get this error message and can't really figure out why. I'm new to SQL so would appreciate some help. The two servers don't differ in sql version.

Here's the result from the working SQL job:

  Message Executed as user: VT\sqladmin-internaltes. Unable to execute
  job on secondary node [SQLSTATE 42000] (Error 50001).  The step
  failed.


texts:
I've been scratching my head to solve the following error, but haven't made much progress:

$ barman switch-wal --force --archive --archive-timeout 180 pg

The WAL file 0000000100000059000000FF has been closed on server 'pg'
Waiting for the WAL file 0000000100000059000000FF from server 'pg' (max: 180 seconds)
ERROR: The WAL file 0000000100000059000000FF has not been received in 180 seconds

I had earlier configured barman on a throwaway VPS and everything worked fine. But when I tried replicating this on our final EC2 instance, I ran into the issue mentioned above. I have ensured the following (even though I don't remember doing this on the VPS):

SSH from barman => pg is working
SSH from pg => barman is working
All firewall rules on 'pg' have been disabled, temporarily

What could be the issue here?

Edit: Adding the output of barman check pg

Server pg:
2017-12-08 05:56:31,841 [14559] barman.server ERROR: Check 'WAL archive' failed for server 'pg'
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
2017-12-08 05:56:34,174 [14559] barman.server ERROR: Check 'replication slot' failed for server 'pg'
    replication slot: FAILED (slot 'pgbackup' not active: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'backup maximum age' failed for server 'pg'
    backup maximum age: FAILED (interval provided: 14 days, latest backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'minimum redundancy requirements' failed for server 'pg'
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 3)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
2017-12-08 05:56:34,367 [14559] barman.server ERROR: Check 'receive-wal running' failed for server 'pg'
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

Edit: Added output of barman receive-wal pg

$ sudo barman receive-wal pg
2017-12-10 01:48:01,129 [25410] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
Starting receive-wal for server pg
2017-12-10 01:48:01,138 [25410] barman.server INFO: Starting receive-wal for server pg
2017-12-10 01:48:03,087 [25410] barman.wal_archiver INFO: Activating WAL archiving through streaming protocol
pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)
2017-12-10 01:48:03,391 [25410] barman.command_wrappers INFO: pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)

^CSIGINT received. Terminate gracefully.
2017-12-10 01:50:42,512 [25410] barman.wal_archiver INFO: SIGINT received. Terminate gracefully.

Edit: Configuration files --

; --------------------
; Contents of /etc/barman.conf after removing all commented lines.
; --------------------

[barman]
barman_user = ubuntu
path_prefix = /home/ubuntu/pg/bin
configuration_files_directory = /home/ubuntu/barman.conf.d
barman_home = /home/ubuntu/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = bzip2
retention_policy = RECOVERY WINDOW OF 1 MONTH
last_backup_maximum_age = 2 WEEKS
minimum_redundancy = 3
retention_policy = RECOVERY WINDOW OF 4 WEEKS

/home/ubuntu/barman.conf.d/pg.conf:

; --------------------
; Contents of /home/ubuntu/barman.conf.d/pg.conf after removing all commented lines.
; --------------------

[pg]
description =  "Postgres DB"
conninfo = host=REDACTED user=REDACTED dbname=REDACTED
backup_method = postgres
archiver = off
streaming_conninfo =  host=REDACTED user=REDACTED dbname=REDACTED
streaming_archiver = on
slot_name = pgbackup


texts: Why can't I shrink my logfile below to the requested value?

texts:
Experimenting VLFs behaviors with my test lab. Firsty, I have created two databases (BASE2 & BASE3) with different log files size (1024 & 2048 MO) :

Secondly, I'am running the following queries to determine log space usage and the active portion of the VLF :

USE BASE2
dbcc loginfo
GO
USE BASE3
dbcc loginfo
GO

DBCC SQLPERF(LOGSPACE)

So in my opinion there is plenty of space that I can reclaim. Now starting this query to shrink all databases log files :

DECLARE
@db nvarchar(255),
@fileName nvarchar(255),
@cmd nvarchar(255)

BEGIN

DECLARE dbcursor CURSOR
FOR

SELECT table2.name AS BASE, table1.name AS FICHIER_JOURNAL_BASE

    --table1.database_id,
    --table1.physical_name,
    --table1.type_desc
FROM sys.master_files AS table1

INNER JOIN sys.databases AS table2
ON table1.database_id = table2.database_id

WHERE table1.database_id > 4 AND table1.type_desc = 'LOG'
AND table2.state_desc = 'ONLINE' AND table2.recovery_model_desc = 'FULL';

OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @db, @fileName;
WHILE (@@FETCH_STATUS <> -1)
BEGIN

--print @db;

 SET @cmd = 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
      PRINT 'USE ['+ @db +']; DBCC SHRINKFILE ('''+@fileName+''',512);'
        EXECUTE (@cmd)

FETCH NEXT FROM dbcursor INTO @db, @fileName;
END
CLOSE dbcursor;
DEALLOCATE dbcursor;
END

end here the result :

The requested value was 512MO but the current value on disk is around 650MO. If I restart the query with

USE [BASE2]; DBCC SHRINKFILE ('BASE2_log',8);
USE [BASE3]; DBCC SHRINKFILE ('BASE3_log',8);

the value on disk will be around 250 MO. So why my firt query did not shrink the log files to the requested value 512 MO ? Indeed, There was no active VLF at the end of file and still a lot of unused space.


texts: We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER INDEX command with rebuild, also, we have performance problems right now with database, could it be related to using old method for reindexing ? We have MS SQL Server 2008 (11.2)


texts:
Im trying to list all the indexes in my Postgres along with their order like ASC or DESC. I found some great answers from this StackOverflow question. But unfortunately, the pg_am table got removed a lot more columns.
So Im  not able to find any alternate system table to check this. Can someone help me with that?


texts: Returning Result Based on 2 conditions from one Table

texts:
Problem Statement: I have a table that holds three pieces of information (Number, Id, Type). Let’s call it table A:

╔══════╦════╦══════╗
║Number║ Id ║Type  ║
╠══════╬════╬══════╣
║ 1234 ║1111║Test 1║
║ 1234 ║2222║Test 1║
║ 1234 ║3333║Test 2║
║ 1234 ║4444║Test 2║
╚══════╩════╩══════╝

I have another table that holds 4 pieces of information (Number, Test1, Test2, ID). Let’s call it table B:

╔══════╦═════╦══════╦══════════════════╗
║Number║Test1║Test 2║ ID               ║
╠══════╬═════╬══════╬══════════════════╣
║ 1234 ║1111 ║      ║a1v1b0000006lmMAAQ║
║ 1234 ║2222 ║      ║a1v1b0000006lmNAAQ║
║ 1234 ║     ║3333  ║a1vf1000000qvTYAAY║
║ 1234 ║     ║4444  ║a1vf1000000qvTiAAI║
╚══════╩═════╩══════╩══════════════════╝

What I would like to do is, based on Number and Type from Table A, to search the Table B and return the corresponding ID.

The desired outcome is as follows:

╔══════╦════╦══════╦══════════════════╗
║Number║ ID ║Type  ║ ID               ║
╠══════╬════╬══════╬══════════════════╣
║ 1234 ║1111║Test 1║a1v1b0000006lmMAAQ║
║ 1234 ║2222║Test 1║a1v1b0000006lmNAAQ║
║ 1234 ║3333║Test 2║a1vf1000000qvTYAAY║
║ 1234 ║4444║Test 2║a1vf1000000qvTiAAI║
╚══════╩════╩══════╩══════════════════╝

NB: I have tried to use a decode but reading up on it, I am getting mixed signals whether this is the correct approach.


texts: Mysql multi-master active-active replication : replication doesn't happen for all slaves

texts:
so i created a multi-master replication with bellow servers two for master and two for slave.

hostname : master-1
ip : 192.168.120.107

hostname : master-2
ip : 192.168.120.111

hostname : slave-1
ip : 192.168.120.117

hostname : slave-2
ip : 192.168.120.118

the two masters (master-1,master-2) are deployed as active-active replication and works well and bellow is the configuration files /etc/mysql/my.cnf :

master-1 :

[mysqld]
#
# * Basic Settings
#
server-id       = 1
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

master-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 2
user            = root
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
log-bin         = master-bin
log-bin-index   = master-bin.index
relay-log       = relay-log-bin
relay-log-index = relay-log-bin.index
skip-external-locking

masters work well. the two slaves are connected to the masters like this :
slave-1 is the master-1 's slave and slave-2 is the master-2 's slave. and these are the configuration files :

slave-1 :

[mysqld]
#
# * Basic Settings
#
server-id       = 3
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

slave-2 :

[mysqld]
#
# * Basic Settings
#
server-id       = 4
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
relay-log       = slave-relay-bin
relay-log-index = slave-relay-bin.index
skip-external-locking

when a master make changes to the database his slaves changes two, it mean works well for example when master-1 changes the database the changes will be replicated to master-2 and slave-1 and changes will be done in those as well. my problem is i need the changes happen in all slaves for example when master-1 changes the database ,slave-2 change his database like master-2 and slave-1. how can i make this happen?


texts: Copy tables to another SQL Server keeping the identity property

texts:
Objective
I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing.
Problem
The copied tables all have the identity property on their ID column set to 'no'. As far as I understand, that setting allows the column to auto-increment (I get an error when I try to insert values without giving a value for ID).
The tables on the server all have this setting set to 'yes' and now when I try to set it to 'yes' on the local copies, my software (SQL Server Management Studio) asks me to drop and re-create the whole table and therefore losing all the data.
What I've tried
The following solutions all copy the tables with their data, but don't copy the identity property:

Copy the tables each with a query (using linked servers): SELECT * INTO NEW_DB.dbo.tblCopy FROM OPENQUERY([server], 'select * from OLD_DB.dbo.tblData')
Import the data with Task -> Import Data... (Using this tutorial)

I tried this both with "Enable identity insert" checked and unchecked

Create the table on the destination database and simply copy/paste the data into it (this did not work at all - no data was inserted)

Notes

I would rather not rely on third-party tools. I am using SQL Server Management Studio 2018.
I am aware of this question on Stack Overflow which talks about the exact same problem. But as already stated above, enabling "enable identity insert" did not copy the identity property.
I am also aware of this other question asked on Server Fault, both of its answers are about a third-party tool though, which I do not want to use.
I only have read permission on the source server/database.
The tables contain up to 6,000 rows.
The source database contains several more tables, I only want to copy 10 specific tables.


texts: Modify an Oracle Dataguard destination database

texts:
I'd like to set up some Oracle triggers to take action after rows are inserted, but I can't do this on our production database because it's controlled by a vendor. One alternative I was looking at is doing Oracle Dataguard replication to a secondary database somewhere, and setting up triggers there to take my action. However, I've gotten the impression that I can't modify the Dataguard destination in any way, even if my triggers won't have impact on the data at all (they're all AFTER triggers).

Can I use dataguard in this way? If I can't, are there are other alternatives to Dataguard, like maybe Oracle streams? Some other type of replication?


texts: How to Create Aggregate function in SQL Server 2008

texts:
My requirement is to create concatenate function so I am trying to create Aggregate function as shown

CREATE AGGREGATE [dbo].[Concatenate]
    (@value [NVARCHAR](MAX))
RETURNS[NVARCHAR](MAX)
EXTERNAL NAME [Test].[Test_SQL.Concatenate]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Concatenate.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'28' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

But it showing error as:

Assembly 'Test' was not found in the SQL catalog of database 'Test'.

How to create Assembly.


texts: How to export Sql Server DB schema as an ALTER statements?

texts:
I have a 2 months old DB & I want to fetch new Schema for that DB without deleting the data in it using ALTER statements. Is it possible?
Currently I am taking a backup of table data of Old DB in some temporary DB & then replacing the Old DB with the new one & Inserting the backup data from the temporary DB. What could be the best solution to migrate the Schema?


texts: How to lock a sql login after N unsuccessful login attempts

texts:
Consider I have login called sql_login. Can I lock sql_login login after 5 unsuccessful login attempts.

When we create a login we can see there is option called password policy check the image. But there is nothing mentioned about locking password

Is there a option in Sql Server to lock a login after N unsuccessful login attempts


texts: What locking can I expect with DBCC CHECKIDENT(RESEED)

texts:
I'm working on a project that has hit a bit of a snag: I'm replacing some replication with a home built system so that if replication goes down we don't blow our SLAs with our customers and shell out hundreds of thousands of dollars.

In my research, I learned that inserting records alongside identity values can cause the next identity for the table to be reset, which is something I want to avoid due to key range management issues.

It then seems like the obvious solution would be something like this:

DECLARE @Identity VARCHAR(15), @SQL VARCHAR(MAX)
SELECT @Identity = IDENT_CURRENT( 'dbo.My_Table' )

SET IDENTITY_INSERT dbo.My_Table ON
-- Do my inserts here

SET @SQL = 'DBCC CHECKIDENT ( ''dbo.My_Table'', RESEED,' +  @Identity + ')'
EXEC (@SQL)

Would I be killing myself with locking if this happens on a highly used table?
Alternatively, has anyone figured out a way to insert identities like replication does without causing the identity value to get reset?


texts: Is a weekly rebuild of indexes a good idea?

texts:
We had a SQL expert suggest rebuilding our table indexes weekly.  We're currently doing this Saturday night and it causes lots of timeouts during the rebuild process.  I'd like to avoid the timeouts if possible, so is it a good idea to do the weekly index rebuild?

We're running MS SQL server 2014.

UPDATE
We get this error during the rebuild "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

Thanks!


texts:
We are using MySQL Community version on Windows and are trying to secure it as best we can.  Assuming that changing the version (for example purchasing the Enterprise edition) is not possible in our environment, I would love to hear how others are doing things such as:

Auditing
Alerts
Monitoring
Password rules
Account disabling after inactive for X days
Backups
Integrity checking
Encryption
Steganography detection

For example, we are using the general log for our cheap "auditing".  Maybe not the best solution, but something is better than nothing.  Any free tools out there, or maybe homegrown scripts anyone would like to suggest?


texts: How to eliminate for loop in a nested aggregate problem

texts:
I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be eliminated and if so how? I am using PostgreSQL version 11.1.

The input (JSON): [{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]

The expected output: {{a="1",b="2"},{c="3",d="4",e=""}}

My plpgsql code:

CREATE OR REPLACE FUNCTION parse(_in JSONB)
RETURNS TEXT LANGUAGE plpgsql STABLE AS $BODY$
DECLARE
    _out TEXT;
    _parts TEXT[];
    _row RECORD;
BEGIN
    FOR _row IN (
        SELECT q1.value, ROW_NUMBER() OVER () AS index
            FROM JSONB_ARRAY_ELEMENTS(_in) q1
    ) LOOP
        _parts[_row.index] := STRING_AGG(CONCAT(q2.key, '=', q2.value::TEXT), ',')
            FROM JSONB_EACH(_row.value) q2;
    END LOOP;

    SELECT CONCAT(
            '{{',
            COALESCE(STRING_AGG(q, '},{'), ''),
            '}}'
        )
        INTO _out
        FROM UNNEST(_parts) q;

    RETURN _out;
END
$BODY$;

Run:

my_db=*# select parse('[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]');
               parse
------------------------------------
 {{a="1",b="2"},{c="3",d="4",e=""}}
(1 row)

UPDATE: In response to request for additional requirements:

The input is an actual json(b) object, not a string representation.
No key or value will ever have a double quote char in it (not even an escaped one.)
The values are always of type string though they might be empty.
There can be a LOT of elements in the JSON.


texts: credentials file for running mysql related jobs via script?

texts:
Is there a method to point mysql to a credentials file instead of entering account passwords directly in scripts?

Example:

mysqldump --opt --credfile /etc/mysql_creds mydatabase > output.sql

/etc/mysql_creds

username=myuser
password=ToPsEcReT


texts: MSSQL - Session has Open Transaction without a Transaction?

texts:
I'm researching an open transaction issue in relation to database updates from processing transactional MSMQ messages (see code-side here). These are enlisted in a distributed transaction and everything I've seen points to the transactions being completed and committed. However, when I check dm_exec_sessions, the connection has an open_transaction_count = 1. When I join out to dm_tran_database_transactions, I can't find a related transaction:

SELECT s.open_transaction_count, COUNT(t.transaction_id)
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_tran_session_transactions st on st.session_id = s.session_id
LEFT JOIN sys.dm_tran_database_transactions t ON t.transaction_id = st.transaction_id
GROUP BY s.open_transaction_count

DBCC OPENTRAN also shows no open transactions.

Anyone know what's going on as to why SQL is reporting an open transaction on the connection?


texts: Pre-staging data causes execution plan cost to skyrocket

texts:
I have a troublesome query that we are trying to tune.  One of our first thoughts was to take a portion of a larger execution plan and store those results to an intermediate temp table and then perform the other operations.

What I'm observing is that, when we prestage the data into a temp table, the execution plan cost goes through the roof (22 -> 1.1k).  Now, this has the benefit of allowing the plan to go parallel, which reduced execution time by 20% but that isn't worth the much higher CPU usage per execution in our case.

We are using SQL Server 2016 SP2 with the legacy CE on.

Original plan (Cost ~20):

https://www.brentozar.com/pastetheplan/?id=ry-QGnkCM

Original SQL:

WITH Object1(Column1, Column2, Column3, Column4, Column5, Column6)
AS
(
    SELECT  Object2.Column1,
            Object2.Column2,
            Object3.Column3,
            Object3.Column4,
            Object3.Column5,
            Object3.Column6
    FROM Object4 AS Object5
    INNER JOIN Object6 AS Object2 ON Object2.Column2 = Object5.Column2 AND Object2.Column7 = 0
    INNER JOIN Object7 AS Object8 ON Object8.Column8 = Object2.Column9 AND Object8.Column7 = 0
    INNER JOIN Object9 AS Object3 ON Object3.Column10 = Object8.Column11 AND Object3.Column7 = 0
    INNER JOIN Object10 AS Object11 ON Object2.Column1 = Object11.Column1
    WHERE   Object8.Column12 IS NULL AND
            Object8.Column13 = Object5.Column13 AND
            Object3.Column3 = Object5.Column3 AND
            Object11.Column14 = Variable1
)
insert Object12
SELECT  Object13.Column2,
        Object13.Column3,
        MIN(Object13.Column4) AS Column15,
        MAX(Object13.Column4) AS Column16,
        COUNT(DISTINCT (CASE WHEN Object13.Column5 = 1 THEN Object13.Column1 END)) AS Column17,
        COUNT(DISTINCT (CASE WHEN Object13.Column6 = 0 THEN Object13.Column1 END)) AS Column18,
        COUNT(DISTINCT Object13.Column1) AS Column19
FROM Object1 AS Object13
GROUP BY Object13.Column2, Object13.Column3 OPTION (RECOMPILE)

New plan (with area highlighted in blue above is pre-staged into a temp table - Cost ~1.1k):

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

New SQL:

SELECT  Object1.Column1,
        Object1.Column2,
        MIN(Object2.Column3) AS Column4,
        MAX(Object2.Column3) AS Column5,
        COUNT(DISTINCT (CASE WHEN Object2.Column6 = 1 THEN Object1.Column7 END)) AS Column8,
        COUNT(DISTINCT (CASE WHEN Object2.Column9 = 0 THEN Object1.Column7 END)) AS Column10,
        COUNT(DISTINCT Object1.Column7) AS Column11
from Object3 Object1
join Object4 Object2 on Object2.Column12 = Object1.Column13 and Object2.Column2 = Object1.Column2
where Object2.Column14 = 0
GROUP BY Object1.Column1, Object1.Column2 OPTION (RECOMPILE)

Can someone help us understand why the new plan would have such a larger cost?  I'll be happy to provide additional information regarding tables/indexes underneath, if needed.

In the case of the original plan, we do realize that it's doing an insert instead of select.  Even so, the select shouldn't (in our minds) be that much more costly.

This is the actual execution plan.  It's a concern because, due to the immensely higher plan cost, it goes parallel.  Therefore using higher CPU.  Also, we're just curious as to why the plan cost goes up that much for something like pre-staging the data, which usually will get you close to, if not better, than the original cost.

The temp table is indexed in the second query as a composite clustered PK on Object1.Column13 and Object1.Column2.  This matches the columns (and order) of Object4.  Adding a MAXDOP hint is an option, but this is also an academic exercise of 'why in the world does cost go up that much'?

Adding OPTION (ORDER GROUP) to the second query results in no change, same operators/costs.

NOTES:

Object9 in the first query is the same object as Object4 in the second.


texts:
I have two tables on which I perform some bulk inserts:

key: key_id (pk), key_name
related_key: related_key_id (pk), key_id (fk)

First INSERT:

values_data = "($$key_1$$), ($$key_2$$)"

INSERT INTO key (key_name) VALUES values_data

Sec INSERT:

values_data = "(1, `the id of the first value inserted in key`)
             , (1, `the id of the sec value inserted in key`)"

INSERT INTO related_key (related_key_id, key_id) VALUES values_data

I don't know how to do the second INSERT because I don't know how to get those ids for the rows inserted in the key table.

Or is there a better way of doing this?


texts:
How to get this result

expected result

CodeCompany      fechaActual    Clave  Usuario  Depto   entrada        salida       sumaHoras
2300                201712       307    USER1   TTT    06:00:04       14:04:12         8

This is my Query

DECLARE @Hoy DATETIME
DECLARE @Hoy1 DATETIME
DECLARE @Hoy2 DATETIME

SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))
SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'

select c.CodeCompany,
       CONVERT(char(6), @Hoy, 112) AS fechaActual,
       a.idClave,
       c.nombre + ' ' + c.apellidos as Nombre,
       d.Descripcion as Depto,
       CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
       CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida,
       DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia
from   tblasistencia a
JOIN   tblpersonal c
ON     a.idClave = c.idClave
JOIN   tblDepto d
ON     c.fkDepto = d.idDepto
where  fechaEntrada BETWEEN CONVERT(DATETIME, @Hoy1,103)
                    and CONVERT(DATETIME, @Hoy2,103)
and    fkStatus != 1

unexpected result

CodeCompany      fechaActual   Clave   Usuario   Depto     entrada      salida   sumaHoras

2300             201712        10120   USER1      TTT    00:00:00      23:59:59     8

The result is unexpected because not show the hour entry and exit time


texts: Galera - why can't I use port 4567 for replication?

texts:
I have two Galera clusters in two different datacenters.
I want to replicate cluster c1(using c1-1 node as replication master) to cluster c2 (using c2-1 node as replication slave). Everything works well if I use port 3306 on c2-1for MASTER_PORT instead of the default port for replication 4567.
If I switch from port 3306 to 4567 for MASTER_PORT I get this error when I run SHOW SLAVE STATUS\G on c2-1:
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master 'repl@x.x.x.x:4567' - retry-time: 60  maximum-retries: 86400  message: Lost connection to server at 'reading initial communication packet', system error: 0 "Internal error/check (Not system error)"

This is what I get when I run lsof -i:3306 on c1-1:
root@c1-1:~# lsof -i:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 1402 mysql   31u  IPv4  25689      0t0  TCP *:mysql (LISTEN)

And this is what i get when i run lsof -i:4567 on c1-1 (same node):
root@c1-1:~# lsof -i:4567
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 1402 mysql    8u  IPv4  25683      0t0  TCP *:4567 (LISTEN)

Also I allowed all traffic (using ufw) on all those ports.
If I run netcat x.x.x.x 3306 (x.x.x.x being c1-1's ip) on c2-1 I get this:
root@c2-1:~# netcat x.x.x.x 3306
q
5.5.5-10.5.9-MariaDB-1:10.5.9+maria~focal-log,)XbbFt8Z��-��:Srf(

And if I run netcat x.x.x.x 4567 (x.x.x.x being c1-1's ip) on `c2-1 I get this:
root@c2-1:~# netcat x.x.x.x 4567
$�-��\�цI�-Vk8�$

Why can't I use port 4567? Is it not using the right socket? If so what can be done?
Thank you!


texts: desing the database for survey app with various types of answers

texts:
I have to create a survey app for which answers should be stored in the database. I created an html form containing some questions (about 30) with various types of answers (check boxes, radio buttons, auto-complete, etc). Now, I am going to design my database.

First, I thought of only 3 tables : Questions, Answers, Users . However after I searched some similar questions such as :Database design for a survey, db design - survey/quiz with questions and answers [closed] I found some other design models e.g creating 4 tables : Questions, PossibleAnswers, UsersAnswers and Users. Now, I have these questions:

Should I create a survey table too ? (at this moment,there is only
one survey for my app, but not sure about the future.)
Which design model would be better in my case?
In case of using the first model, should I create specific column for
each types of answer? (for instance, consider this question: which
are your favorite movie genres? and it has more than 10 possible
answers! then if I want to create a column for each possible answers
of all questions, it would be lots of columns!)
In case of the second model, is it correct? (this is what I
understood from other similar questions, but still I am not sure if
it is useful form me and if yes, what is the advantage of this
model?)

Questions: questionId, question

PossibleAnswers: possibleanswerId, questionId, possibleanswer

UsersAnswers: useranswerId, possibleanswerId, userId, answer

Users: userId, username, password

I really appreciate all your ideas and suggestions


texts:
I am working on migrating a number of reports from an SSRS 2012 install to a SSRS 2019 install.  These are all pretty simple and straight forward reports, built in microsoft report builder.  A number of the reports use an oracle data source, these reports are giving me a heck of a time.  The following outlines the steps I have taken related to the oracle reports

I installed ssrs 2019, set it up per our team standards
Installed the oracle 19c administrator client
Installed the latest ODP.NET client (18.3)
Copied the tnsnames.ora file to both the admin client and the ODP.net client homes
Ensured the TNS_ADMIN environment variable was set
I made sure there was an entry in the rsreportserver.config file for oracle, which there is
Ensured I was able to resolve the oracle service name, by testing and making sure tnsping was able to resolve the name, and it was successful

I am still getting the following error, when trying to view the report from the ssrs server, or when trying to test the data source connection in the report builder.

An error has occurred during report processing. (rsProcessingAborted)
An attempt has been made to use a data extension 'ORACLE' that is
either not registered for this report server or is not supported in
this edition of Reporting Services. (rsDataExtensionNotFound)

I have run through all the troubleshooting steps that I can think of, and I am not making any progress.


texts: How to show multiple record if has top 1

texts:
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-05.

Table 1

Key 2          Name

78     Test 1
222    Test 2
824    Test 3
1980   Test 4
3747   Test 5

Please see image for table 2

select top 1 (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc

my query only getting one record i want to get all the record on key 2


texts: Does it make sense to have CASE .. END in an ORDER BY?

texts:
Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice?

It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.

Let's say we have an application that lists pre-owned cars (à la CraigsList). The list of cars can be sorted by price or colour. we have one function that, given a certain amount of parameters (let's say price-range, colour, and sorting criteria for instance) returns a set of records with the results.

To make it concrete, let's assume cars are all in the following table:

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

The table would have indices for most columns...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

And have some commodity enumerations:

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

... and some sample data

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES
    ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
    ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
    ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
    ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
    ('Fiat',   '500',      2010,  7500.00, 'blueish'),
    ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'),
    ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'),
    ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

The kind of queries we are going to make are of the style:

SELECT
    make, model, year, euro_price, colour
FROM
    cars.cars
WHERE
    euro_price between 7500 and 9500
ORDER BY
    colour ;

We would like to have queries of this style in a function:

CREATE or REPLACE FUNCTION get_car_list
   (IN _colour    text,
    IN _min_price numeric,
    IN _max_price numeric,
    IN _sorting_criterium car_sorting_criteria)
RETURNS record AS
$BODY$
      SELECT
          make, model, year, euro_price, colour
      FROM
          cars
      WHERE
           euro_price between _min_price and _max_price
           AND colour = _colour
      ORDER BY
          CASE WHEN _sorting_criterium = 'colour' THEN
            colour
          END,
          CASE WHEN _sorting_criterium = 'price' THEN
            euro_price
          END
$BODY$
LANGUAGE SQL ;

Instead of this approach, the SQL in this function could be generated dynamically (in PL/pgSQL) as a string, and then EXECUTEd.

We can sense a few limitations, advantages and disadvantages with either approach:

Within a function, finding which is the query plan for a certain statement is difficult (if possible at all). Yet we tend to use functions mostly when going to use something often enough.
Errors in static SQL will be caught (mostly) when the function is compiled or when it is first called.
Errors in dynamic SQL will only be caught (moslty) after the function is compiled, and all the execution paths have been checked (i.e.: the number of tests to perform to the function can be really high).
A parametric query like the one exposed will probably be less efficient than a dynamically generated one; yet the executor will have a harder job parsing / making query tree / deciding every single time (which could impact efficiency in the opposite direction).

Question:

How to "get the best of both worlds" (if possible)? [Efficiency + Compiler checks + Debugging easily + Optimizing easily]

NOTE: this is to be run on PostgreSQL 9.6.


texts: Postgresql/AWS RDS Problem with high write IOPS

texts:
I am currently hosting a ~10GB database in amazon RDS running postgresql. I have 100GB of space allocated using General Purpose SSDs which means that I get 300IOPS to be split between read and writes.

At steady state, my database receives around 8.3 update statements per second which will update a timestamp on up to 100 rows each, and usually above 95 each. There are 6 columns in this table, each of which is indexed as they are searched quite often for other reasons. There should be around 830 row updates per second, and I am seeing around 800-900 write IOPS using the aws monitoring utilities.

However, each of these write operation is very small, and the total bandwidth being used is ~15kB. A write operation can be up to 8kB for postgres(correct me if Im wrong), so this means that is not nearly the amount of bandwidth that I have available, and as such I am being severely bottlenecked by the IOPS allocated to me.

High precision is not necessary for these updates, so I was wondering if there was some way to combine many of the updates together such that fewer IOPS are consumed? I've tried turning synchronous_commit off as well as playing around with commit_delay and commit_siblings to no avail.

So I am wondering if anyone has suggestions to make this possible? What would be the ideal values of commit_delay(higher/lower) and commit_siblings(higher/lower) to remedy this situation if that is the solution. Otherwise is there anything else I can do?

Let me know if you need any more info, thanks! I am very new, so feel free to correct me on anything.


texts: Transactional publication and database mirroring considerations

texts:
I've recently taken over a project, and with it comes 2 backends. 1st is the production services backend and the 2nd is a reporting service backend. Both of which are 2008r2 instances.

The production database is mirrored to a Dr site. It is high safety no witness. The principal database has a transactional publication setup that serves to the reporting instances pull subscription.

I'm trying to better understand under what circumstances do I have to remove a mirror, pause/resume a mirror and delete publication and/or subscription.

Here are some use cases I'm curious about. Generally because of major and minor service releases

New or updated schemas.
New tables/columns.
New or modified mappings and permissions.

And I'm sure I'm missing other stuff.

Any help would be appreciated.


texts:
I was trying my hands on Clusterless AlwaysON for SQL Server 2017 and missed to keep the Cluster Type as None while configuring.

Right now it is set to External and I can't test failover.

Is there any possibility to change the Cluster Type to 'None', or do I need to re-configure?


texts: Why is the First Transaction Log backup of the day huge

texts:
I noticed that the first transaction log backup of the day, at 0700 is like 3GB. We run a full backup each night at 22:00. We are a 9-5 shop so there is no real activity overnight. We take TLog backups hourly during the day and they average around 30 MB. How can I find out why this first log backup is so big?
Thanks!


texts: Can I defer a configuration change until the next time SQL Server is restarted?

texts:
Let's say that I want to change a SQL configuration setting like MAXDOP.  I know that changing this on an active instance will severely impact performance, as it will clear all of the cached query plans. Doing this while the instance is running is simply not an option.

Is there some way to modify the value now, but to "defer" the actual change until the next time SQL server is restarted?

What I'm looking for is an option like Oracle's "scope=spfile" for parameter changes.  Does such an option exist in SQL Server?


texts:
What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of these I have been told are private databases and thus will only allow my application to how the information as long as they are on the same internet connection. I have also been told that port forwarding my database isn't safe either.

In this case what is the best approach to making my database public or what other source should I use to make my application accessible to world wide users.


texts: Append elements to array only if the element does't exist

texts:
WITH upd AS (
  UPDATE univ.products
  SET source = source::int[] || _source
  WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source))
  RETURNING id
)

, ins AS (
  INSERT INTO univ.products(project_id, product_id, source, tracked)
  SELECT _project_id, _product_id, ARRAY[_source], _tracked
  WHERE NOT EXISTS (SELECT 1 FROM upd)
  RETURNING id
 )

SELECT id
FROM   upd NATURAL FULL OUTER JOIN ins
INTO   _project_product_id;

My situation, I have this function which contains all the above and more, what I am trying to avoid is duplicating the project/products pair, but in the same time update the sources by appending new sources. Now the sources for each product/project pair need to be unique, thats why I've added NOT(_source = ANY(source)), but if this is true the select 1 from up will not return anything so will try to insert the records into the db to avoid this I've added the below exception.

EXCEPTION WHEN UNIQUE_VIOLATION THEN
    EXIT;
END;

This is almost doing what I want, but is not returning any id is there a better way of doing this?

Also I have noticed that if I insert a record, then I insert the same record with a different source(the row id will be 1), and then I insert a different record the row id will be 3 instead of 2;

[EDIT] I've solved my problem by doing:

SET source = CASE WHEN NOT(source::int[] @> ARRAY[_source]) THEN source::int[] || _source ELSE source END

Is there a better solution? This one seems a bit slow.


texts: How to detect whether MySQL server is running as Amazon RDS

texts:
I am writing an application wherein I want to detect whether MySQL server is running as Amazon RDS instance?
Can somebody help me out how to detect it?

auto_increment_increment1
auto_increment_offset1
autocommitON
automatic_sp_privilegesON
back_log50
basedir/rdsdbbin/mysql-5.1.45.R1/
big_tablesOFF
binlog_cache_size32768
binlog_direct_non_transactional_updatesOFF
binlog_formatMIXED
bulk_insert_buffer_size8388608
character_set_clientutf8
character_set_connectionutf8
character_set_databaselatin1
character_set_filesystembinary
character_set_resultsutf8
character_set_serverlatin1
character_set_systemutf8
character_sets_dir/rdsdbbin/mysql-5.1.45.R1/share/mysql/charsets/
collation_connectionutf8_general_ci
collation_databaselatin1_swedish_ci
collation_serverlatin1_swedish_ci
completion_type0
concurrent_insert1
connect_timeout10
datadir/rdsdbdata/db/
date_format%Y-%m-%d
datetime_format%Y-%m-%d%H:%i:%s
default_week_format0
delay_key_writeON
delayed_insert_limit100
delayed_insert_timeout300
delayed_queue_size1000
div_precision_increment4
engine_condition_pushdownON
error_count0
event_schedulerOFF
expire_logs_days0
flushOFF
flush_time0
foreign_key_checksON
ft_boolean_syntax+-><()~*:""&|
ft_max_word_len84
ft_min_word_len4
ft_query_expansion_limit20
ft_stopword_file(built-in)
general_logOFF
general_log_file/rdsdbdata/log/mysql-3306.log
group_concat_max_len1024
have_community_featuresYES
have_compressYES
have_cryptYES
have_csvYES
have_dynamic_loadingYES
have_geometryYES
have_innodbYES
have_ndbclusterNO
have_opensslYES
have_partitioningYES
have_query_cacheYES
have_rtree_keysYES
have_sslYES
have_symlinkYES
hostnameip-10-28-182-30
identity0
ignore_builtin_innodbOFF
init_connect
init_file
init_slave
innodb_adaptive_hash_indexON
innodb_additional_mem_pool_size2097152
innodb_autoextend_increment8
innodb_autoinc_lock_mode1
innodb_buffer_pool_size1179648000
innodb_checksumsON
innodb_commit_concurrency0
innodb_concurrency_tickets500
innodb_data_file_pathibdata1:10M:autoextend


texts:
I'm planning to do an index rebuild operation for over 30+ indexes. Some index rebuild require almost >30min because the Table Record is around 60,000,000. Command used for this table is

ALTER INDEX [T_MAIL_RCVLIST_IDX1] ON [dbo].[T_MAIL_RCVLIST] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

However, I would have to run this command for every single index, which is a big hassle and I decided to use a cursor and run it at one go. But I'm worried there could be an unexpected issue and my boss also warned me of this and suggested I run each index build individually. Could there be an issue? If yes, what kind and how would I fix this?

Here is the cursor index build query.

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor;


texts: How to include categories for which there are no values in an ssrs chart

texts:
I've got a query that gets some data points for the last 6 months (prior to current), akin to this:

SELECT  [DataPoint], [When]
FROM    Table
WHERE
        [When] >= DATEADD(MONTH, -7, @CurrentMonth)
        [When] < @CurrentMonth

And my ssrs chart has a category like this:

=Format(DateAdd(DateInterval.Month, DateDiff(DateInterval.Month, CDate("1900/01/01"), Fields!When.Value), CDate("1900/01/01")), "MMM")

The horizontal axis is configured thusly:

Number: Category = Date, Type = Jan 2000

and I've tried setting:

Axis Options: Interval = 1, Interval type = Months

... but it doesn't help.

It works great when there are data points within each month, but I have some data sets that only have data for some months (ie. Dec/Jan) and it will only show those months.

Is there any way to get it to show the full 6 months of "data" (or lack thereof) without dumping empty data in the dataset?


texts: Innodb switch innodb_file_per_table to OFF

texts:
I want to switch off the inno variable: innodb_file_per_table
I found that this affects only new tables, how to apply this change also to old tables? I tried:

Alter table (ALTER TABLE xxx ENGINES=innodb), but it doesn't work

I may dump everything and restore the entire DB but if possibile I want to avoid this procedure.


texts: Xtrabackup Incremental Backups Failing After Enabling Change Page Tracking on MySQL 5.7

texts:
A week back I'd enabled the parameter innodb_track_changed_pages=ON on one of our Percona MySQL 5.7.10 database instances that we use as a Data Warehouse. The OS is Ubuntu 16.04. So far the incremental backups generated using innobackupex (Xtrabackup 2.4) have been running fine until now when I found them to be generating this error:

200612 22:11:15 >> log scanned up to (95282574388766)
200612 22:11:16 >> log scanned up to (95282574406126)
200612 22:11:17 >> log scanned up to (95282574423081)
200612 22:11:18 >> log scanned up to (95282574440020)
200612 22:11:19 >> log scanned up to (95282574456959)
200612 22:11:20 >> log scanned up to (95282574473898)
200612 22:11:21 >> log scanned up to (95282574490837)
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: 950272 bytes should have been read. Only 0 bytes read. Retrying for the remaining bytes.
InnoDB: Retry attempts for reading partial data failed.
InnoDB: Tried to read 950272 bytes at offset 51259736064, but was only able to read 0
InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
InnoDB: Cannot continue operation.

I've tried taking incremental backups after that but everytime innobackupex gives me this same error. Is this parameter unsafe?


texts: Unable to attach SQL Server database with shared folder in docker on Linux

texts:
The problem I'm having is that my SQL Server 2017 Linux container is not able to read my database files. I guess it has something to do with permissions and I'm sure I've overlooked something but I can't fugure it out.

I have created a clone with the PowerShell module called dbaclone.
This module creates and attaches a differencing disk based on another VHD making it easier to provision large databases.

The databases are available using a partition access path which will look something like this

With in the folder, in example "AW2017-C1" is a folder called "Data" and "Log" containing the data and the log files respectively.

The permissions on the folder are "Everyone" to have "Full Control".

I have a virtual machine in VMWare Workstation which is a Linux VM. The Linux VM has Docker installed. I shared the clone folder in the VM

To make it easier to use I added the share to /etc/fstab to mount it automatically when the VM starts.

The permissions are all set that everyone should be able to read it

I have the following script to run the docker container

sudo docker run -e 'ACCEPT_EULA=Y' \
    -e 'SA_PASSWORD=Myp@ssw0rd' \
    -p 1433:1433 --name sql1 \
    -v /home/sander/databases:/databases \
    -v /home/sander/shares/dbaclone:/var/opt/mssql/data/dbaclone \
    -d mcr.microsoft.com/mssql/server:2017-latest

The docker container will mount the directory and make it accessible for the container.

The file permissions from within the container look correct

Everything looks ok, but when i run the following script

USE master;

CREATE DATABASE AdventureWorks2017
ON PRIMARY
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf'
       ),
       (
           FILENAME = '/var/opt/mssql/data/dbaclone/AW2017-C1/Log/AdventureWorks2017_log.ldf'
       )
FOR ATTACH;

This error comes back

  Msg 5120, Level 16, State 101, Line 3
  Unable to open the physical file "/var/opt/mssql/data/dbaclone/AW2017-C1/Data/AdventureWorks2017.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
  Msg 1802, Level 16, State 7, Line 3
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  Completion time: 2020-02-19T13:07:36.1675676+01:00

This obviously has something to do with permissions but it all looks good and maybe someone has else has some pointers how to get this fixed.


texts: Which are the downsides of not deleting the CDC records in change tables?

texts:
If I do something like this:

USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 52494800; -- (100 years)
GO

The records in the change tables will never (until year 2119) be deleted.

Also, I'm applying a script like this to move data from an old instance to a new one every time a new column is added to the tracked table, and I know that if there are many records, it can take a little bit of time to copy them to the new change table.

So, I'm planning to keep the change history forever on these change tables, and never purge the tables using the cleanup job. But the documentation says:

  Data that is deposited in change tables will grow unmanageably if you
  do not periodically and systematically prune the data

If CDC had been in plance since 2007 in my DB, the biggest common change table will contain ~20K rows in 12 years, the source table is replicated to another server, so, having said that:

What does 'grow unmanageably' means for me?
Will it have vey negative impact on the performance or storage in the DB? Maybe an impact on Replication performance?
Am I just fine doing CDC with no cleanup job at all? or
Is it a better alternative to copy the data to a reporting DB using a SSIS package or a job runing a stored procedure?

If so, is there a simple way to handle the creation of new columns in the destination table based on new columns added in the change tables?

Does the answer change if instead of 20K rows in one change table there are 1 million?


texts:
I am making software for patients in hospitals. I want to make an option that if a doctor has prescribed a drug that interacts with another drug that the patient already uses, a warning will appear explaining that the two drugs shouldn't be administered together.

There will be data in the database for every drug, with its composition and its interactions with other drugs.

How can I link between drug interactions and make that feature possible in my design?

For example:

patient: Albert Ricky
diagnosis: asthmatic patient
treatment: salbutamol inhalation

When salbutamol is written in the patient profile in the database, a warning message should appear:

  WARNING: that patient has been prescribed atenolol since 10-9-2010 as a hypertensive drug and that interacts with salbutamol, causing a severe rash and swollen edema.


texts: Create a second Replica set on same 3 nodes as another Replica set

texts: Can a second 3 node Replica set be installed on the same 3 nodes with another 3 node Replica set without interference?


texts:
This might seem like a stupid question.
Can i restore logs of one database to another existing database of same schema.?

I have a database d1 in Server A.I took backup of that and restored as database d1  in another database server B
Then in server B i merged all data from database d1 to database d2.
But i still want to apply transnational logs from database d1(in Server A) to database d2.Is that possible?

This might seem a generic/broad question?But it is a valid question as i have similar scenario of migrating legacy data and current production data into a new sql server after merging current production into legacy, and stay in synch with current production.

I thought of log shipping and tried as well.The issue was server A is in lower version(2008 R2) and server B in higher version(2016).So i have to run log shipping in no recovery mode and i can't do merging on the data in secondary in no recovery mode.If i stop log shipping and bring the database to recover mode and do merge ,i can't put it back to non recovery mode again and enable log shipping.

Please note i merge database d1 to d2 in server B.


texts: oracle - Change host to 0.0.0.0 in listener

texts:
Im using oracle 12c on windows server 2016. I want to make my oracle to listen to the port 1521 to 0.0.0.0

Here is my listner.ora file.

# listener.ora Network Configuration File: C:\app\bhuvi\virtual\product\12.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_ORCL_BHUVI =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\bhuvi\virtual\product\12.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\bhuvi\virtual\product\12.2.0\dbhome_2\bin\oraclr12.dll")
    )
  )

ORCL_BHUVI =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

You can see the HOST is 0.0.0.0, but when I start the listener, there it listening the port 1521 to localhost (my hostname is bhuvi-oracle)

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.2.0.1.0 - Production on 18-APR-2020 17:29:19

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.2.0.1.0 - Production
Start Date                18-APR-2020 17:29:12
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\bhuvi\virtual\product\12.2.0\dbhome_2\network\admin\listener.ora
Listener Log File         C:\app\bhuvi\virtual\diag\tnslsnr\bhuvi-oracle\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuvi-oracle)(PORT=1521)))
The listener supports no services
The command completed successfully

Can somebody help me to solve this issue?


texts: Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

texts:
I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want to add all. The id of the hub is used to search a intermediary table which then links to the content table if there is additional content for the property.

I suppose this is the normalized approach but I'm new to DB design. Is this at all the sensible approach to such a scenario?

Additional details

I don't know how to output DDLs or create these DB tables in Stack but will try to give a clearer example of what I'm currently doing:

Output:

output_id, output
Central: central_id, output_id, central_data
Central_2_A: central_id, property_A_id
Property_A: property_A_id, property_A
Central_2_B: central_id, property_B_id
Property_B: property_B_id, property_B
Central_2_C: central_id, property_C_id
Property_C: property_C_id, property_C

...and so on to F or G, or even more theoretically

knex('Central').select(
Central.central_id
Central.output_id
Central.central_data
Property_A.property_A_id
Property_A.property_A
Property_B.property_B_id
Property_B.property_B
Property_C.property_C_id
Property_C.property_C
)
.leftJoin('Central_2_A','Central_2_A.central_id','Central.central_id')
.leftJoin('Property_A','Property_A.property_A_id','Central_2_A.property_A_id')
.leftJoin('Central_2_B','Central_2_B.central_id','Central.central_id')
.leftJoin('Property_B','Property_B.property_B_id','Central_2_B.property_B_id')
.leftJoin('Central_2_C','Central_2_C.central_id','Central.central_id')
.leftJoin('Property_C','Property_C.property_C_id','Central_2_A.property_C_id')

As a side note, I'm using Knex.js.


texts:
I am working on documenting my databases, and I would like to create a list of all of the indexes in my database.  The reason I want to do this is so that I can track changes to my indexes overtime.  I currently have a spreadsheet with all of the indexes that have changed since i created that spreadsheet, but it doesn't have all of the indexes.  Instead of having to script out each index I would like to be able to just generate the list.  I played with the system views but I wasn't able to figure it out.

How can I generate a list of indexes and the create statement for each index?


texts: How to make a mysql statement for sent log

texts:
I have a table called "make_sent_log"

CREATE TABLE IF NOT EXISTS `make_sent_log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `transaction_id` varchar(255) NOT NULL,
    `total_base` int(11) NOT NULL,
    `delivered` int(11) NOT NULL,
    `pending` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `transaction_id` (`transaction_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

My goal is store delivery status of mobile numbers

For ex: sent bulk sms to 5 mobile Numbers

one unique transaction id will generated then I store it to the transaction_id column ex:- transaction_id = trans_1

I sent sms to 5 Mobile Numbers so I store it to the total_base column
ex:- total_base = 5

If sms sent successfully to the only 3 Mobiles, delivered = 3

So other numbers in pending stage so pending = 2

So My table looks like

INSERT INTO `make_sent_log` (`id`, `transaction_id`, `total_base`, `delivered`, `pending`) VALUES
(1, 'trans_1', 5, 3, 2);

My doubt is after some 1 or 2 Minutes, 2 pending numbers can be delivered so How to set

delivered = 5

And pending = 0

See below image


texts: Connecting from SSMS to an EC2 SQL Instance

texts:
I have an EC2 server running SQL2008 R2.

I opened the port 1433 and successfully tested with yougetsignal.com/tools/open-ports.
I have set the SQL Server to Accept Remote Connections.
I have set the Dynamics Port to be 1433 on the TCP/IP dialog under the Configuration Manager.
On Client Protocols I have Shared Memory 1st, TCP 2nd and Named Pipes 3rd.
The server SQL Browser services is running

On the server I normally connect from SSMS using MACHINENAME\SQL2008

QUESTION:
What is the right way to connect from my local machine? Is it "IP ADDRESS\SQL2008"?

I tried various ways and it does NOT work. So I must assume it's both the way I connect and some missing settings on the server. I get the following error:

A network-related or instance-specific error occurred while establishing
a connection to SQL Server. The server was not found or was not accessible.
Verify that the instance name is correct and that the SQL Server is configured
to allow remote connections. (provider: Named Pipes Provide, error: 5-Invalid
parameter(s) found) (Microsoft SQL Server, Errror: 87)

ANSWER
Thanks Mark. I was able to connect just by appending the ",1433" at the end.
So the right answer (at least for my problem) was  MACHINENAME\SQL2008,1433

Thanks again Mark !!!


texts:
I am using the SSIS package to populate data into the DimSuppliers table. However, this throws the following error whenever it reaches the Derived Column Transformation section.

  [Derived Column [2]] Error: SSIS Error Code
  DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Column" failed
  because error code 0xC0049067 occurred, and the error row disposition
  on "Derived Column.Outputs[Derived Column
  Output].Columns[SupplierFirstName]" specifies failure on error. An
  error occurred on the specified object of the specified component.
  There may be error messages posted before this with more information
  about the failure.

I am trying to split the ContactName in the source table into

(SupplierFirstName)

SUBSTRING(ContactName,1,FINDSTRING(ContactName,",",1) - 1)

(SupplierLastName)

 SUBSTRING(ContactName,FINDSTRING(ContactName,",",1) + 1,FINDSTRING(ContactName,",",2) - FINDSTRING(ContactName,",",1) - 1)

I also included this procedure to check the null on the insert values

USE [NorthWindDW]
GO

/****** Object:  StoredProcedure [dbo].[Insert_New_Suppliers]    Script Date: 09/01/2017 23:14:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Insert_New_Suppliers]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF NOT EXISTS
    (
        SELECT * FROM [dbo].DimSupplier
        WHERE SupplierKey = -1
    )

    BEGIN
    SET IDENTITY_INSERT [dbo].[DimSupplier] ON
    INSERT [dbo].[DimSupplier] (SupplierKey, SupplierId, SupplierFirstName, SupplierLastName, Position, SupplierCountry, PostCode, Telephone) VALUES
    (-1,-1,'Unknown','Unknown','Unknown', 'Unknown', 'Unknown','Unknowm');
    SET IDENTITY_INSERT [dbo].[DimSupplier] OFF
    END
END

GO

DimSupplier Table

Create Table [dbo].[DimSupplier]
(SupplierKey         INT IDENTITY NOT NULL,
 SupplierId          INT NOT NULL,
 SupplierFirstName   NVARCHAR(50) NOT NULL,
 SupplierLastName    NVARCHAR(50) NOT NULL,
 Position            NVARCHAR(50) NOT NULL,
 SupplierCountry     NVARCHAR(50) NOT NULL,
 PostCode            NVARCHAR(25) NOT NULL,
 Telephone           NVARCHAR(25) NOT NULL);
Go

Am I doing something wrong?


texts: Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

texts:
I've developed a timesheet recording system, that allows users to record:

A Period of time (9:00 to 12:00)
What they did during that time (DutyActivity: 1 hour report writing, 2 hours training)

When they submit a timesheet, the system also records their GeographyId, RoleId, CategoryId (single ids) and AttributeIds (multiple ids) - which is a snapshot of the user at that point in time.

This recorded timesheet information can then be queried for reports. In it's simplest form the system will return a list of UserIds with a SUM of the PeriodMinutes for each recorded DutyActivityId. Which would look like the following:

However what I also want to do is group by and return the recorded AttributeIds recorded with each timesheet (in the TimesheetAttribute table). However because there are multiple AttributeIds recorded for each timesheet, it's a little more complex.

My attempt at this creates the following report:

However the problem with this is that the SUM(PeriodMinutes) for each DutyCategoryId returned is now too high. It appears my SQL to include AttributeIds for each timesheet is causing the SUM calculation to be made incorrectly.

I've created a ZIP containing SQL for the database schema plus data, along with the SQL for both queries I've screenshot'd here: ZIP Timesheet example database

The information below is included in the ZIP, but I'm linking them here for ease of access:

Report on timesheets, without attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM
Timesheet AS T

LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

ORDER BY UserId ASC

Report on timesheets, with attempted attributes grouping

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TA.AttributeId * -1 AS AttributeId, /* Multiply AttributeId by -1 in order to create negative Ids, so that two PIVOT operations can be used */
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total

FROM
Timesheet AS T

LEFT JOIN
TimesheetAttribute AS TA ON
TA.TimesheetId = T.TimesheetId

LEFT JOIN
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
AttributeId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([-1],[-2],[-3],[-4],[-5])
)
AS PivotAttribute

ORDER BY UserId ASC


texts: MySQL cluster error: "Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock"

texts:
I am trying to configure MySQL cluster under three Windows 7 machines with disabled firewall, cluster information is like the following:

2 hosts with each running 1 data node

3rd host running management node and 2 MySQL Servers (MySQLD)

MySQL cluster application has been downloaded from http://dev.mysql.com/downloads/cluster/

On each machine I have created two folder inside C:\MySQL_Cluster\My_Cluster\  with the following names: conf and data

Data folder from the previous step has been copied from MySQL cluster application data folder.

the MySQL server application has been also configured in the environment variable

MySQL config file has contain the following:

    [ndbd default]
    noofreplicas=2
    datadir=C:\MySQL_Cluster\My_Cluster\data

    [ndbd]
    hostname=host_nod_1
    Nodeid=2

    [ndbd]
    hostname=host_nod_2
    Nodeid=3

    [ndb_mgmd]
    Nodeid=1
    hostname=host_management

    [mysqld]
    Nodeid=101
    hostname=host_management

    [mysqld]
    Nodeid=102
    hostname=host_management

My.cnf contain the following:

My1.cnf:
[mysqld]
ndb-nodeid=101
ndbcluster
datadir=C:\MySQL_Cluster\My_Cluster\data\mysqld_1
Port=1111
ndb-connectstring=host_management:1186

My2.cnf:

    [mysqld]
        ndb-nodeid=101
        ndbcluster
        datadir=C:\MySQL_Cluster\My_Cluster\data\mysqld_1
        Port=2222
        ndb-connectstring=host_management:1186

to start the management node I used the following command:

    C:\mysql\cluster\bin>ndb_mgmd --initial -f C:\MySQL_Cluster\My_Cluster\conf\config.ini --configdir=C:\MySQL_Cluster\My_Cluster\conf

to start data nodes I used the following command on each node:
   C:\mysql\cluster\bin>ndbd -c host_management:1186 --initial

to start API node (MySQL node) I used the following commands:

C:\>MySQL_Cluster\My_Cluster\data\mysqld_1> mysqld --install My1111 --defaults-file="C:\MySQL_Cluster\My_Cluster\conf\my1111.cnf"
***************************************************************************************************************
C:\>MySQL_Cluster\My_Cluster\data\mysqld_2> mysqld --install My2222 --defaults-file="C:\MySQL_Cluster\My_Cluster\conf\my2222.cnf"

when I checked management using the following command it retains the result:

C:\mysql\cluster\bin>ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @host_nod_1  (mysql-5.6.15 ndb-7.3.4, Nodegroup: 0, *)
id=3    @host_nod_2  (mysql-5.6.15 ndb-7.3.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @host_management  (mysql-5.6.15 ndb-7.3.4)

[mysqld(API)]   2 node(s)
id=4 (not connected, accepting connect from host_management)
id=5 (not connected, accepting connect from host_management)

if I connect for example to MySQL1 and create a table:

create table test.t1(id int) ENGINE=NDBCLUSTER;
I've got the following error and warnings:

ERROR 157 (HY000): Could not connect to storage engine
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire
global schema lock
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB
Warning | 1296 | Got error 4009 'Cluster Failure' from NDB

what did I miss in my configuration?
Edit adding hostname.err
2014-12-24 14:28:25 808 [Warning] NDB : Tables not available after 30 seconds.  Consider increasing --ndb-wait-setup value
2014-12-24 14:52:52 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
2014-12-24 14:55:22 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
2014-12-24 14:55:22 a34 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2014-12-24 14:55:49 808 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure


texts:
I'm trying to understand the order of precedence for logical operations and have the following code:

declare @T bit ='TRUE'
declare @F bit ='False'

print @T and @F

and it returns an error as

  Incorrect syntax near the keyword 'and'.

I replaced 'and' with '&', and the code works again. Why didn't the former code work?
I'm using SQL server.


texts:
I have noticed differences in executions between data pump imports lately, and I am trying to determine what is causing the differences.  I am using impdp to move data between databases, and remap the schemas.  It seems like I have seen two different things happen when I run the import in parallel, and I am not sure what is causing the differences.

Sometimes I see the job running and it is importing a bunch of tables side by side, other times the import process is only importing a few tables at a time, but using parallel inserts for each table.  Using Parallel inserts on just a few tables at a time seems to cut my overall run time substantially, but I can't figure out why it only runs like that sometimes and not others.


texts: Do unused columns in MySQL/MariaDB notably affect performance?

texts:
In the WordPress MySQL/MariaDB database there is a table with some columns that in my own uses of WordPress are never used.
The exact columns I am talking about are:
comment_status varchar(20)
ping_status varchar(20)
to_ping text
pinged text
comment_count bigint(20)

None of them is part of an index.
I wonder if removing those columns would make a notable performance difference.
Of course I cannot just remove those columns without modifying the WordPress core. My real goal behind the question is to gain feedback to know if it's worth the effort to participate in the WordPress development and find an alternative way to manage that information for websites still using those columns.


texts:
I am trying to update a jsonb key with a new value, but I cannot update all the keys at once.

My json structure is something like this:

[{
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"

    }
}]

I want to update the align key with a new value, but right now my query does not update all the elements, it only updates one element

this is my query so far:

with align_position as (
    select
    ('{' || index-1 || ',content,align}' )::text[] as path,
    id
    from section, jsonb_array_elements(entities) with ordinality arr(entity, index)
    where entity->'content'->>'align' = 'leftAlign'
)
update myTable set entities = jsonb_set(entities, align_position.path, '"left"', false) from align_position where section.id = align_position.id;

How can I make the query to update all the elements?

Any thoughts?


texts: UPDATE the records in oracle SQL with joinings and where exists query

texts:
I have the tables below and

tempaddresstable
account
premise
address

Here is the link of details(id of account = account_fk of premise)
(address_fk of premise = id of address)

So only goal is to update all the records  info from tempaddresstable to address table

I have tried the below query but I am getting error

UPDATE ADDRESS AD
SET AD.ADDRESS1 =
  (SELECT (B.TEMPADDRESS1)
   FROM tempAddressTable B,
        ADDRESS AD
   WHERE AD.ID =
       (SELECT (P.ADDRESS_FK)
        FROM ACCOUNT A
        LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
        LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
        LEFT JOIN ADDRESS AD ON AD.ID = P.ADDRESS_FK
        WHERE A.EXT_REF = B.TEMPEXTREF ))
WHERE EXISTS
    (SELECT 1
     FROM tempAddressTable B
     WHERE AD.ID =
         (SELECT DISTINCT max(P.ADDRESS_FK)
          FROM ACCOUNT A
          LEFT JOIN PREMISE P ON A.ID = P.ACCOUNT_FK
          LEFT JOIN tempAddressTable B ON A.EXT_REF = B.TEMPEXTREF
          WHERE A.EXT_REF = B.TEMPEXTREF ));

Error :

  SQL Error: ORA-01427: single-row subquery returns more than one row
  01427. 00000 -  "single-row subquery returns more than one row"
  *Cause:
  *Action:

Details of the table

tempaddresstable
account
premise
address

tempaddresstable

+------------+----------------------------+
| TEMPEXTREF | TEMPADDRESS1               |
+------------+----------------------------+
| 34         | 101 DRCSG117432RES RD TEST |
| 35         | 102 DRCSG117               |
| 36         | 100 DRCSG117432RES RD      |
+------------+----------------------------+

account

+---------+---------+
| ID      | EXT_REF |
+---------+---------+
| 1041261 | 34      |
| 1041262 | 35      |
| 1041263 | 36      |
+---------+---------+

premise

+---------+------------+------------+
| ID      | ACCOUNT_FK | ADDRESS_FK |
+---------+------------+------------+
| 1044610 | 1041261    | 1041502    |
| 1044611 | 1041262    | 1041503    |
| 1044612 | 1041263    | 1041504    |
+---------+------------+------------+

address

+---------+----------+
| ID      | ADDRESS1 |
+---------+----------+
| 1041502 | test     |
| 1041503 | test     |
| 1041504 | test     |
+---------+----------+


texts:
I need a help with the situation below:

In my table SQLServer 2012 have a field with xml values, I would like to make a select of the data in that field and show the result in the form columns.

<row>
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>

Thank you.


texts: Database design - Projects with multiple Clients but only specific client contacts

texts:
I am in the process of setting up a database that among other things will track projects, clients, contacts. I am however stuck on how to design one piece of relationship. The part of the database that relates to this question have the following layout.

Table: Project
ID           INT
ProjectNumber    NVARCHAR
...

Table: Client
ID            INT
Name          NVARCHAR
...

Table: ProjectClient
ID            INT
ClientID      INT (FK Client ID)
ProjectID         INT (FK Job ID)
PrimaryClient BIT (w unique index to prevent duplicates)

Table: ClientContact
ID
FirstName     NVARCHAR
LastName      NVARCHAR
ClientID      INT (FK Client ID)

Every project can have multiple clients (companies or individuals) and those can in turn have multiple contacts. My problem is that currently all contacts are assigned to the project where the client is assigned, I would like to be able to assign specific contacts to each project.

What would be the best way to accomplish that?


texts:
I want to store text differences in MySQL. Let's say I have a text, 500 characters or so, and then someone edits the text. I want to be able to reconstruct both the old and new text.

From a database viewpoint, I thought it would be stupid to store both texts if I could just store the difference. That would save a lot of space, especially if the edits are minute and the texts huge. But how do I do that?

I had some ideas which were really complex, such as storing only the old text, and when an edit occurs, replace the changed parts with a certain variable which then refers to another table. That other table could simply store the new and old value of that particular section of the text.

Is there a simple way to do this?


texts: Postgresql: What the default_statistics_target value really means?

texts:
Increasing the default_statistics_target value can make your database faster, specially after analyze....

Reading this article I see that https://discuss.pivotal.io/hc/en-us/articles/201581033-default-statistics-target-Explained

  (...)in short and in basic term, this
  parameter control the way the stats are collected , with value 1 being
  the least estimated/accurate statistics and the value 1000 being the
  most accurate statistics , obviously with the expense of time /
  resources ( CPU , memory etc ) / space . Normally the default value is
  sufficient to get a accurate plan , but if you have a complex data
  distribution / or a column is referenced in the query quite often ,
  then setting a higher value might help in getting a better statistics
  on the table and hence a better plan for the optimizer to execute.

It is a good explanation, but for example if I set default_statistics_target= 1000 what 1000 really means? It is 1000 kilobytes of statistics being generated? or maybe it is 1000 rows of the tables analyzed? Maybe it is 1000 columns? or perhaps 1000 seconds for each analyze...

So my question is how this number is really affecting the analyze or the query planner? Obvious I understand that default_statistics_target = 1000 will get more time than 100, for running analyze, and that 1000 will generate better statistics...