Performance tuning OLTP query I have the following SQLs executed millions of times a day, a single execution isn't an issue but executing million times is causing some performance issues..Thanks in advance for your help!

NOTE: data can change in any of the tables any time during the day..can't reduce number of executions and this is Oracle 11g env.

SELECT COUNT (*)
    INTO v_cnt
  FROM t1 cpr, t2 brl
 WHERE cpr.cp_id = :1
     AND cpr.brl_id = brl.brl_id
     AND brl.ranking > 0
     AND cpr.security_enabled_ind='Y'
     AND cpr.brl_id NOT IN (
                   SELECT rpr.brl_id
                     FROM t3 rpr, t4 ppr, t5 plcy
                    WHERE rpr.ppr_id = ppr.ppr_id
                      AND ppr.plcy_id = plcy.plcy_id
                      AND plcy.db_object_name = :2);
IF v_cnt = 0
THEN

SELECT where_clause
  FROM
    (SELECT where_clause
       FROM t4 ppr_o
     WHERE ppr_o.ppr_id IN (
                    SELECT ppr.ppr_id
                      FROM t1 cpr,
                           t3 rpr,
                           t4 ppr,
                           t5 plcy
                     WHERE cpr.cp_id = :1
                        AND cpr.brl_id = rpr.brl_id
                        AND rpr.ppr_id = ppr.ppr_id
                        AND ppr.plcy_id = plcy.plcy_id
                        AND cpr.security_enabled_ind='Y'
                        AND plcy.db_object_name = :2
                        AND plcy.statement_type = :3)
    ORDER BY NVL (ppr_o.ranking, 0) DESC)
WHERE ROWNUM = 1;
END IF;

Number of records

t1 -> 205101
t2 -> 42
t3 -> 142
t4 -> 57
t5 -> 13

I would first consider trying to combine the two queries into one. If the second query could be refactored to return null in the case where the first query returned zero would you be able to work with that?

For example,

testbed:

create table t1( cp_id integer,
                 brl_id integer,
                 security_enabled_ind char(1) );
create table t2( brl_id integer,
                 ranking number );
create table t3( ppr_id integer,
                 brl_id integer );
create table t4( ppr_id integer,
                 plcy_id integer,
                 where_clause varchar(1000),
                 ranking number );
create table t5( plcy_id integer,
                 db_object_name varchar(100),
                 statement_type integer );

refactored query:

with w as ( select rpr.brl_id, ppr_id, plcy.statement_type
            from t3 rpr join t4 ppr using(ppr_id) join t5 plcy using(plcy_id)
            where plcy.db_object_name=:2 )
select where_clause
from( select where_clause
      from t4 ppr_o
      where not exists ( select *
                         from t1 cpr join t2 brl using(brl_id)
                         where cpr.cp_id = 1 and brl.ranking > 0
                               and cpr.security_enabled_ind='Y'
                               and brl_id not in (select brl_id from w) )
            and ppr_o.ppr_id in ( select ppr_id
                                  from t1 cpr join w using(brl_id)
                                  where cpr.cp_id=:1
                                        and cpr.security_enabled_ind='Y'
                                        and w.statement_type=:3 )
    order by ppr_o.ranking desc nulls last )
where rownum=1;


Index Maintenance for DW- DB ( ETL process ) I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business grows.

The question is, which considerations should be taken in order to have an efficient Index Maintenance for an DW DB that usually loads data from an ETL process.

Currently I have in mind to implement the MP used by Ola Hallengren

T-SQL Code Below:

EXECUTE dbo.IndexOptimize
@Databases = 'StackOverflow2010',
@FragmentationLow = NULL,
@FragmentationMedium  = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 70,
@UpdateStatistics = 'ALL'

If you have any additional comments or suggestions will be very appreciated

If you are worried about index fragmentation (which really only matters if you are doing range scans off disk) but you are loading your data periodically during an ETL process I'm assuming you have your indexes enabled (and updated) during the ETL process.

When you load your data your indexes have to be updated all the time while inserting data but end up being fragmented which is why you want to rebuild them again.

Maintaining the index when loading data only to rebuild them later is useless work for SQL Server.

One approach I would suggest considering is the following

Disable all your non-clustered indexes on a table before you load data into it
Load your data in the order of the clustered key if you have one (most datawarehouses use a surrogate key anyway)
Enable your indexes again after you loaded your data. That way the index is only built once after the data load and will be contiguous.

The code to disable and enable/rebuild all indexes for a table would be the following

ALTER INDEX ALL ON dbo.[Table Name] DISABLE

-- perform data load

ALTER INDEX ALL ON dbo.[Table Name] REBUILD


what files should I exclude/add/copy mysql dbs (without mysqldump) I need to have an exact copy of my databases in a clone VM of my server (just in case the server goes down, have the VM ready with the dbs). I know that mysqldump is the best alternative to migrate, but I want to have another alternative. So I decided to do it with ROBOCOPY, and copy data folder. This is my batch

@echo off
set exclude=*.pid *.err *.cnf *logfile* *log_file*
set ORIGIN="mysql_data_path\data"
set DEST="mysql_data_path_mirror\data"

NET STOP mysqld
robocopy "%ORIGIN%" "%DEST%" /E /COPYALL /PURGE /MIR /IS /IT /TEE /FFT /ETA /R:10 /W:5 /ZB /V /XF %exclude%

The problem is that I am not sure which files I should exclude/add/copy, that does not affect the operation when I do the reverse process.

For example, I have put some exclusions (*.pid *.err *.cnf *logfile* *log_file*), but I do not know if they are correct. I would like help in this point. And, apart from data folder, is there anything else that I should copy in order to function in the VM?

Thanks

You cannot exclude the ib_logfile* files, if those aren't present, your data will be at best damaged, unless you are running with innodb_fast_shutdown=0 - which can make stopping the service take a very long time.

The only things that are safe to omit are the relay logs and binary logs. You will have to check what the names of those are set to in your config file.


SqlPackage resolving table that doesn't exist I'm trying to validate the integrity of one of our SQL Server 2016 databases by using sqlpackage.exe to extract the dacpac and validate the schema:
 ./sqlpackage.exe  /DiagnosticsFile:"diagnostics_users.txt`"   /Action:Extract /TargetFile: "users.dacpac"   /SourceConnectionString:"connstring"  /p:VerifyExtraction=true"

When I run it, I get the output
Resolving references in schema model
Successfully extracted database and saved it to file

However, when I check the source database, there is a stored procedure which references a table in a database that does not exist. If I try to run it in SSMS, I get the error: Invalid object name '[Old_Database].dbo.VIEWER_LOG'.  However, SqlPackage did not flag this as an error
When I search in the diagnostic logs, I do see the following:
Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 1 : ColumnResolver: #34027998 ResolvedDescriptor for column:
ResolvedDescriptor: [DATE_TIME_STAMP]
Potentials(1):
    [SqlColumn : P1 - [OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP]]

Available Column Sources:
ColumnSource VIEWER_LOG (affinity = 1) with no known columns

There is a table on the target database called Viewer_Log, but, not with the schema OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP, since OLD_DATABASE.DBO does not exist.  Shouldn't SqlPackage be flagging this as an error when it resolves references? Is there a configuration available to detect and flag this?

I found the answer in this post https://www.sqlservercentral.com/forums/topic/create-or-alter-procedure-not-detecting-invalid-object-names-1 Basically, stored procedures use 'late binding' which allows for non-existent entities to be allowed during compile time, and resolved at run time.  This link also provides a helpful SQL Script to detect those issues.
I have additionally created a post-deployment script that can be used in SSDT to verify after a deployment that a given database has no unresolved references https://gist.github.com/DarylSmith/ff69ee75dc1bb61af44dc9b9d9e00d7a


Oracle 12c - Can not login to Enterprise Manager - XDB Login prompt I have installed Oracle 12c Standard Edition on one of my Windows 7 LAN desktops. Installation went fine. I created ORCL Container and ORCLPDB pluggable database during install. I can log in to Oracle SQLPLUS using SYS & SYSTEM passwords.

