texts: Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory?

texts:
Here is my query, it takes 2.7 sec:

SELECT t2.id, t1.id FROM sample t1
INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex;

The associated EXPLAIN (ANALYSE,BUFFERS) output:

Nested Loop (cost=0.00..1108.30 rows=356 width=8) (actual time=56.130..2740.906 rows=33 loops=1)
 Join Filter: (t1.data ~* regex_set.regex)
 Rows Removed by Join Filter: 65967
 Buffers: local hit=18
 -> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.014..1.534 rows=2000 loops=1)
    Buffers: local hit=17
 -> Materialize (cost=0.00..1.08 rows=33 width=36) (actual time=0.000..0.004 rows=33 loops=2000)
    Buffers: local hit=1
    -> Limit (cost=0.00..0.59 rows=33 width=36) (actual time=0.005..0.014 rows=33 loops=1)
       Buffers: local hit=1
       -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.008 rows=33 loops=1)
          Buffers: local hit=1
Planning time: 0.129 ms
Execution time: 2740.952 ms

The same query with limit set to 32 takes only 0.25 sec with same plan (there is nothing special with row 33: tested limit 32 offset 1, had the same result as below):

Nested Loop (cost=0.00..1075.88 rows=345 width=8) (actual time=5.871..255.315 rows=32 loops=1)
 Join Filter: (t1.data ~* regex_set.regex)
 Rows Removed by Join Filter: 63968
 Buffers: local hit=18
 -> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.008..0.498 rows=2000 loops=1)
    Buffers: local hit=17
 -> Materialize (cost=0.00..1.05 rows=32 width=36) (actual time=0.000..0.002 rows=32 loops=2000)
    Buffers: local hit=1
    -> Limit (cost=0.00..0.57 rows=32 width=36) (actual time=0.003..0.013 rows=32 loops=1)
       Buffers: local hit=1
       -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.003..0.006 rows=32 loops=1)
          Buffers: local hit=1
Planning time: 0.109 ms
Execution time: 255.374 ms

Here is the initialisation script I used for these tests.

DO $$
  DECLARE s_length INT=2000;
  DECLARE r_length INT=50;
BEGIN
    DROP TABLE IF EXISTS sample;
    CREATE TEMP TABLE sample AS
      SELECT generate_series(1, s_length) id, md5(random() :: text) AS data;

    DROP TABLE IF EXISTS regex_set;
    CREATE TEMP TABLE regex_set AS
      SELECT g.id, s.data regex
      FROM generate_series(1, r_length) g (id),
           sample s
      WHERE s.id = round(g.id * s_length / r_length);
END $$;

With my real data, I had a delay at exactly the same 33 limit (the time difference was 1 to 30).

I'm running Postgres 10 on Docker container (2 cpu / 2GB RAM) with following (arbitrary) edits to default postgres.conf (didn't have much effect):

shared_buffers = 512MB
temp_buffers = 32MB
work_mem = 16MB (also set to 64MB, had no effect)

Equivalent query is a lot faster on default MySQL 5.8 container.

Is there any Postgres master that could explain what is happening?


texts: datetime index skipped for same query different params


texts: How to turn on GTID on MariaDB if already using binary/position replication?

texts:
MariaDB 10.3
current master config
[mysqld]
log-bin
server_id=1
binlog-format=mixed
expire_logs_days=10

current slave config
[mysqld]
log-bin
server_id=2
binlog-format=mixed
expire_logs_days=10

I can stop both master and slave if needed for changing replication method.
So questions are:

Do I need to stop replication and application and then execute "CHANGE MASTER TO MASTER_USE_GTID = slave_pos" Is it enough? Should I purge bin logs?
If so how do slave knows about where master server is?
Should I changed binlog-format to "row"?


texts: Update MySQL Stored Procedure Definers on shared environment

texts:
I am accessing a MYSQL database through PHPMyADMIN.

My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL Workbench. Routines created on Workbench are visible in PHPMyAdmin, but cannot be viewed or opened there or vice versa.

I believe the issue lies with the Definer that was generated upon the Routine's creation.

When using PHPMyAdmin, Routines are created with definer 'dbname@localhost'.

But when done through Workbench, this varies I assume based on the Network I am currently connected to. The format of the stored procedures created outside of PHPMyAdmin are 'dbname@ipaddress'.

I am trying to change all the Definers to 'dbname@localhost', in the hopes that I will be able to open and manipulate all Routines from PHPMyAdmin.

Running any queries on mysql.* is not an option because the database is hosted in a shared environment and I don't have root permissions on the database server.

I also tried updating INFORMATION_SCHEMA but this also proved fruitless.
I have contacted Support and was told I may be able to find more help here.

I hope this was descriptive enough.
Is there any other way I will be able to update these definers?


texts:
How to write a procedure /job to kill all session running for more than 5mins in Oracle?

So, I query v$session and find out that for a particular user, program pair if the session has been active for more than 5mins kill it.

Also, how to write a job to run this procedure for that pair every minute until 5mins and then kill the process (by sid) ..

