How to calculate quantity of WAL in db without archiving? I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are being used in last 24hours in order to predict how much space I need on external storage.
Unfortunately pgmetrics doesn't have such information. Do you know how to do it?

WAL Files:
WAL Archiving?       no
WAL Files:           5
+--------------------+--------------+
|            Setting |        Value |
+--------------------+--------------+
|          wal_level |      minimal |
|    archive_timeout |            0 |
|    wal_compression |          off |
|       max_wal_size | 64 (1.0 GiB) |
|       min_wal_size |   5 (80 MiB) |
| checkpoint_timeout |          300 |
|   full_page_writes |           on |
|  wal_keep_segments |            0 |
+--------------------+--------------+

There is no easy way as such to get a count of the WAL Files. But what you could do is run the SQL below at a certain time and then run it again 24 hours later. Getting the difference should give you some indication of the number.

SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';

Some details Postgreal Admi functions and pgDash blog.


Fuzzy Matching with Postgresql 9.3 Right now I am using ts_rank (with ts_vector and ts_query) to search strings for relevance. While this is doing a great job for me so far. I was hoping to work in a bit of fuzzieness into the searching.

As it stands the search is done by a user entering their terms as a string, that I then parse into tokens. These tokens are then compared against a string. What I would like to do is use something similar to a levenshtein in these queries to allow for minor misspellings to be matched as well.

For example if the user typed in rubico instead of rubicon I would still get a match on it (because rubico only has a distance of 1 from rubicon).

Is this possible to do with Postgres' ts_rank functionality, or are there other options to allow for text searches to work with levenstheins?

Instead of matching the portion of the string that I wanted to fuzzy search on I instead, concatinated all of the terms into a string, and then ran the difference function (from the fuzzystrmatch module) against it. So it looked something like this.

FOR term IN terms LOOP
    term_string := term_string||' '||term;
END LOOP;

score := score + difference(term_string,string_to_compare_against);

I did need to set a threshold that needed to be passed by the score to keep from getting to many false positives.


Oracle Data Pump, pause a job I currently have a impdp job running for a fairly large schema (2TB) and ran into an error.  The source database has one column that is larger then the destination database.  This is causing ORA-02374 and ORA-12899 errors.  This has caused the import job to come to a crawl on this one table.

I am trying to get past the errors, and the best solutions I can come up with are to either fix the table definition, or tell the import job to skip the current table.  I can not currently fix the table definition because the data pump job has the table locked.

Is there a way to pause the job, make the column modification, and then resume the import?  Alternatively is there a way to tell the job to skip the current table, and move on?  This would allow to to come back once the job is finished, fix the table definition, and then re-import just the one table.

ETA:  This is one of the last few tables in the import, I would rather not kill the whole thing and start over.

Well you can stop the job and make changes in the table and later continue the job either skipping the current object or not.

Demo:

 [oracle@localhost]$ impdp jay/password directory=dp_dir  tables=imptest, imptest2 content=data_only dumpfile=exp_jay_imptest.dmp
 ...

 ORA-02374: conversion error loading table "JAY"."IMPTEST"
 ORA-12899: value too large for column NAME (actual: 12, maximum: 10)

 ORA-02372: data for row: NAME : 'mysamplename'

 ^C--Press CTRL+C keys.
 Import> stop_job=immediate
 Are you sure you wish to stop this job ([yes]/no): yes

Now alter the table definition.

 SQL> alter table imptest modify name varchar2(20);

 Table altered.

 SQL> exit

 [oracle@localhost]$ impdp jay/password attach=SYS_IMPORT_TABLE_01--- you can get job name from log file.

 Import> start_job

 Import> continue_client
 Job SYS_IMPORT_TABLE_01 has been reopened at Tue Oct 11 07:39:12 2016
 Restarting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=dp_dir tables=imptest,imptest2 content=data_only dumpfile=exp_jay_imptest.dmp
 . . imported "JAY"."IMPTEST"                             19.00 MB 1049211 out of 1048576 rows
 . . imported "JAY"."IMPTEST2"                            7.710 KB     396 rows
 Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 11 07:40:13 2016 elapsed 0 00:01:02


Sql query for all ascendent of a node I have a table having records as below:

ID   Name   parentId
1    A      Null
2    B      1
3    C      Null
4    D      3
5    E      4

I need out put in following format

ID   Name   ParentName

 1    A      Null
 2    B       A
 3    c      Null
 4    D      C
 5    E      C->D

I tried with following sql query but not able to get exact output. can anybody help me out

With CTE AS
(
   select id, Name, CAST('' AS VARCHAR(MAX)) as Parent from @table
   union All
   select t.Id, t.Name , CTE.Parent+','+CAST(t.Name AS VARCHAR(MAX)) as parents from
   CTE c inner join @table t on t.parentid=c.id and c.parentid is not null
)
select * from cte

Make sure that the anchor is set properly (need to be either the roots or the final leaves). Your current anchor set (the first set from the UNION ALL) is retrieving all records from the table, not just the border ones.

Since your relationships are through parent and not by child, the anchor should contain all records without parents. Then join recursively with each child.

DECLARE @table TABLE (ID INT, name VARCHAR(1), parentID INT)

INSERT INTO @table
VALUES
    (1, 'A', NULL),
    (2, 'B', 1),
    (3, 'C', NULL),
    (4, 'D', 3),
    (5, 'E', 4)

;With CTE AS
(
    -- Anchor (all grandfathers)
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = CONVERT(VARCHAR(MAX), NULL) -- Set the data type for the UNION
    FROM
        @table AS T
    WHERE
        T.parentId IS NULL

    UNION ALL

    -- Traverse through each children
    SELECT
        ID = T.ID,
        Name = T.Name,
        ParentName = ISNULL(C.ParentName + ' -> ', '') + C.Name
    FROM
        CTE AS C
        INNER JOIN @table AS T ON C.ID = T.parentId
)
SELECT
    C.*
FROM
    cte AS C
ORDER BY
    C.ID

Result:

ID  Name    ParentName
1   A       NULL
2   B       A
3   C       NULL
4   D       C
5   E       C -> D


Text index using text_pattern_ops with utf-8 vs. default operator class with C? What is happening with each operator class, and why is it necessary to use text_pattern_ops when using utf-8?
When using the default operator class with C, what happens to the text column indexes if the database is dumped to another Postgres that uses utf-8? Is it simply a matter of creating new indexes with text_pattern_ops?
Does the default operator class with C perform any better or worse than text_pattern_ops?

I'm considering changing the RDS client_encoding parameter to C so it wouldn't be necessary to use text_pattern_ops everywhere, but I'd like to find out if any disadvantages (other than not supporting emojis and other languages) or complications may exist.

Related docs: https://www.postgresql.org/docs/current/static/indexes-opclass.html

It isn't about encoding, it is about collation.

Consider this:

select * from (values ('1,z'),('1,2j'),('12q'),('13 apples')) foo(x)
  order by x collate "en_US";

gives:

     x
-----------
 1,2j
 12q
 13 apples
 1,z

If you index using that collation, how would you efficiently support a x like '1,%' query?  Not all the things starting with '1,' are adjacent in the index.  This is the problem that text_pattern_ops (or C collation) solves.

You can make a database with UTF8 encoding but C collation.  This mean you don't have to sprinkle text_pattern_ops all over your indexes, and your index builds over text columns will be much faster (and any sort-merge joins you do).  But you can still store non-ASCII characters, although they will sort funny.

Also, setting client_encoding won't make any difference.  Is is the server's encoding and collation that matter, not the client's.


MySQL output file location - accessing server files I am running the MySQL Workbench 6.2 CE on Windows7. I have an output table of 200,000 rows and I need to save it. I will have to repeat this task, so I would like to iterate the following code:

SELECT * FROM mytable
INTO OUTFILE 'mytable.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This saves my output but I cannot access it. It seems it is saved in the server database. I have the server installed in my desktop so I should be able to access it. Do you know how could I find the filepath to my saved csv files?

Edit1: I can save it manually in the Workbench GUI but I would like to find a code-based solution as I have to iterate this process many times.

Edit2: Putting all the file path doesn't work. It gives an error: Errcode: 13 - Permission denied

PS: Sorry if it is a basic question, I am new to MySQL.

If this is a one-time thing, you can do the following to export the result into csv file using MySQL workbench:

SELECT * FROM mytable

then the result will appear in the result table (area) all you need to do use export button in that area:

Otherwise, if you want to create output file you can do it by using a shared folder:

if its over a network //my_pc/folder/test.csv that both machines have read/write access
and if its under the same server use for example d:/test.csv

and save the file into any location you want.


error 2013 (hy000) lost connection to mysql server during query while load of mysqldump I'm trying to load mysqldump and I keep getting following error:

  ERROR 2013 (HY000) at line X: Lost connection to MySQL server during
  query

/etc/my.cnf:

[mysqld]
max_allowed_packet = 16M
net_read_timeout = 30
net_write_timeout = 60
...
[mysqldump]
max_allowed_packet = 16M

I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.


The database system then writes out enough information to disk The text below is from the book Database System Concepts by Silberschatz.
This information that is written out to disk forms a log file or is something other that the log file?

The database system then writes out enough information to disk that,
even in the event of a failure, the updates performed by the
transaction can be re-created when the system restarts after the
failure. When the last of this information is written out, the
transaction enters the committed state.

