texts: I'm trying to add keys to an InnoDB table. I started with the small ones(~6.8GB) and I encountered a problem. After a while of processing it throws an 1114 error(table is full). I restarted the process of adding indexes and monitor everything. The problem seems to be that it is using OS's temp dir(running Windows). I have it installed on another drive that has plenty of space. The error is thrown when the space on C:/ approaches 0. Is there anything I could do to have it save its temp files on the same drive where MySQL is installed and not on C:/ ? Thank you


texts: MySQL Workbench doesn't show relationships between tables when using Reverse Engineering(Mac OS)

texts:
After running this and successfully creating the database, tables and both the primary and foreign keys, when I reverse engineer my database it won't show the relationships between tables. I'm using a MacBook Air(2017) Mojave 10.14.6, no dark mode and I've tried changing relationships notation from craw's foot to the others but still no luck.

create database test_asdf;
use test_asdf;

create table Department
(id int not null unique auto_increment primary key,
name varchar(30));

create table Professor
(id int not null unique auto_increment primary key,
name varchar(30),
adress varchar(50),
department_id int,
section_id int,
foreign key (department_id) references Department(id));

Now the thing is when I run the following the connections show:

create database test_asdf8;
use test_asdf8;

create table table1
(id int not null unique auto_increment primary key,
name varchar(30),
table2_id int);

create table table2
(id int not null unique auto_increment primary key,
name varchar(30),
new_id int,
foreign key (new_id) references table1(id));

Could someone please point out what I'm doing wrong here?


texts:
I have an application that almost continuously works with inserting or updating data. Since multiple requests are handled asynchronous I wrote my queries like below. I used an example based on SO, but that's not what I'm actually doing.

DECLARE @rows int;
INSERT INTO [user] ([username],[reputation])
SELECT [username],[reputation]
FROM (
    SELECT [username]=:user,[reputation]=:rep
) A
WHERE A.[username] NOT IN (
  SELECT [username]
  FROM [user]
);
SET @rows = @@ROWCOUNT;

IF (@rows=0) BEGIN
  UPDATE [user]
  SET [reputation]=:rep, [updated]=GetDate()
  WHERE [username]=:user
END;

This is passed in total to the database with PHP PDO. Because of the amount of data and other processing factors it's heavy on the (cheap) VPS it's running on. It's not really a problem if these processes run slow or get delayed, but on the other hand this data should be available via a website and then the queries on the data should be quick.

I was thinking about replicating parts of the processed data to a second server and running the website on that database. But I'm wondering how that would actually work with a query like above.

I'm guessing the UPDATE query will only be in the transaction log when @rows=0, so that won't be a problem.

But would the first part only send INSERT INTO [user] ([username],[reputation]) VALUES ('Hugo Delsing', '10k') or the entire query with the WHERE NOT IN () query?

Most of the time a user would exists, so if it only runs the new inserts that won't be a problem. But if it would run the entire query each time the benefits would be small.

Obviously I could wrap the first part up in another check if exists(select 1 from [user] where [username] = :user) to make sure it only runs when there is no user, but I'm wondering if that is necessary.

Also: Would replicating be the way to go or does MS SQL offer other/better solutions for something like this?


texts: High Level Permissions For an Application Owner

texts:
I'm the primary DBA for a 100-user data warehouse with approximately a dozen developers and analysts regularly contributing additions to the data model and codebase (mostly stored procedure and fact table additions). I'm following a relatively traditional deployment process focusing on regularly-scheduled code reviews and deployment windows with change control tickets twice a week with me an a colleague DBA handling the deployments. I've used SQL Audit, default trace data and RedGate DLM Dashboard to keep tabs on all the schema changes.

The tempo has increased steadily in the last two years and some of the lead analysts would like to go towards a DevOps deployment method using automation. The director and app owner is the most accomplished developer creating assemblies for the advanced ETL,  looking to perform deployments independent of me and my colleague, asking for sysadmin privs when we're backlogged or short-handed during vacation.

I've declined requests to share sysadmin privileges with the director because of the risk of setting a precedent for other developers, the liability if access is opened too broadly in error and the risk of a deployment error which will get me paged at 3am when I'm off-call. On the flip side, I'm aware of the business pressure on the developers to get new content to market faster, even if it's not properly tuned. Finally, the political and departmental element of reporting to the director is present; it's easier to say 'No' if you're a DBA in a separate unit, but harder if the director signs your paycheck.

I've told my director in the past that in the event of a breach or system failure, all the sysadmins need to be at the IT Security board of inquiry to explain what happened, which has given him pause in the past, but the business risk of delaying content is getting stronger. What options can I offer to the director that go short of sysadmin rights that give him more latitude to deploy but have the proper level of auditing in depth? I'm familiar with the various server and database fixed roles, but I'm looking at departmental protocol options as well.


texts: In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?

texts:
Update 2014-12-18

With the overwhelming response to the main question being "No", the more interesting responses have focused on part 2, how to solve the performance puzzle with an explicit ORDER BY.  Although I've marked an answer already, I wouldn't be surprised if there were an even better performing solution.

Original

This question arose because the only extremely fast solution I could find to a particular problem only works without an ORDER BY clause. Below is the full T-SQL needed to produce the problem, along with my proposed solution (I am using SQL Server 2008 R2, if that matters.)

--Create Orders table
IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders
CREATE TABLE #Orders
(
       OrderID    INT NOT NULL IDENTITY(1,1)
     , CustID     INT NOT NULL
     , StoreID    INT NOT NULL
     , Amount     FLOAT NOT NULL
)
CREATE CLUSTERED INDEX IX ON #Orders (StoreID, Amount DESC, CustID)

--Add 1 million rows w/ 100K Customers each of whom had 10 orders
;WITH
    Cte0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    Cte1 AS (SELECT 1 AS C FROM Cte0 AS A, Cte0 AS B),--4 rows
    Cte2 AS (SELECT 1 AS C FROM Cte1 AS A ,Cte1 AS B),--16 rows
    Cte3 AS (SELECT 1 AS C FROM Cte2 AS A ,Cte2 AS B),--256 rows
    Cte4 AS (SELECT 1 AS C FROM Cte3 AS A ,Cte3 AS B),--65536 rows
    Cte5 AS (SELECT 1 AS C FROM Cte4 AS A ,Cte2 AS B),--1048576 rows
    FinalCte AS (SELECT  ROW_NUMBER() OVER (ORDER BY C) AS Number FROM   Cte5)
INSERT INTO #Orders (CustID, StoreID, Amount)
SELECT CustID = Number / 10
     , StoreID    = Number % 4
     , Amount     = 1000 * RAND(Number)
FROM  FinalCte
WHERE Number <= 1000000

SET STATISTICS IO ON
SET STATISTICS TIME ON

--For StoreID = 1, find the top 500 customers ordered by their most expensive purchase (Amount)

--Solution A: Without ORDER BY
DECLARE @Top INT = 500
SELECT DISTINCT TOP (@Top) CustID
FROM #Orders WITH(FORCESEEK)
WHERE StoreID = 1
OPTION(OPTIMIZE FOR (@Top = 1), FAST 1);
--9 logical reads, CPU Time = 0 ms, elapsed time = 1 ms
GO
--Solution B: With ORDER BY
DECLARE @Top INT = 500
SELECT TOP (@Top) CustID
FROM #Orders
WHERE StoreID = 1
GROUP BY CustID
ORDER BY MAX(Amount) DESC
OPTION(MAXDOP 1)
--745 logical reads, CPU Time = 141 ms, elapsed time = 145 ms
--Uses Sort operator

GO

Here are the execution plans for Solution A and B, respectively:

Solution A gives the performance I need, but I couldn't get it to work with the same performance when adding any kind ORDER BY clause (e.g., see Solution B).  And it certainly seems like Solution A would have to deliver its results in order, since 1) the table has only one index on it, 2) a seek is forced, thus eliminating the possibility of its using an allocation order scan based on IAM pages.

So my questions are:

Am I right that it will guarantee the order in this case without an order by clause?
If not, is there another method to force a plan that is as fast as Solution A, preferably one that avoids sorts? Note that it would have to solve the exact same problem (for StoreID = 1, find the top 500 customers ordered by their most expensive purchase amount). It would also have to still use the #Orders table, but different indexing schemes would be OK.


texts:
I need some insight on how to get data out of a telemetry DB in just the right way and fumbling with this produces either errors or incorrect data

I have a table like this:

CREATE TABLE Telemetry
(
  pk              SERIAL PRIMARY KEY NOT NULL,
  id              VARCHAR(36)        NOT NULL,
  organizationid  VARCHAR(36)        NOT NULL,
  lat             REAL               NOT NULL,
  lon             REAL               NOT NULL,
  basetype        VARCHAR(16)        NOT NULL,
  name            VARCHAR(64)        NOT NULL,
  updatetimestamp TIMESTAMP          NOT NULL
);

and a query like this:

select id, ST_Multi(point(lon,lat)::geometry) as cords from telemetry GROUP BY id;

But that produces:

  [42803] ERROR: column "telemetry.lon" must appear in the GROUP BY
  clause or be used in an aggregate function Position: 27

so, I give this a swing:

SELECT
  id,
  organizationid,
  basetype,
  name,
  ST_Multi(point(lon, lat) :: GEOMETRY) AS cords
FROM telemetry
GROUP BY id, organizationid, basetype, name, updatetimestamp, cords
ORDER BY updatetimestamp;

which produces no error, but then doesn't group as I would expect.

d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...
8e272cf7-bc03-4d70-b139-03081f6fa1b4,...,VEHICLE,...
d333aa7f-223d-4a13-8a4b-50e7ecbe424e,...,PACKAGE,xHSUPlzbizu8IOybrtDw,...