create or replace procedure kill_session( p_sid in number, p_serial# in number )
as
begin
    for x in ( select *
                 from v$session
                where username = USER
                  and sid = p_sid
                  and serial# = p_serial# )
    loop
        execute immediate 'alter system kill session ''' ||
                 p_sid || ',' || p_serial# || '''';
        dbms_output.put_line( 'Alter session done' );
    end loop;
end;

How to check the time if the session has been active for more than 5mins and how to make it run till 5min for a particular user, program pair?


texts: Transactional replication from Oracle PL/SQL to MS SQL Server broke from the data in a Date column but previous records with same date are ok?

texts:
I have a heterogenous transactional replication subscription setup between PL/SQL (12C 12.1.0.1.0) and SQL Server (2008 R2), where the Oracle PL/SQL server is the publisher and the MS SQL server is the subscriber.

I have an alert setup on the MS SQL server for replication failures, and today the replication failed with the following error:

  DESCRIPTION: Replication-Replication Transaction-Log Reader Subsystem:
  agent OracleServer/OracleServer-OracleServer/OracleServer-37 failed.
  An error occurred for column 'DATE31', when processing changes for
  article 'PAGE_THREE', of publication
  'OracleServer_Transaction_Replication'.

I was able to determine the source record of the issue by sorting on an updated date column on my MS SQL server and looking at the updated date and record ID and then comparing to the same type of query on my Oracle PL/SQL server to find the next record that didn't get replicated. I then added a filter on the publication object on my MS SQL Server to filter out that problematic record's record ID. This caused my transactional replication to start working as normal again.

What I don't understand is the DATE31 column (it's PL/SQL datatype "DATE") of the problematic record has the same exact date in it as the last non-problematic record before it (the one that replicated fine.)

(Don't mind the aliases):

Record #7 above (ID = 61216226) is a good record that replicated over.
Record #6 (ID = 61215965) is the bad record that broke replication and caused the error I quoted above.
All the other fields for these two records in this table are null, so it's definitely this DATE31 column that's the issue.

What gives?


texts: Do Availability Groups support Active/Passive?

texts:
I am looking into the licensing of SQL Server 2012 when using Availability Groups with 2 servers.
It is not clear to me if Availability Groups are only available as Active/Active and not Active/Passive.
For reference I found a couple paragraphs in the SQL Server 2012 Licensing Reference Guide but it doesn't mention passive servers with respect to Always On.

Licensing SQL Server for High Availability SQL Server software can be
configured so that if one server fails, its processing will be picked
up, recovered and continued by another server. All editions of SQL
Server 2012 provide basic high availability features including backup
log shipping, database mirroring and two-node failover clustering.
Advanced (AlwaysOn) high availability features in SQL Server 2012
Enterprise Edition include enhanced support for multiple, active
(readable) secondary servers and support for multi-site failover
clustering. Log shipping and database mirroring take place at the
database level, whereas failover clustering takes place at the SQL
Server instance level.
AlwaysOn Availability Groups New for the SQL Server 2012 Enterprise
Edition, AlwaysOn Availability Groups enable customers to configure
multiple databases that will failover as a unit, with support for up
to four active secondary servers and two synchronous secondary
servers. The ability to use secondary servers for more than just
passive failover support can improve the performance of primary,
reporting and backup workloads due to better balancing of workloads
across instances, helping to provide better return on hardware
investment. Note: When secondary servers are actively used to support
these additional workload scenarios—that is, when the servers used for
failover purposes are no longer truly passive—they must be fully
licensed accordingly.


texts: Changing data type from DATE to CHAR(10) with a query

texts:
I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.

I want to alter all columns of that type in any table in this database.

How can I do this?


texts: SSMS 2014 "Deploy Database" feature has old SQL Azure settings

texts:
I'm attempting to test-deploy an existing SQL Server 2005 database into SQL Azure using the "Deploy Database to Windows Azure SQL Database" task in SQL Server Management Studio 2014.

When the wizard launches, it has incorrect (outdated) database settings available:

SQL Azure no longer uses "Web" and "Business" models, and when I try to perform the deployment it fails with the error message

  The service objective 'Business' specified is invalid. (Microsoft SQL Server, Error: 40804)

I receive the same error when I attempt to run "Import Data-tier Application" using a .bacpac of my database. The settings are also old for that interface.

It appears SSMS is using an older deployment wizard. How do I correct this?

Edit:

I fired up a SQL Server 2014 VM on Azure and tried performing the same task. I observed the VM version of SSMS has a newer version number (right) than my local copy of SSMS (left):

I see no pending updates from Windows Update, however.


texts: JDBC Exception JZ0BE: BatchUpdateException: A user with System Administrator (SA) role must reconfigure the system to enable Java

texts:
I am using Sybase JDBC JConnect3 driver to insert large data in a table using Batch Insert feature of JDBC.
I am preparing a batch of 100 inserts and executing the batch. But it is throwing below error while executing batch query.

  com.sybase.jdbc3.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java.
   at com.sybase.jdbc3.jdbc.ErrorMessage.raiseBatchUpdateException(Unknown Source)
         at com.sybase.jdbc3.jdbc.SybStatement.handleBatchException(Unknown Source)
         at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeBatch(Unknown Source)
         at com.loader.LoadTable.loadDataToOracleFirst(LoadTable.java:367)
         at com.loader.LoadTable.fetchDataFromSybaseFirst(LoadTable.java:246)
         at com.loader.LoadTable.main(LoadTable.java:157)

Can anyone tell me excatly what I need to configure to resolve above issue?


texts:
I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know:

Assume the relation GTX {G,T,X} with the following dependencies:

G,X -> T
T -> X

or this one:

Relation {W,X,Y,Z}:

X,Y -> Z
Z -> W

So to my question, how do I properly draw a dependency diagram for
this kind of relations? (tutorials or/and explanations are much
appreciated)
How do I see what the primary key is? (I know what a pk is)
In which normal form are the relations?

I have never been able to understand this fully and always got confused by the tutorials I found. I appreciate all answers!


texts: sysadmin cannot drop login when ddl audit trigger enabled

texts:
I have a DDL trigger defined for database- and server-level events in SQL Server 2008R2 (Standard) which exists to log all DDL events into an audit table in an admin database.  It's only function is to extract the relevant data from EVENTDATA() and insert into this table.  For the purpose of the insert (as only sysadmins can access the admin database), I have created a dedicated SQL login with only INSERT permission to this table, and granted IMPERSONATE on this login to public.  This is intended to prevent permissions-related errors from the trigger firing and attempting to insert into the audit table, when the caller does not have the necessary access to the database/table.

Here is the trigger definition:

CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()

EXECUTE AS LOGIN='<dummy login>'
    INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
    VALUES (
       GETDATE(),
       CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
       @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
       CONVERT(NVARCHAR(100), HOST_NAME()),
       @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
       ) ;
REVERT
END

I have had no issues with this trigger since implemented months ago.  However, now it appears to be preventing even a sysadmin from executing an ALTER LOGIN, DROP LOGIN, etc. under certain circumstances as follows:

My environment also includes MS Dynamics GP 2010 (Great Plains). Great Plains allows an admin to manage users, and for each new Great Plains user, the software creates a SQL login for that user in SQL Server.  Resetting a password in the Great Plains interface resets the SQL password.  And so forth...

However, even if logged into Great Plains as 'sa' as long as the above trigger is enabled any attempt to alter or drop a login fails with error 15151 (Cannot alter the login 'loginname', because it does not exist or you do not have permission).  If I disable the trigger, everything works normally.

The same operations executed in SSMS, or through some other interface, are successful, even for non-sysadmins who have some level of DDL permissions.  It only fails when performed in Great Plains.  A profiler trace of the operation shows that GP is merely submitting a standard T-SQL 'ALTER LOGIN' or 'DROP LOGIN' statement, and that the statement correctly shows as called by the sa account.  It does not appear that the session ever switched to a different context, other than for the insert into the audit table (which it never actually got to, as no record was logged for the statement).  And just in case the session somehow was maintaining the wrong context after that impersonation, I tried making the dummy-insert login a sysadmin with no success.

My question is, are there certain combinations of SET options/connection settings, etc. that could result in this type of behavior or issues with DDL trigger execution that could prevent a sysadmin from performing certain operations?  Or is there some other avenue to investigate that I am completely missing?


texts: User SYS logged on from. Where I can find source of access?

texts:
I´m receiving alerts from EM.

Host=Teste.com
Target type=Database Instance
Target name=REF.Teste.com
Categories=Security
Message=User SYS logged on from kali.
Severity=Warning
Event reported time=Mar 8, 2017 10:11:04 AM BRT
Target Lifecycle Status=Production
Comment=TI DEV-DV
Operating System=Linux
Platform=x86_64
Event Type=Metric Alert
Event name=UserAudit:username
Metric Group=User Audit
Metric=Audited User
Metric value=SYS
Key Value=SYS_kali
Key Column 1=Audited User - Host
Rule Name=Unavailability,Events
Rule Owner=SYSMAN
Update Details:
User SYS logged on from kali.

Where I can find the logs of this audit user?
"User SYS logged on from kali". What would this kali be?

thanks


texts: Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

texts:
I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right direction? For example, can I use any kind of query hints / options to improve performance here?

Currently it runs in about 40 minutes and returns about 30,000 records.

Note: I'm also interested if there's new features in 2019 that apply here that don't apply in 2008 R2 (I know one big difference between the two is the Cardinality Estimator).


texts: Postgres - How to get all the values from a field that it repeats on every json from an array?

texts:
I'm storing a lot of json from some websites on PostgreSQL 9.5 in the jsonb format. One of the fields is an array of Jsons that have the same information. Here is an example.

[
  {
    "text": "JIMIN",
    "indices": [
      16,
      22
    ]
  },
  {
    "text": "PUMA",
    "indices": [
      23,
      28
    ]
  }
]

I want to retrieve all the text values, that is, I want to make a query that gives me JIMIN and PUMA on this example. I can't find a way to accomplish that with the commons operators (like #>>). Is there a way to accomplish this?


texts: Naming a second additional column referencing another table column?

texts:
I'm working with a table in database that references to a column in another table.
Such as:

Product Table
------------
Id
Description
Currencies_Code

Currencies
------------
Id
Code
Description

We have the convention:

"Foreign key fields should be a combination of the name of the referenced table and the name of the referenced fields."

MyColumn: ExternalTableName_ColumnName

Until there, everything is fine. Now, a second column should be added referencing to Currencies table "Code" column for a second scenario where another currency should be used.

My initial idea was using these columns:

Currencies_Code_Internal

Currencies_Code_External

However, the suffix Internal/External breaks the convention.

Which way of naming would be the most appropiate in this case to change the convention?


texts: Hash Match inner join in simple query with in statement

texts:
I am running the execution plan for the following query:

select m_uid from EmpTaxAudit
where clientid = 91682
and  empuid = 42100176452603
and newvalue in('Deleted','DB-Deleted','Added')

Here is the execution plan:

I have a non clustered index on EmpTaxAudit Table on ClientId and NewValue columns which shows above as 14.9% of the execution:

CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].

[EmpTaxAudit]
(
    [ClientID] ASC,
    [NewValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I also have a non clustered unique index PK as follows:

ALTER TABLE [dbo].[EmpTaxAudit] ADD  CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED
(
    [ClientID] ASC,
    [EmpUID] ASC,
    [m_uid] ASC,
    [m_eff_start_date] ASC,
    [ReplacedOn] ASC,
    [ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Trigger code in source table EmpTax:

CREATE                trigger [dbo].[trins_EmpTax]
on [dbo].[emptax]
for insert
as
begin
  declare
    @intRowCount  int,
      @user          varchar(30)

  select @intRowCount = @@RowCount
  IF @intRowCount > 0
  begin
     select @user = suser_sname()

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is null

     insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
     select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Deleted'
        from inserted i
         where m_uid not in (select m_uid from EmpTaxAudit
                    where clientid = i.clientid  and (newvalue = 'Deleted'
            or newvalue = 'DB-Deleted'
                        or newvalue = 'Added') and  empuid = i.empuid)
       and i.m_eff_end_date is not null

  end
end

What can I do to avoid the high cost of Hash Match (Inner Join)?

Thanks!


texts: How to set default schema for PostgreSQL debugger?

texts:
I have 3 schemas: public, meta, data.

While debugging large function from "data" schema in PgAdmin III or SQL Manager for PostgreSQL, debuggers doesn't see any tables or functions from the same schema.

Cause function is large and depends on others functions it's not effective to use raise notice or writing schema prefix.

How can I set default schema to debug it correctly? I guess public schema is default.


texts:
I have this SQL query:

SELECT parent_category FROM category
JOIN
(
    SELECT category_id FROM venue
    JOIN
    (
        SELECT venue_id FROM city_venues
        WHERE city_id = 3
    ) AS venues
    WHERE venues.venue_id = venue.venue_id
) AS venue_categories_list
WHERE venue_categories_list.category_id = category.category_id;

This command gives me exactly the results I want but I have noticed that it's execution is very slow (100-200ms) even with small datasets. Since I want this query to be part of a trigger, I want it to be as efficient as possible. Any ideas on how to make this faster ?

EDIT:

The tables related to this query are the ones below:

CREATE TABLE queries:

-- -----------------------------------------------------
-- Table `app`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`category` (
  `category_id` CHAR(24) NOT NULL,
  `category_name` VARCHAR(50) NOT NULL,
  `category_plural_name` VARCHAR(50) NOT NULL,
  `parent_category` CHAR(24) NOT NULL,
  `url_icon` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `fk_parent_category`
    FOREIGN KEY (`parent_category`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`venue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`venue` (
  `venue_id` CHAR(24) NOT NULL,
  `venue_name` VARCHAR(100) NOT NULL,
  `address` VARCHAR(100) NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  `category_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`venue_id`),
  INDEX `fk_venue_category_idx` (`category_id` ASC) VISIBLE,
  CONSTRAINT `fk_venue_category`
    FOREIGN KEY (`category_id`)
    REFERENCES `app`.`category` (`category_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`city`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city` (
  `city_id` INT NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(50) NOT NULL,
  `lat` DECIMAL(16,14) NOT NULL,
  `lng` DECIMAL(16,14) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE INDEX `city_name_UNIQUE` (`city_name` ASC) VISIBLE)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `app`.`city_venues`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `app`.`city_venues` (
  `city_id` INT NOT NULL,
  `venue_id` CHAR(24) NOT NULL,
  PRIMARY KEY (`city_id`, `venue_id`),
  INDEX `fk_venue_idx` (`venue_id` ASC) VISIBLE,
  CONSTRAINT `fk_hasVenues_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `app`.`city` (`city_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_hasVenues_venue`
    FOREIGN KEY (`venue_id`)
    REFERENCES `app`.`venue` (`venue_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I use MySQL Workbench to generate the queries for creating the schema. I use InnoDB as the engine.

EXPLAIN Output:

What I want to do with this query is to return all parent categories IDs' for each venue a city has. I care about duplicates as I want to count how many venues of each category does a city have. I would appreciate if you could give me hints about how I can do multiple counts also ( return columns with each category metrics).


texts: Get single records when duplicate records exist in 2 table

texts:
I have 2 tables. TableA is the sales and TableB is the leads table. I'm trying to see if the people in the leads TableB made a purchase and their name appears
on the sales TableA. If they do I only want the name of people who appears
on both tables.

TableA (Sales)

 customer   school            email     address    state    phone   Sales
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $500
 john doe   hight middle    111@hu.z   111 updown   wi     2222222  $100
 zow hi     hight middle    222@hop.c  111 updown   wi     2222222  $200
 calispo    dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000
 Jordan     clift high       jp@hy.zut  1 unversity GA     11111111 $100

TableB (Leads)

 customer       school           email       address    state
 john doe      hight middle    111@hu.z    111 updown     wi
 Kawi          hight middle     k@nop.c     111 updown    wi
 calispo       dwight elem    s@dw.co.za   34 jimm elis   CA

What I want to see:

 customer   school           email       address    state
 john doe   hight middle    111@hu.z   111 updown   wi     2222222   $500
 alispo     dwight elem     s@dw.co.za 34 jimm elis CA     35450456 $1,000

I try this query but it is returning duplicate of the info that I want

 SELECT a.email as [sales_school]
  ,b.email[leadds_insti]
  ,a.*
  ,b.*
  FROM [dbo].[Sales] a
  right JOIN leads b ON a.email= b.email


texts: How do I full text search on a datetime column?

texts:
I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick.  Is it possible to do a FULLTEXT search on a DATETIME column? (%like% is way too slow).

The error I get is:

1283 - Column 'created' cannot be part of FULLTEXT index

--
edit:

$add_24 = $i + 1440;

$format_second = date("Y-m-d H:i", strtotime('-'.$add_24.' minutes'));

$p_price = mysqli_fetch_array(mysqli_query($con, "SELECT created FROM products WHERE created LIKE '$format_second%'"));


texts: How to get the difference between two timestamps in minutes?

texts:
Demo

create table my_table (ts1 timestamp,ts2 timestamp);

insert into my_table (ts1,ts2) values
    (timestamp '2017-01-29 07:35:14.52',timestamp '2017-01-29 15:01:59.14');

I'm familiar with the following method:

select  (   extract (day    from (ts2-ts1))*24*60*60
        +   extract (hour   from (ts2-ts1))*60*60
        +   extract (minute from (ts2-ts1))*60
        +   extract (second from (ts2-ts1))
    )/60  as ts_diff_minutes

from    my_table
;

+------------------------------------------+
| TS_DIFF_MINUTES                          |
+------------------------------------------+
| 446.743666666666666666666666666666666667 |
+------------------------------------------+

I was wondering if there is a shorter version to achieve the same result?


texts: Alternative defaults with a different table name

texts: Say the original table has DEFAULT nextval('some_sequence'). I'm looking for a way to specify DEFAULT nextval('another_sequence') on a table view/alias so that I can just insert into the alias as usual instead of repeating int_col = nextval('another_sequence') with every insert. In other words, the user/application only needs to be concerned with a table name and not whichever sequence is being used. Is this possible?


texts:
I have two mariadb servers in cluster that is working galera and xtradb.

it is my primary server configuration:

[mysqld]
datadir=/var/lib/mysql
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_cluster_name='cluster1'
wsrep_node_address='172.16.41.34'
wsrep_node_name='node1'
wsrep_sst_method=xtrabackup
wsrep_sst_auth="test:test1234"
log-error=/var/log/mysql.log

Slave configuration :

[mysqld]
datadir=/var/lib/mysql
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://172.16.41.34
wsrep_cluster_name='cluster1'
wsrep_node_address='172.16.41.36'
wsrep_node_name='node2'
wsrep_sst_method=xtrabackup
wsrep_sst_auth="sstuser:s3cretPass"
log-error=/var/log/mysql.log

After deliberately changed the user in wsrep_sst_auth, I noticed that the servers no problem to connect and synchronize with different user and password .

Do you think that is this a bug in galera?
Is it possible to compromise the cluster?
And also want to ask the traffic between two web server in the cluster that is encrypted?
Thanks :)


texts: MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

texts:
Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup.

How to solve this in the procedure?

Warning messages from innobackupex

InnoDB: The log sequence number in the ibdata files is higher than the log sequence number in the ib_logfiles! Are you sure you are using the right ib_logfiles to start up the database. Log sequence number in the ib_logfiles is 12881010402316, logsequence numbers stamped to ibdata file headers are between 12908127655307 and 12908127655307.
InnoDB: The log sequence numbers 12908127655307 and 12908127655307 in ibdata files do not match the log sequence number 12881010402316 in the ib_logfiles!
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.

And a lot of these:

2016-05-31 04:28:16 7f00d9ec5780 InnoDB: Error: page 7 log sequence number 12908127624790
InnoDB: is in the future! Current system log sequence number 12881010402316.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

It ends with:

xtrabackup: ########################################################
xtrabackup: # !!WARNING!!                                          #
xtrabackup: # The transaction log file is corrupted.               #
xtrabackup: # The log was not applied to the intended LSN!         #
xtrabackup: ########################################################

Procedure to create backups

On Sunday, create a base backup

innobackupex --stream=xbstream /var/lib/mysql | nc $backup_server $port

Monday-Saturday, daily create incremental backup based on last LSN from previous backup

innobackupex --stream=xbstream --incremental --incremental-lsn=$last_lsn /var/lib/mysql | nc $backup_server $port

Procedure to import backups

Import the base backup on Sunday to the standby environment:

Copy the base backup from the backup server to /var/backups/base-<date> (leaving it untouched)
Stop MySQL service
Copy /var/backups/base-<date> stage to /var/lib/mysql
Prepare the backup in /var/lib/mysql

sudo -u mysql innobackupex --ibbackup=/usr/bin/xtrabackup --apply-log /var/lib/mysql

Start MySQL service

Import the incremental backups from Monday-Saturday to the standby environment:

Copy the incremental backup from the backup server to /var/backups/incr-<date>
Prepare the base backup to apply the incremental on it

innobackupex --apply-log --defaults-file=/root/.my.cnf --ibbackup=/usr/bin/xtrabackup --redo-only /var/backups/base-<date>

Apply incremental on base backup

innobackupex --apply-log --defaults-file=/root/.my.cnf --ibbackup=/usr/bin/xtrabackup --redo-only /var/backups/base-<date> --incremental-dir=/var/backups/incr-date

Stop MySQL service
Copy /var/backups/base-<date> stage to /var/lib/mysql
Prepare the backup in /var/lib/mysql (not in the stage dir!)

sudo -u mysql innobackupex --ibbackup=/usr/bin/xtrabackup --apply-log /var/lib/mysql

Start MySQL service


texts: Will my query continue to execute after I shut off my computer

texts:
I am running a very expensive query that I executed via pgAdmin 4 which is installed on my laptop. I am executing the query on a cloud server.

The message that I see is 'Waiting for query execution to complete...'

If I shut my laptop off right now, will the query continue to run until completed?

Thanks.


texts: INT or TIMESTAMP for the change time column in a logging table?

texts:
We're having an argument over what to use for storing change date in a new log table for our site.

One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime query in PHP to a UNIX integer and query based on that (WHERE log_date BETWEEN [convert 12/25/2012 to timestamp] AND [convert 12/31/2012 to timestamp]).

The other side says, use TIMESTAMP for this; we can then use the native tools to query. (WHERE log_date BETWEEN "2012-12-25" AND "2012-12-31").

Since this is a logging table, we're not concerned about the 1970-2038 range of TIMESTAMP.

Which makes more sense?


texts: What is the best choose for PK in Partitioned Table?

texts:
I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older than 14 days will be deleted daily. One of the columns in this table is a reference to a table that will contain up to 5 million records (table name: Sens) and I also want it to be partitioned. I would like to ask you about:

Will it be a problem that both tables will use the same partitioning function? So the Sens table would also be distributed over 32 partitions and would be save on the same files as the Trans table. Is this a good approach ?

The Trans table has a PK based on two columns TranID (Identity (1,1)) and ParititionID. At the moment, FK to a smaller table ('Sens') is based on only one column - SenID. The smaller table also has to be partitioned. What will be the difference in the approach / efficiency / speed of operation if the PK in the Sens table will only be on the IDENTITY (1,1) column instead of the IDENTITY (1,1) column and the partition column, i.e.

ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
    PRIMARY KEY CLUSTERED ([SenID] ASC) ON [PRIMARY];

-- or

ALTER TABLE [dbo].[Sen]
ADD CONSTRAINT [PK_SenID]
    PRIMARY KEY CLUSTERED (
                              [SenID] ASC,
                              [PartitionID]
                          ) ON [psTrans]([PartitionID])

Have you ever try to have partition column which is computed ? I am thinking about choose partition according to new column which is computed base on other column in table:

CAST(HASHBYTES('MD5', [othercolumnInTable]) AS tinyint) % 32


texts: Update Statistics with full scan for a single table

texts:
My application search becomes slow some times in a day.And whenever i update statistics for a particular table,it gets fixed.
So i am thinking of creating a job to  run update statistics every morning by 11 am for that table alone.Currently the statistics is set to update by itself with SQL server default sampling. Is it a bad idea to schedule a job to run Update statistics with full scan for that one particular table.This issue happens in production environment.


texts:
It's possible to change aria_block_size from default 8192 without recreating tables? I want try 1024 or 2048 but after change in my.cnf and restart mariadb I got this errors on aria tables:

#138 - Wrong block size 8192; Expected 1024

My db have about 120Gb and many tables, so export/delete/import will cause many hours of outage and this is not possible.


texts: Local indexes vs Global indexes for partitioned tables in Oracle

texts:
I have partitioned a table that is growing almost at a rate of 7-8 million rows a day. The partitioning has been done using a timestamp column as data can be archived or discarded a few weeks later. I have also created an index on the table which are on primary key or another value that is unique. My indexes are partitioned as well, however, the partitioning of index has been done using a hash function and does not include the partition key of table (which is a timestamp). So I have a few questions.

For an ever-growing table which has majority inserts, does partitioning alleviates slowness with inserts and updates or partitioning indexes is enough to gurantee a faster insert and update. I have been told that there may not be value in partitioning indexes and table differently. Does it make sense to partition tables to speed up inserts?
The table is a write-intensive table. It is currently mostly inserts, one update per row and 2-3 lookups within seconds of creation by indexed id and then the record is never accessed for any operation.
Is it optimal to define local indexes on the unique id or is it better to define global indexes and partition them as I have already done? If I define global indexes, without table partitioning key in it (timestamp, which is not used in lookup), will the access be more expensive if the number of partitions is huge?
What are the downsides of having global partitioned indexes for constantly growing data? Once I decide to remove the partitions at a later point of time, since the indexes are not partitioned by timestamp but instead by unique id, will the operation cause a direct impact on functioning indexes?

Any other recommendations will be helpful.


texts: Optimizing an audit table for inserts and deletes

texts:
I'm trying to identify the best way to design a table for audit purposes. Basically I log several last events for many users in one table. There's a limit on the number of records for each user. The new records come, the older go.

Something like this:

CREATE TABLE Audit
(
  UserId INT NOT NULL,
  EventId INT NOT NULL,
  CreationDate DATETIME NOT NULL,
  UpdateDate DATETIME NULL,
  -- Other data fields
)

The question is what to do with indexes. I'm thinking of creating the clustered index on (UserId, EventId). But since user activity happens independently it would mean  inserts in the middle of the table and deletes in the middle of the table. Probably not good.

Another thought is to add an artificial AuditId field just to have new records get increasing numbers. Like this:

CREATE TABLE Audit
(
  Id INT, -- Becomes the clustered index
  -- The same as above
)

This way new audit entries will be appended to the end but deletes will still happen in the middle of the table. It's probably better than the first option but I'm not sure.

This table will be used frequently, basically every user activity is going to be logged (1 insert) and the oldest activity gets deleted in the same transaction (1 delete). This needs to be fast. Well, I want it to be instantaneous ideally and not noticeable performance-wise.

I also need to be able to retrieve the set of records for a particular user quickly. It can probably be covered by a non-clustered index.

I'm asking for advice in designing this table for optimal performance.

EDIT: I think I have missed something important to mention.

What I'm trying to track is not instantaneous in time but rather a period in time. There are several places in the system where I need this. Consider what the user is doing an activity of some sort that may span some period of time. If certain conditions are met then an existing activity is reused (refreshed, updated). I only wish to delete older sort of abandoned activities. For example, within 2 weeks one user may have issued like 50 of activities, but for another user to produce than many may take over a year. That's why I don't want a generally ordered log for all users together.

It is also not clear how should I cluster per datetime (as suggested). Do I do it on initial creation event or on the update event?


texts: A nonrecoverable I/O error occurred on file after DB Shrink

texts:
After shrinking an SQL Server database & files, I encountered an error message saying "A nonrecoverable I/O error occurred on file" while performing database backup.

Why am I getting this error?


texts: How to turn VARBINARY within PeopleSoft into a string?

texts:
In PeopleSoft there are string messages that are stored in VARBINARY under the hood.
I would like to convert that data into a string so that I can view it in a data query tool.
Is this possible using SQL and perhaps some built-in SQL Server functions?  I would rather not create a special Java program for just this one field.


texts: SQL Server Transaction Logs in the Cloud

texts:
It is a common practice to place the transaction logs (*.ldf) on a separate physical disk system than database files (*.mdf, *.ndf).

In a virtual machine cloud environment, is it okay to put the transaction logs on the same drive letter as the other data files? How is this usually handled in popular cloud virtual machine environments of today.

NOTE: Not using SQL Azure. Using a Windows virtual machine with full access.


texts:
I am using Opencart 2.x with mysql. I have created a custom module for excel export. My query took more than 2 minutes to execute. So I want to optimize my query.

EXPLAIN shows the following image

This is my query

SELECT o.order_id,
       Concat(o.firstname, ' ', o.lastname)            AS customer,
       pov.sku,
       oo.value,
       pov.segment,
       pov.colour,
       op.quantity,
       o.shipping_address_1,
       o.shipping_address_2,
       o.shipping_city,
       o.shipping_zone,
       o.shipping_country,
       o.shipping_postcode,
       (SELECT os.NAME
        FROM   oc_order_status os
        WHERE  os.order_status_id = o.order_status_id
               AND os.language_id = '1') AS order_status,
       (SELECT Count(order_product_id)
        FROM   oc_order_product
        WHERE  order_id = o.order_id) AS product_count,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.code = 'coupon') AS coupon,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%Shipping Fee%') AS shipping_cost,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%COD Fee (30 INR)%') AS cod,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title = 'CGST') AS cgst,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title = 'SGST') AS sgst,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%Sub-Total%') AS sub_total,
       o.total,
       o.currency_code,
       o.currency_value,
       o.payment_method,
       o.date_added,
       o.date_modified
FROM   oc_order o
       LEFT JOIN oc_order_option oo
              ON ( oo.order_id = o.order_id )
       LEFT JOIN oc_product_option_value pov
              ON ( pov.product_option_value_id = oo.product_option_value_id )
       LEFT JOIN oc_order_product op
              ON( o.order_id = op.order_id )
WHERE  o.order_status_id >= 0
GROUP  BY oo.order_option_id
ORDER  BY o.order_id DESC


texts: What is the best type of account to use for SQL services?

texts:
We run stand alone instances all virtual. SQL 2012 - SQL 2017
Backups are performed by a third party tool so the server has no need to access anything on the network.

Agent jobs are all run via proxy accounts.

I've started to use virtual service accounts since i don't see the need to setup AD accounts and also Microsoft indicates to use a VSA or MSA (if network access is needed).

I just noticed today that, in my sp_Blitz results, the lowest priority issue indicates AD accounts are preferred.  Now of course it is the lowest priority, which to me is a non issue, but I'd really like to know why there is a preference (at least from Brent) to use AD accounts.


texts: MS SQL Server installation fails with error message

texts:
I am trying to install SQL Server (tried all 2012 Developer edition, Web edition, Standard edition, Express and also tried all 2014 versions). Each time it is failing with the following error message

> (01) 2016-08-05 13:25:12 Slp: PerfCounter calling lodctr: 'C:\Program
> Files\Microsoft SQL
> Server\MSSQL11.SQLEXPRESS12\MSSQL\Binn\perf-MSSQL$SQLEXPRESS12sqlctr.ini'
> (01) 2016-08-05 13:25:12 Slp: Prompting user if they want to retry
> this action due to the following failure: (01) 2016-08-05 13:25:12
> Slp: ----------------------------------------
>(01) 2016-08-05 13:25:12
> Slp: The following is an exception stack listing the exceptions in
> outermost to innermost order (01) 2016-08-05 13:25:12 Slp: Inner
> exceptions are being indented (01) 2016-08-05 13:25:12 Slp: (01)
> 2016-08-05 13:25:12 Slp: Exception type:
> Microsoft.SqlServer.Configuration.Sco.ScoException (01) 2016-08-05
> 13:25:12 Slp: Message: (01) 2016-08-05 13:25:12 Slp: The system cannot
> find the file specified. (01) 2016-08-05 13:25:12 Slp:  Slp:
> WatsonData =
> SQLCTR110.DLL@OpenSQLPerformanceData@CollectSQLPerformanceData@CloseSQLPerformanceData
> (01) 2016-08-05 13:25:12 Slp: DisableRetry = true (01) 2016-08-05
> 13:25:12 Slp: Inner exception type:
> System.ComponentModel.Win32Exception (01) 2016-08-05 13:25:12 Slp:
> Message: (01) 2016-08-05 13:25:12 Slp: The system cannot find the file
> specified. (01) 2016-08-05 13:25:12 Slp: (01) 2016-08-05 13:25:12 Slp:
> HResult : 0x80004005 (01) 2016-08-05 13:25:12 Slp: Error : 2 (01)
> 2016-08-05 13:25:12 Slp: Stack: (01) 2016-08-05 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.LoadPerformanceCounter(String
> symbolInstallPath, String counterPrefixServiceName) (01) 2016-08-05
> 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounterCore(String
> libraryFileName, String openFunction, String collectionFunction,
> String closeFunction, String symbolInstallPath, String
> counterPrefixServiceName) (01) 2016-08-05 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounter(String
> libraryFileName, String openFunction, String collectionFunction,
> String closeFunction, String symbolInstallPath, String
> counterPrefixServiceName) (01) 2016-08-05 13:25:12 Slp:
> ---------------------------------------- (01) 2016-08-05 13:52:56 Slp: User has chosen to cancel this action (01) 2016-08-05 13:52:56 Slp:
> Configuration action failed for feature SQL_Engine_Core_Inst during
> timing ConfigNonRC and scenario ConfigNonRC. (01) 2016-08-05 13:52:56
> Slp: The system cannot find the file specified.


texts:
I have this query and I want to increase its performance by adding appropriate Indexes.

DELETE
  FROM MYTAB1
  WHERE MYID1 IN
        ( SELECT MYID2 FROM MYTAB2 );

I am not familiar with the syntax for indices and the type of settings that they require. Please provide the same.

The main issue here is that MYTAB1 has millions of records and thus the query takes a lot of time.

What indices do I need?


texts: Multiplication Using Values From Various Tables

texts:
I have created three table using with the following code:

CREATE TABLE Invoices (
    invoiceID nvarchar(5) NOT NULL PRIMARY KEY,
    invoiceDate date,
);

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) REFERENCES Items(itemID),
    itemQuantity numeric(2, 0)
);

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20),
    unit nvarchar(20),
    pricePerUnit float
);

