texts: Database design to track student attendance

texts:
I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students.

Creating a database for attendance to track their attendance will have almost 4000*30days*12months => ~ 1,400,000 rows (neglecting vacations/ sundays).

The attendance table will have:

id (INT)
student_id (INT)
course_id (INT)
data (DATETIME)
present/absent (TINYINT, as I'll store 1/0)
comments

I'm using PHP/MySql. With a table becoming so large, is there any other way?


texts: Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?

texts:
We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.

I just recently received approval to start setting up new environments with SQL 2017.

I would like to understand if there are any code migration related changes that might need to be made for a SQL 2017 installation or would running on SQL 2017 with SQL 2016 DB compatibility mode behave the same as if it was just a SQL 2016 installation running with the full SQL 2016 (130) compatibility mode?

Reading through this link I found the following

  To upgrade the SQL Server Database Engine to the latest version, while maintaining the database compatibility level that existed before the upgrade and its supportability status, it is recommended to perform static functional surface area validation of the application code in the database, by using the Microsoft Data Migration Assistant tool (DMA). The absence of errors in the DMA tool output, about missing or incompatible functionality, protects application from any functional regressions on the new target version.

Assuming this check passes, is there anything else that I should be doing or looking at?


texts: MATCH(title) AGAINST('test 11' IN BOOLEAN MODE) can't search number

texts:
SELECT *, MATCH(title) AGAINST('test 11' IN BOOLEAN MODE) AS     Score
FROM search_table
WHERE `date` > DATE_SUB(NOW(), INTERVAL 1 MONTH)
AND MATCH(title) AGAINST('test 11' IN BOOLEAN MODE)
ORDER BY  `score`  DESC ,`date`  DESC

This query is not working for the number and only returns results related with the test. There are rows with 11, but nothing is returned when I search for 11.


texts: How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row

texts:
I have a query where I JOIN 4 weekly "summary" tables, and SELECT a MIN and a MAX of units FROM each table. This would give me the highest/lowest total units sold for, and I'm pulling an average from another table.

    SELECT
            L4.TotalUnits / 4   AS AvgMvmt,
            MIN(Ago.TotalUnits) AS LowMvmt,
            MAX(Ago.TotalUnits) AS HighMvmt
    FROM

            RTG_LOOKUP_LAST4 AS L4 INNER JOIN
            (
                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

                    UNION

                    SELECT  UCC12UPCCode, TotalUnits, Date
                    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
            ) AS Ago
                    ON Ago.UCC12UPCCode = L4.UCC12UPCCode
    WHERE
            L4.UCC12UPCCode = '01254601144'
    GROUP BY
            L4.TotalUnits

Now I need to add two more columns, HighDate and LowDate. These columns represent the date for the week that sold the highest/lowest units.

The Ago table looks like this:

UCC12UPCCode  TotalUnits  Date
------------- ----------- ----------
01254601144   90          2018-04-14
01254601144   98          2018-05-05
01254601144   107         2018-04-21
01254601144   132         2018-04-28

How would pull the Date column from the correct table so my end results looks like below:

LowMvmt     HighMvmt    LowDate    HighDate
----------- ----------- ---------- ----------
90          132         2018-04-14 2018-04-28

E: Fiddle
https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=749c59e1f11bc2146a605072f1ef7d98


texts: Transaction on defriend and multi databases

texts:
I have a big issue when i'm working with multi database and diffrent databases as same time.

The problem is that I'm working with mssql, mysql and mongodb at sametime, and what I need is a way to be sure whats in "mysql" its there when I hit mongodb and, if something failed in eg. mssql, then rollback all modifications.

I know I can code so I check with a select before everything every time, but if my rollback in this case get disconnected how can I be sure on my rollback is success?

Is there a nice way to handle this issue, or its just brian-hedick to work with multi database connections and transactions?


texts: Does SQL Server cache data results for DAO?

texts:
I was surprised to be confronted with the idea that using .NET and DAO one could make use of the SQL Server data cache in a way not at all possible with stored procedures (which benefit from the plan cache). I.e. that the query results would be cached and your .NET application run much faster due to this. Is this non-sense? There seems to be some sort of caching for DAO but that doesn't seem to be really relevant in this case.

I've read this question, but it doesn't really answer the question, because it's about stored procedures only.

I hope I post this in the right place. Any help appreciated.


texts: Why does my procedure run slowly until I update statistics?

texts:
I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running.  Data is extracted from source and loaded to staging tables in the destination DB, and then the transform processes are run.  On Tuesday (26 Nov) for no known reason, the procedure which 'shreds' the staging data for providers into the relational tables went from running approx 9 per second to one every 5½ minutes.  No parts of the database, ELT process (not a typo!), database server etc were changed.

With around 800 records every night (and up to several thousand) this single step of the process has gone from taking just over a minute to potentially taking just over 3 days (this doesn't happen in practise of course because we notice and stop the long running job).  This happens even if I manually run the code which is within the procedure rather than letting it run as part of SSIS, and the same happens if I run through the code for just one record rather than letting the loop go through.

By sheer chance I happened to discover that if I ran the following code against the provider staging table and the provider destination relational table, suddenly my ELT process runs as normal again and zips through everything:

update statistics tblProviderStaging with fullscan
update statistics tblProvider with fullscan

My question (sorry for the journey to get here) is why would I have to run this manually every morning?  I can't even run it before the ELT as it makes no difference - only affects the process if I run it while the process is slowly churning through.  Presently I am running this at 7am every morning (SSIS kicks off at 6:30).  Auto Update Statistics and Auto Create Statistics are both set to 'True'.

I have never, in my limited experience, come across a scenario where I have to manually run this every morning?  And the fact that I can't just run it either but have to run it while the ELT is still running?  We have tried running the SSIS package from a different server, against a different destination database server (restored the DB to another DBS), have checked all indexes, I/O performance is running as normal, there is no undue server load happening while this runs (it was running about 18% capacity and nothing to speak of on any of the performance metrics).

The staging table in question has just over 12million rows and uses up just over 1.5GB of space (including index size).  The destination table in question has 430k rows and uses around 140 MB of space (including index size).  This has not changed significantly.

If there is any more information I need to provide please let me know but I'm shooting into the dark here so I don't know what is helpful and what is just in the way.  If anyone has any ideas of places and things to check to see why I am having to do this please shout!


texts: Microsoft Help Viewer in ssms 2014 doesn't open local documentation

texts:
I have Visual Studio 2013 installed
I have downloaded all documentation I need installed via visual studio. I can open it with the command

"C:\Program Files (x86)\Microsoft Help Viewer\v2.1\HlpViewer.exe" /catalogName VisualStudio12 /locale en-US /sku 3000 /launchingApp Microsoft,VisualStudio,12.0 /manage

And it works fine, but in SSMS, I want to be able to use the SHIFT + F1 shortcut to open local documentation quickly. (select keyword and press SHIFT + F1 to open it)

I have SSMS 2014, if I select a SQL keyword and press SHIFT + F1, I get an error that says that I don't have local documentation, it is also trying to open Help Viewer 1.0 instead of 2.1. I want to know what I have to do to make SHIFT + F1 open sql documentation in SSMS 2014.


texts: MySQL Subquery hangs / terrible perfomance

texts:
I am using MySQL 5.5.16 @ CentOS.

Please have a look at this query:

SELECT
    inventory_ID,
    (
    SELECT
        price_brutto
    FROM
        _inventory_prices
    WHERE
        _inventory_prices.inventory_ID = _inventory.inventory_ID AND
        price_type_ID = 4
    ORDER BY
        price_date DESC
    LIMIT 1
    ) as price_brutto
FROM
    _inventory
ORDER BY
    inventory_ID ASC
LIMIT 1

It takes forever to execute this query! But separate queries:

SELECT
    inventory_ID
FROM
    _inventory
ORDER BY
    inventory_ID ASC
LIMIT 1

AND

    SELECT
        price_brutto
    FROM
        _inventory_prices
    WHERE
        _inventory_prices.inventory_ID = 12345 /* this is first inventory_ID from _inventory table */ AND
        price_type_ID = 4

Runs in < 0.01s ! (First query returns 1 row, second 0 rows)

_inventory table is filled with ~60k rows
_inventory_prices table is filled with ~40m rows

Why main query (with subquery) is so terribly slow?

Structure:

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory` (
  `inventory_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `SKU` VARCHAR(200) NULL DEFAULT NULL ,
  `item_ID` INT(11) NOT NULL ,
  ... data columns ...,
  PRIMARY KEY (`inventory_ID`) ,
  UNIQUE INDEX `SKU_UNIQUE` (`SKU` ASC) ,
  INDEX `fk__inventory__items1_idx` (`item_ID` ASC) ,
  CONSTRAINT `fk__inventory__items1`
    FOREIGN KEY (`item_ID` )
    REFERENCES `ca_ERP`.`_items` (`item_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 64930
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

CREATE  TABLE IF NOT EXISTS `ca_ERP`.`_inventory_prices` (
  `inventory_prices_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `inventory_ID` INT(11) NOT NULL ,
  `price_type_ID` INT(11) NOT NULL ,
  `price_brutto` DECIMAL(9,2) NULL DEFAULT '0.00' ,
  `currency_ID` INT(11) NULL DEFAULT '1' ,
  `is_custom` TINYINT(4) NULL DEFAULT '0' ,
  `price_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`inventory_prices_ID`) ,
  INDEX `fk__inventory_prices__inventory1_idx` (`inventory_ID` ASC) ,
  INDEX `fk__inventory_prices__currencies1_idx` (`currency_ID` ASC) ,
  INDEX `fk__inventory_prices__marketplaces1_idx` (`price_type_ID` ASC) ,
  INDEX `dateindex` (`price_date` ASC) ,
  INDEX `priceindex` (`price_brutto` ASC) ,
  CONSTRAINT `fk__inventory_prices__currencies1`
    FOREIGN KEY (`currency_ID` )
    REFERENCES `ca_ERP`.`_currencies` (`currency_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__inventory1`
    FOREIGN KEY (`inventory_ID` )
    REFERENCES `ca_ERP`.`_inventory` (`inventory_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk__inventory_prices__marketplaces1`
    FOREIGN KEY (`price_type_ID` )
    REFERENCES `ca_ERP`.`_price_type` (`price_type_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3408033
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci

Explain result:

1   PRIMARY _inventory  index       PRIMARY 4       1   6220900.00  Using index
2   DEPENDENT SUBQUERY  _inventory_prices   ref fk__inventory_prices__inventory1,fk__inventory_prices__marketplaces1    fk__inventory_prices__inventory1    4   ca_ERP._inventory.inventory_ID  177 100.00  Using where; Using filesort


texts: Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?

texts:
Sorry if my title doesn't make much sense, I wasn't sure how to word it though.

Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column imported as [Address ; Address]. Is there a way to remove everything after the first Address, including the ';' in one query?

I thought about the REPLACE function, but wasn't sure how to update every record at once since each store has a different Address. I'll include a screenshot so my question makes more sense. Any suggestions? I was thinking since there's a delimiter it might not be too hard to update, but I'm lost as to how.

Any help is greatly appreciated. Thank you.

Ignore the first record as it was previously setup.

Technically using Progress ODBC Query Tools. I can update using SQL Server 2016 though.


texts:
Table dbsize loading with data of every database size every night.

dbsize (id, database_name, database_size, insert_date)

It looks like this:

 - 1   dbname1   1000   1/1/1900
 - 2   dbname2   2000   1/1/1900
 - 3   dbname3   3000   1/1/1900
 - 4   dbname1   1200   1/2/1900
 - 5   dbname2   2200   1/2/1900

... etc

I need to get result like this

 - insert_date dbname1 dbname2 dbname3
 - 1/1/1900      1000      2000     3000
 - 1/2/1900      1200      2200

Tried with this sql, but nothing:

declare @ColumnNames NVARCHAR(MAX) = ''

declare @SQL NVARCHAR(MAX) = ''

select @ColumnNames += QUOTENAME(database_name) + ',' from  dbo.dbsize

set @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1) set @SQL =
'select * from ( select     database_name   ,insert_date    ,id     from
dbo.dbsize) as BASEDATA

PIVOT   (

    count(id)   for database_name   in      ('

        +@ColumnNames +

        ')  as PivotTable'

execute sp_executesql @SQL


texts: Can not reset MariaDB password "can't connect to local MySQL server through socket"

texts:
When I go

mysqld_safe --skip-grant-tables &

This message is outputted.

170802 14:14:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

When I try to run mariadb, I get

$ mariadb
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

When I check the logs, I don't see anything.


texts: Historical reporting from the database with Reporting Services?

texts:
We run a job tracking service as part of the company software.  Reports are generated by SQL Server (Standard) Reporting Services usually daily but often as and when needed before meetings etc.

As the jobs progress their status changes and the details of the reports change.  Due to the legacy of the front end application and the database we do not store as many milestones as needed for managers to look back at historical reports.

Managers quite often ask “Can we see where XYZ was last week?” or things like “Let’s have a look how Project1 was performing last June?” but running the reports on the Project1 database backup all show 100% complete as it is now finished.

What is the best way to enable historical reporting at a daily level with SQL Server Standard?

Ideally we’d like to keep 3 months of  5 different reports stored daily if possible?


texts: Getting “SQL Server Alert System: 'Alert Severity 20' occurred” error occasionally

texts:
We occasionally get following email alerts.

DESCRIPTION:    The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.

DESCRIPTION:    Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.

I tried to search online. But I haven’t got any good direction about how exactly I should proceed to troubleshoot these alerts. Should I contact application team to check buggy code or security team to check any malicious activity in this case? Appreciate your guidance.


texts: Data Warehouse - DB2 to SQL Server: how & what impacts on ETL?

texts:
I'm working towards migrating an IBM DB2 database to SQL Server, however the DB2 database sits in a Data Warehouse environment.

As I am very new to Data Warehousing, DB2 and DB Migration between different types of databases, I would like to ask about how to best approach this and understand what components are involved (listing or linking elsewhere is fine - happy to read a bunch of things or learn something new).

The context:

The ETL tool in use, and to be kept in use, is Informatica
The only application that queries the DB is Cognos
There are 4 DB2 DBs in use on 2 (non-windows) hosts, one of these is 'H1' which has a 'DB2-DW-PROD' DB used as part of the Data Warehouse
I am interested in migrating the 'DB2-DW-PROD' DB to some space I have on an existing (Windows) SQL Server host, 'H2' under the name 'SQL-DW-PROD'. This host currently has several DBs on it for other purposes (in one instance) and can have SSRS/SSIS or other services/resources installed or configured if needed.
I would like to clean up the poorly maintained DB a little bit during the migration, if possible, based off what has not been used/updated in the past couple of years.

What I would like to know, directly or indirectly:

Should I create a new instance on H2 or is a single new database enough?
Does the new instance/DB need particular resources? If so, where from?
Should I use a particular Microsoft tool or use some type of import/export to get the data I need? (Is Informatica involved in this process?)
Someone mentioned 'metadata tables' for Informatica and/or Cognos, should I be wary of something?
Another mentioned maintaining sequence values -How is this best achieved; is it needed?
Any changes on the Informatica side are not my personal concern but the work of another, as such, Is there anything I should provide this person (apart from the address of the new db)?

I'm very new in this field, so anything dumbed down is greatly appreciated. That being said, anything complicated but necessary or good to know is as well. It's a steep learning curve for me but I'm kind of stumped right now and need a hand.

I have no control over the process - Informatica and Cognos must be kept as they are. Also, at this stage, I am not able to view the DB2 DB but I know it is not too large. At the very, very most, assume it's 1 TB for the purpose of this question.

At the moment I am simply trying to understand the process I will follow and what I will need to look out for when the time comes.

EDIT: Have received plenty of feedback on the instance vs database part of the question which I greatly appreciate. However, I am still confused as to how I should approach migrating the data accross. I.e. 'how, or what should I use, to migrate such that I have everything I need to support Informatica and cognos?'

The most pressing issue is that I don't understand any of informatica's/cognos's dependencies or know how db2 works. I am not confident that simply copying user table data accross is enough and would really like some confirmation or pointers to know exactly what to do.


texts: Best design for adding a folder/file relationship

texts:
I was given a database that has Projects and Media_files.  They were in a simple 1 to many relationship.

I now need to add Folders to Projects.  Media_files can be placed in Folders, and Folders can have Folders within them.

I am at a loss as to the best way to, first, have Folders belong to other Folders, and second, the best way to maintain the current relationship between Projects and Media_files, or if its best to break this relationship.

Projects is a simple table:

id,
title,
description,
owner_id.

Folders are an entirely new table, but my basic idea for them was:

id,
project_id,
parent_folder_id,
title,
description


texts:
This server was recently installed and configured.  I was in the process of creating some maintenance plans to backup the logs and DBs on a daily basis.  Regardless of my method to perform a full backup, it would fail immediately citing the generic error message

Error: 3041, Severity: 16, State: 1.

This is what I found in the SQL Error Logs:

I've never seen this before and wasn't able to find much during research.  I've verified that the associated service account has permissions to the backup folders and there is more than enough space.

Other details

SQL 12.0.4213.0
Windows Server 2012 R2 Standard
VMware virtual machine


texts: sort results by highest performing country & region

texts:
I want to check which country & regions are performing the best in sales.
I have the following order table:

| order_id | amount |
|----------|--------|
| 100      | 1000   |
| 101      | 250    |
| 102      | 120    |
| 103      | 500    |
| 104      | 25     |
| 105      | 2000   |
| 106      | 300    |

And order_address table:

| order_id | country | region |
|----------|---------|--------|
| 100      | US      | CA     |
| 101      | US      | LA     |
| 102      | UK      | GL     |
| 103      | UK      | GL     |
| 104      | UK      | GB     |
| 105      | CA      | OT     |
| 106      | AU      | QL     |

I want to get orders data by country & region and sort by highest performing country (region).

SELECT
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM order o
INNER JOIN order_address oa ON o.order_id = oa.order_id
GROUP BY oa.country, oa.region
ORDER BY SUM(o.amount) DESC, oa.country, oa.region

But this is not working as expected as it's breaking the country sequence. Other country results should only follow if all regions of high performing countries are displayed.

In fact, here is the desired result:

| country | region | total_sales |
|---------|--------|-------------|
| CA      | OT     | 2000        |
| US      | CA     | 1000        |
| US      | LA     | 250         |
| UK      | GL     | 620         |
| UK      | GB     | 25          |
| AU      | QL     | 300         |


texts:
I'm try compiling MySQL 5.6 from source in Ubuntu 12.04 x64.

But when I execute make install DESTDIR="/etc/mysql", not work was expected.

MySQL is installed in /etc/mysql/usr/local/mysql.

What's wrong?

Sorry for my english


texts: I need a way to give a sql authenticated user read access to a windows directory

texts:
A SQL-authenticated user SBA_USER is selecting from a View which uses Linked Server which points to an Excel spreadsheet in the I:\Folder directory on a windows server.
Problem I have is that the access to the directory is controlled by Windows and the Login SBA_USER is not a Windows account.
I need a way to give SBA_USER read access to the directory I:\Folder. Please suggest.


texts: Problem with a foreign key referencing a primary key which is also part of an unique key (MariaDB)

texts:
I created a database using MariaDb. I have the following author table (with id as primary key), which is extended by two other tables,  author_personal and author_corporate, representing two different kinds of author and having different fields:
CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author_type` char(1) NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  UNIQUE KEY `author_UN` (`id`,`author_type`) USING BTREE,
  CONSTRAINT `author_CHECK_type` CHECK (`author_type` in ('P','C'))
);

CREATE TABLE `author_personal` (
  `id` int(10) unsigned NOT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  -- other fields
  `author_type` char(1) GENERATED ALWAYS AS ('P') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_personal_FK` (`id`,`author_type`),
  CONSTRAINT `author_personal_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

CREATE TABLE `author_corporate` (
  `id` int(10) unsigned NOT NULL,
  `corporate_name` varchar(50) DEFAULT NULL,
  `corporate_acronym` varchar(5) DEFAULT NULL,
  `author_type` char(1) GENERATED ALWAYS AS ('C') VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `author_corporate_FK` (`id`,`author_type`),
  CONSTRAINT `author_corporate_FK` FOREIGN KEY (`id`, `author_type`) REFERENCES `author` (`id`, `author_type`) ON DELETE CASCADE
);

Although author.id would be enough, I decided to create an UNIQUE KEY (id, author_type) to be referenced by the foreign key in the two other tables, so that it would be impossible to reference from author_personal an author which is not flagged as P, and from author_corporate an author which is not flagged as C.
The problem rises when I want to reference author using the primary key, like in this table:
CREATE TABLE `work_authors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `work_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  -- other fields
  PRIMARY KEY (`id`),
  KEY `work_authors_FK` (`work_id`),
  KEY `work_authors_FK_author` (`author_id`) USING BTREE,
  CONSTRAINT `work_authors_FK` FOREIGN KEY (`work_id`) REFERENCES `work` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `work_authors_FK_author` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON UPDATE CASCADE
);

Both DBeaver and PhpMyAdmin think that work_authors_FK_author references author_UN instead of the actual primary key (author.id).

This means that in DBeaver I am not able to click on the values in work_authors.author_id and open the referenced record in authors because I get the following error:
Entity [davide_library.author] association [work_authors_FK_author] columns differs from referenced constraint [author_UN] (1<>2)
I created the foreign keys using DBeaver. Although I selected PRIMARY as the unique key, in the list of foreign keys it always show author_UN as the referenced object. I don't know if this behavior depends on MariaDB or DBeaver.
My question is:
Is there any way to explicitily reference in the DDL the primary key instead of the unique key?
or, alternatively:
Is there a different way, other than the UNUQUE constraint, to check that author_personal references only authors flagged with P, and the same for author_corporate?


texts: Simplest MySql query uses filesort and ignores created_at index?


texts: Can I do anything about a very long running KILL command?

texts:
I've got a table with 103M+ records in it and takes up 70GB on the disk.  I decided to DELETE (in one statement) 70% of the records.

As expected, this took an awfully long time to run.  I decided to KILL that DELETE command before it finished (although it had been running for approx. 350K seconds, as shown by SHOW PROCESSLIST).

The command (as shown by SHOW PROCESSLIST) has now changed to 'Killed' with the state now being 'freeing items'.  It is still running, now with a time of 450K seconds.

My question is this, is there anything that I can do to actually stop the command from running?  To stop it from doing the freeing?  Will restarting mysqld itself have that effect?

Is there anyway to judge how far through its freeing process it has gotten?

I ask because, it is actually using a lot of the machine's resources and is having an impact on the performance of the rest of the application.

show processlist;
| Command | Time   | State         | Info
| Killed  | 449822 | freeing items | DELETE FROM *****

As requested, here is the create table and delete statements in question:

 DELETE FROM adc_fetched_documents WHERE id <= 73097292

CREATE TABLE `adc_fetched_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adc_fetch_run_id` int(20) NOT NULL,
  `adc_linked_from_id` int(20) DEFAULT NULL,
  `adc_fetched_document_blob_id` int(20) DEFAULT NULL,
  `adc_scraped_property_id` int(20) DEFAULT NULL,
  `adc_duplicate_document_id` int(20) DEFAULT NULL,
  `status` int(11) NOT NULL,
  `document_type_id` int(4) NOT NULL,
  `fetched_at` datetime NOT NULL,
  `checksum` varchar(256) DEFAULT NULL,
  `http_response_code` int(11) DEFAULT NULL,
  `content_type` varchar(256) DEFAULT '',
  `url` text,
  `http_header` text,
  `search_frag_id` int(11) DEFAULT NULL,
  `adc_property_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_ADC_DUPLICATE_OF` (`adc_duplicate_document_id`),
  KEY `FK_ADC_FETCHED_DOCUMENT_BLOB` (`adc_fetched_document_blob_id`),
  KEY `FK_ADC_FETCHED_DOCUMENT_SCRAPED` (`adc_scraped_property_id`),
  KEY `FK_ADC_URL_FROM_RUN` (`adc_fetch_run_id`),
  KEY `FK_SCR_URL_LINKED_FROM` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetch_run_id` (`adc_fetch_run_id`),
  KEY `index_adc_fetched_documents_on_adc_linked_from_id` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetched_document_blob_id` (`adc_fetched_document_blob_id`),
  KEY `index_adc_fetched_documents_on_adc_scraped_property_id` (`adc_scraped_property_id`),
  KEY `index_adc_fetched_documents_on_adc_duplicate_document_id` (`adc_duplicate_document_id`),
  KEY `index_adc_fetched_documents_on_document_type_id` (`document_type_id`),
  KEY `index_adc_fetched_documents_on_checksum` (`checksum`),
  KEY `index_adc_fetched_documents_on_search_frag_id` (`search_frag_id`),
  KEY `index_adc_fetched_documents_on_adc_property_id` (`adc_property_id`),
  CONSTRAINT `adc_fetched_documents_fetch_run` FOREIGN KEY (`adc_fetch_run_id`) REFERENCES `adc_fetch_runs` (`id`),
  CONSTRAINT `adc_fetched_documents_parent` FOREIGN KEY (`adc_linked_from_id`) REFERENCES `adc_fetched_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `adc_fetched_documents_scraped_property` FOREIGN KEY (`adc_scraped_property_id`) REFERENCES `adc_scraped_properties` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=107925926 DEFAULT CHARSET=latin1

As for my it's taken so long to perform the DELETE, I'm not too worried about for now.  I assume that there are various variables that need tweaking.

Many thanks in advance for any help.


texts: How to select the latest record of each hour in a day?

texts:
I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table. What is the best way to do it? I already tried group by HOUR(reading_on) with HAVING("max(HOUR(reading_on))"), but I didn't get the proper result.

Below is my table structure


texts: Can a 2008 SSIS package Connect to a Newer SQL Instance?

texts:
I am working on a project to upgrade a SQL Server instance from 2008 R2 to 2016.  This server is also running SSIS 2008.  Based on the MS documentation, it appears that different versions of SSIS can co-exist on the same server.  I would like to take advantage of this by leaving SSIS 2008 in place, yet also installing SSIS 2016 side-by-side and slowly migrating the packages to the new version.

What I am unclear on is this:

Can an SSIS 2008 instance/package connect to a SQL 2016 database (or 2012 or 2014)?  I’ve seen a lot about backwards compatibility, but not forwards.  Is anyone else doing this type of thing?

Any help would be greatly appreciated.  Thanks!


texts:
We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse.
The record count is about ~4m for PurchaseOrder and each purchase order has about ~0-150 lines inside.
The TSQL query would be something like this:

SELECT "PurchaseOrderLines.ProductID",
       SUM(SUM("$PurchaseOrderLines.ReceivingQty") - SUM("$PurchaseOrderLines.SendingQty")) TotalQty,
       "WarehouseTypeID"
FROM PurchaseOrder
WHERE (
          "Status" = 4
          OR "TransactionID" IN ( 16, 116 )
      )
      AND "PurchaseOrderLines.LedgerPostingStatus" IS NULL
      AND "TransactionID" NOT IN ( 18, 181 )
      AND "NowDate" >= 'from_date'
      AND "NowDate" <= 'to_date'
GROUP BY "WarehouseID",
         "PurchaseOrderLines.ProductID",
         "WarehouseTypeID";

and the fluent:

db.PurchaseOrder.aggregate(
  [{
      "$match": {
        "$and": [{
            "$and": [{
                "$and": [{
                    "$and": [{
                        "$or": [{
                            "Status": 4
                          },
                          {
                            "TransactionID": {
                              "$in": [
                                16,
                                116
                              ]
                            }
                          }
                        ]
                      },
                      {
                        "PurchaseOrderLines.LedgerPostingStatus": null
                      }
                    ]
                  },
                  {
                    "TransactionID": {
                      "$nin": [
                        18,
                        181
                      ]
                    }
                  }
                ]
              },
              {
                "NowDate": {
                  "$gte": "from_date"
                }
              }
            ]
          },
          {
            "NowDate": {
              "$lte": "to_date"
            }
          }
        ]
      }
    },
    { $unwind:{ path: "$PurchaseOrderLines", preserveNullAndEmptyArrays: true  }},
    {
      "$group": {
        "_id": {
          "WarehouseID": "$WarehouseID",
          "PurchaseOrderLines_ProductID": "$PurchaseOrderLines.ProductID",
          "WarehouseTypeID": "$WarehouseTypeID"
        },
        "TotalReceivingQty":{"$sum":"$PurchaseOrderLines.ReceivingQty"},
        "TotalSendingQty":{"$sum":"$PurchaseOrderLines.SendingQty"}
      }
    },
    {
        "$addFields":{
             "TotalQty": { "$subtract": ["$TotalReceivingQty", "$TotalSendingQty"] }
         }
    },
    {
      "$project": {
        "PurchaseOrderLines_ProductID": "$_id.PurchaseOrderLines_ProductID",
        "TotalQty": "$TotalQty",
        "WarehouseTypeID": "$_id.WarehouseTypeID"
      }
    }
  ],{ allowDiskUse: true })

executionStats

We have tried many indexes such as combined indexes

{PurchaseOrderLines.ProductID , PurchaseOrderLines.ReceivingQty , PurchaseOrderLines.SendingQty}

{"PurchaseOrderLines.ProductID": 1,"PurchaseOrderLines.ReceivingQty":1,"PurchaseOrderLines.SendingQty":1,"WarehouseTypeID":1,"WarehouseID":1}

and rebuilding the indexes but no luck.

It takes about ~4min for one month range, which is slow.

The hardware in use: 32G RAM and 4 core CPU running on windows server 2016 (virtual machine).

In addition we are planning to use spark and training the model, but for now we have to decrease the execution time.

So we need performance on this query, any suggestion?
appreciate your help.


texts:
This are the tables:

Material (Code, ID)
Itemtrans (IteID, FtrID, Tradecode)
Fintrade (Ftrdate, ID)

Material.ID=Itemtrans.IteID
Itemtrans.FtrID=Fintrade.ID

I want to list the last record on all the codes (Material.Code), tradecodes (Itemtrans.Tradecode) and Ftrdate (Fintrade.Ftrdate) based on Fintrade.Ftrdate (the date when registered).

I made this, but i think i am losing something :(

SELECT
         MAT.CODE COD_ARTICOL,
         FTR.FTRDATE DATA_DOCUMENT,
         FTR.TRADECODE DOCUMENT
FROM ITEMTRANS ITM
LEFT JOIN
  ( SELECT
        ID, TRADECODE,
        MAX(FTRDATE) AS maxFTRdate
    FROM FINTRADE
    GROUP BY ID
  ) AS x ON x.ID = ITM.ITEid
LEFT JOIN FINTRADE FTR ON  FTR.id = x.id
                   AND FTR.FTRDATE = x.maxFTRdate

LEFT JOIN MATERIAL MAT ON MAT.id = ITM.ITEID
LEFT JOIN FINTRADE FTR ON FTR.ID = ITM.FTRID

I need some help :(


texts: Merging adjacent date intervals into a single row

texts:
Here is the scenario:
If the employee transaction is continuous display as-is. if the employee got any gap display as a new row.

Below is the sample table

Emid    Fromdate    Todate
-----   --------    ------
A   1/1/2012    12/31/2012
A   12/31/2012  12/31/2013
A   12/31/2013  12/31/2014
A   2/2/2015    12/31/2099

Expected output:

emid    Fromdate    Todate
======  ========    ========
A   1/1/2012    12/31/2014
A   2/2/2015    12/31/2099


texts: Force SQL Server to use fragmented indexes?

texts:
I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index fragmentation can easily go over 90%.

Right now I've addressed this by reorganizing the indexes on a daily basis and a rebuild on a weekly basis. I've also played around with fill factors etc to keep the fragmentation lower.

My main problem is that when the indexes gets too fragmented, SQL Server ignores the indexes and performs full table scans instead. When that happens, it almost kills the application since repeatedly scans of such large table is really heavy. I'm quite sure using a fragmented index would be faster in those cases.

Is there a way to let SQL Server use the index regardless of its fragmentation?


texts: SQL Server 2016 CTP -> RTM, safe to attach CTP DB files (including SSISDB)?

texts:
I'm uninstalling an expired CTP build and installing RTM, because the CTP -> RTM upgrade path is not supported. Is there any word on:

Whether it's supported to attach user database files from CTP to RTM.
Whether it's supported to attach SSISDB from CTP to RTM.
I assume it's definitely not supported to attach other system databases from CTP to RTM.


texts: Replicate_Do_DB and Replicate_Wild_Do_Table - Replication does not work

texts:
I have been tasked to set replication up on a slave host. The master database is a "data store" where tables are dropped, recreated, and reloaded on a daily basis.
My initial setup of the slave host worked fine by replication was always days behind the master. After talking to the users of the "data store", I realized that not all of the databases and not all of the tables need to be replicated over. So this is what I did
CI-DB002-PRD [root@localhost] ON (none)>  STOP SLAVE\G

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact);

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('bidw.domo%', 'bidw.consolidated%', 'bidw.cf2%', 'bidw.tesla%');

CI-DB002-PRD [root@localhost] ON (none)>  START SLAVE\G

Very quickly, replication caught up and now, the slave is 3-7 seconds behind the master.
But replication is not touching the databases I want to be replicated. Even though data changes on master every 10 minutes for the schemas I list above. I did several validation queries and results now differ. When I check the status of my slave, nothing stands out
CI-DB002-PRD [root@localhost] ON (none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.239.0.34
                  Master_User: ci02replicadb
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binary-log.009871
          Read_Master_Log_Pos: 14678596
               Relay_Log_File: ci-db002-prd-relay-bin.007914
                Relay_Log_Pos: 814824
        Relay_Master_Log_File: binary-log.009871
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact,staging,phoenix,data_science
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: bidw.domo%,bidw.consolidated%,bidw.cf2%,bidw.tesla%,bidw.ez%,bidw.ct%,bidw.gt%,bidw.tmc%,bidw.did%,bidw.Shortened%,bidw.other_revenue%,bidw.revenue%,bidw.advanced_cohort%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 814626
              Relay_Log_Space: 14679056
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 22
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a485ab14-aa57-11ea-bef5-42010aef0022
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

For "Replicate_Wild_Do_Table", I replicate only the tables listed there because the entire "bidw" database is lousy with junk tables.
What am I missing here? Am I not allowed to use both filters at the same time? I am on version 5.7.31.


texts: How many files should I create for one filegroup and 9 weekly partitions?

texts:
I have 9 weekly partitions on one filegroup. How many files do I need to create?

There are 3 tables containing 46 million, 16 million and 10 million rows.


texts:
I am trying to import an oracle database from a dump file. While importing I am getting ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3.
I have tried to fetch the information about the tablespace with this query and I am not able to understand what these columns hold information about.
SELECT * FROM DBA_DATA_FILES WHERE Tablespace_name = 'AGILE_DATA3';

FILE_NAME                                  |FILE_ID|TABLESPACE_NAME|BYTES      |BLOCKS |STATUS   |RELATIVE_FNO|AUTOEXTENSIBLE|MAXBYTES   |MAXBLOCKS|INCREMENT_BY|USER_BYTES |USER_BLOCKS|ONLINE_STATUS|
-------------------------------------------|-------|---------------|-----------|-------|---------|------------|--------------|-----------|---------|------------|-----------|-----------|-------------|
D:\APP\ORADATA\AG934\AGILE_DATA301AG934.ORA|     11|AGILE_DATA3    |26791116800|3270400|AVAILABLE|          11|YES           |34359721984|  4194302|        1280|26789019648|    3270144|ONLINE       |

I looked up at this link but as I look into my table I find Auto Extension already enabled. What is it I am not doing correctly here ? I am using data pump utility available in sql developer to import the dump file.
Also I hope that when we import the dump file it actually overwrites all of the information already present on the database. Is that correct ?


texts: How can I model and enforce constraints on a categorical item contained in a collection?

texts:
Scenario:

There is a collection of items where each item belongs to 1 category.
There is a collection of buckets which may contain at most 1 item from each category.  Oh, and an item may belong to at most 1 bucket.

I'm trying to model this with 3 sets at the moment:

categories (category_id),
items (item_id, category_id), and
buckets (bucket_id, category_id, item_id)

With

a foreign key reference between items.category_id and category.category_id,
a foreign key reference between buckets.category_id and category.category_id,
a foreign key reference between buckets.item_id and items.item_id, and
a unique index on (bucket_id, category_id)

But I'm stumped trying to prove that the item_id belongs to the category indicated by the category_id on the buckets set.

Am I going about this the wrong way? Is there a formal name for this problem? Thoughts?

EDIT: To make this more concrete, consider the following question which asks a similar question in terms of people and pet species (https://dba.stackexchange.com/a/16854/35832), but now lift the constraint to genus (genus is a grouping of species).  That is, at most one of each genus, but the item in the bucket is a member of a species.


texts: Vendor wants to put db into simple mode prior to upgrade then back into full recovery mode after upgrade. I do not want to do this because I am not sure what impact it will have on the db cluster. Won't have to drop the db from the AG then put it back in after this process?


texts: MySQL: Try to Port a Trigger from SQL Server

texts:
Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause:

CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
   ON  [dbo].[Test_PTA_Table]
   INSTEAD OF UPDATE
AS
   SET NOCOUNT ON
   DECLARE @key int
   SET @key = (SELECT TestTablePK FROM Inserted)

   UPDATE Test_PTA_Table
      SET DateEnd = GetDate(), DateReplaced = GetDate()
      WHERE TestTablePK = @key

   INSERT INTO dbo.Test_PTA_Table
   (TestTableText, DateCreated, DateEffective, OperatorCode, DateReplaced)
   (SELECT TestTableText, GetDate(), GetDate(), OperatorCode, NULL
FROM Inserted)

I'm trying to port the principle to MySQL:

DELIMITER ;;

DROP TRIGGER IF EXISTS attribut_trigger_update_before;

CREATE TRIGGER attribut_trigger_update_before
BEFORE UPDATE ON attribut
  FOR EACH ROW BEGIN
    INSERT INTO attribut SET
      id_origine      = OLD.id_origine,
      date_v_creation = OLD.date_v_creation,
      date_v_debut    = OLD.date_v_debut,
      date_v_fin      = OLD(),
      importance      = OLD.importance,
      description     = OLD.description,
      valeur          = OLD.valeur,
      abbreviation    = OLD.abbreviation;
  END;;

DELIMITER ;;

But it doesn't work.

mysql> update produit set importance=3;
ERROR 1442 (HY000): Can't update table 'produit' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>

Any idea how to do this?


texts:
I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use.

Some background:

We had some partioned tables that we converted back to non-partitioned ones
All partition-functions and -schemes were deleted
I queried the dm views for unused filegroups like this:

SELECT
*
FROM sys.filegroups fg
LEFT OUTER JOIN
sysfilegroups sfg
ON fg.name = sfg.groupname
LEFT OUTER JOIN
sysfiles f
ON sfg.groupid = f.groupid
LEFT OUTER JOIN
sys.indexes i
ON fg.data_space_id = i.data_space_id
WHERE i.object_id IS NULL

This got me a list of filegroups/files i tried to remove from the database.
But some of them could not be removed.
Example error message:

The filegroup 'FG_XXXX' cannot be removed because it is not empty.

I was not able to get the connection to tables/indexes for these filegroups via sys.data_spaces and sys.indexes:

SELECT * FROM  sys.data_spaces ds
INNER JOIN sys.indexes i
ON ds.data_space_id = i.data_space_id
WHERE ds.name = 'FG_XXXX'

I am aware that a filegroup can not be removed if it is referenced in some partition scheme.
But this cannot be the reason here, as i deleted all partition schemes/functions in the db.

Any hints what i could do to get rid of the filegroups?


texts: If a SQL Agent job step executing a stored procedure on a linked server times out,  does the agent kill the SPID on the linked server or does it continue to run on the linked server?


texts: How to add Spatial Reference ID 28992 to MSSQL server?

texts:
I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?

My end goal is to get this dataset into MS SQL Server, with the Geography Markup Language (GML) elements are stored in MS SQL Server's geography data type. After this, I want to make a connection to the MS SQL Server from QGIS, so I can do some nice data analyses.

My idea was to parse the GML elements (that are contained in an XML) using the GEOMFROMGML parser from MS SQL Server. I tried:

SELECT geography::GeomFromGml (
'<Polygon xmlns="http://www.opengis.net/gml">
<exterior>
<LinearRing>
<posList>78988.952 384548.144 78992.462 384549.74 78998.712 384555.12</posList>
</LinearRing>
</exterior>
</Polygon>'
, 28992 )

However, the above code gives the following error:
"The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view."

So, I tried to add the SRID to sys.spatial_reference_systems catalog view, using:

INSERT INTO [DataAnalyse].[sys].[spatial_reference_systems] ([spatial_reference_id]
      ,[authority_name]
      ,[authorized_spatial_reference_id]
      ,[well_known_text]
      ,[unit_of_measure]
      ,[unit_conversion_factor])
VALUES(
    28992,
    'EPSG',
    28992,
    'GEOGCS["Amersfoort", DATUM["Amersfoort", SPHEROID["Bessel 1841", 6377397.155,299.1528128]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]',
    'metre',
    1
)

This gave another error: "Msg 259, Level 16, State 1, Line 13
Ad hoc updates to system catalogs are not allowed."

So, I looked how I could update a system catalog. It was explained on DBA how to do this. But it was stated there that there is a severe risk of breaking your MS SQL Server installation, and that warranty would cease. So, it seems that updating the system catalog directly is not really an option.

I first asked this question on Microsoft Docs but I was told to ask my question here.

Moreover, people seem to have asked similar questions:

How to add new spatial reference system (on SQLservercentral) asked in 2011, no response.
Insert new spatial reference system SRID into SQL Server (on stackoverflow) asked 2019, no response.

If you have an answer to my question, or have a different idea to get me to my end goal (stated above) please let me know.


texts: standard steps to Restart Seconday server in Logshipping setup

texts:
I have to reboot my Logshipping secondary server.
what are the steps need to be taken on primary and secondary servers to make the reboot clean and without hurting logshipping.

env : SQL server 2005 SP4  64bit
windows server 2008 R2 enterprise


texts: mysql 8.0, updates are sometimes stalling

texts:
Update: The scope of this question does not cover basic performance tweaks and considers these are done already. So if you are happy to share some evident variables like innodb_io_capacity, innodb_flush_log_at_trx_commit or even setting innodb_flush_method to a wonderful knob O_DIRECT you discovered recently, this is not the case.
Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack during these periods.
Mysql config is the same, hardware is similar (same CPU, same amount of RAM and its type, same disk model (only the size is increased)), so the only two thing that changed is the FreeBSD release (11.x -> 12.x) and MySQL version (5.7.x -> 8.0). I really doubt this has something to do with FreeBSD 12, so I assume it's the MySQL 8.0 specifics.
Looking at the server performance I can say there's plenty of CPU available (at least 30% or more) and disk i/o is saturated at 50% at it's most. So the question is - what's in the 8.0 that makes updates to stall at some moments ?
Here's the update statement and it's profile:
UPDATE mp_user SET ts_update = NOW(), latitude = '42.962993621826',
longitude = '47.550437927246', accuracy = '515.91363525391',
source = 'lbs', batteryLevel = '81', steps = '0', turnovers = '0',
setCoordsTime = NOW() WHERE id = '3185997';

'starting','0.000294'
'Executing hook on transaction ','0.000072'
'starting','0.000066'
'checking permissions','0.000068'
'Opening tables','0.000144'
'init','0.000097'
'System lock','0.000095'
'updating','7.698311'
'end','0.000270'
'query end','0.000115'
'waiting for handler commit','0.012015'
'closing tables','0.000301'
'freeing items','0.000185'
'cleaning up','0.000108'

Here's the table structure:
Create Table: CREATE TABLE `mp_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` datetime NOT NULL,
  `ts_update` datetime NOT NULL,
  `lastWakeUpPush` datetime DEFAULT NULL,
  `type` enum('parent','child','admin') NOT NULL,
  `latitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `longitude` double(10,7) NOT NULL DEFAULT '0.0000000',
  `accuracy` float(10,2) NOT NULL DEFAULT '0.00',
  `source` enum('gps','wifi','lbs') DEFAULT NULL,
  `batteryLevel` int(4) DEFAULT NULL,
  `steps` int(4) DEFAULT NULL,
  `turnovers` int(4) DEFAULT NULL,
  `setCoordsTime` datetime DEFAULT NULL,
  `phoneLogin` varchar(45) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `salt` varchar(15) DEFAULT '',
  `licenseTo` datetime NOT NULL,
  `monthForRepostUsed` tinyint(1) NOT NULL DEFAULT '0',
  `monthForQuizUsed` tinyint(1) NOT NULL DEFAULT '0',
  `isTrial` tinyint(1) NOT NULL DEFAULT '1',
  `deviceUid` varchar(10) DEFAULT NULL,
  `warnings`   set('backgroundDataOff','geoDeviceOff','geoAppOff','noSim','noMobileDataTransfer','pushesOff','batteryOptimization','mic','appStats','noGoogleServices','backgroundServicesOff','wifiOff','accessibilityOff') DEFAULT NULL,
  `locale` enum('en','en-US','ru','ar','zh','cs','nl','et','fi','fr','de','el','he','hu','id','it','ja','kk','ko','lv','lt','pl','pt','ro','esp','sv','tr','uk','vi','bg','sr','pt-BR') NOT NULL DEFAULT 'ru',
  `lastNcPush` datetime DEFAULT NULL,
  `priceGroup` int(11) DEFAULT NULL COMMENT 'Foreign key to mp_geoZonePriceGroup',
  `healthCheckTime` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneLogin` (`phoneLogin`),
  KEY `ts_update` (`ts_update`),
  KEY `setCoordsTime` (`setCoordsTime`,`type`,`lastWakeUpPush`),
  KEY `deviceUid` (`deviceUid`),
  KEY `batteryLevel` (`batteryLevel`),
  KEY `mp_user_mp_geoZonePriceGroup_id_fk` (`priceGroup`),
  KEY `lastLoginidx` (`lastLogin`)
) ENGINE=InnoDB AUTO_INCREMENT=9465064 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


texts: Updating a column in Table A from a Column in Table B during an after insert Trigger

texts:
Am using MYSQL Workbench

I have a residence table with a sales tax rate and when a new residence record is inserted I want its sales_tax field updated from a tax rate_table. the 2 tables are connected by zip_code.

I am using WORKBENCH Table Triggers to create the code

I have tried several things but I get errors either on syntax or during a load

If I use "UPDATE" I get an error code 1442 which seems to indicate that "UPDATE" is not allowed.

Things I have tried, code generated by workbench

USE infrastructure$$

CREATE DEFINER = CURRENT_USER TRIGGER `infrastructure`.`Residence_AFTER_INSERT` AFTER
INSERT ON `Residence` FOR EACH ROW
BEGIN

select tr.tax_combined_rate as tax from sales_tax_rate as tr
where residence.res_zip = tr.zip_code;
set residence.res_sales_tax = tax;

END

Result ERROR: Error 1193: Unknown system variable 'res_sales_tax'

I then comment out the line “set residence.res_sales_tax = tax

Result ERROR: Error 1415: Not allowed to return a result set from a trigger

This error suggests that I can not get a value from another table from within a trigger

Can some one give me some guidance
Can this be done?


texts: How to restrict column creation for existing tables?

texts:
I have a table already created in a schema which has columns a, b, and c.  I want to restrict the user(s) from creating new columns.

How to do this?

I have gone through the manual pages about GRANT and REVOKE, but in the manual it is explained only for INSERT, UPDATE, SELECT, etc.

I want to de something like

REVOKE ALTER on Table schemaname.tablename  FROM user;


texts: How do I test if MySQL "require secure transport" is working?

texts:
I activated the checkbox named "require secure transport" in the options file, found in the MySQL Workbench.

How do I know if MySQL "require secure transport" is working? Should I use command line at this point?


texts: Replication Error when adding small table as new article

texts:
I have just added a small (200 row) table to my current replication which has now broken my entire replication scenario as it seems to have broken the Log reader. The 2 tables are unrelated.
I am attempting to locate the table the related field is in and drop this table to see if that resolves the issue. Any other help in the meantime would be appreciated if anyone has come across this issue.
The errors I'm getting are:

The process could not execute 'sp_replcmds' on 'QUEEN'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Could not locate text information records for the column "MergeUpdateDesc", ID 28 during command construction. (Source: MSSQLServer, Error number: 18773)
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0022f069:000090cb:0026}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
The process could not execute 'sp_replcmds' on 'QUEEN'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)


texts:
I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can be seen by deleting a row from a parent table with 254 or more incoming foreign key references: dbfiddle link.

There are three different counts displayed in the operator details:

Foreign Key References Count is the number of incoming foreign keys.
No matching Indexes Count is the number of incoming foreign keys without a suitable index. Verifying that the updated or deleted table won't violate that constraint will require a scan of a child table.
I don't know what Partial Matching Indexes Count represents.

What is a partial matching index in this context? I wasn't able to get any of the following to work:

Filtered indexes
Putting the foreign key column as an INCLUDE column for an index
Indexes with the foreign key column as the second key column
Single column indexes for multiple column foreign keys
Creating multiple covering indexes to enable an "index join" plan for a multiple column foreign key

Dan Guzman pointed out that multiple column foreign keys can match indexes even if the index keys are in a different order than the foreign key columns. His code is here in case someone is able to use it as as starting point to figure out more about partial matching indexes.


texts:
I think I'm nearly there with this but have reached an impasse!

I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work.

Here is the code:

DECLARE @xml XML;
SET @xml = '
<values>
    <Nationality>
        <item>
            <key>UK</key>
            <value>def</value>
        </item>
        <item>
            <key>IE</key>
            <value>def</value>
        </item>
    </Nationality>
    <Currency>
        <item>
            <key>GBP</key>
            <value>123</value>
        </item>
        <item>
            <key>EUR</key>
            <value>345</value>
        </item>
    </Currency>
</values>';

select I.X.value('(/key/text())[1]', 'int') as ItemID,
       I.X.value('(/value/text())[1]', 'int') as Store
from @XML.nodes('/values/Nationality') as O(X)
cross apply O.X.nodes('/item') as I(X);

Essentially, I want to pull out the values from Nationality into:

Key  | Value
-------------
UK   | Def
IE   | Def

I may want to do Currency later but I think if I get the above cracked then the rest will be simple enough.

Can you help please?


texts: Slow SQL Server database on a specific server

texts:
I have a SQL Server database on a production server (Windows server 2012, Intel Xeon, 24 GB RAM).
The same database is present on my computer (Windows 10, Intel I3, 8 GB RAM).

When I'm querying the databases the response time is ~2 times longer on the production server.

The size of the databases and log files are identical.


texts:
I'm currently working on a mobile app alike Google Maps or Wave, but also including parking. The application its running with MySQL primarily. With at this stage most of our data is loaded by python scripts.

In the desire of having real time data we have been learning about Redis as an option to leverage the publish and subscribe capabilities.

Although, after going through the web there is something still not clear, if we are loading data from python scripts would be required to write for each record into Redis AND MySQL at the same time? or what would be a good choice.

I look to permanently store each record, but for most queries its required to have latest updates, and somehow avoid so much polling.

                       Redis
                     /
New Record  Python ->
                     \
                      MySQL

Is this a good practice?


texts: Oracle: summing column value with subquery?

texts:
Are there some restrictions in Oracle that disallows to have a WHERE clause that compares value of column+(subquery count(*))? If the subquery table is empty, count(*) doesn't yield to zero in this scenario.

I expect this query

select * from Foo where (x+(select count(*) from SomeEmptyTable))>0

To be identical to

select * from Foo where x>0

But instead Oracle doesn't return any results with the first query. In MySql the both queries return the same result selt.

My test SQL snippets:

create table Foo ( x int );

insert into Foo values ( 1 );

create table SomeEmptyTable ( x int );

I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

UPDATE:

Or does the comparison to zero have some special meaning in this case?

Both

select * from Foo where (x+(select count(*) from SomeEmptyTable))>=0

and

select * from Foo where (x+(select count(*) from SomeEmptyTable))>-1

Returns 1 row as would be expected. Only when compared to >0 zero rows are returned.


texts: Table is dropped but ibd file still exists

texts: I was dropping some unwanted tables I have in the MySQL. When I run a drop table, its showing table doesnot exists. But when a check in the data directory, the .ibd file exists and its almost 1GB. How can i free that space/drop table now ?


texts: Selecting, filtering and aggregating in MongoDB

texts:
if we have two dataset 1-First dataset contains population data of cities in the United States of America. Each record in the dataset has the following format:

{
     "city": "ACMAR",
     "loc": [
         -86.51557,
         33.584132
     ],
     "pop": 6055,
     "state": "AL",
     "_id": "35004"
}

Second dataset contains log data from a geo-locational service. Each record in the dataset has the following format:

{
    "_id": {
        "$oid": "5191f53b1b76a5666a8cbd64"
    },
    "status": 200,
    "requrest_time": 504,   // in milliseconds
    "type": "stationboard",
    "datetime": {
        "$date": "2014-02-19T07:03:32.000Z"
    }
}

how we can get all the states that have at least 2 cities with population > 1000000 (1M). And we need to  remember that city can be split into multiple records, and we should aggregate its population first. (zips dataset)
we need find a day (date) with the largest number of HTTP 500 errors (status=500).


texts:
When I have a running MongoDB instance with some data and requests against it, can I start a second instance on another machine, register it as another node in the first instance and all the data is copied to the second node while changes on the first node in the meantime are also mirrored to the second instance? Is that possible?

The context is: I have a single machine deployment with an application and mongodb running on the same instance (Amazon EC2). Its a low budget deployment but I try to have as few downtime as possible. I'd like to change the ec2 instance every week and for that I'd need some sort of mirroring. Is that possible?


texts:
What are the prevalent best practices for managing database of an application?

One option is to have an sql script to create the empty tables, and also to start with hardcoded options. (Say all locations, or profession types). During upgrade, you know whats in there and whats not, so on the server, run an sql script that adds columns and any new data.

Another is to do this programmatically, where everytime the server runs- it checks if the db exists, and if not, create it. And during addition of new columns- there would be code that would check if a certain column exists in this schema, and if not add it with a default value of my choice, and also any new data that should be needed.

My constraints are:

Should be blindly able to restore a backup if anything goes wrong.
Minimal manual intervention
I'm planning to run it 24/7
Under addition of tables and columns: By upgrading the SQL database first, and then the application code for corresponding fields.
Under removal of tables/columns: By downgrading the application first, and the SQL database.
I'm not using an ORM.

Am I missing things here that I should add to the constraints?
Please clarify your reasoning in relation to the answer.

Also, I hope that since I'm asking for a constrained answer along with sources and reasoning, this wouldn't count as a opinion based question.

EDIT: In Added constraint(3) in response to comment.


texts: Can't create database on volume mounted as folder (Error Msg 5123 & 1802)

texts:
SQL Server 2016 SP1 + CU8 Installed on Windows Server 2016 Standard Edition.

Everything was working fine for a while and I was able to create databases until for some reason unbeknownst to me, a permissions issue popped up on my mounted volumes.

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.)
while attempting to open or create the physical file
'D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB\test.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created.
Check related errors.

The folder D:\MSSQL13.MSSQLSERVER\MSSQL\Data\UserDB is actually a volume mounted as a folder within the D:\MSSQL13.MSSQLSERVER\MSSQL\Data directory.

I googled around for hours and every link kept babbling on about ownership and icacls, and takecontrol, but that isn't the problem here. I want to be clear that the service account running SQL Server on this instance has FULL CONTROL over this mounted volume and what's above it too.

I verified Effective Access for the service account and it has explicitly granted FULL control.

My suspicion, is that it has something to do with granting permissions to a folder mounted as a volume instead of a disk. This i.s because in my tests, I can create a database with its files in the root of D:\ without any problems.
D:\ is also a volume using the same type of storage.

Also, my own user has full control, but when I try to create a file, I get this warning.

Strangely enough, I can add a folder to the UserDB folder/volume without difficulty.

How do I make this volume, mounted as a folder in the D:\ drive writeable for the MSSQL account?


texts: How and when do I use an alias in my SQL statement

texts:
I have a query in which I do some calculation and then I give that column an alias. In the next column I would like to use the result of that calculation in an IF statement. MySQL did not recognise the alias when I used it as a condition but instead required me to rewrite the whole query, put some brackets around it and then carry out the condition checking.
Here is the SQL query:

SELECT StudentId
    ,SubjectID
    , (COUNT(StudentId) / (
        SELECT COUNT(SubjectID)
        FROM lectureattendancein
        WHERE SubjectID ='MIS4' ) * 100) AS Percentage
    , IF((COUNT(StudentId) / (
        SELECT  COUNT(SubjectID)
        FROM lectureattendancein
        WHERE SubjectID ='MIS4'
        )* 100) >= 80, 'ALLOWED', 'NOT ALLOWED') AS ExamAdmit
FROM attendancein
GROUP BY StudentId, SubjectID
HAVING SubjectID='MIS4'
ORDER BY StudentId ASC

I would like to use the column alias called Percentage in the IF statement


texts: Should I split timestamp parts into separate columns?

texts:
I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later migrated to a data warehouse, from which analytics will be extracted.

At this point, I am wondering whether it is beneficial to add extra columns to the timestamp table, containing the parsed metrics such as the example below, or have a single table with the ID's.

id                       | year | month | day | hour | minutes | seconds
-------------------------------------------------------------------------
Fri Apr 13 2018 15:00:19 | 2018 |   4   | 13  |  15  |    0    |   19

vs

id
-------------------------
Fri Apr 13 2018 15:00:19

My goal is to achieve the best performance possible when querying the data warehouse, so I'm assuming having the timestamp split accordingly will result in faster queries rather than unzipping time metrics in real-time:

SELECT * FROM timestamp_table WHERE year = 2018 /* Querying values already parsed */

vs

SELECT * FROM timestamp_table WHERE YEAR(timestamp_id) = 2018 /* Parsing in real-time*/

I would appreciate some best practices input on this.


texts:
Why do we have negative length for character maximum length for  geography and varchar(max) data types?

Character_Maximum_length = - 1


texts: Aruba-MySQL: can't create/change table to engine=INNODB

texts:
We have our database stored in aruba (mysql.aruba.it) where there is a table called "task". Because of many changes in the requirements we decided to drop the table and create it again from 0 with different fields and constraints. The problem is that MySQL/Aruba won't let us create a called "task" table anymore. Or better, we can create another task table only with engine MyISAM but we need INNODB because we will use contraints and foreign keys in the table. So I have tried to create a MyISAM table and then convert it into INNODB but I get an error like this:

ALTER TABLE  `task` ENGINE = INNODB
#1025 - Error on rename of './Sql689345_4/#sql-6962_1891f' to './Sql689345_4/task' (errno: -1)

I don't know why there is this problem with this table: for other tables we have we can drop them and re-create them as many times as we want.
Is there a way to fix it?


texts: Keep track of time intensive stored procedure with prints

texts:
I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = 0, I have multiple SELECTs and an UPDATE on giant tables. (The procedure works, I've checked with a colleague)

Now in a script or a program I'd have a simple print @current_cursor or print a counter in every WHILE just to have some feedback.

How could I do something like this in MSSQL? (Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2)

Could I do a print @current_cursor, or would that be a no-go in my case?

edit

I've tested the RAISEERROR with the following statements:

DECLARE @cur_id int;
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    SELECT ID FROM TableA
    WHERE ID > 100  ORDER BY ID DESC

OPEN Cur
FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(@cur_id, 0, 0) WITH NOWAIT
    FETCH NEXT FROM Cur INTO @cur_id;
END

But this leads to some time of waiting without output and then an endless wall of (I assume one for every RAISERROR):

Msg 18054, Level 16, State 1, Line 17
Error 983700, severity 0, state 0 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.


texts: Enforcing type correctness for foreign keys into a generic typed table

texts:
We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table.

select * from equipment;

id | name | type

----+--------+----------

1 | item 1 | CONVEYOR

2 | item 2 | PICKER

3 | item 3 | CONVEYOR

I want to create another table, which represents combinations of exactly 1 x PICKER and 1 x CONVEYOR. Something like:

CREATE TABLE "picker_conveyor_combo" (
  "id" serial,
  "picker_id" integer not null,
  "conveyor_id" integer not null
);

picker_id and conveyor_id would be foreign key references into the main equipment table.

However, I'd like to enforce that picker_id always references a row where the type is "PICKER" and similarly with conveyor.

I know that triggers are commonly used for this, but triggers don't typically validate historical data. So the fact the trigger exists won't guarantee that the data is valid.

I'm thinking of an alternative approach with composite foreign keys:

create table "picker_conveyor_combo" (
  id serial,
  "picker_id" integer not null,
  "picker_type" text not null,
  "conveyor_id" integer not null,
  "conveyor_type" integer not null
);

alter table "picker_conveyor_combo"
  add constraint "check_types"
  check ("picker_type" = 'PICKER' and "conveyor_type" = 'CONVEYOR');
alter table "picker_conveyor_combo"
  add constraint "picker_foreign"
  foreign key ("picker_id", "picker_type")
  references "equipment" ("id", "type");
alter table "picker_conveyor_combo"
  add constraint "conveyor_foreign"
  foreign key ("conveyor_id", "conveyor_type")
  references "equipment" ("id", "type");

In this method, there are some extra columns. However, all data will be validated based on constraints - so that adds some extra confidence.

Is this a valid approach, or is there something fundamentally wrong with the schema design?


texts: Add INCLUDE term to primary key index in Postgres 11

texts:
I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra column using the INCLUDE term. This needs to happen without a table rewrite (i.e. no new/changed columns).

Dropping the current PRIMARY KEY constraint isn't convenient, because a lot of other tables reference the target table. I suppose it would be possible to drop the FOREIGN KEY constraints on all those tables, then drop the PRIMARY KEY constraint, recreate using a new index and then recreate the FOREIGN KEY constraints. Is there a better way to do it?


texts: Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

texts:
I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no issue, but unfortunately with our network, the connection between the two server will sometimes be down for a couple hours or more. When this happens and the connection finally comes back up, it appears the relay logs are written on the slave at the expected speed (network speed), but the application of these logs into the MySQL database is not reaching the full potential of the physical disks.

The RAM and CPU allotted are not anywhere near capped either. When replication is catching up on the slave, the disks only reach around 600KB/s, but in typical operations (Windows operations) these SSDs can reach > 500MB/s. I know these are more 'random' writes, but I was not expecting this much of a performance drop off. This is actually the same speed as a similar setup we have that use only spinning disks spec'ed at far less IOPS.

The hardware/software is the same on the master and slave: The data partition is setup in a separate RAID-10 of SSDs with the OS and application on a separate RAID 1, 64GB RAM, 88 'CPU's, both are running Windows 2016 with MySQL 8.0.15, and they are using similar configuration files. Here are the startup configs I am currently using:

[client]
port=3306
default-character-set=utf8
[mysql]
no-beep
default-character-set=utf8
[mysqld]
port=3306
datadir="W:/Data"
character-set-server=utf8
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="S:/general.log"
slow-query-log=1
slow_query_log_file="S:/slow.log"
long_query_time=10
log-bin="S:/bin"
log-error="S:/error.log"
server-id=10
lower_case_table_names=1
secure-file-priv=""
loose-local-infile=1
max_connections=500
table_open_cache=2000
tmp_table_size=7G
thread_cache_size=48
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=12G
key_buffer_size=11M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_data_home_dir="W:/Data"
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_log_buffer_size=120M
innodb_buffer_pool_size=50G
innodb_log_file_size=15G
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
slave_max_allowed_packet=1G
max_connect_errors=2147483648
open_files_limit=4161
sort_buffer_size=2M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060

REPLICATION

replicate-same-server-id=0
sync_binlog=1
gtid-mode=ON
enforce-gtid-consistency=true

Slave optimizations

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=88
slave-compressed-protocol=ON
log-slave-updates=0
binlog-group-commit-sync-delay=4000
binlog-group-commit-sync-no-delay-count=5
binlog-row-image=MINIMAL
binlog-format=STATEMENT

All of our databases use solely innodb tables. I have attempted various optimizations from my research online. I have tried adjusting slave-parallel settings and various innodb settings, none of which seem to have an affect on disk throughput when the slave is catching up. Does anyone see anything here I need to adjust or any recommendations on how to go about troubleshooting this? Or even if you think I should be looking at hardware vs. software? Thank you!

Please let me know if you need any more information.


texts: SQL Server's aggregate functions ignore nulls. How do I correct this?

texts:
According to MS's knowledgebase,

  Except for COUNT, aggregate functions ignore null values.

Does anyone know how I can get it to work correctly, that is, return a null if it attempts to aggregate with a null in the values?

Example:

SELECT SUM("col")
FROM (
    SELECT NULL "col"
    UNION
    SELECT 1 "col"
) test

I would expect this query to return NULL, not 1.


texts: Multiple Batches in SQL Server Migration Script

texts:
I'm trying to come up with a database script that will be used to migrate an earlier version of an application database to a later version. The differences are quite extensive, and accomplishing the migration will involve creating new tables, renaming tables, adding functions, adding views that have calculated columns that depend on those functions, etc. After the script is done, I will hand it to somebody else to run on the target environment.

I'd like to be able to run the entire migration in a single transaction so that if something goes wrong it will be simple to roll back.

The problem I'm running into is that when when the script tries to CREATE FUNCTION, it complains that:

  SQL80001: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.

I've done some research and found that SSMS allows the use of the GO keyword to separate batches, but every time I try to wrap the entire script in a BEGIN TRANSACTION and then include a GO to separate the create function call into its own batch, I get an error:

  Incorrect syntax near 'GO'.

I'm not sure why, since answers like this one seem to work with GO statements in there.

I'm also making an assumption (maybe incorrectly) that the person I give the script to will be using SSMS.

I also found a couple articles that mentioned using SET XACT_ABORT ON;, which I've tried outside of the transaction at the very beginning.

So my script looks something like this:

SET XACT_ABORT ON
GO
BEGIN TRY
BEGIN TRANSACTION
    -- Create some tables
    GO -- Error
    CREATE FUNCTION [...]
    GO -- Error
    -- Create some views that rely on the above function
    COMMIT TRANSACTION
END TRY -- With the GOs in there, this line also gives an error: Incorrect syntax near 'TRY'. Expecting CONVERSATION.
BEGIN CATCH
    -- Error handling, including transaction rollback
END CATCH

Any recommendations on how to migrate a SQL database using multiple batches but one transaction in a single script?


texts: How to make multiple counts in one query?

texts:
I count records with queries like

SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%word%'

For each count, mysql needs to walk thoughout the table, and this is a big problem if having long table and numerous queries.

I wonder if there is a way to make all counts in one query. In this case, when mysql walks over each row, it will process all counts, and no need to scanning the entire table over and over again.


texts: Books on optimizing and refactoring SQL stored procedures

texts:
Can you please recommend resources to write efficient store procedures, refactor and optimize them?

EDIT: - jcolebrand ♦

The intent of the answers to this post may be books or non-transient blogs. Please try not to refer to resources that have a tendency to disappear.

This question is being allowed as being intentful for long term purposes, but is not a typical Stack Exchange network question. Please do not use this question as merit why another question should be permissible to be asked. Reference to it may be made as a request for evaluation on a case-by-case basis.


texts: Oracle 11g Rel 2 installation on windows 2008 Server

texts:
I am SQLServer guy but due to some reason I need to create a VM of windows 2008 R2 standard server and want to install Oracle 11g R2 64bit. I have installed a loop back adapter and gave IP : 10.10.10.10
Subnet : 255.255.0.0

now do I need to add something in host file?

I tried installing but I never got working Enterprise Manager. It does show up when I type this   https://myhostname:1158/em with login, password and role box I tried

User     :  SYS
Password :  mysyspassword
Role     :  SYSDBA

Then I get "Inernal error occured" ..

Why Enterprise manager never works like expected or is there a bug in the 11.2.0.0.1 release?

Hope someone here would be able to tell me what steps do i need to make it work.


texts: change column type from integer to varchar

texts:
I have an integer column size in my table product in PostgreSQL 10.5 database. I want to change it's type from int to varchar(20) using alter table if possible.

The obvious way which worked for me with other types is to execute statement below.

alter table product alter column "size" SET DATA type varchar(20) using size::varchar;

Unfortunately, this way of conversion doesn't work and the error returned:

ERROR:  operator does not exist: character varying > integer
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883

I tried a bit of googling and searching through StackExchange network, but I haven't found a solution. With this error I'm not sure that creating a new column, transfering data into it will work because I still have to cast information from one column to another.


texts: copying data from a SQL server-table to a SQLCE-table

texts:
I want to copy some data from a MSSQL table to a SQLCE table in database.net.

I used the following query, connected to the MSSQL db:

update [SQLCE-table] ([column])
SET (column) FROM [MSSQL]

I keep getting several error messages like token in error = (].
What am I doing wrong?


texts: Fix OGR2OGR "failed when converting the varchar value to data type int" issue in the MSSQL table -

texts:
I am a newbie to MSSQL and am trying to convert an MSSQL table to Mapinfo Tab file using OGR2OGR but it keeps failing with the above message on a few tables.

I have tried to get a reply in the GIS SE about switches to apply in OGR2OGR but have not got any options. https://gis.stackexchange.com/questions/314669/ogr2ogr-conversion-failed-when-converting-the-varchar-value-to-data-type-int

How can I change this in the table?

What does NCPR mean -this text doesn't exist in the table -it it code for something?


texts: MySQL how to change a slave to a new master?

texts:
I have a MySQL active master (lets call it Alpha), a passive master (called Beta), and a slave (called Gamma).

Topology before change:

I am going to switch the active master and the passive master.

I will then need to also change the slave (Gamma) to the new active master (Beta).  I am following the instructions in the book "Effective MySQL: Replication Techniques in Depth", page 97, which states before slave (Gamma) is pointed to the new active master (Beta) I should run:

beta> STOP SLAVE SQL_THREAD;
beta> SHOW SLAVE STATUS\G
beta> SHOW MASTER STATUS\G
beta> START SLAVE SQL_THREAD;

It says use the log position from that SHOW SLAVE STATUS on Beta, and use it to run on Gamma:

gamma> START SLAVE UNTIL MASTER_LOG_FILE='{file}', MASTER_LOG_POS={position}

My question is why is that step necessary.  I am planning to stop all application writes to the current active master (Alpha), wait as long as necessary for Beta and Gamma replication to fully catch up.

Since they are both caught up with master (Alpha), is that command "START SLAVE UNTIL..." really needed?


texts: Database : what design to point on two possibilities?

texts:
I am designing a datamodel for a new project.

One of the requirements specifies that some objects can point either a person or a company.

What is the smartest way to achieve that?

I have thought about a table link "actor" like this (drawn with the excellent yUML.me BTW) :

In the actor table, according to actor_type, person_id or company_id is a foreign key on its corresponding table or is NULL. This way, when one_table wants to retrieve details about the actor, I start by checking the actor_type field and retrieve either person_id or company_id.

It is working, but I am looking for a better design.
Here is the link for editing the diagram


texts: Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl

texts:
What could be the best approach ?

Till now could figure out the following (Oracle)

Calling exp.exe and imp.exe utilities in Oracle client using Java and inturn call it from Perl.
Calling exp.exe and imp.exe utilities in Oracle client directly from Perl.
Explore more of DBI, DBD modules.
Search for some commands and execute them by connecting to respective databases

New to Perl and database(DB2, SQL Server).


texts:
I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)


texts: What options exist for copying data between servers on a regular basis?

texts:
My apologies if this has already been answered.  I've searched SO and of course here on DBA, and am surprised to find no close matches.  Specifically, I'm looking for a solution which survives schema differences.

I finally got a development server with which to shadow a production data warehouse server.  I'll be testing schema and sproc changes here, eventually publishing such changes to the prod server.  To do so, I'll need reasonably current data, but it need not be updated in real time.  My plan is to copy end-state data from the main server to the dev server as the final step in overnight ETL.  What's the best way to achieve this?

Parameters

All automatic copying will be from prod to dev.  Any copying from dev to prod will be by hand, and generally will just be DDL.

Because the dev server's schema is expected to change independently of the production server, I don't want the copy process to fail when the target schema differs (it's OK if the specific tables in flux can't be kept in synch, of course).  Otherwise I would just DROP DATABASE and restore last night's backup.

Keeping the schema, SPs, views, and UDFs in synch is not necessary, or even desirable.  They should only change when I specifically change them.

Records can be changed retroactively in most tables, so incremental updates will probably not be practical.

The volume of data is 14 GB for the most critical tables, plus about 200 GB of less important data which can be updated weekly.

My goal is to finish the process within two hours.  The servers are co-located, and should have high throughput.  Copying (INSERT INTO..SELECT * FROM ProdServer..) a single table with 600 MB of data and 300 of indices took 7.5 minutes; not great.  Of some concern, another table with 11 GB of data and 8 GB of indices did not finish in 130 minutes, when I cancelled it.  I'll test this again without indices.

Taking the prod database offline is acceptable if it can be kept brief - no more than 30 minutes.  The dev database can be offline for hours if necessary.

If I can temporarily exclude a specific table, keeping it static for a few days while testing, that would be valuable.

Options

1: Replication

I could create a daily snapshot after the end of ETL, and publish it to the dev server.  I've not used replication before, but this seems to be the kind of scenario for which it is meant.  Time for me to learn a new facet of the technology?

2: Bulk Copy and BULK INSERT

I could write a script which iterates through every table in the prod database and spits out the content to a uniquely-named file.  On the dev side, I'd loop through these files and TRUNCATE/BULK INSERT into the target table, with a TRY..CATCH block in case the schema has changed.  I'm not sure if this would perform acceptably, but it would be fairly simple to implement.

3: INSERT from Linked Server

I can run a TRUNCATE/INSERT..SELECT for each table, probably pulling from the dev side.  This would be simple, and should be fast, especially if one drops and re-created the indices.  To cope with schema changes, one could identify the intersection of the field lists for each pair of tables and only attempt to copy those fields; this would help if many fields were NULLable.

Are there other options?  Is there an easier way that I'm overlooking?  Are there any gotchas that you've hit when working on similar projects?

This question describes the export side, but the desired output was CSV, so the options are limited (and BCP works great, as answered).

This question talks about quickly copying a whole database, including schema.  It specifically excludes replication.

This question discusses bi-directional synchronization, for which replication is suggested.


texts:
Is there a way to convince PostgreSQL to execute long queries on a hot standy?

It kills the query with User query might have needed to see row versions that must be removed. I understand that, but I am doing a query on a table that I know never gets any row deleted... Is there a way to force porstgres to do it?


texts: Difference between setting of a shared lock when using INSERT or UPDATE/DELETE statements

texts:
I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement.
Specifications:

version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
autocommit: ON

Here's my setup:
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number_a` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_a` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_a` (`id_a`),
  CONSTRAINT `b_ibfk_2` FOREIGN KEY (`id_a`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `a` (`id`, `number_a`) VALUES
(1, 300);

Tests
I first run a certain (INSERT/UPDATE/DELETE) statement on Session 1 and then sleep, and then quickly run Session 2.
Test 1
Session 1: use INSERT statement (REPLACE INTO has the same result)
START TRANSACTION;
INSERT INTO b (id_a) VALUES (1); -- holds an S lock on table a, row 1
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1; -- tries to upgrade it to an X lock, deadlock
COMMIT;

Session 2:
START TRANSACTION;
UPDATE a SET number_a = 200 WHERE id = 1; -- tries to acquire an X lock, deadlock
COMMIT;

Deadlock.
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-12 14:51:06 7f68be5c7700
*** (1) TRANSACTION:
TRANSACTION 9979531, ACTIVE 8 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 779, OS thread handle 0x7f68bf476700, query id 15307 localhost bb updating
UPDATE a SET number_a = 200 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 1 total table locks 3  trx id 9979531 lock_mode X locks rec but not gap waiting lock hold time 8 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 9979530, ACTIVE 10 sec starting index read
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 777, OS thread handle 0x7f68be5c7700, query id 15312 localhost bb updating
UPDATE a SET number_a = 300 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 3 total table locks 3  trx id 9979530 lock mode S locks rec but not gap lock hold time 10 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 686427 page no 3 n bits 72 index `PRIMARY` of table `test`.`a` trx table locks 3 total table locks 3  trx id 9979530 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (1)

Test 2
Session 1: use DELETE statement
START TRANSACTION;
DELETE FROM b WHERE id_a=1;
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1;
COMMIT;

Session 2:
START TRANSACTION;
UPDATE a SET number_a = 200 WHERE id = 1;
COMMIT;

No deadlock.
Test 3
Session 1: use UPDATE statement
START TRANSACTION;
UPDATE b set id_a=2 WHERE id_a=1;
SELECT SLEEP(10);
UPDATE a SET number_a = 300 WHERE id = 1;
COMMIT;

Session 2:
START TRANSACTION;
UPDATE a SET number_a = 200 WHERE id = 1;
COMMIT;

No deadlock.
Why does an INSERT/REPLACE statement set an S lock and an UPDATE/DELETE statement doesn't?


texts: Understanding linking tables in a many-to-many relationship

texts:
I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may be in many photographs, which makes sense.
My issue is in understanding the properties of the linking table between the photo and person tables. I have set up a linking table with the PK of both tables (Photo and Person) as the composite PK for the linking table. Both sets of PK in this table are set to Not Null (by me) but the project then stipulates that people can be in the database even if they aren't in a photo, and I was wondering how this tallies with the NN setting of the composite PK. Surely if it has to be NN, this means it can't allow for there to be any entries where I just have the PK of the photo and none for the person part of the linking table? Or am I totally misunderstanding how these relationships work? As mentioned previously, I am in the early stages of learning about databases.