Notice how d333.. repeats? What I need is

id,orgid,baseType -> [(lat,lonn),(lat,lon)...]

id, org, basetype will all be the same

I need all the telemetry positions for a given id sorted by time; each telemetry event is captured with a timestamp and I need  each point in the multipoint to be added as it was found in time series (or it's no good).

In case it isn't obvious, I need to track positional data as something moved and draw these points on a map.


texts: Galera Cluster not syncing from a standalone Mysql Server

texts:
I had setup a 3 node Galera cluster in production network. And I am replicating the data from a Standalone MySQL Server to one of the galera node(node1) through master slave replication.

I can replicate data within the cluster. But the problem is the data replicated from the Standalone server to the node1 is not replicating across the node2 and node3. I can see the exact async replica of MySQL standalone server in node1.

Thanks in advance.

 -----(Master)        -----
 MySQL --- >          Node1(Slave)
 -----                -----
                   /         \
                  |           |
                -----        -----
                Node2        Node3
                -----        -----


texts: Scaling for MariaDB / Percona / Galera Scaling

texts:
I ventured into the world of NoSQL database and as great as they are the lack of true ACID compliance has been a nightmare resulting in a lot of bad data so I am looking into relational databases.

I am very new into this, but have been looking into MariaDB and Percona clusters with Galera. I see a basic setup is 3 nodes, all which can be read/written to, and all contain identical data in each node.

My question is what happens if I need to scale horizontally:

slow write/reads
disk i/o issues
low memory

QUESTIONS

How can I scale to more machines?
What is the best practice?

I have not been able to find clear documentation on this.


texts: How to List SYNONYMS that no longer point to a valid object?

texts:
I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist.

I want to create a query to list them. But I am stuck.

My table OBJECTS_TO_UPDATE has these columns:

OBJECT_NAME
OBJECT_TYPE
OWNER

In words what I want to do goes along these lines:

I filter my OBJECTS_TO_UPDATE table by OBJECT_TYPE = 'SYNONYM'.
Using the Owner and Name I can get the SYNONYM in ALL_SYNONYMS.
Using the TABLE_OWNER and TABLE_NAME and search for it in the dba_objects, if the object doesn't exist, then the synonym is no longer valid.

I want to write a query using all those conditions but I am having issues.

select *
from OBJECTS_TO_UPDATE o2u
where o2u.OBJECT_TYPE = 'SYNONYM' and NOT EXISTS (
                            --select * from dba_objects dobj
                            --where dobj.OWNER
                            select * from ALL_SYNONYMS syn
                            where  syn.OWNER = o2u.OWNER and syn.SYNONYM_NAME = o2u.OBJECT_NAME)

I am stuck/confused about what to do inside the NOT EXISTS.
How can I achieve what I need? I have DBA privs.

Also, is this the best way to achieve what I want? Maybe there are some other views I should use that more direct.


texts: generate_series for multiple record types in postgresql

texts:
I have two tables that I want to query: pest_counts and pests which look like:

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

I want to use postgres' generate_series to show the number of each type of pest that was found for the date series:

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | 0
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...

I know I'll need something like the following but I'm not exactly sure how to do the rest:

SELECT date FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 day') date


texts: Need help with a query for a helpdesk application

texts:
I have a helpdesk application I'm modifying.

I need to pull data into an ASP.Net DataGrid.

In the problems table, there is a field for employeeID, which is the employee id of the person entering the ticket. There is also a rep field which holds the employee id of the rep handling the ticket.

I'm trying get the employee NAME for both the person entering the ticket and the rep handling the ticket from a users table.

Here are the relevant fields...

Problem Table:

pID
employeeID
sLocation
sRoom
pRep

Users Table:

fName
lName
empID

Here's an example of a query I'm using to pull the problems associated with just one rep:

SELECT p.pID, u.fName + ' ' + u.lName as FullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pRep=Session("EmpID")

If I want to pull all the problems and list the rep who is handling the issue, what would that query be?

Would it look something like this (thought I feel sure there's more to that join statement)

SELECT p.pID, u.fName + ' ' + u.lName as EmpFullName, p.sLocation, p.sRoom, p.pStatus, p.pTitle, u.fName + ' ' + u.lName as RepFullName
FROM Problems p INNER JOIN tblUsers u ON p.employeeID = u.empID
WHERE p.pStatus='OPEN'


texts:
How can I pivot the following SELECT statement:

         SELECT sc.CID,
                sc.CodeName     as OverviewText,
                scRAG.CodeName  as RAGStatusText
           FROM StatusCode      sc
LEFT OUTER JOIN ProjectOverview po
             ON sc.CID          = po.ProjectOverviewCID
            AND po.ProjectId    = 180
LEFT OUTER JOIN StatusCode      scRAG
             ON po.RAGStatusCID = scRAG.CID
          WHERE sc.SCID         = 18

Such statement yields the result set that follows:

CID OverviewText    RAGStatusText
--- ------------    -------------
153 Cost            Green
154 Requirements    Yellow
155 Schedule        NULL
156 Technical       NULL
157 Testing         NULL

But, I want it to return one single row with 10 values, as shown bellow:

 ----------------------------------------------------------------------------------------------
 | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |
 ----------------------------------------------------------------------------------------------

Can I pivot on CID?


texts: I have a peculiar issue on my hands.  I have a few databases in my SSMS already (2014), and when creating a new database and performing a restore from a file that has never been used, it sets my main database to single-user mode.  During the restore I initially didn't realize it was moving the main DB to the new location.  I don't want this, but regardless of what I do it doesn't allow me to just restore this to the selected database.  I have been through the google search gauntlet, finding no results matching what I am experiencing.  I have also attempted to just create a new database by setting restore as my option, but the main database is selected.  Does anyone have a sure-fire way to get this issue resolved so I can add this other desired database to my system?


texts: On one of my servers (Win 2012 R2), SQL Server 2012 is constantly under attack with the SA account being hit like 10 times per second with different passwords. I don't have an SA account but still I would like to stop this probing as its probably consuming resources. I keep checking the logs and blocking the offending IPs at firewall level manually, still I would like a better solution. SQL Server runs locally with IIS to serve its websites, I only need to connect to SQL Server remotely for database development with SSMS, so at first I thought setting up a VPN but not sure if this is going to play well with SSMS and other services like FTP. As in interim solution where can I stop SQL Server from being visible to the outside world at the firewall by disabling port 1433, or elsewhere? I can then enable/disable this when developing and perhaps just for my IP. Thanks.


texts:
How to get the full text of the request from the following request:

SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Here, with a sufficiently long request, the request text breaks off.
I use SSMS 17 and 18 versions.


texts: MariaDB 10.2 Encrypted at rest log files not being written

texts:
We're running MariaDB 10.2 with InnoDB tables that are encrypted at rest. I've also specified that the log files be encrypted at rest but they remain empty files. The tables seem to be encrypted just fine.

I'm hoping to get this database set up for replication with encrypted at rest log files.

Permissions all seem fine - can anyone think of a reason as to why the log files wont write?

Thanks in advance!

Here is my my.cnf

user=mysql

bind-address = 210.5.55.92
local-infile=0
max_connections=2000
max_allowed_packet=100M
skip-name-resolve

query_cache_type=0
query_cache_size = 128M
query_cache_limit = 1M
query_cache_min_res_unit=512

key_buffer_size = 1G
sort_buffer_size=16M
read_buffer_size=16M
read_rnd_buffer_size=16M
thread_concurrency=0

default-storage-engine = InnoDB
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 2

innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_data_file_path=ibdata1:10M:autoextend

thread_cache_size = 4

myisam_use_mmap=1
tmp_table_size = 512M
max_heap_table_size = 512M

symbolic-links=0

key_buffer_size=128M
sort_buffer_size=8M

expire-logs-days = 5
max-binlog-size  = 100M

open_files_limit=50000

table_open_cache=4096
table_definition_cache=4096

log-error=/var/log/mysql/error.log

general-log
general-log-file=/var/log/mysql/mysqld_queries.log
log-output=file

slow_query_log = 1
slow_query_log_file = /var/log/mysql/log-slow-queries.log
long_query_time = 5
log_queries_not_using_indexes = 1

plugin-load-add=file_key_management.so
file-key-management
file-key-management-filename = /var/lib/mysql/keys
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4


texts: How to compare two tables that have no primary key?

texts:
So I got two sets of tables at work that I have to compare the data.
The fields are identical, but there is no column that has unique entries.
(Employee ID, Assignment ID, Employee's last name, Employee's First Name,
Dependent Last name, Dependent First name, Dependent Date of Birth)
An employee can have multiple assignments, and each employee can have multiple dependents.

The data of the two tables were entered by different people and so they are messy.. and I was asked to do some data cleanup.
Some of the dependents can be missing for an employee, or the dependent's name is spelled wrong /blanks etc...

Is there a way to compare the two tables for unmatched records, so that I can create a table for all data?

I tried to use Find Unmatched Fields query in Access but it can only compare one field?

Thanks in advance!:)

==edit==

I'm adding some sample data here:


texts: MongoDB doesn't start after changing the data directory

texts:
I have installed a mongodb instance using yum.. Now everything works fine. I started the service using service mongod start. It works well. Then I changed the data directory and log path in the configuration file. I restarted the server again and I started the service. But I get the below error:

Restarting mongod (via systemctl):  Job for mongod.service failed. See 'systemctl status mongod.service' and 'journalctl -xn' for details.
                                                           [FAILED]

When I give systemctl status mongod.service I get the below:

 Loaded: loaded (/etc/rc.d/init.d/mongod)
   Active: failed (Result: exit-code) since Wed 2015-03-18 11:35:56 IST; 22s ago
  Process: 10672 ExecStop=/etc/rc.d/init.d/mongod stop (code=exited, status=0/SUCCESS)
  Process: 10841 ExecStart=/etc/rc.d/init.d/mongod start (code=exited, status=1/FAILURE)
 Main PID: 10509 (code=exited, status=0/SUCCESS)

Mar 18 11:35:56 localhost systemd[1]: Starting SYSV: Mongo is a scalable, document-oriented database....
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session opened for user mongod by (uid=0)
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session closed for user mongod
Mar 18 11:35:56 localhost mongod[10841]: Starting mongod: [FAILED]
Mar 18 11:35:56 localhost systemd[1]: mongod.service: control process exited, code=exited status=1
Mar 18 11:35:56 localhost systemd[1]: Failed to start SYSV: Mongo is a scalable, document-oriented database..
Mar 18 11:35:56 localhost systemd[1]: Unit mongod.service entered failed state.

When I give journalctl -xn I get the below:

-- Logs begin at Wed 2015-03-18 08:56:56 IST, end at Wed 2015-03-18 11:35:56 IST. --
Mar 18 11:30:01 localhost systemd[1]: Starting Session 20 of user root.
-- Subject: Unit session-20.scope has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-20.scope has begun starting up.
Mar 18 11:30:01 localhost systemd[1]: Started Session 20 of user root.
-- Subject: Unit session-20.scope has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-20.scope has finished starting up.
--
-- The start-up result is done.
Mar 18 11:30:01 localhost CROND[10712]: (root) CMD (/usr/lib64/sa/sa1 1 1)
Mar 18 11:35:56 localhost systemd[1]: Starting SYSV: Mongo is a scalable, document-oriented database....
-- Subject: Unit mongod.service has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mongod.service has begun starting up.
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session opened for user mongod by (uid=0)
Mar 18 11:35:56 localhost runuser[10850]: pam_unix(runuser:session): session closed for user mongod
Mar 18 11:35:56 localhost mongod[10841]: Starting mongod: [FAILED]
Mar 18 11:35:56 localhost systemd[1]: mongod.service: control process exited, code=exited status=1
Mar 18 11:35:56 localhost systemd[1]: Failed to start SYSV: Mongo is a scalable, document-oriented database..
-- Subject: Unit mongod.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

Can anyone help me to fix this? Thanks!!!

P.S.:
The data directory I have created has the all permissions for the user. But again if I change the data directory to default (/var/lib/mongodb) it works fine.


texts: selecting same column twice for different value

texts:
I have a table like this;

event_id  pair  versus  score
       1     1      11    100
       1     1      12      0
 ...
       1    11       1      0
       1    12       1     50

Now I want to get the both scores for a given pair on the same result row.
Like;

event_id pair versus score1 score2
       1    1     11    100      0
       1    1     12      0     50

Any ideas?


texts: Where all can the /*+ PARALLEL */ hint be used?

texts:
It works with SELECT and UPDATE statements.

Are there some more statements with which it works?

I was able to use it with create, but am not sure if the hint was actually considered.

SQL> create /*+ PARALLEL */ table t1
( id number,
name varchar2(40));

Table created.

SQL>

Except INSERT and UPDATE, when all is the PARALLEL hint considered?


texts: Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

texts:
I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing some research seems that the best approach would be to run Insert and Update statements in separate procedures. I'm deciding which stored procedure should be executed and then call procedure with all required parameters. There is also way to catch errors like deadlock or timeout in SQL try/catch block. I'm wondering if this would be necessary in my current code or it's over complicated. Here is example of my current code:

CREATE PROCEDURE [dbo].[UpdateBuilding]
    @Status BIT = NULL,
    @Name VARCHAR(50) = NULL,
    @Code CHAR(2) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
GO
    DECLARE @Transaction varchar(20) = 'TransUpdate';
    DECLARE @RetryCount INT
    DECLARE @Success    BIT
    SELECT @RetryCount = 1, @Success = 0
    WHILE @RetryCount < =  3 AND @Success = 0

    BEGIN TRY
        BEGIN TRANSACTION @Transaction
        -- This line is to show you on which execution
        -- we successfully commit.
        SELECT CAST (@RetryCount AS VARCHAR(5)) +  'st. Attempt'

            BEGIN
                UPDATE dbo.Building
                SET Status = @Status,
                    Name = @Name,
                    Code = @Code,
                    ActionDt = CURRENT_TIMESTAMP,
                    ActionID = @ActionID
                OUTPUT INSERTED.Code
                WHERE Code = @Code;
            END
            COMMIT TRANSACTION @Transaction

            SELECT 'Success!'
            SELECT @Success = 1 -- To exit the loop
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION @Transaction

        SELECT  ERROR_NUMBER() AS [Error Number],
                ERROR_MESSAGE() AS [ErrorMessage];

        -- Now we check the error number to
        -- only use retry logic on the errors we
        -- are able to handle.
        -- You can set different handlers for different
        -- errors
        IF ERROR_NUMBER() IN (  1204, -- SqlOutOfLocks
                                1205, -- SqlDeadlockVictim
                                1222 -- SqlLockRequestTimeout
                            )
        BEGIN
            SET @RetryCount = @RetryCount + 1
            -- This delay is to give the blocking
            -- transaction time to finish.
            -- So you need to tune according to your
            -- environment
            WAITFOR DELAY '00:00:02'
        END
        ELSE
        BEGIN
            -- If we don't have a handler for current error
            -- then we throw an exception and abort the loop
            THROW;
        END
    END CATCH
END

This solution might be to complex for what I actually deal with but at the same time I would like to catch any deadlocks or other problems that I might face with UPDATE/INSERT transactions. This stored procedure is used for single row insert/update (I only showed update in this question). If anyone have suggestion or better approach for this please let me know.


texts:
I have an archival job in my SQL Server which is part of AOAG. I can see the same job in all replicas which are part of AOAG and it's scheduled at the same time.

It is running at the same time on all servers for around 30 seconds successfully.

Can anyone let me know the logic behind the same job same schedule in all replicas successfully running.

It is connecting through AOAG listener to servers and the first step will be copying a file to another location and running the archival job, on job success the source location will be deleted. This is what I can see in job properties-->steps.

I need to create a new customized archival job.

What will be the problem if I create the job with 5 minutes difference in each replica?
Will there be any problem if I schedule this job at same time in each replica?

Please advise.


texts:
I am asked to investigate viability to use SQL Server replication technology for one of our upcoming project. In this project, basically there will be one central server located at corporate HQ (say Hub) and there will be multiple servers located at remote locations (say Nodes). All nodes will get data by using our application and Hub will get data directly injected into SQL Server. I am given 2 business requirements that need to be satisfied by this solution,

Data to be synchronized between hub and nodes both ways.
Data from one node NOT to appear on another node.

So based on those requirements, I chose to try Merge Replication.For test, I setup hub and 2 nodes. Hub is also acting as distributor. Then selected all objects of database and created subscription. And registered both nodes as subscribers. And this setup works... almost. The issue I am facing is, if I update data from node1 it appears in hub as well as node2 (and vise versa). That doesn't satisfy our 2nd requirement.

I am familiar with Replication but not master at it and I would really appreciate if someone can suggest if something is wrong with my approach. Or is merge replication not a good choice for my case?

I am using SQL Server 2014 standard editions for all 3 instances installed in Windows Server 2012R2.


texts: Do I need to ANALYZE table after DROP INDEX?

texts:
In our postgres db we historically have many similar indexes (e.g. is_deleted boolean) that have quite bad selectivity value. Worse than that, sometimes query planner breaks and postgres starts to use some of them instead of much more efficient indexes so we need to call ANALYZE manually to fix performance, so we decided to delete most of them.
Do we need to call ANALYZE for each table after deleting index?
According to the answer in Is it necessary to ANALYZE a table after an index has been created? postgres collects stats about actual values in table (for simple indexes) without index-related info but I couldn't find proof in docs.


texts: How do you hide tool tips in MySQL Workbench?

texts: Is there a way to hide the tool tips that popup when you hover over objects? Especially when you hover over tables, I do not want to see the breakdown of what objects are in that table.


texts: Move data from MySQL 5.0 to other server with MySQL 5.7

texts:
We are switching servers from a Windows Server 2003 (32-bit) with a MySQL 5.0.51b to a Windows Server 2016 (64-bit) on which I will install the latest MySQL version (5.7).

The database contains just tables (no views, foreign keys...) which I want to move to the new database. Should I be able to use mysqldump and mysqlimport (as per the documentation) even though the difference in MySQL versions?


texts: ODBC and ADO connection to SQL Server: What protocol is used when connecting to \?

texts:
We understand when connecting to SQL server that specifying:

< ip address>
< host or ip>,< port number>

That the ADO or ODBC client will typically use tcpip to connect.

However, we observe that when a client asks to connect to:

< server>\< instance name> that it seems the client (ODBC in this case) does NOT use tcpip.

We believe in some cases, connecting to:

< host name>

May also result in a named pipes connection.

Questions:

Since < server>\< instance name> is not connecting over tcpip, that means it is using named pipes, right?
How do I force the connection to use tcpip?

And is the behavior (of the 32 bit Windows ODBC driver for SQL Server) in this regard the same as the behavior of the old dblib client?


texts: What is the difference between ON SCHEMA and ON DATABASE triggers?

texts: We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference between them? Is it some legacy stuff? ON DATABASE should be used when using AFTER STARTUP or AFTER STARTUP because it's definitely related only to database but the same stuff that is done using ON SCHEMA might be done using ON DATABASE, so what's the difference? I can't find references in Oracle docs about that.


texts: Postgres - Where condition on nested joins table not working

texts:
I have 3 tables:
order_items, invoice_items and invoices

In order_items model:

has_many :invoice_items

P.S an order_item may or may not have any invoice items associated with it

In Invoices model:

has_many :invoice_items

In InvoiceItem model

belongs_to :order_item
belongs_to :invoice

order_items table has booked_quantity and cancelled_quantity fields in it

invoices table has state field in it. state can be cancelled or approved

invoice_items table has quantity field in it.

An order item is considered to be invoiceable when the following condition holds:

  (booked_quantity - cancelled_quantity - SUM(invoice_items.quantity where associated invoice is not cancelled)) > 0

I am trying to fetch all the order items which are invoiceable using single sql query. Here are the two solutions that i tried:

Solution 1:

SELECT * FROM order_items
       LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id
       INNER JOIN invoices ON invoices.id = invoice_items.invoice_id
       WHERE invoices.state != 'cancelled'
       GROUP BY order_items.id
       HAVING ((booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity))) > 0)
       ORDER BY order_items.id ASC