How would I be able to query a list of all invoices showing the invoiceID, invoiceDate, and the total cost of all items for each invoice?

Many Thanks.

Edit:

So far I have written something like:

SELECT Invoices.invoiceID, Invoices.invoiceDate, _______________________
FROM Invoices
INNER JOIN ContainsItems ON Invoices.invoiceID = ContainsItems.invoiceID
INNER JOIN Items ON ContainsItems.itemID = Items.itemID;

...but I have no clue how to generate the total of each invoice.

I am using MySQL Workbench.


texts: How can a primary key be inserted as foreign key?

texts:
Hi I am building an order intake form that will have information submitted belonging to 3 tables.  The order table will have the primary key used for the property and customer table.

How can I simultaneously create the primary key via the identity on the order table and insert that into the property and customer table?

SQL please.

Order
OrderKey PK

Property
OrderKey FK


texts: Remove Database from SQL Management Studio Database List

texts:
I'm working with MS SQL Express 2008 and Visual Studio 2010.

If you are attached to a local MDF file how do you remove it from the Databases list without deleting the file? The reason is to purge the list of databases for old projects. The next time you run the project it will re-establish the connection so it makes sense to make things tidy and easy to find when you need it.

Trying to simply delete the the database of course deletes the files as well. I tried unchecking the Delete backup and restore history information for databases but that didn't make a difference. I also tried the ole' Shift-Delete to alter its functionality.