The concept of Transaction Journalisation has been release in the 70's by Bernstein and Gray. The algorithm used is based on ARIES for all the Relational DBMS.
The way that does it is quite simple. Every write that must occurs in the database (INSERT, UPDATE, DELETE, but also CREATE, ALTER, DROP, GRANT...) need a safety net, until the all work is done. To do this, the values before modifiying the database state is physically copyed into a special storage called "transaction log". After the data has been modified in memory, this transaction is marked as "done" in the transaction log, if no exception is raised. That is the COMMIT. In case of an exception (or a voluntary cancel of the writes) that's the ROLLBACK, and previous values are replaced in the memory.
I say that data are written in memory... Not on disk. But the problem is that a server has generally not enough memory to store inside the RAM all the datas.. Also, on a regular tempo, a process called CHECKPOINT is started in order to transcribe, on the data storage, the data modified in memory between two passages.
CHECKPOINT occurs generally on different objectives : a limit of amount of data, a limit of time and a limit on memory pressure...
Let me know if I am not clear or you want some complementary explanations
I am writing a french book about SQL and relational databases that explains all that folks...
To be complet, please read "Database Management Systems 3rd Edition by Johannes Ramakrishnan Raghu page 536"


Oracle 12c PDB Cloning with RMAN - Aux DB connection issues I'm trying to run the following to duplicate a PDB using RMAN.

rman target  testuser/testuser@PDB01 auxiliary testuser/testuser@PDB02 as sysbackup

I can connect to both the target and aux PDBs remotely using sqlplus without any problems. I can connect to the target using RMAN also. But the auxiliary connection is a problem. Does anybody know why RMAN prevents a remote connection to an auxiliary database in this situation?

rman target testuser/testuser@PDB01 auxiliary testuser/testuser@PDB02

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 19 15:34:51 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB1 (DBID=1985676993)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06814: cannot connect to pluggable database PDB02
If I run the commands individually I get the following ....

RMAN> connect target testuser/testuser@PDB01
connected to target database: TESTDB1 (DBID=1985676993)

RMAN> connect auxiliary testuser/testuser@PDB02

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06814: cannot connect to pluggable database PDB02

Just to prove the aux PDB02 has no issues I can use it as a the RMAN target and it works. It's just the auxiliary command that's the problem:

RMAN> connect target testuser/testuser@PDB02
connected to target database: TESTDB2 (DBID=958109427)

Any suggestions as to the possible cause of this?

$ oerr rman 6814
6814, 1, "cannot connect to pluggable database %s"
// *Cause:  An error was received when connecting to database because
//          the database is a pluggable database.
// *Action: Connect to the root database and retry the command.

That is not how you clone a PDB.

Duplicating PDBs

When you duplicate one or more PDBs, RMAN also duplicates the root (CDB$ROOT) and the seed database (PDB$SEED). The resulting duplicate database is a fully functional CDB that contains the root, the seed database, and the duplicated PDBs.

And this is how you do it:

Cloning a Remote PDB or Non-CDB

Example:

CREATE PLUGGABLE DATABASE pdb02 FROM pdb01@pdb01_link;


Optimize InnoDB settings for current VPS setup I have VPS with following setup:

(4 x 2.40 GHz),
8192 MB RAM MEMORY,
20GB HDD space.

My web service is making almost: 30 mln inserts/day  or  ±350 inserts  per second. In near future amounts will be probably bigger (that will require more CPU).

Can you guys give me advices how to tune current MariaDB InnoDB engine to make the machine work better, in more optimized way?

Here are some current parameters:

aria_pagecache_buffer_size  134217728
aria_sort_buffer_size   268434432
bulk_insert_buffer_size 8388608
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 100
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_populate OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  8388608
innodb_sort_buffer_size 1048576

If you need more parameters, just leave an comment below.

Batch inserts into a single INSERT.  Optimal is to gather 100-1000 at a time and build a single, large, INSERT, then perform it.  It will run about 10 times as fast.
Build a CSV file and use LOAD DATA.  Also much faster than single-row inserts.

For tossing the table and starting over, have innodb_file_per_table=ON.  Caution:  You have probably already put the table in ibdata1, it is too late to take advantage of that flag.  Do not change it.

DROP TABLE or TRUNCATE TABLE will free up space for reuse tomorrow.
 (With it ON, it would also give the space back to the OS.)  But, if you will simply be reloading it, then there is no real need to give the space back.  Simply TRUNCATE TABLE.

Yeah, 10GB vs 8GB is feasible.  But you won't lose another 2GB every day.

10GB for 30M rows?  That's 300 bytes per row?  That sounds high.  Let's see the schema; there are probably some improvements to be made.  Most likely is TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT -- sizes 1/2/3/4/8 bytes, but with different max values.

innodb_buffer_pool_size should probably be 5G if you have 8GB of RAM (and you are running only MySQL on it).  More CPU cores won't help anything.

If your 30M will grow, you probably cannot survive in a tiny 20GB of disk.  However... If you could summarize the data either as you go, or hourly, then you won't need to store all 30M.  Give us more details; we can help further.


How do I migrate Postgres database using an intermediate file I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server.
The network pipe recommended by the Postgres docs won't work for me.  The docs also unhelpfully say "Or you can use an intermediate file if you wish" without giving an example of how to do this.
This is what I've tried.  On the originating server, I ran
$ sudo pg_dump -C {dbname} > ~/dumpfile.sql

to create the dump file.
On the target server, I get this error:
$ sudo -u postgres psql {dbname} < ~/dumpfile.sql
could not change directory to "/home/username": Permission denied
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

Of course the target database does not exist; I used the -C option when creating the dumpfile so that it would be created automatically during upload, but evidently this is not automatic.  I read through the man page for psql, but I didn't see a flag that would make it recognize the "create database" part of the dump file.  Web searching didn't turn up anything, and naturally the Postgres documentation is useless.
How do I upload the file and have the "create database" portion recognized?
Edit
The could not change directory to "/home/username": Permission denied line is not the error. This is just something Postgres always does on my installation.
I have tried the upload command from the /tmp/ directory with 777 permissions on the file.  It still does not work:
/tmp$ ls -Alh
-rwxrwxrwx 1 username group 5.1M Oct 21 11:07 dumpfile.sql
/tmp$ sudo -u postgres psql {dbname} < /tmp/dumpfile.sql
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

When you are restoring a dump taken with -C, the database you specify on the command line is name of the database you connect to in order to execute the creation of the new database.  Therefore it can't be the name of the new database.  It seems like you already know this, you just haven't thought it through.  psql is trying to connect to {dbname} because that is what you explicitly told it to do.
PostgreSQL doesn't "treat" sudo commands differently.  Sudo is in charge, not PostgreSQL.  Different commands do different things, and PostgreSQL is just along for the ride.
You are trying to tell the "postgres" system user to read a file located in  your daily user's home directory.  It probably doesn't have permission to do that.  Again, this is not PostgreSQL's choice.  You could put the file somewhere else (like /tmp) and give it world read permissions, or you could change your pg_hba.conf so you can connect to the database as your daily user rather than needing to sudo all the time.


Simple query with JOIN on 60M rows using index takes forever in MariaDB/MySQL Here's a rather simple SQL query:

select source_id
from data_sources
join sources on sources.id = data_sources.source_id
where log_time + interval sources.retention_days day < current_timestamp;

There are ~800 records in sources and ~60 million records in data_sources.

The DBMS used is Maria DB:

mysqld  Ver 10.3.22-MariaDB-1ubuntu1 for debian-linux-gnu on x86_64 (Ubuntu 20.04)

Indizes are being used apparently:

+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|id|select_type|table       |type |possible_keys                  |key                            |key_len|ref                   |rows|Extra      |
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|1 |SIMPLE     |sources     |index|PRIMARY,idx_sources_1          |index_sources_on_retention_days|4      |NULL                  |757 |Using index|
|1 |SIMPLE     |data_sources|ref  |index_data_sources_on_source_id|index_data_sources_on_source_id|5      |vse_web_dev.sources.id|445 |Using where|
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+

Surprisingly this query takes about an hour on an 8 core Intel i7.
RAM doesn't seem to be a problem: While the MySQL processes are causing a very high CPU load on multiple cores during execution time, there is plenty of free RAM.

Here the DDLs for the tables involved:

create table sources (
    id int auto_increment primary key,
    name varchar(255) null,
    source varchar(255) null,
    start_time varchar(255) null,
    frequency varchar(255) null,
    stop_time varchar(255) null,
    unit varchar(255) null,
    created_at datetime not null,
    updated_at datetime not null,
    type varchar(255) null,
    move_source_file tinyint(1) default 1 null,
    revision int null,
    data_freq_minute int default 1 null,
    shift_right_time_stamp tinyint(1) default 0 null,
    display_name varchar(255) null,
    source_group_id int null,
    retention_days int default 365 not null
);

create index idx_sources_1 on sources (id);
create index index_sources_on_source_group_id on sources (source_group_id);
create index index_sources_on_retention_days on sources (retention_days);

create table data_sources (
    id bigint auto_increment primary key,
    source_id int null,
    log_time datetime null,
    value float null,
    created_at datetime not null,
    updated_at datetime not null
);

create index idx_data_sources_1 on data_sources (id);
create index idx_data_sources_2 on data_sources (id, source_id, log_time);
create index index_data_sources_on_log_time on data_sources (log_time);
create index index_data_sources_on_source_id on data_sources (source_id);
create index index_data_sources_on_updated_at on data_sources (updated_at);

Why is this taking so long and how to speed it up?

Adding an index to data_sources(source_id, log_time) solved the problem.

create index index_data_sources_source_id_log_time on data_sources(source_id, log_time);

This brought the execution time down to a couple of seconds.

Apparently the calculation of interval sources.retention_days day < current_timestamp doesn't have such a massive performance impact, as I suspected.

Thanks for the helpful comments!