Solution 2:

(
  WITH ii_ord_items AS
  (
    SELECT invoice_items.order_item_id, COALESCE(SUM(invoice_items.quantity), 0) AS uncancelled_invoiced_quantity from order_items
    LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id
    INNER JOIN invoices ON invoices.id = invoice_items.invoice_id
    WHERE invoices.state != 'cancelled'
    GROUP BY invoice_items.order_item_id
  )
  SELECT order_items.* FROM order_items
  LEFT OUTER JOIN ii_oitems ON order_items.id = ii_oitems.order_item_id
  WHERE ( ii_ord_items.uncancelled_invoiced_quantity < (order_items.booked_quantity - order_items.cancelled_quantity))
) AS order_items

Both of them are working fine when invoiced is not in cancelled state. But in case when invoice is cancelled both of them are giving incorrect results. They seem to be considering cancelled invoices quantity as well. So i guess there's some problem with the where condition but i couldn't figure out what's the issue exactly. Any help would be appreciated. Thanks in advance !!

Sample Data:

order_items table

id      booked_quantity   cancelled_quantity
1          10                    2

invoices table

id      state
1       cancelled

invoice_items table

id      invoice_id    order_item_id     quantity
1           1              1               8

Expected Result: If i try to fetch all the order items for which invoice_items can be generated should include order_item with id 1 as well as invoice is currently cancelled. If the invoice state would have been anything other than "cancelled" then order_item with id 1 should not be present in the result.