Odd thing is when I search I don't see simlar questions. I'd think that would be something others would want to do.

Right now I make a copy of the MDF and LDF files, delete the database from Management Studio and then copy the file back.

Is there a better way?


texts: Why is my mongodb setup not reaching EBS IOPS limit?

texts:
I have an application that is constantly pumping data to MongoDB. MongoDB instance is running with 2-replicas, each with 3TB gp2 EBS volume.
As you can see on the following graph of
irate(node_disk_reads_completed_total{}[1m])
irate(node_disk_writes_completed_total{}[1m])

The reading performance is slow and steady, which is normal, but the writes seem to be under-performing.
Even in the peaks we never reach theoretical 3IOPS/GB * 3000 GB = 9k IOPS. The application itself is spending most of the clock time pumping more and more data to the DB, so from the perspective of the app the DB is the clear bottleneck.
So why can't it go faster? And why is it such a wave? I would expect that writing to WAL will provide constant source of write activity and things like periodic fsync to disk would not really cause such extreme up and down patterns.
Could it be that syncing between replicas are causing pauses in the throughput? But I am using the default write concern, which should be w: 1, j: true and not require waiting for replicas.
Anything else that I might be missing?


texts:
Take the following relational schemes:

R(A, B, C)
S(D, E, F)

where the attributes A and D are the primary keys, respectively.