SQL Server Concise List Of Trace Flags I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking.

Looking at this query which uses 8691, I was wondering if such a list exists.

with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
OPTION (QUERYTRACEON 8691);

http://antapex.org/traceflags_sqlserver.txt

Although this isn't complete, it is the biggest single list I know of.

This MSDN article lists the Query Optimizer Trace Flags necessary to support several changes to the QO in various QFEs and hotfixes, etc.

http://support.microsoft.com/kb/974006

Technet also has a list of various trace flags (probably already documented in the links above:

http://technet.microsoft.com/en-us/library/ms188396.aspx

This documents trace flag 610:  http://msdn.microsoft.com/en-us/library/dd425070%28v=SQL.100%29.aspx (controls minimally logged inserts into indexed tables)

Technet has another huge list of trace flags at: http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx


Querying True Physical Database File Sizes On querying database file sizes...

1) Most if not all of these answers do not always match the physical file size shown in file explorer:

https://stackoverflow.com/questions/5945360

The accepted answer reports:

vs.

2) This post does seem to match physical file size shown in file explorer:

Is there a query that will report the physical size without relying on dm_os_performance_counters?  Are the first set of queries ignoring free space?

Actual size reported by File Explorer:

Why does Tempdb show a discrepency in both the data and log file sizes as shown?

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

Here you go.  You can use this to get file sizes by file and filegroup.  It includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well.  It includes the full path of the file being evaluated.

SELECT fg.data_space_id AS FGID,
   (f.file_id) AS File_Id,
   -- As provided by OP, size on disk in bytes.
   CAST(f.size AS FLOAT) * 8.00 * 1024 AS Size_On_Disk_Bytes,
   ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
   f.name,
   f.physical_name
FROM sys.database_files f
    LEFT JOIN sys.filegroups fg
    ON f.data_space_id = fg.data_space_id

For more information on the difference between 'Size_On_Disk_Bytes' and 'Actual_File_Size', go here: https://superuser.com/questions/66825/what-is-the-difference-between-size-and-size-on-disk

Thanks


Need help with SQL Server 2014 and disaster recovery I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system.  This database will eventually grow to 1TB +.

Background

Our normal method of disaster recovery is that we replicate our storage to a second array at our DR facility.  During a disaster we use a product called Site Recovery Manager by VMWare that mounts the storage LUNS from the replicated SAN, reconfigures the VM’s with local IP addresses and then starts them on the DR network.

I have several products that use SQL Server and work very well with this solution.  I am able to bring up the databases and the application servers on the new network without issue.

Problem

I have several requirements for this new environment that I am not sure how to handle.

We have a requirement for a read only copy of the database for our data warehouse reporting.
They would like very up to date data, so nightly backup is not an option.

Based on some reading it sounds like log shipping is not going to be an option as users will get kicked out when restoring logs.
I have been looking at Always On Availability Groups, but I don’t believe they will work with our current disaster recovery mechanism as all the inter server communication is bases off IP addresses and not DNS names, so when we re-IP the servers and bring them up on the DR network nothing is going to work.

I have also considered an Availability Group with a replica located at our DR facility but not sure how everything would work during a DR test and once we want to return to normal working mode.  We do have domain controllers in DR and we simply shutdown the real one and restore one with our backup and use it during the DR test.  Once we are done with the test we shut down the restored DC and bring the real one back up.

Question

Does SQL Server 2014 support read only copies without Availability Groups and if so will users loose connectivity while replication is occurring?  If it does then I can hopefully use this method and then I believe our DR method will work.  That is if I can use names when configuring read only and not IP addresses.

Any direction would be much appreciated.

Typically people just have an AG node in their DR environment and do not transfer VMs there (for some of the reasons you noted + more). You should read through the documentation for how AGs work, especially planned manual failovers for when you need to switch node roles.

You could also look at SQL Server replication to replicate your data (even close to real time). It work with DNS and even though the DB is not read only per se, you can set user privileges so they don't modify anything.  The only thing to check is it requires the tables to have a primary key. This could be a good option for your reporting requirements.

Can you consider an upgrade? SQL Server 2016+ has Distributed Availability Groups, which could really solve most of your problems. With Windows Server 2016 you can have cluster nodes from different domains. Your AG would not require it to be in the same domain or network and you can even mix SQL Servers. Read more in the documentation for Distributed availability groups.


CheckDB error 9003 SQL server 2014 Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server.  The checkdb is throwing the following errors.

  The log scan number (105217:402:0) passed to log scan in database
  'db_with_error' is not valid. This error may indicate data corruption
  or that the log file (.ldf) does not match the data file (.mdf). If
  this error occurred during replication, re-create the publication.
  Otherwise, restore from backup if the problem results in a failure
  during startup.

I have used the following to resolve the checkdb error.

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

EXEC sp_resetstatus 'db_with_error';

ALTER DATABASE db_with_error SET EMERGENCY;

dbcc checkdb ('db_with_error')

ALTER DATABASE db_with_error SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET multi_user WITH ROLLBACK IMMEDIATE;

ALTER DATABASE db_with_error SET online;
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'db_with_error',
@CheckCommands = 'CHECKDB'

Why is the above resolving the error?

Things to note that the test server is running 12.0.2495 and the prod server is running 11.0.5548. Also the prod server is apart of an availability group which is in good standing as I understand it.

Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server.

Read this article by Denny Cherry - A SAN Snapshot is not a backup !.

I would suggest you to take native SQL Server backup (preferably with compression - to reduce disk footprint) and then restore it on a test server and run below command to see if you get the same error or not.

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS


After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local Indexes but the problem lies with the PK index for some reason. I have the same DDL in another schema and it works fine there. Not sure what tio look for to resolve this.

I am using the Informatica (ETL tool) to load data into this table. Before loading we do truncate partition table:

SQL> ALTER TABLE owner_name.SAMPLE_PARTITIONED_TBL
   2 TRUNCATE PARTITION SMPL_201001 DROP STORAGE;

As the load starts I get the following error:

Message: Database errors occurred:
ORA-01502: index 'owner_name.SAMPLE_PARTITIONED_TBL_PK' or
partition of such index is in unusable state

Looking at the indexes's status...

SQL> select STATUS from all_indexes
  2  where INDEX_NAME like 'SAMPLE_PARTITIONED_TBL_PK';

STATUS
--------
UNUSABLE

Now after executing SQL> ALTER INDEX owner_name.SAMPLE_PARTITIONED_TBL_PK REBUILD; the local partitioned index returns to STATUS=VALID state the loading can continue without a problem.

UPDATE:

As per @Mat's observation below, I am checking if the PK Index is locally partitioned:

SQL> select * from all_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

I see SAMPLE_PARTITIONED_TBL_PK and the other indexes in the list.

SQL> select * from  all_part_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

Here I see all the indexes with LOCALITY='LOCAL'; except SAMPLE_PARTITIONED_TBL_PK is missing in the result set thus confirming Mat's observation. :)

Thanks.

That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations.

Specifically for truncate partition:

  For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then the database truncates them and resets the UNUSABLE marker to VALID.

So local indexes are truncated and marked valid. Global indexes are invalidated unless you specifically request that they be maintained. (See further down.)
In your case, it looks like your primary key is not based on a local index – it appears in fact not to be partitioned, since you can't do an alter index ... rebuild on a partitioned index, you need to rebuild each partition. So the invalidation is expected.

To do that, you can add an UPDATE INDEXES / UPDATE GLOBAL INDEXES clause to your ALTER TABLE statement to have Oracle automatically maintain the indexes for you during the ALTER - See Updating Indexes Automatically. There are some limitations though, read the Considerations when Updating Indexes Automatically section carefully.

But that's not always desirable. Sometimes it's more efficient to rebuild the indexes after the load. In that case, your load process can often use the SKIP_UNUSABLE_INDEXES parameter (can be set at session level). (This parameter defaults to TRUE in 11gR2.)


Filter by using in for SQL Server I have the below table

Col1       Col2
1          ACA,ABS,PHR
2          PHR
3          MCM
4          ABC

Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I want to get only the rows which have at least one matching code. So in this case I should get the filtered result as

Col1       Col2
1          ACA,ABS,PHR
2          MCM

Now I can use the query Select * from myTable where Col2 in ('ABS','MCM') but then it won't retrieve the first row ACA,ABS,PHR. Can some one please tell me how I can do a text search for codes so as long as one code matches in Col2 I get the row so I can pass in directly the , delimited list and as long as it finds a single match the row is retrieved.

Thanks

You could also split the string and then search on the splitted values.
An example could be by creating and using this function.

Starting from SQL Server 2016 you could use the built in STRING_SPLIT() function.

Creating the function from the blogpost and executing this query afterwards

SELECT DISTINCT Col1,Col2
FROM dbo.Searching
CROSS APPLY dbo.fnSplitString(col2,',')
WHERE splitdata in ('ABS','MCM');

Result

Col1    Col2
1   ACA,ABS,PHR
3   MCM

DB<>Fiddle

An optimization could be proactively splitting the table into a permanent one so you don't need to do splitting on the entire table everytime.  (If the values are static)

EDIT

Comment by @Erikdarling

  There are better ways to split strings: Comparing string splitting /
  concatenation methods, including a much better one at SSC by Jeff
  Moden.

The function by Jeff Moden

After creating the function in his blogpost, you could use a query that resembles the previous one.

  SELECT Col1,Col2
  FROM dbo.Searching
  CROSS APPLY dbo.DelimitedSplit8k(Col2,',') split
  WHERE split.Item in ('ABS','MCM');

DB<>Fiddle