texts:
I am currently working as an application developer on a project which involves the creation of a new SQL Server database. I am actively working with a group of DBAs who will play some role in the creation and maintenence of this database.

I want to understand what role the DBA normally plays in this type of scenario. It seems the DBAs consider everything to be the responsibility of the application team. I designed the schema and indexes, generated the DDL, and am currently in the process of performance testing the database. Are these tasks normally performed by development?

Also, it has been suggested that development should be responsible for the creation and maintenence of the database user accounts. We would be responsible for periodically changing the passwords on the production databases. Is this the norm? It seems to me that it should be done by the DBAs.


texts:
I'm not a DBA but I've been asked to see if we can encrypt the data in sql server such that the admin, despite having access to SQL Server won't be able to see the data but application would still be able to query on it and read the data.

My limited knowledge of encryption tells me that if application encrypts the data then db won't be able to query it. If db encrypts the data then DBA can definitely read it.

So is there a way to prevent DBA from peeking into data but still be able to manage database?


texts: PostgreSQL "at time zone" construct does not use index

texts:
I have a simple table and am unable to understand why a certain query does not use an obvious index, and runs so slow.

This is the table:

create table impulse2 (
   account     smallint        NOT NULL,
   sensor      smallint        NOT NULL,
   datetime    timestamp       NOT NULL,
   last_value  numeric(10,2)
);
alter table impulse2 add constraint impulse_pkey2 primary key (account, sensor, datetime);

The slow query is here:

select
date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
round(avg(last_value), 1) as last_value
from impulse2
where account = 1
and sensor = 2
and date_trunc('hour', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') between '2016-09-08 00:00:00' and '2016-09-08 00:10:00'
group by 1
order by 1

Explain analyse returns this:

QUERY PLAN
GroupAggregate  (cost=38120.73..38263.54 rows=5193 width=14) (actual time=931.191..931.278 rows=60 loops=1)
  Group Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
  ->  Sort  (cost=38120.73..38133.72 rows=5193 width=14) (actual time=931.172..931.187 rows=165 loops=1)
        Sort Key: (date_trunc('minute'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)))
        Sort Method: quicksort  Memory: 32kB
        ->  Seq Scan on impulse2  (cost=0.00..37800.26 rows=5193 width=14) (actual time=537.536..931.131 rows=165 loops=1)
              Filter: ((account = 1) AND (sensor = 2) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) >= '2016-09-08 00:00:00'::timestamp without time zone) AND (date_trunc('hour'::text, timezone('Europe/Luxembourg'::text, (datetime)::timestamp with time zone)) <= '2016-09-08 00:10:00'::timestamp without time zone))
              Rows Removed by Filter: 1038445
Planning time: 0.195 ms
Execution time: 931.341 ms


texts: How to increase the number of activity in pg_stat_activity?

texts:
I am working with Postgresql(Data Grip) and I am trying to run many activities at once but pg_stat_activity let me only do 100 activities and from the other it ignores.

How can I increase the number of activities or are there other solutions?


texts: huge size of /var/lib/postgresql/9.3/main/base/1 directory (postgresql)

texts:
i'm using postgresql 9.3.4 and real size of database is about 5.5 GB but POSTGRESQL fill disk about 256 GB !!

is this POSTGRESQL Cache and what is method to clean thing issue ?


texts: Using XPath .exist to look for elements within a decimal range

texts:
I am trying to do a range comparison in an xpath exist - but it is being ignored (returning all rows).  The element "caruwmarketfactor" is a decimal value (stored untyped) - can anybody see what I am doing wrong?

The element can appear anywhere in the document - I know in all our cases it will be in there only 1 time.  I am looking for the range of .70 to .75.

declare @table table (id int identity (1, 1), clientXml xml)