I can as well login to Oracle Web Enterprise Manager (It runs on https://(hostname):5500/em ) using SYS & SYSTEM passwords without specifying any database. However in case of Web Enterprise Manager the moment I choose a database container (See the screenshot, left side login prompt) despite entering correct userid/password it prompts for yet another weblogin prompt. I am clueless what to enter here.

I tried SYS & SYSTEM passwords but it did not help. During installation, it asked for an additional admin password that too does not work here. I also tried unlocking XDB & Anonymous accounts and restarted Database & listener but it did not help. tnsnames.ora has entries for both ORCL & ORCLPDB.

Does anyone have any idea what this login is for and how to proceed further?

Thanks.

You should enable global port for EM Express to Manage a CDB and the PDBs, execute:

exec dbms_xdb_config.SetGlobalPortEnabled(TRUE)


ODBC Driver for Salesforce: Access Linked Server Object in SSMS I have problems to access a linked server for a Salesforce ODBC data source with our MS SQL Server 2016 (Standard 64-Bit) using the Devart ODBC Driver for Salesforce in SSMS and SQL Studio Management Studio (17.8.1).

I have created a linked server in SSMS as described in the documentation.

Testing the connection to the ODBC source only works in SSMS when I use the 64-bit source. When I use the 32-bit ODBC source no connection can be established and the following error is shown:

  The linked server has been created but failed a connection test.
  Cannot initialize the data source object of OLE DB provider "MSDASQL"
  for linked server "SF". OLE DB provider "MSDASQL" for linked server
  "SF" returned message "[Microsoft][ODBC Driver Manager] The specified
  DSN contains an architecture mismatch between the Driver and
  Application". (Microsoft SQL Server, Error: 7303)

Whilst I can connect to the 64-bit source I cannot access the linked server object in SQL Server Management Studio 17.x (32 bit - I believe there is no 64-bit version of SSMS):

When I try to expand the Server Object tree view I get the following error:

Select all Error: "Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server

Or when I try to query something, e.g. SELECT TOP 1 * from SFDC...[Contact]

Cannot create an instance of OLE DB provider "MSDASQL" for linked server "SF". (Microsoft SQL Server, Error: 7302)

I have tried a few solutions that are associated with error 7302/3 but nothing seems to work:

MSDASQL provider: disable Allow inprocess [this was required]
Restart (the database engine and even Windows)
SQL Server create procedure sp_tables_info_rowset_64 [does not fix
the issue]

I found out I can indeed connect to the Salesforce ODBC source and access the data using a 3rd Party Database Manager (Database5Pro 64bit, an SSMS alternative) - but this does not allow me to access both data sources at the same time (no linked objects available).

The Devart driver requires (as mentioned in the installation guide, see page 41) the same bitness of all moving parts.

  The driver, studio, and SQL Server must be of the same bitness.

Can anyone suggest a way to use a Salesforce source as a linked server along with SQL Server 2016 in an SSMS-like way? I am open to alternative solutions and 3rd-party tools.

I have also tried an older version of SSMS (2012) and the beta version of SQL Operations Studio (basically VS Code like SSMS, actually a 64-bit application) to no avail.

I'm going to take a chance and suggest an answer but in this case without the specific product and setup, of course I'm unable to verify.  I believe what you will need to do is go into the Security settings of the Linked Server and specify a particular account to use to make the connection.  That is, create an account on the server you want to connect to, like SalesForceReader, with the corresponding privileges needed, and use that account and password in your Linked Server.


Prometheus High Memory and CPU Usage in PMM We are running PMM v1.17.0 and prometheus is causing huge cpu and mem usage (200% CPU and 100% RAM), and pmm went down because of this. We are running PMM on a VM with 2vCPUs and 7.5G RAM, and are monitoring about 25 servers. PMM is running with below command >>

docker run -d -it --volumes-from pmm-data --name pmm-server  -e QUERIES_RETENTION=1095   -p 80:80   -e METRICS_RESOLUTION=3s  --restart always percona/pmm-server:1

The prometheus.log is filled with below entries:

level=warn ts=2020-01-30T10:27:12.8156514Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:26.464361371Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:27.81316996Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:27.813257165Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:41.462420708Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:42.813356387Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:42.813441108Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:56.463798729Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:57.82083775Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:57.820912309Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"

Can someone please let me know why prometheus is causing issue? Any parameters we need to add/change?

100% RAM -- You are probably swapping, which is terrible for performance.  Lower innodb_buffer_pool_size a little to avoid swapping.

200% CPU -- Poor indexes and/or poor formulation of queries.  Please provide some of the queries and SHOW CREATE TABLE; there may be a quick fix.

"out of order" and "different value" -- Either a bug with the collection mechanism or a bug in Percona.


DB constraints in face of quickly changing requirements in an agile environment I am in a team where requirements are changing often.
I see keeping constraints in application layers makes our life easy.
If I am going to follow the best practice of specifying most constraints into DB, I see hard times for my team members. When a requirement change needs a DB constraint change, we have to migrate the whole data. And migration seems to be a big headache.
What should we do? What is the best practice keeping in view that requirement changes very often?

We want to keep DB schema flexible and yet have data consistency.

What constraints can we still impose on DB while keeping things flexible enough?

As a data professional, I'm going to tell you straight up to leave the constraints in the database.

However, there's no right or wrong answer, because it depends on what risk you're prepared to deal with.

If you don't want relational integrity, use XML or JSON or some other thing that isn't an RDBMS. But if you want to use SQL Server or Oracle or PostgreSQL, go right ahead and manage it in the app.

Somewhere along the line, though, there will be data corruption and orphaned records. That risk is real, but I can't tell you how big of a risk it is.

If the effort of fixing corruption like that is less than the effort of maintaining constraints in the database, that's a manageable risk.

On the other hand, if you can't bear the thought of fixing corruption or orphaned data, then leave the constraints in the database and spend some money on the right tools to help you with database changes and migration.

P.S. "Agile" is not an excuse for bad design or lack of documentation.


Row locking or avoiding duplicate insertion on primarykey/otherfield combo I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system.

For users there may be one or more entries depending on how many files have been uploaded.

For depots, deliveries the same applies, a stripped down version of the table looks like this:

CREATE TABLE IF NOT EXISTS `files` (
  `file_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '...',
  `file_company_id` int(11) NOT NULL DEFAULT '0' COMMENT '...',
  `file_relation` varchar(100) NOT NULL COMMENT '...',
  `file_relation_id` int(11) NOT NULL COMMENT '...',
  `file_name` varchar(200) NOT NULL COMMENT '...',
  `file_mime_type` varchar(150) NOT NULL COMMENT '...',
  PRIMARY KEY (`file_id`),
  KEY `file_relation` (`file_relation`),
  KEY `file_relation_id (`file_relation_id`),
  KEY `file_name` (`file_name`),
  KEY `file_mime_type` (`file_mime_type`),
  KEY `file_company_id` (`file_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

To get an individual entry is simple enough to query the file_id, to get a list of all entries for another entry I do something like the following:

User 123
SELECT .... FROM files WHERE file_relation='user' AND file_relation_id=123

When users upload images they are displayed in the UI either in their original form, or a variety of sizes which are registered in the back end:

<img src="path/to/image/1/200" style="width:200px;" />

The above snippet requests image 1 with a width of 200px ... if the size does not exist yet it is created on the fly (if it is a registered size).

The problem I now have is ensuring that only one entry for the new size is created when first inserting.

#when creating new sizes they reference the original file
file_id:99, file_relation:'files',file_relation_id:1,file_dimension:200

#for another file - this is valid
file_id:100, file_relation:'files',file_relation_id:5,file_dimension:200

#this entry should not have been created because a previous one with the dimension exists
file_id:101, file_relation:'files',file_relation_id:1,file_dimension:200

There can be duplicate file_dimension entries for different parent relations, but if another thread is inserting at the same time it should be able to pick up that an entry already exists and not create a new one.

I am not sure how row locking would work for this scenario (when INSERT is used) or if I even need to worry about it, here is what happens currently:

SELECT * FROM `files` WHERE `file_relation`='files AND `file_relation_id`=1 AND `file_dimension`=200
#if no entry is found
INSERT INTO FILES (...,`file_dimension`) VALUES (...,200)

I am quite certain that there are other places in my application where this type of thing also applies, any advice appreciated.

The OP answered their own question in the comments (now deleted):

  Never mind I figured it out, using a unique index combo, ALTER TABLE files ADD UNIQUE INDEX (file_relation, file_relation_id, file_dimension) does the trick.


not-yet-assigned record is indeterminate, but triggers on update (postgresql 9.3) I create a very simple function, ensuring it only happens with an update.

CREATE OR REPLACE FUNCTION simple_trigger() RETURNS trigger AS $$
DECLARE
        _unused_variable INTEGER; -- unrelated placeholder.
BEGIN
        IF (TG_OP = 'UPDATE') THEN
                INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value);
        END IF;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

I register the trigger

DROP TRIGGER simple_trigger_trigger on trigger_test; -- just in case.
CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test FOR EACH statement execute procedure simple_trigger();

I have simple table being inserted into on update.

mydatabase=# \d trigger_log
  Table "public.trigger_log"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 value  | integer |

Nothing in that table at first.

mydatabase=# select * from trigger_log;
 id | value
----+-------
(0 rows)

Three rows in trigger_test (the table being updated).

mydatabase=# select * from trigger_test;
 id | value
----+-------
  1 |     2
  2 |     2
  3 |     2
(3 rows)

I can't update and have the insert happen.

mydatabase=# update trigger_test set value = 100 where id = 2;
ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  SQL statement "INSERT INTO trigger_log(id, value) values (OLD.id, OLD.value)"
PL/pgSQL function simple_trigger() line 6 at SQL statement

My understanding per postgres docs was that NEW is unassigned on DELETE operations and OLD is unassigned on INSERT operations. That makes sense. This is neither.

How can it possible be unassigned?

This is very frustrating. I can't think of a more simple example.

My mistake. Trigger was for each statement; should have been for each row.

CREATE TRIGGER simple_trigger_trigger AFTER UPDATE ON trigger_test
    FOR EACH ROW EXECUTE PROCEDURE simple_trigger();


Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log' Query:

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO

OUTPUT:

  (7 rows affected) DBCC execution completed. If DBCC printed error
  messages, contact your system administrator. Processed 440 pages for
  database 'Tal', file 'Tal' on file 1. Processed 2 pages for database
  'Tal', file 'Tal_log' on file 1. BACKUP DATABASE successfully
  processed 442 pages in 0.434 seconds (7.939 MB/sec). Msg 8985, Level
  16, State 1, Line 16 Could not locate file 'MyTal_Log' for database
  'master' in sys.database_files. The file either does not exist, or was
  dropped.

I would start by saying that shrinking, be it that of data or log file, is really bad avoid it as much as possible. There is hardly anything you can benefit, specially with what you are doing.

As for your question look at the below specific error message

  Msg 8985, Level 16, State 1, Line 16 Could not locate file 'MyTal_Log' for database 'master' in sys.database_files

The query is looking for logical file named MyTal_log in master, is this the logical filename of log file of database tal ?, I believe it is not. To  confirm run below

sp_helpdb Tal

As shown in pic, select the logical name and use that name in the query, this is Tal database created on my system.

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
USE tal--**database name**
GO
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);--add correct logical name
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO


How to convert a MySQL database from utf8mb4 to utf8? I would like to convert a MySQL database, which has utf8mb4 character set and utf8mb4_unicode_cicollation, to utf8 and utf8_unicode_ci.

How to do that please?

Thank you in advance for your suggestions.

I just solved the charset problem by using Notepad++.

The problem was that my php version is 4.5.5.1 and the hosting firm's was 3.3.8. Because of that, it could not work with the utf8mb4_unicode_520_ci.

To solve the problem open the exported SQL file, search and replace the utf8mb4 with utf8, after that search and replace the utf8mb4_unicode_520_ci with utf8_general_ci. Save the file and import it into your database. After that, change the wp-config.php charset option to utf8, and the magic starts.

Note that it worked in a Hungarian database.


Performance MySQL query with for loop In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make it faster.

//Get the first day and last day
$dateInitial = strtotime('2018-08-21');
$dateFinal   = strtotime('2019-08-21');
$final       = array();

$sql = "SELECT id_product, product, plant_sowing, plant_production, area_planting, CONCAT(id_product,'_', weeks) AS identity
              FROM (
                    SELECT sw_sowing.id_product, pr_products.product, sw_sowing.type, YEARWEEK(:dates,3) AS weeks, SUM(sw_sowing.quantity) AS plant_sowing,
                           SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6-WEEKDAY(:dates)), :dates), sw_sowing.date)/7)>=sw_sowing.weeks_prod, sw_sowing.quantity,0)) AS plant_production,
                           ((SUM(sw_sowing.quantity))/pr_products.plant_m2) AS area_planting
                    FROM (
                          SELECT MAX(id) AS id
                          FROM sw_sowing
                          WHERE status != 0
                          AND id_tenant = :id_tenant
                          AND date <= :dates
                          AND multiply != 1
                          AND id_product = 1
                          GROUP BY id_production_unit_detail
                    ) AS sw
                    INNER JOIN sw_sowing ON sw_sowing.id = sw.id
                    INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product
                    INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
                    WHERE pr_varieties.code != 1
                    GROUP BY sw_sowing.id_product, sw_sowing.type
                    HAVING type NOT IN('ER','PR')
              ) AS s";

$statement    = $this->db->prepare($sql);

//get the sunday dates between two dates and bind the variables
for ($i = $dateInitial; $i <= $dateFinal ; $i = strtotime('+1 day', $i)) {
        if (date('N', $i) == 7){
          $values = [
            ':dates'      => date("Y-m-d", $i),
            ':id_tenant'  => 1
          ];

          $types = [
            ':dates'      => Column::BIND_PARAM_STR,
            ':id_tenant'  => Column::BIND_PARAM_INT
          ];

          $result   = $this->db->executePrepared($statement, $values, $types);
          $final[]  = $result->fetchAll(Phalcon\Db::FETCH_ASSOC);
        }
      }
      return $final;

But despite this it is not so fast. The query lasts 10 seconds and I would like it to be faster.

I have also indexed the tables. I would like some opinion on how to best optimize this query or if the way I am doing the query is not adequate.

This is a question that I did before about why I use GROUP BY and MAX(id)

https://stackoverflow.com/questions/52209300/get-max-ids-by-group-mysql

SELECT MAX(id) AS id
                      FROM sw_sowing
                      WHERE status != 0
                      AND id_tenant = :id_tenant
                      AND date <= :dates
                      AND multiply != 1
                      AND id_product = 1

needs INDEX(id_tennant, id_product, dates, multiply, id)

GROUP BY id_production_unit_detail does not make sense since you are not doing SELECT id_production_unit_detail, MAX(ID) and then using id_production_unit_detail in some way.

Eh?  Doing this for each Sunday??  AND date <= :dates -- That means you are scanning more and more of the table.  And, if there are no entries in a week, returning the same data sometimes.  There are much better ways to get the data for a given week withouut scanning the rest of the data.  And it lends itself better to doing everything at once (instead of looping through Sundays).

I assume these are all the same type?

SELECT ..., sw_sowing.type, ...
    GROUP BY ..., sw_sowing.type
    HAVING type NOT IN('ER','PR')

If so, then it would be more efficient to move the test against type into WHERE instead of HAVING.

(There may be more to improve on.  Do these things, then come back for more abuse/advice.)


Delete _new table from pt-online-schema-change I used pt-online-schema-change but the process died in the middle.

The script makes a copy of the original table with the name followed by "_new".

Since the process died in the middle, the table "_new" wasn't deleted and when I try to delete it my application crashes.

It seems whenever I alter the original table this _new table is also changed so if it is not found it crashes.

I would like to know how can I remove this _new table.

Like many other solutions designed for an almost lockless schema change, pt-online-schema-change works by creating triggers on your table and creating a _new table. As stated in the documentation:

  Any modifications to data in the original tables during the copy will
  be reflected in the new table, because the tool creates triggers on
  the original table to update the corresponding rows in the new table

If this utility crashed, you might have to do the cleanup yourself. This might include:

Removing the triggers: have a look at the SHOW TRIGGERS  syntax to see the ones pt-online-schema-change has just created;
Delete the _new table.


Which design to choose Suppose I have a website that communicates with a database.

In some page of my site i want to exhibit a list with all the students in my database. There are two kinds of students: regular students, that only participate in regular classes (with attributes such as student_id, name, email, password) and special class students, students who participate not only in regular class but also in special extra classes (with all the attributes the regular students have plus an attribute for the special_class_id).

In the exhibition of the students on the mentioned page, I want it to exhibit the student differently depending on whether it's a special class student or not.

So i'm thinking here what would be the best database design in order for the code for the exhibition of the students to have the best efficiency/performance.

Option 1

Two tables on my database, one for all students (with student_id,name,email,password)  and one extra for special class students (with student_id and a special_class_id corresponding to the id of the special class the student participates). Let's name the table with all students by students and the other one by special_students.

With this option, for the exhibition page, the code would pick each row of the students table and display it. It could then check whether that student_id was inside any row of the special_students table, and if it was, it could exhibit that student differently.

Option 2

Another option would be having only one table on my database with an extra special_class_id column. Then, for regular students this column would have some default value (maybe 0), and for special class students, this column would have value identifying the special_class_id.
With this option for the exhibition page, it seems to me that the code would be faster since it would query only one table .. but on the other hand, adding this extra column seems a bit sloppy.

Would there be a significant performance advantage on the second option, over the first one, considering an hypothetical situation where the site might have 10 million visitors a day? Can this sloppy extra-column interfere in some way and cause problems?   Is there any other option for the database design?

Thanks a bunch in advance.

I have read your question, but not your proposed solutions, so I can't be influeced.

So, my ideas is that you can do a table STUDENTS and another table, let's say STUDENT_TYPE.

In STUDENTS, you put a column, ID_STUDENT_TYPE. In this column, you can set, with the ID of STUDENT_TYPE, the type of the single student.

In this way, if you have to insert a new student type, you won't have any problems! ;)
It is an elastic solution!


What is the best way to rebuild a date from integer inputs? I've got a bunch of financial reports, and we want to be able to pass them two inputs (year and quarter) as variables.

I'm doing it this way, but I really don't like it:

    declare @quarter int,
    @year int,
    @date date

    set @quarter = 4
    set @year = 2018

    set @date = cast(@year as varchar(4)) + '-01-01'
    set @date = dateadd(quarter, @quarter - 1, @date)

    print @date

Question What is the best way to reconstruct a date from integer inputs?

desired result:

    2018-10-01

How about

declare @quarter int = 4
declare @year int = 2018

select datefromparts(@year,(@quarter-1)*3+1,1)

or if you're still using SQL 2008:

select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))


SQL Hard Drive Config? I am setting up new server and am a little confused in my research of HD configuration.  My main question is :

Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?
Do Trans Logs and ldf logs write concurrently or one after the other?
My plan is to configure it as follows:

  C:\ Raid 1,

  Data (Raid 10),

  Logs (Trans & .ldf) Raid 10,

  TempDB (Raid 1),

  Local Backups (Raid 10).

Does that make sense or should I separate the Trans & .ldf files onto separate raid configs ?

Your questions are very broad as you are touching a lot of topics in sql server. I am just going to provide you relevant links so you can understand the subject in a better way and at your own pace.

  Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?

Seperate spindles is the best recommendation. Read on When should you put data and logs on the same drive?

  Do Trans Logs and ldf logs write concurrently or one after the other?

Transaction log is your ldf file. Writes to the transaction log are sequential in nature and benefit by being isolated on to separate physical devices.

Read up on : Diagnosing Transaction Log Performance Issues and Limits of the Log Manager and Optimizing Transaction Log Throughput

  Regardless of your decision, though, use Perfmon after the system goes live and track the drive activity.  If one of the two arrays is being overwhelmed with load while the other one sits idle, then it’s time to rethink the decision.

I would suggest :

RAID 1 for OS
RAID 10 for data, logs and tempdb

Note that there are many other factors that will directly or indirectly affect your choice as to whether your workload is OLTP or DSS ?

For optimized I/O parallelism, use 64 KB stripe size. Read on Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?
Make sure to properly size the tempdb files
Use DMVs and Perfmon to set a baseline and look out for spikes or drops in your baseline.
Make sure to Test Disk Performance
Make sure that once your backups are completed locally, you move it to a safe and secure location and test them for restores regularly. So in an event of a disaster, you can confidently recover your data.


Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server I can ping successfully from my Windows command line to localhost and to 127.0.0.1, but attempts to connect with mysql.exe fail...

I found similar errors but their suggested solutions don't work/apply...

https://stackoverflow.com/questions/19101243/error-1130-hy000-host-is-not-allowed-to-connect-to-this-mysql-server

Host is not allowed to connect to MySQL

Host not Allowed Connectivity to MySQL Server

I'm using a Windows 7 WAMP environment (an old Zend installation) meaning its all local (connections made from localhost/127.0.0.1). All worked fine last Friday when I did an import of a database, today, I get the cannot connect error. I did not overwrite mysql database (or at least, i am pretty sure I did not), so the error has taken me a little by surprise.

C:\Backups\Daily\201607>ping /n 1 localhost

Pinging Silver [::1] with 32 bytes of data:
Reply from ::1: time<1ms

Ping statistics for ::1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>ping /n 1 127.0.0.1

Pinging 127.0.0.1 with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

Ping statistics for 127.0.0.1:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

C:\Backups\Daily\201607>mysql --user=root
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

C:\Backups\Daily\201607>mysql --user=root --host=localhost
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

My partial extract from my.ini file:

[client]
port=3306
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files (x86)/Zend/MySQL55/"
#Path to the database root
datadir="C:/Program Files (x86)/Zend/MySQL55/Data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server = utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I'm curious on what went wrong - I suspect I'm responsible for it but lack a history file to examine the error of my ways.

Can anyone advise how I can fix and reconnect?

Short answer: I recovered from backup

During my work day, I do perform several mysqldumps of my work database and mysql. But I could not perform an import since it would fail with the same Error 1130.

I stopped the db, I moved my data/mysql directory out of the way, and then dropped in my March 1st data/mysql. I restarted the database and found I could connect and perform selects from within MySQL Workbench and the DOS command line.

I performed a reboot, restarted the database, and all good.

My backup batch file (copied below) performs a dump of my work database (wipdb) to wipdb.sql and mysql database to mysql.sql

Note the WinRAR command contains the word "PASSWORD" which is the plaintext password used to encrypt my backups. If you use the script, you might want to change it to something else...

The WinRAR files are saved to C:\Backups\Daily\BackupYYYYMMDD-XX.rar where YYYYMMDD is the date and XX is numeric incremented during ever iteration of the script during the day

PATH=%PATH%;C:\Progra~1\WinRar

call mysqldump.exe wipdb --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\wipdb.sql

call mysqldump.exe mysql --single-transaction --default-character-set=utf8 --max_allowed_packet=1G --user=root > C:\Backups\Daily\mysql.sql

cd C:\backups

rar.exe a C:\Backups\Daily\Backup.rar -pPASSWORD -ai -mt2 -m4 -t -agYYYYMMDD-NN -dh -ed -idcdp -r C:\Tools\ C:\Data\websites\  C:\Progra~2\Zend\*.conf C:\Progra~2\Zend\MySQL55\data\mysql C:\Progra~2\Zend\MySQL55\data\wipdb C:\Progra~2\Zend\*.ini C:\Windows\System32\drivers\etc\* C:\Backups\Keep\* C:\Backup\Daily\*.sql


How to delete archive log on just one dest? I have two archive_log_dest, I need to delete archivelog on dest1 until today. I need to keep on archive_log_dest2 all archive. How can I do that with rman?

rman target /
RMAN> delete archivelog like '/path/to/logs/%';

archivelogRecordSpecifier


SQL Azure: More Intermittent Timeouts (also posted on MSDN forums http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/79058c06-d3c5-4073-8571-c29d1f43f7ae)

Hi guys,

We have a set of 5 online auction systems running on Windows Azure & SQL Azure. Each system consists of a single web worker and one or more web roles. Each system is using ASP.NET MVC 3 and Entity Framework, Repository Pattern and StructureMap.

The worker role is responsible for housekeeping and runs two groups of processes. One group is run every ten seconds, the other every second. Each process will likely run a database query or stored procedure. These are scheduled with Quartz.net

The web role serves the public interface and back office. Among other basic crud functionality, both of these provide screens which, when open, will repeatedly call controller methods which will result in execution of stored procedure read-only queries. The frequency of repetition is about 2-3 seconds per client. A typical use case would be 5 back office windows open, and 25 end user windows open – all hitting the system repeatedly.

For a long time we have been experiencing intermittent SQL timeout errors. Three of the most common ones are:

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The only predictable scenario is during an auction where a specific controller -> sproc starts to timeout during the event (presumably due to load). All other times the errors appear to be completely random and come in singles, two’s, and three’s etc. even during periods of user inactivity. For example the system will go 18 hours without an error and then could be 5 – 10 errors from different housekeeping methods, or perhaps a user logged on and viewed their account.

Other info:

I have tried to run the affected queries/sprocs on SQL Azure using both local SSMS and Azure web-based query tool – all seem to execute quickly, 1 second max. Query plans not showing anything too suspicious although I am by no means a SQL query performance expert, or any other kind of expert for that matter J

We have wrapped all affected areas in Azure SQL Transient Fault Handling Blocks – but as is discussed here http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/7a50985d-92c2-472f-9464-a6591efec4b3, they do not catch timeouts, and according to “Valery M” this is for good reason.

We are not storing any session information in the database, although asp.net membership information is stored in the database.

We use 1 “SQL Azure server instance” which hosts all 5 databases, two for staging and three for production. All 5 systems are generally active at the same time although it is unlikely that more than one will be in a state of live load use at any given time.
All web roles, worker roles and the SQL Azure server reside in the same Azure Geographical Region.

Any thoughts on where we should be looking? Would it help giving each system it's own SQL Azure server? ... Failing a solution by ourselves - is it possible to get Microsoft to open a support ticket and take a look under the hood at what’s going on in with our application – how does one go about this?

Thanks in advance.

Ilan

These times where your connection is forced closed is usually an indication of throttling. I have found that the main reason for throttling is from high numbers of outstanding requests within your database. Unfortunately there are no hard limits that can be monitored to identify when this throttling occurs. The good news is that the lower limits have been documented which means you can track when you are getting close to the upper limits.
To give a plug to our Cotega monitoring service, this is one of the main things we track to allow people to be notified when they approach these upper limits. Another useful thing to do when this happens is to do a deep database diagnostics to see what expensive queries, connections, etc are occurring within your database as described on How to Perform SQL Azure Performance & Diagnostics Analysis for SQL Azure.


MySql 5.6 error (Table 'mysql.user' doesn't exist) when started as a daemon We can't seem to get the MySql daemon to start when using the included init script...
A clean install on linux (ubuntu 14.04) was done using the generic MySql binary tarball. The following post-install steps were successful: (NOTE: installation locations and owner account were customized)

$ mysql_install_db --user=mysql-owner --basedir=/mysql-path --datadir=/mysql.data-path
$ mysqld_safe --user=mysql-owner --basedir=/mysql-path --datadir=/mysql.data-path
$ mysql_secure_installation

After those steps were completed (the last resulting in a changed 'root' password and some additional security locks), the mysqld service was stopped.
Subsequently, the 'mysql.server' init script was modified accordingly:

basedir='/mysql-path'
datadir='/mysql.data-path'

Along with my.cnf:

user=the_mysql_owner
basedir='/mysql-path'
datadir='/mysql.data-path'

After manually executing the init script:

./mysql.server start

The service fails to start and produces the following output in the hostname.err file:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
mysqld_safe mysqld from pid file /mysql-path/run/mysql.pid ended

All of the references to this error seem to be based upon incorrect ownership/privileges and/or corruption due to an upgrade routine. Well, this is a clean install (on a new server), and the service seemingly runs fine when manually started from the command line, so I don't see it being an ownership issue. It "feels" like the init script is dropping a configuration setting, but which one...?

I've conquered such issue before, pay attention that there's an original data dir existed in x:/your_mysql_5.6_install_dir/data.

if you have defined datadir=xxx in my.ini, and the path is not the one x:/your_mysql_5.6_install_dir/data, please do the following things:

copy x:/your_mysql_5.6_install_dir/data to the destination folder.
start MySQL

e.g. if you have defined:
[mysqld]
basedir=C:/mysql-5.6.43-winx64
datadir=C:/ProgramData/MySQL/MySQL 5.6 server/data

you need to copy C:/mysql-5.6.43-winx64/data to C:/ProgramData/MySQL/MySQL 5.6 server/data

then start your MySQL.

this has been described in https://dev.mysql.com/doc/refman/5.6/en/windows-create-option-file.html:

  If you would like to use a data directory in a different location, you
  should copy the entire contents of the data directory to the new
  location.  For example, if you want to use E:\mydata as the data
  directory instead, you must do two things:

  1) Move the entire data
  directory and all of its contents from the default location (for
  example C:\Program Files\MySQL\MySQL Server 5.6\data) to E:\mydata.

  2) Use a --datadir option to specify the new data directory location
  each time you start the server.


Sort varchar by its numeric fields I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.
I have an orders table with a varchar field for the order number, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:
SELECT number FROM orders ORDER BY number LIMIT 10;
  number
----------
 1999-13
 2019-11
 2020-1
 2020-10
 2020-100
 2020-12
 2020-2
 2020-21
 2020-3
 2021-1

I need to sort that field by year and then by the progressive number, with this expected result:
  number
----------
 1999-13
 2019-11
 2020-1
 2020-2
 2020-3
 2020-10
 2020-12
 2020-21
 2020-100
 2021-1

A DB fiddle is here.
My questions are:

Which is the simplest way to achieve this with an ORDER subclause?
How to add an efficient index using this custom sort without having to modify the table?

I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.

I would convert that value to an integer array, then sort on that array:
SELECT number
FROM orders
ORDER BY string_to_array(number, '-')::int[]
LIMIT 10;

Online example


Changing Postgres 9.6 to behave more like 9.5 I don't hold myself out as being as a professional database administrator, however I recently upgraded a non-trivial size database (about 100 gigs on disk) from Postgres 9.5 to 9.6.

The database was handling OK under load in Postgres 9.5, however it becomes unusably slow in 9.6.   The only thing that changed was the version of Postgres (which was upgraded using the pg_upgrade tool) - same Postgresql.conf file.

We have run a VACUUM ANALYZE, and this does not seem to have made any difference.

When I analyze a typical query which was taking > 5 seconds under load, it executes in 50ms.  When the system is under load, uptime (on an 8 core cpu) reports values of > 30 and VMSTAT shows all available CPU is being used (with no swap and negligible disk IO)

On the advice of "experts" the Postgres configuration (in 9.5) was tuned with the following options - which are in the 9.6 config as well -

autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 100
autovacuum_max_workers = 10
autovacuum_naptime = 5min
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
vacuum_cost_limit = 800

bgwriter_delay = 100ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4

checkpoint_timeout = 15min
checkpoint_warning = 5min
cpu_tuple_cost = 0.03
effective_cache_size = 50GB
maintenance_work_mem = 1GB
shared_buffers = 8GB
wal_buffers = 64MB
work_mem = 60MB

Are there any settings I can change to make Postgres 9.6 behave more like 9.5, so that I can work out what is causing the issue?

Does anyone have an insight into what might be causing Postgres to eat up all the available CPU ?

How can I go about debugging/getting a grip on the problem in a production environment ?

The issue we were having turned out to be Transparent Huge pages.  I disabled this in the kernel and on Postgres, and things went back to how they were prior to the upgrade to Postgres 9.6


Transaction Log backup overwrites full backup on same media I have a database that is currently backed up every two hours using a SQL Agent job. This is a secondary backup, done in addition to the hosting provider's backup strategy. I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

If the backup file doesn't exist, create it and perform a full (copy only) backup
If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is below:

ALTER PROC [dbo].[e4_backupDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar(4));
    DECLARE @weeknumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar(2));
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupphysicalpath nvarchar(100) = N'C:\SqlScheduledBackups\';
    DECLARE @fullpath nvarchar(150) = @backupphysicalpath + @backupFileName;
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            PRINT 'Creating backup device and performing full backup...';

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, INIT, NAME=@backupname, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            PRINT 'Performing transaction log backup...';
            SET @backupname = @myDbName + '-Transaction Log';
            BACKUP LOG @mydbname TO @backupFileName WITH COPY_ONLY, COMPRESSION, INIT,  NAME=@backupname, STATS=10;
        END
GO

After a few hours, if I open the backup media in SSMS and view its contents, I would expect to see:

A full database backup for @myDbName
The most recently-taken transaction log backup

Instead, it appears that the transaction log backup is overwriting the full backup.  When I view the media contents, I only see the transaction log.  I know that INIT might trigger this behaviour, but MSDN implies that if the NAME doesn't match, then it shouldn't get overwritten.

Why is the full backup getting replaced then please?  I am trying to achieve a full backup once-per-week, then a transaction log backup every 15 minutes.

You are specifying INIT on both your FULL and transaction log backups.

If you want to 'append' the transaction log backups to the FULL backup, specify INIT on the FULL and NOINIT on the transaction log backups.

If you don't want your transaction log backups 'appended' to your FULL, I'd suggest generating a dynamic transaction log backup command 'appending' a timestamp onto the transaction log backup file name and using INIT.

Here is an 'example'

SET NOCOUNT ON
declare @BackupTimeStamp varchar(25)
declare @BackupCommand varchar(max)
declare @VerifyCommand varchar(max)
declare @ServerName varchar(max) = (select @@SERVERNAME)

set @BackupTimeStamp =
    right('00' + CONVERT(varchar(4),datepart(year,sysdatetime())),4) + '_' +
    right('00' + CONVERT(varchar(2),datepart(month,sysdatetime())),2) + '_' +
    right('00' + CONVERT(varchar(4),datepart(day,sysdatetime())),2) + '_' +
    right('00' + CONVERT(varchar(4),datepart(hour,sysdatetime())),2) +
    right('00' + CONVERT(varchar(4),datepart(minute,sysdatetime())),2) +
    right('00' + CONVERT(varchar(4),datepart(second,sysdatetime())),2)

set @BackupCommand =
'
BACKUP LOG [SCOPERATIONAL] TO
DISK = ''\\FBPISILON01\DBBackups-SC\' + @ServerName + '\Xlogs\SCOPERATIONAL_backup_' + @BackupTimeStamp + '.bax'' ' +
'WITH FORMAT, INIT,  NAME = ''SCOPERATIONAL_BACKUPS\SCOPERATIONAL_backup_' + @BackupTimeStamp + ''''
print @BackupCommand
exec (@BackupCommand)  --should probably be sp_executesql

Keep in mind, that EACH transaction log backup will continue to be larger UNTIL a non-COPY_ONLY backup is taken.

Additionally, should you decide to create transaction log backups that are not appended to your FULL backups, you'll need to create some sort of pruning/purging process to delete older transaction log backups.


What is the proper way of Adding a Field in a MySQL table with 1.6 million row I have a table with 1.6 million rows.

Then I add a field on that table.

I am using hostgator as hosting. You know, they have like 16 core CPU.

Result:
1. phpmyadmin doesn't work for 10 minutes
2. After database back online the field I want to add is NOT added.
3. Fortunately I do not get a call from hostgator complaining.

I don't care it's slow. I know it's slow. It's 1.6 million row. I just want to do this once.

Is this because I use innodb engine?

The command is simple:

Alter table tablebusiness add column `Prominent` int(10) NOT NULL

I got this error:

#2013 - Lost connection to MySQL server during query

I do not need this operation to be run fast

I need it to run once.

I suppose I can copy the table somewhere else and then switch the old table into new table but innodb has cascade and I do not know how to do so.

Other update: Hostgator is pissed. Can't blame them. I must have used huge CPU power.

Looks to me there is a proper way to modify a table. Maybe shutting down the database first in maintenance mode or something. Maybe creating a copy of table first.

ALTER on such a huge table can be slow irrespective of Engine_type

Follow this method to avoid downtime and quicker operation:

Setup slave
Stop replication.
Make ALTER on slave
Let slave catch up the master
swap master and slave, so slave becomes production server with changed structure and minimum downtime

You can also refer this post from MySQL Performance blog:

http://www.mysqlperformanceblog.com/2008/03/27/using-mmm-to-alter-huge-tables/


Determine which trace flags are set as a startup parameter vs. enabled globally? Probably a quick question.

We are looking to test out a couple trace flags as startup parameters in our environment, and while I know we can use DBCC TRACESTATUS to get global-scoped trace flags, it doesn't specify whether this was set at startup or merely set with the -1 flag.  Is parsing the SQL error log the only method available?  Possibly a registry entry somewhere?

Any way this can be tackled programmatically would be useful.

Thanks for reading!

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup.

select * from sys.dm_server_registry
where
cast(value_data  as varchar(max)) like '%-T%'

Just incase if you or future visitors need the output in one row then, you can use below T-SQL :

select distinct @@SERVERNAME as SERVER_NAME
                ,replace(STUFF((
                        select ' ' + cast(value_data as varchar(max))
                        from sys.dm_server_registry
                        where cast(value_data as varchar(max)) like '%-T%'
                        for xml PATH('') -- select it as XML
                    ), 1, 1, ' ')        -- This will remove the first character ";" from the result
        , '&#x00', '')                   -- This will remove the "&#x00" else you will get  -traceFlag&#x00
    as TRACE_FLAGS_ENABLED_AT_STARTUP
from sys.dm_server_registry
where cast(value_data as varchar(max)) like '%-T%'


recovery with binlogs? I would like to know if there is a way that I can restore my databases, I don't have a replication and I would like to migrate my databases without downtime or losing data.
So with binlogs could I do that? I have in my new server already a database but is kinda old, could this be possible.
Thanks in advance

if you want to finish your database migration online， replication is necessary.

The common practice for database migration is building replication between your old mysql and new mysql(as slave). When the slave catch the master, switch your business to the mysql(slave).

if you just want to use binlog , you need to lock table during your migrating the database, or you will loss some data.


How to find the system impact of a query in postgres When an query is executed in postgres - pgadmin4 .
Can i know the query to check the performance of the query using any system tables ?

example :

Time taken
Query run by
3.CPU impact
any blocks
etc..

Add pg_stat_statements in shared preload libraries
Create extension pg_stat_statements.
Then query on pg_stat_statements to get time related stats of the query.

Query run by can be tracked from pg_stat_activity for the currently running statements.

For blocks you can query  as below
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));


Oracle 11.2 restore DB to a specific point I should execute following tasks:
1. test system and during test change Oracle data and Create New/Remove Old datafiles and tablespace.
2. at the end of test rollback to behviour before of point 1.

I'm working on Oracle 11.2 on two different sites: on single instance configuration and another in DataGuard environment.

To reach the scope I think following possibilities:
1) a- execute dump before starting.
   b- recreate manually datafiles/tablespaces and import data from dump at the end.

2) a- backup via RMAN all the DB.
   b- restore via RMAN the backup at the end.

3) a- create recovery-point.
   b- flashback at the end ....???? (I'm not sure about this chance with change on Datafiles and tablespace.

What your opinion on it?
Thanks,
 Ste

From the very first lines in the Oracle Documentation about Flashback Database feature ( https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta023.htm#RCMRF194 ):

  This command works by undoing changes made by Oracle Database to the data files that exist when you run the command.

Flashback its mean to undo logical changes in the database. A Backup its a cloned copy of your database in a point in time. So, if you want to reproduce the exactly same environment for any subsequent tests, involving DB changes, you should go for a RMAN backup/restore technique.

Also, for the single instance DB, If it's going to be just a Test environment, you could avoid the creation of Archives and Flashback logs for saving lots of space, and create/restore from cold backups.

Scripting to recreate the DB + dp exp/imp can be another option, but its a waste of time having RMAN.


Creat Temporary Id in SQL Statement I have a table

id  name
1   Alpha
2   Alpha
3   Beta
4   Charlie
5   Charlie

I want to assign a temporary id at the run time in the SQL statement, So the desired result should be like

myid  name
1   Alpha
2   Beta
3   Charlie

So basically the logic should be like reading the value of name field and see if it is changed then increment the value for custom field. How we can achieve this?

Create a temp table to build the myid column for you

DROP DATABASE IF EXISTS neeraj;
CREATE DATABASE neeraj;
USE neeraj
CREATE TABLE mytable
(
    id int not null auto_increment,
    myid int not null default 0,
    name varchar(20),
    primary key (id),
    key (name)
);
INSERT INTO mytable (name) VALUES
('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
SELECT * from mytable;
CREATE TABLE mytablemyid LIKE mytable;
INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
UPDATE mytablemyid A
INNER JOIN mytable B
USING (name) SET B.myid = A.id;
DROP TABLE mytablemyid;
SELECT * from mytable;

Here is the result:

mysql> DROP DATABASE IF EXISTS neeraj;
Query OK, 2 rows affected (0.07 sec)

mysql> CREATE DATABASE neeraj;
Query OK, 1 row affected (0.00 sec)

mysql> USE neeraj
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id int not null auto_increment,
    ->     myid int not null default 0,
    ->     name varchar(20),
    ->     primary key (id),
    ->     key (name)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('Alpha'),('Alpha'),('Beta'),('Charlie'),('Charlie');
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    0 | Alpha   |
|  2 |    0 | Alpha   |
|  3 |    0 | Beta    |
|  4 |    0 | Charlie |
|  5 |    0 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE mytablemyid A
    -> INNER JOIN mytable B
    -> USING (name) SET B.myid = A.id;
Query OK, 5 rows affected (0.05 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> DROP TABLE mytablemyid;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * from mytable;
+----+------+---------+
| id | myid | name    |
+----+------+---------+
|  1 |    1 | Alpha   |
|  2 |    1 | Alpha   |
|  3 |    2 | Beta    |
|  4 |    3 | Charlie |
|  5 |    3 | Charlie |
+----+------+---------+
5 rows in set (0.00 sec)

mysql>

Now you have a temporary column at your disposal

You could also build it as a separate table called mytablemyid

mysql> CREATE TABLE mytablemyid LIKE mytable;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE mytablemyid DROp COLUMN myid;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO mytablemyid (name) SELECT DISTINCT name FROM mytable;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * from mytablemyid;
+----+---------+
| id | name    |
+----+---------+
|  1 | Alpha   |
|  2 | Beta    |
|  3 | Charlie |
+----+---------+
3 rows in set (0.00 sec)

mysql>


When `nvarchar/nchar` is going to be used with SQL Server 2019? With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says:

  This feature may provide significant storage savings, depending on the
  character set in use. For example, changing an existing column data
  type with ASCII strings from NCHAR(10) to CHAR(10) using an UTF-8
  enabled collation, translates into nearly 50% reduction in storage
  requirements. This reduction is because NCHAR(10) requires 22 bytes
  for storage, whereas CHAR(10) requires 12 bytes for the same Unicode
  string.

UTF-8 seems to support every script, so basically we can start storing Unicode data in varchar and char columns. And as is said in the documentation, this can reduce the size of tables and indexes, and from there we can get even better performance, because smaller amount of data is read.

I am wondering does this mean we can stop to use nvarchar and nchar columns which implements UTF-16?

Can anyone point a scenario and reason, not to use the char data types with UTF encoding and continue use the n-chars ones?

UTF-8 support gives you a new set of options. Potential space savings (without row or page compression) is one consideration, but the choice of type and encoding should probably be primarily made on the basis of actual requirements for comparison, sorting, data import, and export.

You may need to change more than you think, since e.g. an nchar(1) type provides two bytes of storage. That is enough to store any character in BMP (code points 000000 to 00FFFF). Some of the characters in that range would be encoded with just 1 byte in UTF-8 while others would require 2 or even 3 bytes (see this comparison chart for more details). Therefore, ensuring coverage of the same set of characters in UTF-8 would require char(3).

For example:

DECLARE @T AS table
(
    n integer PRIMARY KEY,
    UTF16 nchar(1) COLLATE Latin1_General_CI_AS,
    UTF8 char(1) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

INSERT @T (n, UTF16, UTF8)
SELECT 911, NCHAR(911), NCHAR(911);

gives the familiar error:

  Msg 8152, Level 16, State 30, Line xxx
  String or binary data would be truncated.

Or if trace flag 460 is active:

  Msg 2628, Level 16, State 1, Line xxx
  String or binary data would be truncated in table '@T', column 'UTF8'. Truncated value: ' '.

Expanding the UTF8 column to char(2) or varchar(2) resolves the error for NCHAR(911):

DECLARE @T AS table
(
    n integer PRIMARY KEY,
    UTF16 nchar(1) COLLATE Latin1_General_CI_AS,
    UTF8 varchar(2) COLLATE Latin1_General_100_CI_AS_SC_UTF8
);

INSERT @T (n, UTF16, UTF8)
SELECT 911, NCHAR(911), NCHAR(911);

However, if it was e.g. NCHAR(8364), you would need to expand the column further, to char(3) or varchar(3).

Note also that the UTF-8 collations all use supplementary characters, so will not work with replication.

Aside from anything else, UTF-8 support is only in preview at this time, so not available for production use.


Graphical user interface to change data capture on Oracle I'm learning how to implement change data capture in Oracle. However, not being a DB specialist but rather a developer, I find the process tedious with respect to other things that I have to do. I end up doing it because my DBA/DevOp don't want to take care of it.

Hence I was wondering if there is any tool that can help set up Oracle change data capture. In other words a simple graphical user interface that would write the all code for me. Creation of change table, PL/SQL script, etc.

Duplicate post in StackOverflow.

Oracle 11.1 Documentation Warning: "Oracle Change Data Capture will be de-supported in a future release of Oracle Database and will be replaced with Oracle GoldenGate. Therefore, Oracle strongly recommends that you use Oracle GoldenGate for new applications."

Attunity, the company I work for, offers REPLICATE, a sleek GUI alternative.

hth,
Hein.


Which tool to compare an Oracle database with a Postgresql database? We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3.
Thanks to Hibernate, all tables can be recreated seamlessly.

However, the Oracle database is huge. We are going to write a small tool that will bring the data from one db to the other.

We want to check the quality of the migration with some kind of third-party database comparator.

Do you know a tool that can compare an Oracle database and a Postgresql database (schema + data) ?

You could try the Schema Comparison Tool from dbsolo. I don't know if you can compare different databases with one another.

You have to keep in mind that Oracle and PostgreSQL have quite different table definition clauses which make it difficult to compare them accurately.


How to convert rows into columns and query on them in Mysql? I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the values of the controls submitted for each Patient

Lets have some data
Patients Table

|ID  | Name |
|-----------|
| 1  | Ara  |
| 2  | Sada |

Controls table

|ID  | Text | Type     |
|-----------|----------|
| 1  | age  | textbox  |
| 2  |alergy| checkbox |

Then the controlsValues table which is where I want to query at

|ID  | contrlId | value    | patientId |
|---------------|----------|-----------|
| 1  | 1        | 23       | 1         |
| 2  | 2        | true     | 1         |
| 3  | 1        | 26       | 2         |
| 4  | 2        | false    | 2         |

here my problem occurs when I want to return that patient from ControlsValues table that has the (controlId=1 AND value=23) and (controlId=2 AND value=true)
in this case the condition is on two rows not two columns which it is not possible , so I desided to change the rows into the columns depending on controlId but I dont know how and I have been searching for 2 days and seen a lot of samples but none of them helped me to solve my problem

You can try this kind of "solution":

http://sqlfiddle.com/#!9/d33a95/18

It is not an "pivot-table" solution, but maybe it solves your problem - if I have understood it correctly.

Here are the tables / data used:

CREATE TABLE `patients` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controls` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Text` VARCHAR( 50 ) NOT NULL ,
`Type` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `ID` )
);
CREATE TABLE `controlvalues` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`ControlID` INT NOT NULL ,
`Value` VARCHAR( 50 ) NOT NULL ,
`PatientID` INT NOT NULL ,
PRIMARY KEY ( `ID` )
);
INSERT INTO `patients` (`Name`) VALUES ('Ara'), ('Sada'), ('Pada'), ('Lada');
INSERT INTO `controls` (`Text`, `Type`) VALUES ('age', 'textbox'), ('alergy', 'checkbox');
INSERT INTO `controlvalues` (`ControlID`, `Value`, `PatientID`) VALUES
  (1, '23', 1),
  (2, 'true', 1),
  (1, '25', 2),
  (2, 'false', 2),
  (1, '23', 3),
  (2, 'false', 3),
  (1, '23', 4),
  (2, 'true', 4);

And the query:

SELECT PatientID, COUNT(PatientID) AS PatCount, p.Name
FROM controlvalues cv
LEFT JOIN patients p ON p.ID = cv.PatientID
WHERE ((ControlID = 1 AND Value = '23')
   OR (ControlID = 2 AND Value = 'true'))
GROUP BY PatientID
HAVING PatCount = 2
;


Is following way is right Database model for following problem? I am new to the database design so I have to check my data model is correct way to handle the problem or not.

Problem Statement:  I am designing the database for student portal which have following user roles

Administrator
School(Principal)
Teacher
Student

The functionalities is for phase I is just maintain all users data and provide the login system.

I am thinking about the database table are

-------------   -------------   -----------
Schools            Teachers       Students
-------------   -------------   -----------
id                id              id
name              name            name
other info        other info      other info

And I am decided to create the another table which store the login creadintilas and role with id as below

Users Table

--------------------------------------------
username    password      role      user_id
--------------------------------------------

but I am not sure about how to applying the PK/FK relations on the table.
I want create the same login system for all system because all the users shares some features i.e. teachers,student,schools can update the student info.
Questions

Is this right way of thinking?
Can i do it in another better way?

Here are two different possible solutions.

Solution 1:

Solution 2:

For both models, Users is a parent table, with different tables subclassing it. This is achieved by the primary key of the child tables also being a foreign key to the Users' primary key.

Solution 1 involves having a list of possible user roles in the Roles table (Administrator, School, Teacher, Student) with the User having a reference to a role.

Solution 2 involves deriving the idea of the User's role from which child table the User belongs to. This would mean that a row belonging to Users must also belong to one of the child tables.

I personally like the first solution better, since it separates the idea of a Users' role in the portal from the User's position within the school environment.

As an additional note, I would look into "salted password encryption" for storing passwords, especially if this database is for a real application.


mysqld_safe not run on RHEL7, while it runs on RHEL6 when starting mariadb I see this strange behavior when Mariadb is run on RHEL7 and RHEL6.

When I start the mariadb server on RHEL6 via service mysql start, I see mysqld_safe starts along with mysqld.

[user@divinedragon]# service mysql start
Starting mysql (via systemctl):                            [  OK  ]

[user@divinedragon]# ps aux | grep mysql
root      2398  0.0  0.0 115372  1712 ?        S    09:26   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/divinedragon.pid
mysql     2472  2.8  3.4 798208 134576 ?       Sl   09:26   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/divinedragon.err --pid-file=/var/lib/mysql/divinedragon.pid
root      2503  0.0  0.0 112644   956 pts/0    S+   09:27   0:00 grep --color=auto mysql

However, when I do the same thing on RHEL7, I don't see any mysqld_safe process coming up. Here is the ouput from RHEL 7.

[user@divinedragon ~]$ sudo service mysql start
Starting mysql (via systemctl):                            [  OK  ]

[user@divinedragon ~]$ ps aux | grep mysql
mysql     2515  2.6  6.0 774616 114412 ?       Ssl  08:51   0:00 /usr/sbin/mysqld
user      2548  0.0  0.0 112644   952 pts/0    R+   08:51   0:00 grep --color=auto mysql

Can somebody shed some light in here?

It is stated in the mariadb and even mysql docs:

https://mariadb.com/kb/en/mariadb/systemd/

systemd services are packaged on RPM and Debian based Linux distributions. When systemd is used, mysqld_safe is not used and settings in the mysqld_safe section of configuration files will not be read or applied.

https://dev.mysql.com/doc/refman/5.7/en/server-management-using-systemd.html

On platforms for which systemd support is installed, scripts such as mysqld_safe and the System V initialization script are not installed because they are unnecessary. For example, mysqld_safe can handle server restarts, but systemd provides the same capability, and does so in a manner consistent with management of other services rather than using an application-specific program.


Configuring backup drives for Alwayson I have an alwayson cluster which was already built when I joined the company. Both the nodes Have Data and log drives but no dedicated Backup drives. The Veeam application which resides in this cluster is not growing much in terms of database size and is being backed up to the data drive as of now.

We are planning to add two more applications to this cluster. I am thinking to ask for a dedicated backup drive for this. My question is does this 2 node setup require 2 backup drives? One for secondary and one for primary? Please clarify

Thanks!

... is being backed up to the data drive as of now.

So, what happens when you lose a node? What would happen if that server crashed and burned or you lost the data drive? Now you've lost the database backups...

  My question is does this 2 node setup require 2 backup drives? One for secondary and one for primary? Please clarify

The best option would be to have an SMB 3.x share to which these backups are sent. You could also tie into a 3rd party backup software like Commvault, netbackup, etc., by writing your backup process to hit local disk then copy to the 3rd party service/appliance.

The reason is that the AG could be primary on, potentially, any node. If we're backing up to local disk, the backups could be spread across many nodes. This means we might not be able to have an entire backup chain when it comes time for a disaster.


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

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

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

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

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

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

You need a conditional aggregate using CASE:

SELECT sr.itemID, sr.itemname,
   SUM(sr.itemunits) AS "Total Units Sold",
   SUM(CASE WHEN date='2016-04-14' THEN sr.itemunits END) AS "Units Sold"
FROM salesrecord AS sr
WHERE date <= '2016-04-14'
GROUP BY sr.itemID, sr.itemname

I used a table-alias sr which improves readability.

And I added sr.itemID to GROUP BY, which is compliant with Standard SQL (and the latest version of MySQL)


How to update the parent/child of all rows in the tree (ltree)? Here are my configurations:
DB type: postgresql (9.3 if that's necessary)
Table name: product_sections
Column names: section_id (integer) & section_path (ltree)

Detail:
I have one reference value: section_id: 15 & section_path: Automation_Solutionz. So, when I want to rename Automation_Solutionz to, say Chrome, I want all references of Automation_Solutionz to be updated.

FROM:

Automation_Solutionz
Automation_Solutionz.Test_Case_Creation
Automation_Solutionz.Test_Case_Creation.Automation
Automation_Solutionz.Test_Case_Creation.Manual

TO:

Chrome
Chrome.Test_Case_Creation
Chrome.Test_Case_Creation.Automation
Chrome.Test_Case_Creation.Manual

Similarly, when I update a value which is in the middle, it should rename those too. In the case above, Test_Case_Creation should be renamed to something like TestCase or anything else. The same goes for any item which is either in the middle, end or at the start.

Hope, I explained things correctly. Thanks in advance :)

One can view the ltree data type as a simple text with some constraints.  The most important of these constraints is that a label is bordered by dots if part of a label path.  Obviously, if it stands at either end, one dot will be missing; when alone, the dots are completely missing.

Fortunately, casting between the two types is easy.  This way we can set up a small set of rules for handling replacement, using lquery to find the cases:

-- 1. replace when the label is the whole labelpath
replace(section_path::text, 'to_be_replaced', 'replacement')
    WHERE section_path ~ 'to_be_replaced'::lquery

-- 2. replace when the label is at the end
replace(section_path::text, '.to_be_replaced', '.replacement')
    WHERE section_path ~ '*.to_be_replaced'::lquery

-- 3. replace when the label is at the beginning
replace(section_path::text, 'to_be_replaced.', '.replacement')
    WHERE section_path ~ 'to_be_replaced.*'::lquery

-- 4. replace when the label is in the middle
replace(section_path::text, '.to_be_replaced.', '.replacement.')
    WHERE section_path ~ '*.to_be_replaced.*'::lquery

In cases 2-4, mind the dots in the search and replacement values of replace().  This way you can be sure you always replace the whole label, not just parts of it.


Backup SQL Server with a system backup I have installed a Microsoft SQL Server 2012 on a server that also are used as a file server. Each day the server is automatic backed up with Acronis Backup & Recovery to a USB-disc that we switch every day.

My question is whether it is enough to make a system backup, or if I also need to do a SQL Server Backup. Are there any disadvantages to doing like this?

No, you cannot rely on system or NTFS backups as an appropriate disaster recovery strategy for SQL Server.  If all your current solution is doing is backing up the database files (i.e. mdf, ldf, ndf) and if you needed to revert to those "backups", then you could potentially run into a horrific situation where SQL Server can't use those database files.

You need to ensure that you're taking SQL Server backups (full, differential, transaction log [unless you're in simple recovery]).  This will provide you with the correct way to recover from disaster in the event you need to do a restore.

A few notes:

Store your backup files separately from the server in order to not have a single point of failure
You need to test your backed up databases for corruption(i.e. restore database, run DBCC CHECKDB against restored database).  If you have been backing up a corrupt database unknowingly for 3 weeks, and your backup retention is only 2 weeks, then you could have only a corrupt database that has been backed up


MSSQL: How to get the data from 1st - 15th and 16th - Last day of the PREVIOUS MONTH I have a table that have these values:

From data   | Todate     | is_Complete

2018-11-01 |  2018-11-15   |    True
2018-11-16 | 2018-11-30   |    True

We are executing data every 1st day of the month. So assuming that It's already 1st day of december. I need to check if is_Complete column is True from first day of the previous month and 16th up to last day of the previous month then process my queries but if one of those is False I will not process it.

I am not sure if CAST will work here.

IF both is_Complete == True, then

process queries ELSE 'N/A'

declare @AA_WC_TEST_PAYRUN table(from_date date, to_date date, is_Complete varchar(10));
insert into @AA_WC_TEST_PAYRUN
values
('2018-10-01', '2018-10-15', 'True'),
('2018-10-16', '2018-10-30', 'True');

if (select is_Complete
    from @AA_WC_TEST_PAYRUN
    where from_date = (select dateadd(month, datediff(month, 0, getdate())-1, 0))) = 'True'
and
    (select is_Complete
    from @AA_WC_TEST_PAYRUN
    where from_date = (select dateadd(day, 15, dateadd(month, datediff(month, 0, getdate())-1, 0)))) = 'True'
begin
   print 'both true';
   -- other statements
end;


Add aggregated value filtered by a condition to existing query I have this table to collect data from IoT device:
CREATE TABLE public.conferimenti
(
    id bigint NOT NULL DEFAULT nextval('conferimenti_id_seq'::regclass),
    codicestazione smallint NOT NULL,
    codicetag character varying(20) COLLATE pg_catalog."default" NOT NULL,
    dataora timestamp without time zone NOT NULL,
    peso numeric(10,2) NOT NULL DEFAULT 0.0,
    tiporifiuto smallint NOT NULL,
    data character varying COLLATE pg_catalog."default"
)

I am doing a MATERIALIZED VIEW which contains the SUM of weights:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora,
    c.codicestazione,
    c.idcomune,
    sum(c.peso) AS totale_peso_conferimenti,
    count(c.id) AS totale_conferimenti
FROM conferimenti c
GROUP BY 1, c.codicestazione, c.idcomune

I need to add another column called "totale_peso_conferimento_tipo" with the SUM(c.peso) only for rows where tiporifiuto = 0.
At the moment I'm using 2 separate queries. Maybe it can be done using window functions?

Sounds like you want the aggregate FILTER clause:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora
     , c.codicestazione
     , c.idcomune
     , count(c.id) AS totale_conferimenti
     , sum(c.peso) AS totale_peso_conferimenti
     , sum(c.peso) FILTER (WHERE tiporifiuto = 0) AS totale_peso_conferimento_tipo
FROM   conferimenti c
GROUP  BY 1, c.codicestazione, c.idcomune;
See:

Aggregate columns with additional (distinct) filters
How to count multiple values of a column as one group?


Only Select Supersets I have two tables (along with a nonclustered index) that can be created with the below commands:

CREATE TABLE GroupTable
(
  GroupKey int NOT NULL PRIMARY KEY,
  RecordCount int NOT NULL,
  GroupScore float NOT NULL
);

CREATE TABLE RecordTable
(
  RecordKey varchar(10) NOT NULL,
  GroupKey int NOT NULL,
  PRIMARY KEY(RecordKey, GroupKey)
);

CREATE UNIQUE INDEX ixGroupRecord ON RecordTable(GroupKey, RecordKey);

While technically my tables are slightly different and I am joining on a few other tables, this is a suitable proxy for my situation.

I would like to select all GroupKeys that are not subsets of another GroupKey.
For a given superset, I would like to grab the maximum GroupScore of all its subsets (including itself).
In the instance when a GroupKey contains the same exact RecordKeys as another GroupKey(s), then only one of those GroupKeys is grabbed (it doesn't matter which one).
Any GroupKey that has the same exact RecordKeys as another GroupKey(s) will also have the same GroupScore.
Non-related GroupKeys can have the same score as well.

The following is an example to illustrate what I am asking:

              GroupTable                          RecordTable

GroupKey    RecordCount   GroupScore         RecordKey    GroupKey
------------------------------------         ---------------------
  1              3            6.2                A          1
  29             2            9.8                A          29
  95             3            6.2                A          95
  192            4            7.1                A          192
                                                 B          1
                                                 B          29
                                                 B          95
                                                 B          192
                                                 C          1
                                                 C          95
                                                 D          192
                                                 E          192

I would like the output to be the following:

GroupKey    RecordCount    GroupScore
-------------------------------------
  1              3             9.8
  192            4             9.8

GroupTable has around 75M rows, and RecordTable has around 115M rows; however, after the joins and WHERE predicate, there tends to be around 20k rows on a given day.

I apologize if this question is trivial, but for some reason I'm really struggling with it.

I would like the output to be the following:

 GroupKey    RecordCount    GroupScore
 -------------------------------------
   1              3             9.8
   192            4             7.1

Using correlated subqueries is one way to get the output you want.

  In the instance when a GroupKey contains the same exact RecordKeys as another GroupKey(s), then only one of those GroupKeys is grabbed (it doesn't matter which one).

I'm returning the Group with the lowest GroupKey when there is a match, but that is arbitrary as you say it doesn't matter.

test data:

INSERT INTO RecordTable(RecordKey,GroupKey)
VALUES ('A',1)
     , ('A',29)
     , ('A',95)
     , ('A',192)
     , ('B',1)
     , ('B',29)
     , ('B',95)
     , ('B',192)
     , ('C',1)
     , ('C',95)
     , ('D',192)
     , ('E',192);

INSERT INTO GroupTable(GroupKey,RecordCount,GroupScore)
VALUES (1,3,6.2)     -- ABC
     , (29,2,9.8)    -- AB
     , (95,3,6.2)    -- ABC
     , (192,4,7.1);  -- ABDE
GO

query:

SELECT GroupKey
     , RecordCount
     , GroupScore = ( SELECT max(GroupScore)
                      FROM GroupTable g2
                      WHERE ( SELECT count(*)
                              FROM ( SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g1.GroupKey
                                     UNION
                                     SELECT RecordKey
                                     FROM RecordTable
                                     WHERE GroupKey=g2.GroupKey ) z
                            )=g1.RecordCount )
FROM GroupTable g1
WHERE NOT EXISTS ( SELECT *
                   FROM GroupTable g3
                   WHERE ( SELECT count(*)
                           FROM ( SELECT RecordKey
                                  FROM RecordTable
                                  WHERE GroupKey=g1.GroupKey
                                  UNION
                                  SELECT RecordKey
                                  FROM RecordTable
                                  WHERE GroupKey=g3.GroupKey ) z )=g3.RecordCount
                         AND ( g3.RecordCount>g1.RecordCount
                               OR ( g3.RecordCount=g1.RecordCount
                                    AND g3.GroupKey<g1.GroupKey ) ) );
GO

The subquery in the SELECT gets the highest GroupScore from only those groups that are subsets of this ('g1') group. It achieves this by counting the UNION of the RecordKey's for the 'g1' set and each 'g2' set. If the UNION is larger than the 'g1' set, there must be at least one RecordKey in the 'g2' set without a corresponding RecordKey for the 'g1' set, so the 'g2' set is not a subset and should not be considered for this row.

In the WHERE clause, there are two cases to consider for filtering. In either case, the 'g1' set is only filtered if all the 'g1' RecordKeys are also present in the 'g3' set; and this check is achieved by counting the union again (as per the SELECT clause).

The two cases are: ① the 'g1' set has fewer RecordKeys (g3.RecordCount>g1.RecordCount; in which case we filter), and ② the 'g1' set is identical to the 'g3' set (g3.RecordCount=g1.RecordCount; in which case we arbitrarily choose the set with the lower GroupKey)

output:

/*
|GroupKey|RecordCount|GroupScore|
|-------:|----------:|---------:|
|       1|          3|       9.8|
|     192|          4|       9.8|
*/

dbfiddle here


MariaDB FULLTEXT search with apostrophe, and mandatory words An apostrophe / single quote (') should be part of a word in a FULLTEXT index.

Admittedly I can't find this on the MariaDB website, but I'm assuming it should still behave like MySQL (version 5.5).

But when using "IN BOOLEAN MODE", and prefixing the word with a "+" (so it's mandatory in all rows returned), the record is not returned.

For example:

CREATE TABLE customer (
    name TINYTEXT NOT NULL,
    FULLTEXT (name)
) ENGINE = InnoDB;

INSERT INTO customer VALUES ('O''Brien');
INSERT INTO customer VALUES ('O Brien');
INSERT INTO customer VALUES ('X''Brien');
INSERT INTO customer VALUES ('Extra Amy');
INSERT INTO customer VALUES ('Extra Brian');
INSERT INTO customer VALUES ('Extra Cat');
INSERT INTO customer VALUES ('Extra Debbie');

I get these results:

SELECT * FROM customer WHERE MATCH (name) AGAINST ("O'Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE);
Empty set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST ("+Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

SELECT * FROM customer WHERE MATCH (name) AGAINST (+"O'Brien" IN BOOLEAN MODE);
  "O'Brien"
  "O Brien"
  "X'Brien"
3 rows in set (0.000 sec)

Am I missing something, like needing to quote the apostrophe?

Or is this an intentional difference between InnoDB and MyISAM?

Thanks to @rick-james, I've updated this question to include 3 "extra" records to avoid the 50% threshold limit, and I've included an example where the + is before the quoted word.

Works for me in both MyISAM and InnoDB.

But note:  out of 185,082 rows, 17 have "O'Brien".  That's less than 50%, so the search term is not ignored.  I verified the count with LIKE "%O'Brien%".

But, when adding the +, it needs to be outside the quotes for InnoDB.  (Yet another difference between the implementations.)

(I have updated my blog to add this diff.)


MySql Master Master out of sync I have 2 MySql nodes with Master-Master Replication set-up.
MySql1 has read and write access and is in current state.
MySql2 has only read access and stopped working due to space issue.
How can I fix this issue and resync it.

Given the 'Could not find first log file name' error means Mysql1 has rotated away its binary logs.
You will need to reinitialize Mysql2 as if it where a new replica.
RESET SLAVE ALL on Mysql1 to ensure it forgets about its MySQL2 before beginning.


MySQL and PostgreSQL has JSON data types, is it the same as NoSQL database? MySQL and PostgreSQL introduced JSON data types, is it the same as NoSQL database?

Why?

NoSQL here is a class of data bases similar to MongoDb and DynamoDB.

A relational DBMS processes data as relations, which is just a fancy way of saying tables consisting of rows and columns. Even if a column's type is defined as JSON, to the DBMS it is still a single column's value. There's an analogy with, say, a column defined as decimal. Externally we may say that column has an integer part, a fractional part, a number of decimal places and so on. To the DBMS, however, it is a single column's value.

The same holds even if code pulls values from within the JSON. These pulled values will be presented to the consumer as columns within a row. This row will be part of a relation (i.e. a table), and every row in that relation will have the same structure. Just because a query's result set is constructed on-the-fly at run-time it is no less a relation than those defined through a CREATE TABLE statement.

A NoSQL DBMS (and here I believe you mean the document DB variety, like MongoDB, rather than key-value or columnstores), however, thinks of data as JSON documents. It accepts well-formed JSON as input and returns JSON in response to queries. This is its model. It sees the world as documents, not tables.

In both models it is the interface between the client and the server that is the distinguishing feature. What each DBMS chooses to do internally is nobody's business but its.