Oracle schema migration to new database with zero downtime I have a problem:
I have two production schemas on one database each serving two different appplicaitons.

I need to migrate (move) one schema to an entirely new database that will be dedicated for that application with zero downtime. SO EXPORT/IMPORT can not be used.

I have license to even active data guard. But if I setup data guard from current database DB to new DB, should I  switch over OR failover or what else so that the current primary will continue to be primary in its role and only the new DB will change its role from standby to fully operational primary. Then we simply direct the application to new and start the new db.
Thanks for your help.

This is by no means the only way or necessarily the best way.  You should consider streams and GoldenGate as well, but if at least one application is designed to use packages and access the data only through those packages, consider the following:

Create a new database and setup views of the original database's tables for one schema using a database link.
Copy the package code for the application using that schema to the new database.
Switch that application to use the new database.  It should work exactly the same, except that it will be slower due to the link.
For each table or group of related tables, add package code that for each database call that maintains data in both the local and remote tables.  There are many ways you could do this.  One way would be to have a switch table with a row for each application table.  You could make a replication routine for the table and when the tables are in sync it could update the switch table to let the code know that it doesn't need to maintain both tables.

Yes, this is complex, and yes it would take a lot of time and testing, but given a requirement of zero down time, it should be expected.


Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 ) I have a strange situation. Using sp_whoisactive I can see this:

Ok, with this query, I can see what is triggering ( does this word exists in english? ) it:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (SPID) c --0 for all cursors running
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

the result:

it's a simple select. Why is this using fetch_cursor?

Also, I see a lot of "blank" sql_texts too. Does this has something with this "cursor"?

DBCC INPUTBUFFER (spid) shows me this:

there's this question  Here ( made by me ) but i don't know if this is the same thing.

EDIT1:

Using the query provided by kin, I see this:

EDIT2:

Using Activity Monitor, I can see this:

It is the most expensive query ( The first one is intentional, we know about it ).

And again, I would like to know, why this select * from... is the reason of FETCH CURSOR...

EDIT3:

This "select * from..." is running from another server ( via linked server ).

Well, Now i'm having problems to understand what @kin said.

This is the execution plan of the query ( running in the same server of the database):

this is now, the execution plan, running in the other server, via linked server:

Ok, Not a problem too. And NOW! the execution plan , via **activity monitor** ( the same select * from ):

It's a simple select. Why is this using fetch_cursor?

The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found.

The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls.

I believe the cursor plan you show in your question is the internal cursor opened by the engine as part of this process, but I have not had time to try to reproduce this yet.


Need ideas about OPTIMIZE TABLE Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of these tables show highlighted (red) 'Overhead' in phpMyAdmin.

Given these conditions, should the tables be OPTIMIZED just after the purge. If not a good idea, why? (The purge occurs at lowest usage time of day.)

Let's say the tables and current (mid-month) Data & Index sizes are:

          Rows     Deleted     Data      Index

table1   17,000     7000      4.3 MiB    1.2 MiB    Holds +300/day Transaction Info
table2    6,000     1000     25.5 MiB    231 KiB    Holds User Schedule Info
table3    1,800       30      297 KiB     43 KiB    Holds User Info

This question is in response to learning about indexes and efforts to reduce slow queries and learning about high counts for:

Handler read rnd next
Handler read prev
Created tmp disk tables
Opened tables

In reading articles about these items, it seems to be a 'learned' science and requires testing.

Thanks for responding.

Your tables are incredibly small. You should be able to run

OPTIMIZE TABLE table1, table2, table3;

very quickly.

Looking at the statistics in your question

Handler_read_rnd_next : The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Handler_read_prev : The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.
Created_tmp_disk_tables : The number of internal on-disk temporary tables created by the server while executing statements. If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

If these global status variables present a problem after running OPTIMIZE TABLE, this is usually a strong indicator that you have to improve the design of your queries and the size of certain caches.


Updating multiple columns using case conditional clause In the following TSQL code the second column TypeId2 is not updated.
What could be the problem here?

declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 int)
insert into @dd values(25,10, 1),(25,15, 2)
insert into @tt values(25,0,0)

update t set
t.TypeId1=case when TypeId=1 then number end,
t.TypeId2=case when TypeId=2 then number end
from @tt t inner join @dd d on d.Id=t.Id

select * from @tt;

After executing the above code and selecting data from @tt I get the following result:

Id  TypeId1 TypeId2
-------------------
25    10      NULL

which is not acceptable because the value of TypeId2 is null, it should be 15.

Check:

UPDATE @tt
SET @tt.TypeId1 = t.TypeId1, @tt.TypeId2 = t.TypeId2
FROM @tt
JOIN ( SELECT Id,
              MAX(CASE WHEN TypeId=1 THEN number END) TypeId1,
              MAX(CASE WHEN TypeId=2 THEN number END) TypeId2
       FROM @dd
       GROUP BY Id ) t
ON @tt.Id = t.Id


Where is pt-table-checksum finding this hostname? The Setup: An EC2 instance running MySQL 5.6 locally replicating to RDS MySQL. The VPC IP address range is 172.0.0.0/8.

Somehow the slave got out of sync and I'm getting this error when I do SHOW SLAVE STATUS:

Could not execute Update_rows event on table db37708.general; Can't find record in 'general', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log newprod.000106, end_log_pos 285712203

I compared the master and slave tables and yes, there is some data out of sync. Not schema, just text data. Okay, fine, I'll just run my script that wraps pt-table-checksum and I get this:

[ubuntu@fabersubuntu utils ]$ ./checksumSpecificTable.sh db37708 general
Replica ip-10-3-2-119 is stopped.  Waiting.
Replica ip-10-3-2-119 is stopped.  Waiting.
^C# Caught SIGINT.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-19T12:09:50      0      0        1       1       0  32.282 db37708.general

So I run the script again after adding PTDEBUG=1 to it and I see things like this:

# DSNParser:1638 30974 DBI::db=HASH(0x21aae58) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:1652 30974 DBH info:  DBI::db=HASH(0x21aae58) $VAR1 = {
#   '@@hostname' => 'ip-10-3-2-119',
#   'CONNECTION_ID()' => '351452',
#   'DATABASE()' => undef,
#   'VERSION()' => '5.6.40-log'

My questions are:

Where is this "hostname" coming from? Assuming it's an IP address format, it makes sense that it is waiting because I don't have any hosts in a 10.0.0.0 range.
If I CALL mysql.skip_repl_error, I get another error; apparently I have lots of errors all of a similar type (so far). I assume pt-table-checksum can't do anything for me if replication isn't working, so what would be next step be other than rebuild my RDS slave?

Your Question 1

Hostname is from the output of SHOW PROCESSLIST. From that output it tries to detect the slaves. If this is not the right outcome for you, then you can define the dsns table with the list of hosts and use --recursion-method=dsn

Part two of your question will need more information, but you may be able to help yourself using PTDEBUG=1 pt-table-checksum <params>

You are welcome to update your original question with the output from the debug if you would like help with understanding the output.

Disclosure: I work for Percona


Foreign key referencing table with composite primary key when I only need 1 column association So lets say we have 2 tables on SQL Server database:

CREATE TABLE VersionedData (
    Id INT NOT NULL,
    Version INT NOT NULL

    CONSTRAINT [PK_VerData]
      PRIMARY KEY (Id, Version)
)

And:

CREATE TABLE PointingTable (
    Id INT PRIMARY KEY,
    RefId INT NOT NULL

    CONSTRAINT [FK_Pnt_Ver] FOREIGN KEY (RefId)
      REFERENCES VersionedData (Id)
)

My idea of this relationship is when joining these 2 particular tables I only care about latest version of VersionedData table (meaning I will ALWAYS match with maximum version for specific ID value giving me exactly 1 row that I need).

Of course the code above causes error that I must reference both PK columns from PointingTable. I could add Version column for PointingTable - however I'd be stuck to that specific version even when newer one will be inserted for VersionedTable.

I'm struggling to figure out how to redesign this relationship, I've thought of computed column for PointingTable that would utilize scalar function for getting max version, but I think that would be overkill, there must be a better way.

The alternative is to create an additional table

CREATE TABLE CurrentVersions (
    Id INT PRIMARY KEY,
    Version INT
)

with appropriate foreign key relations to VersionedData. You can then have the foreign key FK_Pnt_Ver reference CurrentVersions instead.

You'll either need an extra JOIN to fetch the data from VersionedData (I assume it contains additional columns), or alternative store the data you need in CurrentVersions as well. In that case, you don't even need the Version column.

This situation is similar to how Stack Exchange stores posts and revisions. Their database looks very much like the public Data Explorer whose schema is documented here on Meta. The Posts table contains the current version of a post as HTML (Body) and the revisions are stored (as Markdown) in the Text column of the PostHistory table.


Is it OK to blindly add missing indexes? I often use SSMS to test my slow stored procedures for missing indexes.  Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index.  This results in a faster query every time as far as I can tell.

Any reason why I shouldn't continue to do this?

Many reasons.

One of the biggest that I can think of is that the missing index DMVs don't take into account existing indexes.

Example:

You have a table with ColA, ColB, ColC.

Currently you have an index on ColA.  The missing index DMV will suggest you add an index on (ColA, ColB).  This may be correct, but the smart thing to do is to add ColB as a second key on the existing index.  Otherwise you have duplicate coverage and wasted space and overhead.

Similarly, if you have an index on ColB INCLUDE (ColA), it may suggest an index on ColB INCLUDE (ColC).  Again the smart thing to do is to add ColC to the include list in the existing index.

The suggested indexes have an extremely narrow view - they only look at a single query, or a single operation within a single query.  They don't take into account what already exists or  your other query patterns.