insert into @table (clientXml) values
('<root>
  <ApplicationData>
    <caruwmarketfactor>0.7468</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.55</caruwmarketfactor>
  </ApplicationData>
</root>'),

('<root>
  <ApplicationData>
    <caruwmarketfactor>0.76</caruwmarketfactor>
  </ApplicationData>
</root>')

select id
      ,clientxml.value('(//*/caruwmarketfactor)[1][text()]', 'decimal (22, 7)')
from @table t
where t.clientXml.exist(N'(//*/caruwmarketfactor[xs:decimal(text()[1]) gt xs:decimal("0.70")])
                       and (//*/caruwmarketfactor[xs:decimal(text()[1]) lt xs:decimal("0.75")])') = 1

UPDATE: I just tried using the same logic (value is between a range) for dates and same problem.  Whatever I am doing wrong can be applied to dates too.


texts: Optimize MariaDB query with table that self-joins

texts:
I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why.

The query (which is generated by an application that would be difficult but not impossible to change) is:

SELECT t1.id id1, t2.id id2, 5 weight
FROM ar1 t1
INNER JOIN ar1 t2 ON (t2.contact_type = 'Individual' and t1.first_name = t2.first_name)
WHERE t1.first_name IS NOT NULL
 AND t1.first_name <> ''
 AND t1.contact_type = 'Individual'
 AND t1.id < t2.id GROUP BY id1, id2, weight

And a table with 100k rows.

[EDIT: added:] The puropse of the query is to provide a list of unique pairs of records that could be duplicates. Could be because the table has other fields, too, but this query is only looking at the first name field.

CREATE TABLE `ar1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_type` varchar(64) DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`first_name`,`contact_type`),
  KEY `k2` (`contact_type`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Table status:

Name            | ar1
Engine          | InnoDB
Version         | 10
Row_format      | Dynamic
Rows            | 102463
Avg_row_length  | 46
Data_length     | 4734976
Max_data_length | 0
Index_length    | 5275648
Data_free       | 0
Auto_increment  | <null>
Create_time     | 2020-04-21 09:22:1
Update_time     | <null>
Check_time      | <null>
Collation       | utf8mb4_unicode_ci
Checksum        | <null>
Create_options  |

EXPLAIN says this:

***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t1
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k2
key_len       | 259
ref           | const
rows          | 51232
Extra         | Using where; Using index; Using temporary; Using filesort
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | t2
type          | ref
possible_keys | PRIMARY,k1,k2
key           | k1
key_len       | 518
ref           | od_civicrm.t1.first_name,const
rows          | 5
Extra         | Using where; Using index
2 rows in set

Notes:

There are only 8647 distinct first names in the table.
contact_type is Individual for 99.9% of the rows.
There's no need to group on a constant (weight), either. But ommitting that does not magically fix it either.
I've tried various index combinations.
I'm on MariaDB 10.1
The servers I've tried it on are not massive - but to my mind this is not a huge recordset?

The ANALYZE result can be downloaded


texts: How to know which user update/insert or delete in MYSQL database?

texts:
Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database.
My problem is that I want to know which user made the change in my database?


texts: Archive a single giant table from a SQL 2008 database - filegroup backup?

texts:
I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs to be recovered at some point in the future.

The most obvious idea is to simply archive the full database backup, but recovering this one table from that very large backup would be a challenge, due to the total database size.

I suppose I could also export the table to an otherwise-empty database, and back that up. This may be my fallback option.

I wanted to ask about filegroup backups, however, since this table is part of its own filegroup. Is this a valid option for me?

If I understand this article on filegroup restores correctly, the answer is no, but I'm not sure that my case applies here, since my database is in SIMPLE recovery mode (not FULL), and because I'm dropping the filegroup entirely after the backup.

Is filegroup backup an option? Or should I go with one of the other two?


texts: Optimizing a single table, write-once, read-many PostgreSQL database

texts:
I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single row in the table. Each word in each ngram also has a part of speech that I generated using TextBlob and textblob-aptagger, which is also stored in the ngram's row. This table is the only table in the database, and now that all the rows I could ever want are in the table, I don't expect to insert new rows or update existing ones ever again. At most, I may be deleting a undesired ngram from time to time, but even that shouldn't happen or at least should be kept to a minimum. All of this said, I still have yet to generate all of the indices I need, but I have a plan for that and will be doing it shortly.

My question is this: how can I optimize my database (particularly my configuration file) for periodic reads that occur together in short bursts, at the sacrifice of the speed of inserts, updates and deletes, all of which I should not be performing from here on out?

Specs (I realize these are sub-optimal, but I'm currently on a very tight budget): Amazon RDS; db.t2.micro; 1vCPU, 1GB RAM, SSD (currently 100GB--expect to go as high as 200GB once I've created the indices I need, and hopefully not higher); PostgreSQL 9.3.5-R1; the OS is UNIX-based, but some crazy custom Amazon stuffs and cannot be connected to directly, so any changes I can make are mostly limited to Postgres.

I've already done some research and set the following to 1.0 per several sources because of the SSD: random_page_cost and seq_page_cost. Does anyone disagree with this decision? I'm very open to suggestions!

Thanks in advance for any assistance.


texts:
I have database with :

-two filegroups (PRIMARY and INDEX)

-three files .mdf(data), .ndf(index) and .ldf(log)

every file is on different disk, yesterday disk where was NDF file crashed so I lost that file.
Is there any way to fix database, without restore?

TNX!


texts: Migrating Sql Cluster Installation To New Drive

texts:
We are migrating from an old SAN to a new SAN and need to migrate our SQL Server instance to new LUNs. We are able to migrate the data without any problem, but the SQL Server instance itself is clustered (single instance) and the clustered disks must also be migrated to the new SAN.

Paths are as follows:

Create LUNs on new SAN.
Present new LUNs to the cluster.
Copy everything from old clustered disk resource to new clustered disk
resource.
Rename OLD disk resource to something.
Rename NEW disk resource to OLD name.
Cross fingers.

OR

Reinstall OS/Sql on the cluster.

We will have to do this for 13 instances.


texts: Why select statement requests exclusive locks?

texts:
In postgres (9.5) select requests exclusive lock. Why?

play=# \x on
Expanded display is on.
play=#   SELECT * FROM pg_locks pl
  LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid
    where
    pl.mode = 'ExclusiveLock'
;

-[ RECORD 1 ]------+---------------------------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 7/16
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 7/16
pid                | 17376
mode               | ExclusiveLock
granted            | t
fastpath           | t
datid              | 1700431
datname            | play
pid                | 17376
usesysid           | 10
usename            | postgres
application_name   | psql
client_addr        | 127.0.0.1
client_hostname    |
client_port        | 54308
backend_start      | 2018-05-07 15:12:30.682069+02
xact_start         | 2018-05-07 15:12:59.513311+02
query_start        | 2018-05-07 15:12:59.513311+02
state_change       | 2018-05-07 15:12:59.513313+02
wait_event_type    |
wait_event         |
state              | active
backend_xid        |
backend_xmin       | 7152428
query              | SELECT * FROM pg_locks pl       +
                   |   LEFT JOIN pg_stat_activity psa+
                   |     ON pl.pid = psa.pid         +
                   |     where                       +
                   |     pl.mode = 'ExclusiveLock'   +
                   | ;


texts: Performing aggregations and calculations: SQL (CTE) vs MDX

texts:
I’ve hit a wall and would like some expert advice.

I’m building a data warehouse and don’t quite understand whether the calculations (also time aggregations) should be done in SQL (T-SQL and CTE) or MDX.
So Currently I have the following approach

Define the level of time aggregation needed for the calculations. (currently I’d like to do it in SQL)

Day
Week
Month
Quarter
Year YTD (year-to-date)
QTD (quarter-to-date)
YTW (year-to-week)
YTM (year-to-month)

Calculate the calculations that can be calculated on the column level and at the level of each time aggregation (again I’d like to do it in SQL)
And then do the row level calculations (for this I’m thinking about using MDX

Changes with previous period:

Day change with previous day
Week change with previous week
Month change with previous month
Quarter change with previous quarter
Year change with previous year

Changes compared to same period of last period

Day vs same day last week
Day vs same day last month
Day vs same day last year
Week vs same week last month
Week vs same week last year
Month vs same month last year
Quarter vs same quarter last year

The reason I’m thinking about doing steps 1 and 2 in SQL is that I’ m visualizing it as a table that has all the variables grouped to a level of time aggregation of YTD, YTM, YTW, etc and then in the same table there would also be calculations in columns...and then if I use this table as one of the underlying tables of the DW then in the DW I would use MDX for the parallel period calculations.

I would like to know if this is a good approach...or what is the best approach/place to do calculations?


texts: How to do this binary appearance analysis

texts:
I have a table named results:

integer: id
json: websites

An example of websites column is:

[{"url": "http://google.com"}, {"url": "http://example.com"}]

What I want to do is generate a binary appearance chart, where it shows a 1 or a 0 depending on if ANY url in the JSON blob is contained within another set.

All the possibilities are on columns, and all the results are as rows.

result_id | wikipedia.org | google.com | example.com
10         1               1            1
11         0               1            0
12         0               0            0
13         1               0            0

For it to return 1, ANY url in the JSON array object (with key ->>{'url'}) must match the domain
The URLs are full urls so ideally I would like to use regex to check if its a match (i can't do a full string match because I only care about the HOST of the URL). I'm thinking something like /https?:\/\/(example\.com)(/|?|$)/


texts: Creating rows in a table based on columns from another

texts:
I have an MS SQL 2012 DB with a table in the following format:

account_id   site_id   IRA   RFI   XSS   SQLi   BDP   Month
32501        112233    1     4     4     3      0     August,2016
32501        112233    5     2     6     1      0     September,2016
32501        110025    3     1     5     3      0     September,2016
32501        112233    4     5     2     7      0     October,2016

The table may have multiple lines per account (as the number of sites it contains) as well as multiple accounts. At most each site may have 3 lines, one for each month in a 3-months period.

I'm looking for a way to create a table that is based on the one above but in the following format:

AccountID    SiteID    ThreatName    08/2016    09/2016    10/2016    Quarterly_Total
32501        112233    IRA            1            5        4            10
32501        112233    RFI            4            2        5            11
32501        112233    XSS            4            6        2            12
32501        112233    SQLi           3            3        7            13
32501        112233    BDP            0            0        0            0
32501        110025    XSS            0            3        0            3
[...]

Basically it's a matter of making columns into rows from the source table and I've read somewhere that there is a PIVOT function that can help but didn't really understood how to use its syntax in my case. Also I should point out that the source table is actually a view that I create based on several different tables using a stored procedure.


texts: how to select rows matching a condition with a default


texts: Information_schema appearing in slow_log

texts:
I am finding that my information_schema keeps running this query

SELECT engine, COUNT(*) as n FROM information_schema.tables WHERE engine is not null GROUP BY engine

Which seems to always appear in the slow_log, it seems to run every 4-5 seconds; with a 10 second query time.

I am using Google Cloud SQL.

Is there a way I can disable this from occurring - or rather, what keeps triggering it? I have read the MySQL Documentation on the information_Schema but could not figure out why this was being called.

Thank you


texts: It is possible to take incremental backup in mongoDB Shard cluster? without opsmanager

texts:
Shard MongoDB Cluster:

3-router
3-config
2-shard (3-replica's per shard)

We are tring to take incremental backup for every one hour. We gone through few google links, but no luck. As per our above setup, to achive incremental backup we need to take mongodump with oplogs seperatly from config, shard1 and shard2 secondary servers and we will store it in s3. But the problem is with restoration part. To restore, all those dump (config, shard1 and shard2) needs to be downloaded from s3 and restore it to its specific replicaset. which means config dump to config and shard dump to shard.

Is this the correct process or we have any other option to take incremental backup with single query?

Thanks


texts: Is Memory Grant size 'cached' for subsequent executions of a cached plan?

texts:
I hope the title of the question is clear.  I am a DB developer as one of my job roles.  I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, cardinality estimates, and SQL Server memory management since.

I talked with my team lead and senior team lead about my findings and showed data from tests I had done where queries ran better when a larger sample size was used on certain indices.  As our primary role is software development and no one on my team knows much about database administration it was a very enlightening discussion.  They took the findings to the CIO who asked that I schedule a meeting with the entire DBA team, himself, and all the IT senior team leads.

Within 5 minutes of scheduling the meeting, the team lead of the DBA team was at my desk and not in a good mood.  He was semi hostile and I explained to him that this was meant to only be a discussion and I didn't want to step on toes.  During the course of this conversation he made a comment that Memory Grant sizes get reused from one execution to the next for executions that followed a cached plan.  I had not read anything about that in the 20 or so different websites I read about memory grant.

I wondered if it was in fact true and I am starting to have doubts in the abilities of this TL after another two comments in the same conversation.  The first comment he made was that his predecessor had created a complex script for handling the thresholds and sample sizes and that he had never made any changes to them.  He has been the TL for 2.5 years now.  The second comment he made 3 times was that setting IX_myTable with a sample size of 5 or 10 percent would "take forever to run".  I finally replied that I had tested that and it only took 2 seconds.  He was taken back by my reply and I followed up with by saying that a much larger table's index only took 25 seconds to run a 5% sample size (this table currently has a 0.2% sample size).  I showed him my documented tests that showed improved performance and smaller memory grants and even fully disclosed that in my findings certain indices at certain sample sizes no  longer yielded improved cardinality estimates.

I have tried searching on his claims about the memory grant size but I have turned up nothing.  Can anyone elaborate?


texts: SSRS and Managed Service Account - impossible to configure?

texts:
We are trying to configure Reporting Services to use a Managed Service Account.  The environment is:

Server:  Windows 2008 R2 SP1
Reporting Services: SQL Server 2012 (version 11.0.6567.0)

Currently, SSRS runs as a domain service account, but we would like to change to running as an MSA.  The SQL Server instance and agent have both been successfully changed to use an MSA.

When I try to change the service account to an MSA, via Reporting Services Configuration Manager, I receive an error:

  Microsoft.ReportingServices.WmiProvider.WMIProviderException: The account name is not valid. Specify an account in the form domain\alias.

  ---> System.Runtime.InteropServices.COMException (0x8004021D): Exception from HRESULT: 0x8004021D
     --- End of inner exception stack trace ---
     at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
     at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)
     at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)

My question is simple:

Has anyone successfully changed the SSRS service account to use a Managed Service Account?  If so, how?!


texts: WordPress Woocommerce Large Wp Post Meta table causing hung MYSQL Processes

texts:
WP post meta table size is 1.4GB+ with lots of entries. And the site does not respond due to hung processes in mysql.. Website has 1000 ~ 2000 users daily. Below is one sample of such query:
                    FROM wp_posts as posts
                    LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
                    LEFT JOIN wp_postmeta AS meta2 ON posts.ID = meta2.post_id
                    WHERE   meta.meta_key       = '_customer_user'
                    AND     meta.meta_value     = '27909'
                    AND     posts.post_type     = 'shop_order'
                    AND     posts.post_status   IN ( 'wc-processing','wc-completed','wc-delivered','wc-updated-tracking','wc-fulfilled-morning','wc-fulfilled-evening' )
                    AND     meta2.meta_key      = '_order_total'

Will MySQL performance tuning help ? Like innodb buffer pool size ?


texts: Third Normal Form. Multiple attributes determine another?

texts:
Concerning 3NF. If you have a composite non-key determinant to a single non-key dependent, does this violate 3NF? Everything I read on transitive dependencies says one non-key determining another non-key, but does not address the composite aspect.

Example: Ingredient(IngredientID (PK) —> Name, Supplier, Classification, UnitOfSale, UnitPrice)

A single supplier supplies an ingredient with a specific name and classification. So, (Name, Classification) --> Supplier. Does this violate 3NF?

Thank you for your time and thoughts.


texts:
Our web-application is something like - users can create groups(request for membership of a group also), ask questions, answer etc. It is still in the development stage. What I want to do is tightly couple some of the tables.
For example - Whenever a new member joins a group(i.e when that table is being updated) it should be checked whether he/she is a valid user or Whenever someone asks a question in a group, before inserting the question in the table, it should be checked whether that user is a a member of that group etc.
I cannot do these checks using foreign keys because, the group mmbers are store in this way

group_id    members

mygroup     member1,member2,member3
group001    member2,member10,member2,..<br>

As you can see they are stored by seperating commas. So I cannot put the foreign key constraint.

I do not want to do these checks in each and every servlet. I would like to do them in the database itself on insertion. Please tell me how to proceed with this? Thank you.


texts: Can somebody explain BEGIN; and COMMIT; for me once and for all?

texts:
Let's say I have this PHP code:

dbcall('BEGIN');
dbcall('CREATE SCHEMA "cool schema"');
dbcall('CREATE TABLE "cool schema"."cool table"');
dbcall('COMMIT');

Am I correct in thinking that this will first create a schema, and if the table cannot be created (for any reason, such as a temporary glitch or whatever), the schema that was just created is deleted again, so that the entire "state" of the database is reset to exactly how it was hen the BEGIN query was executed?

I've read the manual on transactions numerous times over the years, and asked people questions like this many times, but I've never got a clear, unambiguous answer, so I've never dared to actually use BEGIN and COMMIT queries anywhere in my application code.

This makes me feel as if my software isn't as "solid" as it could be, and I'm worried that temporary glitches could cause all kinds of issues because I oftentimes execute two queries which are very related, and where one doesn't make sense without the other, and those seem like perfect candidates for the BEGIN/COMMIT syntax.

And what if there is never a COMMIT query being made? Will it just continue piling on query after query and never "commit" them? And then throw them away when the script/session finishes? Will it COMMIT automatically if you make another BEGIN before a COMMIT?

If it has to "roll back" created records and other changes, what happens if other scripts have at the same time used the data in the database while it was working, which now no longer exists?

This truly boggles my poor mind. I cannot comprehend how this can be possible at all, from a logical perspective.


texts: MariaDB - Slow GROUP BY on 55 Million row table

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

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

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

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

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

Some indexes I've tried:

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

Using a monthly summary table

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

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

The schema:

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


texts:
According to the document about the replication and temporary tables, I'm setting --replicate-wild-ignore-table option to the %.norep% to prevent temporary tables from being replicated:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000861
          Read_Master_Log_Pos: 332610533
               Relay_Log_File: mysqld-relay-bin.001578
                Relay_Log_Pos: 9061317
        Relay_Master_Log_File: mysql-bin.000861
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: %.norep%
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330271548
              Relay_Log_Space: 13544924166
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 4

But as you can see from the above show slave status\G output, this statement is still replicated to the Slave and it causes the error.

Is there any problem with my query? Does it has only effect with the main tables (I mean ox_banners in this case)?

Reply to @Aaron Brown:

  This may be somewhat surprising, but this error is proof that your
  replication filter is working.

+1.

  The first question to ask yourself is why am I filtering replication
  at all?  There is rarely a good reason for this and it usually
  causes far more problems that it solves.

Due to the problem with temp tables: How to make MySQL replication reliable?

  You can  definitely work around the problem by using Row Based
  Replication (RBR) instead of Statement Based Replication (SBR).
  RBR replicates the changes to the data instead of the statement.

I will consider switching from mixed-based to row-based.


texts: How to prevent duplicates while storing result from different application

texts:
For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from different site and do some calculations to create data for a date and store it in db and use it.

Since multiple applications require this data I'm looking for data in db by date if it exist simply read it otherwise fetch data from internet and after some processing store it in db with date.

But as same thing will be done by multiple application there are chances that first application comes and look for data in db for a date and founds no data so it start fetching this data from internet, in the meanwhile other application also looks for same date data but it also finds no data so it will also start fetching data and by the time its done other application would have already stored that information so second application would try to insert duplicate information in the table.

What is the best approach in this scenario to avoid duplicate ? Should checking just before insert or using transaction can help ?


texts:
I have two SQL Server named instances, InstanceA and InstanceB on one computer, and they correctly show up under Sql Server Configuration Manager | SQL Server Network Configuration.

However, when I try to configure SQL Native Client 11.0 Configuration | Client Protocols, there is no option to specify to which instance the setting will be applied. It appears that this is server (machine) level setting that would apply to every instance installed on it.

Am i missing something here? How to allow one protocol, say Named Pipes only for InstanceA and TCP/IP only for InstanceB? (without using aliases, preferably)

Thanks


texts: Why isn't SQL Server using the Max Server Memory setting?

texts:
I have a SQL Server machine with the following:

Windows 2003 Enterprise SP2 (32-Bit)
SQL Server 2005 (32-Bit)
12GB RAM
/PAE switch set in boot.ini
6 SQL instances, all with AWE enabled
No other applications running on the machine.

I have the Max Server Memory usage set at what I believe are acceptable values (about 10GB combined, leaving 2GB for Windows). I am using the following query to show a combined total RAM usage of buffer pool and non-buffer pool. I am also showing the MAX SERVER MEMORY setting. The databases on each of the instances are far bigger than the allocated RAM, even though a couple instances get far more use than others. Shouldn't the actual memory in use always be pretty close to the MAX SERVER MEMORY setting? I am finding a gap that made me curious.

DECLARE @TotalRam as decimal (10,2)

--NON-BUFFER POOL
SET @TotalRam = (SELECT sum(pages_allocated_count)/128.0 [Non-Buffer (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0)

--BUFFER POOL
SET @TotalRam = @TotalRam + (
SELECT CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Buffer Pool (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 AND database_id <> 32767 )

SELECT @TotalRam as RAMinUSE, value as MaxServerMemory
FROM sys.configurations
WHERE name like '%max server memory%'

Query results:

I would like to add 12GB more RAM to this machine for the two heavy instances and I want to be sure that it will be used and that I do not have something configured incorrectly. I have verified that the editions of SQL and Windows installed will support 24GB RAM.

Is it normal to see a gap between these values? Perhaps my queries are missing something.

Do you folks see any issues with going from 12GB to 24GB RAM?

Also, Available MBytes remaining in PerfMon shows around 600MB. That number must be based on what's left after the allocation of MAX SERVER MEMORY, right? Otherwise I would expect to see a good bit higher.


texts: Equivalent of DB2 function "decimal(numeric expression, precision, scale)" in PostgreSQL

texts:
Is there an equivalent of the DB2 function decimal(numeric expression, precision, scale) in PostgreSQL? Or how would I do this functionality in PostgreSQL?

The DECIMAL function returns a decimal representation of a character-string. The value of this second argument specifies the precision of the result. The scale value specifies the scale of the result (not sure what that means?).


texts: Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

texts:
Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face.
Our data model consists of 3 entities, which shall be labeled as A, B and C. In order to keep things simple, all their attributes will be of int type.
Entity A has following attributes: D, E and X;
Entity B has following attributes: D, E and Y;
Entity C has following attributes: D and Z;
Since all entities share common attribute D, I have decided to apply type/subtype design.
Important: Entities are mutually exclusive! This means that entity is either A or B or C.
Problem:
Entities A and B have yet another common attribute E, but this attribute is not present in the entity C.
Question:
I would like to use the above described characteristic to further optimize my design, if possible.
To be honest, I have no idea how to do this, nor where to start trying, hence this post.


texts: How to use aes-encryption in PostgreSQL?

texts:
I tried aes-encryption by using following statement:

SELECT encrypt('test', 'key', 'aes');

which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm not sure if that was the right way.

SELECT decrypt(pw, 'key', 'aes') FROM table WHERE ID = 1;

gives me the error

  ERROR:  function decrypt(bytea, unknown, unknown) does not exist
  LINE 1: SELECT decrypt(pw, 'key', 'aes') FROM tabelle WHERE ID = 7;
                 ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Does that really mean that encrypt() is an existing function, but not decrypt()? How else could I retreive aes-encrypted values?


texts:
Guys. When I tried to import a .dmp file into a fresh oracle database (sqlplus), I got this error.
dump file "exp_tbs_gxplt01282019.dmp" may be an Data Pump export dump file

My step is:

SQL> create user CLIENT_TEST identified by client_test_password;
SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST;
SQL> exit

Then,

imp CLIENT_TEST/client_test_password@hostname file=file_name.dmp full=y;

Then, I got that error mentioned. I checked out the similar posts from stackExchange and seems follow the instruction exactly the same, but no luck.


texts: Slow Full Text When using multiple full text searches

texts:
Thank you in advance for your help.   I have been going bonkers.

We recently made a change to our code by adding an additional contains to the below query.  This has had a real negative impact on performance.  When we remove contains or the other, the query returns right away.

select *
from dbo.company p with (nolock)
  join dbo.alt_company o  with (nolock) on p.org_id = o.org_id
where CONTAINS (p.description, '"phrase1*" OR "phrase2"')
  or  CONTAINS (o.description, '"phrase1*" OR "phrase2"')

Why would having the second contains create such havoc on the performance.   The problem is then made worse by the number of keywords.   I have re-written the query in such a way there is only one contains and it works pretty good.  I just want to make sure I understand what's happening.

Thanks,
Pete


texts: Add a column to a table and then "insert into" is invalid in a stored procedure

texts:
I have a problem similar to this SO question.

I have three tables that are related with an identity column as primary key. And I need to clone them into a new database with new identity values.

These are the tables where I'm going to copy the data, but the relationship is the same in the source database.

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...]
)
CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)
CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

There is a master table, Code, and the other tables related to the master by its primary key Code.CodeId.

I'm going to add an auxiliary column, with the value of the primary key in the source database, to make the conversion in tables Aggregation and AggregationChildren.

That process work in SQL Server Management Studio, but when I try to use it in a procedure stored it complains about that the auxiliary column doesn't exist.

The stored procedure is like this pseudo code:

// Alter table to add the auxiliary column
// Insert into with select and inserting into the auxiliary column
// Alter table to drop the auxiliary column

The stored procedure has an error because I'm inserting in a column that doesn't exist.

Do you know if there is another solution to this problem using an auxiliary column?


texts:
In a legacy project I've the following query:

SELECT
    concat(SR_PO, '-', SR_RlsSequence) as Order,
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')
GROUP BY by concat(SR_PO, '-', SR_RlsSequence)

But its too expensive, especially when the IN parameters are lots. Any ideas concerning an optimization? please consider that I cannot use a stored procedure.

Estimated Query plan is uploaded here.


texts: Do not reject entire statement when deleting rows in batch

texts:
I have a table who's purpose is to store the application's various images. It is defined this way :

CREATE TABLE images
(
  id lo NOT NULL,
  name character varying(1024) NOT NULL,
  type character varying(32) NOT NULL,
  dimension point,
  last_modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT "PK_images" PRIMARY KEY (id)
)

Some other tables have foreign keys referencing images.id with constraints that an image should not be deleted if it is referenced somewhere (i.e. the app is responsible to properly delete the reference first);

...
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
   REFERENCES images (id) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE RESTRICT
...

Since many table rows may reference the same image, I need to automatically cleanup images that no longer have any reference.

My first idea was to implement the application to delete all foreign key references, then remove all images matching the values of these deleted foreign keys. To do so, I'd create a trigger on the table images, before delete, that could "swallow" all constraint exception if a query such as only "orphaned" images would be deleted and the others would be silently ignored. I'm just not sure how to properly do that.

If that idea is not good, what could be other (if not better) alternatives?

Basically, if I'm executing a query like

DELETE FROM images WHERE id IN (101, 102, 103, 104, 105)

and images.id's 102 and 104 have constraints that prevents them to be deleted, I'd like all other images to still be deleted. The DELETE query should be simple as the one above.

Thank you.


texts: Are relations slower than a big, inefficient table?

texts:
I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a "student" table should have at least 8 empty fields (e.g telephones: telephone1, telephone2, telephone3...) instead of my suggestion - a "telephone" table which holds a telephone number (and possible other metadata) and the foreign key is the student id number. My boss says that it's better to store them that way because "there are less CPU cycles and that matters in web platforms", instead of using relations. I say that, in the worst of cases, it's negligible.

In that example, using relations (suppose that the tables are filled with lots of records in a medium-sized webapp) is notably slower than using that kind of table schema?


texts:
If I use the following selection

SELECT * FROM users WHERE id IN (3,1,2)

Can I expect the result to be in the same order like the provided ids?


texts:
When working with the json datatype, is there a way to ensure the input JSON must have an element?

I don't mean primary, I want the JSON that gets inserted to at least have the id, name element. It can have more but at the minimum those two must be there. Can this be done?


texts:
I want to store data of customers and employees. What I am having trouble deciding is what is the correct thing to do:

a. Option a: Two tables - Customer and Employee
b. Option b: Three tables - Person, Customer, and Employee

So for Option b, what I had in mind was that common properties of customers and employees like FirstName, LastName, etc. are on Person then related to Customer or Employee if he/she is a customer or employee (or both). Then Customer and Employee table would only have fields specific to them. I know I would need to join to query with Option b but I don't think that is such a big deal.

I'm not sure if Option b is appropriate or I'm mixing OOP concepts with designing so I wanted to ask in the perspective of someone who thinks only for the database in the context of designing, not specifically for performance.


texts: Is there a fast way to change column type without dropping the clustered index?

texts:
I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate the index is extremely slow.

Is there a way to speed this process, or by design this is the only to change the data type?

[Update] Just want to elaborate a bit about my question, the column that I like to update is a date dimension key. It is currently a bigint (8 bytes), and I would like to convert it to int (4 bytes). This should reduce the size of the database, and it should theoretically improve the database performance in general.


texts: Full text search on multiple tables in PostgreSQL

texts:
I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...).

Is it possible to have a FTS implementation in PostgreSQL that will get the item searched for in more than one table and get the results?

For example, if the user typed 45, the query should look in the clients table and return the clients with id 45 or name has 45, also look in the invoices table and return the invoices with number 45, and also look into the delivery notes table and return the delivery notes with number 45.

Or is there a better way of solving this problem without using full text search?


texts: Is it possible to setup a CREATE_LOGON trigger that treats AD Groups differently from users?

texts:
Just started in a DBA role with a new organization and they gave me a bit of a head-scratcher. We've got a trigger on CREATE_LOGON that DENYs VIEW ANY DEFINITION & VIEW ANY DATABASE. This was setup before I joined the organization but I believe it was done in response to some sort of penetration testing. I've been asked to look into editing the trigger to not fire when adding logons for AD Groups.

Sample Code

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [tr_NewLogin]
ON ALL SERVER
FOR CREATE_LOGIN
AS

DECLARE @SQL_Err        INT
DECLARE @data           XML
DECLARE @ObjectType     NVARCHAR(2000)
DECLARE @ObjectName     NVARCHAR(2000)
DECLARE @Value          NVARCHAR(2000)
DECLARE @SQL            NVARCHAR(2000)
DECLARE @LoginName      NVARCHAR(500)
DECLARE @YN_AD_Group    NCHAR(1)

SET @SQL_Err     = 0
SET @data        = EVENTDATA()
SET @ObjectName  = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)');
SET @LoginName   = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)');
SET @YN_AD_Group = (SELECT TOP 1 CASE sp.type WHEN 'G' THEN 'Y' ELSE 'N' END FROM sys.server_principals sp WHERE @LoginName = sp.name);

BEGIN
    IF @YN_AD_Group = 'N'
    BEGIN
        SET @SQL = 'DENY VIEW ANY DEFINITION TO ['+ @ObjectName + ']'
                + ' DENY VIEW ANY DATABASE TO ['+ @ObjectName + ']'
        PRINT @SQL
        EXECUTE @SQL_Err = sp_Executesql @SQL
    END
END
GO
ENABLE TRIGGER [tr_NewLogin] ON ALL SERVER
GO

From looking at the EVENTDATA schema, I'm not seeing anything that stands out as a way to definitely tell a login is an AD Group. LoginType only seems to return either SQL Login or Windows NT Login.

Attempting to check sys.server_principals doesn't work either, I'm guessing because that part of the code is being evaluated before the login is added so the data doesn't exist there yet. Is there someway to change when that gets evaluated in the trigger? Or is there some other way to have this trigger not apply to AD Group logins?

Update 2019-06-19: Thanks to all the comments on this question and the suggestions for some interesting workarounds. I've checked with the original requester of this change. They've decided that while this might be a clever idea; it's not worth exploring odd/kludgy hacks to make it happen. My thanks to those of you who took the time to look over this question.


texts: My company recently turned on SMB digital signing on the Windows 2012 r2 server that runs SSIS. This has had a severe impact on data transfer performance. The security team did this and I can't get them to turn it off. Are there any workarounds? How can I prevent SMB digital signing from killing SSIS performance?


texts: Why is casting a string to DATETIME not accurate to the ms?


texts: I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard code query. Is there any way to do this without using the GUI?


texts: Why does this query return a result in MySQL but not in php?

texts:
I have a query that isn't returning a result in php for a certain search term (that I believe it should), but when I test it directly in MySQL, it does return one. Other search terms return fine.

SELECT id,
  CONCAT_WS(' ',
            TRIM(SUBSTRING_INDEX(
                 SUBSTRING(`text`, 1, INSTR(`text`, 'rig') - 1 ),
                 ' ',
                 -8)
            ),
            TRIM(SUBSTRING_INDEX(
             SUBSTRING(`text`, INSTR(`text`, 'rig') ),
             ' ',
             7)
        )
  ) as `sample`, `title`, `url`
FROM `documents` WHERE MATCH(text)
AGAINST('"rig"' IN BOOLEAN MODE ) OR MATCH(title)
AGAINST('"rig"' IN BOOLEAN MODE )

PHP:

if (!$result=$mysqliSiteSearch->query($query)) send("3149","e");
$hitArray=array();
while ( $row=$result->fetch_assoc() ) { $hitArray[]=$row; }

I set up a .txt log file to record the result of:

print_r($result,true)

I tested a different search term which returns 8 results both directly in MySQL and when running the php script, and my log file shows:

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 4
    [lengths] =>
    [num_rows] => 8
    [type] => 0
)

I then test the search term "rig", which, when testing the same query directly in MySQL, returns 1 row, but when running my php script, I get no hit, and my log file shows:

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 4
    [lengths] =>
    [num_rows] => 0
    [type] => 0
)