Assume to have an instance r of R with n tuples and an instance s of S with m tuples.

Moreover, assume to have a referential integrity constraint between C and the primary key of S.

Now, I was asked the following questions:

  How many tuples does the θ-join between s and r contain if the join predicate is C = D?
  How many tuples does the θ-join between s and r contain if the join predicate is B = E?

My answers:

n tuples since it all depends on the number of tuples belonging to the
second relation that match the tuples which are part of the first one
zero since there's no common attributes

Is my reasoning correct?


texts: Update a column to same value for all rows in a group of the group by

texts:
I have the following table:

name
key
uuid

na1
k1
NULL

na2
k2
NULL

na3
k1
NULL

If two or more rows have the same key value then I want to update the uuid value to the same uuid for those rows. If the key is unique then that row should have a unique uuid value.
Following is the desired outcome:

name
key
uuid

na1
k1
8274e89f-b119-4326-814d-4a864bbbe207

na2
k2
9a6c5f68-a3c2-4250-ac31-cc8c86a6440c

na3
k1
8274e89f-b119-4326-814d-4a864bbbe207

Any ideas how to achieve this?


texts: Transaction on delete is committed, but for a brief time the entries can still be read?

texts: I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single table, and then deletes it when it is finished. Each time a thread is assigned a task it first checks the table to see if that task is already running. It looks to be working, and I'm not getting any deadlocks, but after the application completely exits I noticed I still see entries coming and going from the table (using MySQL Workbench). Eventually they are all deleted successfully. The isolation level is READ_COMMITTED, so I'm not sure why I would be seeing dirty reads.


texts:
I'm trying to use NFS as db path for my MongoDB (which I read is not recommended, but a client demands it that way) and I'm having trouble separating the journal files.

How would I do that? I've tried setting the dbpath to /mnt/nfs and it works, but the journal directory is there as well...

Any hints?


texts:
SQLDependency Notifications work fine logged in as Windows Domain User, using Trusted Connection. But not as SQL-User:

I created a db login, user and role as follows:

CREATE LOGIN myLogin WITH PASSWORD = 'somePW', CHECK_POLICY = OFF;
BEGIN CREATE USER myUser FOR LOGIN myLogin;
BEGIN CREATE ROLE "myRole" AUTHORIZATION [dbo];
EXECUTE sp_addrolemember N'myRole', N'myUser';

Furthermore, I added some permissions, following this link

--Database level permissions
GRANT CREATE PROCEDURE to [myRole];
GRANT CREATE QUEUE to [myRole];
GRANT CREATE SERVICE to [myRole];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [myRole];
GRANT VIEW DEFINITION TO [myRole];

--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [myRole];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [myRole];

and also tried changing the db-owner as described in this article

This DBA-Question ( aiming in the same direction ) does not provide any help.

The permissions to execute the query and do a select on the table by the SqlDependency are also set. I verified this using SSMS and execute the query with the user/pw-combination created above.