You still need a thinking human being to analyze the overall indexing strategy and make sure that you index structure is efficient and cohesive.

If there were no issues with just adding all the suggested indexes then there would be no need to even have them be suggested - they would be implemented automatically.


What do these parameters mean at the beginning of my SQL Server query plan? I'm looking at query plan in SQL Server Mgmt Studio and I see something like this:

(@P0 nvarchar(4000), @P1 nvarchar(4000)) Update...

What does the type mean beside the parameters? For this example lets assume that the column for those parameters are actually varchar(64). Is this how the parameters are being bound at runtime?

EDIT

This is a Java EE application using JDBC 3.0 or 4.0 providers. Basically we have a properties file full of SQL statements like this:

mySqlStatementFoo = UPDATE Schema.Table.Column set column = 1 WHERE objectID = ?

It looks like in the past if there was a binding mismatch we would cast the param like so:

Where objectID = CAST(? AS VARCHAR(36))

I suppose they chose to do it this way since we support multiple databases. I.E. DB2 and Oracle. I haven't seen the actual Java, but I suspect they are just selecting the statement from the file and sending it down the wire. I'm doing load testing and seeing that we are doing table scans and suspect that the optimizer isn't using the correct index because the parameters are being bound incorrectly.

Yes, you are likely seeing implicit conversions due to the guesses SQL Server has to make. It is creating a plan that will work not just for the parameter value you're passing now, but other potential values, too. With strings it (or perhaps JDBC? not sure) chooses an arbitrary default of 4000 and Unicode. I believe there are ways in JDBC to not send Unicode (Unicode vs. non-Unicode implicit conversions are among the most tedious), but you're still going to deal with the length issue (which may or may not be an issue in your specific case; again, not sure).

To avoid the implicit conversions and guesses, and ultimately the scans and high CPU conditions, you need to make SQL Server understand the data type that is being used in the parameter through explicit / strong typing.

Here are my suggestions, in order of preference:

Switch to stored procedures.
Switch to stored procedures.
Declare your variables explicitly, e.g.:

DECLARE @p1 VARCHAR(36) = ?; UPDATE ... WHERE col = @p1;

Keep using CAST/CONVERT.


Select query - group by and sub group by CREATE TABLE [dbo].[temptable]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [uid] INT NOT NULL,
    [pid] INT NOT NULL,
    [event] INT NOT NULL,
    [timeStamp] DateTime NOT NULL
)

I want to select count of uid (group by pid) where event = @event AND timeStamp = [MAX of timestamp](group by uid).

Note that pid..uid is 1..many relationship

Id,uid,pid,event,timeStamp
1,20,300,100,'yesterday'
2,20,300,200,'today'
3,20,300,400,'daybefore'
4,30,300,100,'yesterday'
5,30,300,400,'daybefore'
6,40,300,100,'today'
7,40,300,200,'yesterday'
8,40,300,400,'daybefore'

For the above data, I should get below results if @event = 200

pid, count
300, 1

It shouldn't count uid 30 because there is no event 200 for uid 30. And it should also NOT count uid 40, because event for max(timestamp) i.e. today is 100 which doesn't match to @event i.e. 200.

TimeStamp is DateTime field but I've kept is relative in sample data for simplicity.

If I have figured correctly, what you are after:

; WITH cte AS
  ( SELECT pid, uid, event, timeStamp,
           maxts = MAX(timeStamp) OVER (PARTITION BY pid, uid)
    FROM tableX
  )
SELECT pid, cnt = COUNT(*)
FROM cte
WHERE maxts = timeStamp
  AND event = 200
GROUP BY pid ;

If there is a chance that you have 2 rows with identical pid, uid and timestamp, you'll need to replace COUNT(*) with COUNT(DISTINCT uid).


Database storage limit for open source PostgresSQL I just started using PostgresSQL.  When I installed SQL Server Express years back, you typically had a database storage limit if you didn't install the full version.  I think I remember it being 4GB, but that might have changed.  Is there a database storage size limit for PostgresSQL without buying additional licenses or hardware?  What is that limit?

On their webpage About they clarify that the limit is a lot huge than 4GB.

Limit        Value

Maximum      Database Size  Unlimited

Maximum      Table Size 32 TB

Maximum      Row Size   1.6 TB

Maximum      Field Size 1 GB

Maximum      Rows per Table Unlimited

Maximum      Columns per Table  250 - 1600 depending on column types

Maximum      Indexes per Table  Unlimited


Modeling staff dimension in data warehouse I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like job_start, job_end, salary etc.

Firstly, I tried to draw a dimension for each data, but I consider whether it should be done like a connection between these dimensions each other?

Typically dimensional modelling starts by identifying facts and the dimensions about those facts.  Most of those things you mention are dimension attributes.

Staff/employees alone and their place in an organization is probably going to be modeled with a factless fact table.

You may well have a supervisor/employee relationship, and this would be an instance of a factless fact table where the facts in that table are likely to be dates of supervision (these are really a lot like degenerate dimensions).

However, I expect you will have fact tables like hours worked etc which will use these dimensions as foreign keys.

It is very unusual to be modeling dimensions in isolation, as dimensional modeling is a very pragmatic type of approach which is very dependent upon the type of data you have as well as the types of analysis.  The choice of how many dimension tables and which attributes go in which dimensions is very dependent upon data behavior and can sometimes be quite arbitrary.


Same query plan, different data set, very different query duration SQL Server 2012 This may be a terrible question, because I'm not sure how information I can include to help.

We have data segregated by customer. One customer apparently has higher volume of data. The same query ran for a small customer returns in 2 seconds, and the result is 11 rows. The larger customer takes 47 seconds, and the result is 6600 rows. This is a complicated query with 11 joins. This is just for a report, but the report is timing out and the operator is complaining. It's possible the difference is just the volume of data, but I want to investigate.

When I look at the query plan, the percentages for the query cost are exactly the same between the two. There are no suggestions for indexes. In both query plans, I can see the highest cost is from a join to a table that has 3.8 million rows. However, both sets of data are joining with that same table. In both cases the resulting "actual number of rows" is 3.8 million. In both cases this join has a clustered index scan that is 39% of the query "cost".

So my question is this: Do the percentages and query cost even mean anything? are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Is SQL server lying to me and the actual difference in cost is the "nested loop" for the inner join that produces the final row count even though it lists that as 0% of the cost?

These images show the two elements with the highest cost. They have identical properties between both the query plans. The difference in the final row totals is the result of joins with other tables. But those joins all have a low relative cost.

Do the percentages and query cost even mean anything?

Not really--They have meaning, but they're unreliable.

  are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds?

Exactly--The cost is an estimate. If you look at the detail pop-up when you hover on an operator, you'll see that what you see as "Cost" in the graphical plan is actually "Estimated Operator Cost." It is based on the pre-execution estimate, and will be the same even when actual costs vary.

  Is SQL server lying to me and the actual difference in cost is the "nested loop" for the inner join that produces the final row count even though it lists that as 0% of the cost?

It's not quite lying, but it's not reality either. Again, since these are based on estimates, they can be wrong. In particular, sometimes you'll see Cost% add up to more than 100%--another indicator it's not based on real performance.

There's a better way

IMHO, SQL Sentry Plan Explorer is the easiest way to look at the actual execution performance data. It will give you actual CPU & IO information for each statement in your batch, which you can use to zero in on the most expensive parts of your batch. In this case, you can test the same statement for different customers and look at the actual execution results.

You might also want to try using SET STATISTICS IO,TIME ON and running the results through statisticsparser.com. That will give you a good sense of the IO/CPU differences between the two queries for different customers.


Removing duplicates values with join I have a query that looks like this:

SELECT
B.Company,
COUNT(*) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC

However, there is a field name "Ticket" in table1 that has duplicate values, that I need to exclude. How can I return the count with excluding duplicate "ticket" values.

Use the next query

SELECT
B.Company,
COUNT(DISTINCT A.Ticket) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC


How do I find the least optimised query? A library uses a relational database to store data about library books, their members and lending (books, which are currently borrowed). The database relations of this system are given below.

  The tables are shown in the following format:
  Tablename(column(length),column(length),column(length))

Book(bookno(13), title(20), author(30), edition(7))

Member(memno(4), name(26), category(5), address(40), phone(15))

Lending(memno(4), bookno(13), duedate(8))

The library database currently has 10,000 book tuples and 1,000 member tuples. Assume that 400 book borrowings are on record and 10 copies of the bookno "81-203-1257-0" is on lend.

The following SQL query has been written to retrieve borrower's name with due date given the book no "81-203-1257-0".

SELECT m.*, l.*
FROM Member m, Lending l
WHERE m.memno = l.memno and bookno = "81-203-1257-0";

The following are relational algebra expressions to retrieve the information for the above query.

(i) JOIN(Member and Lending Over memno) where bookno="81-203-1257-0")

(ii) JOIN(Member and (RESTRICT Lending where bookno="81-203-1257-0") over memno

(iii) CARTESIAN(Member and Lending) where bookno="81-203-1257-0")