Clearly I'm overlooking something, but what please?

Thank you for your time and help.


texts: PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet

texts:
I am trying to connect cloud sql with pgadmin but it throws error when I try to execute query.

  "Could not send Data to server, socket is not connected, could not
  sent SSL negotiation pocket"

But When I connect with different Database tool like DBViz / Psql, I can execute the query - no such error in other tools.
I am kind of lost in the error, could you please help me to resolve this.


texts: NOLOCK or Read Uncommitted locking / latching behaviours

texts:
I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken.

When using a SELECT with NOLOCK or in the Read Uncommitted Isolation level, is the only lock taken out a Schema Stability Lock, or do shared locks get taken on a rolling basis as the query works through the rows? (Obviously these locks would need to be dropped straight away)

What about latches? How are the pages in memory dealt with as I assume referencing an in-memory object that's in the middle of being modified isn't allowed?


texts: Is it possible to gain the precise position of a live mysql database when performing full backup

texts:
I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups.

The production server is on the west coast while business hours are on the east coast.

When I perform the fullback up I query the master for is log and position. I put these values in the filename of the zip that I compress the databases to.

I'm having difficulties using mysqlbinlog to create incremental sql file from a certain position that I know the backup has ended at.

Would running Show Master Status then then running mysqldump --flush-logs --single-transaction... suffice to get accurate information in order to restore.