Now, I try to use the SqlDependency via .NET ( connection to DB is established with the login/user described above. Executing SqlDependency.Start( myConnectionString ) throws an exception ( translated from german ):

  The specified user 'owner' was not found.
  Search the queue object 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812' is not possible because the object does not exist or you do not have the required permission.
  Invalid object name 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812'.

Any ideas what is missing?


texts: MySQL 5.5.2 community version performance and hang issue

texts:
We have a 64GB RAM server with HP-UX OS and installed MySQL version 5.5.2.
We observe frequent issues with the database and we must restart the database every 2 or 3 days which is impacting the stability of our application.
Let me know which details are required to solve this issue.


texts: Should I use mysql_upgrade after mysqld_safe when upgrading MariaDB?

texts:
I'm wanting to know the best practice to do a MariaDB upgrade on opensuse.

I am getting this error:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db =
'a_database'': Cannot load from mysql.proc. The table is probably
corrupted (1548)

I'm assuming that an upgrade will fix the error.

So what is the best practice to do that?


texts: Modeling a database structure for a trip management business domain

texts:
I am sketching up a database design and it is giving me some troubles. Basically something just "smells" about this design but I can't seem to arrive at a better way to do it. For example, the Joins needed to get back to Person table could be ugly.

Business rules

A Person can go on many Trips.
Each Trip has many Destinations for its many participants (Person).
Every potential participant has an option to RSVP for a given Trip. If they RSVP for a Trip they must then RSVP for each Destination on that Trip.
Each Destination has an optional agenda (DestinationComponent) that each participant can RSVP for.
Assume that each RSVP relationship (at every level) will "contain many more data columns" required for information about the particular RSVP - for example, each participant can Vote on each Destination and DestinationComponent (Vote column has been omitted in RSVP tables within diagram).

Current diagram

This is the diagram I have created so far:

Questions

Is there a better way to manage these relationships?
A "master 'junction' table" for RSVPs and one for votes?

I'm worried those tables would result in a massive overtime. Guidance would very much be appreciated!


texts:
I have 3 tables. and I want to create a view. There is no problem about generating SQL query and creating a view.

But when I want to use this view in my web app, it says me (LIKE THIS):

  Your view's column doesn't have a primary key

So I want to set a PK to a column in my view.

Please help. Thanks.


texts: See who has access to databases via AD group membership

texts:
SharePoint has a button to "Check Permissions". Here you can select a user from Active Directory, and it will magically traverse groups and show you all the access the user has on the site based on what groups they are in.

This is a long shot as I've been working with SQL Server for quite a while and have never heard of this, but just in case I missed something...

Is there anything similar in SQL Server? Right now, checking permissions involves looking at the AD groups that have logins on the instance, and going through AD manually to see who is in which group, and then look at database permissions manually. A "Check Permissions" like SharePoint could save a lot of time.


texts: What is the SQL Server cost, in terms of performance, of sharing physical disks?

texts:
I am an application user in Health Care.  The application relies on communication with a large database, including image data and other large numeric arrays.  We add approximately 15GB per day to the database server (note image data might be file based).  The application crashes, sometimes coincident in time with querying the database e.g. when you query the patient list in opening a new case.

There are two instances of the application database, used by two groups, that currently share a physical disk in the database server.  I wonder what factors I'd need to understand in order to estimate whether or not there might be appreciable performance gain by giving each instance of the database a dedicated physical disk.


texts: best practice with RAID0 configuration - to keep all of them in separate drives i.e. tempdb, mdf, ldf, backups

texts:
Database size - 145GB
Currently our database mdf file, tempdb, backups are in D drive and log files are in C drive.
Can we go for the below configuration:

D drive - tempdb, E drive - mdf, f drive - backups, C drive - logs

or

D - tempdb, E - mdf ,F - logs & backups

We have 26 servers with full recovery model which works 24/7 for our clients.
and more than 100 for other clients with Simple recovery model which works only 9 to 6.
Currently, we have available only 4 hard drives to allocate with this configuration C, D: Mirrored (RAID 1) and E. Can we fit our database all files in this kind of configuration or avoid RAID 1?
Front end application crashes frequently during the day time and inaccessible during night time as maintenance tasks runs which are database integrity, rebuild/reorganize, backup and delete old files.
Could someone please help me with best suitable approach for this?


texts: Replication : Which subscription changed a row

texts: Is it possible to query the replication metadata to see which subscription caused a change to a specific row in a table?


texts: MySql service on Windows won't start after changing datadir

texts:
Tried to change the data directory for a default Windows installation of MySQL. Here's the steps I've taken.

Stopped the MySql service.
Located the data directory of MySql and moved it to another drive, ensuring the path name had no spaces.
Made a copy of the my-default.ini file in C:\Program Files\MySQL\MySQL Server 5.7 then renamed it to my.ini and edited it to look like this:

    [mysqld]
       datadir = F:\ProgramData\MySQL\MySQLServer\Data

Copied the contents of the original data folder into this new data folder.
The MySql Service is running under the NETWORK SERVICE user, so I have given this user full control permission on the new folder.
Deleted the original data folder.
MySql Service fails to start, with no useful error messages appearing in the windows event logs.

As far as I know, the MySql error logs are located in the datadir, so it's not much help looking there?

How can I find and fix what I've done wrong?


texts: Should a mysql slave answer queries much slower then the master?

texts:
I am seeing a slight issue on a mysql slave server, or at least on the surface it appears there might be an issue.
Both mysql's are virtual machines
The master and slave are the same OS (debian10) and the sql's are the same version, 10.3.27, and were installed installed only half-hour apart.
The replication itself works great, and most writes to the slave appear within seconds including new db's, changed data etc.
I have run a few tests recently, where I change some wordpress config's to point at the ip of the slave instead of the master so I can feel a bit more comfortable all is working well with replication and as a disaster recovery test.
The problem is any sites utilizing the slave for sql data are horribly slow, and as soon as I change the ip back to the master speed returns.
Keep in mind, in this scenario the web server never changes.
In this scenario, NEITHER sql server touches the internet as the public cards are disabled on both master and slave.
Both sql servers have absolutely nothing else installed on them and are stock Debian, maria and webmin.
Private traffic and public traffic are on different subnets and different vlans in the switch so completely segmented. (untagged)
I have tried moving the virtual machine to different hosts and it does not have any effect.
I have tried clearing the web server cache and even restarting the web server.
Ping times to the ip's of each sql server are identical at 1ms or less.
Both have identical firewall rules, and tests where firewall is disabled there is no change.
Before anyone suggests switch ports, this also happens even when both VM's are on the same physical host, so the PRIVATE traffic would be going in and out the same NIC's and traversing the same port on the switch.
I have not enabled any automatic failover, but before I even begin to think about that I would like to see this issue resolved. We are talking of cases where a small site that may take a few seconds to load on the master suddenly taking 10 to 20 or even 30 seconds for a page load.
Now I understand a slave is not exactly designed for the reads, but is it normal for a slave to be a half minute slower on the query then the master?
I have done a bit of searching and have yet to see anyone talk about this, but most query's on the subject start talking about the replication traffic itself being slow, or the time factor for the slave to catch up from the replication log files etc from the master, but none of that is the case, replication itself is pretty darn close to instant.
I hope I explained the situation clearly and if anyone has things to try let me know.
Universal4


texts: Which collation should I choose for a muiti-language website?

texts:
Does a collation have any influence over a query speed? Does the size of a table change depending of the collation?

If I want to build a website that must support all possible languages (lets take for e.g. Google) which would be the recommended collation?

I will need to store characters such as 日本語, my searches over the website will have to return something for the sóméthíng input, it must be case insensitive as well.

How do I know which is the best choice to make? Which collation better suits this case?


texts: How to join 2 tables when calling a count on the second table

texts:
I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest assignment at the top of a list.

coaches
+user_id
+bio
+title

coach_assignments
+id
+client_id
+coach_id
+deleted_at

I wrote this code to get the count from the coach_assignments table
select count(coach_id), coach_id from coach_assignments where deleted_at is not null group by coach_id order by count(coach_id) ASC

and it worked, however the glaring hole is that a new coach doesn't have any entries in coach_assignments so they aren't counted. I don't want to add a client_count column to coaches as I don't feel coaches should have that info.

I tried:

select  user_id, count(coach_id)
    from  coaches
    left join
    (
        SELECT  count(coach_id), coach_id
            from  coach_assignments
            where  deleted_at is not null
            group by  coach_id
            order by  count(coach_id) ASC
    ) as counted  ON coaches.user_id = counted.coach_id;

but this only returned 1 row and there should be 4 for my test data.
How do I do something like this?


texts: How to lower HEAP Fragmentation in SQL Server?

texts:
i recently found out that one heap table had more than 70% fragmentation.
So i decided to do a

ALTER TABLE dbo.myTable REBUILD

Funny enough, afterwards i had 20% fragmentation. There was no write on that table ever since. So i decided to do the rebuild one more time.

After the 2nd time the table hat 50% fragmentation so even more!
I really don't understand how this can happen...


texts: How to get the hierarchy of particular employee using JPA spring boot?

texts:
I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot.

The problem is similar to the following link
Similar Data Model

If A reports to B and B reports to C and C reports to D,

For employee B i need the output to be B->C->D.

The following the Recursive query in Mysql

WITH RECURSIVE Employee_path(id,Name,bid) AS(
SELECT id,Name,bid
FROM Employee
WHERE id = 1005
UNION ALL
SELECT e.id,e.Name,e.bid
FROM Employee_path AS cp JOIN Employee AS e
ON cp.bid = e.id
)
SELECT * FROM Employee_path ORDER by bid ASC;

Since I am new to JPA I am unable to get the solution(I am not getting how to approach this problem)


texts:
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - oradb

  Protection Mode: MaxPerformance
  Members:
  oradb    - Primary database
    Error: ORA-16778: redo transport error for one or more databases

    oradb_s2 - Physical standby database
      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 1 second ago)

DGMGRL>

is someone has any idea about the following error and warning?


texts:
We have been running SQL Server 2000 for years.

Finally, at long last, we have an SQL 2008 Server being shipped in.

We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL 2008 Server.

Where can I go to look up how to convert DTS jobs so that they can be run on SQL 2008?

I am a WinForms C# developer that makes calls, builds stored procedures and occasionally creates a new table for our SQL Server, but I am not a Database or Network Administrator by title.

I want to make sure I do this right so that our company has minimal down time.

From our Network Administrator:

  From what I am reading, I will have to do more research, we have to convert all the Data Transformation Services (DTS) packages to SQL Server Integration Services (SSIS). I do not know what this may take but from my understanding, the jobs are written in .Net. If we could migrate them, great, but if not we may have to write new ones for all, which would not be that hard once you have the first one written.

Does this help?


texts: 'Invalid utf8 character string' warning when importing into AWS RDS MySQL

texts:
I am trying to migrate osTicket from physical Windows machine to AWS RDS, and I get a bunch of warnings while doing so. I am quite a DB noob, so I am not sure what is the issue.

Source: Windows, MySQL 5.6.10, backup created with mysqldump -u root -p --default-character-set=utf8 osTicket > file.sql

mysql> show variables like 'character_set%';
+--------------------------+---------------------------------------------------+
| Variable_name            | Value                                             |
+--------------------------+----------------------------------------------------
| character_set_client     | cp850                                             |
| character_set_connection | cp850                                             |
| character_set_database   | utf8                                              |
| character_set_filesystem | binary                                            |
| character_set_results    | cp850                                             |
| character_set_server     | utf8                                              |
| character_set_system     | utf8                                              |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.6\share\char|
|sets\                                                                         |

Destination: AWS RDS, MySQL 5.6.27-log, import with mysql> source file.sql. I've created a custom Parameter Group to change character_set_database to utf8 since default on RDS is latin1

mysql> show variables like 'character_set%';
+--------------------------+---------------------------------------------------+
| Variable_name            | Value                                             |
+--------------------------+----------------------------------------------------
| character_set_client     | cp850                                             |
| character_set_connection | cp850                                             |
| character_set_database   | utf8                                              |
| character_set_filesystem | binary                                            |
| character_set_results    | cp850                                             |
| character_set_server     | utf8                                              |
| character_set_system     | utf8                                              |
| character_sets_dir       | /rdsdbbin/mysql-5.6.27.R1/share/charsets/         |