(iv) RESTRICT(CARTESIAN (Member and (RESTRICT Lending where bookno="81-203-1257-0")) where Member.memno=Lending.memno

(v) PROJECT m.*, l.* (RESTRICT Member m and Lending l where m.memno = l.memno and bookno = "81-203-1257-0")

Which one is the least optimised, and how do I compare with it others? I am new  to database and query optimisation.

My relational algebra is pretty rusty but maybe I can put you on the right track. Start with a simpler problem: What if you were looking for book number "ZZZZ81-203-1257-0" which had no matches in the tables? Which query would be the least optimized then?

Let's randomly consider the third relational algebra expression as an example. You are doing a cartesian join between the member and lending tables so you'll end up with 1000 x 400 = 400000 rows as an intermediate result set. You then check the filter condition on each of the 400000 rows and don't keep any of them. Does that seem efficient or inefficient to you? How does it to compare to the others? Can you draw any comparisons between the reasoning that you used for this case compared to the problem that you're trying to answer?


How do I update azure SQL baseline to remove certain db owners? I need to update the baseline to reflect only the approved list of owners and remove others.
Can anyone tell me how do I do that?
Here is what I see:

How do I remove the owners from baseline marked in red?

You can remove members from a role using the sp_droprolemember stored procedure. Similarly, the stored procedure sp_addrolemember can add additional users to the role.
Here is a statement to remove members from the db_owner role.
EXEC sp_droprolemember [db_owner],[<User Name here>]


Log Shipping and Daily Backup My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site server and it seemed to be working great. The LSBackup job runs every 15 minutes.

I know that I need to change my maintenance plan to Full + Differential, but my question is do I need to remove all log shipping, restore the secondary database from the most recent full backup and then set up log shipping again?

Or, can I pause the LS jobs, to the restore and then restart the LS jobs?

Or, is the secondary server fine an I just need to fix my maintenance plan?

Recently, we set up log shipping to an off-site server and it seemed to be working great. The LSBackup job runs every 15 minutes.

You should change the maintenance plan and just take full and diff (if required). Since logshipping is taking log backups every 15 mins, you dont need hourly log backup.

  but my question is do I need to remove all log shipping, restore the secondary database from the most recent full backup and then set up log shipping again?

No since your log shipping is working fine. Just remove additional log backups from maintenance plans since it can break log shipping.

  Or, can I pause the LS jobs, to the restore and then restart the LS jobs?

As a caution, you can pause all the jobs, take a diff backup and then start your log backup, copy and restore jobs one by one.

Just remove the maintenance plan log backup job.

Much better to use Ola's backup solution rather than using maintenance plans.


check if each value of array are equal I try to get d.id only if all rows with same id have same pday:
with dif_zero as (d.id,
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d
join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from
dif_zero
group by id
having sum(char_sal_dif) = 0
and avg(pday::int) =  (array_agg(pday))[1]::int
and avg(pday::int) =  (array_agg(pday))[2]::int -- and so on...

Maby there is other way (more simple) to get to know if all elements in array equals to one another?
Or I can use window functioin in subquery, and after take only rows with value = 1.
select id, count(id) over(partition by id) one_day

Wich way is better? Or maby there is other way to do it more corrent?

If you want to make sure that all days are the same, just compare the min and max. If they are the same, then all values are the same as well.
with dif_zero as (
  select d.id,
         date_part('day', p.docdate)::int pday
  from sh_billing.dogovor d
   join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from
dif_zero
group by id
having sum(char_sal_dif) = 0 -- where does char_sal_dif come from?
   and min(pday) = max(pday)

A more costly (=slower) but maybe easier to understand way, is to compare the count with the count of distinct values:
having count(*) = count(distinct pday)`


datetime vs timestamp in mysql 5.5 What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this?

I have a situation where I am trying to migrate from db2 to mysql. In DB2 a field is referred to as timestamp but MySQL's timestamp does not support values greater than 2038-01-19 03:14:07, so I have used datetime. Before implementing datetime I want to do an impact analysis.

Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:

Size:

datetime - uses 8 bytes for each field

timestamp - uses 4 bytes for each field (half of the size)

Range:

datetime - 1000-01-01 00:00:00 to 9999-12-31 23:59:59

timestamp - 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC

Timezone:

as @ypercube mention, timestamp converts your data to utc and store it, and when you retrieve it, it converts from utc to your timezone connection.

Concept:

datetime - Is a calendar date(same point in time can be different depends on timezone).

timestamp - Is a point in time, does not matter the timezone your are.

Suggestion:

The 2 main differences are range and size, then think, do you really need dates bigger then 2038-01-19 03:14:07 at the moment(at the moment, no in the future!)?
If no, go with timestamp for now, when you reach a point where you need a date range outside timestamp range, convert it to datetime.


Get rows without an 'end' event I have a series of rows representing either start (S) or and (E) is a thing

+----+-------+-------+
| Id | Event | Thing |
+----+-------+-------+
|  1 | S     | A     |
|  2 | E     | A     |
|  3 | S     | B     |
|  4 | E     | B     |
|  5 | S     | C     |
|  6 | S     | D     |
|  7 | S     | E     |
+----+-------+-------+

I'm trying to get the rows that have no end event.

SELECT [a].[Id] AS [ID a],
       [a].[Event] AS [Event a],
       [b].[Id] AS [ID b],
       [b].[Event] AS [Event b]
  FROM [dbo].[TimeSeries] AS [a]
  LEFT OUTER JOIN [dbo].[TimeSeries] AS [b]
    ON [b].[Thing] = [a].[Thing]
   WHERE [a].[Event] = 'S'
   AND [b].[Event] = 'E'

This returns

+------+---------+------+---------+
| ID a | Event a | ID b | Event b |
+------+---------+------+---------+
|    1 | S       |    2 | E       |
|    3 | S       |    4 | E       |
+------+---------+------+---------+

This is where I'm getting stuck.

I want all the IDs that are neither in the ID a column nor the ID b column.  Then I'm guessing LEFT JOIN to find all the rows that have no end event.

  NB: The real table is an SSIS log with many millions of rows.

Here's a few ways.

Use NOT EXISTS for an anti semi join

SELECT Id,
       Event,
       Thing
FROM   dbo.TimeSeries t1
WHERE  NOT EXISTS(SELECT *
                  FROM   dbo.TimeSeries t2
                  WHERE  t1.Thing = t2.Thing
                         AND t2.Event = 'E');

Partition by Thing and find the minimum (alphabetically first) event in each partition. If it is S rather than E this Thing has no end event.

WITH T
     AS (SELECT *,
                MIN(Event) OVER (PARTITION BY Thing) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Evt = 'S';

Partition by Thing and use LAG to look at the preceding row to the current row when ordering by event alphabetically.  If the current row is S and there is no preceding row Evt will be NULL. Just preserve any such rows. This is 2012+ syntax.

WITH T
     AS (SELECT *,
                LAG(Event) OVER (PARTITION BY Thing ORDER BY Event) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Event = 'S' AND Evt IS NULL;


Stored Procedures Best Practices: Fenced or Unfenced? I believe I understand the reasons behind fenced and unfenced stored procedures.

Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine should there be issues with things like pointers.

Unfenced runs "inside" of the database, which means that performance is better.

From what I have also researched, SQL PL is always basically unfenced, because it is SQL and therefore cannot access memory like programming languages can.

C/C++ and Java procedures can run fenced or unfenced. But since they can possibly access memory, there should be a consideration for running them fenced, unless there is a certainty on the quality of the code to not crash and it needs performance.

First of all, am I correct in my understand of the above?

Next, is it generally a best practice to start out with all stored procedures (even those defined as SQL PL) as fenced first?

Any other best practices for stored procedures, especially as related to fencing and/or security?

  EDIT: Further research has shown that SQL PL procedures cannot run fenced. Since they do not contain any code that could harm the
  database engine such as pointers or file I/O, DB2 knows they are safe
  and runs them inside the engine (ie, unfenced). That being said, I am
  still looking for best practices regarding all other stored
  procedures.

To be more precise, NOT FENCED routines run in the same process space as the database manager itself. The engine is written in C, so calling a not fenced routine is just like calling another C function from main(). This is where all the memory corruption and performance aspects come from: a not fenced routine has access to all the same resources -- memory, files, etc. -- as the database manager process itself.
For a FENCED routine the database manager launches a separate process (db2fmp), which in turn executes the routine code. As a result the operating system protection prevents a fenced routine to access any memory area or resource that belongs to the database manager.
SQL routines cannot be fenced, strictly speaking, because they don't "run", but they are even better than not fenced -- they are bytecode that the DB2 runtime engine itself executes, so there's no separate thread or process.
C and C++ routines can be fenced, in which case they execute in separate processes, or not fenced, in which case they are loaded into the database manager process space and called as functions.
Java routines can only be fenced by the fact that they need a separate process, Java virtual machine, to execute. If you declare them as NOT FENCED, the option is quietly ignored.
Having said all that, your only choice between fenced and not fenced is with C/C++ routines. Typically you would run the fenced for safety, changing to the not fenced mode only when you are pretty sure they can't harm the database manager and they need higher performance1.

1 - Performance difference between fenced and not fenced routines comes from the overhead associated with allocating a fenced process, as well as with interprocess communications between the database manager and the not fenced routine. Even then, the fenced process is not created every time a fenced routine is called; a pool of them will be created and reused for such calls. All this means that you might see the benefit of declaring a routine as fenced only if that routine is called very frequently (as in, tens of times per second or more).


Database scheme for product tracking I'm in the progress of creating a separate login for users who want to track specific product shippings.

So I got the tables:

Products (Id, Name, Color, Description, ...) Used for storing the products, nothing else
ProductTrackings (Id, Name)
ProductTrackingProducts (Id, ProductTrackingId (FK), ProductId (FK)) Used for storing sold products that are being shipped and can be tracked
ProductTrackingUsers (Id, ProductTrackingId (FK), Username, Password) used for storing the individual users who can track product shipments

But now I'm stuck in how to accomplish my task, the requirements are as following:

A single user needs to be able to track multiple (shipped) Products (via the ProductTrackingProducts table, I suppose?)
A single (shipped) Product (via ProductTrackingProduct) can be tracked by multiple users

So I have a list of shipped products that are available for tracking (ProductTrackingProducts?) and want n users to be able to track the status.

I suppose my database schema is not sufficient or effective in handling that, so I'm asking for help.

Normally what you would have is something like this:

CREATE TABLE Product (
ProductID,
Name,
Description
) ;

CREATE TABLE Order ( /* Shipped products */
OrderID,
ProductID,
ShippedDate
);

CREATE TABLE User (
UserName
) ;

And here's the essential table you need. This establishes a many-to-many relationship (commonly known as an intersection or bridge table) between Orders and Users:

CREATE TABLE User_Order (
OrderID,
UserName
) ;

For every order you would insert one record into the User_Order table for every user that can track that order. So you would have something like this:

OrderID         UserName
-----------------------------
1               joe
1               jane
1               john
2               joe
3               bill
3               jane


Optimal PostgreSQL setup for Windows 2008 R2 I have PostgreSQL 9.2 setup on Windows 2008 R2 64bit with 96 gigs of RAM and 8 cores. What would the optimal settings be for shared_buffers, effective_cache_size, work_mem, etc.? I realize that these values vary a lot between Linux and Windows so any help for best practices would be greatly appreciated!

In general, the consensus is that shared_buffers is less important to increase on Windows than it is on Linux.  Again a lot of this is very workload specific.

I would probably start with a max connections of 20 unless you have a lot of idle connections as the norm.  Start with defaults for work_mem and shared_buffers and experiment with tweaking upwards.  Always run test queries twice and discard the first one just to ensure comparable caching.  work_mem should be tweaked upwards if you have a lot of sorts or aggregates which would use a lot of memory per operation.  shared_buffers may need to go higher if you need to reserve more space in memory specifically for PostgreSQL.  However assuming you aren't running anything else that is a memory hog on the machine, I would start with default values and see how far those get you.  In most setups these should be sufficient for decent performance (occasionally even on Linux, lower shared_buffers can be helpful) and then watch for problems.


Get a parent and the children using the parent name Is there a way to get a parent and its children using a single query?
CREATE TABLE `servers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `parent_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO servers (name) VALUES ('kvm01'), ('kvm02')

INSERT INTO servers (name, parent_id) VALUES ('web01', 1), ('db01', 1), ('web02', 2), ('db02', 2)

Current output:
mysql [lab]> SELECT child.id FROM servers AS parent LEFT JOIN servers AS child ON (child.parent_id = parent.id) WHERE parent.name = 'kvm01';
+------+
| id   |
+------+
|    3 |
|    2 |
+------+
2 rows in set (0.00 sec)

Expected output:
+------+
| id   |
+------+
|    3 |
|    2 |
|    1 |
+------+

You can either adjust your join condition
SELECT child.id
  FROM servers AS parent
  LEFT JOIN servers AS child
    ON (child.parent_id = parent.id
     OR child.id = parent.id)
  WHERE parent.name = 'kvm01';

or use UNION / UNION ALL to retrieve the parent record separately:
SELECT child.id
  FROM servers AS parent
  LEFT JOIN servers AS child
    ON child.parent_id = parent.id
  WHERE parent.name = 'kvm01'
UNION ALL
SELECT parent.id
  FROM servers AS parent
  WHERE parent.name = 'kvm01';


SQL Server 2012 AlwaysOn performance best practice Is it best practice to have 2 Primary then 1 Secondary for SQL Server AlwaysOn Availability Groups?

My purpose for having 2 Primary instances is to balance the database load. (e.g. my db1 and db2 is on 1st Primary Server then my db3 and db4 is on 2nd Primary Server, then i will configure my AG to add db1 and db2 to 2nd Primary as passive and db3 and db4 to my 1st Primary Server as passive).

Will this improve the performance of my SQL Server?

AlwaysON in a roundabout way can help with overall performance but it does that by helping you scale out the reads to other nodes which then also help you with blocking/locking.

From how you described it you are spreading out your databases across 2 different instances, but then making replica's of each instance on the other database server.  Outside of locking/blocking benefits you won't get too much out of this, so no, I wouldn't consider this a 'best practice' for performance.  Your active production databases are still sharing server resources with the read only copies.  Thus, this isn't a 'best practice' for performance and AGs.  Also AG's represent a issue where you have to decide if you're willing to get data that's a little bit stale and near real time, or if you want to force performance loss on the master writer and keep the data on the secondary up to date.

If you're interested, here is how StackExchange uses AGs to give you a bit more idea of how enterprises use it.


.bak file not visible in any directory in SSMS I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it.

I can script the restore process, but I've seen this problem before and I'm not sure what could cause the .bak to not appear.

We had the same issue today. It turned out to be a permissions issue, as illustrated in some of the other answers. The difference is that the account we needed to add was NT SERVICE\MSSQLSERVER.

I identified the account by comparing the permissions of the default SQL Backup folder permissions to the folder containing the backup file.

Adding the service account is not as simple as it sounds. Here is what worked for me. (Server 2012 R2)

Log into the server. (The change must be made on the actual server, not through a network share.)
Change the Locations to the local server name.
Type in NT SERVICE\MSSQLSERVER in the name box. (Do not click Check Names)
Click OK. You will then see a list of the matching service accounts. Select MSSQLSERVER, and click OK to accept the selection.

You should see the service account listed in the security settings, and can adjust the permissions accordingly.

After adding permissions (I added Full Control, to match the default SQL Backup folder.) and restarting the SQL Server Management Console, I was able to select and restore my backup file.


Catch deadlock events in a background job? I'm currently catching deadlock events with the profiler. (deadlock, deadlock graph, deadlock chain).
So far so good, but for this, a client computer needs to be constantly running and online.
==> Is there a way to catch & collect deadlock events as a background job directly on SQL Server?

Deadlock events are captured by the system_health Extended Events trace so you don't need to run Profiler interactively or create a SQL Trace to capture the information.
Below is an example query to retrieve the deadlock info from the file target:
--Get xml_deadlock_report events from system_health session file target
WITH
      --get trace folder path and append session name with wildcard (assumes base file name is same as session name)
      all_trace_files AS (
        SELECT path + '\system_health*.xel' AS FileNamePattern
        FROM sys.dm_os_server_diagnostics_log_configurations
        )
      --get xml_deadlock_report events from all system_health trace files
    , deadlock_reports AS (
        SELECT CAST(event_data AS xml) AS deadlock_report_xml
        FROM all_trace_files
        CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
        WHERE trace_records.object_name like 'xml_deadlock_report'
    )
SELECT TOP 10
      deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
    , deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;


mac os - virtualbox - oracle connection error - invalid username/password; logon denied I have followed the below step guide(URL) to setup Oracle on Mac OS. Everything is fine on the guest side. But, I am facing some issues in host level.

I can connect database in Guest (Virtual box) but, when I tried to open the connection from host then I got a below error message.

"ORA-01017: invalid username/password; logon denied.

Note: I am unable to do the step to stop the firewall on Guest.

https://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/

http://blog.codiez.co.za/2013/09/setup-oracle-instant-client-ruby-oci8-gem-mac/

sqlplus hr/oracle@cdb1

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 18 13:32:48 2015

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

ERROR:
ORA-01017: invalid username/password; logon denied

Update: file - /usr/local/oracle/admin/network/tnsnames.ora

cdb1=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = 127.0.0.1)(port = 1521))
   )
 (connect_data =
   (service_name=cdb1)
 )
)

You can manual check password for HR (if You want - force SET it oracle)
Firewall is not important moment, because Oracle return You exactly error text

if all correct, it must be:

macbookva:instantclient_11_2 vladimir$ sqlplus HR/oracle@XE

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 18 19:24:20 2015

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

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>


Sync MySQL schemas between different databases I have a couple of databases that have the exact same structure than my "master" database, but after a couple of months, the developers did small changes (like, add another column, change a datatype to fix an error, etc) and they forgot to replicate it overall databases.
After some research, I found out that mysqldiff could generate the queries to update the databases left behind. But since I am using MySQL 8, it doesn't work (ERROR: Query failed. 1146 (42S02): Table 'mysql.proc' doesn't exist), and the project (mysql-utilities) looks like got abandoned (the last commit is from 2016)...
Does anyone know a free alternative for mysqldiff? I just need to sync the databases, not the data. I am asking because most questions that I looked up where a little bit old.

MySQL Workbench did the trick.
If anyone reading this wants to do the same thing, go to File, create a new model, then go to Database (the menu is different, lol), and then go to Synchronize with Any Source.
I just selected the source and since the databases were on the same server, I selected the overwrite option to change the destination database.


Postgresql - join most recent row I have a query:

SELECT
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    CASE WHEN UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' THEN TRUE ELSE FALSE END as favourited,
    sum(CASE WHEN MovieRating.ratingUp = TRUE THEN 1 ELSE 0 END) as ratingUp,
    sum(CASE WHEN MovieRating.ratingDown = TRUE THEN 1 ELSE 0 END) as ratingDown,
    count(DISTINCT Movie.id) as movieCount

FROM Author

LEFT JOIN Movie ON Author.id = Movie.authorID
LEFT JOIN Series ON Movie.seriesID = Series.id
LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727'
LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id

GROUP BY
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID

ORDER BY Author.surname ASC, Movie.publicationDate DESC

If author has more than one Movie it lists this Author duplicated rows instead of listing only once with most recent movie (not as many times as there are this authors movies) I tried to add Distinct ON Author.id but this not change anything.

How to refactor this query to return proper results?

You can use a derived table that picks the latest movie, then join to that:

SELECT
    Author.id,
    Author.name,
    Author.surname,
    Author.photoURL as photoURL,
    Movie.title as lastMovieTitle,
    Movie.publicationDate as lastMovieDate,
    Movie.languageID as lastMovieLanguage,
    Series.name as lastMovieSeriesName,
    UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727' as favourited,
    count(*) filter (where MovieRating.ratingUp) as ratingUp,
    count(*) filter (where MovieRating.ratingDown) as ratingDown,
    count(DISTINCT Movie.id) as movieCount
FROM Author
  LEFT JOIN (
      SELECT DISTINCT ON (authorid) *
      FROM movie
      ORDER BY authorid, publicationDate DESC
    ) Movie ON Author.id = Movie.authorID
  LEFT JOIN Series ON Movie.seriesID = Series.id
  LEFT JOIN UserAuthorFavourited ON UserAuthorFavourited.authorID = Author.id AND UserAuthorFavourited.userID = '6F9BD058-853F-4B50-92D6-9170118A2727'
  LEFT JOIN MovieRating ON Movie.id = MovieRating.movieID AND Movie.authorID = Author.id
GROUP BY
    Author.id,
    Movie.id,
    Series.id,
    UserAuthorFavourited.userID
ORDER BY Author.surname ASC, Movie.publicationDate DESC

I also simplified the expression for the favourited column and the conditional counting.


A different number of AFTER INSERT trigger alarms in SQLite and SQL Server I have trigger

CREATE TRIGGER [Foo]
AFTER INSERT
ON [Bar]
BEGIN
  UPDATE Log SET count = count + 1;
END

When i execute query

insert into Bar (ColumnName)
values ('SomeValue1'), ('SomeValue2'), ('SomeValue3'), ('SomeValue4')

trigger alarms 4 times in SQLite and 1 time in SQL Server. What's the reason?

In SQL, triggers can be fired "per row" or "per statement".

SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing).

Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which are specified by the phrases FOR EACH ROW or FOR EACH STATEMENT in the trigger's definition (though amusingly both have different defaults: DB2 defaults to per-row, PostgreSQL to per-statement). I vaguely recall Oracle supports both types too but there was something weird about its "per statement" variant.

Typically per-row triggers have access to the before/after state of the row they are triggered by, while per-statement triggers have access to a pseudo-table of the rows that the triggering statement affects.


Restrict access for Windows administrator in PostgreSQL Is there a way to protect PostgreSQL from user with Windows administrator rights?
Like this question but for Postgres.

No, there is no reliable way to prevent the local administrator (or a domain admin with the ability to grant themselves local admin on a machine) from accessing the raw PostgreSQL data files, starting/stopping the server, changing the service account password, changing pg_hba.conf so they can log in to the server, etc.

As stated in the comments, if you don't trust someone don't give them local admin.

Sufficiently strict group policy could be used to make it a lot harder - but in the process would render the local admin account effectively useless. It would be more typical to create accounts that are "nearly admin", ie they have many rights granted to them but are not local admins and are also quite restricted by ACLs and group policy. This is fairly typical in a corporate SOE, but requires advanced Windows knowledge to set up and won't work on a stand-alone desktop.

If you're envisioning some scheme where you install PostgreSQL on a standalone Windows computer - say as part of some application you're distributing - and stop the person who owns the computer from accessing it then you're totally out of luck. No hope. You can slow the user down a bit if you use an embeddable database that supports whole-DB encryption like SQLcipher or SQLite see,  but even then a user with a clue and a debugger can still get in sooner or later, even if you try to add anti-debugger features to your application. PostgreSQL doesn't support in-application embedding or whole-database encryption, so if this is what you want you should look elsewhere.


How Ola Hallengrens Index maintenance scripts handles columnstore indexes? I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support.

My experience is that script didn't do anything to fragmented columnstore index. Is that by design?

Ola Hallengren's solution doesn't support Columnstore indexes for now.

There is an outstanding feature request on his Github that hasn't been closed as wontfix so you might want to keep an eye on that: ColumnStore Support for Index Maintenance #71


How to identify when to move all or part of the data from Sybase ASE to Sybase IQ? Is there an easy way that any of you guys use to determine this?

Ok, we have more than x gb of data, we should go for IQ!
Ok, we have more than x reports going, we should go for IQ!
Ok, we have a lot of data archived and need better timings retrieving it, we should go to IQ!

What are the factors that you use to make this decision?

Tough call. Look at it from a transactional RDBMS versus reporting-style OLAP perspective, at a high level. The IQ methodology is column-store, versus a traditional row-store for ASE. Column-stores are very fast when running aggregated queries over them, but that depends on how your reports are run, right?

I think you almost nailed it with this:

  Ok, we have a lot of data archived and need better timings retrieving
  it, we should go to IQ!

So, do your analysis on volume of data you'll be querying all round, that is fairly static or slowly changing. If that's going to be queried a lot, look into IQ.

Don't be scared of a hybrid solution either. I have several SQL Server customers that have massive warehouses running with columnstore indexes, right in the same cabinet as their highly transactional RDBMS.


Primary Key Type's Effect on Performance in Oracle? In MS SQL Server, it is well documented that the type of the primary key can have a dramatic effect on performance. Using narrower types and sequential values significantly improves insert performance when using SQL Server, which is why using integers instead of guids for surrogate keys tends to make sense.

However, I cannot find any references to the same discussion for Oracle. Someone asked me to look at a schema for them that uses varchar2(32)'s for all of the primary keys, which seems much wider than needed. Does this matter in Oracle?

Bonus points for anyone who can refer me to a good blog like Kimbery Tripp's but in Oracle world.

From what I know, it depends on whether your table is IOT (Microsoft calls it clustered table, a table with clustered index)or not . Historically, Oracle IOTs performed worse than its heap tables. That seems to change (I believe with Oracle 10), but there is still prejudice against IOT. So the article you mentioned can be applied to some extent if you use IOT in Oracle, but not for heap tables.
Don't misunderstand me, having a PK constraint supported by index on large varchar2 column has impact on performance, but if it's an Oracle heap table, it's the same as having an index on non-PK varchar2 column.


How to DELETE duplicate entries on a field I have a huge table that has 1,990,546 users with numerous data.  Sometimes I get email lists and dump them into this table also.

I want to delete users with duplicate email addresses.  However, I want to keep users WHERE chips > 0.

This query ran for over an hour before I terminated it:

SELECT id, email
FROM users
WHERE id IN
    (
        SELECT a.id
        FROM users a, users b
        WHERE (a.email = b.email)
        AND (a.chips > 0)
    )
LIMIT 100;

Is there a faster or better way to do this?

I would use a temp table

#
# Collect All IDs Your Intend to Keep
#
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
CREATE TABLE KeepIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO KeepIDList (id) SELECT min_id FROM
(SELECT email,MIN(id) min_id FROM users GROUP BY email) A;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# Collect All IDs Your Intend to Delete
#
CREATE TABLE ZapIDList ENGINE=MyISAM SELECT id FROM users;
INSERT INTO ZapIDList (id)
SELECT A.id FROM users A LEFT JOIN KeepIDList B WHERE B.id IS NULL;
ALTER TABLE KeepIDList ADD PRIMARY KEY (id);
#
# From Collected Keys to Delete, Remove Keys Whose chips > 0
#
DELETE B.* FROM users A INNER JOIN ZapIDList B WHERE A.chips > 0;
#
# From Collected Keys Left to Delete, Perform DELETE JOIN
#
DELETE A.* FROM users A INNER JOIN ZapIDList B;
#
# Drop Temp Tables
#
DROP TABLE KeepIDList;
DROP TABLE ZapIDList;

I have a very good reason for using temp tables as opposed to doing a Direct DELETE JOIN of a table against itself: Performing a DELETE using a subquery that references the same table experiencing the DELETE tends to make keys disappear intermittently as the query is being optimized. I wrote about this back on Feb 22, 2011 : Problem with MySQL subquery

CAVEAT

Please test this out with a smaller dataset to make sure my answer indeed performs what you asked. Make sure you make s backup of the users table. To coin a phrase from 1960's  Mission Impossible: Should any of your data be caught or killed, I will disavow any knowledge of your actions (Start From 1:50 of the YouTube Video).


Dump with special characters in table column name I'm taking over an old database, and while doing recovery tests from my dump I found out a table is wrongly backed up, the table definition is truncated.

Looking at the live db I've found a column name with special character in the name. That is the column name is Località.

My current mysql dump produces the following:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contabilita_banche` (
  `ID_Banca` int(6) unsigned NOT NULL default '0',
  `Descrizione` varchar(70) default NULL,
  `Località;
/*!40101 SET character_set_client = @saved_cs_client */;

The first problem is that the table doesn't end there, there are other fields after Località, so the structure dump is incomplete. Other than this the dump doesn't throw any error and the data is dumped correctly to the end.

I tried several commands, all without success:

mysqldump -r /tmp/cbnew2.sql --default-character-set=latin1 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche
mysqldump -r /tmp/cbnew2.sql --default-character-set=utf8 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche

Server version is 4.1.22-log, client 5.5.38. Is there any way to correctly produce the dump?

I tried renaming the table via "adminer" (php mysql manager) but even there the table name was not correct (not showing at all, to be honest). I tried renaming but of course I produced a new table column.

In the end I had to recreate the table.

I managed to get most of the schema from the SHOW CREATE TABLE contabilita_banche\G above.
Then I took the dump with the data, adjusted the CREATE TABLE command with a utf-8 column name and restored the new dump.

Thank you very much Rolando for your support!