I know the ideal setup is to backup a slave database since locking won't hurt the production environment but at the moment I haven't made the switch.

Am I not getting the desirable results because I'm not locking all the tables?

Thanks in advance

EDIT

I realized with the help of RolandoMySQLDBA that I wasn't pointing to the appropriate binlog file. I was instead pointing to the whole directory binlog/bin-log.[0-9]* > sql.txt. So with just the position I was hitting logs that started much earlier than desired. So I changed my script to pull the appropriate binlog file as well as the position and that worked like a charm.

Thanks


texts: Clustered Index improvemens for particular queries

texts:
I found a table that looks like this:

 CREATE TABLE [dbo].Table1 (
  id INT primary key IDENTITY (1, 1),
  [idUser] INT  NOT NULL ,
  [Amount] INT NOT NULL ,
  [Attempts] INT NOT NULL ,
  [date] [datetime] NOT NULL ,
  [SUM_Amount] INT NOT NULL
 ) ON [PRIMARY]

This table is created and populated with aggregated data for a particular period by a job.

Particularities:

This table will hold up to a million rows
idUser is unique
sum_Amount is running total of previous rows of amount.

This table will last as is, no update or delete or insert operation.
Just this type of queries:

 select top (@n) * from table1
 order by [SUM_Amount] desc, [Attempts] desc

 select top (@n) * from table1
 where [SUM_Amount] >=@m order by [SUM_Amount] asc