I get warnings like these:

Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Query OK, 6 rows affected, 6 warnings (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 6

Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'FFD8FF'
Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
Warning (Code 1300): Invalid utf8 character string: 'D0CF11'
Warning (Code 1300): Invalid utf8 character string: 'E2E3CF'
Query OK, 20 rows affected, 20 warnings (0.06 sec)
Records: 20  Duplicates: 0  Warnings: 20

I tried to set character_set_client=cp850 and the same for set_connection and set_results before import, but didn't help me.

If anyone could be so kind to help me what I am doing wrong here, it would be really great.

Thanks in advance!


texts: How to select blocks with sequential data and aggregate the ids


texts:
I am trying to Select Units Sold and Total Units Sold from a Sales record table using.

When I use this Query, I can get the Units Sold for '2016-04-14'

SELECT salesrecord.itemID, salesrecord.itemname,
   SUM(salesrecord.itemunits) AS `Units Sold` ,
   SUM(salesrecord.itemunits)
WHERE `date`='2016-04-14'
GROUP BY salesrecord.itemname

And When I Use this query, I can get the Total units Sold till date

SELECT salesrecord.itemID, salesrecord.itemname,
   SUM(salesrecord.itemunits) AS `Total Units Sold` ,
   SUM(salesrecord.itemunits)
WHERE `date`<='2016-04-14'
GROUP BY salesrecord.itemname

What I will like to do is to get both "Units Sold" And "Total Units Sold" on a single table as shown in image attached


texts: What is the best way to store a large (thousands of properties) nested JSON object in Postgres?  It does not need to be index, queried, or anything.  Just written and read.  Write speed isn't an issue at all, only read speed.  Should I still opt for JSONB, even though it seems like the benefits of JSONB would be rendered inert?  Perhaps JSON?  Or even just a normal text type?


texts: What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

texts:
What's best performance wise, two separate UPDATEs:

UPDATE table1
    SET enabled = 0
    WHERE code NOT IN (
        SELECT id
        FROM table2
    );

UPDATE table1
    SET enabled = 1
    WHERE code IN (
        SELECT id
        FROM table2
    );

Or a single UPDATE with a CASE?

UPDATE table1
    SET enabled = (
        CASE
            WHEN code IN (SELECT id FROM table2) THEN 1
            ELSE 0
        END
    );

(and let's consider there are no NULL values in the relevant columns).

What would perform best, supposed code and id columns have are indexed?

I can't really tell because I don't have significant tables, and the only difference EXPLAIN gives is that the former version uses the WHERE (but I analyzed only one UPDATE) - I'd say that a plus, but I'm unsure if that's enough.

I'm working with MySQL.


texts:
I know what this option does and how to enable it.  My question is what things will happen if I were to enable this.

Without giving too much info, our accounting system is a Microsoft Dynamics product and it uses a VM, 32GB RAM (28GB available to SQL server [2008 R2]).  They had their vendor come and take a look at our configuration to address certain performance issues that the accounting team has been seeing.  They even had another DBA take a look at our configuration.  One of his recommendations was to 'look at missing indexes'.  We could say that about almost every SQL server instance in existence, each table has a unique non-clustered index on it, not my choice but im told that making any changes to the indexes on tables that the software accesses could potentially cause issues per the vendor.  His second was to enable 'optimize for adhoc workloads'.  I've looked into this option a few times before and many say it should always be enabled, whereas some say it's not truly necessary and can go either way.

With optimize for adhoc workloads, I know that single use plans are stored as a stub and the entire plan is not actually kept in cache until the plan is run two times.  With a system such as this, are we REALLY going to see any sort of performance gains?  Per Kimberly Tripp's article, I've run this query:

SELECT objtype AS [CacheType]
    ,count_big(*) AS [Total Plans]
    ,sum(cast(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
    ,avg(usecounts) AS [Avg Use Count]
    ,sum(cast((
                CASE
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs - USE Count 1]
    ,sum(CASE
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

and here is the result I get:

Now, as we can see, we have approx 1.7GB of single use plans.  Probably worth it to turn this option on as the box has 28GB available to SQL server, right?  Well, We're talking about freeing maybe 1-1.25GB as the stubs still take space, just not as much.  I ran a query to pull all single use plans and get their size, our largest plans are about 1-1.5MB.

So, here are my questions (sorry it took a while to actually get to these):

Do we really think that we will see a measurable performance gain here?  If so, how can we quantify it aside from looking at the space used in the query above?
Understandably, I hesitate throwing switches like this on production systems.  What implications should I be cautious of?  Will the procedure cache wipe itself and rebuild?  Are there any issues that I should be made aware of when turning this on?


texts: Where to store cash book debit in double entry bookkeeping relational database?

texts:
I'm designing a very basic banking system, where Users can Deposit to Bank / Withdraw from Bank / Transfer to other user accounts.
I've been studying double entry bookkeeping and so far my database design is like this:
users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(40) NOT NULL UNIQUE,
        password VARCHAR(40) NOT NULL
    )
// Each account is linked to a user account.
accounts (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id)
    )

and these are my transaction tables.
transaction_types (
        id SERIAL PRIMARY KEY,
        name VARCHAR(20) // for example 1.deposit / 2.withdraw / 3.transfer
    )

transactions (
        id SERIAL PRIMARY KEY,
        type_id INTEGER REFERENCES transaction_types(id), // type of transaction deposit.. etc
        account_id INTEGER REFERENCES accounts(id), // each transaction has FK for accounts id
        amount DECIMAL(20, 10) NOT NULL // negative for debit and positive for credit
    )

When a user tries to transfer his/her balance to another user it's pretty straight forward, the rows would be like below:
INSERT INTO transactions (type_id, account_id, amount) VALUES (3, 1, 100)
INSERT INTO transactions (type_id, account_id, amount) VALUES (3, 2, -100)

the sum is 0 and since account_id references ( FK ) to accounts table, everything works as expected.
but what if the user wants to withdraw or deposit money to bank ( and their account ).
I've came up with two possible solutions yet:

Removing the foreign key constraint for account_id column in transactions table to let store something like cashbook or theBank for deposit and withdraw.

Keeping the foreign key constraint and since account_id is a foreign key to accounts table, create an account for the bank itself at the beginning of the program.

which one of these is better? is there any other solution I'm not thinking of?
Thank you for your time.


texts: Best practice for database where the values can't change

texts:
Suppose I have a table Inspection with a field for Place (the location where the Inspection occurred). The field is a foreign key to a Place table, where each place has a Name, among other things. The Place names can change over time (for instance the Place could be a restaurant where the name changes when its ownership changes), but we would like the Inspection to continue to hold the original value it was given when it was created.

Is there a best practice for something like this? One thought is to have two fields, one to store the foreign key, regardless of whether it's been updated, and another to hold a string value for the original Place name. Another thought is to simply make Places immutable. You would have to create a new Place every time the name changed, rather than updating existing Places. Perhaps you could even related one or more Places somehow so that you know that multiple rows in the table actually relate to the same physical location.


texts:
Same query run on two identical servers of the same SQL version(including SP/KB level) with identical server and database level settings errors out on one server(Arithmetic overflow error for data type smallint, value = xxxxx.) but executes successfully on the other one.
Any ideas?

Thanks,
VJ

Query:

SELECT
       c.cost_owner_key1
       ,pl_qty =
        CASE
            WHEN pa.PnlOwnerCode='C' AND pa.PnlOwnerSubCode = 'PR' THEN
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN -1
                        WHEN 'Sell' THEN 1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
            ELSE
                COALESCE
                (
                    CASE pa.BuySell
                        WHEN 'Buy' THEN 1
                        WHEN 'Sell' THEN -1
                    END, 1
                ) * ISNULL(c.cost_qty, 0)
        END
    ,pl_qty_factor =
        CASE
            WHEN c.cost_qty_uom_code = i.contr_qty_uom_code THEN 1
            ELSE (a.secondary_actual_gross_qty / a.ai_est_actual_gross_qty)
        END
    ,contr_qty_uom = c.cost_qty_uom_code
    ,alloc_num = c.cost_owner_key1
    ,alloc_item_num = c.cost_owner_key2
       ,CAST(c.cost_owner_key2 AS smallint)
FROM
    pc_pnl_cp2_pnl_asof pa
    INNER JOIN cost c WITH(NOLOCK) ON c.cost_num = pa.PnlRecordKey
                     AND pa.PnlOwnerCode = 'C'
    INNER JOIN ai_est_actual a WITH(NOLOCK) ON a.alloc_num=c.cost_owner_key1
                              AND a.alloc_item_num=c.cost_owner_key2
                              AND a.ai_est_actual_num=c.cost_owner_key3
    INNER JOIN trade_item i WITH(NOLOCK) ON i.trade_num = pa.TradeId
                           AND i.order_num = pa.OrderNumber
                           AND i.item_num = pa.ItemNumber
WHERE
    pa.CostStatus != 'CLOSED'
    AND pa.CostOwnerCode = 'AA'
    AND pa.CostType = 'WPP'


texts: How to handle multiple hierarchies in a data model?

texts:
In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies.

Example 1: Hierarchy1, that categories patient's based on geography.

Country >> state >> etc... .

A Patient can only be identified in one node of the hierarchy.
When a patient moves, their association with node is also moved to appropriate new node.

Example 1: Hierarchy2, based on benefits they claim, they are put into one program or another.

Program >> CCMA >> etc

Again, same rules as before apply:

A Patient can only be identified in one node of the hierarchy.
When a patient changes plans (program), their association with node is also moved to appropriate new node.

We could have 1 or 2 or ... n hierarchies. How do we handle them?

This is different from Patient_Groups that are used for ad hoc grouping.

Thanks,


texts: Does the SQL Spec require a GROUP BY in EXISTS ()

texts:
Microsoft currently permits this syntax.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

Notice that there is no GROUP BY in the EXISTS clause, is that valid ANSI SQL. Or is it merely exposing an implementation detail.

For reference, this same syntax isn't permitted in PostgreSQL.

  ERROR:  column "t.x" must appear in the GROUP BY clause or be used in an aggregate function

But this syntax is permitted..

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT 1  -- This changed from the first query
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  HAVING count(*) > 1
);

And this syntax is permitted.

SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS t(x)
  WHERE g.x = t.x
  GROUP BY t.x  -- This changed from the first query
  HAVING count(*) > 1
);

Question arises from a conversation with @ErikE in chat


texts: SQL Server 2008R2 DBCC CheckDB never finishing

texts:
I'm running SQL Server 2008 R2 on Windows server 2008 R2 and up until recently we were having no problems. After windows updates recently (no sql server updates) we cannot run step 1 of our maintenance plan DBCC CHECKDB It does not error out, it just runs indefinitely, longest time so far 36 hours. The DB is only about 500MB so I dont think size is an issue.

I was just wondering if anyone has seen this sort of issue in the past.

Edit:
For what it is worth I also noticed rebuilding index and updating statistics is having the same issue. I have no idea if this helps.

Edit2:
So if if it helps anyone after running SELECT * FROM sys.dm_exec_requests; I'm seeing the wait type CXPACKET on the following tasks: ALTER INDEX, DBCC;


texts: Using sp_send_dbmail with SQL authentication to send attachments from UNC path

texts:
I'm trying to send emails with attachments using a SQL authentication account that I have granted credentials to.

The login used in the stored Credentials is a domain account that has permissions to read from the share.

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'me@abc.xyz.nz',
    @profile_name = 'Alarms',
    @subject = 'Test with Attachment',
    @body = '*** Alarms ***' ,
    @file_attachments = '\\Servername\Share\Images\TestImage.png'

But when I use the SQL login I get the following error:

  Msg 22051, Level 16, State 1, Line 26 The client connection security
  context could not be impersonated. Attaching files require an
  integrated client login

What have I missed? how can I test that the credentials have been applied correctly? Does this only work for a local share?

Edit: I granted my SQL login Sysadmin privs and it now works with both a local share and with a remote share. So what privs does sysadmin have that allows this to work?


texts:
I have a table which has more than 200 columns, I want to check if any of the column is having only null/ blank values in the entire table. I tried to do it using

SELECT 'SELECT ' || STRING_AGG(   'max("'
                               || column_name
                               || '") "'
                               || column_name
                               || '"',
                               ',')
                 || ' FROM <table name>;'
  FROM information_schema.columns
 WHERE table_name='<table name>';

From here, it will give me max values of a column, and I understand that columns with values null is what I am looking for, but question is how do I display that. I am using PostgreSQL DB.


texts:
To getting the disk space details I am using the following query.

E.g.
SELECT distinct(volume_mount_point),
 total_bytes/1048576 as Size_in_MB,
 available_bytes/1048576 as Free_in_MB,
 (total_bytes/1048576)-(available_bytes/1048576) as Used_in_MB,
 (((total_bytes/1048576)-(available_bytes/1048576))/(total_bytes/1048576))*100 as Used_in_Percentage
FROM sys.master_files AS f CROSS APPLY
 sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
 available_bytes/1048576 order by 1

But the above query returns '0' in percentage column.How to fix this error?


texts: How can I query for existing database snapshots in SQL server?

texts:
I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it.

For example, if I were to create a snapshot like this:

CREATE DATABASE [DatabaseA_Snapshot] ON
(NAME=DatabaseA, FileName='<whatever>')
AS SNAPSHOT OF [DatabaseA]

Is there a way I can query for the existence of that snapshot again later? I can see that it shows up in sys.databases, but I couldn't find any information that would help me determine that it's a database snapshot that was created from DatabaseA.

SQL Server Management Studio's Object Explorer places it under the 'Database Snapshots' folder so there's obviously some way to differentiate these from regular databases.


texts: Is splitting tempdb files across drives necessary?

texts:
If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?

I want to persuade my manager to put all 4 tempdb files on one drive.
If one of those tempdb files became corrupt, e.g. file D on drive B, would file A and B on drive A and file C on drive B continue to function, would tempdb 'break'? I assume SQL would create another tempdb file, based on the temporary nature of tempdb.

Thanks


texts: Why “sudo vim /etc/my.cnf” file is empty?

texts: I want to edit some of InnoDB variables ,I go to the .my.cnf which is located in home/.my.cnf I found the file just empty. And when I check SHOW VARIABLES in MySQL it gives me all the variables and their values. Why the file is empty and how can I edit the needed variables ?


texts: Storing multiple contact addresses for a person

texts:
Simplified diagram for reference.

My database stores information about people in the following way. There is a  Person table containing common personal information fields; name, date of birth, gender, etc.

The contact information for each person is kept in the ContactInformation table with a 1:1 relationship.

Finally, there are then individual tables for each specific type of person in the organisation we store data on with a 1:1 relation to Person

I have a bit of a problem with the Student table. Basically my client now wants to store both a home address and term time address for a student. But I'm not sure how to approach this.

I see three possible options/solutions:

Junction Table - Create a junction table (e.g. ContactAddressMapping) between Person and ContactInformation.
New Table - Create a TermTimeAddress table with a 1:1 relation to Student
New Fields - Add extra fields to the Student table to hold the term time address.

Now here are the main "problems" I see with each option:

A junction table would allow multiple addresses to be stored for a person, but we'd only ever need to do this for students (for now). There would also be no way to determine which address is the home/term time address without using some kind of boolean field which seems like pointless information in that table since it's only unique to students.

Creating a new table separates the term time address from the main address but these feels like duplication since I'm basically creating a specific type of address with the same fields, minus things like phone numbers and email addresses.

Adding the fields to the Student seems like I'm cluttering the table with information that should be elsewhere. The table should only be for information about that student such as their school, the area of study and the start and end date of their placement.

Which of these would be the most appropriate option, or is there another solution that I haven't seen yet?


texts: Sort spills to tempdb due to varchar(max)

texts:
On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables:

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

with following non-clustered indexes:

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

The database is configured with compatibility level 120.

When I run this query there are spills to tempdb.
This is how I execute the query:

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

If don't select the [remark] field no spills occurs. My first reaction was that the spills occurred due to the low number of estimated rows on the nested-loop operator.

So I add 5 datetime and 5 integer columns to the settings table and add them to my select statement. When I execute the query no spills are happening.

Why are the spills only happening when [remark] is selected? It has probably something to do with the fact that this is a varchar(max).
What can I do to avoid spilling to tempdb?

Adding OPTION (RECOMPILE) to the query makes no difference.


texts: Appending data from one program to the other using batch commands

texts:
I have a database which sometimes crashes. I want to create a batch program that does this:

Runs SQL*Plus.
Makes SQL*Plus run a script from a plain text file.
Gets the results back to the main batch program.
Uses it to check if database is running or not.

I am having trouble in getting SQL*Plus to run something directly through the batch and getting the data back. Hopefully it's a simple process I don't know about.


texts: How can I enforce that my composite key columns do not equal one another in MySQL?

texts:
I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a users table. I'm trying to make sure that something like this cannot happen:
requester_id   addressee_id   created_at
1              1              2020-08-09 18:40:23

I've also added an index expression to ensure that there can only be one unique pairing of IDs as well:
alter table `user_relationships`
add unique index `unique_relationships_index`
((least(requester_id,addressee_id)), (greatest(requester_id,addressee_id)))

To avoid having 1|2 and 2|1.


texts: Delete inactive users - how to prevent jobs and reports to stop working

texts:
We have several Microsoft SQL servers in our organization and the previous DBA have not performed user reviews on any of them, so some servers are filled with inactive local users. I have a script that looks at their last logon date:

SELECT MAX(login_time) AS [Last Login Time], login_name [Login]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY [Login] ASC;

But how can I create a similar script that lists all jobs and reports made by the user, so that they won't stop working when and if I delete the inactive users? Are there any best practices when it comes to deleting users from Microsoft SQL Servers?


texts: Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

texts:
I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS.  Recently, the database has starting going unresponsive for 10-30 minutes at a time during peak traffic time.  This often happens 3 or 4 times throughout the peak traffic hours.
During these unresponsive times, it is almost impossible to open a connection with the database (timeouts are the most common response).  If you get a connection, queries normally perform as expected for a short while.  The processlist shows dozens to hundreds of items with state "init" and info "commit".  Row operations drop nearly to zero and stay there for minutes on end until the database suddenly begins to recover, the processlist clears, and it becomes responsive to traffic once again.
What we have attempted so far:

We have tried to remove all slow queries during these peak hours, shutting down large swaths of functionality and workers during these times.
We have increased the redo log size in innodb.
We have looked for lock contention and deadlocks.
We have doubled the compute power, memory, and throughput available to the RDS instance.   (DB CPU usage during peak hours hovers around 50% and there are no alarming spikes in memory or disk or network usage.)
We have tried using proxy servers to hold open long-lived connections to the database.
We've looked for any recent changes or new queries introduced in the app.

All of that only helped a little bit.  We often still see at least one "storm" where we get stuck in the bad state, and re-enabling any batch jobs tends to push us over the edge.
Has anyone seen the pattern of processes getting stuck in STATE=init, INFO=commit for minutes on end?  Does anyone have suggestions on how to proceed with debugging or analysis?  What other resource contention could we be running into?