I think it will improve preformance to change to a clustered index like this:

 CREATE TABLE [dbo].Table2 (
  id INT IDENTITY (1, 1),
  [idUser] INT  NOT NULL ,
  [Amount] INT NOT NULL ,
  [Attempts] INT NOT NULL ,
  [date] [datetime] NOT NULL ,
  [SUM_Amount] INT NOT NULL

 CONSTRAINT [PK_Nueva]
   PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)

 ) ON [PRIMARY]

I read that using a no unique clustered index will add a 4 bytes hidden column (http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx), so I decide to add Identity to cluster index (not sure if it is the right approach)

I want to ask (at the risk of sound ridiculous, but need to be sure):

How could it be improved?
Will I have an impact on disk size?
Should I rebuild the index once all data have been inserted?

EDIT:

About id, I think is there just as a bad habit. I'll kept it, not sure how previous job calculate running total (I've no access to it)

There are a lot of tables like this, like hundreds for each day(don't ask me why). That is why DBA team ask me to not create a new index because of size issues. That is why I thinking of rearrange table structure via clustered index. Also changing data types which exceeds normal ranges.


texts: Recursive Parent-Child Location Relationship Cache as View

texts:
Here is my data structure:

CREATE TABLE Locations(
    LocID int IDENTITY(1,1) NOT NULL,
    LocationTypeID int NOT NULL,
    ParentID int NULL,
    LocNum varchar(50) NULL,
    LocName varchar(250) NULL,
)

CREATE TABLE LocationTypes(
    LocationTypeID int IDENTITY(1,1) NOT NULL,
    TypeName varchar(50) NOT NULL
)

The LocationTypes table contains the following values:

+----------------+----------+
| LocationTypeID | TypeName |
+----------------+----------+
|              1 | Campus   |
|              2 | Building |
|              3 | Area     |
|              4 | Floor    |
|              5 | Room     |
+----------------+----------+

The Locations table contains a bunch of locations, some where the ParentID is null (Campuses), and others that have a ParentID. There are 5 levels of hierarchy to my data structure, indicated in the LocationTypes table. Campus (1) being the least specific, and Room(5) being the most specific. In my database I have other tables which reference the LocID, most often these tables are referencing Locations with a LocationTypeID of 5, but not always.

I have a need to get the names of every Location in the hierarchy for display to the end-user.

Ultimately what I want to get is a view (or cached table) with the following structure:

+-------+---------------+-----------------+-------------+--------------+-------------+
| LocID | CampusLocName | BuildingLocName | AreaLocName | FloorLocName | RoomLocName |
+-------+---------------+-----------------+-------------+--------------+-------------+

I realize that some of these columns will end up being null, for Locations with a LocationTypeID of anything other than 5, and that is perfectly fine.

I know that I probably need a recursive common table expression, I just don't really know where to start.

Basically the pseudo-code logic for this is:

Foreach Location in the LocationsTable
Get the Parent Location of each Location, continue recursively until there are no more parents
Store this cached data in a view, so that it can be easily LEFT JOINed on any other table that references a LocID

I hope that all makes sense. Thanks anyone that can help me.


texts: Update all tables containing column name where the field is something specific

texts:
I know a similar question has been asked before but i was wonder if there is any way that you could run an update across all tables in a database where a column name exists but only for fields with a specific entry.

So for example UPDATE <table> SET <column_name> = '123' where <column_name> = '124'

Thanks in advance for everyones help


texts:
SQL> set oracle_sid=sid
SP2-0735: unknown SET option beginning "oracle_sid..."
SQL> emctl start dbconsole;
SP2-0734: unknown command beginning "emctl star..." - rest of line ignored.
SQL> set oracle_sid=sid emctl start dbconsole;
SP2-0735: unknown SET option beginning "oracle_sid..."
SQL> SQL> set oracle_sid=sid
SP2-0734: unknown command beginning "SQL> set o..." - rest of line ignored.
SQL> SP2-0735: unknown SET option beginning "oracle_sid...

why this service is invoked??can you tell that?


texts: How to store bitcoin value and other currencies values together in MySql database?

texts:
I need to store bitcoin value and other currencies values together , I have a decimal column (16,8) for amounts. It behaves normally for bitcoin values , but when I want to store other currencies like Usd or Eur It becomes like this eg. 100 Usd becomes 100.00000000 .  0.50 Usd becomes 0.50000000

My question is should I store bitcoin values and other curriencies values in same column?
I will have millions of rows, is it bad for performance (read , write , count ) or this is just cosmetic issue?